共計 2279 個字符，預(yù)計需要花費 6 分鐘才能閱讀完成。

MySQL 索引失效有哪些原因，相信很多沒有經(jīng)驗的人對此束手無策，為此本文總結(jié)了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個問題。

單個索引

1、使用!= 或者 導(dǎo)致索引失效

SELECT * FROM `user` WHERE `name` !=  冰峰 

我們給 name 字段建立了索引，但是如果!= 或者 這種都會導(dǎo)致索引失效，進(jìn)行全表掃描，所以如果數(shù)據(jù)量大的話，謹(jǐn)慎使用

可以通過分析 SQL 看到，type 類型是 ALL，掃描了 10 行數(shù)據(jù)，進(jìn)行了全表掃描。也是同樣的結(jié)果。

2、類型不一致導(dǎo)致的索引失效

在說這個之前，一定要說一下設(shè)計表字段的時候，千萬、一定、必須要保持字段類型的一致性，啥意思？比如 user 表的 id 是 int 自增，到了用戶的賬戶表 user_id 這個字段，一定、必須也是 int 類型，千萬不要寫成 varchar、char 什么的騷操作。

SELECT * FROM `user` WHERE height= 175;

這個 SQL 諸位一定要看清楚，height 表字段類型是 varchar，但是我查詢的時候使用了數(shù)字類型，因為這個中間存在一個隱式的類型轉(zhuǎn)換，所以就會導(dǎo)致索引失效，進(jìn)行全表掃描。

現(xiàn)在明白我為啥說設(shè)計字段的時候一定要保持類型的一致性了不，如果你不保證一致性，一個 int 一個 varchar，在進(jìn)行多表聯(lián)合查詢（eg: 1 = 1）必然走不了索引。

遇到這樣的表，里面有幾千萬數(shù)據(jù)，改又不能改，那種痛可能你們暫時還體會。

少年們，切記，切記。

3、函數(shù)導(dǎo)致的索引失效

SELECT * FROM `user` WHERE DATE(create_time) =  2020-09-03

如果你的索引字段使用了索引，對不起，他是真的不走索引的。

4、運算符導(dǎo)致的索引失效

SELECT * FROM `user` WHERE age - 1 = 20;

如果你對列進(jìn)行了（+，-，*，/，!）, 那么都將不會走索引。

5、OR 引起的索引失效

SELECT * FROM `user` WHERE `name` =  張三  OR height =  175

OR 導(dǎo)致索引是在特定情況下的，并不是所有的 OR 都是使索引失效，如果 OR 連接的是同一個字段，那么索引不會失效，反之索引失效。

6、模糊搜索導(dǎo)致的索引失效

SELECT * FROM `user` WHERE `name` LIKE  % 冰 

這個我相信大家都明白，模糊搜索如果你前綴也進(jìn)行模糊搜索，那么不會走索引。

7、NOT IN、NOT EXISTS 導(dǎo)致索引失效

SELECT s.* FROM `user` s WHERE NOT EXISTS (SELECT * FROM `user` u WHERE u.name = s.`name` AND u.`name` =  冰峰)

SELECT * FROM `user` WHERE `name` NOT IN (冰峰 

這兩種用法，也將使索引失效。但是 NOT IN 還是走索引的，千萬不要誤解為 IN 全部是不走索引的。我之前就有誤解（丟人了 …）。

8、IS NULL 不走索引，IS NOT NULL 走索引

SELECT * FROM `user` WHERE address IS NULL

不走索引。

SELECT * FROM `user` WHERE address IS NOT NULL;

走索引。

根據(jù)這個情況，建議大家這設(shè)計字段的時候，如果沒有必要的要求必須為 NULL，那么最好給個默認(rèn)值空字符串，這可以解決很多后續(xù)的麻煩（有深刻的體驗 體驗 = 教訓(xùn)）。

符合索引

1、最左匹配原則

EXPLAIN SELECT * FROM `user` WHERE sex =  男 

EXPLAIN SELECT * FROM `user` WHERE name =  冰峰  AND sex =  男 

測試之前，刪除其他的單列索引。

啥叫最左匹配原則，就是對于符合索引來說，它的一個索引的順序是從左往右依次進(jìn)行比較的，像第二個查詢語句，name 走索引，接下來回去找 age，結(jié)果條件中沒有 age 那么后面的 sex 也將不走索引。

注意：

SELECT * FROM `user` WHERE sex =  男  AND age = 22 AND `name` =  冰峰 

可能有些搬磚工可能跟我最開始有個誤解，我們的索引順序明明是 name、sex、age，你現(xiàn)在的查詢順序是 sex、age、name，這肯定不走索引啊，你要是自己沒測試過，也有這種不成熟的想法，那跟我一樣還是太年輕了，它其實跟順序是沒有任何關(guān)系的，因為 mysql 的底層會幫我們做一個優(yōu)化，它會把你的 SQL 優(yōu)化為它認(rèn)為一個效率最高的樣子進(jìn)行執(zhí)行。所以千萬不要有這種誤解。

2、如果使用了!= 會導(dǎo)致后面的索引全部失效

SELECT * FROM `user` WHERE sex =  男  AND `name` !=  冰峰  AND age = 22;

我們在 name 字段使用了 !=，由于 name 字段是最左邊的一個字段，根據(jù)最左匹配原則，如果 name 不走索引，后面的字段也將不走索引。

關(guān)于符合索引導(dǎo)致索引失效的情況能說的目前就這兩種，其實我覺得對于符合索引來說，重要的是如何建立高效的索引，千萬不能說我用到那個字段我就去建立一個單獨的索引，不是就可以全局用了嘛。這樣是可以，但是這樣并沒有符合索引高效，所以為了成為高級的搬磚工，我們還是要繼續(xù)學(xué)習(xí)，如何創(chuàng)建高效的索引。

看完上述內(nèi)容，你們掌握 MySQL 索引失效有哪些原因的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道，感謝各位的閱讀！