1.總則
為規(guī)范公司各環(huán)境中數(shù)據(jù)庫系統(tǒng)的設(shè)計與創(chuàng)建流程,提高數(shù)據(jù)庫系統(tǒng)的開發(fā)效率,保障數(shù)據(jù)庫系統(tǒng)性能和安全穩(wěn)定的運行,特制定本規(guī)范。
該規(guī)范涉及PM,RD,SQ,DBA四個角色,每個角色在數(shù)據(jù)庫系統(tǒng)設(shè)計與創(chuàng)建過程中的工作內(nèi)容會按照【高危】、【強(qiáng)制】、【建議】三個級別進(jìn)行標(biāo)注,遵守優(yōu)先級從高到低。
對于不符合【高危】和【強(qiáng)制】兩個級別的工作內(nèi)容,DBA會強(qiáng)制打回要求修改。
2.適用范圍
本規(guī)范所定義的數(shù)據(jù)庫系統(tǒng)包括部署在傳統(tǒng)數(shù)據(jù)中心(Cisco UCS 等物理機(jī))和云(公有云、私有云)上的所有數(shù)據(jù)庫實例,相關(guān)數(shù)據(jù)庫實例參照相關(guān)規(guī)范進(jìn)行數(shù)據(jù)庫開發(fā)與創(chuàng)建的工作。
3.PM需遵守的數(shù)據(jù)庫設(shè)計與創(chuàng)建規(guī)范
本規(guī)范旨在幫助或指導(dǎo)RD、QA、OP等技術(shù)人員做出適合線上業(yè)務(wù)的數(shù)據(jù)庫設(shè)計和開發(fā)。在數(shù)據(jù)庫變更和處理流程、數(shù)據(jù)庫表設(shè)計、SQL編寫等方面予以規(guī)范,從而為公司業(yè)務(wù)系統(tǒng)穩(wěn)定、健康地運行提供保障。
3.1 MySQL設(shè)計開發(fā)規(guī)范
MySQL數(shù)據(jù)庫與 Oracle、 SQL Server 等商用數(shù)據(jù)庫相比,有其內(nèi)核上的優(yōu)勢與劣勢。我們在使用MySQL數(shù)據(jù)庫進(jìn)行設(shè)計開發(fā)的時候需要遵循一定規(guī)范,揚長避短。
3.1.1 命名規(guī)范
數(shù)據(jù)庫對象的命名規(guī)則的范圍為管理平臺設(shè)計開發(fā)所涉及的表,對于其他外部系統(tǒng)所創(chuàng)建的表不在本規(guī)范約束范圍內(nèi),
數(shù)據(jù)庫對象如表、列、序列、過程、函數(shù)等在命名時要遵循如下規(guī)則:
【強(qiáng)制】命名要使用富有意義中英文縮寫,要以字母開頭,不能超過32個字符。
【強(qiáng)制】命名必須使用小寫字母,
【強(qiáng)制】數(shù)據(jù)庫對象名稱由如下部分組成:范圍、類型、名稱實體,各詞匯間采用下劃線“_”連接。
【強(qiáng)制】其中各數(shù)據(jù)庫對象的范圍和類型的具體含義及取值詳見各數(shù)據(jù)庫對象的命名規(guī)則。
數(shù)據(jù)庫對象命名規(guī)則
【強(qiáng)制】數(shù)據(jù)庫對象的名稱不允許是數(shù)據(jù)庫的 保留字 和 關(guān)鍵字。
SELECT id, name, from, age FROM user 不可以
保留字和關(guān)鍵字詳見連接
Option/Variable Reference
3.1.2 建庫規(guī)范
【強(qiáng)制】創(chuàng)建數(shù)據(jù)庫時必須顯式指定字符集,并且字符集只能是utf8或者utf8mb4。創(chuàng)建數(shù)據(jù)庫SQL舉例:
create database db1 default character set utf8;
3.1.3 建表規(guī)范
【強(qiáng)制】創(chuàng)建表時必須顯式指定字符集為utf8或utf8mb4。
【強(qiáng)制】創(chuàng)建表時必須顯式指定表存儲引擎類型,如無特殊需求,一律為InnoDB。當(dāng)需要使用除InnoDB以外的存儲引擎時,必須通過DBA審核才能在生產(chǎn)環(huán)境中使用。因為Innodb表支持事務(wù)、行鎖、宕機(jī)恢復(fù)、MVCC等關(guān)系型數(shù)據(jù)庫重要特性,為業(yè)界使用最多的MySQL存儲引擎。而這是其他大多數(shù)存儲引擎不具備的,因此首推InnoDB。
【強(qiáng)制】建表必須有comment,從一開始就進(jìn)行數(shù)據(jù)詞典的維護(hù)。類status型需指明主要值的含義,如”0-離線,1-在線”
【強(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)生極大影響。
【建議】建表時關(guān)于主鍵:(1)強(qiáng)制要求主鍵為id,類型為int或bigint,且為auto_increment;(2)標(biāo)識表里每一行主體的字段不要設(shè)為主鍵,建議設(shè)為其他字段如user_id,order_id等,并建立unique key索引。因為如果設(shè)為主鍵且主鍵值為隨機(jī)插入,則會導(dǎo)致innodb內(nèi)部page分裂和大量隨機(jī)I/O,性能下降。
【建議】核心表(如用戶表,金錢相關(guān)的表)必須有行數(shù)據(jù)的創(chuàng)建時間字段create_time和最后更新時間字段update_time,便于查問題。
【建議】建議對表里的blob、text等大字段,垂直拆分到其他表里,僅在需要讀這些對象的時候才去select。
【建議】反范式設(shè)計:把經(jīng)常需要join查詢的字段,在其他表里冗余一份。如user_name屬性在user_account,user_login_log等表里冗余一份,減少join查詢。
3.1.4 字段規(guī)范
【建議】表中所有字段必須都是NOT NULL屬性,業(yè)務(wù)可以根據(jù)需要定義DEFAULT值。因為使用NULL值會存在每一行都會占用額外存儲空間、數(shù)據(jù)遷移容易出錯、聚合函數(shù)計算結(jié)果偏差等問題。如果不能保證insert時一定有值過來,定義時使用default值 ‘ ’ 或 0。
使用NUMBER類型是必須指定長度。由NUMBER得到精度與密度來保障數(shù)據(jù)的一致性
表中字段的命名長度不應(yīng)該超過30個字節(jié)
【建議】表中的自增列(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)換。