關於Mysql5.7及8.0版本索引失效情況彙總

2022-08-25 22:01:43

沒有特殊說明，測試環境均為MySQL8.0，早期版本可能會有更多情況導致索引失效。8.0失效的情況，早期版本也失效；8.0不失效的情況，早期版本可能失效。
所有測試預設不考慮表為空的情況，特殊情況文中會有說明。
本文只介紹Innodb引擎下的索引失效情況。

-- 建立測試表
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、隱式型別轉換，索引欄位與條件或關聯欄位的型別不一致。

-- 無法使用索引
    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%則使用索引
-- 該資料不一定準，這跟資料總量、表統計資訊等會有不同的表現，因此還是得看最終優化器的選擇！
    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環境變數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%則使用索引
-- 該資料不一定準，這跟資料總量、表統計資訊等會有不同的表現，因此還是得看最終優化器的選擇！
    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%則使用索引
-- 該資料不一定準，這跟資料總量、表統計資訊等會有不同的表現，因此還是得看最終優化器的選擇！
    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%則使用索引
-- 該資料不一定準，這跟資料總量、表統計資訊等會有不同的表現，因此還是得看最終優化器的選擇！
    EXPLAIN SELECT * FROM test_idx WHERE normal_idx IN (1);
    EXPLAIN SELECT * FROM test_idx WHERE normal_idx NOT IN (1);

-- 使用 != 也跟資料的篩選率有關，具體數值不能確定(但肯定篩選率要>50%)。視實際情況而定，還得看優化器的選擇。
-- 可能使用索引，也可能不使用：
     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，表關聯時，關聯欄位字元集不一致會導致索引失效。

-- 建立一個字元集與之前的表不一致的表
    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，表關聯時，關聯欄位字元集排序規則不一致會導致索引失效。

-- 當使用字串型別索引進行關聯或用於子查詢時會報錯：
-- 1267 - Illegal mix of collations (utf8mb4_german2_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
-- 翻譯：操作「=」的排序規則（utf8mb4_german2_ci，隱式）和（utf8mb4_general_ci，隱式）的非法混合

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

多個獨立索引

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;
    
-- 由於一次查詢中一張表只能使用一個索引，所以優化器是將該sql優化成UNION執行的。
-- 因此我們可以直接將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、組合索引

-- 建立測試表
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的優化器的選擇可能不同。但可以肯定的是，同樣一條語句，可能由於篩選率等原因導致索引失效。

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

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

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

拓展：

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

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

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

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

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

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

在複製其他地方提供的sql建表指令碼時，注意其字元集和排序規則是否跟自己資料庫預設的一致，否則可能出現索引失效的問題。

不同版本不同情況下，索引的使用情況不一致。上文提到的可能使用可能不使用的情況是由MySQL的優化器決定的，可能還會有其他情況下優化器也不使用索引，此時我們可以強制指定需要使用的索引：

-- 通過 force index(IDX_NAME) 強制指定索引
EXPLAIN SELECT * FROM test_idx force index (idx_notnull_idx) WHERE notnull_idx BETWEEN 1 AND 2;

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。

關於Mysql5.7及8.0版本索引失效情況彙總

目錄

一個獨立索引

多個獨立索引

總結

熱門文章