MySQL數(shù)據(jù)庫與 Oracle、 SQL Server 等數(shù)據(jù)庫相比，有其內(nèi)核上的優(yōu)勢與劣勢。我們在使用MySQL數(shù)據(jù)庫的時候需要遵循一定規(guī)范，揚(yáng)長避短。無意中從github上看到一個大佬的MySQL數(shù)據(jù)庫設(shè)計規(guī)范，順便在這里分享一下。

https://github.com/jly8866/archer/blob/master/src/docs/mysql_db_design_guide.md

寫的都很實用，建議收藏閱讀。

一、數(shù)據(jù)庫設(shè)計

以下所有規(guī)范會按照【高?！?、【強(qiáng)制】、【建議】三個級別進(jìn)行標(biāo)注，遵守優(yōu)先級從高到低。對于不滿足【高?！亢汀緩?qiáng)制】兩個級別的設(shè)計，DBA會強(qiáng)制打回要求修改。

1. 庫名

• 【強(qiáng)制】庫的名稱必須控制在32個字符以內(nèi)，相關(guān)模塊的表名與表名之間盡量提現(xiàn)join的關(guān)系，如user表和user_login表。
• 【強(qiáng)制】庫的名稱格式：業(yè)務(wù)系統(tǒng)名稱_子系統(tǒng)名，同一模塊使用的表名盡量使用統(tǒng)一前綴。
• 【強(qiáng)制】一般分庫名稱命名格式是庫通配名_編號，編號從0開始遞增，比如wenda_001以時間進(jìn)行分庫的名稱格式是“庫通配名_時間”
• 【強(qiáng)制】創(chuàng)建數(shù)據(jù)庫時必須顯式指定字符集，并且字符集只能是utf8或者utf8mb4。創(chuàng)建數(shù)據(jù)庫SQL舉例：create database db1 default character set utf8;。

2. 表結(jié)構(gòu)

• 【強(qiáng)制】表和列的名稱必須控制在32個字符以內(nèi)，表名只能使用字母、數(shù)字和下劃線，一律小寫。
• 【強(qiáng)制】表名要求模塊名強(qiáng)相關(guān)，如師資系統(tǒng)采用”sz”作為前綴，渠道系統(tǒng)采用”qd”作為前綴等。
• 【強(qiáng)制】創(chuàng)建表時必須顯式指定字符集為utf8或utf8mb4。
• 【強(qiáng)制】創(chuàng)建表時必須顯式指定表存儲引擎類型，如無特殊需求，一律為InnoDB。當(dāng)需要使用除InnoDB/MyISAM/Memory以外的存儲引擎時，必須通過DBA審核才能在生產(chǎn)環(huán)境中使用。因為Innodb表支持事務(wù)、行鎖、宕機(jī)恢復(fù)、MVCC等關(guān)系型數(shù)據(jù)庫重要特性，為業(yè)界使用最多的MySQL存儲引擎。而這是其他大多數(shù)存儲引擎不具備的，因此首推InnoDB。
• 【強(qiáng)制】建表必須有comment
• 【建議】建表時關(guān)于主鍵：(1)強(qiáng)制要求主鍵為id，類型為int或bigint，且為auto_increment(2)標(biāo)識表里每一行主體的字段不要設(shè)為主鍵，建議設(shè)為其他字段如user_id，order_id等，并建立unique key索引(可參考cdb.teacher表設(shè)計)。因為如果設(shè)為主鍵且主鍵值為隨機(jī)插入，則會導(dǎo)致innodb內(nèi)部page分裂和大量隨機(jī)I/O，性能下降。
• 【建議】核心表(如用戶表，金錢相關(guān)的表)必須有行數(shù)據(jù)的創(chuàng)建時間字段create_time和最后更新時間字段update_time，便于查問題。
• 【建議】表中所有字段必須都是NOT NULL屬性，業(yè)務(wù)可以根據(jù)需要定義DEFAULT值。因為使用NULL值會存在每一行都會占用額外存儲空間、數(shù)據(jù)遷移容易出錯、聚合函數(shù)計算結(jié)果偏差等問題。
• 【建議】建議對表里的blob、text等大字段，垂直拆分到其他表里，僅在需要讀這些對象的時候才去select。
• 【建議】反范式設(shè)計：把經(jīng)常需要join查詢的字段，在其他表里冗余一份。如user_name屬性在user_account，user_login_log等表里冗余一份，減少join查詢。
• 【強(qiáng)制】中間表用于保留中間結(jié)果集，名稱必須以tmp_開頭。備份表用于備份或抓取源表快照，名稱必須以bak_開頭。中間表和備份表定期清理。
• 【強(qiáng)制】對于超過100W行的大表進(jìn)行alter table，必須經(jīng)過DBA審核，并在業(yè)務(wù)低峰期執(zhí)行。因為alter table會產(chǎn)生表鎖，期間阻塞對于該表的所有寫入，對于業(yè)務(wù)可能會產(chǎn)生極大影響。

3. 列數(shù)據(jù)類型優(yōu)化

• 【建議】表中的自增列(auto_increment屬性)，推薦使用bigint類型。因為無符號int存儲范圍為-2147483648~2147483647(大約21億左右)，溢出后會導(dǎo)致報錯。
• 【建議】業(yè)務(wù)中選擇性很少的狀態(tài)status、類型type等字段推薦使用tinytint或者smallint類型節(jié)省存儲空間。
• 【建議】業(yè)務(wù)中IP地址字段推薦使用int類型，不推薦用char(15)。因為int只占4字節(jié)，可以用如下函數(shù)相互轉(zhuǎn)換，而char(15)占用至少15字節(jié)。一旦表數(shù)據(jù)行數(shù)到了1億，那么要多用1.1G存儲空間。 SQL：select inet_aton('192.168.2.12'); select inet_ntoa(3232236044); PHP: ip2long(‘192.168.2.12’); long2ip(3530427185);
• 【建議】不推薦使用enum，set。 因為它們浪費空間，且枚舉值寫死了，變更不方便。推薦使用tinyint或smallint。
• 【建議】不推薦使用blob，text等類型。它們都比較浪費硬盤和內(nèi)存空間。在加載表數(shù)據(jù)時，會讀取大字段到內(nèi)存里從而浪費內(nèi)存空間，影響系統(tǒng)性能。建議和PM、RD溝通，是否真的需要這么大字段。Innodb中當(dāng)一行記錄超過8098字節(jié)時，會將該記錄中選取最長的一個字段將其768字節(jié)放在原始page里，該字段余下內(nèi)容放在overflow-page里。不幸的是在compact行格式下，原始page和overflow-page都會加載。
• 【建議】存儲金錢的字段，建議用int，程序端乘以100和除以100進(jìn)行存取。因為int占用4字節(jié)，而double占用8字節(jié)，空間浪費。
• 【建議】文本數(shù)據(jù)盡量用varchar存儲。因為varchar是變長存儲，比char更省空間。MySQL server層規(guī)定一行所有文本最多存65535字節(jié)，因此在utf8字符集下最多存21844個字符，超過會自動轉(zhuǎn)換為mediumtext字段。而text在utf8字符集下最多存21844個字符，mediumtext最多存2^24/3個字符，longtext最多存2^32個字符。一般建議用varchar類型，字符數(shù)不要超過2700。
• 【建議】時間類型盡量選取timestamp。因為datetime占用8字節(jié)，timestamp僅占用4字節(jié)，但是范圍為1970-01-01 00:00:01到2038-01-01 00:00:00。更為高階的方法，選用int來存儲時間，使用SQL函數(shù)unix_timestamp()和from_unixtime()來進(jìn)行轉(zhuǎn)換。

詳細(xì)存儲大小參加下圖：

4. 索引設(shè)計

• 【強(qiáng)制】InnoDB表必須主鍵為id int/bigint auto_increment,且主鍵值禁止被更新。
• 【建議】主鍵的名稱以“pk_”開頭，唯一鍵以“uk_”或“uq_”開頭，普通索引以“idx_”開頭，一律使用小寫格式，以表名/字段的名稱或縮寫作為后綴。
• 【強(qiáng)制】InnoDB和MyISAM存儲引擎表，索引類型必須為BTREE;MEMORY表可以根據(jù)需要選擇HASH或者BTREE類型索引。
• 【強(qiáng)制】單個索引中每個索引記錄的長度不能超過64KB。
• 【建議】單個表上的索引個數(shù)不能超過7個。
• 【建議】在建立索引時，多考慮建立聯(lián)合索引，并把區(qū)分度最高的字段放在最前面。如列userid的區(qū)分度可由select count(distinct userid)計算出來。
• 【建議】在多表join的SQL里，保證被驅(qū)動表的連接列上有索引，這樣join執(zhí)行效率最高。
• 【建議】建表或加索引時，保證表里互相不存在冗余索引。對于MySQL來說，如果表里已經(jīng)存在key(a,b)，則key(a)為冗余索引，需要刪除。

5. 分庫分表、分區(qū)表

• 【強(qiáng)制】分區(qū)表的分區(qū)字段(partition-key)必須有索引，或者是組合索引的首列。
• 【強(qiáng)制】單個分區(qū)表中的分區(qū)(包括子分區(qū))個數(shù)不能超過1024。
• 【強(qiáng)制】上線前RD或者DBA必須指定分區(qū)表的創(chuàng)建、清理策略。
• 【強(qiáng)制】訪問分區(qū)表的SQL必須包含分區(qū)鍵。
• 【建議】單個分區(qū)文件不超過2G，總大小不超過50G。建議總分區(qū)數(shù)不超過20個。
• 【強(qiáng)制】對于分區(qū)表執(zhí)行alter table操作，必須在業(yè)務(wù)低峰期執(zhí)行。
• 【強(qiáng)制】采用分庫策略的，庫的數(shù)量不能超過1024
• 【強(qiáng)制】采用分表策略的，表的數(shù)量不能超過4096
• 【建議】單個分表不超過500W行，ibd文件大小不超過2G，這樣才能讓數(shù)據(jù)分布式變得性能更佳。
• 【建議】水平分表盡量用取模方式，日志、報表類數(shù)據(jù)建議采用日期進(jìn)行分表。

6. 字符集

• 【強(qiáng)制】數(shù)據(jù)庫本身庫、表、列所有字符集必須保持一致，為utf8或utf8mb4。
• 【強(qiáng)制】前端程序字符集或者環(huán)境變量中的字符集，與數(shù)據(jù)庫、表的字符集必須一致，統(tǒng)一為utf8。

二、SQL編寫規(guī)范

1. DML語句

• 【強(qiáng)制】SELECT語句必須指定具體字段名稱，禁止寫成*。因為select *會將不該讀的數(shù)據(jù)也從MySQL里讀出來，造成網(wǎng)卡壓力。且表字段一旦更新，但model層沒有來得及更新的話，系統(tǒng)會報錯。
• 【強(qiáng)制】insert語句指定具體字段名稱，不要寫成insert into t1 values(…)，道理同上。
• 【建議】insert into…values(XX),(XX),(XX)…。這里XX的值不要超過5000個。值過多雖然上線很很快，但會引起主從同步延遲。
• 【建議】SELECT語句不要使用UNION，推薦使用UNION ALL，并且UNION子句個數(shù)限制在5個以內(nèi)。因為union all不需要去重，節(jié)省數(shù)據(jù)庫資源，提高性能。
• 【建議】in值列表限制在500以內(nèi)。例如select… where userid in(….500個以內(nèi)…)，這么做是為了減少底層掃描，減輕數(shù)據(jù)庫壓力從而加速查詢。
• 【建議】事務(wù)里批量更新數(shù)據(jù)需要控制數(shù)量，進(jìn)行必要的sleep，做到少量多次。
• 【強(qiáng)制】事務(wù)涉及的表必須全部是innodb表。否則一旦失敗不會全部回滾，且易造成主從庫同步終端。
• 【強(qiáng)制】寫入和事務(wù)發(fā)往主庫，只讀SQL發(fā)往從庫。
• 【強(qiáng)制】除靜態(tài)表或小表(100行以內(nèi))，DML語句必須有where條件，且使用索引查找。
• 【強(qiáng)制】生產(chǎn)環(huán)境禁止使用hint，如sql_no_cache，force index，ignore key，straight join等。因為hint是用來強(qiáng)制SQL按照某個執(zhí)行計劃來執(zhí)行，但隨著數(shù)據(jù)量變化我們無法保證自己當(dāng)初的預(yù)判是正確的，因此我們要相信MySQL優(yōu)化器!
• 【強(qiáng)制】where條件里等號左右字段類型必須一致，否則無法利用索引。
• 【建議】SELECT|UPDATE|DELETE|REPLACE要有WHERE子句，且WHERE子句的條件必需使用索引查找。
• 【強(qiáng)制】生產(chǎn)數(shù)據(jù)庫中強(qiáng)烈不推薦大表上發(fā)生全表掃描，但對于100行以下的靜態(tài)表可以全表掃描。查詢數(shù)據(jù)量不要超過表行數(shù)的25%，否則不會利用索引。
• 【強(qiáng)制】WHERE 子句中禁止只使用全模糊的LIKE條件進(jìn)行查找，必須有其他等值或范圍查詢條件，否則無法利用索引。
• 【建議】索引列不要使用函數(shù)或表達(dá)式，否則無法利用索引。如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)。
• 【建議】分頁查詢，當(dāng)limit起點較高時，可先用過濾條件進(jìn)行過濾。如select a,b,c from t1 limit 10000,20;優(yōu)化為:select a,b,c from t1 where id>10000 limit 20;。

2. 多表連接

• 【強(qiáng)制】禁止跨db的join語句。因為這樣可以減少模塊間耦合，為數(shù)據(jù)庫拆分奠定堅實基礎(chǔ)。
• 【強(qiáng)制】禁止在業(yè)務(wù)的更新類SQL語句中使用join，比如update t1 join t2…。
• 【建議】不建議使用子查詢，建議將子查詢SQL拆開結(jié)合程序多次查詢，或使用join來代替子查詢。
• 【建議】線上環(huán)境，多表join不要超過3個表。
• 【建議】多表連接查詢推薦使用別名，且SELECT列表中要用別名引用字段，數(shù)據(jù)庫.表格式，如select a from db1.table1 alias1 where …。
• 【建議】在多表join中，盡量選取結(jié)果集較小的表作為驅(qū)動表，來join其他表。

3. 事務(wù)

• 【建議】事務(wù)中INSERT|UPDATE|DELETE|REPLACE語句操作的行數(shù)控制在2000以內(nèi)，以及WHERE子句中IN列表的傳參個數(shù)控制在500以內(nèi)。
• 【建議】批量操作數(shù)據(jù)時，需要控制事務(wù)處理間隔時間，進(jìn)行必要的sleep，一般建議值5-10秒。
• 【建議】對于有auto_increment屬性字段的表的插入操作，并發(fā)需要控制在200以內(nèi)。
• 【強(qiáng)制】程序設(shè)計必須考慮“數(shù)據(jù)庫事務(wù)隔離級別”帶來的影響，包括臟讀、不可重復(fù)讀和幻讀。線上建議事務(wù)隔離級別為repeatable-read。
• 【建議】事務(wù)里包含SQL不超過5個(支付業(yè)務(wù)除外)。因為過長的事務(wù)會導(dǎo)致鎖數(shù)據(jù)較久，MySQL內(nèi)部緩存、連接消耗過多等雪崩問題。
• 【建議】事務(wù)里更新語句盡量基于主鍵或unique key，如update … where id=XX; 否則會產(chǎn)生間隙鎖，內(nèi)部擴(kuò)大鎖定范圍，導(dǎo)致系統(tǒng)性能下降，產(chǎn)生死鎖。
• 【建議】盡量把一些典型外部調(diào)用移出事務(wù)，如調(diào)用webservice，訪問文件存儲等，從而避免事務(wù)過長。
• 【建議】對于MySQL主從延遲嚴(yán)格敏感的select語句，請開啟事務(wù)強(qiáng)制訪問主庫。

4. 排序和分組

• 【建議】減少使用order by，和業(yè)務(wù)溝通能不排序就不排序，或?qū)⑴判蚍诺匠绦蚨巳プ?。order by、group by、distinct這些語句較為耗費CPU，數(shù)據(jù)庫的CPU資源是極其寶貴的。
• 【建議】order by、group by、distinct這些SQL盡量利用索引直接檢索出排序好的數(shù)據(jù)。如where a=1 order by可以利用key(a,b)。
• 【建議】包含了order by、group by、distinct這些查詢的語句，where條件過濾出來的結(jié)果集請保持在1000行以內(nèi)，否則SQL會很慢。

5. 線上禁止使用的SQL語句

• 【高危】禁用update|delete t1 … where a=XX limit XX; 這種帶limit的更新語句。因為會導(dǎo)致主從不一致，導(dǎo)致數(shù)據(jù)錯亂。建議加上order by PK。
• 【高?！拷故褂藐P(guān)聯(lián)子查詢，如update t1 set … where name in(select name from user where…);效率極其低下。
• 【強(qiáng)制】禁用procedure、function、trigger、views、event、外鍵約束。因為他們消耗數(shù)據(jù)庫資源，降低數(shù)據(jù)庫實例可擴(kuò)展性。推薦都在程序端實現(xiàn)。
• 【強(qiáng)制】禁用insert into …on duplicate key update…在高并發(fā)環(huán)境下，會造成主從不一致。
• 【強(qiáng)制】禁止聯(lián)表更新語句，如update t1,t2 where t1.id=t2.id…。