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

MySQL中一條查詢SQL是如何被執(zhí)行的？

如上圖，我們可以看到一條Mysql查詢語句，從被客戶端下發(fā)到調(diào)用存儲引擎讀取數(shù)據(jù)，返回結(jié)果，經(jīng)歷了連接器、分析器、優(yōu)化器、執(zhí)行器。我們以下面SQL為例，簡單說明下在各個環(huán)節(jié)中分別做了哪些事情。

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

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

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

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

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

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

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

step4執(zhí)行器：首先，進行權(quán)限校驗，如果沒有權(quán)限則會返回錯誤信息，如果有權(quán)限就會調(diào)用數(shù)據(jù)庫存儲引擎的查詢接口，返回引擎的查詢結(jié)果。

---

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

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

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

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

---

如何避免索引失效

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

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

---

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

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

---

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

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

我們在實際SQL設(shè)計時盡量UNIONALL代替UNION，UNION和UNIONALL的區(qū)別主要是UNION需要將結(jié)果集合并后并進行唯一性過濾操作，涉及到排序，產(chǎn)生大量的CPU運算，增加資源消耗及延遲。當然，使用UNIONALL的前提條件是兩個結(jié)果集沒有重復數(shù)據(jù)，或?qū)κ欠翊嬖谥貜蛿?shù)據(jù)無要求。

---

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

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

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

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

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

---

避免在WHERE子句中對索引列使用計算、函數(shù)、類型轉(zhuǎn)換等操作

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

---

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

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

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

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

• 查詢條件中必須處理為空的情況，否則將會出現(xiàn)一些很奇怪的問題，比如NOTIN、!=等負向條件查詢在有NULL值的時候返回永遠為空結(jié)果，查詢?nèi)菀滓壮鲥e。
• 在部分數(shù)據(jù)庫中將導致索引失效。
• 可空列需要更多存儲空間，導致空間變大。

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

---

避免在WHERE子句中對索引列進行隱式類型轉(zhuǎn)換

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

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

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