數(shù)據(jù)庫設(shè)計幾個規(guī)范：

數(shù)據(jù)庫命名規(guī)范、數(shù)據(jù)庫基本設(shè)計規(guī)范、數(shù)據(jù)庫索引設(shè)計規(guī)范、數(shù)據(jù)庫字段設(shè)計規(guī)范、數(shù)據(jù)庫SQL開發(fā)規(guī)范、數(shù)據(jù)庫操作行為規(guī)范

數(shù)據(jù)庫命名規(guī)范

1、所有數(shù)據(jù)庫對象名稱必須使用小寫字母并用下劃線分割

不同的數(shù)據(jù)庫名 DbName dbname

不同的表名 Table table tabLe

2、所有數(shù)據(jù)庫對象名稱禁止使用MySQL保留關(guān)鍵字

select id,username,from,age from tb_user from是關(guān)鍵字 ，但是可以用單引號括起來

3、數(shù)據(jù)庫對象的命名要見名識義，并且最好不要超過32個字符

例如 用戶數(shù)據(jù)庫mc_userdb 用戶賬號表 user_account

4、臨時庫表必須以tmp為前綴并以日期為后綴

5、備份庫、備份表必須以bak為前綴并以日期為前綴

6、所有存儲相同數(shù)據(jù)的列名和列類型必須一致

比如兩張表中有相同的數(shù)據(jù)，那么他們列名和列類型必須是一致的

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

1、所有表必須使用Innodb存儲引擎，mysql 5.6后默認(rèn)存儲引擎，支持事務(wù)，行級鎖，更好的恢復(fù)性，高并發(fā)下性能更好

2、數(shù)據(jù)庫和表的字符集統(tǒng)一使用UTF8，避免由于字符集轉(zhuǎn)換產(chǎn)生的亂碼

mysql中utf8字符集漢字占3個字節(jié)，ASCII碼占用1個字節(jié)

3、所有表和字段都需要添加注釋，使用comment從句添加表和列的備注

4、盡量控制單表數(shù)據(jù)量的大小，建議控制在500萬以內(nèi)，500萬并不是mysql數(shù)據(jù)庫的限制，

mysql最多可以存儲多少萬數(shù)據(jù)？目前沒有做限制，這種限制取決于存儲設(shè)置和文件系統(tǒng)

可以用歷史數(shù)據(jù)歸檔，分庫分表等手段來控制數(shù)據(jù)量大小

5、謹(jǐn)慎使用mysql分區(qū)表

分區(qū)表在物理上表現(xiàn)為多個文件沒在邏輯上表現(xiàn)為一個表

謹(jǐn)慎選擇分區(qū)鍵，跨分區(qū)查詢效率可能更低

建議采用物理分表的方式管理大數(shù)據(jù)

6、盡量做到冷熱數(shù)據(jù)分離，減小表的寬度

mysql限制最多存儲4096列，為了是減少磁盤IO，保證熱數(shù)據(jù)的內(nèi)存緩存命中率，利用更有效的利用緩存，避免讀入無用的冷數(shù)據(jù)

經(jīng)常使用的列放在一個表中

7、禁止在表中建立預(yù)留字段

預(yù)留字段的命名很難做到見名識義

預(yù)留字段無法確認(rèn)存儲的數(shù)據(jù)類型，所以無法選擇合適的類型

對預(yù)留字段類型的修改，會對表進(jìn)行鎖定，修改字段類型的成本往往大于增加

8、禁止在數(shù)據(jù)庫中存儲圖片、文件等二進(jìn)制數(shù)據(jù)

9、禁止在線上做數(shù)據(jù)庫壓力測試

10、禁止從開發(fā)環(huán)境，測試環(huán)境直連生產(chǎn)環(huán)境數(shù)據(jù)庫

索引設(shè)計規(guī)范

索引對數(shù)據(jù)庫的查詢性能來說非常重要，查詢、更新，禁止濫用索引

1、限制每張表上的索引數(shù)量，建議單張表索引不超過5個

索引并不是越多越好的，索引可以提高效率同樣可以降低效率

索引可以增加查詢效率，但同樣也會降低插入和更新的效率

2、禁止給表中的每一列都建立單獨(dú)的索引

3、Innodb是按照主鍵索引的順序來組織表的

4、每個Innodb表必須有一個主鍵

不使用更新頻繁的列作為主鍵，不使用多列主鍵

不使用uuid、MD5、hash，字符串列作為主鍵

主鍵建議使用主鍵自增

常見的索引列建議：

select、update、delete語句的where從句中的列

包含在order by、group by 、destinct中的字段

多表join的關(guān)聯(lián)列

如何選擇索引列的順序：

聯(lián)合索引是從做到右的順序來實(shí)現(xiàn)的

1、區(qū)分度最高的列放在聯(lián)合索引的最左側(cè)

2、盡量把字段長度小的列放在聯(lián)合索引的最左側(cè)

3、使用最頻繁的列放到聯(lián)合索引的左側(cè)

5、避免建立冗余索引和重復(fù)索引

重復(fù)索引：如 primary key(id)、index(id)、unique index(id)

冗余索引：如 index(a,b,c) index(a,b) index(a)

6、對于頻繁的查詢優(yōu)先使用覆蓋索引

覆蓋索引：就是包括了所有查詢字段的索引

優(yōu)點(diǎn)：避免Innodb表進(jìn)行索引的二次查找，可以把隨機(jī)IO變?yōu)轫樞騃O加快查詢效率

7、盡量避免使用外鍵

不建議使用外鍵約束，但一定在表與表之間的關(guān)聯(lián)鍵上建立索引

外鍵可用于保證數(shù)據(jù)的參照完整性，但建議在業(yè)務(wù)端實(shí)現(xiàn)

外鍵會影響父表和子表的寫操作從而降低性能

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

1、優(yōu)先選擇符合存儲需要的最小的數(shù)據(jù)類型

將字符串轉(zhuǎn)化為數(shù)字類型存儲

對于非負(fù)數(shù)據(jù)采用無符號整形進(jìn)行存儲

signed int -2147483648-2147483648

unsigned int 0-2147483648

有符號比無符號多出一倍的存儲空間

varchar(n) n代表字符數(shù)，不是字節(jié)數(shù)，varchar(255)=765個字節(jié)

過大的長度會消耗更多的內(nèi)存

2、避免使用text\BLOB數(shù)據(jù)類型

建議text\BLOB列分離到單獨(dú)的擴(kuò)展表中

text\BLOB類型只能使用前綴索引

3、避免使用enum數(shù)據(jù)類型

修改enum需要使用alter語句

enum類型的order by操作效率低，需要額外操作

禁止使用數(shù)值作為enum的枚舉值

4、盡可能把所有列定義為not null

索引null列需要額外的空間來保存，所以要占用更多的空間

進(jìn)行比較和計算時要對null值做特別的處理

5、禁止字符串存儲日期型的數(shù)據(jù)

缺點(diǎn)1：無法用日期函數(shù)進(jìn)行計算和比較

缺點(diǎn)2：用字符串存儲日期要占用更多的空間

使用timestamp或datetime類型存儲時間，timestamp存儲空間更小

6、財務(wù)的相關(guān)金額使用decimal類型

decimal類型為精準(zhǔn)浮點(diǎn)數(shù)，在計算時不會丟失精度

float、double非精準(zhǔn)浮點(diǎn)數(shù)

數(shù)據(jù)庫SQL開發(fā)規(guī)范

1、建議使用預(yù)編譯語句進(jìn)行數(shù)據(jù)庫操作

好處：只傳參數(shù)，比傳遞SQL語句更高效

相同語句可以一次解析，多次使用，提高處理效率

2、避免數(shù)據(jù)類型的隱式轉(zhuǎn)換

隱式轉(zhuǎn)換會導(dǎo)致索引失效

3、利用表上已經(jīng)存在的索引

4、禁止連接不同的數(shù)據(jù)庫使用不同的賬號，禁止垮庫查詢

為數(shù)據(jù)庫遷移和分庫分表留出余地

減低業(yè)務(wù)耦合度

避免權(quán)限過大而產(chǎn)生的安全風(fēng)險

5、禁止使用select * 必須用select 字段列表 查詢

消耗更多的CPU和io以及網(wǎng)絡(luò)帶寬資源

無法使用覆蓋索引

可減少表結(jié)構(gòu)變更帶來的影響

6、禁止使用不含字段列表的insert語句

如，insert into t values(‘a(chǎn)’,’b’,’c’)

7、避免使用子查詢，可以把子查詢優(yōu)化為join操作

子查詢的結(jié)果集無法使用索引

子查詢會產(chǎn)生臨時表，如果子查詢數(shù)據(jù)量大則嚴(yán)重影響效率

消耗過多的CPU和io資源

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

每join一個表會多占用一部分內(nèi)存join_buffer_size

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

mysql最多關(guān)聯(lián)61個表，建議不超過5個

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

10、禁止使用order by rand()進(jìn)行隨機(jī)排序

11、where從句中禁止對列進(jìn)行函數(shù)轉(zhuǎn)換和計算

對列進(jìn)行函數(shù)轉(zhuǎn)換和計算會導(dǎo)致無法使用索引

如：where date(createtime)=’20160901’

優(yōu)化后：where createtime>=’20160901’

目錄展示

需要更多mysql相關(guān)資料，關(guān)注公眾號：Java架構(gòu)師聯(lián)盟，后臺回復(fù)mysql即可獲取，更可以查看本文檔的獲取方式