MySQL 索引维护

索引维护概述

索引维护的重要性

索引在MySQL数据库中扮演着加速查询的重要角色，但索引并非一劳永逸，需要定期维护。随着数据的不断变化，索引可能会出现以下问题：

碎片化：频繁的插入、更新、删除操作导致索引页分裂，产生碎片，降低查询效率
过时索引：业务需求变化，原有的索引不再被查询使用，浪费存储空间和维护成本
冗余索引：存在多个功能相似的索引，增加写入操作的开销
统计信息不准确：索引统计信息过时，导致查询优化器选择错误的执行计划
索引膨胀：索引大小不合理增长，影响整体性能

索引维护的目标

目标	具体描述
提高查询性能	确保索引被有效使用，减少查询时间
降低维护成本	减少不必要的索引，降低写入操作的开销
优化存储	减少索引占用的存储空间，提高存储效率
提高写入性能	减少索引对插入、更新、删除操作的影响
确保统计信息准确	帮助查询优化器选择正确的执行计划

索引维护的频率

索引维护的频率取决于以下因素：

因素	维护频率建议
数据更新频率	频繁更新：每周维护稳定更新：每月维护很少更新：每季度维护
数据量大小	大数据量：更频繁维护，如每周或每两周
业务重要性	核心业务：更频繁维护，确保高性能
查询性能变化	性能下降时立即维护，及时解决问题

索引维护前的准备

工具准备

工具	用途	安装命令
MySQL客户端	执行索引维护命令	系统自带
pt-index-usage	分析索引使用情况	percona-toolkit
pt-duplicate-key-checker	检测冗余索引	percona-toolkit
pt-table-sync	同步数据（维护索引后可能需要）	percona-toolkit
mysqlcheck	检查和修复表	系统自带

环境准备

备份数据：

bash

# 全量备份
mysqldump -u root -p --all-databases > full_backup.sql

# 或备份特定表
mysqldump -u root -p mydb mytable > table_backup.sql

选择维护时间：
- 业务低峰期（如凌晨2:00-4:00）
- 提前通知相关团队，做好沟通
- 预留足够的维护时间，考虑回滚时间
监控准备：
- 开启慢查询日志，便于分析查询性能
- 监控数据库性能指标，如CPU、内存、I/O等
- 准备回滚方案，确保出现问题时可以快速恢复

索引使用情况分析

查看索引使用统计

使用Performance Schema

sql

-- 确保Performance Schema启用
SHOW VARIABLES LIKE 'performance_schema';

-- 查看索引使用情况
SELECT 
    OBJECT_SCHEMA AS db_name,
    OBJECT_NAME AS table_name,
    INDEX_NAME,
    COUNT_FETCH AS index_scans,
    COUNT_INSERT AS insert_ops,
    COUNT_UPDATE AS update_ops,
    COUNT_DELETE AS delete_ops
FROM 
    performance_schema.table_io_waits_summary_by_index_usage
WHERE 
    OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY 
    index_scans ASC;

分析未使用的索引

sql

-- 查找未使用的索引
SELECT 
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.INDEX_NAME
FROM 
    information_schema.STATISTICS t
LEFT JOIN 
    performance_schema.table_io_waits_summary_by_index_usage s ON 
        t.TABLE_SCHEMA = s.OBJECT_SCHEMA AND 
        t.TABLE_NAME = s.OBJECT_NAME AND 
        t.INDEX_NAME = s.INDEX_NAME
WHERE 
    s.INDEX_NAME IS NULL AND 
    t.TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema') AND
    t.INDEX_NAME != 'PRIMARY';

使用Percona工具分析

使用pt-index-usage

bash

# 分析慢查询日志中的索引使用情况
pt-index-usage slow.log --database=mydb > index-usage.txt

# 查看报告摘要
cat index-usage.txt | grep -A 20 "SUMMARY"

# 查看未使用的索引
cat index-usage.txt | grep "^# Indexes with no hits"

检测冗余索引

bash

# 检测冗余索引
pt-duplicate-key-checker h=localhost,u=root,p=password --database=mydb

# 输出示例
# # 圝圝圝 Table mydb.users圝圝圝
# # 圝圝圝 mydb.users idx_username (username) is a left-prefix of mydb.users idx_username_email (username, email)
# # 圝圝圝 mydb.users idx_username is redundant
# # Key definitions:
# #   KEY `idx_username` (`username`),
# #   KEY `idx_username_email` (`username`,`email`),
# # Column types:
# #   `username` varchar(50) not null
# #   `email` varchar(100) not null
# # To remove redundant index:
# ALTER TABLE `mydb`.`users` DROP INDEX `idx_username`;

索引维护操作

重建索引

重建索引是解决索引碎片、更新统计信息的有效方法，适用于索引碎片化严重或统计信息不准确的情况。

使用ALTER TABLE重建

sql

-- 重建表的所有索引（会锁表，建议在低峰期使用）
ALTER TABLE users ENGINE=InnoDB;

-- 或
ALTER TABLE users FORCE;

-- 重建单个索引
ALTER TABLE users DROP INDEX idx_username, ADD INDEX idx_username (username);

使用OPTIMIZE TABLE

sql

-- 优化表，重建索引（会锁表）
OPTIMIZE TABLE users;

-- 对于InnoDB表，OPTIMIZE TABLE会重建表和索引
-- 等同于 ALTER TABLE users ENGINE=InnoDB

使用REPAIR TABLE

sql

-- 仅适用于MyISAM表，InnoDB表不支持
REPAIR TABLE myisam_table QUICK;
REPAIR TABLE myisam_table EXTENDED;

在线重建索引（MySQL 8.0+）

MySQL 8.0引入了在线重建索引的功能，可以在不锁表或只使用共享锁的情况下重建索引，极大地减少了对业务的影响：

sql

-- 在线重建索引，无锁
ALTER TABLE users DROP INDEX idx_username, ADD INDEX idx_username (username) ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM选项	描述
COPY	复制表到新表，锁表时间长，适用于所有版本
INPLACE	原地重建，锁表程度低，MySQL 5.6+支持
DEFAULT	自动选择最佳算法，推荐使用

LOCK选项	描述
SHARED	共享锁，允许读，阻止写，MySQL 5.6+支持
EXCLUSIVE	排他锁，阻止读写，默认选项
NONE	无锁，允许读写，MySQL 8.0+支持
DEFAULT	自动选择最佳锁策略，推荐使用

删除未使用和冗余索引

删除未使用和冗余索引可以减少维护成本，提高写入性能。

删除未使用的索引

sql

-- 生成删除未使用索引的语句
SELECT 
    CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' DROP INDEX ', INDEX_NAME, ';') AS drop_sql
FROM 
    information_schema.STATISTICS t
LEFT JOIN 
    performance_schema.table_io_waits_summary_by_index_usage s ON 
        t.TABLE_SCHEMA = s.OBJECT_SCHEMA AND 
        t.TABLE_NAME = s.OBJECT_NAME AND 
        t.INDEX_NAME = s.INDEX_NAME
WHERE 
    s.INDEX_NAME IS NULL AND 
    t.TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema') AND
    t.INDEX_NAME != 'PRIMARY';

-- 执行生成的删除语句，建议先在测试环境验证
ALTER TABLE mydb.users DROP INDEX idx_unused;

删除冗余索引

sql

-- 使用pt-duplicate-key-checker生成的删除语句
ALTER TABLE `mydb`.`users` DROP INDEX `idx_username`;

更新索引统计信息

MySQL的查询优化器依赖于准确的索引统计信息来选择最佳执行计划，定期更新统计信息非常重要。

自动更新统计信息

InnoDB会自动更新统计信息，但在某些情况下（如大量数据变更）需要手动更新：

sql

-- 查看自动更新统计信息的配置
SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';
SHOW VARIABLES LIKE 'innodb_stats_persistent_sample_pages';

手动更新统计信息

sql

-- 更新单个表的统计信息
ANALYZE TABLE users;

-- 更新多个表的统计信息
ANALYZE TABLE users, orders, products;

-- 使用InnoDB特定的统计信息更新（MySQL 5.6+）
ANALYZE TABLE users PERSISTENT FOR ALL;

大表统计信息更新优化

对于大表，更新统计信息可能会很慢，可以调整采样页数，减少扫描的数据量：

sql

-- 临时调整采样页数，适用于当前会话
SET SESSION innodb_stats_persistent_sample_pages = 1000;
ANALYZE TABLE large_table;

-- 或永久调整表的采样页数
ALTER TABLE large_table STATS_SAMPLE_PAGES = 1000;
ANALYZE TABLE large_table;

索引维护的最佳实践

日常维护

定期分析索引使用情况：
- 每周使用pt-index-usage分析慢查询日志，了解索引使用情况
- 每月使用pt-duplicate-key-checker检测冗余索引，及时清理
- 每季度全面检查索引使用情况，评估索引效果
监控索引相关指标：
- 监控慢查询数量，及时发现性能问题
- 监控全表扫描的查询，评估是否需要添加索引
- 监控索引的大小变化，防止索引膨胀
制定索引维护计划：
- 明确维护周期，根据业务需求调整
- 明确维护流程，确保操作规范
- 明确责任人，落实维护工作
- 准备回滚方案，降低风险

维护中的注意事项

避免在业务高峰期维护：
- 选择凌晨或业务低峰期进行维护
- 提前通知相关团队，做好沟通
- 预留足够的维护时间，考虑回滚时间
备份数据：
- 维护前备份相关表或数据库
- 确保备份可用，定期验证备份有效性
- 制定回滚方案，确保出现问题时可以快速恢复
逐步执行：
- 先在测试环境验证维护操作，确保安全
- 生产环境逐步执行，每执行一步验证结果
- 从非核心表开始维护，积累经验后再维护核心表
监控执行过程：
- 监控数据库负载，防止资源耗尽
- 监控查询性能，确保维护效果
- 监控锁等待情况，避免长时间锁表影响业务
记录维护过程：
- 记录执行的命令和参数
- 记录执行时间和持续时间
- 记录执行结果和性能变化
- 记录遇到的问题和解决方案

维护后的验证

验证索引状态：

sql

-- 查看索引是否存在
SHOW INDEX FROM users;

-- 查看索引使用情况
SHOW GLOBAL STATUS LIKE 'Handler_read%';

验证查询性能：

sql

-- 使用EXPLAIN验证查询计划
EXPLAIN SELECT * FROM users WHERE username = 'admin';

-- 对比维护前后的查询执行时间
SELECT BENCHMARK(100000, SELECT * FROM users WHERE username = 'admin');

验证应用功能：
- 执行核心业务查询，确保功能正常
- 验证写入操作正常，确保数据完整性
- 验证应用功能正常，确保业务不受影响

索引维护案例

案例一：电商订单表索引维护

场景描述

某电商平台的订单表（orders）有1000万条数据，近期查询性能下降，慢查询数量增加，需要进行索引维护。

维护步骤

分析索引使用情况：

bash

pt-index-usage slow.log --database=orders_db > index-usage.txt
pt-duplicate-key-checker h=localhost,u=root,p=password --database=orders_db

发现问题：
- 发现3个未使用的索引，占用存储空间
- 发现2个冗余索引，增加写入开销
- 索引存在碎片化，查询效率下降
制定维护计划：
- 时间：凌晨2:00-4:00，业务低峰期
- 步骤：先删除冗余索引，再删除未使用的索引，最后重建核心索引
- 回滚方案：准备好表备份，随时可以恢复

执行维护：

sql

-- 删除冗余索引
ALTER TABLE orders DROP INDEX idx_user_id, DROP INDEX idx_order_date;

-- 删除未使用的索引
ALTER TABLE orders DROP INDEX idx_unused1, DROP INDEX idx_unused2, DROP INDEX idx_unused3;

-- 重建核心索引，使用在线重建减少影响
ALTER TABLE orders DROP INDEX idx_order_no, ADD INDEX idx_order_no (order_no) ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE orders DROP INDEX idx_user_id_order_date, ADD INDEX idx_user_id_order_date (user_id, order_date DESC) ALGORITHM=INPLACE, LOCK=NONE;

-- 更新统计信息
ANALYZE TABLE orders;

验证结果：
- 查询性能提升30%，平均响应时间从500ms降至350ms
- 写入性能提升15%，平均响应时间从200ms降至170ms
- 索引总大小减少25%，从2GB降至1.5GB
- 慢查询数量减少70%，从100条/天降至30条/天
- 应用功能正常，无业务影响

案例二：MySQL 8.0在线索引重建

场景描述

某金融系统使用MySQL 8.0，核心交易表（transactions）需要重建索引，但不能影响业务运行，要求零停机维护。

维护步骤

选择在线重建方式：

sql

-- 使用在线重建，无锁，确保不影响业务
ALTER TABLE transactions DROP INDEX idx_transaction_date, ADD INDEX idx_transaction_date (transaction_date) ALGORITHM=INPLACE, LOCK=NONE;

监控执行过程：

sql

-- 查看进程状态，确保执行正常
SHOW PROCESSLIST;

-- 查看锁等待情况，确保无阻塞
SHOW ENGINE INNODB STATUS\G

-- 查看性能指标，监控资源使用
SHOW GLOBAL STATUS LIKE 'Innodb_rows_read';
SHOW GLOBAL STATUS LIKE 'Innodb_rows_written';

验证结果：
- 索引成功重建，无业务中断
- 查询性能提升20%，交易响应更快
- 写入操作正常，无数据丢失
- 系统负载稳定，资源使用正常

常见索引维护问题及解决方案

问题	解决方案
维护过程中锁表，影响业务	使用在线重建索引（ALGORITHM=INPLACE, LOCK=NONE），MySQL 8.0+支持
维护时间过长，超出预期	分片维护，先维护非核心表，再维护核心表；调整采样页数，减少统计信息更新时间
维护后性能下降，执行计划变差	验证执行计划，手动更新统计信息；检查是否误删了有用索引
误删有用索引，导致查询性能下降	维护前备份索引定义，准备回滚方案；快速重建误删的索引
索引重建失败，报错退出	检查错误日志，分析失败原因；调整维护策略，如减少并发操作、增加资源等

总结

索引维护是MySQL数据库运维的重要组成部分，通过定期的索引维护可以：

提高查询性能：确保索引被有效使用，减少查询时间
降低维护成本：减少不必要的索引，降低写入操作的开销
优化存储：减少索引占用的存储空间，提高存储效率
提高写入性能：减少索引对插入、更新、删除操作的影响
确保统计信息准确：帮助查询优化器选择正确的执行计划

作为DBA，应该建立完善的索引维护机制，定期分析索引使用情况，及时删除未使用和冗余索引，定期重建核心索引，记录维护过程和结果，不断优化维护策略。

通过科学的索引维护，可以确保MySQL数据库的高性能运行，为业务提供稳定可靠的支持，尤其是在大数据量、高并发的生产环境中，索引维护更是至关重要。

MySQL 索引维护 ​

索引维护概述 ​

索引维护的重要性 ​

索引维护的目标 ​

索引维护的频率 ​

索引维护前的准备 ​

工具准备 ​

环境准备 ​

索引使用情况分析 ​

查看索引使用统计 ​

使用Performance Schema ​

分析未使用的索引 ​

使用Percona工具分析 ​

使用pt-index-usage ​

检测冗余索引 ​

索引维护操作 ​

重建索引 ​

使用ALTER TABLE重建 ​

使用OPTIMIZE TABLE ​

使用REPAIR TABLE ​

在线重建索引（MySQL 8.0+） ​

删除未使用和冗余索引 ​

删除未使用的索引 ​

删除冗余索引 ​

更新索引统计信息 ​

自动更新统计信息 ​

手动更新统计信息 ​

大表统计信息更新优化 ​

索引维护的最佳实践 ​

日常维护 ​

维护中的注意事项 ​

维护后的验证 ​

索引维护案例 ​

案例一：电商订单表索引维护 ​

场景描述 ​

维护步骤 ​

案例二：MySQL 8.0在线索引重建 ​

场景描述 ​

维护步骤 ​

常见索引维护问题及解决方案 ​

总结 ​

MySQL 索引维护

索引维护概述

索引维护的重要性

索引维护的目标

索引维护的频率

索引维护前的准备

工具准备

环境准备

索引使用情况分析

查看索引使用统计

使用Performance Schema

分析未使用的索引

使用Percona工具分析

使用pt-index-usage

检测冗余索引

索引维护操作

重建索引

使用ALTER TABLE重建

使用OPTIMIZE TABLE

使用REPAIR TABLE

在线重建索引（MySQL 8.0+）

删除未使用和冗余索引

删除未使用的索引

删除冗余索引

更新索引统计信息

自动更新统计信息

手动更新统计信息

大表统计信息更新优化

索引维护的最佳实践

日常维护

维护中的注意事项

维护后的验证

索引维护案例

案例一：电商订单表索引维护

场景描述

维护步骤

案例二：MySQL 8.0在线索引重建

场景描述

维护步骤

常见索引维护问题及解决方案

总结