您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 資料庫優化核心知識體系總結：從設計到架構

編輯：MySQL 綜合教學

MySQL 資料庫優化核心知識體系總結：從設計到架構

MySQL 優化是一個系統工程，涵蓋表設計、SQL 語句、索引策略、參數配置、硬體資源、架構拆分等多個維度。本文將零散優化知識點整理為結構化體系，並針對每個維度給出具體可執行的最佳實踐與避坑指南。

一、資料庫表設計優化

1. 範式與逆範式平衡

範式	核心規則	應用場景	權衡
第一範式 (1NF)	屬性原子性，不可再分	所有關係型資料庫基礎要求	無爭議
第二範式 (2NF)	消除部分依賴（非主鍵列完全依賴主鍵）	複合主鍵表拆分	減少冗余，增加 JOIN
第三範式 (3NF)	消除傳遞依賴（非主鍵列不依賴其他非主鍵列）	查詢表拆分（如：學號→系別→系辦電話）	極致規範，查詢需多表關聯

逆範式策略（針對讀多寫少場景）：

在「一」的一方添加冗余欄位（如：相冊表加「點擊次數」，避免每次 COUNT 相片表）
冗余欄位原則：一對多關係中，冗余字段放在「一」的一方，空間換時間
代價：寫入成本增加（需同步冗余欄位）、資料一致性風險（需觸發器/應用層保證）

2. 反外鍵設計

業務邏輯層面維護關聯完整性，資料庫層面不建立外鍵約束
優點：避免鎖競爭、提升寫入效能、跨庫分表無阻礙、避免級聯刪除風險
缺點：需應用層嚴格保證一致性、資料庫無法自動阻攔髒資料

二、SQL 語句與索引優化（核心重點）

1. 慢查詢識別與配置

-- 查看慢查詢閾值（預設 10 秒，建議改 1 秒）
SHOW VARIABLES LIKE 'long_query_time';
SET GLOBAL long_query_time = 1;

-- 啟用慢查詢日誌（配置檔永久生效）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1  -- 記錄未用索引查詢

2. 索引類型與創建語法

-- 主鍵索引
ALTER TABLE tb ADD PRIMARY KEY (id);

-- 唯一索引
CREATE UNIQUE INDEX uk_email ON tb(email);
ALTER TABLE tb ADD UNIQUE uk_email (email);

-- 普通索引
CREATE INDEX idx_name ON tb(name);
ALTER TABLE tb ADD INDEX idx_name (name);

-- 組合索引（最左前綴原則）
CREATE INDEX idx_name_age_city ON tb(name, age, city);

-- 全文索引（MyISAM/InnoDB 5.6+）
CREATE FULLTEXT INDEX ft_content ON tb(content);

3. EXPLAIN 執行計劃核心欄位解讀

欄位	關鍵值	含義	優化方向
type	system > const > eq_ref > ref > range > index > ALL	訪問類型，從優到劣	避免 ALL（全表掃描），至少達到 range/ref
possible_keys	可能用到的索引	優化器候選索引	無則需建立索引
key	實際使用的索引	最終選擇	與 possible_keys 差異大需分析
key_len	索引長度	判斷組合索引用了幾列	配合字段長度計算
rows	預估掃描行數	越小越好	統計資訊不準時會偏差
Extra	Using index / Using where / Using temporary / Using filesort	額外資訊	避免 temporary/filesort

4. 索引失效常見場景（必背）

LIKE 以 % 開頭： LIKE '%abc' 不用索引，LIKE 'abc%' 可用
組合索引非最左列： 索引 (a,b,c)，查詢 WHERE b=1 或 WHERE c=1 不用索引
隱式轉換： WHERE varchar_col = 123（數字比較字串）導致索引失效
函數/運算在索引列： WHERE YEAR(date_col) = 2023、WHERE id + 1 = 10
OR 條件未全覆蓋索引： WHERE idx_col = 1 OR non_idx_col = 2
優化器判斷全表更快： 數據分佈導致預估掃描行數超過表總行數 20%~30%

5. 實用優化技巧

大批量插入： MyISAM 關鍵字 ALTER TABLE DISABLE/ENABLE KEYS；InnoDB SET unique_checks=0; SET autocommit=0; 批次提交
GROUP BY 去排序： ORDER BY NULL 禁用預設排序
子查詢轉 JOIN： 避免臨時表，利用索引關聯
金額用 DECIMAL： 避免 FLOAT/DOUBLE 精度丟失
時間戳選型： DATETIME (8 bytes, 到 9999 年) vs TIMESTAMP (4 bytes, 到 2038 年)，按業務選擇
圖片/大文件： 存路徑或對象存儲，不存 BLOB

三、存儲引擎選擇：MyISAM vs InnoDB

維度	MyISAM	InnoDB
事務支援	不支援	支援 (ACID)
鎖粒度	表級鎖	行級鎖
全文索引	5.6 前支援	5.6+ 支援
崩潰恢復	無	Redo/Undo Log 保證
並發效能	讀多寫少優	高並發讀寫優
外鍵	不支援	支援
適用場景	唯讀/日誌/非核心表	核心業務/高並發/需事務

現代最佳實踐： 預設使用 InnoDB，MySQL 5.7+ 已將 InnoDB 設為預設引擎。

四、關鍵參數配置調優

[mysqld]
# InnoDB 核心緩衝池（物理內存 60%-80%）
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4

# Redo Log 容量（減少 checkpoint 衝刷）
innodb_log_file_size = 512M
innodb_log_files_in_group = 2

# 刷盤策略（1 最安全，2 效能優先）
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# 連接與線程
max_connections = 2000
thread_cache_size = 100

# 查詢快取（5.7 廢用，8.0 移除，慎用）
query_cache_type = 0
query_cache_size = 0

# 臨時表與排序
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 2M
join_buffer_size = 2M

五、硬體與作業系統層面

內存 > 4GB 必用 64 位 OS，突破 32 位寻址限制
SSD/NVMe 替代 HDD，隨機 IOPS 提升百倍
關閉 Swap 或設 swappiness=1，避免 MySQL 內存被換出
文件系統選擇： XFS/EXT4，掛載參數 noatime,nodiratime
NUMA 架構綁核： numactl --interleave=all mysqld

六、架構層面：分表、讀寫分離、分庫

策略	核心思想	適用場景	工具/中間件
垂直拆表	大表拆小表（熱/冷欄位分離）	含 TEXT/BLOB、部分欄位高頻訪問	應用層路由
水平分表	同結構表按鍵值分散（取模/範圍/Hash）	單表 > 2000 萬行、寫入瓶頸	ShardingSphere, Vitess, TDDL
主從複制	主寫從讀，Binlog 異步同步	讀壓力大、備份需求	原生 GTID、MHA、Orchestrator
讀寫分離	中間件路由 SELECT 到從庫	讀寫比 > 3:1	ProxySQL, MyCat, Atlas
分庫分表	結合水平分表 + 多實例分散	TB 級資料、極高並發	自研/開源中間件