【MySQL系列】-回表、覆蓋索引真的懂嗎

一、MYSQL索引結(jié)構(gòu)

1.1 索引的概念

MYSQL官方對(duì)索引的定義為：索引（Index）是幫助MySQL提高獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。索引的本質(zhì)是數(shù)據(jù)結(jié)構(gòu)?？梢院?jiǎn)單理解為"預(yù)先排好一組能快速查詢的數(shù)據(jù)結(jié)構(gòu)"。這些數(shù)據(jù)結(jié)構(gòu)以某種方式指向數(shù)據(jù)，可以通過(guò)這些數(shù)據(jù)結(jié)構(gòu)實(shí)現(xiàn)高級(jí)查詢算法。

1.2 索引的特點(diǎn)

1. 索引一個(gè)排序的數(shù)據(jù)結(jié)構(gòu)可以加速數(shù)據(jù)庫(kù)的檢索速度。
2. 索引降低了數(shù)據(jù)庫(kù)Insert、Update、Delete等維護(hù)任務(wù)的難度
3. MySQL索引只能創(chuàng)建在表上，不能創(chuàng)建在視圖上。
4. 查詢處理器執(zhí)行SQL語(yǔ)句，一個(gè)表上，一次只能使用一個(gè)索引

1.3 索引的優(yōu)點(diǎn)

1. 提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫(kù)的IO成本
2. 創(chuàng)建唯一性的索引，保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性。
3. 加速表和表之間的連接。
4. 在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)，可以顯著減少查詢中分組和排序的時(shí)間。

1.4 索引的缺點(diǎn)

1. 創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加
2. 索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會(huì)更大
3. 當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，降低了數(shù)據(jù)的維護(hù)速度

二、B-Tree與B+Tree

2.1 B-Tree

B-Tree及為B樹。B樹是一種自平衡的樹，能夠保持?jǐn)?shù)據(jù)有序。這種數(shù)據(jù)結(jié)構(gòu)能夠讓查詢數(shù)據(jù)，順序訪問(wèn)、插入數(shù)據(jù)及刪除的動(dòng)作，都在對(duì)數(shù)時(shí)間內(nèi)完成。B數(shù)概況來(lái)說(shuō)是一個(gè)一般化的二叉查找樹，可以擁有多于2個(gè)子節(jié)點(diǎn)。與自平衡二叉查找樹不同，B樹為系統(tǒng)大塊數(shù)據(jù)的讀寫操作做了優(yōu)化。B樹減少定位記錄時(shí)所經(jīng)歷的中間過(guò)程，從而加快存取速度。B樹這種數(shù)據(jù)結(jié)構(gòu)可以用來(lái)描述外部存儲(chǔ)。

2.2 B+Tree

B+Tree是B-Tree的一種優(yōu)化。節(jié)點(diǎn)上只存儲(chǔ)鍵值，不存儲(chǔ)數(shù)據(jù)。這樣的設(shè)計(jì)在有限的節(jié)點(diǎn)空間（頁(yè)空間）內(nèi)可以存放更多的鍵值、指針。所有數(shù)據(jù)都存放在葉子節(jié)點(diǎn)中，所有葉子節(jié)點(diǎn)之間有鏈指針（雙向循環(huán)列表），便于范圍查詢，也便于排序。

2.3 B-Tree 與B+Tree樹的區(qū)別

1. B-Tree 中，所有節(jié)點(diǎn)都會(huì)帶有指向具體記錄的指針；B+Tree 中只有葉子結(jié)點(diǎn)會(huì)帶有指向具體記錄的指針。
2. B-Tree 中不同的葉子之間沒(méi)有連在一起；B+Tree 中所有的葉子結(jié)點(diǎn)通過(guò)指針連接在一起。
3. B-Tree 中可能在非葉子結(jié)點(diǎn)就拿到了指向具體記錄的指針，搜索效率不穩(wěn)定；B+Tree 中，一定要到葉子結(jié)點(diǎn)中才可以獲取到具體記錄的指針，搜索效率穩(wěn)定。

B+Tree 中，由于非葉子結(jié)點(diǎn)不帶有指向具體記錄的指針，所以非葉子結(jié)點(diǎn)中可以存儲(chǔ)更多的索引項(xiàng)，這樣就可以有效降低樹的高度，進(jìn)而提高搜索的效率。

B+Tree 中，葉子結(jié)點(diǎn)通過(guò)指針連接在一起，這樣如果有范圍掃描的需求，那么實(shí)現(xiàn)起來(lái)將非常容易，而對(duì)于 B-Tree，范圍掃描則需要不停的在葉子結(jié)點(diǎn)和非葉子結(jié)點(diǎn)之間移動(dòng)。

2.4 那么為什么InnoDB的主鍵最好要搞成有序的？

InnoDB中主鍵索引是聚集索引，所有數(shù)據(jù)都存在主鍵索引所在的聚集索引的B+Tree結(jié)構(gòu)的葉子節(jié)點(diǎn)中。如果每次插入的主鍵是大小隨機(jī)的話，每次數(shù)據(jù)進(jìn)來(lái)找到的葉子節(jié)點(diǎn)的位置是隨機(jī)的，這樣的話，有些葉子節(jié)點(diǎn)所在頁(yè)本來(lái)就排滿了，結(jié)果又來(lái)了一條數(shù)據(jù)，就勢(shì)必要引起頁(yè)分裂，所以導(dǎo)致性能下降；但是如果主鍵是有序的話，每次進(jìn)行都找到當(dāng)前葉子前面的位置，一個(gè)一個(gè)葉子按順序排滿一個(gè)頁(yè)再排一個(gè)頁(yè)，就不會(huì)又頁(yè)分裂的問(wèn)題了。所以自增主鍵對(duì)于InnoDB這種使用B+Tree索引的存儲(chǔ)引擎來(lái)說(shuō)，性能更好。

三、回表查詢

回表查詢就是在數(shù)據(jù)查詢過(guò)程中MySQL內(nèi)部需要兩次查詢。既先定位查詢數(shù)據(jù)所在表的主鍵值，在根據(jù)主鍵定位行記錄。

要弄清楚回表查詢，我們就要先從InnoDB的索引實(shí)現(xiàn)說(shuō)起，InnoDB索引分為兩大類：聚集索引（Clustered Index）和普通索引（Secondary Index）

3.1 InnoDB 聚集索引

聚集索引是索引結(jié)構(gòu)和數(shù)據(jù)一起存放的索引。主鍵索引為聚集索引。

InnoDB聚集索引的葉子節(jié)點(diǎn)存儲(chǔ)行記錄，因此InnoDB 必須要有且只有一個(gè)聚集索引。

1. 如果表定義了 PK （Primary Key，主鍵），那么 PK 就是聚集索引；
2. 如果表沒(méi)有定義 PK，則第一個(gè) NOT NULL UNIQUE 的列就是聚集索引。
3. 否則 InnoDB 會(huì)另外創(chuàng)建一個(gè)隱藏的 ROWID 作為聚集索引。

由于這種機(jī)制是直接定位行記錄，因此使得基于 PK 的查詢速度非常快。

3.2 InnoDB非聚集索引

非聚集索引是索引結(jié)構(gòu)和數(shù)據(jù)分開存在的索引。輔助索引就是非聚集索引。

非聚集索引的葉子節(jié)點(diǎn)不一定存儲(chǔ)的是數(shù)據(jù)的指針（輔助索引的葉子節(jié)點(diǎn)存儲(chǔ)的是就是主鍵，然后根據(jù)主鍵在回表查詢數(shù)據(jù)。）

3.3 InnoDB回表

回表查詢，就是先通過(guò)非聚集索引查詢到對(duì)應(yīng)的主鍵，在通過(guò)主鍵索引查詢到對(duì)應(yīng)的值。兩次經(jīng)過(guò)B+Tree索引。

四、覆蓋索引

如果執(zhí)行一個(gè)查詢語(yǔ)句不經(jīng)過(guò)兩次B+Tree查詢直接得到要查詢的值，這個(gè)時(shí)候就不需要回表，也就是說(shuō)在這個(gè)查詢中，索引"覆蓋了"查詢，這個(gè)稱為覆蓋索引。

由于覆蓋索引減少B+Tree是搜索次數(shù)，提高查詢性能，所以使用覆蓋索引是一個(gè)常用的索引手段。使用覆蓋索引最常見的方法是創(chuàng)建聯(lián)合索引，將需要查詢的字段都放在聯(lián)合索引上。

用explain sql，如果Extra中有using index，則證明使用到了覆蓋索引。

五、最左前綴原則

最左前綴就是利用索引來(lái)加速檢索，最左前綴可以是聯(lián)合索引的最左N個(gè)字段，也可以是字符串索引的最左M個(gè)字符，就是說(shuō)你要查詢N個(gè)字段就包含在某個(gè)聯(lián)合索引的最左N個(gè)字段內(nèi)，簡(jiǎn)單說(shuō)，也就是索引字段的數(shù)據(jù)必須是有序的，才能實(shí)現(xiàn)這種類型的查找，才能利用到索引。

最左前綴原則總結(jié)

1. 假設(shè)有三個(gè)字段(col1, col2, col3)，MySQL可以支持(col1), (col1, col2), 和(col1, col2, col3)的聯(lián)合索引。
2. 比較有爭(zhēng)議的(col1, col3) 是否支持聯(lián)合索引，官方給的文檔中是支持的，我們?cè)囼?yàn)也是支持。
3. where子句幾個(gè)搜索條件順序調(diào)換不影響查詢結(jié)果，因?yàn)镸ysql中有查詢優(yōu)化器，會(huì)自動(dòng)優(yōu)化查詢順序。
4. where子句，若遇到范圍查詢（> < between, like）或未在總結(jié)1中創(chuàng)建的索引對(duì)時(shí)，就會(huì)停止匹配（遇到的范圍查詢還是參與索引）。

六、索引失效

建好索引后，但是一些不好的SQL會(huì)導(dǎo)致索引失效，有一下幾種場(chǎng)景會(huì)導(dǎo)致失效。

1. 查詢條件中有OR，即使有部分條件帶索引也會(huì)失效；
2. LIKE查詢時(shí)已%開頭；
3. 如果列類型是字符串，那在查詢條件中需要將數(shù)據(jù)用引號(hào)引用起來(lái)，否則不走索引；
4. 索引列上參與計(jì)算會(huì)導(dǎo)致索引失效；
5. 違背最左匹配原則；
6. 如果Mysql估計(jì)全表掃描要比使用索引要快，會(huì)不適用索引
7. B-tree索引 is null不會(huì)走,is not null會(huì)走,位圖索引 is null,is not null 都會(huì)走；
8. 聯(lián)合索引 is not null 只要在建立的索引列（不分先后）都會(huì)走, in null時(shí) 必須要和建立索引第一列一起使用,當(dāng)建立索引第一位置條件是is null 時(shí),其他建立索引的列可以是is null（但必須在所有列 都滿足is null的時(shí)候）,或者=一個(gè)值； 當(dāng)建立索引的第一位置是=一個(gè)值時(shí),其他索引列可以是任何情況（包括is null =一個(gè)值）,以上兩種情況索引都會(huì)走。其他情況不會(huì)走

七、索引下推

索引下推（index condition pushdown ）簡(jiǎn)稱ICP，在Mysql5.6以后的版本上推出，用于優(yōu)化回表查詢；在不使用ICP的情況下，在使用非主鍵索引（又叫普通索引或者二級(jí)索引）進(jìn)行查詢時(shí)，存儲(chǔ)引擎通過(guò)索引檢索到數(shù)據(jù)，然后返回給MySQL服務(wù)器，服務(wù)器然后判斷數(shù)據(jù)是否符合條件 ；在使用ICP的情況下，如果存在某些被索引的列的判斷條件時(shí)，MySQL服務(wù)器將這一部分判斷條件傳遞給存儲(chǔ)引擎，
然后由存儲(chǔ)引擎通過(guò)判斷索引是否符合MySQL服務(wù)器傳遞的條件，只有當(dāng)索引符合條件時(shí)才會(huì)將數(shù)據(jù)檢索出來(lái)返回給MySQL服務(wù)器 ；文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-660897.html

• 查看索引下推的狀態(tài)

show VARIABLES like '%optimizer_switch%';
-------------------------------------------------------
optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on

• 關(guān)閉索引下推

#索引下推是mysql 5.6優(yōu)化查詢回表的功能，在5.6之前都不支持索引下推
set optimizer_switch='index_condition_pushdown=off';

• 開啟索引下推

set optimizer_switch='index_condition_pushdown=on';

• 總結(jié)
  1. 索引下推功能是mysql 5.6推出優(yōu)化回表的操作，只支持向上兼容，低版本是不支持的；
  2. 索引下推優(yōu)化的只是回表次數(shù)，掃描行數(shù)還是一樣的。

到了這里，關(guān)于【MySQL系列】-回表、覆蓋索引真的懂嗎的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！