一、數據庫命令規範

所有數據庫對(duì)象名稱必須使用(yòng)小寫字母并用(yòng)下(xià)劃線分(fēn)割

所有數據庫對(duì)象名稱禁止使用(yòng)mysql保留關鍵字(如果表名中包含關鍵字查詢時(shí)，需要将其用(yòng)單引号括起來(lái))

數據庫對(duì)象的(de)命名要能做(zuò)到見名識意，并且最後不要超過32個(gè)字符

臨時(shí)庫表必須以tmp_爲前綴并以日期爲後綴，備份表必須以bak_爲前綴并以日期(時(shí)間戳)爲後綴

所有存儲相同數據的(de)列名和(hé)列類型必須一緻(一般作爲關聯列，如果查詢時(shí)關聯列類型不一緻會自動進行數據類型隐式轉換，會造成列上的(de)索引失效，導緻查詢效率降低)

二、數據庫基本設計規範

1、所有表必須使用(yòng)Innodb存儲引擎

沒有特殊要求(即Innodb無法滿足的(de)功能如：列存儲，存儲空間數據等)的(de)情況下(xià)，所有表必須使用(yòng)Innodb存儲引擎(mysql5.5之前默認使用(yòng)Myisam，5.6以後默認的(de)爲Innodb)Innodb 支持事務，支持行級鎖，更好的(de)恢複性，高(gāo)并發下(xià)性能更好

2、數據庫和(hé)表的(de)字符集統一使用(yòng)UTF8

兼容性更好，統一字符集可(kě)以避免由于字符集轉換産生的(de)亂碼，不同的(de)字符集進行比較前需要進行轉換會造成索引失效

3、所有表和(hé)字段都需要添加注釋

使用(yòng)comment從句添加表和(hé)列的(de)備注 從一開始就進行數據字典的(de)維護

4、盡量控制單表數據量的(de)大(dà)小，建議(yì)控制在500萬以内

500萬并不是MySQL數據庫的(de)限制，過大(dà)會造成修改表結構，備份，恢複都會有很大(dà)的(de)問題

可(kě)以用(yòng)曆史數據歸檔(應用(yòng)于日志數據)，分(fēn)庫分(fēn)表(應用(yòng)于業務數據)等手段來(lái)控制數據量大(dà)小

5、謹慎使用(yòng)MySQL分(fēn)區(qū)表

分(fēn)區(qū)表在物(wù)理(lǐ)上表現爲多(duō)個(gè)文件，在邏輯上表現爲一個(gè)表 謹慎選擇分(fēn)區(qū)鍵，跨分(fēn)區(qū)查詢效率可(kě)能更低 建議(yì)采用(yòng)物(wù)理(lǐ)分(fēn)表的(de)方式管理(lǐ)大(dà)數據

6、盡量做(zuò)到冷(lěng)熱(rè)數據分(fēn)離，減小表的(de)寬度

MySQL限制每個(gè)表最多(duō)存儲4096列，并且每一行數據的(de)大(dà)小不能超過65535字節 減少磁盤IO,保證熱(rè)數據的(de)内存緩存命中率(表越寬，把表裝載進内存緩沖池時(shí)所占用(yòng)的(de)内存也(yě)就越大(dà),也(yě)會消耗更多(duō)的(de)IO) 更有效的(de)利用(yòng)緩存，避免讀入無用(yòng)的(de)冷(lěng)數據 經常一起使用(yòng)的(de)列放到一個(gè)表中(避免更多(duō)的(de)關聯操作)

7、禁止在表中建立預留字段

預留字段的(de)命名很難做(zuò)到見名識義 預留字段無法确認存儲的(de)數據類型，所以無法選擇合适的(de)類型 對(duì)預留字段類型的(de)修改，會對(duì)表進行鎖定

8、禁止在數據庫中存儲圖片，文件等大(dà)的(de)二進制數據

通(tōng)常文件很大(dà)，會短時(shí)間内造成數據量快(kuài)速增長(cháng)，數據庫進行數據庫讀取時(shí)，通(tōng)常會進行大(dà)量的(de)随機IO操作，文件很大(dà)時(shí)，IO操作很耗時(shí) 通(tōng)常存儲于文件服務器，數據庫隻存儲文件地址信息

9、禁止在線上做(zuò)數據庫壓力測試

10、禁止從開發環境，測試環境直接連接生成環境數據庫

三、數據庫字段設計規範

1、優先選擇符合存儲需要的(de)最小的(de)數據類型

• 原因

列的(de)字段越大(dà)，建立索引時(shí)所需要的(de)空間也(yě)就越大(dà)，這(zhè)樣一頁中所能存儲的(de)索引節點的(de)數量也(yě)就越少也(yě)越少，在遍曆時(shí)所需要的(de)IO次數也(yě)就越多(duō)， 索引的(de)性能也(yě)就越差

• 方法

1)将字符串轉換成數字類型存儲，如：将IP地址轉換成整形數據。

mysql提供了(le)兩個(gè)方法來(lái)處理(lǐ)ip地址：

1. inet_aton 把ip轉爲無符号整型(4-8位)  
2. inet_ntoa 把整型的(de)ip轉爲地址 

插入數據前，先用(yòng)inet_aton把ip地址轉爲整型，可(kě)以節省空間。顯示數據時(shí)，使用(yòng)inet_ntoa把整型的(de)ip地址轉爲地址顯示即可(kě)。

2)對(duì)于非負型的(de)數據(如自增ID、整型IP)來(lái)說，要優先使用(yòng)無符号整型來(lái)存儲

因爲：無符号相對(duì)于有符号可(kě)以多(duō)出一倍的(de)存儲空間

1. SIGNED INT -2147483648~2147483647 
2. UNSIGNED INT 0~4294967295 

VARCHAR(N)中的(de)N代表的(de)是字符數，而不是字節數

使用(yòng)UTF8存儲255個(gè)漢字 Varchar(255)=765個(gè)字節。過大(dà)的(de)長(cháng)度會消耗更多(duō)的(de)内存

2、避免使用(yòng)TEXT、BLOB數據類型，最常見的(de)TEXT類型可(kě)以存儲64k的(de)數據

• 建議(yì)把BLOB或是TEXT列分(fēn)離到單獨的(de)擴展表中

Mysql内存臨時(shí)表不支持TEXT、BLOB這(zhè)樣的(de)大(dà)數據類型，如果查詢中包含這(zhè)樣的(de)數據，在排序等操作時(shí)，就不能使用(yòng)内存臨時(shí)表，必須使用(yòng)磁盤臨時(shí)表進行。

而且對(duì)于這(zhè)種數據，Mysql還(hái)是要進行二次查詢，會使sql性能變得(de)很差，但是不是說一定不能使用(yòng)這(zhè)樣的(de)數據類型。

如果一定要使用(yòng)，建議(yì)把BLOB或是TEXT列分(fēn)離到單獨的(de)擴展表中，查詢時(shí)一定不要使用(yòng)select * 而隻需要取出必要的(de)列，不需要TEXT列的(de)數據時(shí)不要對(duì)該列進行查詢。

• TEXT或BLOB類型隻能使用(yòng)前綴索引

因爲MySQL對(duì)索引字段長(cháng)度是有限制的(de)，所以TEXT類型隻能使用(yòng)前綴索引，并且TEXT列上是不能有默認值的(de)。

3、避免使用(yòng)ENUM類型

• 修改ENUM值需要使用(yòng)ALTER語句
• ENUM類型的(de)ORDER BY操作效率低，需要額外操作
• 禁止使用(yòng)數值作爲ENUM的(de)枚舉值

4、盡可(kě)能把所有列定義爲NOT NULL

原因：

索引NULL列需要額外的(de)空間來(lái)保存，所以要占用(yòng)更多(duō)的(de)空間;

進行比較和(hé)計算(suàn)時(shí)要對(duì)NULL值做(zuò)特别的(de)處理(lǐ)

5、使用(yòng)TIMESTAMP(4個(gè)字節)或DATETIME類型(8個(gè)字節)存儲時(shí)間

TIMESTAMP 存儲的(de)時(shí)間範圍 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。

TIMESTAMP 占用(yòng)4字節和(hé)INT相同，但比INT可(kě)讀性高(gāo)

超出TIMESTAMP取值範圍的(de)使用(yòng)DATETIME類型存儲。

經常會有人(rén)用(yòng)字符串存儲日期型的(de)數據(不正确的(de)做(zuò)法)：

• 缺點1：無法用(yòng)日期函數進行計算(suàn)和(hé)比較
• 缺點2：用(yòng)字符串存儲日期要占用(yòng)更多(duō)的(de)空間

6、同财務相關的(de)金額類數據必須使用(yòng)decimal類型

• 非精準浮點：float,double
• 精準浮點：decimal

Decimal類型爲精準浮點數，在計算(suàn)時(shí)不會丢失精度。占用(yòng)空間由定義的(de)寬度決定，每4個(gè)字節可(kě)以存儲9位數字，并且小數點要占用(yòng)一個(gè)字節。可(kě)用(yòng)于存儲比bigint更大(dà)的(de)整型數據。

四、索引設計規範

1、限制每張表上的(de)索引數量，建議(yì)單張表索引不超過5個(gè)

索引并不是越多(duō)越好!索引可(kě)以提高(gāo)效率同樣可(kě)以降低效率。

索引可(kě)以增加查詢效率，但同樣也(yě)會降低插入和(hé)更新的(de)效率，甚至有些情況下(xià)會降低查詢效率。

因爲mysql優化(huà)器在選擇如何優化(huà)查詢時(shí)，會根據統一信息，對(duì)每一個(gè)可(kě)以用(yòng)到的(de)索引來(lái)進行評估，以生成出一個(gè)最好的(de)執行計劃，如果同時(shí)有很多(duō)個(gè)索引都可(kě)以用(yòng)于查詢，就會增加mysql優化(huà)器生成執行計劃的(de)時(shí)間，同樣會降低查詢性能。

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

5.6版本之前，一個(gè)sql隻能使用(yòng)到一個(gè)表中的(de)一個(gè)索引，5.6以後，雖然有了(le)合并索引的(de)優化(huà)方式，但是還(hái)是遠(yuǎn)遠(yuǎn)沒有使用(yòng)一個(gè)聯合索引的(de)查詢方式好

3、每個(gè)Innodb表必須有個(gè)主鍵

Innodb是一種索引組織表：數據的(de)存儲的(de)邏輯順序和(hé)索引的(de)順序是相同的(de)。

每個(gè)表都可(kě)以有多(duō)個(gè)索引，但是表的(de)存儲順序隻能有一種 Innodb是按照(zhào)主鍵索引的(de)順序來(lái)組織表的(de)。

不要使用(yòng)更新頻(pín)繁的(de)列作爲主鍵，不适用(yòng)多(duō)列主鍵(相當于聯合索引) 不要使用(yòng)UUID、MD5、HASH、字符串列作爲主鍵(無法保證數據的(de)順序增長(cháng))。

主鍵建議(yì)使用(yòng)自增ID值。

五、常見索引列建議(yì)

• 出現在SELECT、UPDATE、DELETE語句的(de)WHERE從句中的(de)列
• 包含在ORDER BY、GROUP BY、DISTINCT中的(de)字段

并不要将符合1和(hé)2中的(de)字段的(de)列都建立一個(gè)索引，通(tōng)常将1、2中的(de)字段建立聯合索引效果更好

• 多(duō)表join的(de)關聯列

六、如何選擇索引列的(de)順序

建立索引的(de)目的(de)是：希望通(tōng)過索引進行數據查找，減少随機IO，增加查詢性能 ，索引能過濾出越少的(de)數據，則從磁盤中讀入的(de)數據也(yě)就越少。

• 區(qū)分(fēn)度最高(gāo)的(de)放在聯合索引的(de)最左側(區(qū)分(fēn)度=列中不同值的(de)數量/列的(de)總行數);
• 盡量把字段長(cháng)度小的(de)列放在聯合索引的(de)最左側(因爲字段長(cháng)度越小，一頁能存儲的(de)數據量越大(dà)，IO性能也(yě)就越好);
• 使用(yòng)最頻(pín)繁的(de)列放到聯合索引的(de)左側(這(zhè)樣可(kě)以比較少的(de)建立一些索引)。

七、避免建立冗餘索引和(hé)重複索引

因爲這(zhè)樣會增加查詢優化(huà)器生成執行計劃的(de)時(shí)間。

• 重複索引示例：primary key(id)、index(id)、unique index(id)
• 冗餘索引示例：index(a,b,c)、index(a,b)、index(a)

八、優先考慮覆蓋索引

對(duì)于頻(pín)繁的(de)查詢優先考慮使用(yòng)覆蓋索引。

覆蓋索引：就是包含了(le)所有查詢字段(where,select,ordery by,group by包含的(de)字段)的(de)索引

覆蓋索引的(de)好處：

• 避免Innodb表進行索引的(de)二次查詢

Innodb是以聚集索引的(de)順序來(lái)存儲的(de)，對(duì)于Innodb來(lái)說，二級索引在葉子節點中所保存的(de)是行的(de)主鍵信息，

如果是用(yòng)二級索引查詢數據的(de)話(huà)，在查找到相應的(de)鍵值後，還(hái)要通(tōng)過主鍵進行二次查詢才能獲取我們真實所需要的(de)數據。而在覆蓋索引中，二級索引的(de)鍵值中可(kě)以獲取所有的(de)數據，避免了(le)對(duì)主鍵的(de)二次查詢 ，減少了(le)IO操作，提升了(le)查詢效率。

• 可(kě)以把随機IO變成順序IO加快(kuài)查詢效率

由于覆蓋索引是按鍵值的(de)順序存儲的(de)，對(duì)于IO密集型的(de)範圍查找來(lái)說，對(duì)比随機從磁盤讀取每一行的(de)數據IO要少的(de)多(duō)，因此利用(yòng)覆蓋索引在訪問時(shí)也(yě)可(kě)以把磁盤的(de)随機讀取的(de)IO轉變成索引查找的(de)順序IO。

九、索引SET規範

盡量避免使用(yòng)外鍵約束

• 不建議(yì)使用(yòng)外鍵約束(foreign key)，但一定要在表與表之間的(de)關聯鍵上建立索引;
• 外鍵可(kě)用(yòng)于保證數據的(de)參照(zhào)完整性，但建議(yì)在業務端實現;
• 外鍵會影(yǐng)響父表和(hé)子表的(de)寫操作從而降低性能。

十、數據庫SQL開發規範

1、建議(yì)使用(yòng)預編譯語句進行數據庫操作

預編譯語句可(kě)以重複使用(yòng)這(zhè)些計劃，減少SQL編譯所需要的(de)時(shí)間，還(hái)可(kě)以解決動态SQL所帶來(lái)的(de)SQL注入的(de)問題 隻傳參數，比傳遞SQL語句更高(gāo)效 相同語句可(kě)以一次解析，多(duō)次使用(yòng)，提高(gāo)處理(lǐ)效率。

2、避免數據類型的(de)隐式轉換

隐式轉換會導緻索引失效。如：select name,phone from customer where id = '111';

3、充分(fēn)利用(yòng)表上已經存在的(de)索引

• 避免使用(yòng)雙%号的(de)查詢條件。

如a like '%123%'，(如果無前置%,隻有後置%，是可(kě)以用(yòng)到列上的(de)索引的(de))

• 一個(gè)SQL隻能利用(yòng)到複合索引中的(de)一列進行範圍查詢

如：有 a,b,c列的(de)聯合索引，在查詢條件中有a列的(de)範圍查詢，則在b,c列上的(de)索引将不會被用(yòng)到，在定義聯合索引時(shí)，如果a列要用(yòng)到範圍查找的(de)話(huà)，就要把a列放到聯合索引的(de)右側。

• 使用(yòng)left join或 not exists來(lái)優化(huà)not in操作

因爲not in 也(yě)通(tōng)常會使用(yòng)索引失效。

4、數據庫設計時(shí)，應該要對(duì)以後擴展進行考慮

5、程序連接不同的(de)數據庫使用(yòng)不同的(de)賬号，進制跨庫查詢

• 爲數據庫遷移和(hé)分(fēn)庫分(fēn)表留出餘地
• 降低業務耦合度
• 避免權限過大(dà)而産生的(de)安全風險

6、禁止使用(yòng)SELECT * 必須使用(yòng)SELECT <字段列表> 查詢

原因：

• 消耗更多(duō)的(de)CPU和(hé)IO以網絡帶寬資源
• 無法使用(yòng)覆蓋索引
• 可(kě)減少表結構變更帶來(lái)的(de)影(yǐng)響

7、禁止使用(yòng)不含字段列表的(de)INSERT語句

如：insert into values ('a','b','c');

應使用(yòng)insert into t(c1,c2,c3) values ('a','b','c');

8、避免使用(yòng)子查詢，可(kě)以把子查詢優化(huà)爲join操作

通(tōng)常子查詢在in子句中，且子查詢中爲簡單SQL(不包含union、group by、order by、limit從句)時(shí)，才可(kě)以把子查詢轉化(huà)爲關聯查詢進行優化(huà)。

子查詢性能差的(de)原因：

• 子查詢的(de)結果集無法使用(yòng)索引，通(tōng)常子查詢的(de)結果集會被存儲到臨時(shí)表中，不論是内存臨時(shí)表還(hái)是磁盤臨時(shí)表都不會存在索引，所以查詢性能會受到一定的(de)影(yǐng)響;
• 特别是對(duì)于返回結果集比較大(dà)的(de)子查詢，其對(duì)查詢性能的(de)影(yǐng)響也(yě)就越大(dà);
• 由于子查詢會産生大(dà)量的(de)臨時(shí)表也(yě)沒有索引，所以會消耗過多(duō)的(de)CPU和(hé)IO資源，産生大(dà)量的(de)慢(màn)查詢。

9、避免使用(yòng)JOIN關聯太多(duō)的(de)表

對(duì)于Mysql來(lái)說，是存在關聯緩存的(de)，緩存的(de)大(dà)小可(kě)以由join_buffer_size參數進行設置。

在Mysql中，對(duì)于同一個(gè)SQL多(duō)關聯(join)一個(gè)表，就會多(duō)分(fēn)配一個(gè)關聯緩存，如果在一個(gè)SQL中關聯的(de)表越多(duō)，所占用(yòng)的(de)内存也(yě)就越大(dà)。

如果程序中大(dà)量的(de)使用(yòng)了(le)多(duō)表關聯的(de)操作，同時(shí)join_buffer_size設置的(de)也(yě)不合理(lǐ)的(de)情況下(xià)，就容易造成服務器内存溢出的(de)情況，就會影(yǐng)響到服務器數據庫性能的(de)穩定性。

同時(shí)對(duì)于關聯操作來(lái)說，會産生臨時(shí)表操作，影(yǐng)響查詢效率Mysql最多(duō)允許關聯61個(gè)表，建議(yì)不超過5個(gè)。

10、減少同數據庫的(de)交互次數

數據庫更适合處理(lǐ)批量操作 合并多(duō)個(gè)相同的(de)操作到一起，可(kě)以提高(gāo)處理(lǐ)效率

11、對(duì)應同一列進行or判斷時(shí)，使用(yòng)in代替or

in的(de)值不要超過500個(gè)in操作可(kě)以更有效的(de)利用(yòng)索引，or大(dà)多(duō)數情況下(xià)很少能利用(yòng)到索引。

12、禁止使用(yòng)order by rand() 進行随機排序

會把表中所有符合條件的(de)數據裝載到内存中，然後在内存中對(duì)所有數據根據随機生成的(de)值進行排序，并且可(kě)能會對(duì)每一行都生成一個(gè)随機值，如果滿足條件的(de)數據集非常大(dà)，就會消耗大(dà)量的(de)CPU和(hé)IO及内存資源。

推薦在程序中獲取一個(gè)随機值，然後從數據庫中獲取數據的(de)方式

13、WHERE從句中禁止對(duì)列進行函數轉換和(hé)計算(suàn)

對(duì)列進行函數轉換或計算(suàn)時(shí)會導緻無法使用(yòng)索引。

不推薦：

1. where date(create_time)='20190101' 

推薦：

1. where create_time >= '20190101' and create_time < '20190102' 

14、在明(míng)顯不會有重複值時(shí)使用(yòng)UNION ALL而不是UNION

• UNION會把兩個(gè)結果集的(de)所有數據放到臨時(shí)表中後再進行去重操作
• UNION ALL不會再對(duì)結果集進行去重操作

15、拆分(fēn)複雜(zá)的(de)大(dà)SQL爲多(duō)個(gè)小SQL

• 大(dà)SQL：邏輯上比較複雜(zá)，需要占用(yòng)大(dà)量CPU進行計算(suàn)的(de)SQL
• MySQL：一個(gè)SQL隻能使用(yòng)一個(gè)CPU進行計算(suàn)
• SQL拆分(fēn)後可(kě)以通(tōng)過并行執行來(lái)提高(gāo)處理(lǐ)效率

十一、數據庫操作行爲規範

1、超100萬行的(de)批量寫(UPDATE、DELETE、INSERT)操作，要分(fēn)批多(duō)次進行操作

• 大(dà)批量操作可(kě)能會造成嚴重的(de)主從延遲

主從環境中，大(dà)批量操作可(kě)能會造成嚴重的(de)主從延遲，大(dà)批量的(de)寫操作一般都需要執行一定長(cháng)的(de)時(shí)間，而隻有當主庫上執行完成後，才會在其他(tā)從庫上執行，所以會造成主庫與從庫長(cháng)時(shí)間的(de)延遲情況

• binlog日志爲row格式時(shí)會産生大(dà)量的(de)日志

大(dà)批量寫操作會産生大(dà)量日志，特别是對(duì)于row格式二進制數據而言，由于在row格式中會記錄每一行數據的(de)修改，我們一次修改的(de)數據越多(duō)，産生的(de)日志量也(yě)就會越多(duō)，日志的(de)傳輸和(hé)恢複所需要的(de)時(shí)間也(yě)就越長(cháng)，這(zhè)也(yě)是造成主從延遲的(de)一個(gè)原因。

• 避免産生大(dà)事務操作

大(dà)批量修改數據，一定是在一個(gè)事務中進行的(de)，這(zhè)就會造成表中大(dà)批量數據進行鎖定，從而導緻大(dà)量的(de)阻塞，阻塞會對(duì)MySQL的(de)性能産生非常大(dà)的(de)影(yǐng)響。

特别是長(cháng)時(shí)間的(de)阻塞會占滿所有數據庫的(de)可(kě)用(yòng)連接，這(zhè)會使生産環境中的(de)其他(tā)應用(yòng)無法連接到數據庫，因此一定要注意大(dà)批量寫操作要進行分(fēn)批。

2、對(duì)于大(dà)表使用(yòng)pt-online-schema-change修改表結構

• 避免大(dà)表修改産生的(de)主從延遲
• 避免在對(duì)表字段進行修改時(shí)進行鎖表

對(duì)大(dà)表數據結構的(de)修改一定要謹慎，會造成嚴重的(de)鎖表操作，尤其是生産環境，是不能容忍的(de)。

pt-online-schema-change它會首先建立一個(gè)與原表結構相同的(de)新表，并且在新表上進行表結構的(de)修改，然後再把原表中的(de)數據複制到新表中，并在原表中增加一些觸發器。

把原表中新增的(de)數據也(yě)複制到新表中，在行所有數據複制完成之後，把新表命名成原表，并把原來(lái)的(de)表删除掉。

把原來(lái)一個(gè)DDL操作，分(fēn)解成多(duō)個(gè)小的(de)批次進行。

3、禁止爲程序使用(yòng)的(de)賬号賦予super權限

當達到最大(dà)連接數限制時(shí)，還(hái)運行1個(gè)有super權限的(de)用(yòng)戶連接super權限隻能留給DBA處理(lǐ)問題的(de)賬号使用(yòng)。

4、對(duì)于程序連接數據庫賬号，遵循權限最小原則

程序使用(yòng)數據庫賬号隻能在一個(gè)DB下(xià)使用(yòng)，不準跨庫 程序使用(yòng)的(de)賬号原則上不準有drop權限。