2021-05-29 v1.0.1
目錄
1. 規(guī)范背景與目的
MySQL 數據庫與 Oracle主之、 SQL Server 等數據庫相比,有其內核上的優(yōu)勢與劣勢。我們在使用 MySQL 數據庫的時候需要遵循一定規(guī)范,揚長避短。
本規(guī)范旨在幫助或指導技術人員做出適合線上業(yè)務的數據庫設計腌逢。在數據庫變更和處理流程、數據庫表設計超埋、SQL 編寫等方面予以規(guī)范搏讶,從而為業(yè)務系統(tǒng)穩(wěn)定、健康地運行提供保障霍殴。
2. 設計規(guī)范
2.1 數據庫設計
以下所有規(guī)范會按照【強制】媒惕、【建議】兩個級別進行標注。
2.1.1 一般命名規(guī)則
- 【強制】使用小寫繁成,避免因大小寫敏感而導致的錯誤吓笙。
- 【強制】沒有空格淑玫,使用下劃線代替巾腕。
- 【強制】名稱中沒有數字,只有英文字母絮蒿,單詞之間用下劃線分隔尊搬。
- 【強制】有效的可理解的名稱,禁止拼音英文混用土涝,庫名最好與應用名稱一致佛寿。
- 【強制】名稱應該是自我解釋的。
- 【強制】名稱不應超過 32 個字符但壮。
- 【強制】使用前綴冀泻,便于業(yè)務分類。
2.1.2 庫
- 【強制】遵守以上全部一般命名規(guī)則蜡饵。
- 【強制】使用單數命名弹渔。
- 【強制】庫的名稱格式:業(yè)務系統(tǒng)名稱_子系統(tǒng)名。
- 【強制】一般分庫名稱命名格式是
庫通配名_編號
溯祸,編號從 0 開始遞增肢专,比如northwind_001
,以時間進行分庫的名稱格式是庫通配名_時間
焦辅。 - 【強制】開發(fā)環(huán)境庫的名稱增加后綴
_dev
博杖,測試環(huán)境增加后綴_test
。 - 【強制】創(chuàng)建數據庫時必須顯式指定字符集筷登,并且字符集只能是
utf8
或utf8mb4
剃根,優(yōu)先使用utf8mb4
。
2.1.3 表
- 【強制】遵守以上全部一般命名規(guī)則前方。
- 【強制】使用單數命名跟继。
- 【強制】表的名稱格式:業(yè)務模塊名稱_表名种冬。視圖增加前綴
v_
。 - 【強制】每個表建議不超過 30-50 個字段舔糖。
- 【強制】相關模塊的表名與表名之間盡量體現 join 的關系娱两,如
user
表和user_login
表。 - 【強制】創(chuàng)建表時必須顯式指定字符集為
utf8
或utf8mb4
金吗,優(yōu)先使用utf8mb4
十兢。 - 【強制】創(chuàng)建表時必須顯式指定表存儲引擎類型,如無特殊需求摇庙,一律為 InnoDB旱物。當需要使用除 InnoDB/MyISAM/Memory 以外的存儲引擎時,必須通過 DBA 審核才能在生產環(huán)境中使用卫袒。因為 InnoDB 表支持事務宵呛、行鎖、宕機恢復夕凝、MVCC 等關系型數據庫重要特性宝穗,為業(yè)界使用最多的 MySQL 存儲引擎。而這是其它大多數存儲引擎不具備的码秉,因此首推 InnoDB逮矛。
- 【強制】建表必須有 comment。
- 【建議】關于主鍵:每個表必須有主鍵转砖。(1) 類型為
bigint
须鼎,使用 snowflake 雪花算法生成,禁止使用自增數值主鍵府蔗;(2) 命名格式是表名_id
晋控,例如user_id
,order_id
姓赤,禁止使用id
命名赡译,多表關聯(lián)時容易產生混淆。 - 【建議】核心表(如用戶表模捂,金錢相關的表)必須有行數據的創(chuàng)建人捶朵、創(chuàng)建時間、修改人狂男、修改時間字段
create_userid
综看、create_time
、update_userid
岖食、update_time
红碑,便于排查問題。 - 【建議】表中所有字段必須都是
NOT NULL
屬性,業(yè)務可以根據需要定義DEFAULT
值析珊。因為使用NULL
值會存在每一行都會占用額外存儲空間羡鸥、數據遷移容易出錯、聚合函數計算結果偏差等問題忠寻。 - 【建議】建議對表里的
blob
惧浴、text
等大字段,垂直拆分到其它表里奕剃,僅在需要讀這些對象的時候才去 select衷旅。 - 【建議】反范式設計:把經常需要 join 查詢的字段,在其它表里冗余一份纵朋。如
username
屬性在user_account
柿顶,user_login_log
等表里冗余一份,減少 join 查詢操软。 - 【強制】中間表用于保留中間結果集嘁锯,名稱必須以
tmp_
開頭。備份表用于備份或抓取源表快照聂薪,名稱必須以bak_
開頭家乘。中間表和備份表定期清理。 - 【強制】對于超過 100 萬行的大表進行
alter table
胆建,必須經過 DBA 審核烤低,并在業(yè)務低峰期執(zhí)行肘交。因為alter table
會產生表鎖笆载,期間阻塞對于該表的所有寫入,對于業(yè)務可能會產生極大影響涯呻。 - 【建議】盡量控制單表數據量的大小凉驻,建議控制在 500 萬以內。500 萬并不是 MySQL 數據庫的限制复罐,數據量過大會對修改表結構涝登,數據備份、恢復產生影響效诅。
- 【強制】禁止使用存儲過程胀滚、觸發(fā)器、Event乱投。
- 【建議】建議不使用視圖咽笼。
- 【強制】禁止嵌套視圖。
- 【強制】禁止存儲大文件或者大照片戚炫。
2.1.4 字段
- 【強制】遵守以上全部一般命名規(guī)則剑刑。
- 【建議】盡可能選擇短的或一兩個單詞。
- 【強制】避免使用保留字作為字段名稱:
order
,date
施掏,name
是數據庫的保留字钮惠,避免使用它∑甙牛可以為這些名稱添加前綴使其易于理解素挽,如user_name
,signup_date
等狸驳。 - 【強制】避免使用與表名相同的字段名毁菱,這會在編寫查詢時造成混淆。
- 【建議】字符集和庫級保持一致锌历。不單獨定義字段字符集贮庞。
- 【建議】反范式設計:為了提高數據更新性能,禁止使用外鍵究西。如果有外鍵完整性約束窗慎,需要應用程序控制。外鍵會導致表與表之間耦合卤材,update 與 delete 操作都會涉及相關聯(lián)的表遮斥,十分影響性能,甚至會造成死鎖扇丛。高并發(fā)情況下容易造成數據庫性能术吗,大數據高并發(fā)業(yè)務場景數據庫使用以性能優(yōu)先。
- 【強制】嚴禁在數據庫中明文存儲用戶密碼帆精、身份證较屿、信用卡號(信用卡PIN碼)等核心機密數據。
- 【強制】一個字段僅允許表達一種業(yè)務含義卓练,例如通過員工職級反映薪資等級是不合適的隘蝎。
- 【強制】多表中的相同列,必須保證列名一致襟企,數據類型一致嘱么。
2.1.5 字段數據類型優(yōu)化
- 【建議】文本數據盡量用
varchar
存儲。因為varchar
是變長存儲顽悼,比char
更省空間曼振。字符數不要超過 2700。 - 【建議】時間類型盡量選取
datetime
蔚龙。 - 【建議】業(yè)務中選擇性很少的狀態(tài)
status
冰评、類型type
等字段推薦使用smallint
類型節(jié)省存儲空間。 - 【建議】金額貨幣科學計數建議采用
decimal
數據類型府蛇。 - 【建議】禁止使用數據庫私有數據類型集索,例如
enum
,set
,不利于數據庫遷移务荆。
-
詳細存儲大小參考下圖:
類型(同義詞) 存儲長度(BYTES) 最小值(SIGNED/UNSIGNED) 最大值(SIGNED/UNSIGNED) 整形數字 TINYINT 1 -128/0 127/255 SMALLINT 2 -32,768/0 32767/65,535 MEDIUMINT 3 -8,388,608/0 8388607/16,777,215/ INT(INTEGER) 4 -2,14,7483,648/0 2147483647/4,294,967,295/ BIGINT 8 -2^63/0 263-1/264-1 小數支持 FLOAT[(M[,D])] 4 or 8 - DOUBLE[(M[,D])]
(REAL, DOUBLE PRECISION)8 - 時間類型 DATETIME 8 1001-01-01 00:00:00 9999-12-31 23:59:59 DATE 3 1001-01-01 9999-12-31 TIME 3 00:00:00 23:59:59 YEAR 1 1001 9999 TIMESTAMP 4 1970-01-01 00:00:00
2.1.6 索引設計
- 【強制】主鍵類型為
int/bigint
妆距,且主鍵值禁止被更新。 - 【建議】主鍵索引的名稱以
pk_
開頭函匕,唯一鍵以uk_
開頭娱据,普通索引以idx_
開頭,一律使用小寫格式盅惜,以表名_字段的名稱或縮寫
作為后綴中剩。 - 【強制】InnoDB 和 MyISAM 存儲引擎表,索引類型必須為
BTREE
抒寂;MEMORY 表可以根據需要選擇HASH
或者BTREE
類型索引结啼。 - 【強制】單個索引中每個索引記錄的長度不能超過 64KB。
- 【建議】單個表上的索引個數不能超過 5 個屈芜。
- 【建議】在建立索引時郊愧,多考慮建立聯(lián)合索引,并把區(qū)分度最高的字段放在最前面井佑。如列
user_id
的區(qū)分度可由select count(distinct user_id)
計算出來属铁。 - 【建議】在多表 join 的 SQL 里,保證被驅動表的連接列上有索引躬翁,這樣 join 執(zhí)行效率最高焦蘑。
- 【建議】建表或加索引時,保證表里互相不存在冗余索引盒发。如果表里已經存在
key(a, b)
例嘱,則key(a)
為冗余索引,需要刪除迹辐。 - 【建議】如果選擇性超過 20%蝶防,那么全表掃描比使用索引性能更優(yōu)甚侣,即沒有設置索引的必要明吩。
2.1.7 分庫分表、分區(qū)表
- 【強制】分區(qū)表的分區(qū)字段(
partition-key
)必須有索引殷费,或者是組合索引的首列印荔。 - 【強制】單個分區(qū)表中的分區(qū)(包括子分區(qū))個數不能超過 1024。
- 【強制】上線前 DBA 必須指定分區(qū)表的創(chuàng)建详羡、清理策略仍律。
- 【強制】訪問分區(qū)表的 SQL 必須包含分區(qū)鍵。
- 【建議】單個分區(qū)文件不超過 2G实柠,總大小不超過 50G水泉。建議總分區(qū)數不超過 20 個。
- 【強制】對于分區(qū)表執(zhí)行
alter table
操作,必須在業(yè)務低峰期執(zhí)行草则。 - 【強制】采用分庫策略的钢拧,庫的數量不能超過 1024。
- 【強制】采用分表策略的炕横,表的數量不能超過 4096源内。
- 【建議】單個分表不超過 500 萬行,ibd 文件大小不超過 2G份殿,這樣才能讓數據分布式變得性能更佳膜钓。
10.【建議】水平分表盡量用取模方式,日志卿嘲、報表類數據建議采用日期進行分表颂斜。
2.1.8 字符集
- 【強制】數據庫本身庫、表拾枣、列所有字符集必須保持一致焚鲜,為
utf8
或utf8mb4
,優(yōu)先使用utf8mb4
放前。 - 【強制】前端程序字符集或者環(huán)境變量中的字符集忿磅,與數據庫、表的字符集必須一致凭语,統(tǒng)一為
utf8
葱她。
2.1.9 程序層 DAO 設計建議
- 【強制】SQL 使用綁定變量傳入參數的方式,避免 SQL 注入似扔。
- 【建議】前端程序連接數據源吨些,必須要有連接超時和失敗重連機制,且失敗重試必須有間隔時間炒辉。
- 【建議】前端程序報錯里盡量能夠提示原生態(tài)的報錯信息豪墅,便于排查錯誤。
- 【建議】對于有連接池的前端程序黔寇,必須根據業(yè)務需要配置初始偶器、最小、最大連接數缝裤,超時時間以及連接回收機制屏轰,否則會耗盡數據庫連接資源,造成線上事故憋飞。
- 【建議】對于
log
或history
類型的表霎苗,隨時間增長容易越來越大,因此上線前 DBA 必須建立表數據清理或歸檔方案榛做。 - 【建議】在應用程序設計階段唁盏,必須考慮并規(guī)避數據庫中主從延遲對于業(yè)務的影響内狸。盡量避免從庫短時延遲(20 秒以內)對業(yè)務造成影響,建議強制一致性的讀開啟事務走主庫厘擂,或更新后過一段時間再去讀從庫答倡。
- 【建議】多個并發(fā)業(yè)務邏輯訪問同一塊數據(InnoDB 表)時,會在數據庫端產生行鎖甚至表鎖導致并發(fā)下降驴党,因此建議更新類 SQL 盡量基于主鍵去更新瘪撇。
- 【建議】業(yè)務邏輯之間加鎖順序盡量保持一致,否則會導致死鎖港庄。
- 【建議】對于單表讀寫比大于 10:1 的數據行或單個列倔既,可以將熱點數據放在緩存里(如 Memcached 或 Redis),加快訪問速度鹏氧,降低數據庫壓力渤涌。
2.1.10 一個規(guī)范的建表語句示例
- 一個較為規(guī)范的建表語句為:
create table user ( `id` bigint(11) not null, `user_id` bigint(11) not null comment '用戶 ID', `username` varchar(45) not null comment '登錄名', `email` varchar(30) not null comment '郵箱', `nickname` varchar(45) not null comment '昵稱', `avatar` int(11) not null comment '頭像', `birthday` date not null comment '生日', `gender` tinyint(4) default '0' comment '性別', `intro` varchar(150) default null comment '簡介', `resume_url` varchar(300) not null comment '簡歷存放地址', `register_ip` int not null comment '用戶注冊時的源 IP', `review_status` tinyint not null comment '審核狀態(tài),1-通過把还,2-審核中实蓬,3-未通過,4-尚未提交審核', `create_time` timestamp not null comment '記錄創(chuàng)建的時間', `update_time` timestamp not null comment '資料修改的時間', primary key (`id`), unique key `idx_user_id` (`user_id`), key `idx_username`(`username`), key `idx_create_time`(`create_time`, `review_status`) ) engine = InnoDB default charset = utf8 comment = '用戶基本信息';
2.2 SQL 編寫
2.2.1 DML 語句
- 【強制】select 語句必須指定具體字段名稱吊履,禁止寫成
*
安皱。因為select *
會將不該讀的數據也從 MySQL 里讀出來,造成網卡壓力艇炎。 - 【強制】insert 語句指定具體字段名稱酌伊,不要寫成
insert into t1 values(…)
,道理同上缀踪。 - 【建議】
insert into … values(xx),(xx),(xx)…
居砖,這里 xx 的值不要超過 5000 個。值過多雖然上線很快驴娃,但會引起主從同步延遲奏候。 - 【建議】select 語句不要使用
union
,推薦使用union all
唇敞,并且union
子句個數限制在 5 個以內蔗草。因為union all
不需要去重,節(jié)省數據庫資源厚棵,提高性能蕉世。 - 【建議】in 值列表限制在 500 以內。例如
select … where user_id in(…500 個以內…)
婆硬,這么做是為了減少底層掃描,減輕數據庫壓力從而加速查詢奸例。 - 【建議】事務里批量更新數據需要控制數量彬犯,進行必要的 sleep向楼,做到少量多次。
- 【強制】事務涉及的表必須全部是 InnoDB 表谐区。否則一旦失敗不會全部回滾湖蜕,且易造成主從庫同步終端。
- 【強制】寫入和事務發(fā)往主庫宋列,只讀 SQL 發(fā)往從庫昭抒。
- 【強制】除靜態(tài)表或小表(100 行以內),dml 語句必須有 where 條件炼杖,且使用索引查找灭返。
- 【強制】生產環(huán)境禁止使用
hint
,如sql_no_cache
坤邪,force index
熙含,ignore key
,straight join
等艇纺。因為hint
是用來強制 sql 按照某個執(zhí)行計劃來執(zhí)行怎静,但隨著數據量變化我們無法保證自己當初的預判是正確的,因此我們要相信 MySQL 優(yōu)化器黔衡。 - 【強制】where 條件里等號左右字段類型必須一致蚓聘,否則無法利用索引。
- 【建議】
select|update|delete|replace
要有 where 子句盟劫,且 where 子句的條件必需使用索引查找或粮。 - 【強制】生產數據庫中強烈不推薦大表上發(fā)生全表掃描,但對于 100 行以下的靜態(tài)表可以全表掃描捞高。查詢數據量不要超過表行數的 25%氯材,否則不會利用索引。
- 【強制】where 子句中禁止只使用全模糊的 like 條件進行查找硝岗,必須有其它等值或范圍查詢條件氢哮,否則無法利用索引。
- 【建議】索引列不要使用函數或表達式型檀,否則無法利用索引冗尤。如
where length(name) = 'admin'
或where user_id + 2 = 10023
。 - 【建議】減少使用 or 語句胀溺,可將 or 語句優(yōu)化為 union裂七,然后在各個 where 條件上建立索引。如
where a = 1 or b = 2
優(yōu)化為where a = 1 … union … where b = 2, key(a), key(b)
仓坞。 - 【建議】分頁查詢背零,當
limit
起點較高時,可先用過濾條件進行過濾无埃。如select a, b, c from t1 limit 10000, 20;
優(yōu)化為:select a, b, c from t1 where id > 10000 limit 20;
徙瓶。
2.2.2 多表連接
- 【強制】禁止跨 DB 的 join 語句毛雇。因為這樣可以減少模塊間耦合,為數據庫拆分奠定堅實基礎侦镇。
- 【強制】禁止在業(yè)務的更新類 SQL 語句中使用 join灵疮,比如
update t1 join t2 …
。 - 【建議】不建議使用子查詢壳繁,建議將子查詢 SQL 拆開結合程序多次查詢震捣,或使用 join 來代替子查詢。
- 【建議】線上環(huán)境闹炉,多表 join 不要超過 3 個表蒿赢。
- 【建議】多表連接查詢推薦使用別名,且 select 列表中要用別名引用字段剩胁,數據庫.表格式诉植,如
select a from db1.table1 alias1 where …
。 - 【建議】在多表 join 中昵观,盡量選取結果集較小的表作為驅動表晾腔,來 join 其它表。
2.2.3 事務
- 【建議】事務中
insert|update|delete|replace
語句操作的行數控制在 2000 以內啊犬,以及 where 子句中 in 列表的傳參個數控制在 500 以內灼擂。 - 【建議】批量操作數據時,需要控制事務處理間隔時間觉至,進行必要的 sleep剔应,一般建議值 5-10 秒。
- 【建議】對于有
auto_increment
屬性字段的表的插入操作语御,并發(fā)需要控制在 200 以內峻贮。 - 【強制】程序設計必須考慮“數據庫事務隔離級別”帶來的影響,包括臟讀应闯、不可重復讀和幻讀纤控。線上建議事務隔離級別為
repeatable-read
。 - 【建議】事務里包含 SQL 不超過 5 個(支付業(yè)務除外)碉纺。因為過長的事務會導致鎖數據較久船万,MySQL 內部緩存、連接消耗過多等雪崩問題骨田。
- 【建議】事務里更新語句盡量基于主鍵或
unique key
耿导,如update … where id = XX;
,否則會產生間隙鎖态贤,內部擴大鎖定范圍舱呻,導致系統(tǒng)性能下降,產生死鎖抵卫。 - 【建議】盡量把一些典型外部調用移出事務狮荔,如調用 Web Service胎撇,訪問文件存儲等介粘,從而避免事務過長殖氏。
- 【建議】對于 MySQL 主從延遲嚴格敏感的 select 語句,請開啟事務強制訪問主庫姻采。
2.2.4 排序和分組
- 【建議】減少使用
order by
雅采,和業(yè)務溝通能不排序就不排序,或將排序放到程序端去做慨亲。order by
婚瓜、group by
、distinct
這些語句較為耗費 CPU刑棵,數據庫的 CPU 資源是極其寶貴的巴刻。 - 【建議】
order by
、group by
蛉签、distinct
這些 SQL 盡量利用索引直接檢索出排序好的數據胡陪。如where a = 1 order by
可以利用key(a, b)
。 - 【建議】包含了
order by
碍舍、group by
柠座、distinct
這些查詢的語句,where 條件過濾出來的結果集請保持在 1000 行以內片橡,否則 SQL 會很慢妈经。
2.2.5 線上禁止使用的 SQL 語句
- 【強制】禁用
update|delete t1 … where a = XX limit XX;
這種帶 limit 的更新語句。因為會導致主從不一致捧书,導致數據錯亂吹泡。建議加上order by PK
。 - 【強制】禁止使用關聯(lián)子查詢经瓷,如
update t1 set … where name in(select name from user where …);
爆哑,效率極其低下。 - 【強制】禁用 procedure了嚎、function泪漂、trigger、views歪泳、event萝勤、外鍵約束。因為他們消耗數據庫資源呐伞,降低數據庫實例可擴展性敌卓。推薦都在程序端實現。
- 【強制】禁用
insert into … on duplicate key update …
在高并發(fā)環(huán)境下伶氢,會造成主從不一致趟径。 - 【強制】禁止聯(lián)表更新語句瘪吏,如
update t1, t2 where t1.id = t2.id …
。
3. MySQL 使用約束及建議
3.1. 關于性能的爭論
最佳實踐通常是為了獲取最佳性能蜗巧,例如一個支付環(huán)境掌眠,每秒只需要 3000 個事務就夠了,更看重一致性幕屹。
所以對于性能蓝丙,關建是要明確一下定位,然后再跟據自已的情況來判到底使用那些技術組合望拖。
讀寫分離:對于非特殊要求的業(yè)務可以考慮部分 SQL 或是全量讀 SQL 進行讀寫分離操作渺尘,從而增加 DB 的處理能力。
關于分庫分表:分庫分表屬于已經在 DB 的設計上無路可走说敏,已經達到了前所未有的業(yè)務壓力下才去走的一個技能鸥跟。
通過常情況可以考慮優(yōu)先進行硬件層面優(yōu)化、SQL 簡化兩個方向的工作盔沫,最后再來考慮分庫分表医咨,減少業(yè)務的復雜度。
建議
簡單的使用 MySQL迅诬;
計算機在處理整數比較浮點數快 N 倍腋逆,慎用浮點數;
盡量不要在DB里做運算侈贷,大的計算可以考慮中間件完成惩歉;
數據庫拆分中適合冗余;
單庫容量:SAS 盤 Raid 10 控制在 500G 左右俏蛮,PCI-E 卡撑蚌,可以控制在卡的大小 80% 左右。日志或是歷史庫除外搏屑。
3.2. MySQL 特點
MySQL 是單進程多線程争涌,不像 Oracle 是多進程的;
每個 MySQL 內部線程同時只能用到一個邏輯 CPU 線程辣恋;
每個 SQL 同時只能用到一個邏輯 CPU 線程亮垫;
無執(zhí)行計劃緩存(無類似 ORACLE 的 library cache),不過 MySQL 的執(zhí)行計劃解析比較輕量級伟骨,效率還不錯歉摧,這方面不會是瓶頸松邪;
query cache 的更新需要持有全局 mutex吴裤,數據有任何更新都需要等待該 mutex 效率低诗充,且整個表的 query cache 也會失效,因此強烈建議關閉 query cache
沒有 thread pool 時,如果有瞬間大量連接請求稀并,性能會急劇下降仅颇。