程序员求职经验分享与学习资料整理平台

网站首页 > 文章精选 正文

MySQL索引失效场景分析与优化(mysql索引何时失效)

balukai 2025-07-10 13:11:46 文章精选 4 ℃

MySQL索引失效是性能优化的关键问题,以下列举15+个典型场景,结合原理和示例分析,帮你全面避坑:


一、基础失效场景

  1. 对索引列进行计算或函数操作
  2. sql
  3. SELECT * FROM users WHERE YEAR(create_time) = 2023; -- 索引失效
  4. 分析:对create_time索引列使用YEAR()函数,需逐行计算后再比较。
    优化:改用范围查询:
  5. sql
  6. SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
  7. 隐式类型转换
  8. sql
  9. SELECT * FROM users WHERE phone = 13800138000; -- phone是varchar类型
  10. 分析:数字13800138000被转为字符串,相当于对索引列做CAST(phone AS signed)操作。
    优化:保持类型一致:
  11. sql
  12. SELECT * FROM users WHERE phone = '13800138000';
  13. 最左前缀原则缺失
    索引:(name, age)
  14. sql
  15. SELECT * FROM users WHERE age = 30; -- 失效(未用name) SELECT * FROM users WHERE name LIKE 'A%' AND age = 30; -- 有效
  16. 使用OR连接非索引列
  17. sql
  18. SELECT * FROM users WHERE id = 100 OR email = 'test@example.com'; -- 若email无索引,全表扫描
  19. 优化:拆分查询或用UNION
  20. sql
  21. SELECT * FROM users WHERE id = 100 UNION SELECT * FROM users WHERE email = 'test@example.com';

二、复杂查询失效场景

  1. 范围查询阻断后续索引
    索引:
    (age, salary)
  2. sql
  3. SELECT * FROM users WHERE age > 25 AND salary = 8000; -- salary无法用索引
  4. 原理age>25返回大量无序数据,无法再用索引过滤salary
  5. !=<>操作符
  6. sql
  7. SELECT * FROM users WHERE status != 1; -- 全表扫描(需排除大部分数据)
  8. 例外:覆盖索引可能被使用(只需查索引)。
  9. NOT IN / NOT EXISTS
  10. sql
  11. SELECT * FROM users WHERE id NOT IN (1,2,3); -- 通常全表扫描
  12. 优化:小数据集用LEFT JOIN过滤:
  13. sql
  14. SELECT u.* FROM users u LEFT JOIN (SELECT 1 AS id UNION SELECT 2 ...) t ON u.id = t.id WHERE t.id IS NULL;
  15. LIKE以通配符开头
  16. sql
  17. SELECT * FROM users WHERE name LIKE '%abc'; -- 索引失效
  18. 优化
  19. 后缀匹配:LIKE 'abc%'(有效)
  20. 全文索引:MATCH(name) AGAINST('abc')

三、优化器行为导致失效

  1. 索引区分度过低
    例如
    gender列(值仅M/F),优化器可能弃用索引。
    解决方案:复合索引(如(gender, age))。
  2. 表数据量过小
    当行数 < 全表扫描成本阈值时(如<1000行),优化器直接全表扫描。
  3. 统计信息不准确
    现象EXPLAIN显示错误执行计划。
    修复ANALYZE TABLE table_name; 更新统计信息。
  4. 强制索引合并成本高
  5. sql
  6. SELECT * FROM users WHERE name = 'John' OR age = 30; -- 可能全表扫描
  7. 优化器逻辑:当OR条件涉及不同索引,合并成本高时弃用索引。

四、隐式陷阱

  1. 字符集/排序规则不匹配
  2. sql
  3. SELECT * FROM t1 JOIN t2 ON t1.utf8_col = t2.latin1_col; -- 隐式转换失效
  4. 解决方案:统一字符集或显式转换:
  5. sql
  6. ON CONVERT(t1.utf8_col USING latin1) = t2.latin1_col
  7. JOIN列类型不一致
  8. sql
  9. SELECT * FROM orders o JOIN users u ON o.user_id = u.id; -- 若o.user_id为varchar,u.id为int,失效
  10. 原理:类型不匹配触发隐式转换。
  11. 索引列使用子查询
  12. sql
  13. SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE name = 'Electronics'); -- 可能失效
  14. 优化:改用JOIN
  15. sql
  16. SELECT p.* FROM products p JOIN categories c ON p.category_id = c.id WHERE c.name = 'Electronics';

五、特殊场景

  1. 使用ORDER BY非索引列
  2. sql
  3. SELECT * FROM users ORDER BY create_time DESC; -- 无索引时filesort
  4. 优化:为排序字段添加索引。
  5. 覆盖索引未命中
  6. sql
  7. SELECT * FROM table WHERE indexed_col = 1; -- 需回表
  8. 对比:若只查索引列则无需回表(Using index)。
  9. 索引损坏
    现象:索引存在但查询极慢。
    修复REPAIR TABLE table_name; 或重建索引。

解决方案总结

场景

优化策略

函数操作

重写查询避免计算

类型不匹配

统一数据类型

最左前缀缺失

调整索引顺序或新增复合索引

OR条件失效

UNION或确保所有列有索引

LIKE通配符开头

倒排索引/ES分词

优化器选错索引

FORCE INDEX(慎用)

统计信息过期

ANALYZE TABLE

终极诊断工具
使用
EXPLAIN查看执行计划,关注:

type列:ALL(全表扫描)、index(全索引扫描)需警惕

key列:实际使用的索引

Extra列:Using filesortUsing temporary表示性能瓶颈

通过理解这些场景,结合EXPLAIN分析,能有效规避索引失效问题,提升查询性能!

最近发表
标签列表