在MySQL數據庫中，的確存在一些場景會導致存儲引擎放棄使用索引而進行全表掃描，下面我們將從索引失效的原因以及如何避免索引失效兩個方面回答這個問題，希望對您有所幫助。

MySQL中一條查詢SQL是如何被執行的？

如上圖，我們可以看到一條Mysql查詢語句，從被客戶端下發到調用存儲引擎讀取數據，返回結果，經歷了連接器、分析器、優化器、執行器。我們以下面SQL為例，簡單說明下在各個環節中分別做了哪些事情。

如上SQL，實現了查詢Score表中學號為9527同學的Math(數學)成績，下面我們分析下這個語句的執行流程：

step1連接器：首先會檢查該該查詢SQL語句是否有權限，如果無權限，則直接返回錯誤信息，如果有權限，在MySQL8.0版本之前，會先查詢緩存，以這條SQL語句作為KEY在內存中進行查詢，如果有結果則直接將歷史查詢結果返回，如果沒有，執行下一步。

step2分析器：在分析器中會進行詞法分析與語法分析，通過分析器詞法分析，提取SQL語句中關鍵字，比如，提取SQL語句中的SELECT、WHERE，提取查詢的表名是CourseInfo，提取查詢的字段是StudentId、Score，提取查詢條件是Course等于'Math'且StudentId等于9527。

然后再通過再語法分析判斷在該SQL語句是否有語法錯誤，比如，關鍵詞是否正確、StudentId、Score字段是否存在于CourseInfo表中等等，若檢查通過，則繼續執行下一步。

step3優化器：優化器會通過自己的分析算法確認執行方案，上面的SQL語句，有兩種執行方案，如下：

• 方案一：首先，查詢課程是Math的所有學生的成績。然后，再查詢其學號是9527的成績。
• 方案二：首先，查詢學號是9527的所有科目的成績。然后，再查詢其科目是Math的成績。

因此，優化器會根據它的優化算法分析它所認為執行效率最高的一個方案（優化器認為不一定是最好。同時如果如優化器分析使用索引掃描比全表掃描效率低時，會放棄使用索引而選擇全表掃描，一般數據量極少時，可能不會走索引）。

step4執行器：首先，進行權限校驗，如果沒有權限則會返回錯誤信息，如果有權限就會調用數據庫存儲引擎的查詢接口，返回引擎的查詢結果。

---

放棄使用索引而選擇全表掃描

除了上面提到的當優化器分析使用索引掃描比全表掃描效率低時，優化器會放棄使用索引而選擇全表掃描，還有哪些原因會導致放棄索引而選擇全表掃描呢？

因索引失效，導致全表掃描的可能原因有以下幾點：

• WHERE子句中對索引列進行計算、函數、類型轉換等操作。
• WHERE子句中對索引列使用不等于，如!=或者<>。
• WHERE子句中對索引列使用ISNULL，ISNOTNULL。
• WHERE子句中對索引列使用模糊查詢LIKE并以通配符開頭如，%ab。
• WHERE子句中對索引列使用OR來連接條件。
• WHERE子句中對索引列使用IN和NOTIN。
• WHERE子句中對索引列使用隱式類型轉換，如字段age類型為int，WHERE條件中卻使用varchar類型，如，age='17'。
• 復合索引未遵循最佳左前綴原則或者存在斷點。
• 索引被禁用，開啟索使用ALTERTABLETESTOPSENABLEKEYS。

---

如何避免索引失效

避免在WHERE子句中使用!=或<>不等于操作符

在WHERE子句中使用!=或者<>操作符，將導致引擎放棄使用索引而進行全表掃描。MySQL僅有對以下操作符才會使用索引：<，>，<=，>=，=，BETWEEN，IN，以及使用LIKE時的后綴模糊查詢%。

---

避免在WHERE子句中對索引列使用%前綴模糊查詢

WHERE子句中使用%前綴模糊查詢，將導致引擎放棄使用索引而進行全表掃描。解決%前綴模糊查詢時索引失效的方法是添加覆蓋索引（僅訪問索引的查詢，查詢列都是索引，只需掃描索引而無須回表）。

---

避免在WHERE子句中對索引列使用OR來連接條件

在WHERE子句中使用OR來連接條件，將導致引擎放棄使用索引而進行全表掃描。使用OR的字句可以分解為多個查詢，并且通過UNION連接多個查詢的結果。他們的速度只同是否使用索引有關，若查詢需要時能夠用到復合索引，使用UNIONALL執行的效率更高。

我們在實際SQL設計時盡量UNIONALL代替UNION，UNION和UNIONALL的區別主要是UNION需要將結果集合并后并進行唯一性過濾操作，涉及到排序，產生大量的CPU運算，增加資源消耗及延遲。當然，使用UNIONALL的前提條件是兩個結果集沒有重復數據，或對是否存在重復數據無要求。

---

避免在WHERE子句中對索引列使用IN和NOTIN

在WHERE子句中使用IN和NOTIN，將導致引擎放棄使用索引而進行全表掃描。在SQL設計時對于連續的數值，可以使用BETWEEN…AND…盡量避免使用IN。除此之外，一般可使用EXISTS代替IN。若需要使用IN，在IN后面值的列表中，應按照值的分布數量降序排列，以減少判斷的次數。

嘗試使用BETWEENAND替換IN，示例如下。

我們使用EXISTS來替代IN，用NOTEXISTS來替代NOTIN，無論哪種情況NOTIN效率都是最低的。

除此之外，我們可以嘗試使用LEFTJOIN替換IN。

---

避免在WHERE子句中對索引列使用計算、函數、類型轉換等操作

在WHERE子句中對“=”左邊的字段進行函數、算術運算及其他表達式運算，將導致引擎放棄使用索引而進行全表掃描，可以將表達式運算移至“=”右邊。

---

避免在WHERE子句中對索引列進行NULL值判斷

在WHERE子句中對字段進行NULL值判斷，將導致引擎放棄使用索引而進行全表掃描。創建表時NULL是默認值，但大多數時候應該使用NOTNULL，或者使用一個默認值，如使用0作為默認值。

例如，性別字段，使用1表示男，2表示女，0表示未知，或者是當用戶沒有選擇，默認值設置為0（大部分編程語言的數字類型的默認值0）。

如果字段允許為空，可能會有以下問題：

• 查詢條件中必須處理為空的情況，否則將會出現一些很奇怪的問題，比如NOTIN、!=等負向條件查詢在有NULL值的時候返回永遠為空結果，查詢容易易出錯。
• 在部分數據庫中將導致索引失效。
• 可空列需要更多存儲空間，導致空間變大。

凡事沒有絕對的，使用默認值的思路一定程度可以解決很大一部分可為空的問題，但不是所有的都需這樣做，具體還是需要根據具體業務進行分析。

---

避免在WHERE子句中對索引列進行隱式類型轉換

WHERE子句中對索引列進行隱式類型轉換（條件中字段賦值與字段定義類型不匹配），將導致引擎放棄使用索引而進行全表掃描。當我們對不同類型的值進行比較的時候，為了使得這些數值可比較，MySQL數據庫會做一些隱式轉化（Implicittypeconversion）。

SQL查詢語句的條件中字段賦值與字段定義類型不匹配是一種常見的錯誤用法。

如上，字段account字段的定義為varchar類型，而在WHERE條件中account字段值是數字型，數據類型不匹配，此時是沒法直接進行比較的，需要進行類型轉換。MySQL的策略是將表中account字段全部轉換為數字型之后再比較，因此引發函數作用于字段，使得索引失效，導致全表掃描，正確的寫法如下：