MySQL 資料庫開發與維運規範完整指南
本文整理自實務經驗，涵蓋 MySQL 既有問題、環境分層、開發命名與設計規範、索引策略、SQL 撰寫原則、DBA 維運建議、備份策略與架構演進等核心主題，旨在建立一套可落地、可自動化、長期演進的資料庫治理體系。
一、MySQL 面臨的核心挑戰
- 最佳化器對複雜 SQL 支援有限
- SQL 標準相容性不足
- 大規模叢集方案（中介軟體）成熟度待加強
- 全域自增 ID 生成器缺失
- 非同步邏輯複寫帶來資料安全隱憂
- Online DDL 鎖表影響服務可用性
- 高可用（HA）方案不夠完善
- 備份/恢複依賴外部工具，流程複雜
- 可觀測性不足，排查困難
- 分支版本眾多，選型困擾
二、資料庫環境分層（五環境模型）
| 環境 | 代碼 | 用途 | 權限 |
|---|
| 開發環境 | dev | 開發自測、結構變更 | 可讀寫、可改結構 |
| 測試環境 | qa | 功能/整合測試 | 可讀寫、可透過工具改結構 |
| 模擬環境 | sim | 上線預演、壓測、預執行 | 可讀寫、Web 平台管控 |
| 準實時唯讀 | real | 線上問題排查、資料查詢 | 唯讀、禁止改結構 |
| 線上環境 | online | 正式服務 | 僅 DBA 可操作，需審計記錄 |
帳號命名規範：w_業務名（寫）、r_業務名（讀），權限最小化、環境隔離。
三、資料庫開發規範
1. 核心約束規範
- 字元集統一使用
utf8mb4（支援 emoji），建表時指定 DEFAULT CHARSET=utf8mb4。
- 儲存引擎強制
InnoDB。
- 變長字串用
VARCHAR/VARBINARY，禁止存圖片/檔案（存物件儲存路徑）。
- 所有表、欄位、索引必須加註釋（
COMMENT）。
- 單表資料量 < 1 億行、物理大小 < 10GB、平均行長 < 8KB。
- 嚴禁線上壓測、嚴禁開發/測試環境直連線上資料庫。
2. 命名規範
- 庫/表/欄位/索引：全小寫 + 底線分隔，見名知意，長度 ≤ 32 字元。
- 禁用保留字、拼音、駝峰式。
- 臨時表：
tmp_業務_日期；備份表：bak_業務_日期。
- 資料庫名：3-7 個英文字母，多詞底線分隔。
3. 分表規範
- 禁用分區表，改用應用層或中介層分表。
- 拆分大欄位/低頻欄位（冷熱分離）。
- Hash 分表：後綴用十進制數字，從 0 開始（如
order_0、order_1）。
- 時間分表：後綴格式
YYYY[MM][DD][HH]。
4. 欄位設計規範
- 預設
NOT NULL，除非業務真需 NULL。
- 整數優先
UNSIGNED，類型越小越好。
- 時間用
TIMESTAMP（4 位元組），IPv4 用 UNSIGNED INT（INET_ATON/NTOA）。
- 精確浮點數用
DECIMAL，禁用 FLOAT/DOUBLE。
- 極力避免
BLOB/TEXT，若必須用拆分到獨立表。
- 用
TINYINT 替代 ENUM，明文密碼嚴禁入庫。
- 大小寫敏感字串用
VARBINARY。
5. 索引規範（核心）
- 單表索引 ≤ 5 個，複合索引欄位 ≤ 5 個，遵循最左前綴原則。
- 高區分度欄位前置，字串索引用前綴（≤ 8 字元）。
- 覆蓋索引優先解決 80% 核心查詢。
- 主鍵原則：必須有、非更新頻繁欄位、非字串、非 UUID/MD5、建議自增或發號器。
- 重要 SQL 必須命中索引：
WHERE、ORDER BY、GROUP BY、JOIN 欄位。
- 避免隱式轉換（如
VARCHAR 欄位傳入數字導致全表掃描）。
- 拒絕冗餘索引、外鍵、
LIKE '%...'、負向查詢（NOT IN/NOT LIKE）。
6. SQL 撰寫規範
- 使用預編譯語句（Prepared Statement），僅傳參數。
- 大表禁用
JOIN，MySQL 最佳化器對 Join 支援弱。
- 業務邏輯勿下推至資料庫（存儲過程/觸發器/函數慎用）。
- DML 批次合併（多值
INSERT），減少交互。
- 大分頁優化：先取主鍵再關聯回表。
UPDATE/DELETE 禁用 LIMIT（主從不一致風險）。IN 替代 OR，IN 值 ≤ 1000。- 禁用
ORDER BY RAND()、臨時表、UNION（改 UNION ALL）。
- 禁止單條 SQL 同時更新多表。
- 查詢僅選需要欄位，禁用
SELECT *。
- 子查詢優先改寫為
JOIN。
- 多表關聯：關聯欄位型別一致且有索引，結果集小的表做驅動表，排序欄位屬驅動表。
四、DBA 維運規範
版本選擇優先序
- MySQL 社區版（用戶群最大、生態最豐）
- Percona Server（新特性多、效能強）
- MariaDB
- MySQL 企業版（收費）
核心維運項目
- SQL 審核（DDL 審核與執行窗口管控，特別是 Online DDL）
- 高危操作檢查（
DROP 前必備份）
- 日誌分析（慢查詢、錯誤日誌）
- 備份策略執行與驗證
Online DDL 工具推薦：pt-online-schema-change
優點：無阻塞寫入、完善的檢測與流控機制。
限制：執行時間較長、需有主鍵/唯一鍵、同埠並發修改受限。
叢集架構推薦：MHA (Master High Availability)
採用 一主多從 或 雙主多從，支援 GTID 模式。
優勢：開源、成熟、嚴格判斷減少資料遺失、可二次開發、支援 Binlog Server 提升傳輸效率。
限制：需節點間 SSH 互信、腳本需持續完善。
備份策略建議
- 核心業務：熱備（xtrabackup）+ 邏輯備份 + 延遲備份（防誤刪）。
- 全量 Binlog 保留（支援 PITR）。
- 備份調度集中化、結果統計分析、一致性校驗、分散式檔案系統存儲（解決 NFS 效能與擴容）。
五、資料拆分原則
- 解決單機寫入壓力與容量瓶頸。
- 垂直拆分（業務解耦）+ 水平拆分（分片）。
- 適度拆分，避免過度導致維運成本暴增。
- 最佳有中介層管控分片邏輯。
結語
規範的價值在於「長期堅持」與「自動化落地」。建議將上述規範轉化為審核工具（如 SQL Advisor、自動化審核平台），納入 CI/CD 流程，讓違規 SQL 無法上線，真正實現開發與 DBA 雙贏。
