「MySQL」之 開發規範

目錄

• 一、資料庫命名規範
• 二、資料庫基本設計規範
• 三、索引設計規範
• 四、資料庫欄位設計規範
• 五、資料庫SQL 開發規範
• 六、資料庫操作行為規範

一、資料庫命名規範

1. 所有數據對象名稱必須小寫 : db_user
2. 禁止使用MySQL 保留關鍵字，若是則引用 ``
3. 臨時表以tmp_ 開頭，備份表以bak_ 開頭並以時間戳結尾
4. 所有存儲相同數據的列名和列類型必須一致

二、資料庫基本設計規範

1. MySQL 5.6以後，必須使用 Innodb 存儲引擎
2. 資料庫和表的字符集統一使用 UTF-8
3. （統一字符集可以避免由於字符集轉換產生的亂碼）
4. MySQL 中 UTF-8 字符集 漢字 占 3 個位元組，ASCII 碼佔用 1 個位元組。
5. 所有表和欄位都需要添加註釋。 使用 comment
6. 儘可能控制單表數據量的大小，建議控制在500萬以內 （這種限制取決於存儲設置和文件系統）
7. 可以用歷史數據歸檔，分庫分表等手段來控制數據量大小
8. 謹慎使用MySQL分區表
9. （分區表在物理上表現為多個文件，在邏輯上表現為一個表）
10. 謹慎選擇分區鍵，跨分區查詢效率可能更低
11. 建議採用物理分表的方式管理大數據
12. 盡量做到冷熱數據分類，減小表的寬度（即：列）
13. 減少磁碟IO，保證熱數據的緩存命中率
14. 利用有效的緩存，避免讀入無用的冷數據（不建議使用 SELECT *）
15. 垂直拆分：經常一起使用的列放到一個表中

16. 禁止在表中建立預留欄位

17. 預留欄位的命名很難做到見名識義
18. 預留欄位無法確認存儲的數據類型，所有無法選擇合適的類型
19. 對預留欄位類型的修改，會對錶進行鎖定

20. 禁止在資料庫中存儲圖片，文件等二進位數據

三、索引設計規範

1. 限制每張表上的索引數量，建議單張表索引不超過 5 個
2. （索引可以提高效率同樣可以降低效率）
3. Innodb 按照 主鍵 索引來組織表，每個Innodb表必須有一個主鍵
4. （不適用更新頻繁的列作為主鍵，不使用多列主鍵）
5. （不使用 UUID , MD5, HASH，字元串列作為主鍵）
6. （主鍵建議選擇使用自增 ID 值）

7. 常見索引列 建議：

• SELECT 、UPDAT、DELETE語句的 WHERE 從句中的列
• 包含在 ORDER BY、GROUP BY 、DISTINCT中的欄位
• 多表 JOIN 的關聯列

1. 如何選擇索引列的順序

2. （區分度最高的列放在聯合索引的最左側 ，比如 主鍵）
3. （盡量把欄位長度小的列放在聯合索引的最左側）
4. （使用最頻繁的列放到聯合索引的左側）

5. 避免建立冗餘索引和重複索引

6. 重複索引：primary key(id), index(id), unique index(id)
7. 冗餘索引：index(a，b，c）、index（a，b）、index（a）

8. 對於頻繁的查詢優先考慮使用覆蓋索引

9. 覆蓋索引：就是包含了所有查詢欄位的索引
10. （避免Innodb表進行索引的二次查詢）
11. （可以把隨機IO變為順序IO加快速度）

12. 盡量避免使用外鍵

13. 不建議使用外鍵約束，但一定在表與表之間的關聯鍵上建立索引
14. 外鍵可用於保證數據的參照完整性，但建議在業務端實現
15. 外鍵會影響父表和子表的寫操作從而降低性能

四、資料庫欄位設計規範

1. 優先選擇符合存儲需要的最小的數據類型
2. 將字元串轉化為數字類型存儲
3. 比如：將IP轉為數字。（15位元組 -》4位元組）

INET_ATON("255.255.255.255") = 4294967295
INET_NTOA(4294967295)= ""255.255.255.255"
1
2

1. 對於非負數據採用無符號整型進行存儲
2. SIGNED INT : -2147483648 ~ 2147483647
3. UNSIGNED INT : 0 ~ 4294967295
4. VARCHAR(N) 中的N代表的字元數，而不是位元組數
5. 使用UTF-8 存儲漢字 VARCHAR(255) = 765 位元組
6. 過大的長度會消耗更多的內存
7. 避免使用 TEXT BLOB 數據類型
8. 建議把 BLOB 或是 TEXT 列分離到單獨的擴展表中
9. TEXT 或 BLOB 類型只能使用前綴索引
10. 避免使用 ENUM 數據類型
11. 修改ENUM值需要使用 ALTER 語句
12. ENUM類型的 ORDER BY 操作效率低， 需要額外操作
13. 禁止使用數值作為ENUM 的枚舉值
14. 儘可能把所有列定義為 NOT NULL
15. 索引NULL 列需要額外的空間來保存，所以要佔用更多的空間
16. 進行比較和計算時要對NULL 值做特別的處理

17. 字元串存儲日期型的數據（不正確的做法）

18. 缺點1：無法用日期函數進行計算和比較
19. 缺點2：用字元串存儲日期要佔用更多的空間
20. 使用 TIMESTAMP 或 DATETIME 類型存儲時間
21. TIMESTAMP : 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 （佔用 4 個位元組）
22. 超出TIMESTAMP取值範圍的使用 DATETIME 類型
23. 同財務相關的金額類數據，必須使用decimal類型
24. Decimal 類型為精確浮點數，在計算時不會丟失精度
25. 佔用空間由定義的寬度決定（. 佔一個位元組）
26. 可用於存儲必 bigint 更大的整數數據

五、資料庫SQL 開發規範

1. 建議使用預編譯語句進行資料庫操作
2. 避免數據類型的隱式轉換
3. 隱式轉換會導致索引失效
4. 充分利用表上已經存在的索引
5. 避免使用雙%號的查詢條件。如 a like "%123%"
6. 一個SQL只能利用到複合索引中的一列進行範圍查詢
7. 使用left join 或 not exists 來優化 not in 操作
8. 程序連接不同的資料庫使用不同的賬號，禁止跨庫查詢
9. 為 資料庫 遷移和分庫分表留出餘地
10. 降低業務耦合度
11. 避免許可權過大而產生的安全風險

12. 禁止使用 SELECT * ，必須使用SELECT <欄位列表> 查詢

13. 消耗更多的 CPU 和 IO 以及網路帶寬資源
14. 無法使用覆蓋索引
15. 可減少表結構變更帶來的影響
16. 禁止使用不含欄位列表的INSERT 語句
17. 避免使用子查詢，可以把子查詢優化為 join 操作
18. 子查詢的結果集無法使用索引
19. 子查詢會產生臨時表操作，如果子查詢數據量大則嚴重影響效率
20. 消耗過多的CPU 及 IO資源
21. 避免使用 JOIN 關聯太多的表
22. 每 join 一個表會多佔用一部分內存（join_buffer_size）
23. 會產生臨時表操作，影響查詢效率
24. MySQL 最多允許關聯61個表，建議不超過5個
25. 減少同資料庫的交互次數
26. 資料庫更適合處理批量操作。
27. 合併多個相同的操作到一個，可以提高處理效率
28. 使用 in 代替 or
29. 禁止使用 order by rand() 進行隨機排序
30. 會把表中所有符合條件的數據裝載到內存中進行排序
31. 會消耗大量的CPU 和 IO及內存資源
32. WHERE 從句中禁止對列進行函數轉換和計算
33. 對列進行函數轉換或計算會導致無法使用索引

# 如：
where date(createtime) = "20160901"
# 改為：
where createtime >= "20160901" and createtime < "20160i902"
1
2
3
4
5

1. 在明顯不會有重複值時使用 UNION ALL 而不是 UNION
2. UNION 會把所有數據放到臨時表中後再進行去重操作
3. UNION ALL 不會再對結果集進行去重操作
4. 拆分複雜的大SQL 為多個小SQL
5. MySQL 一個 SQL 只能使用一個 CPU 進行計算
6. SQL 拆分後可以通過並行執行來提高處理效率

六、資料庫操作行為規範

1. 超過100萬行的批量寫操作，要分批多次進行操作
2. 大批量操作可能會造成嚴重的主從延遲
3. 避免產生大事務操作
4. 對於大表使用 pt-online-schema-change修改表結構
5. 禁止為程序使用的賬號賦予super許可權
6. 對於程序資料庫賬號只能在一個DB下使用，不準跨庫

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！