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

Posted on by

一、前言

在對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索引失效內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：