我們?cè)陧?xiàng)目一開始的設(shè)計(jì)中，就要忙著考慮數(shù)據(jù)庫(kù)的設(shè)計(jì)，表、字段、索引、sql等等，而在項(xiàng)目比較大型的時(shí)候，團(tuán)隊(duì)開發(fā)中由于多人同時(shí)進(jìn)行，那么盡早的進(jìn)行設(shè)計(jì)規(guī)范是項(xiàng)目開發(fā)非常關(guān)鍵的一步，那么關(guān)于數(shù)據(jù)庫(kù)設(shè)計(jì)規(guī)范有哪些呢，包括以下6項(xiàng)：

? ? ? ?1.數(shù)據(jù)庫(kù)命名規(guī)范

　　2.數(shù)據(jù)庫(kù)基本設(shè)計(jì)規(guī)范

　　3.數(shù)據(jù)庫(kù)索引設(shè)計(jì)規(guī)范

　　4.數(shù)據(jù)庫(kù)字段設(shè)計(jì)規(guī)范

　　5.SQL開發(fā)規(guī)范

　　6.數(shù)據(jù)庫(kù)操作行為規(guī)范

接下來(lái)逐一詳細(xì)介紹一下各個(gè)規(guī)范：

1.數(shù)據(jù)庫(kù)命名規(guī)范：

1）所有數(shù)據(jù)庫(kù)對(duì)象名稱必須使用小寫字母并用下劃線分割（MySQL對(duì)大小寫是敏感的）

2）禁止使用MySQL的保留關(guān)鍵字（比如：select user,from,age from tb_user? 這時(shí)候識(shí)別不出from關(guān)鍵字，除非使用`from`，反向單引號(hào)來(lái)區(qū)別）

　　3）數(shù)據(jù)庫(kù)對(duì)象的命名最后能夠見名識(shí)義，并且最好不要超過(guò)32個(gè)字符?，例如：用戶數(shù)據(jù)庫(kù) bd_userdb (百度_用戶數(shù)據(jù)庫(kù)) ，用戶賬號(hào)表 user_account

4）臨時(shí)庫(kù)表必須以tmp前綴并以日期為后綴，tmp_user_20180505 提供更加明確的表名

備份庫(kù)，備份表必須以bak前綴并以日期為后綴，bak_userdb_20180504? bak_user_20180505

　　5）所有存儲(chǔ)相同數(shù)據(jù)的列名和類型必須一致，例如：不同庫(kù)表中的user_id（int unsigned not null），那么名稱和類型必須是一致的，否則會(huì)產(chǎn)生查詢效率降低等各種問(wèn)題

2.數(shù)據(jù)庫(kù)基本設(shè)計(jì)規(guī)范：

1）所有表必須使用InnoDB存儲(chǔ)引擎。在MySQL5.6以后，innodb已經(jīng)成為了默認(rèn)存儲(chǔ)引擎，它支持事務(wù)、行級(jí)鎖，更好的恢復(fù)性，高并發(fā)下性能更好

2）數(shù)據(jù)庫(kù)和表的字符集統(tǒng)一使用UTF-8。為了避免亂碼、性能等問(wèn)題

3）所有的表和字段都需要添加注釋。使用comment添加備注信息，從一開始就進(jìn)行數(shù)據(jù)庫(kù)字典的維護(hù)

4）盡量控制單表數(shù)據(jù)量的大小，建議控制在500萬(wàn)行以內(nèi)。使用歷史數(shù)據(jù)歸檔、分庫(kù)分表操作手段來(lái)控制數(shù)據(jù)量的大小

　　5）謹(jǐn)慎使用MySQL中的分區(qū)表?？绶謪^(qū)查詢效率比較低，建議采用物理分表的方式來(lái)管理大數(shù)據(jù)

　　6）盡量做到冷數(shù)據(jù)分離，減小表寬度。字段太多的情況，盡量分表，將常用的放在一塊，不常用的字段分到其他表中，有效減少磁盤的IO，保證熱數(shù)據(jù)的緩存命中率

7）禁止在表中建立預(yù)留字段。由于無(wú)法預(yù)知預(yù)留字段的類型，后期對(duì)改字段進(jìn)行修改會(huì)耗費(fèi)很多資源，對(duì)表進(jìn)行鎖定等問(wèn)題

8）禁止存儲(chǔ)圖片、文件等二進(jìn)制文件，造成MySQL的性能影響。這些應(yīng)該存儲(chǔ)到專門的圖片、文件服務(wù)器中，數(shù)據(jù)庫(kù)中只存儲(chǔ)對(duì)應(yīng)的信息標(biāo)識(shí)。

禁止在線上做數(shù)據(jù)庫(kù)壓力測(cè)試

?禁止從開發(fā)環(huán)境、測(cè)試環(huán)境直連生成環(huán)境數(shù)據(jù)庫(kù)

3.索引設(shè)計(jì)規(guī)范：

1）限制每張表索引的數(shù)量，建議單表索引不超過(guò)5個(gè)。索引并不是越多越好，能提高查詢效率，也能降低效率。應(yīng)該根據(jù)實(shí)際情況來(lái)建立索引。

2）每個(gè)InnoDB表中必須有一個(gè)主鍵（唯一非空列）。不用使用頻繁更新的列為主鍵，不使用MD5，UUID,HASH,字符串列作為主鍵。主鍵建議選擇使用自增ID值

　　3）常見索引列建議：where從句中的列 order by、group by、distinct 中的字段，多表join的關(guān)聯(lián)列，如果在字句中是單個(gè)列，那就單獨(dú)索引，有多個(gè)列，那可以建立聯(lián)合索引

4）如何選擇索引列的順序，區(qū)分度最高（比如主鍵列）的列，字段長(zhǎng)度小，使用頻繁的列放在聯(lián)合索引的最左側(cè)

　　5）避免建立冗余和重復(fù)的索引：index（a,b,c），index(a,b)，index(a) 對(duì)于a就是重復(fù)索引

　　6）對(duì)于頻繁的查詢優(yōu)先考慮使用覆蓋索引：包含了所有查詢字段的索引

　　7）盡量避免使用外鍵約束。外鍵會(huì)影響父表與子表的寫操作從而降低性能

4.數(shù)據(jù)庫(kù)字段設(shè)計(jì)規(guī)范：

　　1）優(yōu)先選擇符合存儲(chǔ)需要的最小數(shù)據(jù)類型。例如：將字符串轉(zhuǎn)化為數(shù)字存儲(chǔ)

　　? ? 對(duì)于非負(fù)數(shù)優(yōu)先選用無(wú)符號(hào)型來(lái)存儲(chǔ)。例如:主鍵id，無(wú)符號(hào)比有符號(hào)多出一倍的存儲(chǔ)空間。

　　　有符號(hào)：signed int -2147483648 ~ 2147483647

　　　無(wú)符號(hào)：unsigned int? 0 ~ 4294967295??

　　　varchar(N) N代表的是字符數(shù)，而不是字節(jié)數(shù)，使用UTF8存儲(chǔ)漢字varchar(255)=765個(gè)字節(jié)

　　　過(guò)大的長(zhǎng)度會(huì)消耗更多的內(nèi)存，根據(jù)字段長(zhǎng)度來(lái)分配內(nèi)存。

2）避免使用Text、Blob數(shù)據(jù)類型，若需要使用，盡量分配到專門的擴(kuò)展表中

3）避免使用Enum枚舉類型。order by操作效率低。禁止使用數(shù)值作為ENum枚舉值

4）盡可能把所有列定義為NOT NULL。索引NULL列需要更多的存儲(chǔ)空間來(lái)保存。索引會(huì)失效。

5）避免使用字符串來(lái)存儲(chǔ)日期時(shí)間，使用TIMESTAMP或DATATIME來(lái)存儲(chǔ)時(shí)間

6）與財(cái)務(wù)相關(guān)的金額類型數(shù)據(jù)，必須使用Decimal類型。保證數(shù)據(jù)精度，計(jì)算時(shí)不丟失精度。

?5.數(shù)據(jù)庫(kù)SQL開發(fā)規(guī)范：

1）建議使用預(yù)編譯語(yǔ)句進(jìn)行數(shù)據(jù)庫(kù)操作。只傳參數(shù)，比傳遞sql更加高效，相同語(yǔ)句一次解析之后，多次使用，節(jié)約sql解析的成本，提高處理效率。

2）避免數(shù)據(jù)類型的隱式轉(zhuǎn)換。隱式轉(zhuǎn)換導(dǎo)致索引失效，一般在where字句條件中出現(xiàn)的類型轉(zhuǎn)換，導(dǎo)致了索引失效。

　　3）合理利用已存在索引，而不是盲目添加索引。

　　　　避免使用雙%的查詢條件：like '%123%'，只要出現(xiàn)前綴%，索引失效。

　　　　一個(gè)SQL只能利用到復(fù)合索引的一列進(jìn)行范圍查詢，若聯(lián)合索引 index(a,b,c) 對(duì)a進(jìn)行范圍查詢，那么b和c將失效，應(yīng)當(dāng)將a放到最右側(cè)

　　　　使用left join 或 not exists 來(lái)優(yōu)化 not in 操作，not in會(huì)使索引失效

4）程序連接不同數(shù)據(jù)庫(kù)時(shí)應(yīng)該使用不同的賬號(hào)，禁止跨庫(kù)查詢

　　5）禁止使用 select * 必須使用 select <字段列表> 查詢，消耗過(guò)多的IO和cpu以及網(wǎng)絡(luò)帶寬資源

　　6）禁止使用不含字段的insert 語(yǔ)句,為了減少表結(jié)構(gòu)的變更帶來(lái)的影響：insert into table values('a','b','c'); 應(yīng)當(dāng)指明要插入的列，insert into table(c1,c2,c3) values('a','b','c');

　　7）避免使用子查詢，可以將子查詢優(yōu)化為join操作：子查詢都會(huì)創(chuàng)建臨時(shí)表，占用cpu和io資源，子查詢結(jié)果集無(wú)法使用索引。

　　8）避免使用join關(guān)聯(lián)太多的表：

　　　　每關(guān)聯(lián)一張表，多占用一部分內(nèi)存（join_buffer_size）

　　　　會(huì)產(chǎn)生臨時(shí)表操作，影響查詢效率

MySQL最多允許關(guān)聯(lián)61張表，建議不超過(guò)5張表

9）減少同數(shù)據(jù)庫(kù)的交互次數(shù)

　　10）使用in代替or。in的值不超過(guò)500個(gè)，in可以有效使用索引，or不行。

11）禁止使用order by rand() 進(jìn)行隨機(jī)排序，這個(gè)操作對(duì)性能有很大影響，盡量通過(guò)程序來(lái)得到隨機(jī)值再?gòu)臄?shù)據(jù)庫(kù)中獲取數(shù)據(jù)。

12）禁止在where從句中對(duì)列進(jìn)行函數(shù)轉(zhuǎn)換和計(jì)算，造成索引的失效。where data(createtime) = '2018-01-01' ，盡量在程序中進(jìn)行計(jì)算

　　13）在明顯不會(huì)出現(xiàn)重復(fù)值的時(shí)候使用union all 而不是union。union會(huì)先加載所有數(shù)據(jù)到臨時(shí)表中然后去重，而union all不會(huì)去重。

14）拆分復(fù)雜的大SQL成多個(gè)小SQL。并行執(zhí)行小SQL來(lái)提高處理效率

?6.數(shù)據(jù)庫(kù)操作行為規(guī)范：

1）超過(guò)100萬(wàn)行的批量寫操作，要分批多次進(jìn)行操作：

　　　　大批量操作可能造成嚴(yán)重的主從延遲問(wèn)題

　　　　binlog日志為row格式時(shí)，胡產(chǎn)生大量的日志，造成資源不足

　　　　避免產(chǎn)生大事務(wù)的操作

2）對(duì)于大表使用pt-online-schema-change工具來(lái)修改表結(jié)構(gòu)。過(guò)程是：先創(chuàng)建新表，然后復(fù)制舊表數(shù)據(jù)到新表，將新表名稱改成舊表名稱，最后刪除舊表

3）禁止為程序使用的賬號(hào)賦予super超管權(quán)限

4）對(duì)于程序連接數(shù)據(jù)庫(kù)賬號(hào)，遵循權(quán)限最小的原則。程序使用數(shù)據(jù)庫(kù)支行和只能在一個(gè)DB下使用，不準(zhǔn)跨庫(kù)，程序使用的賬號(hào)原則上不準(zhǔn)有drop權(quán)限

以上就是MySQL的一些設(shè)計(jì)規(guī)范，當(dāng)然不是說(shuō)一定要遵循以上的原則，具體視實(shí)際應(yīng)用場(chǎng)景而定，通過(guò)DBA指導(dǎo)來(lái)指定原則。

本文原創(chuàng)，轉(zhuǎn)載請(qǐng)標(biāo)注出處：http://www.cnblogs.com/Luke-Me/p/8994432.html