核心優化五大支柱

1. 表結構設計：遵循 3NF（第三範式），必要時適度反範式化提升查詢效能。
2. SQL 語句優化：善用索引、掌握常用調優技巧。
3. 資料庫參數配置：根據引擎特性調整關鍵參數。
4. 硬體與作業系統：合理配置 CPU、記憶體、磁碟 I/O、網路。
5. 讀寫分離架構：主庫寫、從庫讀，分散負載。

資料庫三大範式速記

• 1NF（原子性）：欄位不可再分，關聯式資料庫天然滿足。
• 2NF（唯一性）：滿足 1NF 且每筆記錄唯一，通常透過主鍵保證。
• 3NF（無傳遞依賴）：滿足 2NF 且非主鍵欄位不傳遞依賴於主鍵，常用外鍵關聯實現（需 InnoDB 引擎）。

關鍵參數配置（InnoDB 為例）

參數	建議值	說明
innodb_buffer_pool_size	物理記憶體 60%~80%	核心緩衝池，缓存數據與索引
innodb_additional_mem_pool_size	64M	內部資料結構記憶體池（舊版本相關）
key_buffer_size	視 MyISAM 表大小而定	MyISAM 索引緩衝區

善用 SHOW STATUS 觀察運行狀態，決定調整哪些參數。

常用狀態監控指令

-- 查看 DML 執行次數（Session 級，關閉視窗歸零）
SHOW SESSION STATUS LIKE 'Com_update';

-- 查看 DML 執行次數（Global 級，持久累計）
SHOW GLOBAL STATUS LIKE 'Com_insert';

-- 連線嘗試總數
SHOW STATUS LIKE 'Connections';

-- 服務運行時間（秒）
SHOW STATUS LIKE 'Uptime';

-- 慢查詢次數（預設 > 10 秒）
SHOW STATUS LIKE 'Slow_queries';

慢查詢日誌開啟與分析

方法一：配置檔永久生效（需重啟）

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 3

方法二：線上動態開啟（無需重啟）

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 3;

查看日誌路徑：SHOW VARIABLES LIKE 'slow_query_log_file';

索引優化精要

索引類型

• 主鍵索引
• 唯一索引
• 普通索引
• 全文索引
• 組合索引（多欄位）

建立索引的黃金法則

1. 頻繁出現在 WHERE、JOIN、ORDER BY 的欄位優先建索引。
2. 區分度極低的欄位（如性別、狀態）不適合單獨建索引。
3. 頻繁更新的欄位慎建索引（維護成本高）。
4. 不出現在查詢條件中的欄位無需建索引。

組合索引最左前綴原則

查詢條件必須包含組合索引的最左列，索引才會生效。
LIKE 'abc%' 可用索引，LIKE '%abc' 無法使用。

導致索引失效的常見場景

• 條件中使用 OR，且兩側欄位索引不一致
• 組合索引未用最左列
• LIKE '%...' 以萬用字元開頭
• 字串欄位查詢未加引號（隱式轉型）
• 優化器判斷全表掃描更快

索引使用情況監控

SHOW STATUS LIKE 'Handler_read%';

• Handler_read_key：越大越好（索引命中）
• Handler_read_rnd_next：越小越好（全表掃描）

儲存引擎選擇：MyISAM vs InnoDB

特性	MyISAM	InnoDB
事務支援	否	是
外鍵支援	否	是
鎖粒度	表鎖	行鎖
查詢速度	快	較慢但支援並發
刪除空間回收	需手動 OPTIMIZE TABLE	自動回收

分表策略

水平分表

將同一張表的資料按規則（如 ID 範圍、時間、Hash）拆分到多張結構相同的表中。適用於單表資料量過大、查詢緩慢的場景。

垂直分表

將大表按業務邏輯拆分為多張關聯表，將不常用或大欄位（如頭像、長文本）移至獨立表。類似「學生基本資料表」與「考試詳情表」分離，甚至將頭像獨立至圖片服務器。

讀寫分離架構圖解

主庫處理寫入，同步至多個從庫分擔讀取壓力，透過中間件（如 MyCat、ProxySQL）實現自動路由。