一、前言

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

二、最左前綴匹配原則

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

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

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

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

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

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

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

1.type：聯接類型。

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

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

計算規(guī)則：

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

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

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

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

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

MySQL邏輯架構：

mysql架構可分為大概的4層，分別是：

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

server層重要組件介紹：

1.連接器

連接器負責來自客戶端的連接、獲取用戶權限、維持和管理連接。

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

2.查詢緩存

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

其實大多數情況下建議不要使用查詢緩存，為什么呢？因為查詢緩存往往弊大于利。查詢緩存非常容易失效，只要對一個表進行更新，與這個表相關的所有查詢緩存都會被清空。因此很可能費勁把結果存起來后，還沒使用就被一個更新操作全清空了。對于更新操作多的數據庫來說，查詢緩存的命中率會非常低。除非業(yè)務需要的是一張靜態(tài)表，很長時間才會更新一次。比如，一個系統(tǒng)配置表，那么這張表的查詢才適合使用查詢緩存。

3.分析器

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

4.優(yōu)化器

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

5.執(zhí)行器

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

MySQL優(yōu)化器：

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

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

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

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

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

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

數據只存放在葉子節(jié)點，而且是有序的排放。

如果索引關鍵字的類型是String類型，排列順序如下：

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

在使用聯合索引時也是如此，如果違背了索引有序排列的規(guī)則，同樣會造成索引失效，進行全表掃描。
例子:表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; 不可以走索引

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

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

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

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

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

為什么第一條語句未加單引號就不走索引了呢？ 這是因為不加單引號時，是字符串跟數字的比較，它們類型不匹配，MySQL會做隱式的類型轉換，把它們轉換為浮點數再做比較。

3.OR 前后只要存在非索引的列，都會導致索引失效。查詢條件包含or，就有可能導致索引失效。
例子:表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這種情況，假設它走了p_id的索引，但是走到score查詢條件時，它還得全表掃描，也就是需要三步過程： 全表掃描+索引掃描+合并。
mysql是有優(yōu)化器的，處于效率與成本，遇到OR條件，索引可能會失效也是合理的。

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

4.聯合索引(組合索引)，查詢時的條件列不是聯合索引中的第一個列，索引失效。在聯合索引中，查詢條件滿足最左匹配原則時，索引是正常生效的。
當我們創(chuàng)建一個聯合索引的時候，如(k1,k2,k3)，相當于創(chuàng)建了（k1）、(k1,k2)和(k1,k2,k3)三個索引，這就是最左匹配原則。
例子:有一個聯合索引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

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

5.計算、函數、類型轉換(自動或手動)導致索引失效，索引字段上使用（！= 或者 < >，not in）時，可能會導致索引失效。
birthtime加了索引，但是因為使用了mysql的內置函數Date_ADD()，也沒有走索引。
例子：在表example中有idx_birth_time索引為datetime類型的birthtime字段

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

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

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

還有不等于(!= 或者<>)導致索引失效。
例子：在表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這些時，索引如同虛設。
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.左連接查詢或者右連接查詢查詢關聯的字段編碼格式不一樣。兩張表相同字段外連接查詢時字段編碼格式不同則會不走索引查詢。
例子:在表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

當把兩表的字段類型改為一致時:

//此時執(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

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

五、總結

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

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