SQL 效能調優實戰：三範式、慢查詢分析、索引策略與壓力測試全攻略

SQL 效能調優實戰：三範式、慢查詢分析、索引策略與壓力測試全攻略

資料庫三範式通俗理解

• 1NF (第一範式)： 欄位原子性，不可再分解
• 2NF (第二範式)： 記錄唯一性，實體有唯一標識
• 3NF (第三範式)： 欄位無冗余，不可由其他欄位推導

物理設計階段可適當降低範式標準、保留冗余欄位以提升執行效率。

SQL 語句分類

• DDL：CREATE、ALTER、DROP
• DML：INSERT、DELETE、UPDATE
• DQL：SELECT
• DTL：ROLLBACK、COMMIT、SAVEPOINT
• DCL：GRANT、REVOKE

SHOW STATUS 核心監控指標

SHOW STATUS LIKE 'Com%';           -- 當前會話統計
SHOW GLOBAL STATUS LIKE 'Com%';    -- 啟動以來累計統計
重點關注：Com_select, Com_insert, Com_delete, Com_update
SHOW STATUS LIKE 'Connections';    -- 嘗試連線數
SHOW STATUS LIKE 'Uptime';         -- 伺服器運行時間(秒)
SHOW STATUS LIKE 'Slow_queries';   -- 慢查詢次數
SHOW VARIABLES LIKE 'long_query_time'; -- 慢查詢閾值(預設10秒)

壓力測試資料生成腳本

建立 dept、emp、salgrade 三表，並透過儲存函數生成隨機字串與數字，存儲過程批量插入 18 萬員工資料與 10 筆部門資料。

-- 隨機字串函數 rand_string(n)
-- 隨機部門編號函數 rand_num()
-- 批量插入員工存儲過程 insert_emp(start, max_num)
-- 批量插入部門存儲過程 insert_dept(start, max_num)
CALL insert_emp(100001, 180000);
CALL insert_dept(100, 10);

慢查詢日誌啟用與分析

# 啟動慢查詢日誌
mysqld.exe --slow-query-log
# 修改慢查詢閾值
SET long_query_time = 0.5;
SHOW STATUS LIKE 'slow%';

EXPLAIN 執行計劃分析

EXPLAIN SELECT * FROM emp WHERE empno = 123456\G;

重點欄位：

• type：all(全表掃描) → system → const → ref → eq_ref → 避免 all
• possible_keys：可能用到的索引
• key：實際用到的索引
• rows：掃描行數估算
• Extra：Using filesort(需排序)、Using temporary(臨時表)、Using where(覆蓋索引)

索引四大類型與建立原則

• 主鍵索引 (PRIMARY)
• 唯一索引 (UNIQUE)
• 普通索引 (INDEX)
• 全文索引 (FULLTEXT)
• 組合索引：多欄位聯合

適合建索引的欄位：

1. 頻繁作為 WHERE 條件的欄位

不適合建索引的欄位：

1. 唯一性極差 (如性別)
2. 更新極頻繁
3. 不出現在 WHERE 子句中

索引管理指令

ALTER TABLE emp ADD PRIMARY KEY(empno);
CREATE [UNIQUE|FULLTEXT] INDEX idx_name ON tbl_name(col...);
ALTER TABLE tbl_name ADD INDEX [idx_name] (col...);
SHOW INDEXES FROM tbl_name;
ALTER TABLE emp DROP PRIMARY KEY;
DROP INDEX idx_name ON tbl_name;

索引使用特殊情況

1. 複合索引遵循「最左前綴」原則
2. LIKE '%aaa' 不用索引，LIKE 'aaa%' 用索引
3. 條件含 OR 通常不走索引
4. 字串欄位條件必須加引號才走索引
5. 優化器判斷全表掃描更快時不使用索引

索引效果驗證

SHOW STATUS LIKE 'Handler_read_key';      -- 值越高索引越有效
SHOW STATUS LIKE 'Handler_read_rnd_next'; -- 值越高查詢效率越低