MySQL索引 索引分類 最左首碼原則 覆蓋索引 索引下推 聯合索引順序

What's Index ?

索引就是幫助RDBMS高效獲取數據的數據結構。

索引可以讓我們避免一行一行進行全表掃描。它的價值就是可以幫助你對數據進行快速定位。

索引分類

按照功能邏輯來分

• 普通索引 INDEX(col_name)或者key index_name(col_name) 沒有任何約束，一張表可以有多個普通索引
• 唯一索引 UNIQUE INDEX index_name(col_name) 在不同索引上增加了唯一約束，允許多個ull值
• 主鍵索引 PRIMARY KEY (col_name) 主鍵約束=UNIQUE+NOT NULL，一張表只能有一個主鍵索引---該性質由主鍵索引的物理實現方式決定---數據在文件中只能按照一種順序進行存儲
• 外鍵索引 CONSTRAINT foreign_key_name FOREIGN KEY(col_name) REFREENCES table_name(col2_name)
• 全文索引 FULLTEXT INDEX index_name(col_name)...)ENGINE=MyISAM; MySQL自帶的全文索引僅支持英文，一般我們使用專門的全文搜索引擎Elasticsearch或者solr。

按照物理實現方式來分（也是按照葉子結點的內容來分）索引模型見此working

• 聚集索引（InnnoDB主鍵索引/ clustered index），葉子節點存放的是整行數據。一張表只能有一個聚集（InnnoDB主鍵）索引。查詢時，儘量選用主鍵進行查詢，這樣可以減少回表次數，提高效率
• 非聚集索引（InnnoDB非主鍵索引/二級索引/輔助索引/second index）,葉子節點存放的是主鍵的值，為了找到數據，它單獨維護了一個索引表（樹），先在索引表（樹）中查詢主鍵值，再到主鍵索引上查找對應的真實數據，這個過程稱為回表
• 區別：
  • 聚集索引葉子節點存儲整行數據，非聚集索引存儲的是主鍵值---是數據位置，非聚集索引不會影響數據表的物理存儲順序。（而聚集索引決定物理存儲結構）
  • 一張表只能有一個聚集索引，但可以有多個非聚集索引
  • 主鍵索引的查詢效率高，但對數據的增刪改的效率比非主鍵索引低

但是請格外註意：主鍵索引不一定是聚集索引

因為聚集索引決定了資料庫物理存儲結構，而主鍵只決定資料庫邏輯組織方式

在InnoDB中，主鍵是一個聚集索引，但當一個表沒有主鍵或者沒有索引，innodb也會有對應的處理規則。而在MyISAM引擎中，主鍵不是聚集索引。一般按照innodb與機具集索引的關係進行討論。

按照欄位個數來分

• 單一索引
• 聯合（組合）索引

最左首碼原則

創建聯合索引時我們要註意聯合索引的欄位順序

因為B+樹的結構，所以有最左首碼原則/最左匹配原則，意思就是，對於聯合索引，只要查詢條件與聯合索引從左到右部分欄位順序相匹配，該次查詢就可以利用聯合索引進行加速。

例如現有聯合索引（x,y,z）

如果查詢條件是WHERE x=1 AND y=2 AND z=3；那麼就是對該聯合索引的完全匹配。

如果查詢條件是WHERE x=1 AND y=2；那麼就是利用了該聯合索引（符合最左匹配原則）

當然，WHRER x=1;可以以利用該聯合索引

如果是WHERE y=2 AND z=3；或者 WHERE z=3；等，就無法利用聯合索引（x,y,z）

如何安排聯合索引的欄位順序

• 原則一：精簡索引數量

  如果聯合索引的順序可以讓我們少維護一個索引，那麼這個聯合索引順序就該被優先考慮

  比如說現在你有兩個欄位

  那麼只需要建立（a,b）+（b）兩個索引即可。而不是（a）+（b）+（a+b）

  因為最左首碼原則給予了聯合索引復用能力

  當然，如果僅有根據欄位a來進行索引的要求，b欄位的單一索引可以不創建。如無必要，勿增實體

• 原則二：精簡索引占用的空間

  那麼在a，b兩個欄位，中我們應該選用哪個欄位來做單獨的索引（b）呢

  這時候就需要考慮空間了，一般將欄位長度比較小的當做這裡的單一索引（b）。

  比如說有name和age兩個欄位，name欄位比age大，如果我們使用（age,name）+（name）的形式，所占用的空間就比（name，age）+（age）的要大

覆蓋索引

用自己的話總結，覆蓋索引就是不需要回表的查詢

翻譯過來就是， 要的查詢結果就是主鍵值且查詢條件就是非主鍵索引欄位，即在索引樹上的查詢結果就是我們需要的結果。

索引覆蓋可以顯著減少樹的搜索次數，顯著提升查詢性能。是常用的性能優化手段。

例如，現有主鍵id，有已經建立了普通索引的欄位col(int)，我們查詢SELECT id from test where col between 6 and 8;

執行該語句時，引擎內部在索引樹上讀取了三條記錄，但因為對於MySQL的Server層來說，該語句就是拿到了符合條件的兩個記錄，因此MySQL認為掃描行數是2

索引下推

MySQL5.6版本引入索引下推，就是在只能利用部分聯合索引時，對剩餘聯合索引的欄位（不符和最左匹配原則的欄位）進行先判斷，先過濾，通過先過濾來減少回表的次數。

例如有索引（name，age），然後執行SELECT * FORM student WHERE name like '高%' and age=21;

5.6後，MySQL就會先利用聯合索引找出所有姓高的名字，然後僅對age=21的主鍵值進行回表，而不是找出所有姓高的同學之後，對他們的所有主鍵值都進行回表。