1. 什么是最左前綴法則

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

2. 為什么存在最左前綴

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

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

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

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

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

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

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

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

我們任然以索引圖為例。b字段在全局的視角來看是沒有順序的。只有a字段確定下來，b字段才能有順序。當a = Bill時，b字段對應的數(shù)據(jù)則呈現(xiàn)升序狀態(tài)。同理，c字段要想有序，b字段必須確定下來

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

3. 索引失效情況

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

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

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

tip：數(shù)據(jù)庫的表最好不要太簡單，數(shù)據(jù)不要太少。否則優(yōu)化器可能并不會走索引，因為在數(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 = '[]';

沒走索引

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

沒走索引

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

走了索引

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

當然了，第一句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這個字段，而value字段必須要name字段確定才能夠有序，因此當前SQL會走idx_dishid_name_value索引，但僅僅到dish_id這個字段為止。

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

第一句SQL兩個篩選條件都用于和索引進行比較

第二句SQL只有第一個篩選條件用于和索引進行比較

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

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關鍵字為何會導致失效，其實很簡單。

OR在結果上可以等價于當個SQL得到集合的并集，具體來說

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

=>

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

對于拆解成的兩句SQL，前者可以走聯(lián)合索引，后者不能走（因為最左前綴法則）。事實上，MySQL可不會真這么拆解SQL，那對于MySQL來說，OR的兩個篩選條件一個能走idx，一個不能。這到底是能還是不能，干脆就不走聯(lián)合索引。

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

因此，OR關鍵字會導致索引失效。總結一下：OR關聯(lián)的兩個查詢條件，必然存在一個條件無法滿足最左前綴法則，走不了索引。對于那個走不了索引的查詢條件，在不存在其它索引的前提下，必然需要全表掃描。因此OR關鍵字會破壞聯(lián)合索引，導致索引失效

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

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

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

現(xiàn)在我們在執(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這個條件，idx_name索引解決name = '中餐'這個條件，非常完美

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

為了下文實驗不被多余索引干擾，我們刪除idx_name索引

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

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

索引失效

模糊匹配中

索引未失效

模糊匹配尾

索引未失效

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

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

首模糊

對于首模糊情況，他的起始端點無法走索引確定，因為起始端點可以是任何數(shù)據(jù)。

同樣的，他的結束端點也無法走索引確定，因為字符串字段構建索引，依然遵循另一個層面的最左前綴法則，字符串比較就是從左到右一次比較。如果左側字符串無法確定，右側字符串就無法保證有序性，這種情況也可以歸類為全局無序，局部有序。

中間模糊

對于中間模糊情況，他的起始端點可以走索引確定。但他的尾端點無法確定，但這足夠了。因為有一部分內容可以走索引，剩下內容掃描整個索引即可

尾模糊

起始端點可以走索引確定，尾端點不需要確定，因為它可以無限匹配后續(xù)內容

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

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

最新評論