悠悠楠杉
网站页面
MySQL索引优化实战：删除重复索引与避坑指南
08/09
本文深入讲解MySQL重复索引的识别与删除方法，剖析5个常见索引创建错误场景，提供可落地的优化方案，帮助开发者提升数据库性能。
一、揪出数据库中的"影子刺客"：重复索引
上周排查公司订单系统慢查询时，发现一个有趣现象：某核心表的order_no字段同时存在两个B+树索引，查询性能却比单个索引时更差。这正是典型的重复索引问题——它们像"影子刺客"一样潜伏在数据库中，不仅浪费存储空间，更会导致优化器选择错误的执行计划。
1.1 什么是重复索引？
- 定义：在同一组列上创建多个功能相同的索引
- 常见形式：
sql INDEX idx1 (col1), INDEX idx2 (col1) -- 完全重复 INDEX idx3 (col1), INDEX idx4 (col1, col2) -- 前缀重复
1.2 自动化检测手段
推荐使用Percona工具包中的pt-duplicate-key-checker：
bash
pt-duplicate-key-checker --user=root --password=xxx --database=prod_db
输出示例：
order_table
idxorderno与ukorderno功能重复
建议保留唯一索引ukorderno，删除普通索引idxorderno
二、5大索引创建陷阱与修复方案
2.1 陷阱：滥用联合索引顺序
错误示例：
sql
CREATE INDEX idx_name ON users(last_name, first_name);
当查询仅包含first_name条件时，该索引完全失效。
修复方案：
sql
-- 根据实际查询模式调整顺序
CREATE INDEX idx_name ON users(first_name, last_name);
2.2 陷阱：忽视索引选择性
错误示例：
sql
CREATE INDEX idx_gender ON employees(gender); -- 性别只有2-3个枚举值
低选择性索引的效用极低，反而增加写入开销。
修复方案：
sql
-- 组合高选择性列
CREATE INDEX idx_loc_gender ON employees(location_id, gender);
2.3 陷阱：盲目添加覆盖索引
错误示例：
sql
CREATE INDEX idx_cover ON orders(user_id, status, price, create_time);
虽然满足某个查询的覆盖索引需求，但导致索引体积过大。
修复方案：
sql
-- 拆分为多个专用索引
CREATE INDEX idx_user_status ON orders(user_id, status);
CREATE INDEX idx_user_price ON orders(user_id, price);
三、实战：安全删除重复索引
3.1 操作流程
- 确认索引使用情况：
sql SELECT * FROM sys.schema_unused_indexes; - 在测试环境验证删除影响
- 使用在线DDL工具执行（避免锁表）：
sql ALTER TABLE orders DROP INDEX idx_order_no, ALGORITHM=INPLACE, LOCK=NONE;
3.2 监控回退方案
删除后需监控关键指标：sql
-- 查询QPS变化
SHOW GLOBAL STATUS LIKE 'Questions';
-- 慢查询增长监控
SELECT * FROM mysql.slowlog WHERE starttime > NOW() - INTERVAL 1 HOUR;
四、索引设计黄金法则
三星索引原则：
- 一星：WHERE条件匹配索引列
- 二星：ORDER BY使用索引排序
- 三星：覆盖所有SELECT字段
组合索引避坑口诀：
等值查询放左边，范围查询靠右边
排序字段跟着走，覆盖查询不用愁定期维护建议：
sql ANALYZE TABLE orders; -- 更新统计信息 OPTIMIZE TABLE logs; -- 重建索引组织
通过持续优化索引策略，某电商平台将订单查询响应时间从1200ms降至200ms。记住：好的索引设计不是一蹴而就，需要结合业务查询模式持续迭代优化。
