操作背景
在日常的数据库运维工作中，我们经常会遇到性能突然下降的情况。最近在我们的测试环境中，就遇到了一个典型的性能问题：一个核心业务查询的响应时间从平时的200毫秒突然增加到了10秒以上。经过排查，发现这是由于表索引碎片过高导致的性能下降。
我们的测试环境模拟了生产环境的数据库结构和数据量，使用的是MySQL 8.0版本。问题出现在一个订单查询接口上，该接口支撑着商城的订单查看功能。
问题发现与初步分析
首先，我们通过监控系统发现订单查询的响应时间在特定时间段内出现了异常峰值：
-- 查看慢查询日志中关于订单查询的记录
SELECT * FROM mysql.slow_log
WHERE sql_text LIKE '%SELECT * FROM orders%'
AND start_time > '2023-08-20 10:00:00'
ORDER BY start_time DESC;
查询结果显示，确实有多条订单查询语句执行时间超过了5秒，被记录到了慢查询日志中。
深入排查过程
1. 分析查询执行计划
我们先对问题查询语句进行执行计划分析：
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 12345
AND order_status = 'completed'
ORDER BY create_time DESC
LIMIT 20;
执行计划显示，虽然查询使用了复合索引 idx_user_status (user_id, order_status)，但是预估行数远高于实际返回行数，这是一个典型索引问题的迹象。
2. 检查索引状态
接下来我们检查相关索引的状态：
-- 查看订单表的索引信息
SHOW INDEX FROM orders;
-- 检查索引碎片情况
SELECT
table_name,
index_name,
round(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS index_size_mb,
stat_description
FROM mysql.innodb_index_stats
WHERE table_name = 'orders'
AND database_name = 'ecommerce_db'
AND stat_name = 'size';
3. 计算索引碎片率
我们发现 idx_user_status 索引的碎片率异常高：
-- 计算索引碎片率
SELECT
table_name,
index_name,
round((data_free / (data_length + index_length)) * 100, 2) AS fragmentation_percentage
FROM information_schema.tables
WHERE table_name = 'orders'
AND table_schema = 'ecommerce_db';
查询结果显示碎片率达到了35%，远高于建议的10%阈值。
问题解决步骤
1. 选择维护窗口
由于这是测试环境，我们可以在任意时间进行操作。但在生产环境中，需要在业务低峰期执行维护操作。
2. 执行在线索引重建
我们使用Online DDL方式重建索引，避免表锁影响业务：
-- 重建索引
ALTER TABLE orders ALTER INDEX idx_user_status VISIBLE;
-- 使用Online DDL重建索引（MySQL 5.6+支持）
ALTER TABLE orders ENGINE=InnoDB;
-- 或者直接重建表（需要更多时间但效果更好）
OPTIMIZE TABLE orders;
3. 监控重建过程
在重建过程中，我们监控进程状态：
-- 查看当前运行的DDL操作
SELECT * FROM information_schema.innodb_alter_table_status;
-- 查看进程列表
SHOW PROCESSLIST;
4. 验证修复效果
索引重建完成后，我们再次检查性能：
-- 再次分析查询执行计划
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 12345
AND order_status = 'completed'
ORDER BY create_time DESC
LIMIT 20;
-- 检查碎片率
SELECT
table_name,
round((data_free / (data_length + index_length)) * 100, 2) AS fragmentation_percentage
FROM information_schema.tables
WHERE table_name = 'orders'
AND table_schema = 'ecommerce_db';
预防措施
为了防止类似问题再次发生，我们实施了以下预防措施：
1. 建立定期维护任务
-- 创建定期优化任务的存储过程
DELIMITER //
CREATE PROCEDURE maintain_tables()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE tname VARCHAR(64);
DECLARE cur CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'ecommerce_db'
AND engine = 'InnoDB';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO tname;
IF done THEN
LEAVE read_loop;
END IF;
-- 检查碎片率，超过20%则优化
SET @fragmentation := 0;
SELECT round((data_free / (data_length + index_length)) * 100, 2)
INTO @fragmentation
FROM information_schema.tables
WHERE table_name = tname
AND table_schema = 'ecommerce_db';
IF @fragmentation > 20 THEN
SET @sql := CONCAT('OPTIMIZE TABLE ', tname);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END LOOP;
CLOSE cur;
END //
DELIMITER ;
-- 创建定期执行的事件
CREATE EVENT nightly_maintenance
ON SCHEDULE EVERY 1 WEEK
STARTS '2023-08-27 02:00:00'
DO
CALL maintain_tables();
2. 加强监控告警
我们更新了监控系统，添加了对表碎片率的监控，当任何核心表的碎片率超过20%时自动发送告警。
总结
这次索引碎片导致的性能问题是一个典型的数据库维护案例。通过这次经历，我们更加认识到定期数据库维护的重要性。关键 takeaways：
- 需要定期监控关键表的索引碎片率
- 建立自动化的维护流程预防问题发生
- 使用Online DDL减少维护对业务的影响
- 完善的监控告警系统能够帮助及早发现问题
数据库运维不仅仅是处理紧急故障，更重要的是建立预防机制，通过定期维护和监控来避免问题的发生。希望这次的经验分享对大家的数据库运维工作有所帮助。
附录：常用索引维护命令
-- 查看表状态
SHOW TABLE STATUS LIKE 'orders';
-- 分析表键的分布
ANALYZE TABLE orders;
-- 检查表错误
CHECK TABLE orders;
-- 修复表（MyISAM引擎适用）
REPAIR TABLE orders;
-- 清空表并重置自增ID
TRUNCATE TABLE orders;
记住，在生产环境执行任何维护操作前，务必进行备份并在测试环境验证！
