前言

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

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

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

1.1 MyISAM

1.2 InnoDB

2.1 普通索引

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

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

2.1 主鍵索引查詢

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

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

2.2 二級(jí)索引查詢

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

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

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

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

三、索引失效

3.1 對(duì)索引使用左或者左右模糊匹配

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

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

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

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

這是因?yàn)锽+樹索引是根據(jù)索引值有序存儲(chǔ)的，僅能支持前綴比較。

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

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

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

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

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

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

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

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

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

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

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

3.3.對(duì)索引進(jìn)行表達(dá)式計(jì)算

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

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

為什么表達(dá)式計(jì)算會(huì)使索引失效呢?

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

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

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

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

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

在這種情況下，查詢?nèi)匀粫?huì)走索引掃描。

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

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

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

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

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

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

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

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

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

例如，對(duì)于(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 中，雖然會(huì)使用索引定位到主鍵，但仍需回表讀取數(shù)據(jù)。
• 而自 MVSQL 5.6 以來，引入的索引下推功能使得條件判斷在存儲(chǔ)引擎層進(jìn)行，從而減少了回表次數(shù)，提高了性能。

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

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

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

2.6.WHERE 子句中的 OR

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

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

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

因此，當(dāng)有一個(gè)條件不使用索引時(shí)，索引的存在毫無意義。

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

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

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

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

總結(jié)

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