mysql索引失效的常见场景
约 2882 字大约 10 分钟
2025-05-18
测试数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.5 |
+-----------+
1 row in set (0.01 sec)sql分析
参见文章: SQL执行分析
测试数据表
说明
不要关注索引的科学性与合理性, 此处的索引仅仅是为了掩饰索引失效情况而存在
CREATE TABLE `people` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`code` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`age` int unsigned NOT NULL DEFAULT '0',
`name` varchar(30) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`height` int unsigned NOT NULL DEFAULT '0',
`address` varchar(30) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx_code_age_name` (`code`,`age`,`name`),
KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO people (id, code, age, name, height, address) VALUES (1, '101', 21, '周星驰', 175, '香港'), (2, '102', 18, '周杰伦', 173, '台湾'), (3, '103', 23, '苏三', 174, '成都');不满足最左匹配原则
explain select * from people where code = '100000' and name = '张三';
explain select * from people where code = '100000' and age > 10;
explain select * from people where code = '100000' and age > 10 and name = '张三';以上三条sql语句 会命中索引, 均是索引 idx_code_age_name
+----+-------------+--------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | people | NULL | range | idx_code_age_name | idx_code_age_name | 86 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.04 sec)explain select * from people where name = '张三';
explain select * from people where age = 10;
explain select * from people where name = '张三';以上三条sql语句 不会命中索引, 均是全表扫描, sql 分析结果如下:
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | people | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)总结
上述示例命中索引的sql语句中都有code字段，它是索引字段中的第一个字段，也就是 最左边的字段。只要有这个字段在，该sql已经就能走索引。这就是我们所说的最左匹配原则。
隐式类型转换
上面说了, 只要查询语句中出现 code 作为查询条件, 满足了最左前缀规则, 就可以使用索引. 但是这是全部场景么? 并不是的.
看一下下面的sql语句:
explain select * from people where code = 100000;sql语句中, code字段的值是一个int数字, 但是索引字段是varchar类型, 这是一个隐式类型转换. 所以sql语句 不会命中索引, sql分析结果如下:
mysql> explain select * from people where code = 100000;
+----+-------------+--------+------------+------+-------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | people | NULL | ALL | idx_code_age_name | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+--------+------------+------+-------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.01 sec)将sql修改成 explain select * from people where code = '100000'; 便可以命中索引了. 在日常使用过程中, 隐式类型转换导致索引失效的情况还是比较多的.
那么, 隐式类型转换是否一定到值索引失效? 不是的, 如果索引不是联合索引, 有独立字段构成的索引, 是可以命中的
mysql> explain select * from people where height="234";
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | people | NULL | ref | idx_height | idx_height | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)但是隐式类型转换需要注意有可能查出非预期数据, 这个适合mysql内部类型转换实现有关, 具体转换参见: mysql类型转换规则
查出非预期数据示例:
mysql> select * from people where height="175";
+----+------+-----+-----------+--------+---------+
| id | code | age | name | height | address |
+----+------+-----+-----------+--------+---------+
| 1 | 101 | 21 | 周星驰 | 175 | 香港 |
+----+------+-----+-----------+--------+---------+
1 row in set (0.00 sec)
mysql> select * from people where height="175s";
+----+------+-----+-----------+--------+---------+
| id | code | age | name | height | address |
+----+------+-----+-----------+--------+---------+
| 1 | 101 | 21 | 周星驰 | 175 | 香港 |
+----+------+-----+-----------+--------+---------+
1 row in set, 1 warning (0.00 sec)select *
使用了select *, 会 导致无法使用任何覆盖索引, 尽可能按需查询需要的字段, 在查询条件无法使用任何索引的情况下, 如果查询的字段全部在某一个索引中, 则会使用覆盖索引
explain select * from people where name = 'zhangsan';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | people | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)下面则会命中覆盖索引:
explain select code, name from people where name ='zhangsan';
+----+-------------+--------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | people | NULL | index | idx_code_age_name | idx_code_age_name | 208 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)上述sql分析结果中, 发现命中了索引, 在Extra中, 发现除了 Use Where 之外, 多了 Using index 信息, 说明, 查询条件本身不满足任何索引, 但是查询的字段全部在索引中, 所以使用了覆盖索引.
索引列上有计算
explain select * from user where id = 1;上面的sql可以说是日常使用频率做高的了, 基于主键索引查询数据详情, 但是如果对 id 进行计算, 则会导致索引失效:
mysql> explain select * from people where id - 1 = 1;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | people | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)对id字段进行计算之后, 发现逐渐索引已失效, 无法命中索引, 那么是不是完全无法再sql语句中进行运算呢? 也不是的, 可以将运算移到右边, 在表达式中计算.
mysql> explain select * from people where id = 1 + 1;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | people | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)将 id - 1 = 1 调整为 id = 1 + 1, 发现依旧可以使用逐渐索引
那么, 如果表达式中存在字段呢?
mysql> explain select * from people where id = age + 1;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | people | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)发现也是无法命中索引的
说明
索引列上有计算, 指的是在字段上(=左侧)不要有计算逻辑, 但是表达式上可以有计算逻辑(=右侧), 如果计算逻辑中包含其他字段，也是无法命中索引
索引列上使用函数
explain select * from people where height=17; 因为在 height 列上有索引, 所以一定能命中索引
explain select * from people where substr(height,1,2)=17;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | people | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)上述在height列上使用函数之后发现索引失效了, 在上面介绍在索引列上计算会导致索引失效, 但是将计算逻辑全部放到表达式中, 是可以命中索引的, 那么使用函数是不是形同逻辑呢?
explain select * from people where height=substr(age, 1,2);
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | people | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)函数作用在表达式上, 但是表达式中包含字段, 是无法命中索引的, 那么如果表达式中不包含字段, 是纯粹的函数运算呢?
mysql> explain select * from people where height=substr(123, 1,2);
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | people | NULL | ref | idx_height | idx_height | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)发现上述是可以命中索引的
总结
合在列上进行运算类似, 索引使用函数, 无法命中索引; 在表达式中使用函数, 如果表达式不包含其他字段, 则可以命中索引, 如果包含其他字段, 则无法命中索引
like左侧模糊匹配
模糊查询，在我们日常的工作中，使用频率还是比较高的。
explain select * from people where height like "%1%";
explain select * from people where height like "%1";
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | people | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)上述两条sql均无法命中索引, 而下面的sql, 是可以命中索引的
mysql> explain select * from people where height like "1%";
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | people | NULL | ALL | idx_height | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)总结
like 语句有些类似于联合索引的最左前缀匹配, 只有不存在左%的时候才会使用索引
列对比
列对比是指对数据库两个字段进行比较, 上面说过的 对索引列进行运算/使用函数, 其实就是列对比, 此种场景索引失效, 无法使用。
OR语句
先来看如下查询语句, 是否能命中索引
explain select * from people where id = 1 or height = 180;结果是可以命中, 分析结果如下:
mysql> explain select * from people where id = 1 or height = 180;
+----+-------------+--------+------------+-------------+--------------------+--------------------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------------+--------------------+--------------------+---------+------+------+----------+----------------------------------------------+
| 1 | SIMPLE | people | NULL | index_merge | PRIMARY,idx_height | PRIMARY,idx_height | 8,4 | NULL | 2 | 100.00 | Using union(PRIMARY,idx_height); Using where |
+----+-------------+--------+------------+-------------+--------------------+--------------------+---------+------+------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)但是不是说 OR 语句会导致索引失效么? 那再看如下语句:
explain select * from people where id = 1 or address = '北京';这个语句能命中索引么? 答案是索引失效
mysql> explain select * from people where id = 1 or address = '北京';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | people | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 55.56 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)为何第一个场景的sql能命中索引而第二个场景的SQL无法命中索引?
原因是第一个sql语句, id与height均具有索引, 但是第二个sql语句中, address没有索引。可以再自行尝试下如下sql是否命中索引
explain select * from people where id = 1 or code = '123';总结
OR语句并不是一定会导致索引失效,但是命中索引的前提是: 每一个or条件都能命中索引
NOT IN / NOT EXISTS
以前的时候, NOT IN 会导致索引失效, 但是 测试用的 mysql8 是可以命中索引的
mysql> explain select * from people where height not in (123, 456);
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | people | NULL | range | idx_height | idx_height | 4 | NULL | 5 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)ORDER BY
注意, ORDER BY 可以正常使用索引的, 但是排序字段会影响排序效率:
mysql> explain select * from people where code = "123" order by name;
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+----------------+
| 1 | SIMPLE | people | NULL | ref | idx_code_age_name | idx_code_age_name | 82 | const | 1 | 100.00 | Using filesort |
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)上述sql命中了索引, 但是order by的时候，排序效率比较低，需要走一次filesort排序
mysql> explain select * from people where code = "123" order by code,age,name;
mysql> explain select * from people where code = "123" order by code,age;
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | people | NULL | ref | idx_code_age_name | idx_code_age_name | 82 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)上述sql没有需要额外排序
mysql> explain select * from people where code = "123" order by code,height;
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+----------------+
| 1 | SIMPLE | people | NULL | ref | idx_code_age_name | idx_code_age_name | 82 | const | 1 | 100.00 | Using filesort |
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)上述sql也经过filesort排序了
mysql> explain select * from people where code = "123" order by code asc , age desc, name asc;
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+----------------+
| 1 | SIMPLE | people | NULL | ref | idx_code_age_name | idx_code_age_name | 82 | const | 1 | 100.00 | Using filesort |
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)上述sql也经过了额外的filesort排序
总结
查询条件条件能命中索引, ORDER BY 就可以正常走索引, 但是 ORDER BY 的规则会影响排序的效率:
- ORDER BY 字段与查询条件命中统一索引, 排序效率最好, 戴氏瑶瑶所有字段同升(asc)或者同降(desc)
- 其他情况排序时会走一次filesort排序, 效率会有所降低