MySQL 效能優化全景圖：設計、SQL、分表、讀寫分離

MySQL 效能優化全景圖：設計、SQL、分表、讀寫分離

MySQL 優化是一項系統工程，涵蓋資料庫設計、SQL 語句、架構拆分三大維度。本文結合實戰案例，從三範式到 HandlerSocket，為你構建完整優化知識體系。

一、資料庫設計優化：三範式與反範式的平衡

1. 三範式速記

• 1NF（原子性）：每個欄位不可再分，無重複群組。
• 2NF（唯一性）：滿足 1NF 且非主鍵欄位完全依賴主鍵（無部分依賴）。
• 3NF（無冗餘）：滿足 2NF 且非主鍵欄位不傳遞依賴於主鍵。

2. 適度反範式

為查詢效能，可在「寫少讀多」、「大表關聯查詢頻繁」場景引入冗餘欄位（如訂單表存冗餘用戶名），但需透過觸發器或應用層保證一致性。

二、SQL 語句優化實戰

1. 標準四步法

1. SHOW STATUS LIKE 'Com_%' 掌握負載畫像
2. 開啟慢查詢日誌定位問題 SQL
3. EXPLAIN 分析執行計劃
4. 針對性加索引、改寫 SQL、調參數

2. 關鍵變數與配置

# 監控併發與慢查詢
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

# 慢查詢閾值動態調整
SET GLOBAL long_query_time = 1;  # 秒，生產建議 1~2 秒

3. 索引設計黃金法則

4. 驗證索引生效

SHOW STATUS LIKE 'Handler_read%';
# Handler_read_key 高 = 索引命中好
# Handler_read_rnd_next 高 = 全表掃描多

5. 批量寫入優化

• MyISAM：ALTER TABLE t DISABLE KEYS; LOAD DATA...; ALTER TABLE t ENABLE KEYS;
• InnoDB：按主鍵排序數據 → SET unique_checks=0; SET autocommit=0; → 批次提交 → 恢復設置。

三、表分割：垂直 vs 水平

1. 垂直分表（拆欄位）

將大字段（如 TEXT、BLOB）、低頻欄位拆到獨立表，主表只保高頻欄位，減少 I/O、提升緩存命中。

-- 原表：user_info(id, name, age, email, intro)
-- 拆分後：
CREATE TABLE user_base (id PK, name, age, email);
CREATE TABLE user_intro (id PK, intro);

2. 水平分表（拆行數）

按 id % N、時間範圍、租戶 ID 等策略將數據分散到結構相同的多張表。

-- 取模 2 示例
CREATE TABLE user_0 LIKE user;
CREATE TABLE user_1 LIKE user;
-- 應用層路由：id % 2 決定寫入 user_0 或 user_1

注意：分表後跨分片查詢、事務、全局唯一 ID 需額外解決方案（如分布式 ID 生成器、中間件 ShardingSphere）。

四、讀寫分離架構

• 主庫：處理寫入（INSERT/UPDATE/DELETE/DDL），同步 Binlog 到從庫。
• 從庫：處理讀取（SELECT），可掛載多個從庫做負載均衡（LVS/HAProxy/Nginx）。
• 代理層：應用端透過中間件（MyCat、ProxySQL、ShardingSphere-Proxy）自動路由讀寫。

關鍵指標：主從延遲（Seconds_Behind_Master），監控報警 + 半同步複制（rpl_semi_sync_master_enabled=1）保障數據安全。

五、進階：HandlerSocket 跳過 SQL 層

針對極高 QPS 的單鍵查詢（PK Lookup），HandlerSocket 作為 MySQL Daemon Plugin 直接走 InnoDB 存儲引擎 API，繞過 SQL 解析、優化器、鎖開銷，實測單機 75 萬+ QPS，遠超 Memcached。適用場景：用戶資料查詢、Session 讀取、計數器高併發增減。

六、優化檢查清單

1. 表結構符合 3NF？必要時反範式？
2. 慢查詢日誌已開啟並定期分析？
3. 核心 SQL 皆有 EXPLAIN 驗證？
4. 索引覆蓋率、區分度、最左前綴是否合規？
5. 大表是否已分表/分區？
6. 讀寫分離架構是否上線？主從延遲是否可控？
7. 熱點數據是否引入 Redis/Memcached/HandlerSocket？

優化無終點，唯有持續觀測、迭代，才能駕馭業務增長帶來的壓力。