關(guān)于Mysql5.7及8.0版本索引失效情況匯總
更新時間：2022年08月25日 15:44:39 作者：keep丶
這篇文章主要介紹了關(guān)于Mysql5.7及8.0版本索引失效情況匯總，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
TIPS：
- 沒有特殊說明，測試環(huán)境均為MySQL8.0，早期版本可能會有更多情況導(dǎo)致索引失效。8.0失效的情況，早期版本也失效；8.0不失效的情況，早期版本可能失效。
- 所有測試默認(rèn)不考慮表為空的情況，特殊情況文中會有說明。
- 本文只介紹Innodb引擎下的索引失效情況。
-- 創(chuàng)建測試表 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; -- 插入幾條測試數(shù)據(jù)，因為當(dāng)表里沒有數(shù)據(jù)時，部分使用到索引的情況會失效。 INSERT INTO test_idx VALUES (1,1,'1',1,'111'), (2,2,'2',2,'222'), (3,3,'3',3,'333')
一個獨立索引
1、使用like且在左邊有“%”。
-- 無法使用索引 ? ? 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)字段的類型不一致。
-- 無法使用索引 ?? ?EXPLAIN select * from test_idx where bid = 1; -- 可以使用索引 ?? ?EXPLAIN select * from test_idx where bid = '1';
3、條件中對索引列進(jìn)行運算或使用函數(shù)
-- 無法使用索引 ? ? 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)查詢列只有該索引列時會使用索引
-- 無法使用索引 ? ? 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且存在非索引列
-- 無法使用索引 EXPLAIN SELECT * FROM test_idx WHERE id > 1 OR str_col = '1'; -- 使用OR時，OR包含的所有列必須都是獨立索引才有可能用到索引
6、使用 NOT IN、IN、IS NULL、IS NOT NULL，且返回值中不止包含條件索引列。
-- 部分情況下可以使用索引? -- 當(dāng)表里沒有數(shù)據(jù)時不使用索引 -- 本次測試當(dāng)后面的條件查詢的返回值大于等于總數(shù)據(jù)50%時不使用索引;少于總數(shù)據(jù)50%則使用索引 -- 該數(shù)據(jù)不一定準(zhǔn)，這跟數(shù)據(jù)總量、表統(tǒng)計信息等會有不同的表現(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的值對IN語法有很大影響，該參數(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個時，該查詢一定不會走索引。<=200則可能用到索引。
7、使用非主鍵范圍條件查詢時，部分情況索引失效。
-- 部分情況下可以使用索引? -- 當(dāng)表里沒有數(shù)據(jù)時不使用索引 -- 本次測試當(dāng)范圍條件查詢的返回值大于等于總數(shù)據(jù)50%時不使用索引;少于總數(shù)據(jù)50%則使用索引 -- 該數(shù)據(jù)不一定準(zhǔn)，這跟數(shù)據(jù)總量、表統(tǒng)計信息等會有不同的表現(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)表里沒有數(shù)據(jù)時索引失效 -- 本次測試條件查詢的返回值大于等于總數(shù)據(jù)50%時不使用索引;少于總數(shù)據(jù)50%則使用索引 -- 該數(shù)據(jù)不一定準(zhǔn)，這跟數(shù)據(jù)總量、表統(tǒng)計信息等會有不同的表現(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 部分情況下索引失效
-- 部分情況下可以使用索引? -- 本次測試當(dāng)條件查詢的返回值大于等于總數(shù)據(jù)50%時不使用索引;少于總數(shù)據(jù)50%則使用索引 -- 該數(shù)據(jù)不一定準(zhǔn)，這跟數(shù)據(jù)總量、表統(tǒng)計信息等會有不同的表現(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%)。視實際情況而定，還得看優(yōu)化器的選擇。 -- 可能使用索引，也可能不使用： ?? ? EXPLAIN SELECT * FROM test_idx WHERE normal_idx IN (1); -- 可以使用索引,但是這里不是使用索引去查數(shù)據(jù)，而且是去查索引鍵值。 -- 即先根據(jù)該索引去查其他索引的值，再根據(jù)查出來的索引值去查數(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)時，關(guān)聯(lián)字段字符集不一致會導(dǎo)致索引失效。
-- 創(chuàng)建一個字符集與之前的表不一致的表 ? ? 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ù)，如果沒有數(shù)據(jù)，在MySQL8.0中索引也會失效 ? ? INSERT INTO test_idx2 values? ? ? (1,1,1,'1',1,'11'), ? ? (2,2,2,'2',2,'22'), ? ? (3,3,3,'3',3,'33'); -- 無法使用索引 ? ? EXPLAIN SELECT * FROM test_idx t1? ? ? LEFT JOIN test_idx2 t2 ON t1.str_idx = t2.str_idx; -- 子查詢可以使用索引,但是這里不是使用索引去查數(shù)據(jù)，而且是去查索引鍵值。 -- 即先根據(jù)該索引去查其他索引的值，再根據(jù)查出來的索引值去查數(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)時，關(guān)聯(lián)字段字符集排序規(guī)則不一致會導(dǎo)致索引失效。
-- 當(dāng)使用字符串類型索引進(jìn)行關(guān)聯(lián)或用于子查詢時會報錯： -- 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)時，索引失效。 -- 使用其他類型索引進(jìn)行子查詢時，可以使用索引。 -- 這里就不做舉例了，實際情況下出現(xiàn)的可能性不大。
多個獨立索引
1、使用OR且第一個條件是范圍查詢，且返回值中不止包含條件索引列。
-- 無法使用索引 ? ? EXPLAIN SELECT * FROM test_idx WHERE id > 1 OR normal_idx = 1; -- 部分情況下可以使用索引? -- 當(dāng)表里沒有數(shù)據(jù)時不使用索引 -- 當(dāng)后面的條件查詢的返回值超過總數(shù)據(jù)50%時不使用索引;少于總數(shù)據(jù)50%則使用索引 ? ? EXPLAIN SELECT * FROM test_idx WHERE id = 1 OR normal_idx > 2; ? ?? -- 由于一次查詢中一張表只能使用一個索引，所以優(yōu)化器是將該sql優(yōu)化成UNION執(zhí)行的。 -- 因此我們可以直接將OR語句改成UNION語句(如果肯定兩個條件不會出現(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且存在條件是范圍查詢，且返回值中不止包含條件索引列。
-- 無法使用索引 ? ? 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)建測試表 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? ); -- 插入測試數(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） , 那么查詢就會使用到索引。 ?-- 所以想讓組合索引失效很簡單，條件中不包含索引最左邊的列（此處即 col1）, 則索引失效。
擴展：
SELECT col2,col3 FROM test_idx4 SELECT col2,col3 FROM test_idx4 WHERE col2 = '1' AND col3='2'
- 以上兩條SQL是可以使用到索引的，原理就是上面提到的索引覆蓋，
- 雖然根據(jù)最左匹配原則是沒法使用索引去快速檢索數(shù)據(jù)的；但是因為該查詢中所查詢的列是col1,col2，而該索引只包含col1,col2,col3三個字段信息，而主鍵索引中包含所有字段信息，用該索引做全表掃描的效率更高，所以還是會使用到該索引！
總結(jié)
上述所有可能用到可能沒用到索引的情況，并不是一定的！導(dǎo)致索引失效的閾值也不一定100%準(zhǔn)確，畢竟不同數(shù)據(jù)類型、不同數(shù)據(jù)量的情況下，MySQL的優(yōu)化器的選擇可能不同。但可以肯定的是，同樣一條語句，可能由于篩選率等原因?qū)е滤饕А?/p>
所有簡單查詢(執(zhí)行計劃中 select_type = simple)，只要where條件中有索引列(無論什么條件)，且返回值中只包含該索引列(和主鍵)，都會用到索引。根據(jù)執(zhí)行計劃中的extra可以區(qū)分索引的用途：
1、extra = Using index，表示索引覆蓋。
2、extra = Using index, Using where，表示存在回表操作。
拓展：
為什么只要返回值只包含索引和主鍵就會用到索引？
眾所周知，InnoDB保存數(shù)據(jù)是通過B+樹結(jié)構(gòu)存儲的。且只有主鍵索引所在的B+樹的葉子節(jié)點會保存實際數(shù)據(jù)，其他節(jié)點只保存主鍵值，這種數(shù)據(jù)與索引在一起的索引我們稱之為聚簇索引。
二級索引(非主鍵索引)的所有節(jié)點除了保存索引列的值外還會保存主鍵的值。
所以當(dāng)我們通過二級索引查詢數(shù)據(jù)時，第一步先通過二級索引查詢到對應(yīng)的主鍵值；再通過主鍵值到主鍵索引中查詢對應(yīng)的實際數(shù)據(jù)，這個過程我們稱之為回表。
而回表操作是隨機IO，所以性能較差，當(dāng)需要回表的數(shù)據(jù)量比較大時，優(yōu)化器可能就會選擇不走索引，直接全表掃描，因為走全表是順序IO，指不定走全表比走索引還快。(這也解釋了為什么同樣的SQL，表數(shù)據(jù)不同查詢策略也不同)
其中一個特殊情況是當(dāng)我們的查詢只涉及到索引列和主鍵的時候，我們就不需要再回表查詢實際數(shù)據(jù)了，因為二級索引中保存了主鍵和索引列的數(shù)據(jù)，這個時候就肯定會走索引了。
在復(fù)制其他地方提供的sql建表腳本時，注意其字符集和排序規(guī)則是否跟自己數(shù)據(jù)庫默認(rèn)的一致，否則可能出現(xiàn)索引失效的問題。
不同版本不同情況下，索引的使用情況不一致。上文提到的可能使用可能不使用的情況是由MySQL的優(yōu)化器決定的，可能還會有其他情況下優(yōu)化器也不使用索引，此時我們可以強制指定需要使用的索引：
-- 通過 force index(IDX_NAME) 強制指定索引 EXPLAIN SELECT * FROM test_idx force index (idx_notnull_idx) WHERE notnull_idx BETWEEN 1 AND 2;
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關(guān)文章
這篇文章主要介紹了MYSQL之on和where的區(qū)別解讀，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-03-03
MySQL中on?duplicate?key?update的使用方法實例
在做數(shù)據(jù)統(tǒng)計的時候,我們經(jīng)常會用到mysql的on duplicate key update語法來自動更新數(shù)據(jù),下面這篇文章主要給大家介紹了關(guān)于MySQL中on?duplicate?key?update的使用方法的相關(guān)資料,需要的朋友可以參考下2022-09-09
本文主要介紹了MySQL深分頁問題及三種解決方案，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-06-06
MySQL函數(shù)講解(MySQL函數(shù)大全)
MySQL函數(shù)大全和函數(shù)講解，管理MYSQL數(shù)據(jù)一定會用到2013-11-11
這篇文章主要介紹了Mysql如何查看表的索引問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12
深入解析MySQL索引數(shù)據(jù)結(jié)構(gòu)
什么是索引?索引就是排好序的數(shù)據(jù)結(jié)構(gòu),可以幫助我們快速的查找到數(shù)據(jù),下面這篇文章主要給大家介紹了關(guān)于MySQL索引數(shù)據(jù)結(jié)構(gòu)的相關(guān)資料,需要的朋友可以參考下2021-10-10
mysql8.0 windows x64 zip包安裝配置教程
這篇文章主要為大家詳細(xì)介紹了mysql8.0 windows x64 zip包安裝配置教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-05-05
相信大家使用MySQL都有過重裝的經(jīng)歷，要是重裝MySQL基本都是在最后一步通不過，除非重裝操作系統(tǒng)，究其原因就是系統(tǒng)里的注冊表沒有刪除干凈2012-11-11
這篇文章主要介紹了如何實現(xiàn)MySQL的索引，MySQL中索引分三類，有B+樹索引、Hash索引和全文索引，下面我們一起來看看MySQL索引的具體實現(xiàn)，需要的小伙伴可以參考一下2022-01-01
Mysql 直接查詢存儲的Json字符串中的數(shù)據(jù)
本文主要介紹了Mysql直接查詢存儲的Json字符串中的數(shù)據(jù)，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-02-02