这篇数据库设计规范建议,我必须分享给你
為什么需要規(guī)范
在互聯(lián)網(wǎng)項(xiàng)目,分工明確,拆分細(xì)致, 但一個(gè)項(xiàng)目往往會(huì)涉及眾多參與人員,包括架構(gòu)師、高級(jí)工程師、研發(fā)工程師、測(cè)試工程師、DBA等不同角色。數(shù)據(jù)庫(kù)是一個(gè)項(xiàng)目的最底層設(shè)計(jì), 如果設(shè)計(jì)不清晰,結(jié)構(gòu)模糊, 規(guī)范混亂,是很難做成一個(gè)成功的項(xiàng)目,而這些需要我們從最基本的開始,對(duì)數(shù)據(jù)庫(kù)有一套明確的設(shè)計(jì)規(guī)范。
如何設(shè)計(jì)
傳統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)往往會(huì)遵循設(shè)計(jì)范式(三范式), 隨著不斷發(fā)展, 范式設(shè)計(jì)往往難以滿足現(xiàn)在項(xiàng)目的要求, 出現(xiàn)了反范式設(shè)計(jì)。
-
范式設(shè)計(jì):
第一范式:數(shù)據(jù)庫(kù)表中的每一列都是不可分割的基本數(shù)據(jù)項(xiàng), 比如股票名稱、股票代碼。
第二范式:數(shù)據(jù)庫(kù)表所有屬性都必須和主鍵有完全依賴關(guān)系, 比如用戶編號(hào)為主鍵,那么與之依賴的有用戶名、用戶地址、用戶性別和身份證等, 這些屬性完全與具體用戶產(chǎn)生依賴。
第三范式:數(shù)據(jù)庫(kù)表中屬性不能存在傳遞關(guān)系, 比如A->B->C, 屬性之間存在這樣關(guān)系則不符合第三范式。再具體點(diǎn), 比如股票表(股票編號(hào), 股票名稱,股票市場(chǎng),股票交易手續(xù)費(fèi)),這個(gè)表就不符合第三范式,因?yàn)楣善苯灰资掷m(xù)費(fèi)是依賴股票市場(chǎng)來(lái)確定, 需要再拆分為(股票編號(hào), 股票名稱)和(股票市場(chǎng),股票交易手續(xù)費(fèi))多張表。
-
反范式設(shè)計(jì):
范式設(shè)計(jì)雖然關(guān)系層次明確, 但是給業(yè)務(wù)查詢帶來(lái)了相當(dāng)?shù)膹?fù)雜度,中間關(guān)系表膨脹, 查詢需要連接更多的表, 導(dǎo)致性能嚴(yán)重下降。
反范式并不是完全打破范式設(shè)計(jì), 而是在范式設(shè)計(jì)與查詢性能上找到平衡點(diǎn),適合業(yè)務(wù)的設(shè)計(jì)才是好的設(shè)計(jì)。
在實(shí)際業(yè)務(wù)中, 如何去設(shè)計(jì)呢? 做好需求分析, 理解業(yè)務(wù)流程, 弄清業(yè)務(wù)概念, 技術(shù)是生產(chǎn)工具, 服務(wù)于業(yè)務(wù)才能體現(xiàn)其價(jià)值,所以明白業(yè)務(wù)需求才知道怎么去設(shè)計(jì)。業(yè)務(wù)設(shè)計(jì)三步法則:
-
先拆解:哪些是業(yè)務(wù)系統(tǒng)的核心模塊, 核心模塊具體包含哪些對(duì)象, 哪些又是非核心模塊, 必須先列清楚。
-
畫關(guān)系:將拆解的模塊串接起來(lái),形成一個(gè)整體, 哪些是依賴關(guān)系, 哪些是包含關(guān)系,哪些是聚合, 哪些又是組合。
-
再細(xì)化:有上面的清晰脈絡(luò), 再去填充對(duì)象的屬性, 仔細(xì)考量, 適當(dāng)加入反范式設(shè)計(jì), 冗余一些常用信息,這些就需再仔細(xì)去細(xì)化, 斟酌與權(quán)衡。
命名與表設(shè)計(jì)規(guī)范
規(guī)范并不是千遍一律, 但要在自己的項(xiàng)目體系內(nèi)形成一套有效規(guī)范, 以下規(guī)范可作參考:
命名規(guī)范
?1.命名有意義,一眼知道這張表是干什么用的2.數(shù)據(jù)庫(kù),表都用小寫數(shù)據(jù)庫(kù)形如:backend數(shù)據(jù)表形如:client_device_info(客戶端設(shè)備信息),不要縮寫,字母全小寫3.索引命名以idx_為前綴4.命名不要過(guò)長(zhǎng)(應(yīng)盡量少于25字符)5.不要使用保留字6.同一字段在不同的表中也應(yīng)是相同的類型和長(zhǎng)度7.同一數(shù)據(jù)庫(kù)下有不同的模塊,可以考慮對(duì)表名用不同的前綴標(biāo)識(shí)8.備份表時(shí)加上時(shí)間標(biāo)識(shí)表設(shè)計(jì)規(guī)范
?1.如果沒(méi)有特殊情況,建議選擇InnoDB引擎2.每個(gè)表都應(yīng)該有主鍵,可選擇自增字段,或長(zhǎng)整型字段。例外情況,如果有多個(gè)字段可以組成唯一, 并且需要頻繁查詢的, 那么可以采用復(fù)合主鍵,比如用戶編號(hào)+交易商編號(hào)做為復(fù)合主鍵,因?yàn)榭梢源_保不產(chǎn)生重復(fù)數(shù)據(jù), 同時(shí)節(jié)省空間。3.(不做強(qiáng)制要求)盡量將字段設(shè)置為NOT NULL。因?yàn)镹ULL值的存儲(chǔ)需要額外的空間,且會(huì)導(dǎo)致比較運(yùn)算更為復(fù)雜,會(huì)使得優(yōu)化器更難以優(yōu)化sql。空值是不占用空間, 但NULL列需要占用一個(gè)額外字節(jié)。4.使用更短小的列,比如整型列。整型列的執(zhí)行速度往往更快。5.存儲(chǔ)精確浮點(diǎn)數(shù)必須使用DECIMAL代替float和double。6.建議使用unsigned類型存儲(chǔ)非負(fù)值7.建議使用 int unsigned存儲(chǔ)ipv48.整型定義中不添加顯示長(zhǎng)度的值,使用int,而不是int(4)9.盡可能不要使用text,blob類型10.varchar(n) n表示字符數(shù)而不是字節(jié)數(shù),比如varchar(255)最大可存儲(chǔ)255個(gè)漢字,需根據(jù)實(shí)際字符長(zhǎng)度選擇n的值。11.字符集建議選擇utf-812.存儲(chǔ)年時(shí)使用year類型13.存儲(chǔ)日期時(shí)使用date類型14.存儲(chǔ)時(shí)間時(shí),建議使用timestamp類型,因?yàn)閠imestamp使用的是4字節(jié),datetime使用的是8字節(jié)。15.不要在數(shù)據(jù)庫(kù)中使用varbinary或blob存儲(chǔ)圖片及文件,mysql 并不適合大量存儲(chǔ)這類型文件16.join 操作的字段,在不同表中的類型及命名要一致17.如果更改表結(jié)構(gòu)會(huì)影響性能,需要找DBA、表設(shè)計(jì)人員進(jìn)行聯(lián)合評(píng)審。數(shù)據(jù)庫(kù)設(shè)計(jì)原則
核心原則
不在數(shù)據(jù)庫(kù)做運(yùn)算; cpu計(jì)算務(wù)必移至業(yè)務(wù)層;
控制每個(gè)表的列數(shù)量(字段少而精,字段數(shù)建議在20以內(nèi));
平衡范式與冗余(效率優(yōu)先;往往犧牲范式)
拒絕3B(拒絕大sql語(yǔ)句:big sql、拒絕大事物:big transaction、拒絕大批量:big batch);
字段類原則
用好數(shù)值類型(用合適的字段類型節(jié)約空間);
字符轉(zhuǎn)化為數(shù)字(能轉(zhuǎn)化的最好轉(zhuǎn)化,同樣節(jié)約空間、提高查詢性能);
避免使用NULL字段(NULL字段很難查詢優(yōu)化、NULL字段的索引需要額外空間、NULL字段的復(fù)合索引無(wú)效);
少用text類型(盡量使用varchar代替text字段);
索引類原則
合理使用索引(改善查詢,減慢更新,索引一定不是越多越好); 字符字段大量重復(fù)屬性時(shí), 盡量建前綴索引(比如地區(qū)chinaXXX, alter table x_test add index(x_city(6)) ); 不在索引做列運(yùn)算; innodb主鍵推薦使用自增列(主鍵建立聚簇索引,主鍵不應(yīng)該被修改,字符串不應(yīng)該做主鍵)?不用外鍵(由程序保證約束);
SQL類原則
sql語(yǔ)句盡可能簡(jiǎn)單(一條sql只能在一個(gè)cpu運(yùn)算,大語(yǔ)句拆小語(yǔ)句,減少鎖時(shí)間,一條大sql可以堵死整個(gè)庫(kù)); 避免使用trig/func(觸發(fā)器、函數(shù), 采用程序取而代之); 大批量SQL的更新操作要開啟事務(wù),越多效果性能越明顯。
規(guī)范背景與目的
MySQL數(shù)據(jù)庫(kù)與 Oracle、 SQL Server 等數(shù)據(jù)庫(kù)相比,有其內(nèi)核上的優(yōu)勢(shì)與劣勢(shì)。我們?cè)谑褂肕ySQL數(shù)據(jù)庫(kù)的時(shí)候需要遵循一定規(guī)范,揚(yáng)長(zhǎng)避短。本規(guī)范旨在幫助或指導(dǎo)RD、QA、OP等技術(shù)人員做出適合線上業(yè)務(wù)的數(shù)據(jù)庫(kù)設(shè)計(jì)。在數(shù)據(jù)庫(kù)變更和處理流程、數(shù)據(jù)庫(kù)表設(shè)計(jì)、SQL編寫等方面予以規(guī)范,從而為公司業(yè)務(wù)系統(tǒng)穩(wěn)定、健康地運(yùn)行提供保障。
設(shè)計(jì)規(guī)范
數(shù)據(jù)庫(kù)設(shè)計(jì)
以下所有規(guī)范會(huì)按照【高危】、【強(qiáng)制】、【建議】三個(gè)級(jí)別進(jìn)行標(biāo)注,遵守優(yōu)先級(jí)從高到低。
對(duì)于不滿足【高危】和【強(qiáng)制】?jī)蓚€(gè)級(jí)別的設(shè)計(jì),DBA會(huì)強(qiáng)制打回要求修改。
庫(kù)名
【強(qiáng)制】庫(kù)的名稱必須控制在32個(gè)字符以內(nèi),相關(guān)模塊的表名與表名之間盡量提現(xiàn)join的關(guān)系,如user表和user_login表。
【強(qiáng)制】庫(kù)的名稱格式:業(yè)務(wù)系統(tǒng)名稱_子系統(tǒng)名,同一模塊使用的表名盡量使用統(tǒng)一前綴。
【強(qiáng)制】一般分庫(kù)名稱命名格式是庫(kù)通配名_編號(hào),編號(hào)從0開始遞增,比如wenda_001以時(shí)間進(jìn)行分庫(kù)的名稱格式是“庫(kù)通配名_時(shí)間”
【強(qiáng)制】創(chuàng)建數(shù)據(jù)庫(kù)時(shí)必須顯式指定字符集,并且字符集只能是utf8或者utf8mb4。創(chuàng)建數(shù)據(jù)庫(kù)SQL舉例:create database db1 default character set utf8;。
表結(jié)構(gòu)
【強(qiáng)制】表和列的名稱必須控制在32個(gè)字符以內(nèi),表名只能使用字母、數(shù)字和下劃線,一律小寫。
【強(qiáng)制】表名要求模塊名強(qiáng)相關(guān),如師資系統(tǒng)采用”sz”作為前綴,渠道系統(tǒng)采用”qd”作為前綴等。
【強(qiáng)制】創(chuàng)建表時(shí)必須顯式指定字符集為utf8或utf8mb4。
【強(qiáng)制】創(chuàng)建表時(shí)必須顯式指定表存儲(chǔ)引擎類型,如無(wú)特殊需求,一律為InnoDB。當(dāng)需要使用除InnoDB/MyISAM/Memory以外的存儲(chǔ)引擎時(shí),必須通過(guò)DBA審核才能在生產(chǎn)環(huán)境中使用。因?yàn)镮nnodb表支持事務(wù)、行鎖、宕機(jī)恢復(fù)、MVCC等關(guān)系型數(shù)據(jù)庫(kù)重要特性,為業(yè)界使用最多的MySQL存儲(chǔ)引擎。而這是其他大多數(shù)存儲(chǔ)引擎不具備的,因此首推InnoDB。
【強(qiáng)制】建表必須有comment
【建議】建表時(shí)關(guān)于主鍵:(1)強(qiáng)制要求主鍵為id,類型為int或bigint,且為auto_increment(2)標(biāo)識(shí)表里每一行主體的字段不要設(shè)為主鍵,建議設(shè)為其他字段如user_id,order_id等,并建立unique key索引(可參考cdb.teacher表設(shè)計(jì))。因?yàn)槿绻O(shè)為主鍵且主鍵值為隨機(jī)插入,則會(huì)導(dǎo)致innodb內(nèi)部page分裂和大量隨機(jī)I/O,性能下降。
【建議】核心表(如用戶表,金錢相關(guān)的表)必須有行數(shù)據(jù)的創(chuàng)建時(shí)間字段create_time和最后更新時(shí)間字段update_time,便于查問(wèn)題。
【建議】表中所有字段必須都是NOT NULL屬性,業(yè)務(wù)可以根據(jù)需要定義DEFAULT值。因?yàn)槭褂肗ULL值會(huì)存在每一行都會(huì)占用額外存儲(chǔ)空間、數(shù)據(jù)遷移容易出錯(cuò)、聚合函數(shù)計(jì)算結(jié)果偏差等問(wèn)題。
【建議】建議對(duì)表里的blob、text等大字段,垂直拆分到其他表里,僅在需要讀這些對(duì)象的時(shí)候才去select。
【建議】反范式設(shè)計(jì):把經(jīng)常需要join查詢的字段,在其他表里冗余一份。如user_name屬性在user_account,user_login_log等表里冗余一份,減少join查詢。
【強(qiáng)制】中間表用于保留中間結(jié)果集,名稱必須以tmp_開頭。備份表用于備份或抓取源表快照,名稱必須以bak_開頭。中間表和備份表定期清理。
【強(qiáng)制】對(duì)于超過(guò)100W行的大表進(jìn)行alter table,必須經(jīng)過(guò)DBA審核,并在業(yè)務(wù)低峰期執(zhí)行。因?yàn)閍lter table會(huì)產(chǎn)生表鎖,期間阻塞對(duì)于該表的所有寫入,對(duì)于業(yè)務(wù)可能會(huì)產(chǎn)生極大影響。
列數(shù)據(jù)類型優(yōu)化
【建議】表中的自增列(auto_increment屬性),推薦使用bigint類型。因?yàn)闊o(wú)符號(hào)int存儲(chǔ)范圍為-2147483648~2147483647(大約21億左右),溢出后會(huì)導(dǎo)致報(bào)錯(cuò)。
【建議】業(yè)務(wù)中選擇性很少的狀態(tài)status、類型type等字段推薦使用tinytint或者smallint類型節(jié)省存儲(chǔ)空間。
【建議】業(yè)務(wù)中IP地址字段推薦使用int類型,不推薦用char(15)。因?yàn)閕nt只占4字節(jié),可以用如下函數(shù)相互轉(zhuǎn)換,而char(15)占用至少15字節(jié)。一旦表數(shù)據(jù)行數(shù)到了1億,那么要多用1.1G存儲(chǔ)空間。SQL:select inet_aton('192.168.2.12'); select inet_ntoa(3232236044);?PHP:?ip2long(‘192.168.2.12’); long2ip(3530427185);
【建議】不推薦使用enum,set。因?yàn)樗鼈兝速M(fèi)空間,且枚舉值寫死了,變更不方便。推薦使用tinyint或smallint。
【建議】不推薦使用blob,text等類型。它們都比較浪費(fèi)硬盤和內(nèi)存空間。在加載表數(shù)據(jù)時(shí),會(huì)讀取大字段到內(nèi)存里從而浪費(fèi)內(nèi)存空間,影響系統(tǒng)性能。建議和PM、RD溝通,是否真的需要這么大字段。Innodb中當(dāng)一行記錄超過(guò)8098字節(jié)時(shí),會(huì)將該記錄中選取最長(zhǎng)的一個(gè)字段將其768字節(jié)放在原始page里,該字段余下內(nèi)容放在overflow-page里。不幸的是在compact行格式下,原始page和overflow-page都會(huì)加載。
【建議】存儲(chǔ)金錢的字段,建議用int,程序端乘以100和除以100進(jìn)行存取。因?yàn)閕nt占用4字節(jié),而double占用8字節(jié),空間浪費(fèi)。
【建議】文本數(shù)據(jù)盡量用varchar存儲(chǔ)。因?yàn)関archar是變長(zhǎng)存儲(chǔ),比char更省空間。MySQL server層規(guī)定一行所有文本最多存65535字節(jié),因此在utf8字符集下最多存21844個(gè)字符,超過(guò)會(huì)自動(dòng)轉(zhuǎn)換為mediumtext字段。而text在utf8字符集下最多存21844個(gè)字符,mediumtext最多存224/3個(gè)字符,longtext最多存232個(gè)字符。一般建議用varchar類型,字符數(shù)不要超過(guò)2700。
【建議】時(shí)間類型盡量選取timestamp。因?yàn)閐atetime占用8字節(jié),timestamp僅占用4字節(jié),但是范圍為1970-01-01 00:00:01到2038-01-01 00:00:00。更為高階的方法,選用int來(lái)存儲(chǔ)時(shí)間,使用SQL函數(shù)unix_timestamp()和from_unixtime()來(lái)進(jìn)行轉(zhuǎn)換。
索引設(shè)計(jì)
【強(qiáng)制】InnoDB表必須主鍵為id int/bigint auto_increment,且主鍵值禁止被更新。
【建議】主鍵的名稱以“pk_”開頭,唯一鍵以“uk_”或“uq_”開頭,普通索引以“idx_”開頭,一律使用小寫格式,以表名/字段的名稱或縮寫作為后綴。
【強(qiáng)制】InnoDB和MyISAM存儲(chǔ)引擎表,索引類型必須為BTREE;MEMORY表可以根據(jù)需要選擇HASH或者BTREE類型索引。
【強(qiáng)制】單個(gè)索引中每個(gè)索引記錄的長(zhǎng)度不能超過(guò)64KB。
【建議】單個(gè)表上的索引個(gè)數(shù)不能超過(guò)7個(gè)。
【建議】在建立索引時(shí),多考慮建立聯(lián)合索引,并把區(qū)分度最高的字段放在最前面。如列userid的區(qū)分度可由select count(distinct userid)計(jì)算出來(lái)。
【建議】在多表join的SQL里,保證被驅(qū)動(dòng)表的連接列上有索引,這樣join執(zhí)行效率最高。
【建議】建表或加索引時(shí),保證表里互相不存在冗余索引。對(duì)于MySQL來(lái)說(shuō),如果表里已經(jīng)存在key(a,b),則key(a)為冗余索引,需要?jiǎng)h除。
分庫(kù)分表、分區(qū)表
【強(qiáng)制】分區(qū)表的分區(qū)字段(partition-key)必須有索引,或者是組合索引的首列。
【強(qiáng)制】單個(gè)分區(qū)表中的分區(qū)(包括子分區(qū))個(gè)數(shù)不能超過(guò)1024。
【強(qiáng)制】上線前RD或者DBA必須指定分區(qū)表的創(chuàng)建、清理策略。
【強(qiáng)制】訪問(wèn)分區(qū)表的SQL必須包含分區(qū)鍵。
【建議】單個(gè)分區(qū)文件不超過(guò)2G,總大小不超過(guò)50G。建議總分區(qū)數(shù)不超過(guò)20個(gè)。
【強(qiáng)制】對(duì)于分區(qū)表執(zhí)行alter table操作,必須在業(yè)務(wù)低峰期執(zhí)行。
【強(qiáng)制】采用分庫(kù)策略的,庫(kù)的數(shù)量不能超過(guò)1024
【強(qiáng)制】采用分表策略的,表的數(shù)量不能超過(guò)4096
【建議】單個(gè)分表不超過(guò)500W行,ibd文件大小不超過(guò)2G,這樣才能讓數(shù)據(jù)分布式變得性能更佳。
【建議】水平分表盡量用取模方式,日志、報(bào)表類數(shù)據(jù)建議采用日期進(jìn)行分表。
2.1.6 字符集
【強(qiáng)制】數(shù)據(jù)庫(kù)本身庫(kù)、表、列所有字符集必須保持一致,為utf8或utf8mb4。
【強(qiáng)制】前端程序字符集或者環(huán)境變量中的字符集,與數(shù)據(jù)庫(kù)、表的字符集必須一致,統(tǒng)一為utf8。
程序?qū)覦AO設(shè)計(jì)建議
【建議】新的代碼不要用model,推薦使用手動(dòng)拼SQL+綁定變量傳入?yún)?shù)的方式。因?yàn)閙odel雖然可以使用面向?qū)ο蟮姆绞讲僮鱠b,但是其使用不當(dāng)很容易造成生成的SQL非常復(fù)雜,且model層自己做的強(qiáng)制類型轉(zhuǎn)換性能較差,最終導(dǎo)致數(shù)據(jù)庫(kù)性能下降。
【建議】前端程序連接MySQL或者redis,必須要有連接超時(shí)和失敗重連機(jī)制,且失敗重試必須有間隔時(shí)間。
【建議】前端程序報(bào)錯(cuò)里盡量能夠提示MySQL或redis原生態(tài)的報(bào)錯(cuò)信息,便于排查錯(cuò)誤。
【建議】對(duì)于有連接池的前端程序,必須根據(jù)業(yè)務(wù)需要配置初始、最小、最大連接數(shù),超時(shí)時(shí)間以及連接回收機(jī)制,否則會(huì)耗盡數(shù)據(jù)庫(kù)連接資源,造成線上事故。
【建議】對(duì)于log或history類型的表,隨時(shí)間增長(zhǎng)容易越來(lái)越大,因此上線前RD或者DBA必須建立表數(shù)據(jù)清理或歸檔方案。
【建議】在應(yīng)用程序設(shè)計(jì)階段,RD必須考慮并規(guī)避數(shù)據(jù)庫(kù)中主從延遲對(duì)于業(yè)務(wù)的影響。盡量避免從庫(kù)短時(shí)延遲(20秒以內(nèi))對(duì)業(yè)務(wù)造成影響,建議強(qiáng)制一致性的讀開啟事務(wù)走主庫(kù),或更新后過(guò)一段時(shí)間再去讀從庫(kù)。
【建議】多個(gè)并發(fā)業(yè)務(wù)邏輯訪問(wèn)同一塊數(shù)據(jù)(innodb表)時(shí),會(huì)在數(shù)據(jù)庫(kù)端產(chǎn)生行鎖甚至表鎖導(dǎo)致并發(fā)下降,因此建議更新類SQL盡量基于主鍵去更新。
【建議】業(yè)務(wù)邏輯之間加鎖順序盡量保持一致,否則會(huì)導(dǎo)致死鎖。
【建議】對(duì)于單表讀寫比大于10:1的數(shù)據(jù)行或單個(gè)列,可以將熱點(diǎn)數(shù)據(jù)放在緩存里(如mecache或redis),加快訪問(wèn)速度,降低MySQL壓力。
一個(gè)規(guī)范的建表語(yǔ)句示例
一個(gè)較為規(guī)范的建表語(yǔ)句為:
?CREATE TABLE user (`id` bigint(11) NOT NULL AUTO_INCREMENT,`user_id` bigint(11) NOT NULL COMMENT ‘用戶id’`username` varchar(45) NOT NULL COMMENT '真實(shí)姓名',`email` varchar(30) NOT NULL COMMENT ‘用戶郵箱’,`nickname` varchar(45) NOT NULL COMMENT '昵稱',`avatar` int(11) NOT NULL COMMENT '頭像',`birthday` date NOT NULL COMMENT '生日',`sex` tinyint(4) DEFAULT '0' COMMENT '性別',`short_introduce` varchar(150) DEFAULT NULL COMMENT '一句話介紹自己,最多50個(gè)漢字',`user_resume` varchar(300) NOT NULL COMMENT '用戶提交的簡(jiǎn)歷存放地址',`user_register_ip` int NOT NULL COMMENT ‘用戶注冊(cè)時(shí)的源ip’,`create_time` timestamp NOT NULL COMMENT ‘用戶記錄創(chuàng)建的時(shí)間’,`update_time` timestamp NOT NULL COMMENT ‘用戶資料修改的時(shí)間’,`user_review_status` tinyint NOT NULL COMMENT ‘用戶資料審核狀態(tài),1為通過(guò),2為審核中,3為未通過(guò),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 CHARSET=utf8 COMMENT='網(wǎng)站用戶基本信息';SQL編寫
DML語(yǔ)句
【強(qiáng)制】SELECT語(yǔ)句必須指定具體字段名稱,禁止寫成*。因?yàn)閟elect *會(huì)將不該讀的數(shù)據(jù)也從MySQL里讀出來(lái),造成網(wǎng)卡壓力。且表字段一旦更新,但model層沒(méi)有來(lái)得及更新的話,系統(tǒng)會(huì)報(bào)錯(cuò)。
【強(qiáng)制】insert語(yǔ)句指定具體字段名稱,不要寫成insert into t1 values(…),道理同上。
【建議】insert into…values(XX),(XX),(XX)…。這里XX的值不要超過(guò)5000個(gè)。值過(guò)多雖然上線很很快,但會(huì)引起主從同步延遲。
【建議】SELECT語(yǔ)句不要使用UNION,推薦使用UNION ALL,并且UNION子句個(gè)數(shù)限制在5個(gè)以內(nèi)。因?yàn)閡nion all不需要去重,節(jié)省數(shù)據(jù)庫(kù)資源,提高性能。
【建議】in值列表限制在500以內(nèi)。例如select… where userid in(….500個(gè)以內(nèi)…),這么做是為了減少底層掃描,減輕數(shù)據(jù)庫(kù)壓力從而加速查詢。
【建議】事務(wù)里批量更新數(shù)據(jù)需要控制數(shù)量,進(jìn)行必要的sleep,做到少量多次。
【強(qiáng)制】事務(wù)涉及的表必須全部是innodb表。否則一旦失敗不會(huì)全部回滾,且易造成主從庫(kù)同步終端。
【強(qiáng)制】寫入和事務(wù)發(fā)往主庫(kù),只讀SQL發(fā)往從庫(kù)。
【強(qiáng)制】除靜態(tài)表或小表(100行以內(nèi)),DML語(yǔ)句必須有where條件,且使用索引查找。
【強(qiáng)制】生產(chǎn)環(huán)境禁止使用hint,如sql_no_cache,force index,ignore key,straight join等。因?yàn)閔int是用來(lái)強(qiáng)制SQL按照某個(gè)執(zhí)行計(jì)劃來(lái)執(zhí)行,但隨著數(shù)據(jù)量變化我們無(wú)法保證自己當(dāng)初的預(yù)判是正確的,因此我們要相信MySQL優(yōu)化器!
【強(qiáng)制】where條件里等號(hào)左右字段類型必須一致,否則無(wú)法利用索引。
【建議】SELECT|UPDATE|DELETE|REPLACE要有WHERE子句,且WHERE子句的條件必需使用索引查找。
【強(qiáng)制】生產(chǎn)數(shù)據(jù)庫(kù)中強(qiáng)烈不推薦大表上發(fā)生全表掃描,但對(duì)于100行以下的靜態(tài)表可以全表掃描。查詢數(shù)據(jù)量不要超過(guò)表行數(shù)的25%,否則不會(huì)利用索引。
【強(qiáng)制】WHERE 子句中禁止只使用全模糊的LIKE條件進(jìn)行查找,必須有其他等值或范圍查詢條件,否則無(wú)法利用索引。
【建議】索引列不要使用函數(shù)或表達(dá)式,否則無(wú)法利用索引。如where length(name)='Admin'或where user_id+2=10023。
【建議】減少使用or語(yǔ)句,可將or語(yǔ)句優(yōu)化為union,然后在各個(gè)where條件上建立索引。如where a=1 or b=2優(yōu)化為where a=1… union …where b=2, key(a),key(b)。
【建議】分頁(yè)查詢,當(dāng)limit起點(diǎn)較高時(shí),可先用過(guò)濾條件進(jìn)行過(guò)濾。如select a,b,c from t1 limit 10000,20;優(yōu)化為:?select a,b,c from t1 where id>10000 limit 20;。
多表連接
【強(qiáng)制】禁止跨db的join語(yǔ)句。因?yàn)檫@樣可以減少模塊間耦合,為數(shù)據(jù)庫(kù)拆分奠定堅(jiān)實(shí)基礎(chǔ)。
【強(qiáng)制】禁止在業(yè)務(wù)的更新類SQL語(yǔ)句中使用join,比如update t1 join t2…。
【建議】不建議使用子查詢,建議將子查詢SQL拆開結(jié)合程序多次查詢,或使用join來(lái)代替子查詢。
【建議】線上環(huán)境,多表join不要超過(guò)3個(gè)表。
【建議】多表連接查詢推薦使用別名,且SELECT列表中要用別名引用字段,數(shù)據(jù)庫(kù).表格式,如select a from db1.table1 alias1 where …。
【建議】在多表join中,盡量選取結(jié)果集較小的表作為驅(qū)動(dòng)表,來(lái)join其他表。
事務(wù)
【建議】事務(wù)中INSERT|UPDATE|DELETE|REPLACE語(yǔ)句操作的行數(shù)控制在2000以內(nèi),以及WHERE子句中IN列表的傳參個(gè)數(shù)控制在500以內(nèi)。
【建議】批量操作數(shù)據(jù)時(shí),需要控制事務(wù)處理間隔時(shí)間,進(jìn)行必要的sleep,一般建議值5-10秒。
【建議】對(duì)于有auto_increment屬性字段的表的插入操作,并發(fā)需要控制在200以內(nèi)。
【強(qiáng)制】程序設(shè)計(jì)必須考慮“數(shù)據(jù)庫(kù)事務(wù)隔離級(jí)別”帶來(lái)的影響,包括臟讀、不可重復(fù)讀和幻讀。線上建議事務(wù)隔離級(jí)別為repeatable-read。
【建議】事務(wù)里包含SQL不超過(guò)5個(gè)(支付業(yè)務(wù)除外)。因?yàn)檫^(guò)長(zhǎng)的事務(wù)會(huì)導(dǎo)致鎖數(shù)據(jù)較久,MySQL內(nèi)部緩存、連接消耗過(guò)多等雪崩問(wèn)題。
【建議】事務(wù)里更新語(yǔ)句盡量基于主鍵或unique key,如update … where id=XX; 否則會(huì)產(chǎn)生間隙鎖,內(nèi)部擴(kuò)大鎖定范圍,導(dǎo)致系統(tǒng)性能下降,產(chǎn)生死鎖。
【建議】盡量把一些典型外部調(diào)用移出事務(wù),如調(diào)用webservice,訪問(wèn)文件存儲(chǔ)等,從而避免事務(wù)過(guò)長(zhǎng)。
【建議】對(duì)于MySQL主從延遲嚴(yán)格敏感的select語(yǔ)句,請(qǐng)開啟事務(wù)強(qiáng)制訪問(wèn)主庫(kù)。
排序和分組
【建議】減少使用order by,和業(yè)務(wù)溝通能不排序就不排序,或?qū)⑴判蚍诺匠绦蚨巳プ觥rder by、group by、distinct這些語(yǔ)句較為耗費(fèi)CPU,數(shù)據(jù)庫(kù)的CPU資源是極其寶貴的。
【建議】order by、group by、distinct這些SQL盡量利用索引直接檢索出排序好的數(shù)據(jù)。如where a=1 order by可以利用key(a,b)。
【建議】包含了order by、group by、distinct這些查詢的語(yǔ)句,where條件過(guò)濾出來(lái)的結(jié)果集請(qǐng)保持在1000行以內(nèi),否則SQL會(huì)很慢。
線上禁止使用的SQL語(yǔ)句
【高危】禁用update|delete t1 … where a=XX limit XX;?這種帶limit的更新語(yǔ)句。因?yàn)闀?huì)導(dǎo)致主從不一致,導(dǎo)致數(shù)據(jù)錯(cuò)亂。建議加上order by PK。
【高危】禁止使用關(guān)聯(lián)子查詢,如update t1 set … where name in(select name from user where…);效率極其低下。
【強(qiáng)制】禁用procedure、function、trigger、views、event、外鍵約束。因?yàn)樗麄兿臄?shù)據(jù)庫(kù)資源,降低數(shù)據(jù)庫(kù)實(shí)例可擴(kuò)展性。推薦都在程序端實(shí)現(xiàn)。
【強(qiáng)制】禁用insert into …on duplicate key update…在高并發(fā)環(huán)境下,會(huì)造成主從不一致。
【強(qiáng)制】禁止聯(lián)表更新語(yǔ)句,如update t1,t2 where t1.id=t2.id…。
?
?
總結(jié)
以上是生活随笔為你收集整理的这篇数据库设计规范建议,我必须分享给你的全部?jī)?nèi)容,希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇: GPU架构杂乱备忘——IMR、TBR、T
- 下一篇: FT232RL USB串口与GP232R