MySQL 日常性能優化核心技巧總結
本文匯總慢查詢分析、EXPLAIN 解讀、Hint 用法、查詢快取、自動優化、索引排序、不可達查詢檢測等實戰技巧，幫助 DBA 與開發快速定位瓶頸。
一、慢查詢日誌與 mysqldumpslow
1. 開啟配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 5
log_queries_not_using_indexes = 1
2. mysqldumpslow 常用參數
| 參數 | 含義 |
|---|
-s at | 按平均查詢時間排序（預設） |
-s c | 按出現次數排序 |
-t 10 | 顯示前 10 條 |
-a | 不抽象化數字/字串 |
-g "SELECT" | 只分析包含 SELECT 的語句 |
mysqldumpslow -s at -t 20 /var/log/mysql/slow.log
二、EXPLAIN 深度解讀
EXPLAIN SELECT * FROM whisper WHERE to_id=6696 AND del=0 AND whisper=0 ORDER BY send_time DESC LIMIT 4;
+----+-------------+---------+------+-------------+-------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-------------+-------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | whisper | ref | to_id | to_id | 4 | const | 1 | Using where; Using filesort |
+----+-------------+---------+------+-------------+-------+---------+-------+------+-----------------------------+
關鍵列含義
- type：連接類型，優到劣
system > const > eq_ref > ref > range > index > ALL。
- key：實際使用的索引，
NULL 表示全表掃描。
- rows：預估掃描行數，越小越好。
- Extra：
Using filesort、Using temporary 為危險信號，需優化。
三、MySQL Hint 常用速查
| Hint | 作用 | 範例 |
|---|
FORCE INDEX(idx) | 強制使用指定索引 | SELECT * FROM t FORCE INDEX(idx_a) WHERE a=1 |
IGNORE INDEX(idx) | 忽略指定索引 | SELECT * FROM t IGNORE INDEX(idx_b) WHERE b=2 |
SQL_NO_CACHE | 禁用查詢快取 | SELECT SQL_NO_CACHE * FROM t |
SQL_CACHE | 強制使用快取（需 query_cache_type=2） | SELECT SQL_CACHE * FROM t |
HIGH_PRIORITY | 提升 SELECT/INSERT 優先級 | SELECT HIGH_PRIORITY * FROM t |
LOW_PRIORITY | 降低 INSERT/UPDATE 優先級 | INSERT LOW_PRIORITY INTO t VALUES(...) |
DELAYED | 延遲插入（MyISAM/MEMORY） | INSERT DELAYED INTO t VALUES(...) |
STRAIGHT_JOIN | 強制按書寫順序連表 | SELECT * FROM t1 STRAIGHT_JOIN t2 ON ... |
SQL_BUFFER_RESULT | 結果集存入臨時表，快釋表鎖 | SELECT SQL_BUFFER_RESULT * FROM t |
SQL_BIG_RESULT | GROUP BY/DISTINCT 使用磁盤臨時表 | SELECT SQL_BIG_RESULT a, COUNT(*) FROM t GROUP BY a |
四、查詢快取最佳實踐
query_cache_type=1：全開（預設 OFF），注意大小寫敏感、空格敏感。- 實時性要求高、低頻查詢用
SQL_NO_CACHE。
- MySQL 8.0 移除 Query Cache，改用外部 Redis/ProxySQL 快取層。
五、MySQL 自動優化機制
- 索引選擇：當 WHERE 條件命中行數超過表行數 30% 左右，優化器傾向全表掃描而非索引。
- 複合索引消除排序：
WHERE name='x' ORDER BY sale_date → 建 INDEX(name, sale_date) 避免 filesort。
- 不可達查詢檢測：
WHERE name='a' AND name='b' → EXPLAIN 顯示 table: NULL，直接跳過執行。
六、查詢優化常用選項
STRAIGHT_JOIN：人工指定連表順序，適用於優化器誤判場景。USE INDEX / IGNORE INDEX / FORCE INDEX：干預索引選擇。SQL_BUFFER_RESULT：大結果集查詢，釋放表鎖提升併發。SQL_BIG_RESULT：大數據量 GROUP BY/DISTINCT，直接用磁盤臨時表避免記憶體不足。
七、優化檢查清單
- 慢查詢日誌是否開啟、閾值是否合理？
- 核心 SQL 是否定期
EXPLAIN 檢查執行計劃？
- 高頻查詢是否命中最佳索引（覆蓋索引、最左前綴）？
- 是否存在
Using filesort、Using temporary 可消除的場景？
- 查詢快取配置是否適配業務讀寫比？
- 大表是否需要分區、分庫分表？
