MySQL引起索引失效的原因

本文详细解析了MySQL索引的使用原则,包括全值匹配、最左前缀法则、索引列计算影响、范围条件限制、LIKE语句优化、覆盖索引及特殊操作对索引的影响,帮助读者掌握高效SQL查询的秘诀。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

查看索引结构

mysql> show index from staffs;
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs |          0 | PRIMARY          |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | idx_name_age_pos |            1 | NAME        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | idx_name_age_pos |            2 | age         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | idx_name_age_pos |            3 | pos         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

全值匹配我最爱

查询的字段或查询条件字段与建立的索引字段一一对应

mysql> explain select * from staffs where NAME = 'July' and age = 23 and pos = 'dev';
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_name_age_pos | idx_name_age_pos | 140     | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
1 row in set (0.00 sec)

# type为ref,且用到了索引

最佳左前缀法则

含义:如果为多值索引,要遵守最左前缀法则。指的是查询从索引的最左列开始并且不跳过索引中的列。

解释:查询的字段或者条件字段可以不与索引字段全部一致,但开头必须一致,且中间不能隔断。

  1. 带头大哥不能死
  2. 中间兄弟不能断
# 只有前两个
mysql> explain select * from staffs where NAME = 'July' and age = 23;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_name_age_pos | idx_name_age_pos | 78      | const,const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

# 没有‘大哥’
mysql> explain select * from staffs where age = 23 and pos = 'dev';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

# ‘兄弟’间断
mysql> explain select * from staffs where NAME = 'July' and pos = 'dev';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_name_age_pos | idx_name_age_pos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

分析:没有‘大哥’就像楼梯没有第一层一样。‘兄弟’间断就像楼梯中间少了一层。

在索引列上做任何操作(计算、 函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描

# 没有在索引列上做操作
mysql> explain select * from staffs where name='July';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_name_age_pos | idx_name_age_pos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
1 row in set (0.01 sec)

# 在索引列上做了操作
mysql> explain select * from staffs where left(name,4)='July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

存储引擎不能使用索引中范围条件右边的列

分析以下代码可以知道,当索引出现范围条件后,其后边的索引列将不能被使用(出现范围条件本身的这个字段还可以被使用)

mysql> explain select * from staffs where name='July' and age=22 and pos='dev';
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_name_age_pos | idx_name_age_pos | 140     | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select * from staffs where name='July' and age>15 and pos='dev';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | staffs | range | idx_name_age_pos | idx_name_age_pos | 78      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select * from staffs where name='July' and age=15;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_name_age_pos | idx_name_age_pos | 78      | const,const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

尽量使用覆盖索引(只访问索引的查询(查询列和索引列保持一致)),减少select*

mysql> explain select * from staffs where name='July' and age=15;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_name_age_pos | idx_name_age_pos | 78      | const,const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select name,age from staffs where name='July' and age=15;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+--------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                    |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | staffs | ref  | idx_name_age_pos | idx_name_age_pos | 78      | const,const |    1 | Using where; Using index |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

MySQL在使用不等于(<>或!=)时无法使用索引,会导致全表扫描

mysql> explain select * from staffs where name!='July';
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys    | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | idx_name_age_pos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from staffs where name='July';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_name_age_pos | idx_name_age_pos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

is null和is not null也无法使用索引

mysql> explain select * from staffs where name is not null;
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys    | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | idx_name_age_pos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from staffs where name is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

like以通配符开头(%abc),MySQL将无法使用索引,导致全表扫描

mysql> explain select * from staffs where name like'%July%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from staffs where name like'%July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from staffs where name like'July%';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | staffs | range | idx_name_age_pos | idx_name_age_pos | 74      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

解决%不能在左边

使用覆盖索引

字符串不加单引号引起索引失效

mysql> explain select * from staffs where name = '2000';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_name_age_pos | idx_name_age_pos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select * from staffs where name = 2000;
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys    | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | idx_name_age_pos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

注意

  1. 在sql中varchar一定要加单引号
  2. 应该避免隐式或显式的发生类型转换

or也会引起索引失效

mysql> explain select * from staffs where name = 'July' or age =20;
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys    | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | idx_name_age_pos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

[优化总结口诀]

全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
LIKE百分写最右,覆盖素引不写星;
不等空值还有or,索引失效要少用;

### MySQL索引失效原因 当查询条件中的列顺序不匹配创建索引时定义的列顺序,则可能导致索引无法被利用[^1]。例如,在一个多列组合索引的情况下,如果查询只涉及该多列索引的一部分前导列之后的其他列而未包含前面所有的列,那么这个索引可能不会生效。 隐式转换也是造成索引失效的一个常见因素之一。当数据类型的自动转换发生时——比如把字符串与整数比较——数据库引擎可能会放弃使用现有的索引来执行全表扫描操作来获取结果集[^2]。 某些SQL函数的应用同样会影响索引的有效性。对于一些特定于行的操作型内置函数(如`LOWER()`、`UPPER()`),即使这些表达式的参数本身是基于已建立好索引字段上的,也可能因为需要逐行处理而导致原有索引结构变得不再适用从而引起性能下降问题[^3]。 另外,不当的数据范围查询也会使优化器决定不用现有索引而是采取更耗资源的方式来检索所需记录;特别是那些涉及到大量连续值区间判断或是模糊匹配模式搜索语句往往容易触发此类现象[^4]。 最后,统计信息过期或缺失会误导查询规划者做出错误的选择,进而忽视掉原本可以加速访问速度的理想路径选项[^5]。 ### 解决方案 为了防止由于上述提到的各种情况所引起的索引效率低下甚至完全不起作用的问题,建议遵循如下最佳实践: 保持查询逻辑尽可能简单明了,并确保WHERE子句里的过滤条件能够充分利用到已经存在的单个或者复合形式下的索引项上[^6]。 ```sql SELECT * FROM table_name WHERE indexed_column = 'value'; ``` 定期分析并更新有关基础表内各列分布状况的相关元数据,以便让CBO(cost-based optimizer)能依据最新最准确的信息来进行决策过程[^7]。 ```sql ANALYZE TABLE table_name; ``` 避免不必要的类型转换行为出现,尤其是在连接运算符两侧之间存在不同属性的对象时要格外注意这一点,可以通过显式指定CAST()方法强制统一双方的表现形态以维持住原有的索引优势[^8]。 ```sql SELECT * FROM table_name WHERE CAST(column AS CHAR) LIKE '%pattern%'; ``` 谨慎对待任何带有复杂计算性质或者是非确定性的函数调用场景,尝试重构原始SQL表述使之更加贴近底层物理存储特性的同时减少对外部辅助手段依赖程度,必要时候考虑新建专门针对特殊需求定制化的覆盖索引[^9]。 ```sql CREATE INDEX idx_custom ON table_name (function_based_expression); ``` 尽量缩小目标集合规模,通过增加额外约束限定查找空间边界,提高命中率的同时也降低了整体I/O成本开销,这有助于增强最终输出质量同时也间接促进了系统响应时间缩短的效果[^10]。 ```sql SELECT * FROM table_name WHERE indexed_column BETWEEN start_value AND end_value; ```
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值