mysql索引失效的原因有哪些_mysql索引失效排查方法

索引失效主因包括函数操作、否定查询、OR连接无索引字段、前导模糊匹配、类型转换、联合索引违背最左原则及大数据量回表；通过EXPLAIN分析执行计划，检查索引设计与SQL写法，避免隐式转换，可有效排查并优化。

MySQL索引失效会显著影响查询性能，导致原本应走索引的查询变成全表扫描。了解索引失效的原因并掌握排查方法，是优化数据库查询的关键。

一、MySQL索引失效的常见原因

1. 查询条件中使用了函数或表达式

对索引列进行函数操作或计算会导致索引无法使用。

• SELECT * FROM user WHERE YEAR(create_time) = 2023; —— 对日期字段使用YEAR()函数，即使create_time有索引也会失效。
• SELECT * FROM user WHERE age + 1 = 20; —— 对索引列做加法运算。

2. 使用了不等于（!= 或 ）、NOT IN、NOT EXISTS等否定操作

这类操作通常无法有效利用B+树索引结构，导致全表扫描。

• SELECT * FROM user WHERE status != 1;
• SELECT * FROM user WHERE id NOT IN (1,2,3);

3. 使用OR连接多个条件，且部分字段无索引

当OR前后有一个字段没有索引时，可能导致整个查询放弃使用索引。

• SELECT * FROM user WHERE name = '张三' OR phone = '138...'; —— 若phone无索引，name的索引也可能不生效。

4. 模糊查询以%开头

B+树索引从左往右匹配，前导通配符破坏了最左匹配原则。

• SELECT * FROM user WHERE name LIKE '%三'; —— 索引失效。
• SELECT * FROM user WHERE name LIKE '张%'; —— 可用索引。

5. 类型转换或隐式转换

当查询字段与条件值类型不一致时，MySQL会自动做类型转换，导致索引失效。

• SELECT * FROM user WHERE user_id = '123'; —— user_id是INT类型，传入字符串会触发隐式转换。

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

联合索引(a,b,c)，只有按a、a+b、a+b+c的顺序才能命中索引。跳过a直接查b或c，索引无效。

• WHERE b = 2 AND c = 3; —— 无法使用(a,b,c)联合索引。

7. 查询返回数据量过大，优化器选择全表扫描

即使有索引，如果MySQL预估通过索引扫描的行数接近全表，优化器可能认为全表扫描更高效。

Skybox AI

一键将涂鸦转为360°无缝环境贴图的AI神器

140

查看详情

• 表中有10万条数据，查询条件匹配9万条，此时走索引成本更高。

8. 使用SELECT *

若索引不是覆盖索引，查询*需要回表，当数据量大时优化器可能放弃索引。

二、MySQL索引失效的排查方法

1. 使用EXPLAIN分析执行计划

在SQL语句前加上EXPLAIN，查看是否使用了索引。

• type：ALL表示全表扫描，index表示索引扫描，ref/eq_ref表示使用了索引，性能较好。
• key：实际使用的索引名称，为NULL表示未使用索引。
• rows：扫描的行数，越大说明效率越低。
• Extra：出现Using where; Using filesort或Using temporary说明存在性能问题。

2. 检查索引设计是否合理

• 确认查询条件中的字段是否包含在索引中。
• 联合索引是否遵循最左前缀原则。
• 是否可以创建覆盖索引减少回表。

3. 查看表结构和索引信息

使用以下命令查看索引情况：

4. 避免隐式类型转换

• 确保查询条件的数据类型与字段定义一致。
• 尤其是INT字段不要传字符串，VARCHAR不要漏掉引号。

5. 优化SQL写法

• 避免在索引列上做计算或函数处理。
• 尽量用IN代替OR（前提是字段都有索引）。
• 模糊查询尽量避免前导%。

6. 使用FORCE INDEX强制走索引（谨慎使用）

可用于验证索引是否有效：

但生产环境不建议长期使用，干扰优化器判断。

基本上就这些。索引失效多数源于SQL写法不当或设计不合理。通过EXPLAIN分析执行计划，结合业务逻辑调整索引和查询方式，能有效提升查询性能。

以上就是mysql索引失效的原因有哪些_mysql索引失效排查方法的详细内容，更多请关注php中文网其它相关文章！