🚀 MySQL慢查詢全攻略：定位、分析與優化實戰

#數據庫優化 #性能調優 #SQL優化 #MySQL實戰

---

一、慢查詢定位：找到性能瓶頸

1.1 開啟慢查詢日志

-- 查看當前配置  
SHOW VARIABLES LIKE '%slow_query%';  -- 動態開啟（重啟失效）  
SET GLOBAL slow_query_log = 'ON';  
SET GLOBAL long_query_time = 2;  -- 閾值設為2秒  
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';  -- 永久生效（修改my.cnf）  
[mysqld]  
slow_query_log = 1  
slow_query_log_file = /var/log/mysql/slow.log  
long_query_time = 2  
log_queries_not_using_indexes = 1  -- 記錄未走索引的查詢  

1.2 分析工具推薦

工具	使用場景	命令示例
mysqldumpslow	官方自帶，基礎分析	mysqldumpslow -s t /path/to/slow.log
pt-query-digest	高級分析，生成詳細報告	pt-query-digest slow.log > report.txt
Percona Toolkit	專業級分析，支持多維度統計	pt-query-digest --filter '$event->{arg} =~ m/SELECT/i' slow.log

---

二、核心優化策略：從SQL到架構

2.1 索引優化（90%的性能問題根源）

黃金法則：

• 最左前綴原則：聯合索引按字段順序匹配
• 覆蓋索引：SELECT字段全在索引中，避免回表
• 索引選擇性：區分度高的字段（如唯一ID）優先建索引

示例優化：

-- 優化前（全表掃描）  
SELECT * FROM orders WHERE status = 'paid' AND create_time > '2023-01-01';  -- 添加聯合索引  
ALTER TABLE orders ADD INDEX idx_status_time(status, create_time);  -- 優化后（索引范圍掃描）  
SELECT id, status, amount FROM orders   
WHERE status = 'paid' AND create_time > '2023-01-01';  

2.2 SQL語句重構

常見問題與解決方案：

問題類型	優化方案	示例
大分頁查詢	使用WHERE替代LIMIT OFFSET	WHERE id > 1000 LIMIT 10
隱式類型轉換	保持字段與參數類型一致	WHERE phone = '13800138000'
不必要的排序	移除ORDER BY或添加索引	添加INDEX(create_time)
IN子查詢	改用JOIN	JOIN (SELECT id FROM ...) tmp

2.3 EXPLAIN執行計劃解析

關鍵字段解讀：

EXPLAIN SELECT * FROM users WHERE age > 20;  

字段	理想值	問題信號
type	ref/range/index	ALL（全表掃描）
key	使用索引名稱	NULL（未用索引）
rows	掃描行數少	數值過大（如>10000）
Extra	Using index	Using filesort/Using temporary

---

三、高級調優：參數與架構升級

3.1 參數優化（my.cnf關鍵配置）

[mysqld]  
# 緩沖池大小（通常設為物理內存的70%-80%）  
innodb_buffer_pool_size = 8G  # 日志寫入策略  
innodb_flush_log_at_trx_commit = 1  # 高安全要求  
innodb_flush_log_at_trx_commit = 2  # 高性能場景  # 連接管理  
max_connections = 500  
thread_cache_size = 50  

3.2 架構升級方案

場景	解決方案	優勢
單表數據量過大（>5000萬）	分庫分表（Sharding）	水平擴展，降低單表壓力
高頻復雜查詢	讀寫分離（主從復制）	分散讀壓力
實時分析需求	使用列式存儲（如ClickHouse）	提升聚合查詢速度

---

四、預防與監控：建立長效機制

4.1 實時監控工具

• Percona Monitoring and Management (PMM)：監控慢查詢、鎖等待
• Prometheus + Grafana：自定義指標可視化
• MySQL Enterprise Monitor：官方企業級方案

4.2 自動化優化建議

-- 使用內置診斷工具  
ANALYZE TABLE orders;  -- 更新統計信息  
OPTIMIZE TABLE logs;   -- 重建表（針對碎片化嚴重場景）  -- 查詢優化建議器  
SELECT * FROM sys.schema_index_statistics;  
SELECT * FROM sys.statements_with_full_table_scans;  

---

五、實戰案例：電商訂單查詢優化

5.1 原始慢查詢（執行時間3.2秒）

SELECT * FROM orders  
WHERE user_id = 1001  AND status IN ('paid', 'shipped')  
ORDER BY create_time DESC  
LIMIT 0, 10;  

5.2 優化步驟

1. 執行計劃分析：發現type=ALL，未使用索引

2. 創建覆蓋索引：

   ALTER TABLE orders ADD INDEX idx_user_status_time(user_id, status, create_time);  
   

3. SQL改寫：

   SELECT id, user_id, status, amount, create_time  
   FROM orders  
   WHERE user_id = 1001  AND status IN ('paid', 'shipped')  
   ORDER BY create_time DESC  
   LIMIT 10;  
   

4. 結果：執行時間降至28ms，提升115倍！

---

總結：MySQL慢查詢優化需結合索引策略、SQL重構、參數調優三位一體。通過EXPLAIN分析執行計劃，使用pt-query-digest定位問題查詢，建立監控體系預防性能退化，方能實現數據庫高效穩定運行。