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

MySQL 性能優化全攻略（一）：從架構設計到參數調優的系統性指南

編輯：MySQL 綜合教學

前言：優化是場系統工程，而非單點突破

MySQL 性能優化無法一蹴而就，必須從表設計、索引策略、架構拆分、讀寫分離、存儲過程、參數配置、硬體升級、日常維護八大維度協同推進。本文梳理完整知識體系，並深入實戰 EXPLAIN 分析與慢查詢定位，作為系列首篇奠基。

一、MySQL 優化技術全景圖

表設計合理化（符合 3NF）：根基中的根基。
添加適當索引：普通、主鍵、唯一、全文四大類型，按業務選型。
分表技術：水平分割（分庫分表）與垂直分割（大欄位拆分）。
讀寫分離：主庫寫、從庫讀，緩解單點壓力。
存儲過程：模組化編程，減少網路交互，提升批量處理速度。
配置優化：max_connections、innodb_buffer_pool_size 等核心參數調優。
硬體升級：SSD、大記憶體、多核 CPU 為軟體優化托底。
定時清理與碎片整理：MyISAM 需定期 OPTIMIZE TABLE；InnoDB 依賴自動合併。

二、資料庫優化工作的四大支柱（影響力遞減序）

資料庫設計（決策層面，影響最大）
SQL 語句優化（開發層面，日常核心）
資料庫參數配置（運維層面，適配負載）
硬體資源與作業系統（基礎設施，物理上限）

此外，善用存儲過程亦能提升性能。

三、表設計深度解析：三大範式與「有理冗餘」

通俗理解三範式

範式	核心約束	通俗說法
1NF	屬性原子性	每個欄位不可再拆分（關係型資料庫天然滿足）
2NF	記錄唯一性	每行有唯一標識（主鍵），無部分依賴
3NF	字段無冗餘	非主鍵欄位不傳遞依賴於主鍵

為什麼有時要「降級」保留冗餘？

嚴格 3NF 雖消除冗餘，但可能導致頻繁多表 JOIN，查詢效率下降。策略：概念模型遵循 3NF，物理模型適度降級。

反面教材：不合理冗餘

不合理冗餘示例

場景：單位表 500 行，學生活動表 200 萬行。為查詢效率在活動表冗餘「單位名稱」。

表體積膨脹 3 倍，I/O 激增。
更新單位名需同步 4000 條冗餘行，寫入放大嚴重。
結論：1 對 N 的冗餘應發生在「1」的一方（此處應在單位表冗餘統計欄位）。

正面教材：合理冗餘

合理冗餘示例

訂單表冗餘 Price 總價（可從明細表加總得出）。

查詢訂單列表時避免 JOIN 明細表聚合，讀取極快。
冗餘發生在「1」的一方（訂單對明細是 1:N），更新成本可控。

四、SQL 語句優化實戰：從慢查詢定位到 EXPLAIN 深度解讀

1. 監控核心狀態變量


-- 會話級 / 全域級
SHOW [SESSION|GLOBAL] STATUS LIKE 'Com_%';
-- 重點：Com_select, Com_insert, Com_update, Com_delete 掌握讀寫比例
SHOW GLOBAL STATUS LIKE 'Connections';   -- 連接嘗試次數
SHOW GLOBAL STATUS LIKE 'Uptime';        -- 運行秒數
SHOW GLOBAL STATUS LIKE 'Slow_queries';  -- 慢查詢次數（預設 >10s）

2. 慢查詢閾值調整與開啟日誌


SHOW VARIABLES LIKE 'long_query_time';  -- 查看預設 10s
SET GLOBAL long_query_time = 2;         -- 臨時改為 2s（永久需改 my.cnf）

-- 5.5+ 在 my.cnf [mysqld] 區塊
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
-- 5.6+ 預設已開啟，檢查 datadir 確認路徑

3. 構建百萬級測試表（存儲過程生成隨機資料）


-- 表結構
CREATE TABLE `emp` (
  `empno` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  `ename` VARCHAR(20) NOT NULL DEFAULT '',
  `job` VARCHAR(9) NOT NULL DEFAULT '',
  `mgr` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  `hiredate` DATE NOT NULL,
  `sal` DECIMAL(7,2) NOT NULL,
  `comm` DECIMAL(7,2) NOT NULL,
  `deptno` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 隨機字串函數
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
  DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  DECLARE return_str VARCHAR(255) DEFAULT '';
  DECLARE i INT DEFAULT 0;
  WHILE i < n DO
    SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1+RAND()*52), 1));
    SET i = i + 1;
  END WHILE;
  RETURN return_str;
END;

-- 批量寫入存儲過程（關閉自動提交加速）
CREATE PROCEDURE insert_emp(IN start INT, IN max_num INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  SET autocommit = 0;
  REPEAT
    SET i = i + 1;
    INSERT INTO emp VALUES (start+i, rand_string(6), 'SALESMAN', 1, CURDATE(), 2000, 400, RAND());
  UNTIL i = max_num END REPEAT;
  COMMIT;
END;

-- 呼叫：生成 400 萬測試行
CALL insert_emp(100001, 4000000);

4. EXPLAIN 逐欄詳解（核心技能）

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

欄位	含義	關鍵判讀
id	SELECT 識別符	數字越大越先執行；相同則按順序
select_type	查詢類型	PRIMARY(最外層)、SUBQUERY、DERIVED(衍生表)、UNION 等
table	輸出結果集的表	表示衍生表
type	訪問類型（最關鍵）	ALL(全表掃描) > index > range > ref > eq_ref > const > system 目標：至少達到 ref，最好 range 或更優
possible_keys	可能用到的索引	為 NULL 表示無索引可用
key	實際使用的索引	與 possible_keys 對比，若為 NULL 需建索引
key_len	索引長度	推斷聯合索引用了幾個欄位
rows	預估掃描行數	越小越好；InnoDB 為估算值
Extra	額外信息	Using filesort(需優化)、Using temporary(臨時表)、Using index(覆蓋索引✓)、Using where(WHERE 過濾)

關鍵 Extra 值對照

Using filesort：ORDER BY 無法用索引排序 → 加索引或調整排序欄位。
Using temporary：GROUP BY/DISTINCT 需臨時表 → 檢查索引順序。
Using index：覆蓋索引，無需回表 → 極佳。
Impossible WHERE：優化器判定無結果 → 條件衝突。

5. 實戰：子查詢 vs JOIN 效能對比


-- 慢：子查詢 + ORDER BY 導致 filesort，400 萬行耗時 1min+
SELECT * FROM emp WHERE empno = (SELECT empno FROM emp WHERE ename='研發部') ORDER BY empno;

-- 快：JOIN 利用索引
SELECT * FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE e.empno = 100002;

EXPLAIN 輸出示例