索引（index）是幫助MySQL高效獲取資料的資料結構，常見的查詢演算法：順序查找、二分查找、二叉樹查找、哈希散列、分塊查找、B樹，
1）哈希演算法：就是把任意長度值（key）通過散列演算法變成固定長度的key地址，通過這個地址進行訪問的資料結構，它通過關鍵碼值映射到表中一個位置來訪問記錄，以加快查找速度，時間復雜度為O(1)，尋址查詢，不適用于范圍查詢，無法排序，
2）二叉樹：它的左子節點值比父節點值小，右子節點值比父節點值大，時間復雜度為O(logN)，缺點：不平衡二叉樹，
3）B樹：度（degree）-節點的資料存盤個數；葉節點具有相同的深度且指標為空；葉節點的資料key從左到右遞增排序，
4）B+樹：真實的資料存在于葉子節點，非葉子節點不存盤真實的資料，只存盤指引搜索方向的資料項，
常見的索引原則：
1）選擇唯一性索引：唯一性索引的值是唯一的，可以更快速地通過該索引來確定某條記錄，
2）為常作為查詢條件的欄位建立索引、為經常需要排序、分組和聯合操作的欄位建立索引，
3）限制索引的數目：越多的索引，會使更新表變得很浪費時間，
4）盡量使用資料量少的索引：如果索引的值很長，那么查詢的速度會受到影響，
5）盡量使用前綴來索引：如果索引欄位的值很長，最好使用值得前綴來索引，
6）洗掉不再使用或很少使用的索引，
7）最左匹配原則，是非常重要的原則，
8）盡量選擇區分度高的列作為索引，
9）索引列不能參與運算，帶函式的查詢不能參與索引，
10）盡量的擴展索引，不要新建索引，
創建索引
1）創建表時，創建索引：
create table 表名 (欄位 資料型別 , [unique | fulltext | spatial][index | key] 索引名稱 (被索引欄位名[length]) [asc | desc] );
1 CREATE TABLE book (
2 bookid INT NOT NULL,
3 bookname VARCHAR(255)NOT NULL,
4 year_publication YEAR NOT NULL,
5 UNIQUE INDEX UniquId(id), ##唯一索引
6 INDEX(year_publication), ##普通索引
7 INDEX SingleIdx(bookname(20)), ##單列索引
8 INDEX MultiIdx(bookid,bookname(100)), ##組合索引
9 FULLTEXT INDEX FullTextIdx(bookname)， ##全文索引（只支持MyISAM—>engine=MyISAM）
10 g GEOMETRY NOT NULL,
11 SPATIAL INDEX spatIdx(g) ##空間索引（存盤引擎必須為MyISAM且空間型別的欄位為空值）
12 );
2）在已經存在的表上創建索引：
a. 使用 alter table 創建索引：alter table 表名 add [unique | fulltext | spatial] [index] 索引名稱 (被索引欄位名[length]) [asc | desc];
例：alter table book add index BknameIdx(bookname(30));
b. 使用 create index 創建索引：create [unique | fulltext | spatial] [index] 索引名稱 on 表名 (被索引欄位名[length]) [asc | desc];
洗掉索引
1）使用 alter table 洗掉索引：alter table 表名 drop index 索引名稱;
例：alter table book drop index BknameIdx;
2）使用 drop index 洗掉索引：drop index 索引名稱 on 表名;
查看索引
show index from 表名;
索引的分類：唯一索引/非唯一索引、主鍵索引（主索引）、聚集索引/非聚集索引、組合索引，
1）唯一索引是在表上一個或者多個欄位組合建立的索引，這個或者這些欄位的值組合起來在表中不可以重復，
如下表中，為“學號”建立索引：
學號 姓名
------------------------------------
001 張三
002 李四
2）非唯一索引是在表上一個或者多個欄位組合建立的索引，這個或者這些欄位的值組合起來在表中可以重復，不要求唯一，
如下表，為Score建立索引，可不唯一：
Score Name
98 張三
98 李四
3）主鍵索引（主索引）是唯一索引的特定型別，表中創建主鍵時自動創建的索引，一個表只能建立一個主索引，
4）聚集索引（聚簇索引）：表中記錄的物理順序與鍵值的索引順序相同，一個表只能有一個聚集索引，
擴展：聚集索引和非聚集索引的區別？分別在什么情況下使用？
聚集索引和非聚集索引的根本區別是表中記錄的物理順序和索引的排列順序是否一致，
聚集索引的表中記錄的物理順序與索引的排列順序一致，
優點是查詢速度快，因為一旦具有第一個索引值的記錄被找到，具有連續索引值的記錄也一定無力的緊跟其后，
缺點是對表進行修改速度較慢，這是為了保持表中的記錄的物理順序與索引順序一致，而把記錄插入到資料頁的相應位置，必須在資料頁中進行資料重排，降低了執行速度，在插入記錄時資料檔案為了維持B+樹的特性而頻繁的分裂調整，十分低效，
建議使用聚集索引的場合為：
a. 某列包含了小數目的不同值；
b. 排序和范圍查找；
非聚集索引的記錄的物理順序和索引的順序不一致，
其他方面的區別：
a. 聚集索引和非聚集索引都采用了B+樹結構，但非聚集索引的葉子層并不與實際的資料頁相重疊，而采用葉子層包含一個指向表中的記錄在資料頁中的指標的方式，聚集索引的葉子節點就是資料節點，而非聚集索引的葉子節點仍然是索引節點，
b. 非聚集索引添加記錄時，不會引起資料順序的重組，
看上去聚簇索引的效率明顯要低于非聚簇索引，因為每次使用輔助索引檢索都要經過兩次B+樹查找，這不是多此一舉嗎？聚簇索引的優勢在哪？
由于行資料和葉子節點存盤在一起，這樣主鍵和行資料是一起被載入記憶體的，找到葉子節點就可以立刻將行資料回傳了，如果按照主鍵id來組織資料，獲得資料更快，
輔助索引使用主鍵作為“指標”，而不是使用地址值作為指標的好處是，減少了當出現行移動或者資料頁分裂時，輔助索引的維護作業，Innodb在移動行時無需更新輔助索引中的這個“指標”，也就是說行的位置會隨著資料庫里的資料的修改而發生變化，使用聚簇索引就可以保證不管這個主鍵B+樹的節點如何變化，輔助索引樹都不受影響，
建議使用非聚集索引的場合為：
a. 此列包含了大數目的不同值；
b. 頻繁更新的列；
5）組合索引（聯合索引）：基于多個欄位而創建的索引就稱為組合索引，
創建索引
create index idx1 on table1(col1,col2,col3);
查詢
select * from table1 where col1=A and col2 =B and col3 = C;
舉例說明：給出一個多列索引(username,password,age)，當三列在where中出現的順序如(username,password,age)、(username,password)、(username)才能用到索引，如下面幾個順序（password,age）、(password)、(age)---這三者不從username開始，（username,age）---斷層，少了password，都無法利用到索引，因為B+樹多列索引保存的順序是按照索引創建的順序，檢索索引時按照此順序檢索，
資料庫索引的原理（實作）
MyISAM索引實作
MyISAM引擎使用B+樹作為索引結構，葉子節點的data域存放的是資料記錄的地址，在MyISAM中，主索引和輔助索引在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key可以重復，
MyISAM中索引檢索的演算法為首先按照B+樹搜索演算法搜索索引，如果指定的key存在，則取出其data域的值，然后以data域的值為地址，讀取相應資料記錄，MyISAM的索引方式也叫做“非聚集索引”，
InnoDB索引實作
雖然InnoDB也使用B+樹作為索引結構，但具體實作方式卻與其截然不同，
第一個重大區別是InnoDB的資料檔案本身就是索引檔案，在InnoDB中，表資料檔案本身就是B+樹組織的一個索引結構，這棵樹的葉結點data域保存了完整的資料記錄，這種索引也叫做聚集索引，
第二個與MyISAM索引的不同是InnoDB的輔助索引data域存盤相應記錄主鍵的值而不是地址，
索引的優缺點
優點：大大加快了資料的檢索速度；
創建唯一性索引，保證資料庫表中每行資料的唯一性；
加速表和表之間的連接；
在使用分組和排序子句進行資料檢索時，可以顯著減少查詢中分組和排序的時間，
缺點：索引需要占物理空間；
當對表中的資料進行增加、洗掉和修改時，索引也要動態的維護，降低了資料的維護速度，
輔助索引
聚集索引這種實作方式使得按主鍵的搜索十分高效，但是輔助索引搜索需要搜索兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄，
為什么不建議使用過長的欄位作為主鍵？
因為所有輔助索引都參考主索引，過長的主索引會令輔助索引變得過大，
B樹的查找程序
如上圖所示，如果要查找資料項為29，那么首先會把磁盤塊1由磁盤加載到記憶體，此時發生一次IO，在記憶體中用二分查找確定29在17和35之間，鎖定磁盤塊1的P2指標，記憶體時間因為非常短（相比磁盤的IO）可以忽略不計，通過磁盤塊1的P2指標的磁盤地址把磁盤塊3由磁盤加載到記憶體，發生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指標，通過指標加載磁盤塊8到記憶體，發生第三次IO，同時記憶體中做二分查找找到29，結束查詢，總計三次IO，真實的情況是，3層的B+樹可以表示上百萬的資料，如果上百萬的資料查找只需要三次IO，性能提高將是巨大的，如果沒有索引，每個資料項都要發生一次IO，那么總共需要百萬此次的IO，顯然成本非常高，
注意：當B+樹的資料項是復合的資料結構（建立的是復合索引），比如(name,age,sex)的時候，B+樹是按照從左到右的順序建立搜索樹的，比如當(張三,20,F)這樣的資料來檢索的時候，B+樹會優先比較name來確定下一步的搜索方向，如果name相同再依次比較age和sex，最后得到檢索的資料；但當(20,F)這樣的沒有name的資料來的時候，B+樹就不知道下一步該查哪個節點，因為建立搜索樹的時候name就是第一個比較因子，必須要先根據name來搜索才能知道下一步去哪里查詢，比如當(張三,F)這樣的資料來檢索時，B+樹可以用name來指定搜索方向，但下一個欄位age的缺失，所以只能把名字等于張三的資料都找到，然后再匹配性別是F的資料了，這個是非常重要的性質，即索引的最左匹配特性，注意：B+tree多列索引保存的順序是按照索引創建的順序，檢索索引時按照此順序檢索，
最左匹配原則中where字句有or出現還是會遍歷全表，
性別欄位為什么不適合加索引？從B+樹原理解釋，
盡量選擇區分度高的欄位作為索引，區分度的公式是count(distince col)/count(*)，表示欄位不重復的比例，比例越高我們掃描的記錄數越少，唯一鍵的區分度為1，而一些狀態、性別欄位可能在大資料面前區分度為0，在性別欄位上增加索引，并不能明顯加快索引速度，
MySQL的B+樹索引的優點？為什么不用二叉樹？B樹和B+樹為什么比紅黑樹更合適？
資料庫檔案很大，需要存盤到磁盤上，索引的結構組織要盡量減少查找程序中磁盤IO的讀取次數，
1）高度原因
B+樹中的每個節點可以包含大量的關鍵字，這樣樹的深度降低了，所以任何關鍵字的查找必須走一條從根節點到葉子節點的路，所有關鍵字查詢的路徑長度相同，導致每一個資料的查詢效率相當，這就意味著查找一個元素只要很少節點從外存磁盤中讀入記憶體，很快訪問到要查詢的資料，減少了磁盤IO的讀取次數，
2）磁盤預讀原理和區域性原理
將一個節點的大小設為等于一個頁（16k），這樣每個節點只需要一次IO就可以完全載入，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/101388.html
標籤：MySQL