操作背景
在日常的數據庫運維工作中，我們經常會遇到性能突然下降的情況。最近在我們的測試環境中，就遇到了一個典型的性能問題：一個核心業務查詢的響應時間從平時的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;
記住，在生產環境執行任何維護操作前，務必進行備份并在測試環境驗證！
