MySql索引失效(版本8.0.18)

一、MySql的索引

1、索引失效问题

（1）总结

MySql在版本8.0.18中，导致索引失效的原因有

• 模糊查询时，前模糊导致索引失效

• 对列使用函数操作，导致索引失效

• 使用<>、!=导致索引失效

• 当字段是字符串类型，传入数字类型导致索引失效

• 复合索引时，条件1失效或者是不存在，导致索引失效

MySql在版本5.6中，导致索引失效的原因额外增加了几种

• 使用 IN 导致索引失效

• 使用 OR 导致索引失效

（2）普通索引

1. 新建一张测试表

   CREATE TABLE `Test` (
     `id` bigint(20) NOT NULL AUTO_INCREMENT,
     `column1` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
     `column2` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
     `column3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
     `column4` varchar(10000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
     `column5` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
     `varcharKey` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
     `bigintKey` bigint(20) NOT NULL,
     PRIMARY KEY (`id`),
     KEY `idx_varcharKey` (`varcharKey`) USING BTREE,
     KEY `idx_bigintKey` (`bigintKey`) USING BTREE
   ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

    

2. 使用or查询并未导致索引失效？

   #使用or查询并未导致索引失效？
   EXPLAIN
   SELECT * FROM Test WHERE varcharKey = '2019-12-21' or varcharKey = '2019-12-22';

   

3. 使用模糊前缀，索引失效

   #使用模糊前缀，索引失效
   EXPLAIN
   SELECT * FROM Test WHERE varcharKey like '%2019-12-21';

   

4. 使用函数操作列，索引失效

   #使用函数操作，索引失效
   EXPLAIN
   SELECT * FROM Test WHERE bigintKey + 1 = 64967499;

   

5. 使用<>、!=操作，索引失效

   #使用<>、!=操作，索引失效
   EXPLAIN
   SELECT *     FROM Test WHERE bigintKey <> 64967499;
   EXPLAIN
   SELECT *     FROM Test WHERE bigintKey != 64967499;

   

6. 当数据库字段是字符类型，传入数字，索引失效
   当数据库字段是数字，传入字符，索引未失效

   #当数据库字段是字符类型，传入数字，索引失效
   #当数据库字段是数字，传入字符，索引未失效
   EXPLAIN
   SELECT *     FROM Test WHERE varcharKey = 64967499;
   EXPLAIN
   SELECT *     FROM Test WHERE bigintKey = '64967499';

   

（3）复合索引

1. 新建一张测试表

   CREATE TABLE `Test2` (
     `id` bigint(20) NOT NULL AUTO_INCREMENT,
     `column1` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
     `column2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
     `column3` varchar(10000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
     `column4` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
     `varcharKey` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
     `varcharKey2` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
     `bigintKey` bigint(20) NOT NULL,
     PRIMARY KEY (`id`),
     KEY `idx_varcharKey1_varcharKey2_bigintKey` (`varcharKey`,`varcharKey2`,`bigintKey`) USING BTREE
   ) ENGINE=InnoDB AUTO_INCREMENT=233168 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

    

2. 缺少条件1不会走索引

   #缺少条件1不会走索引
   EXPLAIN
   SELECT *     FROM Test2 WHERE varcharKey = '64967499';
   EXPLAIN
   SELECT *     FROM Test2 WHERE varcharKey2 = '64967499';
   EXPLAIN
   SELECT *     FROM Test2 WHERE bigintKey = 64967499;

   

3. 条件1索引失效，不会走索引

   #条件1索引失效，不会走索引
   EXPLAIN
   SELECT * 	FROM Test2 WHERE varcharKey <> '64967499' and varcharKey2 = '64967499' and bigintKey = 64967499;
   EXPLAIN
   SELECT * 	FROM Test2 WHERE varcharKey = '64967499' and varcharKey2 <> '64967499' and bigintKey = 64967499;
   EXPLAIN
   SELECT * 	FROM Test2 WHERE varcharKey = '64967499' and varcharKey2 = '64967499' and bigintKey <> 64967499;

   

二、 Mysql版本5.6

1、索引失效

（1）使用 OR 导致索引失效

EXPLAIN
SELECT * FROM Test WHERE varcharKey = '2019-12-21' or varcharKey = '2019-12-22';

（2）使用 IN 导致索引失效

EXPLAIN
SELECT * FROM test WHERE bigintKey IN (1,4);