在MySQL數(shù)據(jù)庫(kù)中，的確存在一些場(chǎng)景會(huì)導(dǎo)致存儲(chǔ)引擎放棄使用索引而進(jìn)行全表掃描，下面我們將從索引失效的原因以及如何避免索引失效 兩個(gè)方面回答這個(gè)問(wèn)題，希望對(duì)您有所幫助。
MySQL中一條查詢(xún)SQL是如何被執(zhí)行的？如上圖，我們可以看到一條Mysql查詢(xún)語(yǔ)句，從被客戶(hù)端下發(fā)到調(diào)用存儲(chǔ)引擎讀取數(shù)據(jù)，返回結(jié)果，經(jīng)歷了連接器、分析器、優(yōu)化器、執(zhí)行器。我們以下面SQL為例，簡(jiǎn)單說(shuō)明下在各個(gè)環(huán)節(jié)中分別做了哪些事情。
如上SQL，實(shí)現(xiàn)了查詢(xún)Score表中學(xué)號(hào)為 9527 同學(xué)的Math(數(shù)學(xué))成績(jī)，下面我們分析下這個(gè)語(yǔ)句的執(zhí)行流程：step1 連接器：首先會(huì)檢查該該查詢(xún)SQL語(yǔ)句是否有權(quán)限，如果無(wú)權(quán)限，則直接返回錯(cuò)誤信息，如果有權(quán)限，在MySQL 8.0 版本之前，會(huì)先查詢(xún)緩存，以這條SQL語(yǔ)句作為KEY在內(nèi)存中進(jìn)行查詢(xún)，如果有結(jié)果則直接將歷史查詢(xún)結(jié)果返回，如果沒(méi)有，執(zhí)行下一步。
step2 分析器：在分析器中會(huì)進(jìn)行詞法分析與語(yǔ)法分析，通過(guò)分析器詞法分析，提取SQL語(yǔ)句中關(guān)鍵字，比如，提取SQL語(yǔ)句中的SELECT、WHERE，提取查詢(xún)的表名是 CourseInfo，提取查詢(xún)的字段是StudentId、Score，提取查詢(xún)條件是 Course 等于 'Math' 且 StudentId 等于 9527。
然后再通過(guò)再語(yǔ)法分析判斷在該SQL 語(yǔ)句是否有語(yǔ)法錯(cuò)誤，比如，關(guān)鍵詞是否正確、StudentId、Score字段是否存在于CourseInfo表中等等，若檢查通過(guò)，則繼續(xù)執(zhí)行下一步。
step3 優(yōu)化器：優(yōu)化器會(huì)通過(guò)自己的分析算法確認(rèn)執(zhí)行方案，上面的SQL語(yǔ)句，有兩種執(zhí)行方案，如下：
方案一：首先，查詢(xún)課程是Math的所有學(xué)生的成績(jī)。然后，再查詢(xún)其學(xué)號(hào)是9527的成績(jī)。方案二：首先，查詢(xún)學(xué)號(hào)是9527的所有科目的成績(jī)。然后，再查詢(xún)其科目是Math的成績(jī)。因此，優(yōu)化器會(huì)根據(jù)它的優(yōu)化算法分析它所認(rèn)為執(zhí)行效率最高的一個(gè)方案（優(yōu)化器認(rèn)為不一定是最好。同時(shí)如果如優(yōu)化器分析使用索引掃描比全表掃描效率低時(shí)，會(huì)放棄使用索引而選擇全表掃描，一般數(shù)據(jù)量極少時(shí)，可能不會(huì)走索引）。
step4 執(zhí)行器：首先，進(jìn)行權(quán)限校驗(yàn)，如果沒(méi)有權(quán)限則會(huì)返回錯(cuò)誤信息，如果有權(quán)限就會(huì)調(diào)用數(shù)據(jù)庫(kù)存儲(chǔ)引擎的查詢(xún)接口，返回引擎的查詢(xún)結(jié)果。
放棄使用索引而選擇全表掃描除了上面提到的當(dāng)優(yōu)化器分析使用索引掃描比全表掃描效率低時(shí)，優(yōu)化器會(huì)放棄使用索引而選擇全表掃描，還有哪些原因會(huì)導(dǎo)致放棄索引而選擇全表掃描呢？
因索引失效，導(dǎo)致全表掃描的可能原因有以下幾點(diǎn)：
WHERE子句中對(duì)索引列進(jìn)行計(jì)算、函數(shù)、類(lèi)型轉(zhuǎn)換等操作。WHERE子句中對(duì)索引列使用不等于，如!= 或者 <>。WHERE子句中對(duì)索引列使用 IS NULL ，IS NOT NULL。WHERE子句中對(duì)索引列使用模糊查詢(xún)LIKE并以通配符開(kāi)頭如，%ab。WHERE子句中對(duì)索引列使用OR來(lái)連接條件。WHERE子句中對(duì)索引列使用IN和NOT IN 。WHERE子句中對(duì)索引列使用隱式類(lèi)型轉(zhuǎn)換，如字段 age類(lèi)型為 int，WHERE條件中卻使用varchar類(lèi)型，如，age= '17'。復(fù)合索引未遵循最佳左前綴原則或者存在斷點(diǎn)。索引被禁用，開(kāi)啟索使用 ALTER TABLE TESTOPS ENABLE KEYS 。如何避免索引失效避免在 WHERE 子句中使用 != 或 <> 不等于操作符
在 WHERE 子句中使用 != 或者 <> 操作符，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。MySQL 僅有對(duì)以下操作符才會(huì)使用索引：<，>，<=，>=，=，BETWEEN，IN，以及使用LIKE 時(shí)的 后綴模糊查詢(xún) % 。
避免在WHERE 子句中對(duì)索引列使用%前綴模糊查詢(xún)
WHERE 子句中使用 %前綴模糊查詢(xún)，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。解決 %前綴模糊查詢(xún)時(shí)索引失效的方法是添加覆蓋索引（僅訪(fǎng)問(wèn)索引的查詢(xún)，查詢(xún)列都是索引，只需掃描索引而無(wú)須回表）。
避免在WHERE 子句中對(duì)索引列使用 OR 來(lái)連接條件
在 WHERE 子句中使用 OR 來(lái)連接條件，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。使用 OR 的字句可以分解為多個(gè)查詢(xún)，并且通過(guò) UNION 連接多個(gè)查詢(xún)的結(jié)果。他們的速度只同是否使用索引有關(guān)，若查詢(xún)需要時(shí)能夠用到復(fù)合索引，使用 UNION ALL 執(zhí)行的效率更高。
我們?cè)趯?shí)際SQL設(shè)計(jì)時(shí)盡量UNION ALL 代替 UNION ，UNION 和 UNION ALL 的區(qū)別主要是UNION 需要將結(jié)果集合并后并進(jìn)行唯一性過(guò)濾操作，涉及到排序，產(chǎn)生大量的CPU運(yùn)算，增加資源消耗及延遲。當(dāng)然，使用UNION ALL的前提條件是兩個(gè)結(jié)果集沒(méi)有重復(fù)數(shù)據(jù)，或?qū)κ欠翊嬖谥貜?fù)數(shù)據(jù)無(wú)要求。
避免在WHERE 子句中對(duì)索引列使用 IN 和 NOT IN
在 WHERE 子句中使用 IN 和 NOT IN ，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。在SQL設(shè)計(jì)時(shí)對(duì)于連續(xù)的數(shù)值，可以使用 BETWEEN…AND… 盡量避免使用 IN。除此之外，一般可使用 EXISTS 代替 IN 。若需要使用 IN，在 IN 后面值的列表中，應(yīng)按照值的分布數(shù)量降序排列，以減少判斷的次數(shù)。
嘗試使用BETWEEN AND 替換 IN，示例如下 。
我們使用 EXISTS 來(lái)替代 IN，用 NOT EXISTS 來(lái)替代 NOT IN，無(wú)論哪種情況 NOT IN效率都是最低的。
除此之外，我們可以嘗試使用LEFT JOIN 替換 IN。
避免在WHERE 子句中對(duì)索引列使用計(jì)算、函數(shù)、類(lèi)型轉(zhuǎn)換等操作
在 WHERE 子句中對(duì) “=” 左邊的字段進(jìn)行函數(shù)、算術(shù)運(yùn)算及其他表達(dá)式運(yùn)算，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，可以將表達(dá)式運(yùn)算移至“=”右邊。
避免在WHERE 子句中對(duì)索引列進(jìn)行 NULL 值判斷在 WHERE 子句中對(duì)字段進(jìn)行 NULL 值判斷，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。創(chuàng)建表時(shí) NULL 是默認(rèn)值，但大多數(shù)時(shí)候應(yīng)該使用 NOT NULL，或者使用一個(gè)默認(rèn)值，如 使用0 作為默認(rèn)值。
例如，性別字段，使用1表示男，2表示女，0表示未知，或者是當(dāng)用戶(hù)沒(méi)有選擇，默認(rèn)值設(shè)置為0（大部分編程語(yǔ)言的數(shù)字類(lèi)型的默認(rèn)值0）。
如果字段允許為空，可能會(huì)有以下問(wèn)題：查詢(xún)條件中必須處理為空的情況，否則將會(huì)出現(xiàn)一些很奇怪的問(wèn)題，比如 NOT IN、!= 等負(fù)向條件查詢(xún)?cè)谟?NULL 值的時(shí)候返回永遠(yuǎn)為空結(jié)果，查詢(xún)?nèi)菀滓壮鲥e(cuò)。在部分?jǐn)?shù)據(jù)庫(kù)中將導(dǎo)致索引失效。可空列需要更多存儲(chǔ)空間，導(dǎo)致空間變大。凡事沒(méi)有絕對(duì)的，使用默認(rèn)值的思路一定程度可以解決很大一部分可為空的問(wèn)題，但不是所有的都需這樣做，具體還是需要根據(jù)具體業(yè)務(wù)進(jìn)行分析。
避免在WHERE 子句中對(duì)索引列進(jìn)行隱式類(lèi)型轉(zhuǎn)換
WHERE 子句中對(duì)索引列進(jìn)行隱式類(lèi)型轉(zhuǎn)換（條件中字段賦值與字段定義類(lèi)型不匹配），將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。當(dāng)我們對(duì)不同類(lèi)型的值進(jìn)行比較的時(shí)候，為了使得這些數(shù)值可比較，MySQL數(shù)據(jù)庫(kù)會(huì)做一些隱式轉(zhuǎn)化（Implicit type conversion）。
SQL查詢(xún)語(yǔ)句的條件中字段賦值與字段定義類(lèi)型不匹配是一種常見(jiàn)的錯(cuò)誤用法。
如上，字段 account 字段的定義為 varchar 類(lèi)型，而在 WHERE 條件中 account 字段值是數(shù)字型，數(shù)據(jù)類(lèi)型不匹配，此時(shí)是沒(méi)法直接進(jìn)行比較的，需要進(jìn)行類(lèi)型轉(zhuǎn)換。MySQL的策略是將表中 account 字段全部轉(zhuǎn)換為數(shù)字型之后再比較，因此引發(fā)函數(shù)作用于字段，使得索引失效，導(dǎo)致全表掃描，正確的寫(xiě)法如下：