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

法則一：選擇最精簡的資料型別

• 原則：表越小，查詢越快。CHAR(6) 足夠存郵遞區號，勿用 VARCHAR(255)；整數優先 TINYINT/SMALLINT/MEDIUMINT 而非 BIGINT。
• NOT NULL：可能時設定 NOT NULL，避免 NULL 判斷開銷。
• ENUM 型別：固定值集合（性別、狀態、省份）用 ENUM，內部以整數儲存，比較極快。

法則二：用 JOIN 取代子查詢

反例：

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

優化：

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

JOIN 避免建立臨時表，配合索引效能提升數倍。

法則三：用 UNION 取代手動臨時表

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

• UNION 自動去重（較慢）；UNION ALL 保留重複（極快），業務允許時優先用 ALL。
• 所有 SELECT 欄位數、型別必須一致。

法則四：善用交易控制

START TRANSACTION;
INSERT INTO orders ...;
UPDATE inventory SET qty = qty - 1 WHERE id = 1;
COMMIT;  -- 或 ROLLBACK;

• ACID 保證資料一致性。
• 長交易會鎖定資源，拆分為短交易或用樂觀鎖。

法則五：表鎖定優化高併發寫入

LOCK TABLES inventory WRITE;
SELECT qty FROM inventory WHERE item='book';
UPDATE inventory SET qty=11 WHERE item='book';
UNLOCK TABLES;

適用於簡單邏輯、極高併發場景，避免交易開銷。注意：鎖定期間阻塞其他讀寫，慎用。

法則六：外鍵保障參考完整性

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

• 必須使用 InnoDB 引擎。
• ON DELETE CASCADE 自動級聯刪除，防孤兒資料。

法則七：索引策略決定查詢生死

• 建立索引：WHERE、JOIN、ORDER BY、GROUP BY 涉及的欄位。
• 避免索引：低基數欄位（性別、布林值）、頻繁更新的欄位。
• 前綴索引：長字串 VARCHAR(255) 用 INDEX(name(10))。
• 覆蓋索引：查詢僅需索引欄位，避免回表。
• 函數殺手：WHERE YEAR(date_col) = 2023 → WHERE date_col >= '2023-01-01' AND date_col < '2024-01-01'。

法則八：SQL 重寫讓索引生效

總結

優化是系統工程：硬體 → 系統 → 架構 → SQL → 索引。定期 EXPLAIN、SHOW STATUS、pt-query-digest 分析慢查詢，持續迭代。