MySQL 效能優化 8 大黃金法則：從欄位設計到 SQL 重寫

前言

MySQL 效能優化是系統工程，涵蓋架構、結構、查詢、配置各層面。本文蒐整 8 大高影響力優化原則，附實戰範例與反模式對照。

1. 選取最適用的欄位屬性 — 縮小即正義

• 整數：TINYINT (1B) > SMALLINT (2B) > MEDIUMINT (3B) > INT (4B) > BIGINT (8B)。郵遞區號用 CHAR(6) 而非 VARCHAR(255)。
• 定長 vs 變長：CHAR 適合固定長 (MD5、UUID、國家代碼)；VARCHAR 適合變長。CHAR 不需長度前綴，查詢略快但浪費空間。
• NOT NULL：預設加上，避免 NULL 比較開銷、索引統計複雜度、應用層防禦性判斷。
• ENUM：低基數固定集合 (性別、狀態、省份) 內部存儲為整數，比較極快。

2. 用 JOIN 取代子查詢 — 避免臨時表物化

反模式 (子查詢)：

SELECT * FROM customerinfo
WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo);

優化 (LEFT JOIN + IS NULL)：

SELECT c.* FROM customerinfo c
LEFT JOIN salesinfo s ON c.CustomerID = s.CustomerID
WHERE s.CustomerID IS NULL;

原理：JOIN 由優化器直接執行巢狀迴圈/哈希聯接；子查詢 (特別是 NOT IN) 常導致全表掃描或臨時表建立。確保被驅動表聯接欄位有索引。

3. 用 UNION 取代手動臨時表 — 簡潔且自動清理

SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product;

注意：所有 SELECT 欄位數、順序、類型必須相容。UNION ALL 保留重複、免排序、效能更高 (若業務允許重複)。

4. 善用交易 (Transaction) — 一致性與並發控制

START TRANSACTION;
INSERT INTO salesinfo SET CustomerID = 14;
UPDATE inventory SET Quantity = 11 WHERE item = 'book';
COMMIT;

• ACID 保障：全成或全敗
• 行鎖範圍最小化：只鎖異動行，縮短持有時間
• 避免長交易：拆分業務、異步處理非關鍵寫入

5. 表鎖定 (LOCK TABLES) — 特定場景的輕量替代

LOCK TABLE inventory WRITE;
SELECT Quantity FROM inventory WHERE item = 'book';
-- 業務邏輯計算
UPDATE inventory SET Quantity = 11 WHERE item = 'book';
UNLOCK TABLES;

適用：MyISAM、批次匯入、唯寫場景。InnoDB 首選行鎖/交易，表鎖會阻塞所有並發。

6. 外鍵約束 — 資料關聯完整性的守門員

CREATE TABLE customerinfo (
    CustomerID INT NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE salesinfo (
    SalesID INT NOT NULL,
    CustomerID INT NOT NULL,
    PRIMARY KEY (CustomerID, SalesID),
    FOREIGN KEY (CustomerID) REFERENCES customerinfo(CustomerID) ON DELETE CASCADE
) ENGINE=InnoDB;

關鍵：ON DELETE CASCADE 級聯刪除；ENGINE=InnoDB 必須 (MyISAM 不支援 FK)。外鍵有鎖開銷，高併發寫入可考慮應用層維護。

7. 索引策略 — 精準建立、定期維護

• 建立對象：WHERE、JOIN ON、ORDER BY、GROUP BY 欄位
• 避免：低選擇性欄位 (性別、布林)、長字串全文 (用全文索引)
• 複合索引：遵循最左前綴，(a,b,c) 支援 a/a,b/a,b,c
• 覆蓋索引：SELECT a,b FROM t WHERE c=1 → 索引 (c,a,b) 避免回表
• 維護：ANALYZE TABLE 更新統計；OPTIMIZE TABLE 整理碎片 (或 ALTER TABLE ... ENGINE=InnoDB)

8. SQL 重寫技巧 — 讓索引發揮效用

總結檢查清單

1. 欄位類型最小化、NOT NULL、ENUM 善用
2. 子查詢 → JOIN / UNION
3. 交易控制粒度、避免長交易
4. InnoDB + 外鍵 (視負載權衡)
5. 索引：精準建立、覆蓋查詢、定期 ANALYZE/OPTIMIZE
6. SQL 重寫：遠離欄位函數/運算、隱式轉型、SELECT *
7. EXPLAIN 驗證每個核心查詢執行計畫
8. 監控慢查詢日誌 (long_query_time=1)、pt-query-digest 分析

1. 上一篇文章： SQL Server 使用者定義函數 (UDF) 替代游標的兩大經典模式
2. 下一篇文章： MySQL root 密碼忘記？5 分鐘重置 root 密碼完整圖文教學

MySQL 資料庫相關知識

MySQL 5.7.30 安裝配置圖文完整教程（含 Workbench 使用）

安裝前準備 從 MySQL 官網下載 MySQL 5.7.3

MySQL 資料目錄架構深度解析

資料目錄的樹狀結構 MySQL 將所有資料庫與表組織在單一資

MySQL 水平分庫分表中間件 Spock Proxy 架構解析

專案背景 Spock Proxy 由實際 Rails 專案衍

SQL 去重完全指南：DISTINCT vs ROW_NUMBER() 實戰對比

場景準備：UserInfo 示例表 IDNameAgeCi