關(guān)于Mysql5.7及8.0版本索引失效情況匯總
TIPS：
- 沒(méi)有特殊說(shuō)明，測(cè)試環(huán)境均為MySQL8.0，早期版本可能會(huì)有更多情況導(dǎo)致索引失效。8.0失效的情況，早期版本也失效；8.0不失效的情況，早期版本可能失效。
- 所有測(cè)試默認(rèn)不考慮表為空的情況，特殊情況文中會(huì)有說(shuō)明。
- 本文只介紹Innodb引擎下的索引失效情況。
-- 創(chuàng)建測(cè)試表 DROP TABLE IF EXISTS `test_idx`; CREATE TABLE `test_idx` ( `id` int(11) NOT NULL AUTO_INCREMENT, `unique_idx` int(11) NOT NULL, `notnull_idx` int(11) NOT NULL, `str_idx` varchar(20) DEFAULT NULL, `normal_idx` int(11) DEFAULT NULL, `str_col` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_unique_idx` (`unique_idx`), KEY `idx_str_idx` (`str_idx`), KEY `idx_normal_idx` (`normal_idx`) USING BTREE ) DEFAULT CHARSET=utf8; -- 插入幾條測(cè)試數(shù)據(jù)，因?yàn)楫?dāng)表里沒(méi)有數(shù)據(jù)時(shí)，部分使用到索引的情況會(huì)失效。 INSERT INTO test_idx VALUES (1,1,'1',1,'111'), (2,2,'2',2,'222'), (3,3,'3',3,'333')
一個(gè)獨(dú)立索引
1、使用like且在左邊有“%”。
-- 無(wú)法使用索引 ? ? EXPLAIN select * from test_idx where bid like '%1%'; -- 可以使用索引 ? ? EXPLAIN select * from test_idx where bid like '1%';
2、隱式類型轉(zhuǎn)換，索引字段與條件或關(guān)聯(lián)字段的類型不一致。
-- 無(wú)法使用索引 ?? ?EXPLAIN select * from test_idx where bid = 1; -- 可以使用索引 ?? ?EXPLAIN select * from test_idx where bid = '1';
3、條件中對(duì)索引列進(jìn)行運(yùn)算或使用函數(shù)
-- 無(wú)法使用索引 ? ? EXPLAIN SELECT * FROM test_idx WHERE SUBSTR(bid, 1) = '1'; ? ? EXPLAIN SELECT * FROM test_idx WHERE id - 1 = 1; -- 可以使用索引 ? ? EXPLAIN SELECT * FROM test_idx WHERE id = 4 - 2; ? ? EXPLAIN SELECT * FROM test_idx WHERE id = TIME_TO_SEC(1);
4、不可空索引使用 is not null，僅當(dāng)查詢列只有該索引列時(shí)會(huì)使用索引
-- 無(wú)法使用索引 ? ? EXPLAIN SELECT * FROM test_idx WHERE notnull_idx IS NOT NULL; -- 可以使用索引 ? ? EXPLAIN SELECT notnull_idx FROM test_idx WHERE notnull_idx IS NOT NULL; ? ? EXPLAIN SELECT * FROM test_idx WHERE normal_idx IS NOT NULL;
5、使用OR且存在非索引列
-- 無(wú)法使用索引 EXPLAIN SELECT * FROM test_idx WHERE id > 1 OR str_col = '1'; -- 使用OR時(shí)，OR包含的所有列必須都是獨(dú)立索引才有可能用到索引
6、使用 NOT IN、IN、IS NULL、IS NOT NULL，且返回值中不止包含條件索引列。
-- 部分情況下可以使用索引? -- 當(dāng)表里沒(méi)有數(shù)據(jù)時(shí)不使用索引 -- 本次測(cè)試當(dāng)后面的條件查詢的返回值大于等于總數(shù)據(jù)50%時(shí)不使用索引;少于總數(shù)據(jù)50%則使用索引 -- 該數(shù)據(jù)不一定準(zhǔn)，這跟數(shù)據(jù)總量、表統(tǒng)計(jì)信息等會(huì)有不同的表現(xiàn)，因此還是得看最終優(yōu)化器的選擇！ ? ? EXPLAIN SELECT * FROM test_idx WHERE normal_idx IN (1,2); ? ? EXPLAIN SELECT * FROM test_idx WHERE normal_idx NOT IN (1,2); ? ? EXPLAIN SELECT * FROM test_idx WHERE normal_idx IS NULL; ? ? EXPLAIN SELECT * FROM test_idx WHERE normal_idx IS NOT NULL;
拓展：
- MySQL環(huán)境變量eq_range_index_dive_limit的值對(duì)IN語(yǔ)法有很大影響，該參數(shù)表示使用索引情況下IN中參數(shù)的最大數(shù)量。MySQL 5.7.3以及之前的版本中，eq_range_index_dive_limit的默認(rèn)值為10，之后的版本默認(rèn)值為200。
- 我們拿MySQL8.0.19舉例，eq_range_index_dive_limit=200表示當(dāng)IN (...)中的值 >200個(gè)時(shí)，該查詢一定不會(huì)走索引。<=200則可能用到索引。
7、使用非主鍵范圍條件查詢時(shí)，部分情況索引失效。
-- 部分情況下可以使用索引? -- 當(dāng)表里沒(méi)有數(shù)據(jù)時(shí)不使用索引 -- 本次測(cè)試當(dāng)范圍條件查詢的返回值大于等于總數(shù)據(jù)50%時(shí)不使用索引;少于總數(shù)據(jù)50%則使用索引 -- 該數(shù)據(jù)不一定準(zhǔn)，這跟數(shù)據(jù)總量、表統(tǒng)計(jì)信息等會(huì)有不同的表現(xiàn)，因此還是得看最終優(yōu)化器的選擇！ ? ? EXPLAIN SELECT * FROM test_idx WHERE normal_idx > 1; ? ? EXPLAIN SELECT * FROM test_idx WHERE normal_idx <= 1; ? ? EXPLAIN SELECT * FROM test_idx WHERE normal_idx BETWEEN 0 AND 1; ? ? EXPLAIN SELECT * FROM test_idx WHERE normal_idx BETWEEN 2 AND 5;
8、MySQL5.7，使用 IS NOT NULL或 IS NULL 部分情況下索引失效。
-- 部分情況下可以使用索引? -- 當(dāng)表里沒(méi)有數(shù)據(jù)時(shí)索引失效 -- 本次測(cè)試條件查詢的返回值大于等于總數(shù)據(jù)50%時(shí)不使用索引;少于總數(shù)據(jù)50%則使用索引 -- 該數(shù)據(jù)不一定準(zhǔn)，這跟數(shù)據(jù)總量、表統(tǒng)計(jì)信息等會(huì)有不同的表現(xiàn)，因此還是得看最終優(yōu)化器的選擇！ ? ? EXPLAIN SELECT normal_idx FROM test_idx WHERE normal_idx IS NOT NULL; ? ? EXPLAIN SELECT normal_idx FROM test_idx WHERE normal_idx IS NULL;
9、MySQL5.7，使用 != 或 IN 或 NOT IN 部分情況下索引失效
-- 部分情況下可以使用索引? -- 本次測(cè)試當(dāng)條件查詢的返回值大于等于總數(shù)據(jù)50%時(shí)不使用索引;少于總數(shù)據(jù)50%則使用索引 -- 該數(shù)據(jù)不一定準(zhǔn)，這跟數(shù)據(jù)總量、表統(tǒng)計(jì)信息等會(huì)有不同的表現(xiàn)，因此還是得看最終優(yōu)化器的選擇！ ? ? EXPLAIN SELECT * FROM test_idx WHERE normal_idx IN (1); ? ? EXPLAIN SELECT * FROM test_idx WHERE normal_idx NOT IN (1); -- 使用 != 也跟數(shù)據(jù)的篩選率有關(guān)，具體數(shù)值不能確定(但肯定篩選率要>50%)。視實(shí)際情況而定，還得看優(yōu)化器的選擇。 -- 可能使用索引，也可能不使用： ?? ? EXPLAIN SELECT * FROM test_idx WHERE normal_idx IN (1); -- 可以使用索引,但是這里不是使用索引去查數(shù)據(jù)，而且是去查索引鍵值。 -- 即先根據(jù)該索引去查其他索引的值，再根據(jù)查出來(lái)的索引值去查數(shù)據(jù)。 -- extra中顯示 Using index 均表示該情況。 ? ? EXPLAIN SELECT normal_idx FROM test_idx WHERE normal_idx IN (1); ? ? EXPLAIN SELECT normal_idx FROM test_idx WHERE normal_idx NOT IN (1);
10、MySQL5.7，表關(guān)聯(lián)時(shí)，關(guān)聯(lián)字段字符集不一致會(huì)導(dǎo)致索引失效。
-- 創(chuàng)建一個(gè)字符集與之前的表不一致的表 ? ? CREATE TABLE `test_idx2` ( ? ? ? `id` int(11) NOT NULL AUTO_INCREMENT, ? ? ? `unique_idx` int(11) DEFAULT NULL, ? ? ? `notnull_idx` int(11) NOT NULL, ? ? ? `str_idx` varchar(20) DEFAULT NULL, ? ? ? `normal_idx` int(11) DEFAULT NULL, ? ? ? `str_col` varchar(10) DEFAULT NULL, ? ? ? PRIMARY KEY (`id`) USING BTREE, ? ? ? UNIQUE KEY `idx_unique_idx` (`unique_idx`), ? ? ? KEY `idx_str_idx` (`str_idx`), ? ? ? KEY `idx_normal_idx` (`normal_idx`) USING BTREE ? ? ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 插入數(shù)據(jù)，如果沒(méi)有數(shù)據(jù)，在MySQL8.0中索引也會(huì)失效 ? ? INSERT INTO test_idx2 values? ? ? (1,1,1,'1',1,'11'), ? ? (2,2,2,'2',2,'22'), ? ? (3,3,3,'3',3,'33'); -- 無(wú)法使用索引 ? ? EXPLAIN SELECT * FROM test_idx t1? ? ? LEFT JOIN test_idx2 t2 ON t1.str_idx = t2.str_idx; -- 子查詢可以使用索引,但是這里不是使用索引去查數(shù)據(jù)，而且是去查索引鍵值。 -- 即先根據(jù)該索引去查其他索引的值，再根據(jù)查出來(lái)的索引值去查數(shù)據(jù)。 -- extra中顯示 Using index 均表示該情況。 ? ? EXPLAIN SELECT * FROM test_idx t1 WHERE t1.id =? ? ? (SELECT id FROM test_idx2 WHERE t1.str_idx = str_idx LIMIT 1);
11、MySQL5.7，表關(guān)聯(lián)時(shí)，關(guān)聯(lián)字段字符集排序規(guī)則不一致會(huì)導(dǎo)致索引失效。
-- 當(dāng)使用字符串類型索引進(jìn)行關(guān)聯(lián)或用于子查詢時(shí)會(huì)報(bào)錯(cuò)： -- 1267 - Illegal mix of collations (utf8mb4_german2_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=' -- 翻譯：操作“=”的排序規(guī)則（utf8mb4_german2_ci，隱式）和（utf8mb4_general_ci，隱式）的非法混合 -- 使用其他類型索引進(jìn)行關(guān)聯(lián)時(shí)，索引失效。 -- 使用其他類型索引進(jìn)行子查詢時(shí)，可以使用索引。 -- 這里就不做舉例了，實(shí)際情況下出現(xiàn)的可能性不大。
多個(gè)獨(dú)立索引
1、使用OR且第一個(gè)條件是范圍查詢，且返回值中不止包含條件索引列。
-- 無(wú)法使用索引 ? ? EXPLAIN SELECT * FROM test_idx WHERE id > 1 OR normal_idx = 1; -- 部分情況下可以使用索引? -- 當(dāng)表里沒(méi)有數(shù)據(jù)時(shí)不使用索引 -- 當(dāng)后面的條件查詢的返回值超過(guò)總數(shù)據(jù)50%時(shí)不使用索引;少于總數(shù)據(jù)50%則使用索引 ? ? EXPLAIN SELECT * FROM test_idx WHERE id = 1 OR normal_idx > 2; ? ?? -- 由于一次查詢中一張表只能使用一個(gè)索引，所以優(yōu)化器是將該sql優(yōu)化成UNION執(zhí)行的。 -- 因此我們可以直接將OR語(yǔ)句改成UNION語(yǔ)句(如果肯定兩個(gè)條件不會(huì)出現(xiàn)重復(fù)返回值，則可以使用UNION ALL，UNOIN在查詢后還需要做一次去重操作，UNOIN ALL則不需要，可以進(jìn)一步提高查詢速度)。 ?? ?EXPLAIN? ?? ?SELECT * FROM test_idx WHERE id = 1 ?? ?UNION ALL ?? ?SELECT * FROM test_idx WHERE normal_idx > 2;
2、 MySQL5.7，使用OR且存在條件是范圍查詢，且返回值中不止包含條件索引列。
-- 無(wú)法使用索引 ? ? EXPLAIN SELECT * FROM test_idx WHERE id > 1 OR normal_idx = 1; ? ? EXPLAIN SELECT * FROM test_idx WHERE id = 1 OR normal_idx > 1;
3、組合索引
-- 創(chuàng)建測(cè)試表 CREATE TABLE test_idx4 ( ? ? id INT ( 11 ) PRIMARY KEY auto_increment, ? ? col1 VARCHAR ( 11 ) DEFAULT NULL, ? ? col2 VARCHAR ( 11 ) DEFAULT NULL, ? ? col3 VARCHAR ( 11 ) DEFAULT NULL, ? ? col4 VARCHAR ( 11 ) DEFAULT NULL, ? ? KEY idx_col1_col2_col3( col1, col2, col3 ) USING BTREE? ); -- 插入測(cè)試數(shù)據(jù) INSERT INTO test_idx4 VALUES ?? ?( 1, '1', '1', '1', '1' ), ?? ?( 2, '1', '1', '1', '1' ), ?? ?( 3, '1', '1', '1', '1' ), ?? ?( 4, '1', '1', '1', '1' ); ? ?-- 最左匹配原則：只要查詢條件中帶有組合索引最左邊的列（此處即 col1） , 那么查詢就會(huì)使用到索引。 ?-- 所以想讓組合索引失效很簡(jiǎn)單，條件中不包含索引最左邊的列（此處即 col1）, 則索引失效。
擴(kuò)展：
SELECT col2,col3 FROM test_idx4 SELECT col2,col3 FROM test_idx4 WHERE col2 = '1' AND col3='2'
- 以上兩條SQL是可以使用到索引的，原理就是上面提到的索引覆蓋，
- 雖然根據(jù)最左匹配原則是沒(méi)法使用索引去快速檢索數(shù)據(jù)的；但是因?yàn)樵摬樵冎兴樵兊牧惺莄ol1,col2，而該索引只包含col1,col2,col3三個(gè)字段信息，而主鍵索引中包含所有字段信息，用該索引做全表掃描的效率更高，所以還是會(huì)使用到該索引！
總結(jié)
上述所有可能用到可能沒(méi)用到索引的情況，并不是一定的！導(dǎo)致索引失效的閾值也不一定100%準(zhǔn)確，畢竟不同數(shù)據(jù)類型、不同數(shù)據(jù)量的情況下，MySQL的優(yōu)化器的選擇可能不同。但可以肯定的是，同樣一條語(yǔ)句，可能由于篩選率等原因?qū)е滤饕А?/p>
所有簡(jiǎn)單查詢(執(zhí)行計(jì)劃中 select_type = simple)，只要where條件中有索引列(無(wú)論什么條件)，且返回值中只包含該索引列(和主鍵)，都會(huì)用到索引。根據(jù)執(zhí)行計(jì)劃中的extra可以區(qū)分索引的用途：
1、extra = Using index，表示索引覆蓋。
2、extra = Using index, Using where，表示存在回表操作。
拓展：
為什么只要返回值只包含索引和主鍵就會(huì)用到索引？
眾所周知，InnoDB保存數(shù)據(jù)是通過(guò)B+樹結(jié)構(gòu)存儲(chǔ)的。且只有主鍵索引所在的B+樹的葉子節(jié)點(diǎn)會(huì)保存實(shí)際數(shù)據(jù)，其他節(jié)點(diǎn)只保存主鍵值，這種數(shù)據(jù)與索引在一起的索引我們稱之為聚簇索引。
二級(jí)索引(非主鍵索引)的所有節(jié)點(diǎn)除了保存索引列的值外還會(huì)保存主鍵的值。
所以當(dāng)我們通過(guò)二級(jí)索引查詢數(shù)據(jù)時(shí)，第一步先通過(guò)二級(jí)索引查詢到對(duì)應(yīng)的主鍵值；再通過(guò)主鍵值到主鍵索引中查詢對(duì)應(yīng)的實(shí)際數(shù)據(jù)，這個(gè)過(guò)程我們稱之為回表。
而回表操作是隨機(jī)IO，所以性能較差，當(dāng)需要回表的數(shù)據(jù)量比較大時(shí)，優(yōu)化器可能就會(huì)選擇不走索引，直接全表掃描，因?yàn)樽呷硎琼樞騃O，指不定走全表比走索引還快。(這也解釋了為什么同樣的SQL，表數(shù)據(jù)不同查詢策略也不同)
其中一個(gè)特殊情況是當(dāng)我們的查詢只涉及到索引列和主鍵的時(shí)候，我們就不需要再回表查詢實(shí)際數(shù)據(jù)了，因?yàn)槎?jí)索引中保存了主鍵和索引列的數(shù)據(jù)，這個(gè)時(shí)候就肯定會(huì)走索引了。
在復(fù)制其他地方提供的sql建表腳本時(shí)，注意其字符集和排序規(guī)則是否跟自己數(shù)據(jù)庫(kù)默認(rèn)的一致，否則可能出現(xiàn)索引失效的問(wèn)題。
不同版本不同情況下，索引的使用情況不一致。上文提到的可能使用可能不使用的情況是由MySQL的優(yōu)化器決定的，可能還會(huì)有其他情況下優(yōu)化器也不使用索引，此時(shí)我們可以強(qiáng)制指定需要使用的索引：
-- 通過(guò) force index(IDX_NAME) 強(qiáng)制指定索引 EXPLAIN SELECT * FROM test_idx force index (idx_notnull_idx) WHERE notnull_idx BETWEEN 1 AND 2;
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
這篇文章主要為大家詳細(xì)介紹了CentOS 7下使用rpm包安裝mysql 5.7.18的教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-07-07
mysql數(shù)據(jù)庫(kù)視圖和執(zhí)行計(jì)劃實(shí)戰(zhàn)案例
這篇文章主要給大家介紹了關(guān)于mysql數(shù)據(jù)庫(kù)視圖和執(zhí)行計(jì)劃的相關(guān)資料,在使用MySQL過(guò)程中視圖和執(zhí)行計(jì)劃是一個(gè)很好的工具,文中通過(guò)圖文以及代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-02-02
本篇文章是對(duì)mysql多實(shí)例安裝進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
MySQL客戶端軟件(DBeaver)連接報(bào)錯(cuò)解決方案
DBeaver是一個(gè)開源的通用數(shù)據(jù)庫(kù)管理工具,可以連接多種不同類型的數(shù)據(jù)庫(kù),本文主要介紹了MySQL客戶端軟件(DBeaver)連接報(bào)錯(cuò)解決方案,具有一定的參考價(jià)值,感興趣的可以了解一下2024-04-04
解決mysql連接超時(shí)和mysql連接錯(cuò)誤的問(wèn)題
這篇文章主要介紹了解決mysql連接超時(shí)和mysql連接錯(cuò)誤的問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-07-07
python中的mysql數(shù)據(jù)庫(kù)LIKE操作符詳解
LIKE操作符用于在WHERE子句中搜索列中的指定模式，like操作符的語(yǔ)法在文章開頭也給大家提到，通過(guò)兩種示例代碼給大家介紹python中的mysql數(shù)據(jù)庫(kù)LIKE操作符知識(shí)，感興趣的朋友跟隨小編一起看看吧2021-07-07
MySQL刪除外鍵、增加外鍵以及刪除主鍵、增加主鍵的實(shí)戰(zhàn)步驟
在Mysql中使用外鍵,可以有效地將多個(gè)表格的數(shù)據(jù)"聯(lián)動(dòng)"起來(lái),打破獨(dú)立狀態(tài),互相影響,下面這篇文章主要給大家介紹了關(guān)于MySQL刪除外鍵、增加外鍵以及刪除主鍵、增加主鍵的相關(guān)資料,需要的朋友可以參考下2022-08-08
MySQL中的數(shù)據(jù)備份與SQL語(yǔ)句
這篇文章主要介紹了MySQL中的數(shù)據(jù)備份與SQL語(yǔ)句，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-05-05
在MySQL中按字符串中的數(shù)字排序的詳細(xì)教程
本文將詳細(xì)介紹如何在MySQL中利用正則表達(dá)式提取字符串中的數(shù)字并按這些數(shù)字進(jìn)行排序,以一個(gè)具體的例子來(lái)說(shuō)明,使得即使是數(shù)據(jù)庫(kù)操作的初學(xué)者也能輕松理解和應(yīng)用,需要的朋友可以參考下2024-07-07
MYSQL中常用的強(qiáng)制性操作（例如強(qiáng)制索引）
對(duì)于經(jīng)常使用oracle的朋友可能知道，oracle的hint功能種類很多，對(duì)于優(yōu)化sql語(yǔ)句提供了很多方法。同樣，在mysql里，也有類似的hint功能。2014-05-05