前言

在工作中，為了提高查詢速度，我們通常會考慮為字段建立索引。然而，索引并不是萬無一失的。即使建立了索引，并不意味著所有查詢語句都能利用索引進行掃描。索引失效的原因如下

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

MySQL 的默認存儲引擎為 InnoDB，它使用 B+ 樹作為索引的數(shù)據(jù)結(jié)構(gòu)。選擇 B+ 樹作為索引結(jié)構(gòu)的原因在于其高效的查找和插入性能。在創(chuàng)建表時，InnoDB 存儲引擎會默認創(chuàng)建主鍵索引，即聚簇索引，而其他的索引則為二級索引。與 InnoDB 不同，MVISAM 存儲引擎支持多種索引數(shù)據(jù)結(jié)構(gòu)，包括 B+ 樹索引、R 樹索引和全文索引。在 MyISAM 中，創(chuàng)建表時默認使用 B+ 樹作為主鍵索引。盡管 InnoDB 和 MyISAM 都支持 B+ 樹索引，但它們的數(shù)據(jù)存儲結(jié)構(gòu)實現(xiàn)不同，InnoDB:B+樹索引的葉子節(jié)點直接保存數(shù)據(jù)本身；MyISAM:B+樹索引的葉子節(jié)點保存數(shù)據(jù)的物理地址。

1.1 MyISAM

1.2 InnoDB

2.1 普通索引

如果將 name 字段設(shè)置為普通索引，那么這個二級索引長下圖這樣

二、索引實現(xiàn)邏輯

2.1 主鍵索引查詢

當我們使用主鍵索引字段進行查詢時，如果數(shù)據(jù)在聚簇索引的葉子節(jié)點上，查詢則會在B+樹中直接找到對應(yīng)的節(jié)點并讀取數(shù)據(jù)。例如:

// id字段為主鍵索引
SELECT *FROM t user WHERE id=1;

2.2 二級索引查詢

使用二級索引字段查詢時，若數(shù)據(jù)在聚簇索引的葉子節(jié)點上，查詢需要檢索兩個B+樹:

1.首先在二級索引的B+樹中找到葉子節(jié)點并獲取主鍵值;

2.然后使用主鍵值在聚簇索引的B+樹中找到對應(yīng)節(jié)點，讀取數(shù)據(jù)。這個過程稱為“回表”，例如:

// name字段為二級索引
SELECT id FROM t user WHERE name="林某”;

三、索引失效

3.1 對索引使用左或者左右模糊匹配

在使用 LIKE 關(guān)鍵字進行模糊匹配時，如 LIKE'%xx'或 LIKE'%xx%'都會導(dǎo)致索引失效，從而引發(fā)全表掃描。例如，以下查詢用于查找名稱以「林」結(jié)尾的用戶:

在這個查詢的執(zhí)行計劃中， type=ALL 表示全表掃描，表明末使用索引。相反，如果查詢名稱以「林」開頭的用戶:

執(zhí)行計劃中的 type=range 表示使用了索引，key=index name 則確認了實際使用了索引。

為什么使用 LIKE 進行左模糊匹配會導(dǎo)致無法使用索引呢?

這是因為B+樹索引是根據(jù)索引值有序存儲的，僅能支持前綴比較。

舉個例子，假設(shè)我們查詢以「林」為前綴的名稱。查詢過程如下:

a.比較首節(jié)點的索引值:若「林」的拼音在某些節(jié)點中 介于「陳」和「周」之間，則繼續(xù)査找下一個節(jié)點。

b.依此類推，直到找到符合前綴的葉子節(jié)點，并讀取相應(yīng)數(shù)據(jù)。

如果查詢條件是 LIKE'%林·，因無法確定從哪個索引值開始比較，導(dǎo)致必須進行全表掃描。

3.2.對索引使用函數(shù)

在使用MySQL時，我們常常依賴內(nèi)置函數(shù)來獲取所需的查詢結(jié)果然而，需要特別注意的是，如果在查詢條件中對索引字段使用了函數(shù)，這通常會導(dǎo)致索引失效，從而導(dǎo)致全表掃描,例如，以下查詢語句中，length(name)函數(shù)被應(yīng)用于 name 字段。

關(guān)鍵在于，索引存儲的是字段的原始值，而不是經(jīng)過函數(shù)處理后的計算結(jié)果，這使得數(shù)據(jù)庫無法有效利用索引。

不過從MySQL8.0版本開始，數(shù)據(jù)庫引入了函數(shù)索引這一特性，允許我們針對函數(shù)計算結(jié)果建立索引。也就是說，索引的值可以是某個函數(shù)計算后的結(jié)果，這樣就可以通過索引來快速查詢所需數(shù)據(jù)。

例如，我們可以使用以下語句創(chuàng)建一個名為 idx_name length 的索引，該索引對應(yīng)于 length(name)的計算結(jié)果:

ALTER TABLE t user ADD KEY idx name length((LENGTH(name)));

在這種情況下，當我們使用類似的查詢時，數(shù)據(jù)庫將能夠利用索引來加速查詢過程。

3.3.對索引進行表達式計算

在查詢條件中進行表達式計算通常會導(dǎo)致無法使用索引。舉個例子，以下查詢語句在執(zhí)行計劃中顯示為類型ALL，表明使用了全表掃描:

然而，如果將查詢條件修改為 WHERE id=10-1，則可以利用索引進行査詢。

為什么表達式計算會使索引失效呢?

原因與對索引使用函數(shù)類似，索引中保存的是字段的原始值，而不是經(jīng)過計算的結(jié)果，因此數(shù)據(jù)庫必須先取出字段所有的值，然后逐個進行計算，從而導(dǎo)致全表掃描。

2.4.對索引隱式類型轉(zhuǎn)換

如果索引字段是字符串類型，但是在條件查詢中，輸入的參數(shù)是整型的話，查看執(zhí)行計劃結(jié)果可以發(fā)現(xiàn)這條語句會走全表掃描。
在人員表中，我增加了一個 phone 字段，該字段為二級索引，類型為 VARCHAR。

在進行條件查詢時，如果使用整型作為輸入?yún)?shù)，如執(zhí)行以下SQL:

然而，如果索引字段是整型，即使查詢條件中使用字符串類型的參數(shù)，索引依然能夠正常生效。例如，執(zhí)行以下SQL:

在這種情況下，查詢?nèi)匀粫咚饕龗呙琛?/p>

這引發(fā)了一個問題:為何第一個例子導(dǎo)致索引失效，而第二個例子卻沒有?

首先需要掌握MSQL的數(shù)據(jù)類型轉(zhuǎn)換規(guī)則，它決定了在比較字符串和數(shù)字時，哪個會被轉(zhuǎn)換。用一個簡單的方法來測試這一規(guī)則:選擇SELECT"10">9

如果MVSQL會自動將字符串轉(zhuǎn)換為數(shù)字，那么這相當于執(zhí)行 SELECT 10>9，結(jié)果應(yīng)該是1，因為數(shù)字10確實大于9。如果MVSOL會將數(shù)字轉(zhuǎn)換為字符串，那么這相當于執(zhí)行 SELECT"1”>"9”。在這種情況下，字符串比較是逐位進行的，按照ASCI碼進行比較。首先比較字符“1”和“9”，由于“1”的ASCII碼小于“9”，所以結(jié)果應(yīng)該是0。

根據(jù)測試結(jié)果，可以知道MySQL在比較時，會將字符串轉(zhuǎn)換為數(shù)字。

因此第二個查詢可以走索引，就是查不到期望正確的值。

2.5.聯(lián)合索引非最左匹配

在數(shù)據(jù)庫索引中，對主鍵字段建立的索引稱為聚簇索引，而對普通字段建立的索引則稱為二級索引。

當多個普通字段組合在一起創(chuàng)建索引時，稱為聯(lián)合索引(或組合索引)。

創(chuàng)建聯(lián)合索引時，字段的順序至關(guān)重要。例如，聯(lián)合索引(a,b,c)與(c,b,a)在使用時會有顯著不同。為了有效利用聯(lián)合索引，必須遵循最左匹配原則，即查詢條件必須從最左邊的字段開始匹配。

例如，對于(a,b,c)的聯(lián)合系引，以下查詢能夠成功匹配聯(lián)合系引:

• WHERE a=3。
• WHERE a=3 AND b=5 AND C=4
• WHERE a=3 AND b=5

然而，若查詢條件不符合最左匹配原則，索引將失效，如以下查詢

• WHERE b=5
• WHERE C=4
• WHERE b=5 AND C=4

還有一種特殊情況是 WHERE a=3 AND c=4。特殊之處在于不同版本的 MySQL，處理方法有所不同。

• 在 MySQL5.5 中，雖然會使用索引定位到主鍵，但仍需回表讀取數(shù)據(jù)。
• 而自 MVSQL 5.6 以來，引入的索引下推功能使得條件判斷在存儲引擎層進行，從而減少了回表次數(shù)，提高了性能。

例如，通過執(zhí)行計劃中的 Extra=Using index condition 可以確認索引下推的使用。

為什么聯(lián)合索引不遵循最左匹配原則就會失效?

聯(lián)合索引內(nèi)部的排序是基于第一列。只有當?shù)谝涣械臄?shù)據(jù)相同，才會依據(jù)第二列排序。因此，若希望利用聯(lián)合索引中盡可能多的列，查詢條件中的各列必須從最左側(cè)開始連續(xù)匹配。

2.6.WHERE 子句中的 OR

如果在 WHERE 子句中，0R 前的條件列是索引列，而 0R 后的條件列不是索引列，將會面臨全表掃描的問題。舉個例子，考慮以下查詢語句:

//id 是主鍵且為索引列，age 是普通列
SELECT*FRoM tuser WHERE id=1OR age = 18;

執(zhí)行計劃顯示，該查詢會執(zhí)行全表掃描，因為0R的邏輯是，只需滿足任一條件即可。

因此，當有一個條件不使用索引時，索引的存在毫無意義。

解決這個問題很簡單:將 age 字段設(shè)置為索引。這樣，查詢將會充分利用索引，避免全表掃描。

ALTER TABLE t user ADD INDEX idx age(age);

優(yōu)化后，如果執(zhí)行計劃變?yōu)?ldquo;type=index merge“，則意味著數(shù)據(jù)庫分別對 id 和 age 條件進行了索引掃描，并將結(jié)果合并，從而提高了查詢效率。

通過這種方式，可以顯著提升查詢性能，減少數(shù)據(jù)庫的負載。

總結(jié)

到此這篇關(guān)于MySQL索引失效的原因及實現(xiàn)邏輯的文章就介紹到這了,更多相關(guān)MySQL索引失效內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！