拓冰网站定制 
# MySQL索引优化避坑指南 索引是MySQL性能优化的第一战场但实际生产中大量慢查询并非“没建索引”而是“索引被绕过了”或“索引设计不合理”。本文总结几个高频踩坑点均来自真实场景复盘。 ## 一、隐式类型转换最隐蔽的索引杀手 当查询条件中列类型与传入值类型不一致时MySQL会对列做隐式转换导致索引失效。典型场景是字符串列用数字查询 sql -- phone 为 varchar 类型 -- 坏写法全表扫描索引失效 EXPLAIN SELECT * FROM users WHERE phone 13800138000; -- 好写法走 ref 索引 EXPLAIN SELECT * FROM users WHERE phone 13800138000; 原因在于字符串与数字比较时MySQL将**列**转为数字相当于对列套了一层函数破坏了索引的有序性。反向则没有问题int列用字符串查询会走索引因为转换发生在常量一侧。 排查技巧线上突然出现的慢SQL先看EXPLAIN的type是否退化为ALL再核对字段类型与传参类型是否一致。ORM框架如MyBatis的#{}拼接中参数类型由Java侧决定尤其容易踩这个坑。 ## 二、联合索引与最左前缀范围查询会“截断”后续列 联合索引(a, b, c)遵循最左前缀原则但很多人忽略了范围查询对后续列的影响 sql -- 索引 idx_status_created (status, created_at) -- 可以完整利用两列status等值 created_at范围 SELECT * FROM orders WHERE status 1 AND created_at 2026-01-01; -- 只能利用 status 一列范围查询后的列无法继续定位 SELECT * FROM orders WHERE created_at 2026-01-01 AND status BETWEEN 1 AND 3; 第二个查询中status是范围条件created_at虽在索引中但只能作为覆盖索引扫描过滤效率大打折扣。**设计原则等值条件列放前面范围条件列放后面**。 另一个进阶技巧是利用索引顺序扫描避免filesort。如果查询是WHERE a ? ORDER BY b LIMIT 10建立(a, b)索引可以直接按索引序返回免掉排序这也是深分页优化的基础——先在覆盖索引上定位主键再回表比直接LIMIT 100000, 10快几个数量级。 ## 三、索引选择性不足建了等于白建 区分度Cardinality / 总行数太低的列建索引收益极低。经典的反例是性别字段但更常见的坑是**状态 时间的组合设计不当** sql -- 差status 只有 3 个值单独查 status 会命中大量行 ALTER TABLE orders ADD INDEX idx_status (status); -- 好用前缀索引提升选择性或调整列顺序 ALTER TABLE orders ADD INDEX idx_status_created (status, created_at); -- 查看区分度 SELECT COUNT(DISTINCT status) / COUNT(*) AS selectivity_status, COUNT(DISTINCT CONCAT(status, -, DATE(created_at))) / COUNT(*) AS selectivity_combo FROM orders; 经验阈值单列选择性低于10%就要考虑组合索引或前缀索引。注意CONCAT做选择性估算时结果会偏乐观组合越细区分度越高还需结合实际查询模式判断。 ## 四、几个容易被忽视的细节 1. **函数与表达式失效**WHERE DATE(created_at) 2026-09-30无法走索引应改写为范围条件WHERE created_at 2026-09-30 AND created_at 2026-10-01。MySQL 8.0支持函数索引可作为过渡方案的补充。 2. **OR与IN的陷阱**OR连接的两边必须都有可用索引否则整体退化为全表扫描。MySQL 8.0的索引跳跃扫描Skip Scan能部分缓解联合索引跳过首列的问题但不要依赖它兜底。 3. **回表与覆盖索引**SELECT *几乎是对覆盖索引的宣战。高频查询尽量只取需要的列让(查询列)构成覆盖索引用Extra中的Using index验证。 4. **索引不是越多越好**每个索引都会拖慢写入、占用空间且优化器面对过多可选索引时可能选错。冗余索引如已有(a, b)又建(a)应定期用sys.schema_redundant_indexes清理。 ## 写在最后 索引优化的本质是理解B树的有序结构任何破坏有序性的操作类型转换、函数、范围后的列都会让优化器放弃索引。实践中建议的流程是**慢查询定位慢日志 EXPLAIN→ 确认失效原因 → 调整SQL写法或索引设计 → 用真实数据量验证执行计划**。工具会变、版本会变但“让数据结构为你工作”这一原则不会变。 MySQL索引优化避坑指南
# MySQL索引优化避坑指南 索引是MySQL性能优化的第一战场但实际生产中大量慢查询并非“没建索引”而是“索引被绕过了”或“索引设计不合理”。本文总结几个高频踩坑点均来自真实场景复盘。 ## 一、隐式类型转换最隐蔽的索引杀手 当查询条件中列类型与传入值类型不一致时MySQL会对列做隐式转换导致索引失效。典型场景是字符串列用数字查询 sql -- phone 为 varchar 类型 -- 坏写法全表扫描索引失效 EXPLAIN SELECT * FROM users WHERE phone 13800138000; -- 好写法走 ref 索引 EXPLAIN SELECT * FROM users WHERE phone 13800138000; 原因在于字符串与数字比较时MySQL将**列**转为数字相当于对列套了一层函数破坏了索引的有序性。反向则没有问题int列用字符串查询会走索引因为转换发生在常量一侧。 排查技巧线上突然出现的慢SQL先看EXPLAIN的type是否退化为ALL再核对字段类型与传参类型是否一致。ORM框架如MyBatis的#{}拼接中参数类型由Java侧决定尤其容易踩这个坑。 ## 二、联合索引与最左前缀范围查询会“截断”后续列 联合索引(a, b, c)遵循最左前缀原则但很多人忽略了范围查询对后续列的影响 sql -- 索引 idx_status_created (status, created_at) -- 可以完整利用两列status等值 created_at范围 SELECT * FROM orders WHERE status 1 AND created_at 2026-01-01; -- 只能利用 status 一列范围查询后的列无法继续定位 SELECT * FROM orders WHERE created_at 2026-01-01 AND status BETWEEN 1 AND 3; 第二个查询中status是范围条件created_at虽在索引中但只能作为覆盖索引扫描过滤效率大打折扣。**设计原则等值条件列放前面范围条件列放后面**。 另一个进阶技巧是利用索引顺序扫描避免filesort。如果查询是WHERE a ? ORDER BY b LIMIT 10建立(a, b)索引可以直接按索引序返回免掉排序这也是深分页优化的基础——先在覆盖索引上定位主键再回表比直接LIMIT 100000, 10快几个数量级。 ## 三、索引选择性不足建了等于白建 区分度Cardinality / 总行数太低的列建索引收益极低。经典的反例是性别字段但更常见的坑是**状态 时间的组合设计不当** sql -- 差status 只有 3 个值单独查 status 会命中大量行 ALTER TABLE orders ADD INDEX idx_status (status); -- 好用前缀索引提升选择性或调整列顺序 ALTER TABLE orders ADD INDEX idx_status_created (status, created_at); -- 查看区分度 SELECT COUNT(DISTINCT status) / COUNT(*) AS selectivity_status, COUNT(DISTINCT CONCAT(status, -, DATE(created_at))) / COUNT(*) AS selectivity_combo FROM orders; 经验阈值单列选择性低于10%就要考虑组合索引或前缀索引。注意CONCAT做选择性估算时结果会偏乐观组合越细区分度越高还需结合实际查询模式判断。 ## 四、几个容易被忽视的细节 1. **函数与表达式失效**WHERE DATE(created_at) 2026-09-30无法走索引应改写为范围条件WHERE created_at 2026-09-30 AND created_at 2026-10-01。MySQL 8.0支持函数索引可作为过渡方案的补充。 2. **OR与IN的陷阱**OR连接的两边必须都有可用索引否则整体退化为全表扫描。MySQL 8.0的索引跳跃扫描Skip Scan能部分缓解联合索引跳过首列的问题但不要依赖它兜底。 3. **回表与覆盖索引**SELECT *几乎是对覆盖索引的宣战。高频查询尽量只取需要的列让(查询列)构成覆盖索引用Extra中的Using index验证。 4. **索引不是越多越好**每个索引都会拖慢写入、占用空间且优化器面对过多可选索引时可能选错。冗余索引如已有(a, b)又建(a)应定期用sys.schema_redundant_indexes清理。 ## 写在最后 索引优化的本质是理解B树的有序结构任何破坏有序性的操作类型转换、函数、范围后的列都会让优化器放弃索引。实践中建议的流程是**慢查询定位慢日志 EXPLAIN→ 确认失效原因 → 调整SQL写法或索引设计 → 用真实数据量验证执行计划**。工具会变、版本会变但“让数据结构为你工作”这一原则不会变。