高效 MySQL 應用開發六大鐵律：只存必要、只取所需、索引驅動、型別極簡、連接池、監控先行

前言：MySQL 很快，但寫得爛會無限慢

MySQL 擁有當之無愧的「極速」聲譽，但多數效能問題源於應用層反模式而非資料庫本身。本文提煉六大鐵律，從資料模型、查詢模式、索引策略、型別選擇、連接管理、觀測體系六個維度，構建高效 MySQL 應用開發標準。

鐵律一：只存業務必要資料（拒絕廚房下水道）

• 反模式：什麼都存——衍生欄位、冗餘快取、日誌、二進制大物件、審計軌跡全塞主表
• 正解：
  li>衍生值（稅額、運費、總價）→ 應用層計算或 View/Generated Column
  1. 大文本/Blob → 獨立表 1:1、物件儲存 (S3/MinIO) + 資料庫存 URI
  2. 審計/日誌 → 專用審計表/Elasticsearch/ClickHouse
  3. 快取 → Redis/Memcached，資料庫只存權威資料
• 收益：主表窄行 → 更多行放入 Buffer Pool → 更少 I/O → 更快查詢

鐵律二：只查業務必要欄位（拒絕 SELECT *）

• 反模式：SELECT * FROM orders WHERE user_id=1001; 即使只需訂單號與狀態
• 正解：
  1. 明確列出欄位：SELECT order_no, status FROM orders WHERE user_id=1001;
  2. ORM 指定投影：db.Orders.Select(o => new {o.OrderNo, o.Status}).ToList()
  3. 覆蓋索引設計：KEY idx_user_status_cover (user_id, status, order_no)
• 收益：網路傳輸減 80%+、避免回表、啟用覆蓋索引、減少緩衝池污染

鐵律三：索引驅動查詢設計（EXPLAIN 是唯一真理）

• 流程：寫 SQL → EXPLAIN → 驗證 type=ref/range/const、key=預期索引、rows=預期掃描行、Extra=Using index → 不符合 → 改 SQL/加索引/改結構
• 核心原則：
  1. 複合索引最左前綴：KEY idx_a_b_c (a,b,c) 支援 a / a,b / a,b,c
  2. 高選擇性欄位前置：唯一值越多越前
  3. 範圍查詢列後置：WHERE a=1 AND b>2 AND c=3 索引 (a,c,b) 或 (a,b)
  4. 排序欄位納入索引：WHERE a=1 ORDER BY b → KEY (a,b)
• 禁忌：低選擇性單列索引 (性別/狀態/布林)、函數索引欄位 (WHERE DATE(created_at)=...)、隱式轉換 (WHERE phone=13800000001 phone 為 VARCHAR)

鐵律四：型別極簡（最小型別 = 最大效能）

• 對照表（參考「建表決策清單」）：
  • 布林/開關 → TINYINT UNSIGNED (1 byte)
  • 枚舉/狀態 (<256) → TINYINT UNSIGNED
  • ID/自增 (<40億) → INT UNSIGNED (4 bytes)
  • 分散式 ID → BIGINT UNSIGNED (8 bytes)
  • 金額 → DECIMAL(12,2) (6 bytes)
  • 時間戳 → TIMESTAMP (4 bytes) / DATETIME (8 bytes)
  • 短字串 → VARCHAR(N) 動態長度
• 收益公式：行大小減半 → Buffer Pool 容納行數翻倍 → 緩存命中率提升 → 查詢延遲下降

鐵律五：連接池科學配置（拒絕預設值）

鐵律六：監控先行（無觀測即無優化）

• 三層觀測體系：
  1. 基礎指標：Prometheus + mysqld_exporter → QPS、延遲、連線、緩衝池、複製、磁碟
  2. 查詢分析：PMM QAN / 慢查詢日誌 (long_query_time=0.5) → Top SQL、執行計畫、鎖等待
  3. 業務追蹤：APM (SkyWalking/Zipkin) + 分散式追蹤 → SQL 執行時間佔比、上下游依賴
• 告警起跑線：
  • QPS 突變 > 50%
  • P99 延遲 > 500ms
  • 慢查詢佔比 > 5%
  • 複製延遲 > 30s
  • 自增 ID 消耗 > 80%

實戰檢查清單：Code Review 必過項

[ ] 是否有 SELECT *？
[ ] WHERE 條件是否有對應複合索引？EXPLAIN 是否 type=ref/range？
[ ] 是否在大表上使用 OFFSET 深分頁？→ 改游標分頁
[ ] 欄位型別是否最小化？有無 INT 存手機號、FLOAT 存金額？
[ ] 連接字串是否配置連接池參數？CharSet=utf8mb4？
[ ] 是否有慢查詢日誌？long_query_time 是否 <= 1？
[ ] 寫入路徑是否批次化？有無單筆 INSERT 迴圈？
[ ] 事務是否過長？有無在事務中呼叫外部 HTTP/RPC？

結語

高效 MySQL 不是調出來的，是設計出來、寫出來、測出來、監控出來的。將六大鐵律內化為團隊肌肉記憶，建立「EXPLAIN 必跑、型別必審、連接池必配、監控必備」的工程文化，才能在業務高速增長期守住資料庫效能底線，避免「重構資料庫」成為專案最大風險。