mysql索引失效的原因

索引失效会降低查询性能，常见原因包括对索引列使用函数或表达式，如YEAR(create_time)，导致优化器选择全表扫描，应改用范围查询避免。

MySQL索引失效会显著降低查询性能，即使表中已经创建了合适的索引，某些情况下优化器仍可能选择全表扫描。了解索引失效的常见原因有助于写出更高效的SQL语句。

1. 使用函数或表达式操作索引列

当在WHERE条件中对索引列使用函数或表达式时，MySQL无法直接使用索引进行查找。

• 索引失效：SELECT * FROM users WHERE YEAR(create_time) = 2023;
• 建议写法：SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time

因为YEAR()函数改变了列的原始值，优化器不能利用create_time上的索引。

2. 隐式类型转换导致索引失效

当索引列是字符串类型，而查询条件使用数字类型时，MySQL会进行隐式类型转换，从而导致索引失效。

• 索引失效：SELECT * FROM users WHERE user_no = 123; （user_no为VARCHAR类型）
• 正确写法：SELECT * FROM users WHERE user_no = '123';

MySQL会将所有user_no值转换为数字进行比较，破坏了索引有序性。

3. 模糊查询以通配符开头

LIKE查询如果以%开头，会导致索引无法有效使用。

• 索引失效：SELECT * FROM users WHERE name LIKE '%李';
• 可走索引：SELECT * FROM users WHERE name LIKE '张%';

以%开头意味着需要匹配任意前缀，索引的B+树结构无法快速定位。

4. 联合索引未遵循最左前缀原则

联合索引要求查询条件从最左边的列开始，否则索引无法生效。

纳米搜索

纳米搜索：360推出的新一代AI搜索引擎

30

查看详情

• 有联合索引 (age, name, city)
• 能用索引：WHERE age=25 AND name='张三'
• 不能用索引：WHERE name='张三' AND city='北京'

跳过age字段直接使用name和city，无法命中该联合索引。

5. 使用OR连接非索引列

当OR连接一个有索引列和一个无索引列时，可能导致整个条件无法使用索引。

• 索引可能失效：SELECT * FROM users WHERE indexed_col = 1 OR non_indexed_col = 'abc';

此时优化器可能放弃使用index_col的索引，改为全表扫描。

6. 数据分布不均或查询结果占比过大

即使索引语法正确，如果MySQL估算查询结果占全表数据比例较高（如超过10%-20%），优化器可能认为全表扫描更快。

这种情况常见于状态字段只有几个值（如status=0/1），查询status=1时若大量记录满足，索引会被跳过。

7. 使用NOT、!=、NOT IN等否定操作

否定性条件通常无法有效利用索引。

• 索引难以使用：SELECT * FROM users WHERE status != 1;
• IN可以走索引：SELECT * FROM users WHERE id IN (1,2,3);
• NOT IN通常不走索引：SELECT * FROM users WHERE id NOT IN (1,2);

基本上就这些常见情况。写SQL时注意避免上述问题，配合EXPLAIN分析执行计划，能有效提升查询效率。

以上就是mysql索引失效的原因的详细内容，更多请关注php中文网其它相关文章！