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写法或索引设计 → 用真实数据量验证执行计划**。工具会变、版本会变，但“让数据结构为你工作”这一原则不会变。