一、前言

在對SQL語句進(jìn)行索引查詢時會遇到索引失效的時候，對于該語句的可行性以及性能效率方面有至關(guān)重要的影響，本篇剖析索引為何失效，有哪些情況會導(dǎo)致索引失效以及對于索引失效時的優(yōu)化解決方案，其中著重介紹最左前綴匹配原則、MySQL邏輯架構(gòu)和優(yōu)化器、索引失效場景以及為何會失效。

二、最左前綴匹配原則

之前有寫了一篇關(guān)于MySQL添加索引特點(diǎn)及優(yōu)化問題方面的文章，下面將介紹索引失效的相關(guān)內(nèi)容。

首先引入在之后的索引失效原因中會使用到的一個原則：最左前綴匹配原則。

最左前綴底層原理：在MySQL建立聯(lián)合索引時會遵守最左前綴匹配原則，即最左優(yōu)先，在檢索數(shù)據(jù)時從聯(lián)合索引的最左邊開始匹配。

什么是最左前綴匹配原則呢？要想理解聯(lián)合索引的最左匹配原則，先來理解下索引的底層原理：索引的底層是一顆B+樹，那么聯(lián)合索引的底層也就是一顆B+樹，只不過聯(lián)合索引的B+樹節(jié)點(diǎn)中存儲的是鍵值。由于構(gòu)建一棵B+樹只能根據(jù)一個值來確定索引關(guān)系，所以數(shù)據(jù)庫依賴聯(lián)合索引最左的字段來構(gòu)建。

舉例：創(chuàng)建一個（a,b）的聯(lián)合索引，那么它的索引樹就是下圖的樣子。

可以看到a的值是有順序的，1，1，2，2，3，3，而b的值是沒有順序的1，2，1，4，1，2。但是我們又可發(fā)現(xiàn)a在等值的情況下，b值又是按順序排列的，但是這種順序是相對的。這是因?yàn)镸ySQL創(chuàng)建聯(lián)合索引的規(guī)則是首先會對聯(lián)合索引的最左邊第一個字段排序，在第一個字段的排序基礎(chǔ)上，然后在對第二個字段進(jìn)行排序。所以b=2這種查詢條件沒有辦法利用索引。

由于整個過程是基于explain結(jié)果分析的，那接下來在了解下explain中的type字段和key_lef字段。

1.type：聯(lián)接類型。

• system：表只有一行記錄（等于系統(tǒng)表），這是const類型的特例，平時不會出現(xiàn)，可以忽略不計(jì)
• const：表示通過索引一次就找到了，const用于比較primary key 或者 unique索引。因?yàn)橹恍杵ヅ湟恍袛?shù)據(jù)，所有很快。如果將主鍵置于where列表中，mysql就能將該查詢轉(zhuǎn)換為一個const。
• eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵 或 唯一索引掃描。注意：ALL全表掃描的表記錄最少的表如t1表ref：非唯一性索引掃描，返回匹配某個單獨(dú)值的所有行。本質(zhì)是也是一種索引訪問，它返回所有匹配某個單獨(dú)值的行，然而他可能會找到多個符合條件的行，所以它應(yīng)該屬于查找和掃描的混合體。
• range：只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了那個索引。一般就是在where語句中出現(xiàn)了bettween、<、>、in等的查詢。這種索引列上的范圍掃描比全索引掃描要好。只需要開始于某個點(diǎn)，結(jié)束于另一個點(diǎn)，不用掃描全部索引。
• index：Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹。這通常為ALL塊，應(yīng)為索引文件通常比數(shù)據(jù)文件小。（Index與ALL雖然都是讀全表，但index是從索引中讀取，而ALL是從硬盤讀取）
• ALL：Full Table Scan，遍歷全表以找到匹配的行

2.key_len：顯示MySQL實(shí)際決定使用的索引的長度。如果索引是NULL，則長度為NULL。如果不是NULL，則為使用的索引的長度。所以通過此字段就可推斷出使用了那個索引。

計(jì)算規(guī)則：

• 1.定長字段，int占用4個字節(jié)，date占用3個字節(jié)，char(n)占用n個字符。
• 2.變長字段varchar(n)，則占用n個字符+兩個字節(jié)。
• 3.不同的字符集，一個字符占用的字節(jié)數(shù)是不同的。Latin1編碼的，一個字符占用一個字節(jié)，gdk編碼的，一個字符占用兩個字節(jié)，utf-8編碼的，一個字符占用三個字節(jié)。

（由于我數(shù)據(jù)庫使用的是Latin1編碼的格式，所以在后面的計(jì)算中，一個字符按一個字節(jié)算）

• 4.對于所有的索引字段，如果設(shè)置為NULL，則還需要1個字節(jié)。

了解了最左前綴匹配原則后我們來看看索引失效的場景以及剖析為何會失效。

三、MySQL邏輯架構(gòu)和優(yōu)化器

MySQL邏輯架構(gòu)：

mysql架構(gòu)可分為大概的4層，分別是：

1. 1.客戶端：各種語言都提供了連接mysql數(shù)據(jù)庫的方法，比如jdbc、php、go等，可根據(jù)選擇 的后端開發(fā)語言選擇相應(yīng)的方法或框架連接mysql
2. 2.server層：包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，涵蓋mysql的大多數(shù)核心服務(wù)功能，以及所有的內(nèi)置函數(shù)（例如日期、世家、數(shù) 學(xué)和加密函數(shù)等），所有跨存儲引擎的功能都在這一層實(shí)現(xiàn)，比如存儲過程、觸發(fā)器、視圖等。
3. 3.存儲引擎層：負(fù)責(zé)數(shù)據(jù)的存儲和提取，是真正與底層物理文件打交道的組件。 數(shù)據(jù)本質(zhì)是存儲在磁盤上的，通過特定的存儲引擎對數(shù)據(jù)進(jìn)行有組織的存放并根據(jù)業(yè)務(wù)需要對數(shù)據(jù)進(jìn)行提取。存儲引擎的架構(gòu)模式是插件式的，支持Innodb，MyIASM、Memory等多個存儲引擎?，F(xiàn)在最常用的存儲引擎是Innodb，它從mysql5.5.5版本開始成為了默認(rèn)存儲引擎。
4. 4.物理文件層：存儲數(shù)據(jù)庫真正的表數(shù)據(jù)、日志等。物理文件包括：redolog、undolog、binlog、errorlog、querylog、slowlog、data、index等。

server層重要組件介紹：

1.連接器

連接器負(fù)責(zé)來自客戶端的連接、獲取用戶權(quán)限、維持和管理連接。

一個用戶成功建立連接后，即使你用管理員賬號對這個用戶的權(quán)限做了修改，也不會影響已經(jīng)存在連接的權(quán)限。修改完成后，只有再新建連接才會使用新的權(quán)限設(shè)置。

2.查詢緩存

mysql拿到一個查詢請求后，會先到查詢緩存查看之前是否執(zhí)行過這條語句。前執(zhí)行過的語句及其結(jié)果可能會以key-value對的形式，被直接緩存在內(nèi)存中。key是查詢的語句，value是查詢的結(jié)果。如果當(dāng)前sql查詢能夠直接在查詢緩存中找到key，那么這個value就會被直接返回給客戶端。

其實(shí)大多數(shù)情況下建議不要使用查詢緩存，為什么呢？因?yàn)椴樵兙彺嫱状笥诶２樵兙彺娣浅Ｈ菀资В灰獙σ粋€表進(jìn)行更新，與這個表相關(guān)的所有查詢緩存都會被清空。因此很可能費(fèi)勁把結(jié)果存起來后，還沒使用就被一個更新操作全清空了。對于更新操作多的數(shù)據(jù)庫來說，查詢緩存的命中率會非常低。除非業(yè)務(wù)需要的是一張靜態(tài)表，很長時間才會更新一次。比如，一個系統(tǒng)配置表，那么這張表的查詢才適合使用查詢緩存。

3.分析器

詞法分析（識別關(guān)鍵字，操作，表名，列名）
語法分析 (判斷是否符合語法）

4.優(yōu)化器

優(yōu)化器是在表里面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關(guān)聯(lián)（join）的時候，決定各個表的連接順序。優(yōu)化器階段完成后，這個語句的執(zhí)行方案就確定下來了，然后進(jìn)入執(zhí)行器階段。

5.執(zhí)行器

開始執(zhí)行的時候，要先判斷一下用戶對這個表 T 有沒有執(zhí)行查詢的權(quán)限。如果沒有，就會返回沒有權(quán)限的錯誤。如果命中查詢緩存，會在查詢緩存返回結(jié)果的時候，做權(quán)限驗(yàn)證。查詢也會在優(yōu)化器之前調(diào)用 precheck 驗(yàn)證權(quán)限。如果有權(quán)限，就打開表繼續(xù)執(zhí)行。打開表的時候，執(zhí)行器就會根據(jù)表的引擎定義，去調(diào)用這個引擎提供的接口。在有些場景下，執(zhí)行器調(diào)用一次，在引擎內(nèi)部則掃描了多行，因此引擎掃描行數(shù)跟rows_examined并不是完全相同的。

MySQL優(yōu)化器：

MySQL 優(yōu)化器使用基于成本的優(yōu)化方式（Cost-based Optimization），以 SQL 語句作為輸入，利用內(nèi)置的成本模型和數(shù)據(jù)字典信息以及存儲引擎的統(tǒng)計(jì)信息決定使用哪些步驟實(shí)現(xiàn)查詢語句，也就是查詢計(jì)劃。

從高層次來說，MySQL Server 可以分為兩部分：服務(wù)器層以及存儲引擎層。其中，優(yōu)化器工作在服務(wù)器層，位于存儲引擎 API 之上。

優(yōu)化器的工作過程從語義上可以分為四個階段：

1.邏輯轉(zhuǎn)換，包括否定消除、等值傳遞和常量傳遞、常量表達(dá)式求值、外連接轉(zhuǎn)換為內(nèi)連接、子查詢轉(zhuǎn)換、視圖合并等；
2.優(yōu)化準(zhǔn)備，例如索引 ref 和 range 訪問方法分析、查詢條件扇出值（fan out，過濾后的記錄數(shù)）分析、常量表檢測；
3.基于成本優(yōu)化，包括訪問方法和連接順序的選擇等；
4.執(zhí)行計(jì)劃改進(jìn)，例如表?xiàng)l件下推、訪問方法調(diào)整、排序避免以及索引條件下推。

四、索引失效場景以及為何會失效

1.like以通配符%開頭索引失效。上面介紹了最左前綴匹配底層原理，我們知道了通常用的索引數(shù)據(jù)結(jié)構(gòu)是B+樹，而索引是有序排列的。如果索引關(guān)鍵字的類型是Int 類型，索引的排列順序如下：

數(shù)據(jù)只存放在葉子節(jié)點(diǎn)，而且是有序的排放。

如果索引關(guān)鍵字的類型是String類型，排列順序如下：

可以看出，索引的排列順序是根據(jù)比較字符串的首字母排序的。
我們在進(jìn)行模糊查詢的時候，如果把 % 放在了前面，最左的 n 個字母便是模糊不定的，無法根據(jù)索引的有序性準(zhǔn)確的定位到某一個索引，只能進(jìn)行全表掃描，找出符合條件的數(shù)據(jù)。(最左前綴底層原理)

在使用聯(lián)合索引時也是如此，如果違背了索引有序排列的規(guī)則，同樣會造成索引失效，進(jìn)行全表掃描。
例子:表example中有個組合索引為：（A,B,C）
SELECT * FROM example WHERE A=1 and B =1 and C=1; 可以走索引；
SELECT A FROM example WHERE C =1 and B=1 ORDER BY A; 可以走索引（使用了覆蓋索引）
SELECT * FROM example WHERE C =1 and B=1 ORDER BY A; 不可以走索引

覆蓋索引:索引包含所有滿足查詢需要的數(shù)據(jù)的索引，稱為覆蓋索引(Covering Index)

可以有兩種方式優(yōu)化：
一種是使用覆蓋索引，第二種是把%放后面。

2.字段類型是字符串，where時沒有用引號括起來。表中的字段為字符串類型，是B+樹的普通索引，如果查詢條件傳了一個數(shù)字過去，它是不走索引的。
例子:表example中有個字段為pid是varchar類型。

//此時執(zhí)行語句type為ALL全表查詢
explain SELECT * FROM example WHERE pid = 1

//此時執(zhí)行語句type為ref索引查詢
explain SELECT * FROM example WHERE pid = '1'

為什么第一條語句未加單引號就不走索引了呢？ 這是因?yàn)椴患訂我枙r，是字符串跟數(shù)字的比較，它們類型不匹配，MySQL會做隱式的類型轉(zhuǎn)換，把它們轉(zhuǎn)換為浮點(diǎn)數(shù)再做比較。

3.OR 前后只要存在非索引的列，都會導(dǎo)致索引失效。查詢條件包含or，就有可能導(dǎo)致索引失效。
例子:表example中有字段為pid是int類型,score是int類型。

//此時執(zhí)行語句type為ref索引查詢
explain SELECT * FROM example WHERE pid = 1

//把or條件加沒有索引的score，并不會走索引，為ALL全表查詢
explain SELECT * FROM example WHERE pid = 1 OR score = 10

這里對于OR后面加上沒有索引的score這種情況，假設(shè)它走了p_id的索引，但是走到score查詢條件時，它還得全表掃描，也就是需要三步過程： 全表掃描+索引掃描+合并。
mysql是有優(yōu)化器的，處于效率與成本，遇到OR條件，索引可能會失效也是合理的。

注意: 如果or條件的列都加了索引，索引可能會走的。

4.聯(lián)合索引(組合索引)，查詢時的條件列不是聯(lián)合索引中的第一個列，索引失效。在聯(lián)合索引中，查詢條件滿足最左匹配原則時，索引是正常生效的。
當(dāng)我們創(chuàng)建一個聯(lián)合索引的時候，如(k1,k2,k3)，相當(dāng)于創(chuàng)建了（k1）、(k1,k2)和(k1,k2,k3)三個索引，這就是最左匹配原則。
例子:有一個聯(lián)合索引idx_pid_score，pid在前，score在后。

//此時執(zhí)行語句type為ref索引查詢,idx_pid_score索引
explain SELECT * FROM example WHERE pid = 1 OR score = 10

//此時執(zhí)行語句type為ref索引查詢,idx_pid_score索引
explain SELECT * FROM example WHERE pid = 1

//此時執(zhí)行語句type為ALL全表查詢
explain SELECT * FROM example WHERE score = 10

聯(lián)合索引不滿足最左原則，索引一般會失效，但是這個還跟Mysql優(yōu)化器有關(guān)。

5.計(jì)算、函數(shù)、類型轉(zhuǎn)換(自動或手動)導(dǎo)致索引失效，索引字段上使用（！= 或者 < >，not in）時，可能會導(dǎo)致索引失效。
birthtime加了索引，但是因?yàn)槭褂昧薽ysql的內(nèi)置函數(shù)Date_ADD()，也沒有走索引。
例子：在表example中有idx_birth_time索引為datetime類型的birthtime字段

//此時執(zhí)行語句type為ALL全表查詢
explain SELECT * FROM example WHERE Date_ADD(birthtime，INTERVAL 1 DAY) = 6

還有對索引列運(yùn)算（如，+、-、*、/），索引失效。
例子：在表example中有int類型的score字段索引idx_score

//此時執(zhí)行語句type為ALL全表查詢
explain SELECT * FROM example WHERE score-1=5

還有不等于(!= 或者<>)導(dǎo)致索引失效。
例子：在表example中有int類型的score字段索引idx_score

//此時執(zhí)行語句type為ALL全表查詢
explain SELECT * FROM example WHERE score != 2

//此時執(zhí)行語句type為ALL全表查詢
explain SELECT * FROM example WHERE score <> 3

雖然score 加了索引，但是使用了！= 或者 < >，not in這些時，索引如同虛設(shè)。
6. is null可以使用索引，is not null無法使用索引。
例子:在表example中有varchar類型的name字段索引idx_name，varchar類型的card字段索引idx_card。

//此時執(zhí)行語句type為range索引查詢
explain SELECT * FROM example WHERE name is not null

//此時執(zhí)行語句type為ALL全表查詢
explain SELECT * FROM example WHERE name is not null OR  card is not null

7.左連接查詢或者右連接查詢查詢關(guān)聯(lián)的字段編碼格式不一樣。兩張表相同字段外連接查詢時字段編碼格式不同則會不走索引查詢。
例子:在表example中有varchar類型的name字段編碼是utf8mb4,索引為idx_name
在表example_two中有varchar類型的name字段編碼為utf8,索引為idx_name。

//此時執(zhí)行語句example表會走type為index類型索引，example_two則為ALL全表搜索不走索引
explain SELECT e.name,et.name FROM example e LEFT JOIN example_two et on e.name = et.name

當(dāng)把兩表的字段類型改為一致時:

//此時執(zhí)行語句example表會走type為index類型索引，example_two會走type為ref類型索引
explain SELECT e.name,et.name FROM example e LEFT JOIN example_two et on e.name = et.name

所以字段類型也會導(dǎo)致索引失效
8.mysql估計(jì)使用全表掃描要比使用索引快,則不使用索引。當(dāng)表的索引被查詢，會使用最好的索引，除非優(yōu)化器使用全表掃描更有效。優(yōu)化器優(yōu)化成全表掃描取決與使用最好索引查出來的數(shù)據(jù)是否超過表的30%的數(shù)據(jù)。
建議:不要給’性別’等增加索引。如果某個數(shù)據(jù)列里包含了均是"0/1"或“Y/N”等值，即包含著許多重復(fù)的值，就算為它建立了索引，索引效果不會太好，還可能導(dǎo)致全表掃描。
Mysql出于效率與成本考慮，估算全表掃描與使用索引，哪個執(zhí)行快，這跟它的優(yōu)化器有關(guān)。

五、總結(jié)

以上列舉了mysql語句在執(zhí)行過程中會發(fā)生索引失效的場景，

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

最新評論