關于Mysql5.7及8.0版本索引失效情況匯總

更新時間：2022年08月25日 15:44:39 作者：keep丶

這篇文章主要介紹了關于Mysql5.7及8.0版本索引失效情況匯總，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

沒有特殊說明，測試環(huán)境均為MySQL8.0，早期版本可能會有更多情況導致索引失效。8.0失效的情況，早期版本也失效；8.0不失效的情況，早期版本可能失效。
所有測試默認不考慮表為空的情況，特殊情況文中會有說明。
本文只介紹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;

-- 插入幾條測試數據，因為當表里沒有數據時，部分使用到索引的情況會失效。
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、隱式類型轉換，索引字段與條件或關聯(lián)字段的類型不一致。

-- 無法使用索引
?? ?EXPLAIN select * from test_idx where bid = 1;
-- 可以使用索引
?? ?EXPLAIN select * from test_idx where bid = '1';

3、條件中對索引列進行運算或使用函數

-- 無法使用索引
? ? 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，僅當查詢列只有該索引列時會使用索引

-- 無法使用索引
? ? 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，且返回值中不止包含條件索引列。

-- 部分情況下可以使用索引?
-- 當表里沒有數據時不使用索引
-- 本次測試當后面的條件查詢的返回值大于等于總數據50%時不使用索引;少于總數據50%則使用索引
-- 該數據不一定準，這跟數據總量、表統(tǒng)計信息等會有不同的表現，因此還是得看最終優(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語法有很大影響，該參數表示使用索引情況下IN中參數的最大數量。MySQL 5.7.3以及之前的版本中，eq_range_index_dive_limit的默認值為10，之后的版本默認值為200。
我們拿MySQL8.0.19舉例，eq_range_index_dive_limit=200表示當IN (...)中的值 >200個時，該查詢一定不會走索引。<=200則可能用到索引。

7、使用非主鍵范圍條件查詢時，部分情況索引失效。

-- 部分情況下可以使用索引?
-- 當表里沒有數據時不使用索引
-- 本次測試當范圍條件查詢的返回值大于等于總數據50%時不使用索引;少于總數據50%則使用索引
-- 該數據不一定準，這跟數據總量、表統(tǒng)計信息等會有不同的表現，因此還是得看最終優(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 部分情況下索引失效。

-- 部分情況下可以使用索引?
-- 當表里沒有數據時索引失效
-- 本次測試條件查詢的返回值大于等于總數據50%時不使用索引;少于總數據50%則使用索引
-- 該數據不一定準，這跟數據總量、表統(tǒng)計信息等會有不同的表現，因此還是得看最終優(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 部分情況下索引失效

-- 部分情況下可以使用索引?
-- 本次測試當條件查詢的返回值大于等于總數據50%時不使用索引;少于總數據50%則使用索引
-- 該數據不一定準，這跟數據總量、表統(tǒng)計信息等會有不同的表現，因此還是得看最終優(yōu)化器的選擇！
? ? EXPLAIN SELECT * FROM test_idx WHERE normal_idx IN (1);
? ? EXPLAIN SELECT * FROM test_idx WHERE normal_idx NOT IN (1);

-- 使用 != 也跟數據的篩選率有關，具體數值不能確定(但肯定篩選率要>50%)。視實際情況而定，還得看優(yōu)化器的選擇。
-- 可能使用索引，也可能不使用：
?? ? EXPLAIN SELECT * FROM test_idx WHERE normal_idx IN (1);

-- 可以使用索引,但是這里不是使用索引去查數據，而且是去查索引鍵值。
-- 即先根據該索引去查其他索引的值，再根據查出來的索引值去查數據。
-- 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，表關聯(lián)時，關聯(lián)字段字符集不一致會導致索引失效。

-- 創(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;
-- 插入數據，如果沒有數據，在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;

-- 子查詢可以使用索引,但是這里不是使用索引去查數據，而且是去查索引鍵值。
-- 即先根據該索引去查其他索引的值，再根據查出來的索引值去查數據。
-- 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，表關聯(lián)時，關聯(lián)字段字符集排序規(guī)則不一致會導致索引失效。

-- 當使用字符串類型索引進行關聯(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，隱式）的非法混合

-- 使用其他類型索引進行關聯(lián)時，索引失效。
-- 使用其他類型索引進行子查詢時，可以使用索引。
-- 這里就不做舉例了，實際情況下出現的可能性不大。

多個獨立索引

1、使用OR且第一個條件是范圍查詢，且返回值中不止包含條件索引列。

-- 無法使用索引
? ? EXPLAIN SELECT * FROM test_idx WHERE id > 1 OR normal_idx = 1;
-- 部分情況下可以使用索引?
-- 當表里沒有數據時不使用索引
-- 當后面的條件查詢的返回值超過總數據50%時不使用索引;少于總數據50%則使用索引
? ? EXPLAIN SELECT * FROM test_idx WHERE id = 1 OR normal_idx > 2;
? ??
-- 由于一次查詢中一張表只能使用一個索引，所以優(yōu)化器是將該sql優(yōu)化成UNION執(zhí)行的。
-- 因此我們可以直接將OR語句改成UNION語句(如果肯定兩個條件不會出現重復返回值，則可以使用UNION ALL，UNOIN在查詢后還需要做一次去重操作，UNOIN ALL則不需要，可以進一步提高查詢速度)。
?? ?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?
);
-- 插入測試數據
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是可以使用到索引的，原理就是上面提到的索引覆蓋，
雖然根據最左匹配原則是沒法使用索引去快速檢索數據的；但是因為該查詢中所查詢的列是col1,col2，而該索引只包含col1,col2,col3三個字段信息，而主鍵索引中包含所有字段信息，用該索引做全表掃描的效率更高，所以還是會使用到該索引！

總結

上述所有可能用到可能沒用到索引的情況，并不是一定的！導致索引失效的閾值也不一定100%準確，畢竟不同數據類型、不同數據量的情況下，MySQL的優(yōu)化器的選擇可能不同。但可以肯定的是，同樣一條語句，可能由于篩選率等原因導致索引失效。

所有簡單查詢(執(zhí)行計劃中 select_type = simple)，只要where條件中有索引列(無論什么條件)，且返回值中只包含該索引列(和主鍵)，都會用到索引。根據執(zhí)行計劃中的extra可以區(qū)分索引的用途：

1、extra = Using index，表示索引覆蓋。

2、extra = Using index, Using where，表示存在回表操作。

拓展：

為什么只要返回值只包含索引和主鍵就會用到索引？

眾所周知，InnoDB保存數據是通過B+樹結構存儲的。且只有主鍵索引所在的B+樹的葉子節(jié)點會保存實際數據，其他節(jié)點只保存主鍵值，這種數據與索引在一起的索引我們稱之為聚簇索引。

二級索引(非主鍵索引)的所有節(jié)點除了保存索引列的值外還會保存主鍵的值。

所以當我們通過二級索引查詢數據時，第一步先通過二級索引查詢到對應的主鍵值；再通過主鍵值到主鍵索引中查詢對應的實際數據，這個過程我們稱之為回表。

而回表操作是隨機IO，所以性能較差，當需要回表的數據量比較大時，優(yōu)化器可能就會選擇不走索引，直接全表掃描，因為走全表是順序IO，指不定走全表比走索引還快。(這也解釋了為什么同樣的SQL，表數據不同查詢策略也不同)

其中一個特殊情況是當我們的查詢只涉及到索引列和主鍵的時候，我們就不需要再回表查詢實際數據了，因為二級索引中保存了主鍵和索引列的數據，這個時候就肯定會走索引了。

在復制其他地方提供的sql建表腳本時，注意其字符集和排序規(guī)則是否跟自己數據庫默認的一致，否則可能出現索引失效的問題。

不同版本不同情況下，索引的使用情況不一致。上文提到的可能使用可能不使用的情況是由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;

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章:

MYSQL之on和where的區(qū)別解讀
這篇文章主要介紹了MYSQL之on和where的區(qū)別解讀，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
2023-03-03
MySQL中on?duplicate?key?update的使用方法實例
在做數據統(tǒng)計的時候,我們經常會用到mysql的on duplicate key update語法來自動更新數據,下面這篇文章主要給大家介紹了關于MySQL中on?duplicate?key?update的使用方法的相關資料,需要的朋友可以參考下
2022-09-09
MySQL深分頁問題及三種解決方案
本文主要介紹了MySQL深分頁問題及三種解決方案，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2023-06-06
MySQL函數講解(MySQL函數大全)
MySQL函數大全和函數講解，管理MYSQL數據一定會用到
2013-11-11
Mysql如何查看表的索引
這篇文章主要介紹了Mysql如何查看表的索引問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
2023-12-12
深入解析MySQL索引數據結構
什么是索引?索引就是排好序的數據結構,可以幫助我們快速的查找到數據,下面這篇文章主要給大家介紹了關于MySQL索引數據結構的相關資料,需要的朋友可以參考下
2021-10-10
mysql8.0 windows x64 zip包安裝配置教程
這篇文章主要為大家詳細介紹了mysql8.0 windows x64 zip包安裝配置教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2018-05-05
如何解決mysql重裝失敗方法介紹
相信大家使用MySQL都有過重裝的經歷，要是重裝MySQL基本都是在最后一步通不過，除非重裝操作系統(tǒng)，究其原因就是系統(tǒng)里的注冊表沒有刪除干凈
2012-11-11
如何實現MySQL的索引
這篇文章主要介紹了如何實現MySQL的索引，MySQL中索引分三類，有B+樹索引、Hash索引和全文索引，下面我們一起來看看MySQL索引的具體實現，需要的小伙伴可以參考一下
2022-01-01
Mysql 直接查詢存儲的Json字符串中的數據
本文主要介紹了Mysql直接查詢存儲的Json字符串中的數據，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2022-02-02