您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 慢查詢日誌配置與 SQL 優化指南：開啟閾值設置、索引失效場景與優化最佳實踐

編輯：MySQL 綜合教學

一、慢查詢日誌啟用與配置

查看當前狀態

SHOW VARIABLES LIKE 'slow%';

slow_query_log：ON/OFF
slow_query_log_file：日誌文件路徑
long_query_time：閾值 (秒)，預設 10s

動態開啟 (即時生效)

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2; -- 記錄超過 2 秒的查詢

持久化配置

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = ON  -- 記錄未用索引查詢 (慎用，量大)

分析工具

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log：按耗時排序 Top 10。
pt-query-digest (Percona Toolkit)：專業分析、聚合、報告。

二、SQL 優化十大鐵律

避免 SELECT *：只取需要列，減少網絡傳輸、內存佔用、覆蓋索引機會。
用 JOIN 替代子查詢：MySQL 子查詢易產生臨時表，JOIN 可直接利用索引嵌套循環。
優先 ENUM/CHAR 代替 VARCHAR：固定長度利於存儲與比較，ENUM 內部存數字極省空間。
盡可能 NOT NULL：NULL 需額外位圖、索引統計複雜、比較語義特殊。
固定長度表更快：無變長列 (VARCHAR/TEXT/BLOB) 表，MySQL 可按位置偏移快速定位行。
拆分大 DELETE/INSERT：分批次 (如 500-1000 行) 提交，避免長事務鎖表、主從延遲、Binlog 膨脹。
查詢列越小越快：INT 優於 BIGINT，CHAR(10) 優於 VARCHAR(255)。
WHERE 條件順序優化：將過濾性最強 (選擇率最低) 的條件前置，減少後續掃描行數。
EXPLAIN 驗證執行計劃：關注 type (system/const/eq_ref/ref/range/index/ALL)、key、rows、Extra (Using index/Using where/Using temporary/Using filesort)。
定期 ANALYZE TABLE：更新索引統計信息，防止優化器誤判。

三、導致索引失效的典型 WHERE 陷阱

場景	錯誤寫法	正確寫法	原因
不等於	WHERE status != 1	WHERE status IN (0,2,3) 或 UNION	!= 導致全表掃描
函數/表達式作用於列	WHERE DATE(create_time) = '2023-01-01'	WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02'	函數破壞索引順序
隱式類型轉換	WHERE phone = 13800000000 (phone 是 VARCHAR)	WHERE phone = '13800000000'	字串轉數字導致索引失效
LIKE 以 % 開頭	WHERE name LIKE '%張%'	全文索引 / Elasticsearch	B+樹無法利用前綴匹配
LIKE 固定前綴	WHERE name LIKE '張%'	同上 (索引生效)	前綴匹配可用索引
OR 條件列無索引	WHERE a=1 OR b=2 (b 無索引)	UNION ALL 分開查詢	OR 要求兩側都有索引才用 index_merge
IS NULL / IS NOT NULL	WHERE col IS NULL	可用索引 (MySQL 支援)	注意 NOT NULL 列優化

四、實戰優化檢查清單

[ ] 1. EXPLAIN 確認 type 非 ALL/index
[ ] 2. key 欄位顯示預期索引
[ ] 3. rows 預估行數合理 (非全表)
[ ] 4. Extra 無 Using temporary / Using filesort
[ ] 5. WHERE 條件無函數/隱式轉換/!=/前導%
[ ] 6. 覆蓋索引優化 (Using index)
[ ] 7. 大表 DDL 走 pt-online-schema-change
[ ] 8. 慢查詢日誌定期 pt-query-digest 分析