為統一團隊資料庫開發標準、提升效能與可維護性,彙整 29 條必須遵守的 MySQL 開發鐵律,涵蓋命名慣例、資料型別選擇、索引設計、SQL 寫法與分庫分表策略。
📐 命名與基礎規範 (1-5)
- 全小寫 + 底線分隔:庫/表/欄位統一小寫,避免
lower_case_table_names 導致跨平台大小寫敏感差異。
- 長度 ≤ 32 字元:雖支援 64 字元,但縮短名稱利於傳輸、辨識與工具相容。
- 強制 InnoDB:5.5+ 預設引擎,支援交易、行鎖、熱備份、多核擴展。
- 禁用保留字:避免反引號包裹帶來的 SQL 複雜度與 Shell 轉義地獄。
- 禁止分區表:分區鍵限制多、DDL/Sharding/單表還原困難,改由業務層手動 Sharding。
🔢 資料型別選擇 (6-11)
- 非負數值用 UNSIGNED:同位元寬度下範圍翻倍(TINYINT 0-255 vs -128~127)。
- IPv4 用 INT UNSIGNED:4 bytes vs CHAR(15) 15 bytes,配合
INET_ATON/NTOA 轉換。
- TINYINT 代替 ENUM:ENUM 修改需 DDL、數值預設值易混淆;TINYINT + 應用層字典更靈活。
- 大小寫敏感字串用 VARBINARY:無字元集開銷、比較更快。
- INT(n) 僅影響顯示寬度:儲存固定 4 bytes,
INT(4) 不限制數值範圍。
- 時間型別精準選擇:
- 年份 →
YEAR (1 byte)
- 日期 →
DATE (3 bytes)
- 精確到秒 → 優先
TIMESTAMP (4 bytes,自動初始化/更新);跨時區或 2038 年後用 DATETIME (8 bytes)。
🚫 NULL 與冷熱分離 (12-13)
- 全欄位 NOT NULL:NULL 額外佔用位圖空間、B+ 樹不存 NULL 降低索引效率,用 0/空串/特殊值替代。
- 大欄位/低頻欄位拆表:冷熱分離提升緩存命中率、減少磁碟 I/O。
🔐 安全與主鍵 (14-15)
- 嚴禁明文密碼:鹽值雜湊(bcrypt/Argon2)+ 應用層驗證。
- 主鍵必有,推薦 UNSIGNED AUTO_INCREMENT:避免隱藏主鍵導致定位困難、Row-based 複寫效率低。
📇 索引設計鐵律 (16-19)
- 冗餘索引:
(a,b,c) 存在時,(a,b) 為冗餘,必刪。
- 重複索引:PK + UK 同欄位為重複,必刪。
- 低基數欄位不建索引:性別、狀態等全表掃描更快,索引反增 I/O。
- 善用覆蓋索引:Secondary Index 存 PK 值,回表成本高;
INDEX(uid,email) 可覆蓋 SELECT email,uid FROM ... WHERE uid=?。
✍️ SQL 寫法優化 (20-26)
- IN 代替 OR:
IN 內部排序後範圍查找,值勿超過 1000。
- 字元集 UTF8 / UTF8MB4:統一通用、Emoji 需 MB4。
- UNION ALL 代替 UNION:免排序去重。
- 禁用 ORDER BY RAND():產生偽欄位+磁碟臨時表,改用
RAND()*MAX_ID 取隨機 PK 再查詢。
- 高效分頁兩式:
- 小結果集/複雜條件:
LIMIT offset,size 直取全欄位。
- 大結果集+覆蓋索引:
SELECT t.* FROM (SELECT id FROM t WHERE ... ORDER BY ... LIMIT offset,size) a JOIN t ON a.id=t.id,每頁成本固定。
- SELECT 只取必要欄位:減少網路傳輸、利用覆蓋索引、結構變更不影響程式。
- 禁用不確定函數:
NOW()、RAND()、SYSDATE() 導致主從不一致、查詢快取失效。
🏗️ 架構與運維 (27-29)
- 分庫分表策略預留:千庫十表、十庫百表,配合 MySQL 多執行緒複寫。
- 批次 SQL 減少交互:
INSERT ... ON DUPLICATE KEY UPDATE、REPLACE INTO、INSERT IGNORE、多值 INSERT。
- 拆解複雜 SQL、避免大事務:簡單 SQL 易命中 Query Cache、縮短鎖持有時間。
將此規範納入 Code Review Checklist,配合 pt-query-digest 定期掃描慢查詢,持續沉澱最佳實踐。