萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 開發規範 29 條鐵律:命名、型別、索引、SQL 與架構全覆蓋

為統一團隊資料庫開發標準、提升效能與可維護性,彙整 29 條必須遵守的 MySQL 開發鐵律,涵蓋命名慣例、資料型別選擇、索引設計、SQL 寫法與分庫分表策略。

📐 命名與基礎規範 (1-5)

  1. 全小寫 + 底線分隔:庫/表/欄位統一小寫,避免 lower_case_table_names 導致跨平台大小寫敏感差異。
  2. 長度 ≤ 32 字元:雖支援 64 字元,但縮短名稱利於傳輸、辨識與工具相容。
  3. 強制 InnoDB:5.5+ 預設引擎,支援交易、行鎖、熱備份、多核擴展。
  4. 禁用保留字:避免反引號包裹帶來的 SQL 複雜度與 Shell 轉義地獄。
  5. 禁止分區表:分區鍵限制多、DDL/Sharding/單表還原困難,改由業務層手動 Sharding。

🔢 資料型別選擇 (6-11)

  1. 非負數值用 UNSIGNED:同位元寬度下範圍翻倍(TINYINT 0-255 vs -128~127)。
  2. IPv4 用 INT UNSIGNED:4 bytes vs CHAR(15) 15 bytes,配合 INET_ATON/NTOA 轉換。
  3. TINYINT 代替 ENUM:ENUM 修改需 DDL、數值預設值易混淆;TINYINT + 應用層字典更靈活。
  4. 大小寫敏感字串用 VARBINARY:無字元集開銷、比較更快。
  5. INT(n) 僅影響顯示寬度:儲存固定 4 bytes,INT(4) 不限制數值範圍。
  6. 時間型別精準選擇:
    • 年份 → YEAR (1 byte)
    • 日期 → DATE (3 bytes)
    • 精確到秒 → 優先 TIMESTAMP (4 bytes,自動初始化/更新);跨時區或 2038 年後用 DATETIME (8 bytes)。

🚫 NULL 與冷熱分離 (12-13)

  1. 全欄位 NOT NULL:NULL 額外佔用位圖空間、B+ 樹不存 NULL 降低索引效率,用 0/空串/特殊值替代。
  2. 大欄位/低頻欄位拆表:冷熱分離提升緩存命中率、減少磁碟 I/O。

🔐 安全與主鍵 (14-15)

  1. 嚴禁明文密碼:鹽值雜湊(bcrypt/Argon2)+ 應用層驗證。
  2. 主鍵必有,推薦 UNSIGNED AUTO_INCREMENT:避免隱藏主鍵導致定位困難、Row-based 複寫效率低。

📇 索引設計鐵律 (16-19)

  1. 冗餘索引:(a,b,c) 存在時,(a,b) 為冗餘,必刪。
  2. 重複索引:PK + UK 同欄位為重複,必刪。
  3. 低基數欄位不建索引:性別、狀態等全表掃描更快,索引反增 I/O。
  4. 善用覆蓋索引:Secondary Index 存 PK 值,回表成本高;INDEX(uid,email) 可覆蓋 SELECT email,uid FROM ... WHERE uid=?。

✍️ SQL 寫法優化 (20-26)

  1. IN 代替 OR:IN 內部排序後範圍查找,值勿超過 1000。
  2. 字元集 UTF8 / UTF8MB4:統一通用、Emoji 需 MB4。
  3. UNION ALL 代替 UNION:免排序去重。
  4. 禁用 ORDER BY RAND():產生偽欄位+磁碟臨時表,改用 RAND()*MAX_ID 取隨機 PK 再查詢。
  5. 高效分頁兩式:
    • 小結果集/複雜條件:LIMIT offset,size 直取全欄位。
    • 大結果集+覆蓋索引:SELECT t.* FROM (SELECT id FROM t WHERE ... ORDER BY ... LIMIT offset,size) a JOIN t ON a.id=t.id,每頁成本固定。
  6. SELECT 只取必要欄位:減少網路傳輸、利用覆蓋索引、結構變更不影響程式。
  7. 禁用不確定函數:NOW()、RAND()、SYSDATE() 導致主從不一致、查詢快取失效。

🏗️ 架構與運維 (27-29)

  1. 分庫分表策略預留:千庫十表、十庫百表,配合 MySQL 多執行緒複寫。
  2. 批次 SQL 減少交互:INSERT ... ON DUPLICATE KEY UPDATE、REPLACE INTO、INSERT IGNORE、多值 INSERT。
  3. 拆解複雜 SQL、避免大事務:簡單 SQL 易命中 Query Cache、縮短鎖持有時間。

將此規範納入 Code Review Checklist,配合 pt-query-digest 定期掃描慢查詢,持續沉澱最佳實踐。

copyright © 萬盛學電腦網 all rights reserved