关于Mysql5.7及8.0版本索引失效情况汇总

TIPS：

• 没有特殊说明，测试环境均为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）, 则索引失效。

扩展：&emsp;&emsp;

SELECT col2,col3 FROM test_idx4
SELECT col2,col3 FROM test_idx4 WHERE col2 = '1' AND col3='2'

• 以上两条SQL是可以使用到索引的，原理就是上面提到的索引覆盖，

• 虽然根据最左匹配原则是没法使用索引去快速检索数据的；但是因为该查询中所查询的列是col1,col2，而该索引只包含col1,col2,col3三个字段信息，而主键索引中包含所有字段信息，用该索引做全表扫描的效率更高，所以还是会使用到该索引！

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

来源：https://blog.csdn.net/qq_38688267/article/details/104900507