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