MySQL 效能提升進階實戰：鎖表、外鍵、索引、查詢優化四大支柱

延續基礎優化策略，本文深入四大進階支柱：鎖表機制替代長事務、外鍵約束保證關聯完整性、索引設計核心原則、SQL 查詢重寫避開索引失效陷阱，構建生產級效能優化知識體系。

支柱一：鎖表優化高併發場景

長事務的代價

• 持有鎖時間長 → 阻塞其他寫入、甚至讀取 (視隔離級別)
• Undo Log 膨脹 → 回滾段壓力、purge 線程落後
• 鎖升級風險：行鎖 → 表鎖，並發度暴跌

鎖表替代模式

-- 事務模式 (長事務)
BEGIN;
SELECT qty FROM inventory WHERE item='book' FOR UPDATE;
-- 業務計算 (可能耗時)
UPDATE inventory SET qty=qty-1 WHERE item='book';
COMMIT;

-- 鎖表模式 (短鎖、可控)
LOCK TABLES inventory WRITE;
SELECT qty FROM inventory WHERE item='book';
-- 業務計算 (應用層完成，不持有資料庫連線)
UPDATE inventory SET qty=11 WHERE item='book';
UNLOCK TABLES;

關鍵差異

最佳實踐

• 按固定順序鎖多表 (如按表名字母序) 避免死鎖
• 鎖表邏輯放在應用層，資料庫連線只做讀寫，縮短持鎖時間
• 讀多寫少場景用 LOCK TABLES ... READ，寫入用 WRITE
• MySQL 8.0+ 可用 NOWAIT/SKIP LOCKED 進一步控制

支柱二：外鍵保證關聯完整性

外鍵 vs 觸發器 vs 應用層

外鍵實戰範例

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
    ON UPDATE CASCADE
) ENGINE=InnoDB;

• ON DELETE CASCADE：父表刪除 → 子表關聯行級聯刪除
• ON UPDATE CASCADE：父表主鍵更新 → 子表關聯鍵級聯更新
• ON DELETE RESTRICT (預設)：有子行時禁止刪除父行
• ON DELETE SET NULL：父表刪除 → 子表外鍵設 NULL (需欄位允許 NULL)

注意事項

• 外鍵欄位雙方必須建立索引 (InnoDB 自動建立)
• 資料類型、長度、字符集、排序規則必須完全一致
• 大量匯入時暫時關閉 SET FOREIGN_KEY_CHECKS=0，匯入後開啟
• 分庫分表架構不可使用外鍵，改用應用層 eventual consistency

支柱三：索引設計核心原則

索引建立黃金法則

1. 為 WHERE、JOIN ON、ORDER BY、GROUP BY 欄位建索引
2. 複合索引遵循最左前綴：等值 > 範圍 > 排序
3. 覆蓋索引消除回表：SELECT 欄位 ⊆ 索引欄位
4. 前綴索引處理長字串：區分度足夠的最短前綴
5. 主鍵短、遞增、不可更新：INT/BIGINT AUTO_INCREMENT

全文索引 (FULLTEXT)

• MySQL 5.6+ InnoDB 支援，MyISAM 早期支援
• 適用：文章搜尋、日誌分析、關鍵字匹配
• 語法：WHERE MATCH(title, content) AGAINST('keyword' IN NATURAL LANGUAGE MODE)
• 中文需配置 ngram parser (MySQL 5.7.6+) 或使用 Elasticsearch
• 大量資料先匯入再建索引 (ALTER TABLE ... ADD FULLTEXT) 遠快於邊匯入邊建索引

支柱四：查詢優化避開索引失效陷阱

四大索引殺手

字串比較優化

-- 低效：LIKE 通配符
SELECT * FROM books WHERE name LIKE 'MySQL%';

-- 高效：範圍查詢 (利用索引有序性)
SELECT * FROM books WHERE name >= 'MySQL' AND name < 'MySQM';
-- 原理：'MySQL' 到 'MySQM' 覆蓋所有 'MySQL' 開頭字串

避免自動類型轉換

-- 錯誤：數字與字串比較
WHERE id = '123'  -- id 為 INT，觸發轉換

-- 正確：類型一致
WHERE id = 123

綜合優化工作流

1. 開啟慢查詢日誌：long_query_time=1, log_queries_not_using_indexes=ON
2. 定期分析：pt-query-digest 找出 Top 10 慢查詢
3. EXPLAIN 驗證：檢查 type, key, rows, Extra
4. 精準施策：加索引 / 重寫 SQL / 調整架構 / 分庫分表
5. 測試驗證：預發環境相同資料量壓測
6. 上線觀測：監控 QPS、RT、錯誤率、慢查詢數

總結

鎖表解決熱點併發、外鍵守護關聯完整、索引加速查詢路徑、SQL 重寫避開失效陷阱——四大支柱環環相扣。生產環境優化核心是「測量驅動、精準施策、持續迭代」。沒有銀彈，只有對業務查詢模式、資料分佈、硬體資源的深度理解，才能做出正確的架構決策。