1、MySQL數(shù)據(jù)庫設(shè)計規(guī)范目錄1.規(guī)范背景與目的32.設(shè)計規(guī)范33.1 數(shù)據(jù)庫設(shè)計33.2.1 庫名33.2.2 表結(jié)構(gòu)33.2.3列數(shù)據(jù)類型優(yōu)化43.2.4 索引設(shè)計63.2.5 分庫分表、分區(qū)表73.2.6 字符集73.2.7程序DAO層設(shè)計建議73.2.8一個規(guī)范的建表語句示例83.2 SQL編寫83.2.1 DML語句83.2.2 多表連接93.2.3 事務(wù)103.2.4 排序和分組103.2.5 線上禁止使用的SQL語句101. 規(guī)范背景與目的MySQL數(shù)據(jù)庫與oracle、sqlserver等數(shù)據(jù)庫相比，有其內(nèi)核上的優(yōu)勢與劣勢。我們在使用MySQL數(shù)據(jù)庫的時候需要遵循一定規(guī)范，揚長避

2、短。本規(guī)范旨在幫助或指導(dǎo)RD、QA、OP等技術(shù)人員做出適合線上業(yè)務(wù)的數(shù)據(jù)庫設(shè)計。在數(shù)據(jù)庫變更和處理流程、數(shù)據(jù)庫表設(shè)計、SQL編寫等方面予以規(guī)范，從而為公司業(yè)務(wù)系統(tǒng)穩(wěn)定、健康地運行提供保障。2. 設(shè)計規(guī)范3.1 數(shù)據(jù)庫設(shè)計以下所有規(guī)范會按照【高?！俊ⅰ緩娭啤?、【建議】三個級別進行標注，遵守優(yōu)先級從高到低。對于不滿足【高?！亢汀緩娭啤績蓚€級別的設(shè)計，DBA會強制打回要求修改。3.2.1 庫名1. 【強制】庫的名稱必須控制在32個字符以內(nèi)，相關(guān)模塊的表名與表名之間盡量提現(xiàn)join的關(guān)系，如user表和user_login表。2. 【強制】庫的名稱格式：業(yè)務(wù)系統(tǒng)名稱_子系統(tǒng)名，同一模塊使用的表名盡量使

3、用統(tǒng)一前綴。3. 【強制】一般分庫名稱命名格式是“庫通配名_編號”，編號從“0”開始遞增，比如“wenda_001”以時間進行分庫的名稱格式是“庫通配名_時間”3. 【強制】創(chuàng)建數(shù)據(jù)庫時必須顯式指定字符集，并且字符集只能是utf8或者utf8mb4創(chuàng)建數(shù)據(jù)庫SQL舉例：Create database db1 default character set utf8;3.2.2 表結(jié)構(gòu)1. 【強制】表和列的名稱必須控制在32個字符以內(nèi)，表名只能使用字母、數(shù)字和下劃線，一律小寫。2. 【強制】表名要求模塊名強相關(guān)，如師資系統(tǒng)采用”sz”作為前綴，渠道系統(tǒng)采用”qd”作為前綴等。3. 【強制】創(chuàng)建表時必

4、須顯式指定字符集為utf8或utf8mb4。4. 【強制】創(chuàng)建表時必須顯式指定表存儲引擎類型，如無特殊需求，一律為InnoDB。當需要使用除InnoDB/MyISAM/Memory以外的存儲引擎時，必須通過DBA審核才能在生產(chǎn)環(huán)境中使用。因為Innodb表支持事務(wù)、行鎖、宕機恢復(fù)、MVCC等關(guān)系型數(shù)據(jù)庫重要特性，為業(yè)界使用最多的MySQL存儲引擎。而這是其他大多數(shù)存儲引擎不具備的，因此首推InnoDB。4. 【強制】建表必須有comment5. 【建議】建表時關(guān)于主鍵：(1)強制要求主鍵為id，類型為int或bigint，且為auto_increment(2)標識表里每一行主體的字段不要設(shè)為主

5、鍵，建議設(shè)為其他字段如user_id，order_id等，并建立unique key索引（可參考cdb.teacher表設(shè)計）。因為如果設(shè)為主鍵且主鍵值為隨機插入，則會導(dǎo)致innodb內(nèi)部page分裂和大量隨機I/O，性能下降。6. 【建議】核心表（如用戶表，金錢相關(guān)的表）必須有行數(shù)據(jù)的創(chuàng)建時間字段create_time和最后更新時間字段update_time，便于查問題。7. 【建議】表中所有字段必須都是NOT NULL屬性，業(yè)務(wù)可以根據(jù)需要定義DEFAULT值。因為使用NULL值會存在每一行都會占用額外存儲空間、數(shù)據(jù)遷移容易出錯、聚合函數(shù)計算結(jié)果偏差等問題。8. 【建議】建議對表里的blo

6、b、text等大字段，垂直拆分到其他表里，僅在需要讀這些對象的時候才去select。9. 【建議】反范式設(shè)計：把經(jīng)常需要join查詢的字段，在其他表里冗余一份。如user_name屬性在user_account，user_login_log等表里冗余一份，減少join查詢。10. 【強制】中間表用于保留中間結(jié)果集，名稱必須以“tmp_”開頭。備份表用于備份或抓取源表快照，名稱必須以“bak_”開頭。中間表和備份表定期清理。11. 【強制】對于超過100W行的大表進行alter table，必須經(jīng)過DBA審核，并在業(yè)務(wù)低峰期執(zhí)行。因為alter table會產(chǎn)生表鎖，期間阻塞對于該表的所有寫入，

7、對于業(yè)務(wù)可能會產(chǎn)生極大影響。3.2.3 列數(shù)據(jù)類型優(yōu)化1. 【建議】表中的自增列（auto_increment屬性），推薦使用bigint類型。因為無符號int存儲范圍為-（大約21億左右），溢出后會導(dǎo)致報錯。2. 【建議】業(yè)務(wù)中選擇性很少的狀態(tài)status、類型type等字段推薦使用tinytint或者smallint類型節(jié)省存儲空間。3. 【建議】業(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.

8、168.2.12); select inet_ntoa();Php: ip2long(192.168.2.12); long2ip();4. 【建議】不推薦使用enum，set因為它們浪費空間，且枚舉值寫死了，變更不方便。推薦使用tinyint或smallint5. 【建議】不推薦使用blob，text等類型它們都比較浪費硬盤和內(nèi)存空間。在加載表數(shù)據(jù)時，會讀取大字段到內(nèi)存里從而浪費內(nèi)存空間，影響系統(tǒng)性能。建議和PM、RD溝通，是否真的需要這么大字段？Innodb中當一行記錄超過8098字節(jié)時，會將該記錄中選取最長的一個字段將其768字節(jié)放在原始page里，該字段余下內(nèi)容放在overflow-p

9、age里。不幸的是在compact行格式下，原始page和overflow-page都會加載。6. 【建議】存儲金錢的字段，建議用int，程序端乘以100和除以100進行存取。因為int占用4字節(jié)，而double占用8字節(jié)，空間浪費。7. 【建議】文本數(shù)據(jù)盡量用varchar存儲因為varchar是變長存儲，比char更省空間。MySQL server層規(guī)定一行所有文本最多存65535字節(jié)，因此在utf8字符集下最多存21844個字符，超過會自動轉(zhuǎn)換為mediumtext字段。而text在utf8字符集下最多存21844個字符，mediumtext最多存224/3個字符，longtext最多存

10、232個字符。一般建議用varchar類型，字符數(shù)不要超過27008. 【建議】時間類型盡量選取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()來進行轉(zhuǎn)換。詳細存儲大小參加下圖：3.2.4 索引設(shè)計1. 【強制】InnoDB表必須主鍵為id int/bigint auto_increment,且主鍵值禁止被更新。2. 【建議】主鍵的名稱以“pk_”開頭，唯一

11、鍵以“uk_”或“uq_”開頭，普通索引以“idx_”開頭，一律使用小寫格式，以表名/字段的名稱或縮寫作為后綴。3. 【強制】InnoDB和MyISAM存儲引擎表，索引類型必須為BTREE；MEMORY表可以根據(jù)需要選擇HASH或者BTREE類型索引。4. 【強制】單個索引中每個索引記錄的長度不能超過64KB5. 【建議】單個表上的索引個數(shù)不能超過7個6. 【建議】在建立索引時，多考慮建立聯(lián)合索引，并把區(qū)分度最高的字段放在最前面。如列userid的區(qū)分度可由select count(distinct userid)計算出來。7. 【建議】在多表join的SQL里，保證被驅(qū)動表的連接列上有索引，

12、這樣join執(zhí)行效率最高。8. 【建議】建表或加索引時，保證表里互相不存在冗余索引。對于MySQL來說，如果表里已經(jīng)存在key(a,b)，則key(a)為冗余索引，需要刪除。3.2.5 分庫分表、分區(qū)表1. 【強制】分區(qū)表的分區(qū)字段（partition-key）必須有索引，或者是組合索引的首列。2. 【強制】單個分區(qū)表中的分區(qū)（包括子分區(qū)）個數(shù)不能超過1024。3. 【強制】上線前RD或者DBA必須指定分區(qū)表的創(chuàng)建、清理策略。4. 【強制】訪問分區(qū)表的SQL必須包含分區(qū)鍵。5. 【建議】單個分區(qū)文件不超過2G，總大小不超過50G。建議總分區(qū)數(shù)不超過20個。6. 【強制】對于分區(qū)表執(zhí)行alter

13、 table操作，必須在業(yè)務(wù)低峰期執(zhí)行。7. 【強制】采用分庫策略的，庫的數(shù)量不能超過10248. 【強制】采用分表策略的，表的數(shù)量不能超過40969. 【建議】單個分表不超過500W行，ibd文件大小不超過2G，這樣才能讓數(shù)據(jù)分布式變得性能更佳。10. 【建議】水平分表盡量用取模方式，日志、報表類數(shù)據(jù)建議采用日期進行分表。3.2.6 字符集1. 【強制】數(shù)據(jù)庫本身庫、表、列所有字符集必須保持一致，為utf8或utf8mb42. 【強制】前端程序字符集或者環(huán)境變量中的字符集，與數(shù)據(jù)庫、表的字符集必須一致，統(tǒng)一為utf83.2.7 程序DAO層設(shè)計建議1. 【建議】新的代碼不要用model，推薦

14、使用手動拼SQL+綁定變量傳入?yún)?shù)的方式。因為model雖然可以使用面向?qū)ο蟮姆绞讲僮鱠b，但是其使用不當很容易造成生成的SQL非常復(fù)雜，且model層自己做的強制類型轉(zhuǎn)換性能較差，最終導(dǎo)致數(shù)據(jù)庫性能下降。2. 【建議】前端程序連接MySQL或者redis，必須要有連接超時和失敗重連機制，且失敗重試必須有間隔時間。3. 【建議】前端程序報錯里盡量能夠提示MySQL或redis原生態(tài)的報錯信息，便于排查錯誤。4. 【建議】對于有連接池的前端程序，必須根據(jù)業(yè)務(wù)需要配置初始、最小、最大連接數(shù)，超時時間以及連接回收機制，否則會耗盡數(shù)據(jù)庫連接資源，造成線上事故。5. 【建議】對于log或history類

15、型的表，隨時間增長容易越來越大，因此上線前RD或者DBA必須建立表數(shù)據(jù)清理或歸檔方案。6. 【建議】 在應(yīng)用程序設(shè)計階段，RD必須考慮并規(guī)避數(shù)據(jù)庫中主從延遲對于業(yè)務(wù)的影響。盡量避免從庫短時延遲（20秒以內(nèi)）對業(yè)務(wù)造成影響，建議強制一致性的讀開啟事務(wù)走主庫，或更新后過一段時間再去讀從庫。7. 【建議】多個并發(fā)業(yè)務(wù)邏輯訪問同一塊數(shù)據(jù)（innodb表）時，會在數(shù)據(jù)庫端產(chǎn)生行鎖甚至表鎖導(dǎo)致并發(fā)下降，因此建議更新類SQL盡量基于主鍵去更新。8. 【建議】業(yè)務(wù)邏輯之間加鎖順序盡量保持一致，否則會導(dǎo)致死鎖。9. 【建議】對于單表讀寫比大于10:1的數(shù)據(jù)行或單個列，可以將熱點數(shù)據(jù)放在緩存里（如mecache或

16、redis），加快訪問速度，降低MySQL壓力。3.2.8 一個規(guī)范的建表語句示例一個較為規(guī)范的建表語句為：CREATE TABLE user ( id bigint(11) NOT NULL AUTO_INCREMENT, 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

17、NULL COMMENT 頭像, birthday date NOT NULL COMMENT 生日, sex tinyint(4) DEFAULT 0 COMMENT 性別, short_introduce varchar(150) DEFAULT NULL COMMENT 一句話介紹自己，最多50個漢字, user_resume varchar(300) NOT NULL COMMENT 用戶提交的簡歷存放地址, user_register_ip int NOT NULL COMMENT 用戶注冊時的源ip, create_time timestamp NOT NULL COMMENT 用

18、戶記錄創(chuàng)建的時間, update_time timestamp NOT NULL COMMENT 用戶資料修改的時間, user_review_status tinyint NOT NULL COMMENT 用戶資料審核狀態(tài)，1為通過，2為審核中，3為未通過，4為還未提交審核, PRIMARY KEY (id), UNIQUE KEY idx_user_id (user_id), KEY idx_username(username), KEY idx_create_time(create_time,user_review_status) ENGINE=InnoDB DEFAULT CHARSE

19、T=utf8 COMMENT=網(wǎng)站用戶基本信息;3.2 SQL編寫3.2.1 DML語句1. 【強制】SELECT語句必須指定具體字段名稱，禁止寫成“*”因為select *會將不該讀的數(shù)據(jù)也從MySQL里讀出來，造成網(wǎng)卡壓力。且表字段一旦更新，但model層沒有來得及更新的話，系統(tǒng)會報錯。2. 【強制】insert語句指定具體字段名稱，不要寫成insert into t1 values()，道理同上。3. 【建議】insert intovalues(XX),(XX),(XX). 這里XX的值不要超過5000個。值過多雖然上線很很快，但會引起主從同步延遲。4. 【建議】SELECT語句不要使用

20、UNION，推薦使用UNION ALL，并且UNION子句個數(shù)限制在5個以內(nèi)。因為union all不需要去重，節(jié)省數(shù)據(jù)庫資源，提高性能。5. 【建議】in值列表限制在500以內(nèi)。例如select where userid in(.500個以內(nèi))，這么做是為了減少底層掃描，減輕數(shù)據(jù)庫壓力從而加速查詢。6. 【建議】事務(wù)里批量更新數(shù)據(jù)需要控制數(shù)量，進行必要的sleep，做到少量多次。7. 【強制】事務(wù)涉及的表必須全部是innodb表。否則一旦失敗不會全部回滾，且易造成主從庫同步終端。8. 【強制】寫入和事務(wù)發(fā)往主庫，只讀SQL發(fā)往從庫。9. 【強制】除靜態(tài)表或小表（100行以內(nèi)），DML語句必須

21、有where條件，且使用索引查找。10. 【強制】生產(chǎn)環(huán)境禁止使用hint，如sql_no_cache，force index，ignore key，straight join等。因為hint是用來強制SQL按照某個執(zhí)行計劃來執(zhí)行，但隨著數(shù)據(jù)量變化我們無法保證自己當初的預(yù)判是正確的，因此我們要相信MySQL優(yōu)化器！11. 【強制】where條件里等號左右字段類型必須一致，否則無法利用索引。12. 【建議】SELECT|UPDATE|DELETE|REPLACE要有WHERE子句，且WHERE子句的條件必需使用索引查找。13. 【強制】生產(chǎn)數(shù)據(jù)庫中強烈不推薦大表上發(fā)生全表掃描，但對于100行以下

22、的靜態(tài)表可以全表掃描。查詢數(shù)據(jù)量不要超過表行數(shù)的25%，否則不會利用索引。14. 【強制】WHERE 子句中禁止只使用全模糊的LIKE條件進行查找，必須有其他等值或范圍查詢條件，否則無法利用索引。15. 【建議】索引列不要使用函數(shù)或表達式，否則無法利用索引。如where length(name)=Admin或where user_id+2=10023。16. 【建議】減少使用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)17. 【建議】分頁查詢，

23、當limit起點較高時，可先用過濾條件進行過濾。如select a,b,c from t1 limit 10000,20;優(yōu)化為: Select a,b,c from t1 where id10000 limit 20;3.2.2 多表連接1. 【強制】禁止跨db的join語句。因為這樣可以減少模塊間耦合，為數(shù)據(jù)庫拆分奠定堅實基礎(chǔ)。2. 【強制】禁止在業(yè)務(wù)的更新類SQL語句中使用join，比如update t1 join t23. 【建議】不建議使用子查詢，建議將子查詢SQL拆開結(jié)合程序多次查詢，或使用join來代替子查詢。4. 【建議】線上環(huán)境，多表join不要超過3個表。5. 【建議】多表

24、連接查詢推薦使用別名，且SELECT列表中要用別名引用字段，數(shù)據(jù)庫.表格式，如“select a from db1.table1 alias1 where ”6. 【建議】在多表join中，盡量選取結(jié)果集較小的表作為驅(qū)動表，來join其他表。3.2.3 事務(wù)1. 【建議】事務(wù)中INSERT|UPDATE|DELETE|REPLACE語句操作的行數(shù)控制在2000以內(nèi)，以及WHERE子句中IN列表的傳參個數(shù)控制在500以內(nèi)。2. 【建議】批量操作數(shù)據(jù)時，需要控制事務(wù)處理間隔時間，進行必要的sleep，一般建議值5-10秒。3. 【建議】對于有auto_increment屬性字段的表的插入操作，并發(fā)需要控制在200以內(nèi)。4. 【強制】程序設(shè)計必須考慮“數(shù)據(jù)庫事務(wù)隔離級別”帶來的影響，包括臟讀、不可重復(fù)讀和幻讀。線上建議事務(wù)隔離級別為repeatable-read5. 【建議】事務(wù)里包含SQL不超過5個（支付業(yè)務(wù)除外）因為過長的事務(wù)會導(dǎo)致鎖數(shù)據(jù)較久，MySQL內(nèi)部緩存、連接消耗過多等雪崩問題。6. 【建議】事務(wù)里更新語句盡量基于主鍵或unique key