大型互聯網公司使用的數據庫設計規範
- 2019 年 10 月 4 日
- 筆記
DDL設計標準
1、 所有表的DDL，都不回退
2、 數據庫命名規範，統一：hs_xxxx；表名不超過40個字符（即最大只能40個字符）
3、 表一旦設計好，字段只允許增加，不允許減少（drop column），不允許改名稱（change column）
4、 統一使用INNODB存儲引擎,UTF8編碼（整個數據庫的編碼統一為utf8_general_ci，為此不需要建立表的DDL加上特別CHARACTER SET utf8 COLLATE utf8_general_ci）；
5、 禁用Stored procedure （包括存儲過程，函數，觸發器）；
6、 表必須有主鍵，建議統一由Auto-Increment字段生成整型，不建議使用組合主鍵， 自增id只作為虛擬主鍵，不建議與業務數據處理有關聯關係，如果把控不好，會有問題（案例：AUTO_INCREMENT主鍵字段不要與業務有關聯關係）
索引使用標準
1、非唯一索引建議使用「idx_表縮寫名稱_字段縮寫名稱」進行命名。
2、唯一索引建議使用「uniq_表縮寫名稱_字段縮寫名稱」進行命名。
3、索引名稱必須使用小寫。
4、唯一鍵不和主鍵重複。每個業務實體表和關係表都應該至少有一個業務主鍵對應的唯一索引。
5、索引字段的順序需要考慮字段值去重之後的個數，個數多的放在前面，就是數據分佈。
6、使用EXPLAIN判斷SQL語句是否合理使用索引，盡量避免extra列出現：Using File Sort，Using Temporary。
7、UPDATE、DELETE語句需要根據WHERE條件添加索引。
8、合理創建聯合索引（避免冗餘），(a,b,c) 相當於 (a) 、(a,b) 、(a,b,c)。
9、合理利用覆蓋索引。比如SELECT email,uid FROM user_email WHERE uid=xx，如果uid不是主鍵，適當時候可以將索引添加為index(uid,email)，以獲得性能提升。
約束設計標準
1、 主鍵的內容不能被修改。
2、外鍵約束一般不在數據庫上創建，只表達一個邏輯的概念，由程序控制。
3、 禁用數據庫外鍵
4、命名
a) 主鍵約束：默認PRIMARY；
b) unique約束：UK_<column_name>
c) check約束：CK_<column_name>
d) 外鍵約束：業務禁用
事務的處理標準
1、一個事務，處理的行數不能超過1000 rows/s
2、操作完後請及時commit，釋放鎖；
3、並發下，避免死鎖，如果存在並發對相同對數據做DML，請按順序操作，舉例
# 有3條數據，主鍵是1，2，3，那麼
Session 1:
update 數據的順序是 1，2，3
Session 2:
update 數據的順序是 3，2，1 或 2，1，3
在並發下，存在死鎖的可能性，如購物車曾經出現過
Session 1，更新了 1，持有鎖，將要更新2
Session 2，更新了 2，持有鎖，將要更新1
#### Oops，死鎖了。
分區表使用規範
原則上：禁止使用分區表！禁止使用分區表！禁止使用分區表！
1、分區表也是一個db特性，少一個特性，少一個功能bug的風險
2、其實分區表解決的是，單表大數據量，然後這些數據不太重要，需要定期drop partition清理，方便清理而已，真正帶來查詢效率的，是索引和數據訪問方式
3、DBA無法做Online DDL，這個才是重點中的重點
如果一定要用遵循
1、單表大數據量且有一定的字段冗餘以後都不會做DDL了
2、然後這些數據生命周期很短，不太重要，不需要歸檔，可以直接清理的，定期drop partition可以方便清理，如監控數據，告警數據，一些日誌數據等
SQL語句標準
0、請避免 多於2表的join。
1、使用prepared statement，可以提供性能並且避免SQL注入。
2、SELECT語句只獲取需要的字段，禁止使用SELECT * FROM語句，這是有效防止新增字段對應用邏輯的影響，還能減少對性能的影響；
3、INSERT語句必須顯式的指明字段名稱，不使用INSERT INTO table value()。
4、禁止在where子句中對字段施加函數，如to_date（add_time)>xxxxx,應改為:add_time >= unix_timestamp(date_add(str_to_date('20130227','%Y%m%d'),interval – 29 day))
5、UPDATE、DELETE語句不使用LIMIT 。以前我們使用的是MySQL 5.0，使用statment模式，所以有此規範，目前5.5，row和mixed模式不會出現，此規則去掉。
6、寫到應用程序里的SQL語句，禁止一切DDL操作，如對這些權限有要求，必需與DBA協商同意方可使用
7、WHERE條件中必須使用合適的類型，避免MySQL進行隱式類型轉化，如ISENDED=1，字段類型是tinyint，那麼不能是ISENDED=『1』。
8、避免在SQL語句進行數學運算或者函數運算，容易將業務邏輯和DB耦合在一起。
9、INSERT語句使用batch提交。
10、避免使用存儲過程、觸發器、函數等，容易將業務邏輯和DB耦合在一起，並且MySQL的存儲過程、觸發器、函數中存在一定的bug。
11、使用合理的SQL語句減少與數據庫的交互次數。
12、不使用ORDER BY RAND()，使用其他方法替換。
13、建議使用合理的分頁方式以提高分頁的效率。
14、InnoDB表避免使用COUNT(*)操作，計數統計實時要求較強可以使用memcache或者redis，非實時統計可以使用單獨統計表，定時更新。
15、不建議使用%前綴模糊查詢，例如LIKE 「%weibo」。
16、避免多餘的排序。使用GROUP BY 時，默認會進行排序，當你不需要排序時，可以使用order by null,例如Select a.OwnerUserID,count(*) cnt from DP_MessageList a group by a.OwnerUserID order by null;
17、 新增排序要求：不鼓勵在DB里排序，特別是只有1000行一下的，請在app server上排序，app server有上百台，而db僅僅個位數的服務器數量，排序都在db，會把db壓垮的，特別是禁止上千行的排序在db這邊。
18、 禁止使用 REPLACE INTO ；
19、禁止使用子查詢，select col、col from table where id in （select col from table）這是禁止的；
20、batch size 大小不能超過1000，同時請根據業務QPS和記錄長度來評估1000以內什麼值合適，如where col in （）的值不能超過1000。參考：batch size標準
21、禁止使用 UUID()，USER()這樣的MYSQL INSIDE函數對於複製來說是很危險的，會導致主備數據不一致，重要的是會嚴重影響mysql性能。
22、 如果應用使用的是長連接，應用必須具有自動重連的機制。但請避免每執行一個SQL去檢查一次DB可用性；
使用replace帶來的問題
1、Replace into 操作在唯一鍵重複情況下，是先嘗試寫入，檢測到衝突則刪除原記錄，再寫入新記錄。該操作在二進制日誌中只有一條 update 操作，將導致同一張表的 auto_increment 值主從不一致，主從切換後，極易致業務寫入失敗；
2、據 1 描述， 一次 Replace into 的寫入操作最壞情況下有兩次 DB 操作，操作負擔重；
3、並發情況下，Replace into 因不同 session 獲取鎖的順序問題，產生死鎖的概率更高；
