1. 什么是最左前綴法則

最左前綴是在使用innodb存儲(chǔ)引擎索引時(shí)，需要遵守的法則。在一個(gè)聯(lián)合索引如idx(a, b, c)，執(zhí)行查詢SQL時(shí)，如果查詢條件包含索引的最左前綴，那么可以使用聯(lián)合索引加速查詢。對(duì)于上述例子，最左前綴包括idx(a), idx(a, b), idx(a, b, c)

2. 為什么存在最左前綴

最左前綴涉及到聯(lián)合索引如何構(gòu)建這一問(wèn)題。

我們構(gòu)建如下圖所示的數(shù)據(jù)庫(kù)表。

我們構(gòu)建a, b, c字段的聯(lián)合索引，idx(a, b, c)。

索引構(gòu)建的規(guī)則很簡(jiǎn)單，先按照a排序，如果a一致，則按照b排序。如果b一致，按照c排序。如果所有索引字段都一致，那么按照主鍵排序。

有上述規(guī)則，我們可以得到如下索引圖

通過(guò)索引圖，我們觀察發(fā)現(xiàn)如下結(jié)論

• 在全局范圍內(nèi)，a字段（紅框標(biāo)記）全局有序
• 在全局范圍內(nèi)，b字段（橘色框標(biāo)記）全局無(wú)序，但局部有序（從左往右數(shù)的第一個(gè)數(shù)據(jù)頁(yè)）
• 在全局范圍內(nèi)，c字段（綠框標(biāo)記）全局無(wú)序

由索引的構(gòu)建規(guī)則，我們可以提煉非常關(guān)鍵的一條信息：右側(cè)字段對(duì)應(yīng)數(shù)據(jù)有序的前提是，左側(cè)字段數(shù)據(jù)確定。

我們?nèi)稳灰运饕龍D為例。b字段在全局的視角來(lái)看是沒(méi)有順序的。只有a字段確定下來(lái)，b字段才能有順序。當(dāng)a = Bill時(shí)，b字段對(duì)應(yīng)的數(shù)據(jù)則呈現(xiàn)升序狀態(tài)。同理，c字段要想有序，b字段必須確定下來(lái)

這就是為什么要遵守索引前綴法則。其核心原因就是聯(lián)合索引創(chuàng)建時(shí)，需要優(yōu)先滿足左側(cè)字段的有序性，然后才會(huì)考慮右側(cè)字段

3. 索引失效情況

知道了為什么存在最左前綴法則，我們來(lái)分析一下什么時(shí)候聯(lián)合索引會(huì)失效。

3.1 查詢條件未添加最左側(cè)列，索引失效

我們以dish_flavor數(shù)據(jù)表為例進(jìn)行分析

tip：數(shù)據(jù)庫(kù)的表最好不要太簡(jiǎn)單，數(shù)據(jù)不要太少。否則優(yōu)化器可能并不會(huì)走索引，因?yàn)樵跀?shù)據(jù)量小的情況下，可能全表掃描效率更高

我們以dish_id，name，value為字段，創(chuàng)建聯(lián)合索引

CREATE INDEX idx_dishid_name_value ON dish_flavor(dish_id, name, value);

我們分別執(zhí)行以下SQL，看看索引情況

EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 AND name = '中餐';

走了索引

EXPLAIN SELECT * FROM dish_flavor WHERE name = '中餐' AND value = '[]';

沒(méi)走索引

EXPLAIN SELECT * FROM dish_flavor WHERE value = '["不辣"]';

沒(méi)走索引

EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 AND value = '["不辣"]';

走了索引

以上四種情況只有第一條、第四條SQL走了索引。而這兩條SQL的共同點(diǎn)就是：都包含了dish_id這個(gè)最左側(cè)的字段。因此，想要索引生效，必須包含最左側(cè)的字段

當(dāng)然了，第一句SQL和第四句SQL也是存在區(qū)別的。我們建立的索引是idx(dish_id, name, value)，dish_id緊鄰的是name。因此第一句SQL索引全部生效EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 AND name = '中餐';

但第二句SQL，EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 AND value = '["不辣"]';缺少了name這個(gè)字段，而value字段必須要name字段確定才能夠有序，因此當(dāng)前SQL會(huì)走idx_dishid_name_value索引，但僅僅到dish_id這個(gè)字段為止。

我們可以觀察explain輸出表的ref列

第一句SQL兩個(gè)篩選條件都用于和索引進(jìn)行比較

第二句SQL只有第一個(gè)篩選條件用于和索引進(jìn)行比較

tip: explain輸出的表格，ref列表示的意思是，篩選條件是否和索引進(jìn)行比較。下方是筆者從官方文檔中摘錄的信息

The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table

3.2 使用OR，索引失效

執(zhí)行這條SQL：EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 OR name = '中餐';，索引失效

想要解釋OR關(guān)鍵字為何會(huì)導(dǎo)致失效，其實(shí)很簡(jiǎn)單。

OR在結(jié)果上可以等價(jià)于當(dāng)個(gè)SQL得到集合的并集，具體來(lái)說(shuō)

SELECT * FROM dish_flavor WHERE dish_id = 1 OR name = '中餐'

=>

SELECT * FROM dish_flavor WHERE dish_id = 1 并上 SELECT * FROM dish_flavor WHERE name = '中餐'

對(duì)于拆解成的兩句SQL，前者可以走聯(lián)合索引，后者不能走（因?yàn)樽钭笄熬Y法則）。事實(shí)上，MySQL可不會(huì)真這么拆解SQL，那對(duì)于MySQL來(lái)說(shuō)，OR的兩個(gè)篩選條件一個(gè)能走idx，一個(gè)不能。這到底是能還是不能，干脆就不走聯(lián)合索引。

即使走了聯(lián)合索引，也只能對(duì)dish_id做篩選，而存在的name字段必須要全表掃描，因?yàn)閐ish_id沒(méi)法被確定，因此他全局無(wú)序。既然如此，那為什么不直接全表掃描呢？

因此，OR關(guān)鍵字會(huì)導(dǎo)致索引失效?？偨Y(jié)一下：OR關(guān)聯(lián)的兩個(gè)查詢條件，必然存在一個(gè)條件無(wú)法滿足最左前綴法則，走不了索引。對(duì)于那個(gè)走不了索引的查詢條件，在不存在其它索引的前提下，必然需要全表掃描。因此OR關(guān)鍵字會(huì)破壞聯(lián)合索引，導(dǎo)致索引失效

tip: 當(dāng)前數(shù)據(jù)表中，只存在idx(dish_id, name, value)

現(xiàn)在，我們做些額外操作，在OR的查詢條件下，讓MySQL依然走索引。

我們?yōu)?code>name字段單獨(dú)創(chuàng)建索引CREATE INDEX idx_name ON dish_flavor(name);

現(xiàn)在我們?cè)趫?zhí)行含有OR的SQL，EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 OR name = '中餐';

我們發(fā)現(xiàn)，MySQL不僅走了聯(lián)合索引，還走了idx_name索引。聯(lián)合索引解決dish_id = 1這個(gè)條件，idx_name索引解決name = '中餐'這個(gè)條件，非常完美

tip: explain輸出的type字段，內(nèi)容為index_merge。這表明SQL走了多個(gè)索引

為了下文實(shí)驗(yàn)不被多余索引干擾，我們刪除idx_name索引

3.3 函數(shù)運(yùn)算，索引失效

我們執(zhí)行SQLEXPLAIN SELECT * FROM dish_flavor WHERE dish_id + 1 = 1;，會(huì)發(fā)現(xiàn)索引失效

索引失效

模糊匹配中

索引未失效

模糊匹配尾

索引未失效

筆者嘗試解釋上述現(xiàn)象

首先，模糊匹配本質(zhì)就是范圍查找。既然是范圍，那必然存在兩端。我們查找范圍的思路可以大致框定，對(duì)于有序的數(shù)據(jù)，我們可以通過(guò)索引確定端點(diǎn)，端點(diǎn)間的數(shù)據(jù)就是我們模糊匹配的內(nèi)容。

首模糊

對(duì)于首模糊情況，他的起始端點(diǎn)無(wú)法走索引確定，因?yàn)槠鹗级它c(diǎn)可以是任何數(shù)據(jù)。

同樣的，他的結(jié)束端點(diǎn)也無(wú)法走索引確定，因?yàn)樽址侄螛?gòu)建索引，依然遵循另一個(gè)層面的最左前綴法則，字符串比較就是從左到右一次比較。如果左側(cè)字符串無(wú)法確定，右側(cè)字符串就無(wú)法保證有序性，這種情況也可以歸類為全局無(wú)序，局部有序。

中間模糊

對(duì)于中間模糊情況，他的起始端點(diǎn)可以走索引確定。但他的尾端點(diǎn)無(wú)法確定，但這足夠了。因?yàn)橛幸徊糠謨?nèi)容可以走索引，剩下內(nèi)容掃描整個(gè)索引即可

尾模糊

起始端點(diǎn)可以走索引確定，尾端點(diǎn)不需要確定，因?yàn)樗梢詿o(wú)限匹配后續(xù)內(nèi)容

一句話總結(jié)，模糊查詢依然遵循另一個(gè)維度的最左前綴法則，它依賴于字符串索引創(chuàng)建的規(guī)則。優(yōu)先匹配左側(cè)字符串，右側(cè)字符串確定順序的前提是左側(cè)字符串已確定。

到此這篇關(guān)于mysql最左前綴法則導(dǎo)致索引失效的解決的文章就介紹到這了,更多相關(guān)mysql最左前綴法內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論