MySQL索引失效場景及解決方案

一、前言

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

二、最左字首匹配原則

之前有寫了一篇關於MySQL新增索引特點及優化問題方面的文章，下面將介紹索引失效的相關內容。

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

最左字首底層原理：在MySQL建立聯合索引時會遵守最左字首匹配原則，即最左優先，在檢索資料時從聯合索引的最左邊開始匹配。

什麼是最左字首匹配原則呢？要想理解聯合索引的最左匹配原則，先來理解下索引的底層原理：索引的底層是一顆B+樹，那麼聯合索引的底層也就是一顆B+樹，只不過聯合索引的B+樹節點中儲存的是鍵值。由於構建一棵B+樹只能根據一個值來確定索引關係，所以資料庫依賴聯合索引最左的欄位來構建。

舉例：建立一個（a,b）的聯合索引，那麼它的索引樹就是下圖的樣子。

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

由於整個過程是基於explain結果分析的，那接下來在瞭解下explain中的type欄位和key_lef欄位。

1.type：聯接型別。

• system：表只有一行記錄（等於系統表），這是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區別為index型別只遍歷索引樹。這通常為ALL塊，應為索引檔案通常比資料檔案小。（Index與ALL雖然都是讀全表，但index是從索引中讀取，而ALL是從硬碟讀取）
• ALL：Full Table Scan，遍歷全表以找到匹配的行

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

計算規則：

• 1.定長欄位，int佔用4個位元組，date佔用3個位元組，char(n)佔用n個字元。
• 2.變長欄位varchar(n)，則佔用n個字元+兩個位元組。
• 3.不同的字元集，一個字元佔用的位元組數是不同的。Latin1編碼的，一個字元佔用一個位元組，gdk編碼的，一個字元佔用兩個位元組，utf-8編碼的，一個字元佔用三個位元組。

（由於我資料庫使用的是Latin1編碼的格式，所以在後面的計算中，一個字元按一個位元組算）

• 4.對於所有的索引欄位，如果設定為NULL，則還需要1個位元組。

瞭解了最左字首匹配原則後我們來看看索引失效的場景以及剖析為何會失效。

三、MySQL邏輯架構和優化器

MySQL邏輯架構：

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

1. 1.使用者端：各種語言都提供了連線mysql資料庫的方法，比如jdbc、php、go等，可根據選擇 的後端開發語言選擇相應的方法或框架連線mysql
2. 2.server層：包括聯結器、查詢快取、分析器、優化器、執行器等，涵蓋mysql的大多數核心服務功能，以及所有的內建函數（例如日期、世家、數 學和加密函數等），所有跨儲存引擎的功能都在這一層實現，比如儲存過程、觸發器、檢視等。
3. 3.儲存引擎層：負責資料的儲存和提取，是真正與底層物理檔案打交道的元件。 資料本質是儲存在磁碟上的，通過特定的儲存引擎對資料進行有組織的存放並根據業務需要對資料進行提取。儲存引擎的架構模式是外掛式的，支援Innodb，MyIASM、Memory等多個儲存引擎。現在最常用的儲存引擎是Innodb，它從mysql5.5.5版本開始成為了預設儲存引擎。
4. 4.物理檔案層：儲存資料庫真正的表資料、紀錄檔等。物理檔案包括：redolog、undolog、binlog、errorlog、querylog、slowlog、data、index等。

server層重要元件介紹：

1.聯結器

聯結器負責來自使用者端的連線、獲取使用者許可權、維持和管理連線。

一個使用者成功建立連線後，即使你用管理員賬號對這個使用者的許可權做了修改，也不會影響已經存在連線的許可權。修改完成後，只有再新建連線才會使用新的許可權設定。

2.查詢快取

mysql拿到一個查詢請求後，會先到查詢快取檢視之前是否執行過這條語句。前執行過的語句及其結果可能會以key-value對的形式，被直接快取在記憶體中。key是查詢的語句，value是查詢的結果。如果當前sql查詢能夠直接在查詢快取中找到key，那麼這個value就會被直接返回給使用者端。

其實大多數情況下建議不要使用查詢快取，為什麼呢？因為查詢快取往往弊大於利。查詢快取非常容易失效，只要對一個表進行更新，與這個表相關的所有查詢快取都會被清空。因此很可能費勁把結果存起來後，還沒使用就被一個更新操作全清空了。對於更新操作多的資料庫來說，查詢快取的命中率會非常低。除非業務需要的是一張靜態表，很長時間才會更新一次。比如，一個系統設定表，那麼這張表的查詢才適合使用查詢快取。

3.分析器

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

4.優化器

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

5.執行器

開始執行的時候，要先判斷一下使用者對這個表 T 有沒有執行查詢的許可權。如果沒有，就會返回沒有許可權的錯誤。如果命中查詢快取，會在查詢快取返回結果的時候，做許可權驗證。查詢也會在優化器之前呼叫 precheck 驗證許可權。如果有許可權，就開啟表繼續執行。開啟表的時候，執行器就會根據表的引擎定義，去呼叫這個引擎提供的介面。在有些場景下，執行器呼叫一次，在引擎內部則掃描了多行，因此引擎掃描行數跟rows_examined並不是完全相同的。

MySQL優化器：

MySQL 優化器使用基於成本的優化方式（Cost-based Optimization），以 SQL 語句作為輸入，利用內建的成本模型和資料字典資訊以及儲存引擎的統計資訊決定使用哪些步驟實現查詢語句，也就是查詢計劃。

從高層次來說，MySQL Server 可以分為兩部分：伺服器層以及儲存引擎層。其中，優化器工作在伺服器層，位於儲存引擎 API 之上。

優化器的工作過程從語意上可以分為四個階段：

1.邏輯轉換，包括否定消除、等值傳遞和常數傳遞、常數表示式求值、外連線轉換為內連線、子查詢轉換、檢視合併等；
2.優化準備，例如索引 ref 和 range 存取方法分析、查詢條件扇出值（fan out，過濾後的記錄數）分析、常數表檢測；
3.基於成本優化，包括存取方法和連線順序的選擇等；
4.執行計劃改進，例如表條件下推、存取方法調整、排序避免以及索引條件下推。

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

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

資料只存放在葉子節點，而且是有序的排放。

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

可以看出，索引的排列順序是根據比較字串的首字母排序的。
我們在進行模糊查詢的時候，如果把 % 放在了前面，最左的 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; 不可以走索引

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

可以有兩種方式優化：
一種是使用覆蓋索引，第二種是把%放後面。

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

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

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

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

3.OR 前後只要存在非索引的列，都會導致索引失效。查詢條件包含or，就有可能導致索引失效。
例子:表example中有欄位為pid是int型別,score是int型別。

//此時執行語句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是有優化器的，處於效率與成本，遇到OR條件，索引可能會失效也是合理的。

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

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

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

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

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

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

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

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

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

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

還有不等於(!= 或者<>)導致索引失效。
例子：在表example中有int型別的score欄位索引idx_score

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

//此時執行語句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。

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

//此時執行語句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。

//此時執行語句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

當把兩表的欄位型別改為一致時:

//此時執行語句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估計使用全表掃描要比使用索引快,則不使用索引。當表的索引被查詢，會使用最好的索引，除非優化器使用全表掃描更有效。優化器優化成全表掃描取決與使用最好索引查出來的資料是否超過表的30%的資料。
建議:不要給’性別’等增加索引。如果某個資料列裡包含了均是"0/1"或“Y/N”等值，即包含著許多重複的值，就算為它建立了索引，索引效果不會太好，還可能導致全表掃描。
Mysql出於效率與成本考慮，估算全表掃描與使用索引，哪個執行快，這跟它的優化器有關。

五、總結

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

到此這篇關於MySQL索引失效場景及解決方案的文章就介紹到這了,更多相關MySQL索引失效內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！