MySQL 資料庫效能優化基礎篇：三範式設計、SQL 優化流程、Show Status 監控與慢查詢定位

資料庫效能優化是系統工程，涵蓋設計、SQL、參數、硬體四層次，影響力遞減。本文建立優化知識體系，詳細解析三範式通俗理解、適度冗余原則、SQL 優化標準流程、SHOW STATUS 關鍵指標、慢查詢日誌配置與 EXPLAIN 執行計畫深度解讀。

優化技術全景圖

• 表設計合理化（符合 3NF）
• 適當索引
• 分表技術（水平/垂直分割）
• 讀寫分離
• 儲存過程
• 參數調優 (my.cnf)
• 硬體升級
• 定期清理與碎片整理

優化工作四層次（影響力排序）

1. 資料庫設計 — 決定架構上限
2. SQL 語句優化 — 直接影響執行路徑
3. 參數配置 — 發揮硬體潛力
4. 硬體資源 — 物理基礎設施

原則：優先解決高層次問題，事半功倍。

三範式通俗理解與適度冗余

適度冗余原則：1 端冗余到 N 端

反例：單位表 500 行，學生活動 200 萬行。為避免 Join 將單位名冗余到活動表 → 表膨脹 3 倍、I/O 暴增、更新單位名需改 4000 行冗余資料 → 得不償失。
正例：訂單表冗余 total_price（可從明細加總得出） → 查詢訂單總額無需 Join 明細 → 查詢加速、更新僅在訂單建立/修改時同步 → 合理。
結論：冗余應發生在 1 這一方（基數小、更新頻低、查詢頻高）。

SQL 優化標準流程

1. SHOW [SESSION|GLOBAL] STATUS LIKE 'Com_%'; — 瞭解 SELECT/INSERT/UPDATE/DELETE 執行頻率比例。
2. 定位低效 SQL（重點 SELECT）。
3. EXPLAIN 分析執行計畫。
4. 確定問題並採取優化措施。

關鍵狀態指標

慢查詢閾值調整

SHOW VARIABLES LIKE 'long_query_time';  -- 查看預設 10
SET long_query_time = 2;  -- 臨時調整，生產建議 1~2 秒
-- 永久生效需寫入 my.cnf: long_query_time=2

慢查詢日誌配置與定位

啟用方式

• 5.0：mysqld --log-slow-queries[=file] 或 my.cnf 設 log-slow-queries
• 5.5：my.cnf 設 slow-query-log=1, slow-query-log-file=...
• 5.6+：預設開啟，my.cnf 設 slow_query_log=1

構建大表測試慢查詢

-- 建表
CREATE TABLE dept(...) ENGINE=MyISAM;
CREATE TABLE emp(...) ENGINE=MyISAM;
CREATE TABLE salgrade(...);

-- 存儲過程產生 400 萬筆測試資料
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) ...
CREATE PROCEDURE insert_emp(IN start INT, IN max_num INT) ...
CALL insert_emp(100001, 4000000);

測試慢查詢

-- 子查詢 + 大表掃描，極慢
SELECT * FROM emp WHERE empno = (SELECT empno FROM emp WHERE ename='研發部');

-- Join 有索引則快
SELECT * FROM emp e, dept d WHERE e.empno=100002 AND e.deptno=d.deptno;

EXPLAIN 深度解讀

EXPLAIN SELECT * FROM emp WHERE ename='JKLOIP'\G

type 存取類型優劣排序

1. system/const：最多一行，極快（主鍵/唯一鍵等值）。
2. eq_ref：Join 時主鍵/唯一鍵關聯。
3. ref：非唯一索引等值查詢。
4. range：索引範圍掃描 (>, <, IN, BETWEEN)。
5. index：全索引掃描。
6. ALL：全表掃描，最差。

Extra 關鍵提示

• Using filesort：需外部排序（ORDER BY 無索引支援）。
• Using temporary：需建立臨時表（GROUP BY/DISTINCT 無索引）。
• Using index：覆蓋索引，僅讀索引不回表，極佳。
• Using where：WHERE 過濾（存取類型非 ALL 時為佳）。

建立「設計優先、SQL 為核、參數輔助、硬體兜底」的優化思維模型，配合 SHOW STATUS 基線監控、慢查詢日誌持續收集、EXPLAIN 深度分析，將效能優化從經驗主義轉向數據驅動工程實踐。