一葉知秋丿
在MySQL數據庫中，的確存在一些場景會導致存儲引擎放棄使用索引而進行全表掃描，下面我們將從索引失效的原因以及如何避免索引失效 兩個方面回答這個問題，希望對您有所幫助。
MySQL中一條查詢SQL是如何被執行的？
如上圖，我們可以看到一條Mysql查詢語句，從被客戶端下發到調用存儲引擎讀取數據，返回結果，經歷了連接器、分析器、優化器、執行器。我們以下面SQL為例，簡單說明下在各個環節中分別做了哪些事情。
step1 連接器：首先會檢查該該查詢SQL語句是否有權限，如果無權限，則直接返回錯誤信息，如果有權限，在MySQL 8.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子句中對索引列使用 IS NULL ，IS NOT NULL。
- WHERE子句中對索引列使用模糊查詢LIKE並以通配符開頭如，%ab。
- WHERE子句中對索引列使用OR來連接條件。
- WHERE子句中對索引列使用IN和NOT IN 。
- WHERE子句中對索引列使用隱式類型轉換，如字段 age類型為 int，WHERE條件中卻使用varchar類型，如，age= '17'。
- 複合索引未遵循最佳左前綴原則或者存在斷點。
- 索引被禁用，開啟索使用 ALTER TABLE TESTOPS ENABLE KEYS 。
如何避免索引失效
避免在 WHERE 子句中使用 != 或 <> 不等於操作符
在 WHERE 子句中使用 != 或者 <> 操作符，將導致引擎放棄使用索引而進行全表掃描。MySQL 僅有對以下操作符才會使用索引：，<=，>=，=，BETWEEN，IN，以及使用LIKE 時的 後綴模糊查詢 % 。
避免在WHERE 子句中對索引列使用%前綴模糊查詢
WHERE 子句中使用 %前綴模糊查詢，將導致引擎放棄使用索引而進行全表掃描。解決 %前綴模糊查詢時索引失效的方法是添加覆蓋索引（僅訪問索引的查詢，查詢列都是索引，只需掃描索引而無須回表）。
避免在WHERE 子句中對索引列使用 OR 來連接條件
在 WHERE 子句中使用 OR 來連接條件，將導致引擎放棄使用索引而進行全表掃描。使用 OR 的字句可以分解為多個查詢，並且通過 UNION 連接多個查詢的結果。他們的速度只同是否使用索引有關，若查詢需要時能夠用到複合索引，使用 UNION ALL 執行的效率更高。
我們在實際SQL設計時儘量UNION ALL 代替 UNION ，UNION 和 UNION ALL 的區別主要是UNION 需要將結果集合並後並進行唯一性過濾操作，涉及到排序，產生大量的CPU運算，增加資源消耗及延遲。當然，使用UNION ALL的前提條件是兩個結果集沒有重複數據，或對是否存在重複數據無要求。
避免在WHERE 子句中對索引列使用 IN 和 NOT IN
在 WHERE 子句中使用 IN 和 NOT IN ，將導致引擎放棄使用索引而進行全表掃描。在SQL設計時對於連續的數值，可以使用 BETWEEN…AND… 儘量避免使用 IN。除此之外，一般可使用 EXISTS 代替 IN 。若需要使用 IN，在 IN 後面值的列表中，應按照值的分佈數量降序排列，以減少判斷的次數。
嘗試使用BETWEEN AND 替換 IN，示例如下 。
我們使用 EXISTS 來替代 IN，用 NOT EXISTS 來替代 NOT IN，無論哪種情況 NOT IN效率都是最低的。
除此之外，我們可以嘗試使用LEFT JOIN 替換 IN。
避免在WHERE 子句中對索引列使用計算、函數、類型轉換等操作
在 WHERE 子句中對 “=” 左邊的字段進行函數、算術運算及其他表達式運算，將導致引擎放棄使用索引而進行全表掃描，可以將表達式運算移至“=”右邊。
在 WHERE 子句中對字段進行 NULL 值判斷，將導致引擎放棄使用索引而進行全表掃描。創建表時 NULL 是默認值，但大多數時候應該使用 NOT NULL，或者使用一個默認值，如 使用0 作為默認值。
例如，性別字段，使用1表示男，2表示女，0表示未知，或者是當用戶沒有選擇，默認值設置為0（大部分編程語言的數字類型的默認值0）。
- 查詢條件中必須處理為空的情況，否則將會出現一些很奇怪的問題，比如 NOT IN、!= 等負向條件查詢在有 NULL 值的時候返回永遠為空結果，查詢容易易出錯。
- 在部分數據庫中將導致索引失效。
- 可空列需要更多存儲空間，導致空間變大。
凡事沒有絕對的，使用默認值的思路一定程度可以解決很大一部分可為空的問題，但不是所有的都需這樣做，具體還是需要根據具體業務進行分析。
避免在WHERE 子句中對索引列進行隱式類型轉換
WHERE 子句中對索引列進行隱式類型轉換（條件中字段賦值與字段定義類型不匹配），將導致引擎放棄使用索引而進行全表掃描。當我們對不同類型的值進行比較的時候，為了使得這些數值可比較，MySQL數據庫會做一些隱式轉化（Implicit type conversion）。
SQL查詢語句的條件中字段賦值與字段定義類型不匹配是一種常見的錯誤用法。
如上，字段 account 字段的定義為 varchar 類型，而在 WHERE 條件中 account 字段值是數字型，數據類型不匹配，此時是沒法直接進行比較的，需要進行類型轉換。MySQL的策略是將表中 account 字段全部轉換為數字型之後再比較，因此引發函數作用於字段，使得索引失效，導致全表掃描，正確的寫法如下：
軟件測試開發技術棧
在oracle遇到過這樣的問題，該字段明明設置了索引，但查詢時卻沒走索引。這種情況遇到的有兩種原因。一是:該字段存在大量的null值，二是:該字段存在大量的重複值。在oracle中可以通過強制走索引解決。
