效能問題四大常見成因

1. 索引缺失或未命中
2. SQL 寫法過於複雜
3. 參數配置不當
4. 硬體資源瓶頸

即時診斷利器：SHOW FULL PROCESSLIST

mysql -h localhost -u root -p
SHOW FULL PROCESSLIST;

重點觀察欄位：

• Time：執行秒數，長時間運行者優先排查。
• State：Sending data、Locked、Copying to tmp table 等狀態提示瓶頸。
• Info：完整 SQL 文本，鎖定問題語句。

索引分析核心：EXPLAIN (或 DESC)

關鍵欄位解讀

欄位	關注重點
type	連接類型：system > const > eq_ref > ref > range > index > ALL，越左越好。
possible_keys	理論可用索引。
key	實際使用索引，NULL 表示全表掃描。
rows	預估掃描行數，越小越好。
Extra	Using index（覆蓋索引）、Using where、Using filesort（需排序）、Using temporary（臨時表）。

實戰案例：從全表掃描到索引覆蓋

問題 SQL

SELECT * FROM imgs WHERE userid='admin' ORDER BY clicks DESC LIMIT 10;

初始 EXPLAIN

type=ALL, key=NULL, rows=12506, Extra=Using where; Using filesort

步驟 1：加 userid 索引

CREATE INDEX idx_userid ON imgs(userid);

type=ref, key=idx_userid, rows=2944, Extra=Using where; Using filesort

步驟 2：加 clicks 索引並強制使用

CREATE INDEX idx_clicks ON imgs(clicks);
SELECT * FROM imgs USE INDEX (idx_clicks) 
WHERE userid='admin' AND clicks > 10 ORDER BY clicks DESC LIMIT 10;

type=range, key=idx_clicks, rows=5455, Extra=Using where

雖 rows 仍大，但避免 filesort，實際執行時間大幅下降。若仍不足，可考慮聯合索引 (userid, clicks) 或覆蓋索引。

常見優化策略

• 覆蓋索引：SELECT id, name FROM t WHERE id > 100 若索引含 (id, name) 即可 Using index。
• 避免 SELECT *：減少回表與網路傳輸。
• 優化 LIMIT 深分頁：改用 WHERE id > last_id ORDER BY id LIMIT n。
• 調整參數：innodb_buffer_pool_size 設為實體記憶體 60-80%、sort_buffer_size、join_buffer_size 視查詢複雜度調大。
• 定期 ANALYZE TABLE 更新統計資訊，確保優化器決策準確。