【MySQL优化】索引失效的场景
目录
简介
发生索引失效的情况：
-
当我们使用左或者左右模糊匹配的时候，即
like %xx或者like %xx%这两种方式都会造成索引失效； -
当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
-
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
-
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。（ OR 的条件中包含非索引列）
索引失效场景
假设存在如下表：
CREATE TABLE `t_employee` (
`id` int NOT NULL,
`name` char(64) DEFAULT NULL,
`entity_id` int DEFAULT NULL,
`class_id` int DEFAULT NULL,
`department_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `entity_idx` (`entity_id`,`class_id`,`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
未遵循最左前缀匹配导致索引失效
模糊查询时（like语句），模糊匹配的占位符位于条件的首部，如下所示：
mysql> EXPLAIN SELECT * FROM t_employee WHERE name LIKE "%snow";
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_employee | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
联合索引不遵循最左前缀，如下所示：
mysql> EXPLAIN SELECT * FROM t_employee WHERE class_id = 1;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_employee | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM t_employee WHERE class_id = 1 AND department_id = 2;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_employee | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
索引列参与运算
如果索引列参与了运算，会导致索引失效，引发全表扫描，如下所示：
mysql> EXPLAIN SELECT * FROM t_employee WHERE id + 1 = 10;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_employee | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
索引列使用了函数
mysql> EXPLAIN SELECT * FROM t_employee WHERE LEFT(name, 4) = "snow";
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_employee | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
MySQL 的内建函数、运算符，是由 MySQL Server 层实现的，如果查询条件中使用了函数，InnoDB 引擎层会将满足条件的记录，全部返回给 MySQL Server，由 MySQL 对记录做运算，并得到结果集。引擎层只会负责数据的存储和检索。
类型转换导致索引失效
- 类型隐式转换
例如，name 字段为 CHAR 格式，但是查询条件是 INT，查询时就会被隐式转换，如下所示：
mysql> EXPLAIN SELECT * FROM t_employee WHERE name = 666;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_employee | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
- 显示类型转换
mysql> EXPLAIN SELECT * FROM t_employee WHERE CONVERT(id, CHAR) = "10";
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_employee | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
OR 引起的索引失效
查询条件使用 OR 关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效
例如，对于如下查询语句 id 字段为主键，name 不是索引：
mysql> EXPLAIN SELECT * FROM t_employee WHERE id = 10 OR name != "john snow";
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_employee | NULL | ALL | PRIMARY,id | NULL | NULL | NULL | 4 | 81.25 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
对于上述查询语句，如果单独使用 username 字段作为条件很显然是全表扫描，既然已经进行了全表扫描了，前面id的条件再走一次索引反而是浪费了。
负向查询导致索引失效
对于 != 负向查询肯定不能命中索引，如下语句所示：
mysql> EXPLAIN SELECT * FROM t_employee WHERE name != "john snow";
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_employee | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 75.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
对于其他的负向查询，如：BETWEEN、IN、NOT IN、>、<、NOT LIKE 等负向查询条件，查询语句是否能命中索引，会受到结果集的范围影响，如果结果集的数量较大，优化器评估全表扫描的效率更高，即使查询条件所在的列有创建索引，优化器也会选择全表扫描。
索引字段使用 IS NOT NULL 导致失效
查询条件使用 IS NULL 时，正常走索引，使用 IS NOT NULL 时，不走索引，如下所示：
mysql> EXPLAIN SELECT * FROM t_employee WHERE id IS NOT NULL;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t_employee | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
两列数据做比较会导致索引失效
两列数据做比较，即便两列都创建了索引，索引也会失效，如下所示：
mysql> EXPLAIN SELECT * FROM t_employee WHERE id < entity_id;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_employee | NULL | ALL | NULL | NULL | NULL | NULL | 13 | 33.33 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
