MySQL 進階優化五大絕技：事務/鎖表/外鍵/索引/查詢重寫 系統級性能躍升

超越參數調優：事務、鎖、外鍵、索引、SQL 重寫五大系統級優化手段

參數調優是「養護」，架構與代碼層優化是「手術」。五大手段直擊核心瓶頸。

一、事務：一致性與併發的平衡術

START TRANSACTION;
INSERT INTO orders (user_id, amount) VALUES (100, 99.9);
UPDATE inventory SET stock = stock - 1 WHERE sku = 'A001' AND stock > 0;
COMMIT;
-- 任一失敗 → ROLLBACK，保證原子性

關鍵點：

• 事務粒度**越小越好**：長事務 = 長鎖 = 併發殺手。
• 業務邏輯**勿放入事務**：RPC 調用、發郵件、文件操作移出事務外。
• 隔離級別權衡：READ COMMITTED (RC) + binlog_format=ROW 兼顧性能與一致性 (5.7+ 默認)。
• 死鎖處理：應用層捕獲 Deadlock found 重試 (指數退避)，InnoDB 自動回滾受害者。

二、鎖表：特定場景下的「大錘」

-- 表鎖 (MyISAM/InnoDB 皆支援，但 InnoDB 推薦行鎖)
LOCK TABLES inventory WRITE, orders WRITE;
SELECT stock FROM inventory WHERE sku='A001';
UPDATE inventory SET stock=stock-1 WHERE sku='A001';
UNLOCK TABLES;

適用場景：

• 批量導入/重建索引/結構變更 (DDL) 需獨佔。
• 跨表複雜業務無法用行鎖表達 (極罕見)。
• 禁忌：高併發核心業務，表鎖 = 序列化 = 吞吐崩塌。

三、外鍵：數據完整性的「守門員」

CREATE TABLE customers (
  id INT PRIMARY KEY
) ENGINE=InnoDB;

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

收益：

• 物理級阻擋孤兒數據 (無效 customer_id 寫入失敗)。
• ON DELETE CASCADE 級聯刪除，應用層無需手動清理。

代價：

• 寫入額外檢查父表 (共享鎖)，高併發寫入可見性能下降 5-15%。
• 鎖升級風險：刪除父表行需鎖定子表相關行，易死鎖。
• 建議：核心鏈路 (訂單-用戶、庫存-SKU) 用 FK；高頻寫入邊緣表 (日誌、統計) 應用層維護。

四、索引：查詢加速的「核武器」

五、查詢重寫：同語義、異性能

-- 1. OR 改 UNION (索引合併)
SELECT * FROM t WHERE a=1 OR b=2;
→ SELECT * FROM t WHERE a=1 UNION SELECT * FROM t WHERE b=2;

-- 2. IN 子查詢改 JOIN (5.6+ 半連接優化但不穩)
SELECT * FROM t1 WHERE id IN (SELECT t1_id FROM t2 WHERE status=1);
→ SELECT t1.* FROM t1 JOIN t2 ON t1.id=t2.t1_id WHERE t2.status=1;

-- 3. 分頁優化 (游標法)
SELECT * FROM orders WHERE user_id=100 ORDER BY id LIMIT 100000, 20;
→ SELECT * FROM orders WHERE user_id=100 AND id > 999999 ORDER BY id LIMIT 20;

-- 4. 聚合下推
SELECT COUNT(*) FROM orders WHERE user_id=100;
-- 覆蓋索引 (user_id) 直接掃描索引葉子節點，極快

綜合優化清單

1. 開啟慢查詢日誌 + pt-query-digest 定期分析 Top 20。
2. EXPLAIN 每條核心 SQL，消除 ALL/filesort/temporary。
3. 索引審計：pt-index-usage / pt-duplicate-key-checker 找廢棄/重複索引。
4. 事務審計：長事務、未提交事務、死鎖日誌分析。
5. 架構演進：讀寫分離 → 分庫分表 → 異構存儲 (Redis/ES/ClickHouse) 分流。