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是查詢的結果。當SQL查詢的關鍵字(key)能夠直接在查詢快取中配對時，查詢結果(value)就會直接傳回給客戶端。

其實大多數情況下建議不要使用查詢緩存，為什麼呢？因為查詢快取往往弊大於利。只要涉及到一個表的更新操作，所有和該表相關的查詢快取都很容易失效並被清空。因此很有可能經過費力將結果儲存之後，還未來得及使用就被新的更新操作全部清空了。對於更新操作多的資料庫來說，查詢快取的命中率會非常低。除非業務需要的是一張靜態表，很長時間才會更新一次。例如，一個系統設定表，那麼這張表的查詢才適合使用查詢快取。

3.分析器

詞法分析（辨識關鍵字，操作，表名，列名）

語法分析(判斷是否符合語法）

4.優化器

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

5.執行器

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

並不是完全相同的。

MySQL優化器：

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

從高層而言，MySQL服務端分成伺服器層和儲存引擎層兩個組成部分。其中，優化器工作在伺服器層，位於儲存引擎 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索引失效如何解決的詳細內容。更多資訊請關注PHP中文網其他相關文章！