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，

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

聚集索引（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）

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

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

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

例如，現有主鍵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的主鍵值進行回表，而不是找出所有姓高的同學之后，對他們的所有主鍵值都進行回表，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/16383.html

標籤：MySQL