聊一聊MySQL索引失效的問題

一、索引原理

索引是提高資料庫查詢效能的一個重要方法。

使用索引用可快速找出某個列中包含特定值的行。不使用索引，必須從第一條記錄開始讀，可能要讀完整個表，才能找出相關的行。

使用索引就像查字典一樣，我們可以根據拼音、筆畫、偏旁部首等排序的目錄(索引)，快速查詢到需要的字。

之前介紹MySQL儲存引擎的文章（ 聊一聊MySQL的儲存引擎），測試對比了兩種儲存引擎(MyISAM或者InnoDB)，使用主鍵索引查詢，效率快了幾十倍。

雖然索引大大提高了查詢(select)速度，但同時也會降低更新(insert,update,delete)表的速度，因為更新表時，資料庫不僅要更新和儲存資料，還要更新和儲存索引檔案。當然索引檔案也佔用儲存空間。

二、索引失效的場景

索引雖然加快了查詢效率，但使用方法不當，就會出現索引失效。

下面實際操作，列舉一些索引失效的場景。

CREATETABLE 
customer(


id 
INTAUTO_INCREMENT,


companyVARCHAR(30),


nameVARCHAR(30),


sex 
enum('male','female'),


age 
INT,


phoneVARCHAR(30),


addressVARCHAR(60),


PRIMARYKEY 
(id));






CREATEINDEX 
index_company ON customer(company);


CREATEINDEX 
index_age ON customer(age);


CREATEINDEX 
index_phone ON customer(phone);


CREATEINDEX 
index_phone_name ON customer(phone,name);

上面建立了一個客戶表，以及三個單列索引和一個組合索引，我們來檢視一下索引：

6 rows inset (0.01 sec)

上面顯示包括一個預設的主鍵索引，還有三個單列索引，兩個組合索引項。其中主鍵索引為唯一(unique)索引，索引類別(Index_type)顯示為BTREE，實際為B+樹。

2.1 模糊匹配LIKE以 % 開頭，會導致索引失效。

應該把模糊匹配放到最右邊。

2.2 索引列進行計算，會導致索引失效。

應該把運算放在右邊。

2.3 索引列使用函式，會導致索引失效。

2.4 索引列型別轉換，會導致索引失效。

應該保持原有型別，避免型別轉換。

2.5 索引列比較字符集不一致時，會導致索引失效。

按照上面客戶表的格式，建立兩個供應商的表supplier、supplier_utf8，字符集分別為utf8mb4、utf8。 關於字符集可以看看之前文章( 聊一聊MySQL的字符集 )。

可以看到字符集一樣的，一個表使用了索引，另一個表走了全表掃描。

字符集不一樣的，兩個表都走了全表掃描。

這種情況隱蔽性比較強，經常業務上線了才被發現。

2.6 使用 or 查詢，不論另一項是否為索引列，都會導致索引失效。

不過如果非要使用 or進行查詢，可以利用MRR功能，對回表查詢進行排序最佳化。

2.7 組合索引，沒有使用第一列索引，會導致索引失效。

組合索引並不要求按照排列順序，下面可以用到索引。

如果資料庫預計使用全表掃描比使用索引快，則不使用索引。MySQL最新版本中，對索引判斷有了很大改善，之前版本使用 in,!= ，<>，is null，is not null 會導致索引失效，最新版測試，還是使用了索引查詢。

MySQL5.6引入了MRR(Multi-Range Read Optimization)，專門來最佳化：二級索引的範圍掃描並且需要回表的情況。

它的原理是，將多個需要回表的二級索引根據主鍵進行排序，然後一起回表，將原來的回表時進行的隨機 I/O ，轉變成順序 I/O ，降低查詢過程中的 I/O 開銷，同時減少緩衝池中資料頁被替換的頻次。

MySQL5.7引入了 Generated Column，如果確實需要對索引列進行計算等操作，可以採用虛擬列的方式來處理，比如建立客戶表時增加對應的列，公司名稱小寫 lcase_company ，和實歲 full_age , 並建立索引。

之後直接對 Cenerated Column 進行查詢，就相當於計算列用到了索引。

三、索引的使用規範

在使用索引的時候，不僅要注意避免索引失效，也要遵循一定的規範，以便高效的使用索引。

下面總結了一些規範建議， 可以用來參考，並非絕對真理 。

3.1 單表的索引數建議不超過5個，組合索引的欄位原則上不超過3個。

3.2 儘量不要在較長字串的欄位上建立索引，可以設定索引欄位字首長度。

3.3 選擇在查詢過濾中使用率較高，如 where，orderby，group by 的列建立索引。

3.4 不要在區分度不高的列上建立索引，比如性別等，利用不了索引效能。

3.5 不要在經常更新的列上建立索引，資料更新也會更新索引，影響資料庫效能。

3.6 建立組合索引時，區分度最高，或者查詢頻率最高的，放在最左側。

3.7 合理利用覆蓋索引來滿足查詢要求，避免回表查詢，減少I/O開銷。

3.8 刪除不再使用、少使用、或者重複的索引，減少資料更新的開銷。

3.9 利用explain來判斷查詢語句，是使用了索引，還是走了全表掃描。

KunlunDB專案已開源

【GitHub：】

【Gitee：】

END