外观
MySQL 冗余索引识别与清理
什么是冗余索引
冗余索引是指在数据库表中存在的、对于查询优化没有实际帮助，反而会影响插入、更新和删除操作性能的索引。冗余索引包括：
- 重复索引：完全相同的索引定义，包括索引名、列名和顺序
- 冗余覆盖索引：一个索引的列是另一个索引的前缀，或者两个索引包含相同的列但顺序不同
- 无用索引：从未被查询使用过的索引
冗余索引的危害
- 存储空间浪费：每个索引都需要占用磁盘空间
- 写入性能下降：每次插入、更新或删除操作都需要维护所有相关索引
- 内存占用增加：索引会占用缓冲池空间，可能导致其他有用数据被置换
- 优化器负担加重：过多的索引会增加查询优化器的选择时间
- 维护成本增加：索引需要定期重建和优化
识别冗余索引的方法
1. 使用 INFORMATION_SCHEMA.STATISTICS
可以通过查询 INFORMATION_SCHEMA.STATISTICS 表来分析索引结构：
sql
SELECT
table_schema,
table_name,
index_name,
index_type,
GROUP_CONCAT(column_name ORDER BY seq_in_index SEPARATOR ',') AS index_columns
FROM
INFORMATION_SCHEMA.STATISTICS
WHERE
table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
GROUP BY
table_schema, table_name, index_name, index_type
ORDER BY
table_schema, table_name, index_name;2. 使用 sys.schema_redundant_indexes 视图
MySQL 5.7+ 提供了 sys.schema_redundant_indexes 视图，可以直接查看冗余索引：
sql
SELECT * FROM sys.schema_redundant_indexes;3. 使用 pt-duplicate-key-checker 工具
Percona Toolkit 提供的 pt-duplicate-key-checker 工具可以自动识别冗余索引：
bash
# 安装 Percona Toolkit
sudo apt-get install percona-toolkit
# 使用 pt-duplicate-key-checker 检查冗余索引
pt-duplicate-key-checker --host=localhost --user=root --password=password4. 使用 MySQL Enterprise Monitor
MySQL Enterprise Monitor 提供了索引分析功能，可以自动识别冗余索引。
5. 分析索引使用情况
可以通过 performance_schema 查看索引使用情况：
sql
-- 启用索引使用统计
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'wait/io/file/innodb/innodb_data_file';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'statement/%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_waits%';
-- 查看索引使用情况
SELECT
object_schema,
object_name,
index_name,
count_read,
count_write
FROM
performance_schema.table_io_waits_summary_by_index_usage
WHERE
object_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY
count_read + count_write ASC;清理冗余索引的步骤
1. 备份数据库
在清理索引之前，一定要备份数据库，以防出现意外情况：
bash
# 使用 mysqldump 备份数据库
mysqldump -u root -p --all-databases > all_databases_backup.sql2. 确认索引冗余性
对于识别出的冗余索引，需要仔细分析其是否真的冗余。特别是对于：
- 复合索引的前缀索引
- 不同顺序的相同列索引
- 可能在不同查询场景下使用的索引
3. 测试删除索引的影响
在测试环境中删除索引，然后运行关键查询，确认性能没有下降：
sql
-- 在测试环境中删除索引
DROP INDEX index_name ON table_name;
-- 运行关键查询并查看执行计划
EXPLAIN SELECT * FROM table_name WHERE column1 = 'value1' AND column2 = 'value2';
-- 运行查询并记录执行时间
SELECT SQL_NO_CACHE * FROM table_name WHERE column1 = 'value1' AND column2 = 'value2';4. 生产环境删除索引
在确认安全后，可以在生产环境中删除冗余索引：
sql
-- 删除冗余索引
DROP INDEX index_name ON table_name;5. 监控删除后的性能
删除索引后，需要监控数据库性能，特别是写入操作的性能变化：
sql
-- 监控写入操作性能
SHOW GLOBAL STATUS LIKE 'Handler_write';
SHOW GLOBAL STATUS LIKE 'Handler_update';
SHOW GLOBAL STATUS LIKE 'Handler_delete';
-- 监控查询性能
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Queries';最佳实践
1. 定期检查冗余索引
建议定期（如每月或每季度）检查和清理冗余索引，特别是在以下情况下：
- 表结构发生变化后
- 应用程序查询模式发生变化后
- 数据库性能下降时
2. 合理设计索引
- 遵循索引设计原则：选择合适的索引列，避免过度索引
- 优先考虑复合索引，而不是多个单列索引
- 考虑索引的选择性和基数
- 避免在低选择性列上创建索引
3. 使用工具辅助
- 利用 sys.schema_redundant_indexes 视图进行日常检查
- 使用 pt-duplicate-key-checker 工具进行深度分析
- 考虑使用第三方监控工具进行持续监控
4. 分批处理
对于大型数据库，建议分批处理冗余索引，避免一次性删除过多索引导致系统负载过高。
5. 记录索引变更
建立索引变更的记录机制，包括：
- 索引名称和表名
- 删除原因
- 删除时间
- 操作人
- 后续性能影响
常见问题（FAQ）
Q1: 如何区分重复索引和冗余覆盖索引？
A1: 重复索引是完全相同的索引定义，包括索引名、列名和顺序。冗余覆盖索引是指一个索引的列是另一个索引的前缀，或者两个索引包含相同的列但顺序不同，例如：
- 索引1：(col1, col2)
- 索引2：(col1) -- 这是索引1的前缀，是冗余索引
- 索引3：(col2, col1) -- 包含相同列但顺序不同，可能是冗余索引
Q2: 如何处理从未使用过的索引？
A2: 对于从未使用过的索引，可以考虑删除，但需要注意：
- 确认监控周期足够长，覆盖了所有查询场景
- 检查是否有周期性任务或报表查询使用该索引
- 考虑业务发展，是否未来会使用该索引
Q3: 删除冗余索引会影响正在运行的查询吗？
A3: 删除索引是一个DDL操作，会锁定表（InnoDB在MySQL 5.6+支持在线DDL）。对于大型表，建议在业务低峰期执行，并使用在线DDL：
sql
ALTER TABLE table_name DROP INDEX index_name, ALGORITHM=INPLACE, LOCK=NONE;Q4: 如何避免创建冗余索引？
A4: 可以通过以下方法避免创建冗余索引：
- 建立索引设计规范和评审流程
- 使用工具检查索引设计
- 定期审查现有索引
- 培训开发人员了解索引设计原则
Q5: 复合索引的顺序对冗余性有影响吗？
A5: 是的，复合索引的顺序很重要。例如，索引(col1, col2)和(col2, col1)是不同的索引，可能都有各自的使用场景，不一定是冗余索引。
Q6: 如何处理主键索引和唯一索引的关系？
A6: 主键索引是表的物理顺序，唯一索引是逻辑约束。主键索引通常是必须的，而唯一索引需要根据业务需求设计。如果唯一索引的列包含主键列，可能是冗余的。
Q7: 冗余索引会影响备份和恢复性能吗？
A7: 是的，冗余索引会增加备份和恢复的时间和空间消耗，因为备份工具需要处理更多的数据。
Q8: 如何监控索引使用情况？
A8: 可以通过以下方式监控索引使用情况：
- 使用 performance_schema.table_io_waits_summary_by_index_usage 视图
- 启用 slow_query_log 并分析查询执行计划
- 使用第三方监控工具如 Prometheus + Grafana
- 定期运行 pt-index-usage 工具分析慢查询日志
Q9: 如何处理外键索引？
A9: 外键索引是由MySQL自动创建的，用于维护引用完整性。在删除外键索引之前，需要先删除对应的外键约束。
Q10: 冗余索引清理后，需要重建其他索引吗？
A10: 一般情况下不需要，但如果其他索引存在碎片问题，可以考虑重建：
sql
-- 重建单个索引
ALTER TABLE table_name DROP INDEX index_name, ADD INDEX index_name (column1, column2);
-- 重建所有索引（会锁定表）
ALTER TABLE table_name ENGINE=InnoDB;案例分析
案例1：电商订单表冗余索引
问题：电商订单表 orders 有以下索引：
- PRIMARY KEY (order_id)
- INDEX idx_user_id (user_id)
- INDEX idx_user_id_order_date (user_id, order_date)
- INDEX idx_order_date (order_date)
分析：idx_user_id 是 idx_user_id_order_date 的前缀，是冗余索引。
解决方案：删除 idx_user_id 索引。
结果：
- 减少了约10%的磁盘空间使用
- 插入订单的性能提升了8%
- 查询性能保持不变
案例2：用户表无用索引
问题：用户表 users 有一个 INDEX idx_email (email)，但监控发现该索引从未被使用。
分析：应用程序可能使用了其他方式查询用户，或者该索引是为未来功能准备的。
解决方案：
- 延长监控周期，确认该索引确实从未使用
- 检查应用程序代码，确认是否有使用该索引的查询
- 与开发团队沟通，确认该索引的用途
- 确认无用后，删除该索引
结果：
- 减少了磁盘空间使用
- 提升了用户注册和更新的性能
工具推荐
| 工具名称 | 功能 | 适用场景 |
|---|---|---|
| pt-duplicate-key-checker | 自动识别冗余索引 | 定期检查和清理 |
| sys.schema_redundant_indexes | 查看冗余索引 | 日常监控 |
| pt-index-usage | 分析索引使用情况 | 慢查询分析 |
| MySQL Enterprise Monitor | 自动监控和分析 | 企业级监控 |
| Percona Monitoring and Management (PMM) | 可视化监控和分析 | 大规模数据库集群 |
不同版本的差异
MySQL 5.6
- 支持在线DDL，但功能有限
- 没有 sys.schema_redundant_indexes 视图
- performance_schema 功能有限
MySQL 5.7
- 增强了在线DDL支持
- 引入了 sys.schema_redundant_indexes 视图
- 增强了 performance_schema 功能
MySQL 8.0
- 进一步增强了在线DDL支持
- 引入了不可见索引，便于测试索引删除
- 增强了 sys schema 视图
- 引入了即时DDL，某些DDL操作更快