MySQL 開發與運維規範大全：權限模型、命名規範、建表準則、索引策略、SQL 優化與架構治理
本文匯總生產環境驗證的 MySQL 規範體系，涵蓋用戶權限管理、SQL 開發建議、命名規範、建表準則、索引策略、SQL 優化技巧、架構治理原則七大維度，可直接作為團隊編碼規範、Code Review 清單、DBA 審核依據。
一、用戶權限管理規範
1. 建立用戶標準語法
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
host：localhost (本地)、192.168.1.% (內網段)、% (任意，慎用)。- 密碼必須滿足複雜度（長度≥12、大小寫數字特殊字元混合）。
2. 權限授予原則（最小權限）
- 僅授予業務必要的最小權限集（如唯讀帳號只給 SELECT）。
- 限制登入來源 IP/網段，禁止
'user'@'%' 無條件開放。
- 初始化資料庫後立即刪除無密碼匿名帳號（
DELETE FROM mysql.user WHERE User='';）。
- 定期審計清理離職/廢棄帳號（
REVOKE ALL → DROP USER）。
3. MySQL 權限完整對照表
| 權限 | 層級 | 說明 |
|---|
| CREATE | 庫/表/索引 | 建立庫、表、索引 |
| DROP | 庫/表 | 刪除庫、表 |
| GRANT OPTION | 庫/表/程序 | 轉授權限 |
| ALTER | 表 | 修改表結構 |
| DELETE | 表 | 刪除資料 |
| INDEX | 表 | 建立/刪除索引 |
| INSERT | 表 | 插入資料 |
| SELECT | 表 | 查詢資料 |
| UPDATE | 表 | 更新資料 |
| FILE | Server 檔案 | 讀寫伺服器檔案 |
| PROCESS | Server 管理 | 查看/殺死線程 |
| RELOAD | Server 管理 | FLUSH 權限/日誌/快取 |
| SHUTDOWN | Server 管理 | 關閉服務 |
| SUPER | Server 管理 | KILL 線程、修改全域變數 |
| REPLICATION SLAVE/CLIENT | Server 管理 | 複寫相關 |
| CREATE USER | Server 管理 | 建立用戶 |
4. 權限分佈矩陣
- 表級：SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, REFERENCES, INDEX, ALTER
- 欄位級：SELECT, INSERT, UPDATE, REFERENCES
- 程序級：EXECUTE, ALTER ROUTINE, GRANT
5. GRANT/REVOKE/FLUSH 實戰模板
-- 建立唯讀報表帳號
GRANT SELECT ON prod.* TO 'report'@'10.0.0.%' IDENTIFIED BY 'strong_pass';
-- 建立開發帳號（DDL+DML）
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX,
CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE,
CREATE TEMPORARY TABLES
ON devdb.* TO 'dev'@'192.168.0.%' IDENTIFIED BY 'strong_pass';
-- 回收權限
REVOKE DELETE ON prod.* FROM 'dev'@'192.168.0.%';
-- 刪除帳號
DROP USER 'dev'@'192.168.0.%';
-- 重命名帳號
RENAME USER 'old'@'%' TO 'new'@'%';
-- 修改密碼 (Root 改他人)
SET PASSWORD FOR 'user'@'host' = PASSWORD('newpass');
-- 自己改密
SET PASSWORD = PASSWORD('newpass');
-- 刷新權限 (直接操作權限表後必須)
FLUSH PRIVILEGES;
二、SQL 開發建議
1. 命名規範
- 庫名、表名、欄位名 全小寫 + 下劃線分隔（
order_detail）。
- 長度 ≤ 32 字元（上限 64），禁用保留字（若必須用，SQL 中必須加反引號）。
- 索引名建議：
idx_表名_欄位名、唯一索引 uk_表名_欄位名、主鍵 pk_表名。
2. 建表準則
| 規則 | 說明 |
|---|
| 引擎 | 強制 InnoDB（支援事務、行鎖、崩潰恢復、熱備） |
| 非負數值 | 使用 UNSIGNED（同位元組範圍更大，如 TINYINT UNSIGNED 0-255） |
| IPv4 存儲 | INT UNSIGNED + INET_ATON()/INET_NTOA()（4 Bytes vs CHAR(15) 15 Bytes） |
| 枚舉類型 | 改用 TINYINT（ENUM 修改值需 DDL、數值易混淆） |
| 大小寫敏感字串 | VARBINARY（無字元集開銷、比較快） |
| INT(N) 寬度 | N 僅為顯示寬度，不影響儲存範圍（INT(4) 仍佔 4 Bytes） |
| 時間類型 | 優先 TIMESTAMP (4 Bytes、自動更新)；年用 YEAR，日用 DATE |
| 冷熱分離 | 大欄位/低頻欄位拆獨立表（提升緩存命中、減少 IO） |
| 密碼存儲 | 嚴禁明文，加鹽不可逆加密（bcrypt/Argon2） |
| 主鍵 | 必須有，推薦 UNSIGNED AUTO_INCREMENT |
| 字元集 | 統一 utf8mb4，Emoji 需求才申請 utf8mb4 |
| 分庫分表 | 預留策略（千庫十表、十庫百表），便後續水平擴展 |
3. 索引策略
- 禁冗余索引：
(a,b,c) 覆蓋 (a,b)，後者為冗余。
- 禁重複索引：PK 已含唯一，勿再建 UK 同欄位。
- 低基數欄位不建索引：如「性別」，全表掃描優於索引回表。
- 善用覆蓋索引：
INDEX(uid, email) 讓 SELECT email FROM user WHERE uid=? 避免回表。
- 主鍵查詢天然覆蓋索引。
4. SQL 撰寫優化技巧
| 技巧 | 說明 |
|---|
| IN 代替 OR | IN 內部排序後範圍查找，效率高於 OR；IN 列表值 < 1000 |
| UNION ALL 代替 UNION | UNION ALL 免排序去重 |
| 避免 ORDER BY RAND() | 會產生磁盤臨時表；改用隨機主鍵值再主鍵查詢 |
| 合理分頁 | 深分頁用 WHERE id > last_id LIMIT N 替代 OFFSET |
| 禁止 SELECT * | 只取必要欄位，減少網路/解析/緩存開銷 |
| 避免不確定函數 | NOW(), RAND(), SYSDATE() 導致主從不一、Query Cache 失效 |
| 批次操作 | INSERT ... ON DUPLICATE KEY UPDATE, REPLACE INTO, INSERT IGNORE, 多值 INSERT |
| 拆分複雜 SQL | 小 SQL 易用 Query Cache、減少鎖時間、利用多核 |
| 合併 ALTER | 同表多個 ALTER 合併為單條 |
| 少用存儲過程/觸發器/視圖/UDF | 效能陷阱多、DDL/Sharding 困難、業務邏輯應上移至應用層 |
三、其他治理原則
- 管控 SUPER 權限帳號：極少數人持有，嚴格審批。
- 上線建表/改表需求：必須附帶完整 SQL（含 DML），供 DBA 審核索引與鎖影響。
- 業務邏輯不下沉：資料庫僅作儲存層，邏輯放應用層/中間層，支援快速迭代與水平擴展。
附：常用管理指令速查
SHOW DATABASES; -- 庫列表
SHOW TABLES; -- 表列表
SELECT DATABASE(); -- 當前庫
SELECT USER(); -- 當前連線用戶
DESC table1; -- 表結構
FLUSH PRIVILEGES; -- 刷新權限
將此規範內化為團隊肌肉記憶，配合自動化檢測工具（如 pt-query-digest、gh-ost、SQL Review 平台），可將 MySQL 相關線上故障降低 90% 以上。
