目錄
- 一、資料庫命名規範
- 二、資料庫基本設計規範
- 三、索引設計規範
- 四、資料庫字段設計規範
- 五、資料庫SQL 開發規範
- 六、資料庫操作行為規範
一、資料庫命名規範
- 所有資料對象名稱必須小寫 :
db_user
- 禁止使用MySQL 保留關鍵字，若是則引用 ``
- 臨時表以
tmp_
bak_
- 所有存儲相同資料的列名和列類型必須一緻
二、資料庫基本設計規範
-
MySQL 5.6
Innodb
- 資料庫和表的字元集統一使用
UTF-8
（統一字元集可以避免由于字元集轉換産生的亂碼）
MySQL 中 UTF-8 字元集 漢字 占 3 個位元組，ASCII 碼占用 1 個位元組。
- 所有表和字段都需要添加注釋。 使用
comment
-
盡可能控制單表資料量的大小，建議控制在500萬以内 （這種限制取決于存儲設定和檔案系統）
可以用曆史資料歸檔，分庫分表等手段來控制資料量大小
-
謹慎使用MySQL分區表
（分區表在實體上表現為多個檔案，在邏輯上表現為一個表）
謹慎選擇分區鍵，跨分區查詢效率可能更低
建議采用實體分表的方式管理大資料
-
盡量做到冷熱資料分類，減小表的寬度（即：列）
減少磁盤IO，保證熱資料的緩存命中率
利用有效的緩存，避免讀入無用的冷資料（不建議使用
SELECT *
）
垂直拆分：經常一起使用的列放到一個表中
-
禁止在表中建立預留字段
預留字段的命名很難做到見名識義
預留字段無法确認存儲的資料類型，所有無法選擇合适的類型
對預留字段類型的修改，會對表進行鎖定
- 禁止在資料庫中存儲圖檔，檔案等二進制資料
三、索引設計規範
-
限制每張表上的索引數量，建議單張表索引不超過 5 個
（索引可以提高效率同樣可以降低效率）
-
Innodb
主鍵
Innodb
表必須有一個主鍵
（不适用更新頻繁的列作為主鍵，不使用多列主鍵）
（不使用 UUID , MD5, HASH，字元串列作為主鍵）
（主鍵建議選擇使用自增 ID 值）
- 常見索引列 建議：
- SELECT 、UPDAT、DELETE語句的 WHERE 從句中的列
- 包含在 ORDER BY、GROUP BY 、DISTINCT中的字段
- 多表 JOIN 的關聯列
-
如何選擇索引列的順序
（區分度最高的列放在聯合索引的最左側 ，比如 主鍵）
（盡量把字段長度小的列放在聯合索引的最左側）
（使用最頻繁的列放到聯合索引的左側）
-
避免建立備援索引和重複索引
重複索引：primary key(id), index(id), unique index(id)
備援索引：index(a，b，c）、index（a，b）、index（a）
-
對于頻繁的查詢優先考慮使用覆寫索引
覆寫索引：就是包含了所有查詢字段的索引
（避免Innodb表進行索引的二次查詢）
（可以把随機IO變為順序IO加快速度）
-
盡量避免使用外鍵
不建議使用外鍵限制，但一定在表與表之間的關聯鍵上建立索引
外鍵可用于保證資料的參照完整性，但建議在業務端實作
外鍵會影響父表和子表的寫操作進而降低性能
四、資料庫字段設計規範
-
優先選擇符合存儲需要的最小的資料類型
将字元串轉化為數字類型存儲
比如：将IP轉為數字。（15位元組 -》4位元組）
INET_ATON('255.255.255.255') = 4294967295
INET_NTOA(4294967295)= ''255.255.255.255' -
對于非負資料采用無符号整型進行存儲
SIGNED INT : -2147483648 ~ 2147483647
UNSIGNED INT : 0 ~ 4294967295
- VARCHAR(N) 中的N代表的字元數，而不是位元組數
- 使用UTF-8 存儲漢字 VARCHAR(255) = 765 位元組
- 過大的長度會消耗更多的記憶體
- 避免使用
TEXT
BLOB
資料類型
建議把
BLOB
TEXT
列分離到單獨的擴充表中
TEXT
BLOB
- 避免使用
ENUM
資料類型
修改ENUM值需要使用
ALTER
語句
ENUM類型的 ORDER BY 操作效率低， 需要額外操作
禁止使用數值作為ENUM 的枚舉值
- 盡可能把所有列定義為
NOT NULL
索引NULL 列需要額外的空間來儲存，是以要占用更多的空間
進行比較和計算時要對NULL 值做特别的處理
-
字元串存儲日期型的資料（不正确的做法）
缺點1：無法用日期函數進行計算和比較
缺點2：用字元串存儲日期要占用更多的空間
使用 TIMESTAMP 或 DATETIME 類型存儲時間
TIMESTAMP : 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 （占用 4 個位元組）
超出TIMESTAMP取值範圍的使用 DATETIME 類型
- 同财務相關的金額類資料，必須使用
decimal
類型
Decimal 類型為精确浮點數，在計算時不會丢失精度
占用空間由定義的寬度決定（
.
占一個位元組）
可用于存儲必 bigint 更大的整數資料
五、資料庫SQL 開發規範
- 建議使用預編譯語句進行資料庫操作
-
避免資料類型的隐式轉換
隐式轉換會導緻索引失效
-
充分利用表上已經存在的索引
避免使用雙%号的查詢條件。如
a like '%123%'
一個SQL隻能利用到複合索引中的一列進行範圍查詢
使用
left join
not exists
not in
-
程式連接配接不同的資料庫使用不同的賬号，禁止跨庫查詢
為 資料庫 遷移和分庫分表留出餘地
降低業務耦合度
避免權限過大而産生的安全風險
-
禁止使用 SELECT * ，必須使用SELECT <字段清單> 查詢
消耗更多的 CPU 和 IO 以及網絡帶寬資源
無法使用覆寫索引
可減少表結構變更帶來的影響
- 禁止使用不含字段清單的INSERT 語句
- 避免使用子查詢，可以把子查詢優化為
join
操作
子查詢的結果集無法使用索引
子查詢會産生臨時表操作，如果子查詢資料量大則嚴重影響效率
消耗過多的CPU 及 IO資源
- 避免使用
JOIN
關聯太多的表
每 join 一個表會多占用一部分記憶體（join_buffer_size）
會産生臨時表操作，影響查詢效率
MySQL 最多允許關聯61個表，建議不超過5個
-
減少同資料庫的互動次數
資料庫更适合處理批量操作。
合并多個相同的操作到一個，可以提高處理效率
- 使用 in 代替 or
-
禁止使用 order by rand() 進行随機排序
會把表中所有符合條件的資料裝載到記憶體中進行排序
會消耗大量的CPU 和 IO及記憶體資源
-
WHERE 從句中禁止對列進行函數轉換和計算
對列進行函數轉換或計算會導緻無法使用索引
# 如：
where date(createtime) = '20160901'
# 改為：
where createtime >= '20160901' and createtime < '20160i902' - 在明顯不會有重複值時使用
UNION ALL
UNION
UNION 會把所有資料放到臨時表中後再進行去重操作
UNION ALL 不會再對結果集進行去重操作
-
拆分複雜的大SQL 為多個小SQL
MySQL 一個 SQL 隻能使用一個 CPU 進行計算
SQL 拆分後可以通過并行執行來提高處理效率
六、資料庫操作行為規範
-
超過100萬行的批量寫操作，要分批多次進行操作
大批量操作可能會造成嚴重的主從延遲
- 避免産生大事務操作
- 對于大表使用 pt-online-schema-change修改表結構
- 禁止為程式使用的賬号賦予super權限
- 對于程式資料庫賬号隻能在一個DB下使用，不準跨庫
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)