原创 马听 MySQL数据库联盟
大家好，根據以往的工作經驗，很多時候資料庫出問題，都是沒有規範使用資料庫所導致的。
例如條件欄位沒有加入合適索引導致查詢緩慢，進而影響到使用者體驗；
還有就是什麼都往Redis存放，導致成本浪費。
等等。
所以，制定內部的資料庫規範，顯得特別重要。
在我製作的DBA體系課中，就有一部分是模擬DBA去維護一套電商網站的資料庫（MySQL、Redis、MongoDB），包括高可用環境準備、日常維護、備份、監控、自動化、遷移等。
當然，也包括這套電商網站資料庫的開發規範。
這篇文章就來分享一下。
MySQL
1 命名規範
1.1、表名建議使用有業務意義的英文詞彙，必要時可加數字和底線，並以英文字母開頭；
例如商品表products，如果有多個表，可以寫成products_001
1.2、庫、表格、字段全部採用小寫；
防止因為大小寫問題找不到表格或弄錯表。
1.3、避免用MySQL的保留字；
關於哪些是MySQL的保留字，可以參考官方文件：
1.4、命名（包括表名、列名）禁止超過 30 個字元；
一張表，表名四五十個字符，例如：
online_store_product_information_table
真沒必要啊，維護和管理也挺麻煩的。
1.5、臨時函式庫、表名必須以 tmp 為前綴，並以日期為後綴；
如：tmp_shop_info_20240101；
1.6、備份庫、表格必須以 bak 為前綴，並以日期為後綴；
如：bak_shop_info_20240101；
1.7、索引命名：
非唯一索引必須依照「idx_欄位名稱」進行命名；
唯一索引必須按照“uniq_欄位名稱”進行命名。
2 設計規範
2.1、主鍵：
無特殊要求，主鍵均按如下語句來設定：
id INT NOT NULL AUTO_INCREMENT COMMENT ‘主鍵’,
2.2、如無特殊要求，建議都使用 InnoDB 引擎；
2.3、字符集
預設使用utf8mb4字元集；
資料排序規則使用utf8mb4_general_ci；
原因：utf8mb4為萬國碼，無亂碼風險；與utf8編碼相比，utf8mb4能支持Emoji表情。
2.4、所有表格、欄位都需要增加comment來描述此表格、欄位所表示的含義；
比如：
data_status TINYINT NOT NULL DEFAULT 1 COMMENT ‘1代表記錄有效，0代表記錄無效’。
2.5、如無說明，建議表包含create_time和update_time字段，即表必須包含記錄建立時間和修改時間的字段；
2.6、用盡量少的儲存空間來存放一個欄位的資料：
能用 int 的就不用 char 或 varchar；
能用 tinyint 的就不用 int；
使用 UNSIGNED 儲存非負數值；
只儲存年使用 YEAR 類型；
只儲存日期使用 DATE 類型。
2.7.儲存精確浮點數必須使用DECIMAL取代FLOAT和DOUBLE；
原因：儲存的時候，FLOAT和DOUBLE都存在精度損失的問題，很可能在比較值的時候，得到不正確的結果。
2.8、盡可能不使用 TEXT、BLOB 類型；
原因：會浪費更多的磁碟和記憶體空間，非必要的大量大字段查詢會淘汰掉熱數據，導致記憶體命中率急劇降低，影響資料庫效能。
如果實在有某個欄位過長需要使用 TEXT、BLOB 類型，建議獨立出來一張表，用主鍵對應，避免影響原表的查詢效率。
2.9、禁止在資料庫中儲存明文密碼；
2.10、索引設計規範：
a、需要新增索引的字段
UPDATE、DELETE 語句的 WHERE 條件列；
ORDER BY、GROUP BY、DISTINCT 的字段
JOIN 語句的關聯字段
b、單表索引建議控制在 5 個以內；
c、適當配置聯合索引；
例如方便查詢能走覆蓋索引，或幾個欄位同時作為條件的機率很高時，可以增加適當的聯合索引。
d、業務上具有唯一性的字段，添加成唯一索引；
遇到過幾次欄位在業務場景上要求唯一，但是該欄位在資料庫裡的資料卻出現了重複。因此在程式碼層考慮外，還需要在 MySQL 上的對應欄位中新增唯一索引。
e、在 varchar 欄位上建立索引時，建議根據實際文字區分度指定索引長度；
原因：可以降低索引所佔用的空間，而且很多時候，例如字串基本上是長度大於 20，但是只要建立長度為 20 的索引，就已經有很高的區分度了。可以使用 count(distinct left(列名, 索引長度))/count(*) 的區分度來決定。
f、索引禁忌：
不在低基數列上建立索引，例如：性別欄位。
不在索引列進行數學運算和函數運算
2.11、不建議使用外鍵；
原因：外鍵會導致表與表之間耦合，update 與 delete 操作都會涉及相關聯的表，很影響sql 的執行效率，甚至會造成死鎖。高並發情況下容易造成資料庫效能下降。
2.12、禁止使用預存程序、檢視、觸發器、Event ；
原因：高併發的情況下，這些功能很可能會將資料庫拖死，業務邏輯放到服務層具備更好的擴充性。
2.13、單表列數目建議小於30；
2.14、表示例：
CREATE TABLE student_info (
id INT NOT NULL AUTO_INCREMENT COMMENT ‘主鍵’,
stu_name VARCHAR(10) NOT NULL DEFAULT ” COMMENT ‘姓名’,
stu_class VARCHAR(10) NOT NULL DEFAULT ” COMMENT ‘班級’,
stu_num INT NOT NULL DEFAULT ‘0’ COMMENT ‘學號’,
stu_score SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘總分’,
tuition DECIMAL(5, 2) NOT NULL DEFAULT ‘0’ COMMENT ‘學費’,
phone_number VARCHAR(20) NOT NULL DEFAULT ‘0’ COMMENT ‘電話號碼’,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘記錄建立時間’,
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘記錄更新時間’,
status TINYINT NOT NULL DEFAULT ‘1’ COMMENT ‘1代表記錄有效，0代表記錄無效’,
PRIMARY KEY (id),
UNIQUE KEY uniq_stu_num (stu_num),
KEY idx_stu_score (stu_score),
KEY idx_update_time_tuition (update_time, tuition)
) ENGINE = INNODB charset = utf8mb4 COMMENT ‘學生資訊表’;
3 SQL語句規範
3.1、避免隱式轉換；
隱式轉換將使用不了索引。
3.2、盡量不使用select *,只select需要的欄位 ；
原因：讀取不需要的欄位會增加CPU、IO、NET消耗，並且無法有效的利用覆蓋索引。使用SELECT *容易在增加或刪除欄位後導致程式報錯。
3.3.禁止代碼中使用INSERT INTO t_xxx VALUES (xxx)，必須顯示指定插入的列名 ；
原因：容易在增加或刪除欄位後導致程式報錯。
3.4、盡量不使用負向查詢；
如 not in/like。
3.5、禁止以%開頭的模糊查詢；
原因：使用不了索引
3.6.禁止單一SQL語句同時更新多個表；
同時更新多個表的SQL語句可能會變得非常複雜，難以理解和維護。
當出現問題時，排除和修復錯誤也會更加困難。
3.7.統計記錄數使用select count(*)
而非select count(primary_key)或select count(普通欄位名稱)；
原因：可能會導致走的索引不是最優的或導致統計數字不準確。
3.8、建議將子查詢轉換為關聯查詢；
關聯查詢通常比子查詢執行速度更快。
子查詢會在內部執行多次，而關聯查詢會以更有效的方式一次檢索所需的資料。這可以減少資料庫的負載，提高查詢效能。
3.9、建議應用程式捕獲SQL異常，並有相應處理；
這樣，可以大大提升出錯時的檢查速度
3.10、SQL中不建議使用 sleep()，如特殊需求需要用到sleep()，請事先告知DBA；
可能佔用資料庫連線和資源，並且可能會在高負載的情況下導致效能下降
4 行為規範
4.1、批次匯入、匯出資料必須事先通知DBA協助觀察；
操作前，可以一起看語句是否可以最佳化，還有就是DBA可以暫時關閉一些定時任務（例如備份），方便加快批次匯入匯出。
另外導入導出過程，可以讓DBA專注於資料庫效能。
4.2、有可能導致MySQL QPS上升的活動，提前告知DBA；
DBA可以評估資料庫是否會到瓶頸，或是有些細節是否可以再調整；活動期間，DBA也會觀察資料庫監控，看資料庫是否有效能問題。
4.3、同一張表的多個alter合成一次操作；
例如加三個字段，有些人會執行三條語句：
ALTER TABLE your_table_name ADD COLUMN new_column1 INT;
ALTER TABLE your_table_name ADD COLUMN new_column2 INT;
ALTER TABLE your_table_name ADD COLUMN new_column3 INT;
實際一條語句可以執行：
ALTER TABLE your_table_name
ADD COLUMN new_column1 INT,
ADD COLUMN new_column2 INT,
ADD COLUMN new_column3 INT;
4.4、不在業務高峰期批量更新、查詢資料庫；
避免影響正常業務
4.5、刪除表或庫要求盡量先rename，觀察幾天，確定對業務沒影響，再drop。
這樣，防止一些我們不知道的業務還在使用這些庫或表的情況。
Redis規範
1 鍵值對的建議
1.1、key名建議；
建議key name設計：業務名:表名:id
比如：
set school:student:1 martin
要求：不包含特殊字符
1.2、string 類型控制在10 KB以內，hash、list、set、zset元素個數不要超過5000；
1.3、控制key的過期時間；
使用expire設定key的過期時間，防止Redis中殘留大量廢棄的數據，Redis不是垃圾桶，記憶體很貴的。
當然，同一個Redis裡面的key過期時間也盡量錯開，集中過期，可能會導致緩存雪崩
2 禁用一些高風險指令
2.1、危險命令直接禁用；
比如：
keys *
flushall
flushdb
等。
當然，可以考慮直接在配置中停用這些命令
在設定檔中增加
rename-command flushall “”
rename-comman
d flushdb “”
rename-command keys “”
或創建一個ACL用戶
ACL SETUSER martin on >martin123 +@all -@dangerous ~*
解釋
+@all 所有的命令
-@dangerous 表示禁用危險指令
~* 表示授權所有的key
dangerous危險指令包括：FLUSHALL, MIGRATE, RESTORE, SORT, KEYS, CLIENT, DEBUG, INFO, CONFIG, SAVE, REPLICAOF
2.2、盡量不全量操作Hash、Set等集合結構；
如果單一集合結構裡有多個元素，單次操作過多的元素，效率可能會很低，並且可能會填滿網卡流量。
3 設計規範
3.1、不同場景合理使用不同的資料結構；
例如排行榜可以使用zset；
地理位置，可以用GEO；
隊列可以用list；
計數器可以用string類型。
3.2、不要將所有的資料放Redis；
建議Redis只做熱資料緩存，冷資料放到MySQL或其他資料庫。
一方面，記憶體是比較貴的，可以節省成本；
另一方面，放到關係型資料庫中，資料很難遺失。
MongoDB的規範
1 庫名
1.1、庫名全小寫；
1.2、不能包括除_以外其他的一些特殊字元；
1.3、庫名不超過30個字元。
2 集合名命規範
2.1、使用小寫字母；
2.2、不能包括除_以外其他的一些特殊字元；
2.3、不能以system.開頭，因為這種是系統表；
2.4、建議是有業務意義的單字組合，用底線連接，例如：shipping_orders；
2.5、集合名不超過30個字串。
3 文檔規範
3.1、文檔的鍵值不能包括除_以外其他的一些特殊字元；
3.2、禁止使用_id，因為這個是預設的主鍵；
3.3、一個集合中盡可能使用相同類型的文檔；
3.4、經常作為條件的欄位添加索引。
4 語句
4.1、單條語句避免查詢過多的資料；
4.2、單一文檔的大小建議不超過16M；
4.3、停用不帶條件的查詢和更新；
4.4.每次查詢的文檔數建議不超過500。
5 清空集群建議
建議用drop()，而不是deleteMany()。