MySQL 索引优化的常见误区及解决方案

一、冗余与重复索引

误区表现‌

重复索引‌：同一列按相同顺序创建多个索引（如 INDEX(col1) 和 UNIQUE(col1) 同时存在），导致维护成本增加。
冗余索引‌：联合索引覆盖单列索引（如已存在 INDEX(col1, col2)，又单独创建 INDEX(col1)）。

影响‌

写入性能下降：每次数据修改需维护多个索引，消耗额外 I/O 和 CPU 资源。
存储空间浪费：索引占用磁盘空间，尤其在大型表中成本显著。

优化建议‌

使用 SHOW INDEX 分析冗余索引，定期清理未使用的索引。
优先使用联合索引覆盖高频查询条件，避免单列索引堆砌。
二、低选择性索引

误区表现‌

在区分度低的列（如性别、状态）上创建索引，导致筛选效果差。

影响‌

索引树无法有效缩小数据范围，优化器可能放弃使用索引，转为全表扫描。

优化建议‌

通过公式计算选择性：COUNT(DISTINCT col) / COUNT(*)，选择值接近 1 的列作为索引前缀。
高频查询优先覆盖高区分度字段（如用户 ID、订单号）。
三、联合索引顺序错误

误区表现‌

未按查询条件的频率排序索引列（如高频查询 WHERE col2 AND col1，但索引顺序为 (col1, col2)）。

影响‌

无法有效利用索引的最左前缀原则，导致索引部分失效。

优化建议‌

根据查询条件频率调整索引列顺序，确保高频字段作为联合索引的前缀。
使用 EXPLAIN 验证索引是否覆盖查询条件。
四、隐式类型转换导致索引失效

误区表现‌

查询条件与字段类型不匹配（如 WHERE varchar_col = 123，触发隐式转换）。

影响‌

MySQL 无法使用索引，强制全表扫描，响应时间显著增加。

优化建议‌

确保查询条件与字段类型严格匹配，避免隐式转换。
使用 EXPLAIN EXTENDED 检查查询计划中的警告信息。
五、索引列使用函数或计算

误区表现‌

在索引列上使用函数或表达式（如 WHERE YEAR(create_time) = 2025）。

影响‌

索引列被计算后，优化器无法直接使用索引。

优化建议‌

改写查询条件，避免对索引列进行运算（如 WHERE create_time BETWEEN '2025-01-01' AND '2025-12-31'）。
六、分页查询未优化

误区表现‌

使用 LIMIT 1000000, 10 分页时，未利用索引覆盖或游标标记。

影响‌

数据库需遍历大量数据定位偏移量，性能急剧下降。

优化建议‌

将分页查询改为基于上一页末条记录的游标方式（如 WHERE create_time > '2025-04-22' ORDER BY create_time LIMIT 10）。
使用覆盖索引减少回表操作。
七、主键设计不合理

误区表现‌

使用低效主键（如长字符串类型的身份证号），导致索引树层级过高。

影响‌

主键索引占用更多存储空间，查询时需多次磁盘 I/O。

优化建议‌

优先使用自增整数作为主键，减少索引树分裂和存储开销。
若需唯一业务字段，可将其设为唯一索引而非主键。
总结

MySQL 索引优化的核心在于‌精准匹配查询需求‌，避免冗余、低效和失效操作。通过 EXPLAIN 分析执行计划、定期清理冗余索引、优化查询逻辑，可显著提升性能。