最近開發(fā)的m2vop平臺(tái)讓人很窩火啊宜鸯，數(shù)據(jù)庫(kù)方面經(jīng)常遇到一些問(wèn)題铝穷，怒下決心在搞一個(gè)數(shù)據(jù)庫(kù)系列的文章，本篇文章主要先介紹我們要模擬做的一個(gè)項(xiàng)目玻驻，然后講講數(shù)據(jù)庫(kù)開發(fā)的規(guī)范。

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

2.1命名規(guī)范

• 所有數(shù)據(jù)庫(kù)對(duì)象名稱必須使用小寫字母并用下劃線分割
• 所有數(shù)據(jù)庫(kù)對(duì)象名稱禁止使用Mysql保留關(guān)鍵字
• 數(shù)據(jù)庫(kù)對(duì)象的命名要能做到見(jiàn)名識(shí)義偿枕，并且最好不要超過(guò)32個(gè)字符
• 臨時(shí)表必須以tmp為前綴并以日期為后綴璧瞬，備份庫(kù)，備份表必須以bak為前綴并以日期為后綴
• 所有存儲(chǔ)相同數(shù)據(jù)的列名和列類型必須一致

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

• 所有表必須適用Innodb存儲(chǔ)引擎渐夸，它支持事務(wù)嗤锉，行級(jí)鎖，更好的恢復(fù)性墓塌，高并發(fā)下性能更好
• 數(shù)據(jù)庫(kù)和表的字符集統(tǒng)一使用UTF-8瘟忱，在Mysql中UTF8字符集漢字占3個(gè)字節(jié)，ASCII碼占用1個(gè)字節(jié)桃纯。如果我們定義了一個(gè)VARCHAR（255）酷誓，那么存儲(chǔ)了255個(gè)中文會(huì)占用765個(gè)字節(jié)
• 所有表和字段都需要添加注釋。使用comment從句添加表和列的備注态坦，從一開始就進(jìn)行數(shù)據(jù)字典的維護(hù)盐数。
• 盡量控制單表數(shù)據(jù)量的大小，建議控制在500萬(wàn)以內(nèi)伞梯。否則修改表結(jié)構(gòu)玫氢，備份，回復(fù)都會(huì)有很大問(wèn)題谜诫⊙浚可以用歷史數(shù)據(jù)歸檔，分庫(kù)分表等手段來(lái)控制數(shù)據(jù)量大小喻旷。
• 謹(jǐn)慎使用Mysql分區(qū)表。分區(qū)表在物理上表現(xiàn)為多個(gè)文件，在邏輯上表現(xiàn)為一個(gè)表槽袄。謹(jǐn)慎選擇分區(qū)鍵烙无，跨分區(qū)查詢效率可能更低，建議采用物理分表的方式管理大數(shù)據(jù)遍尺。
• 盡量做到冷熱數(shù)據(jù)分離截酷，減小表的寬度。這樣可以減少磁盤IO乾戏，保證熱數(shù)據(jù)的內(nèi)存緩存命中率迂苛，經(jīng)常一起使用的列放到一個(gè)表中。Mysql限制最多存儲(chǔ)4096列鼓择。
• 禁止在表中建立預(yù)留字段三幻。因?yàn)轭A(yù)留字段的命名很難做到見(jiàn)名識(shí)義，預(yù)留字段無(wú)法確認(rèn)存儲(chǔ)的數(shù)據(jù)類型惯退，所以無(wú)法選擇合適的類型赌髓。

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

• 單張表的索引數(shù)量最好不要超過(guò)5個(gè)
• 禁止給表中的每一個(gè)列建立索引
• 每個(gè)Innodb表只有一個(gè)主鍵，如果說(shuō)我們沒(méi)有明確指定主鍵催跪，Mysql默認(rèn)會(huì)選擇表中的第一個(gè)非空索引充當(dāng)主鍵锁蠕，否則會(huì)生成一個(gè)占6個(gè)字節(jié)的主鍵（但是性能不好）
• 不使用更新頻繁的列作為主鍵，不使用多列主鍵懊蒸。這是因?yàn)橹麈I頻繁更新意味著存儲(chǔ)邏輯順序的頻繁變動(dòng)荣倾，必然帶來(lái)大量的IO操作。
• 不適用UUID骑丸，MD5舌仍，HASH，字符串列作為主鍵通危，這是因?yàn)橹恚覀儫o(wú)法保證他們的自動(dòng)增長(zhǎng)。
• 主鍵建議使用自增ID
• 常見(jiàn)的索引列建議：1.SELECT UPDATE DELETE語(yǔ)句的WHERE從句中的列 2.包含在order by菊碟、group by节芥、distinct中的字段 3.多表join的關(guān)聯(lián)列
• 區(qū)分度最高的列放在聯(lián)合索引的最左側(cè)
• 盡量把字段長(zhǎng)度小的列放在聯(lián)合索引的最左側(cè)，這樣頁(yè)中存儲(chǔ)的數(shù)據(jù)量越大

• 使用頻繁的列放到聯(lián)合索引左側(cè)
  -避免建立重復(fù)的索引和冗余的索引

  
  
  重復(fù)索引
  
  
  a列是冗余的索引
• 對(duì)于頻繁的查詢有限考慮使用覆蓋索引逆害。覆蓋索引就是包含了所有查詢字段的索引头镊。這樣可以避免Innodb表進(jìn)行索引的二次查詢∑悄唬可以把隨機(jī)IO轉(zhuǎn)變?yōu)轫樞騃O相艇。
• 盡量避免使用外鍵。不建議使用外鍵約束纯陨，但一定在表與表之間的關(guān)聯(lián)鍵上建立索引坛芽。外鍵可用于保證數(shù)據(jù)的參照完整性留储，但建議在業(yè)務(wù)端實(shí)現(xiàn)。外鍵會(huì)影響父表與子表的寫操作從而降低性能靡馁。Mysql會(huì)默認(rèn)給外鍵建立索引欲鹏。

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

• 有限選擇符合存儲(chǔ)需要的最小的數(shù)據(jù)類型，將字符串轉(zhuǎn)換為數(shù)字臭墨，比如把IP地址轉(zhuǎn)換為INT類型。如下圖所示：

  
  
• 對(duì)于非負(fù)型的數(shù)據(jù)來(lái)說(shuō)膘盖，要優(yōu)先使用無(wú)符號(hào)整型來(lái)存儲(chǔ)胧弛。無(wú)符號(hào)相對(duì)于有符號(hào)可以多出一倍的存儲(chǔ)空間。
• VARCHAR（N）中的N代表的是字符數(shù)侠畔，而不是字節(jié)數(shù)结缚。雖然它是按照實(shí)際的長(zhǎng)度進(jìn)行存儲(chǔ)，但是它讀取到內(nèi)存中時(shí)软棺，為了效率红竭，它會(huì)按照我們定義的長(zhǎng)度讀入內(nèi)存，浪費(fèi)內(nèi)存喘落。
• 避免使用TEXT和BLOB類型茵宪，建議把BLOB或是TEXT列分離到單獨(dú)的擴(kuò)展表中。而且他們只能使用前綴索引
• 避免使用ENUM數(shù)據(jù)類型瘦棋。因?yàn)樾薷腅NUM值需要使用ALTER語(yǔ)句稀火。ENUM類型的ORDER BY操作效率低，需要額外操作赌朋，因?yàn)橐D(zhuǎn)換成字符串類型才能ORDER BY凰狞。
• 禁止使用數(shù)值作為ENUM的枚舉值，因?yàn)槿菀自斐善缌x沛慢∩娜簦可以直接使用數(shù)值類型來(lái)存儲(chǔ)。
• 盡可能把所有列定義為NOT NULL团甲。因?yàn)樗饕齆ULL需要額外的空間來(lái)保存是否為空逾冬，所以要趙勇更多的空間。另外進(jìn)行比較和計(jì)算時(shí)要對(duì)NULL值做特別的處理伐庭。
• 字符串存儲(chǔ)日期型的數(shù)據(jù)是不正確的做法粉渠。缺點(diǎn)1：無(wú)法用日期函數(shù)進(jìn)行計(jì)算和比較。缺點(diǎn)2：用字符串存儲(chǔ)日期要占用更多的空間圾另。

• 使用TIMESTAMP或DATETIME類型存儲(chǔ)時(shí)間時(shí)我們要知道的東西如下：

  
  
• 同財(cái)務(wù)相關(guān)的金額類數(shù)據(jù)霸株，必須使用decimal類型。1.Decimal類型為精準(zhǔn)浮點(diǎn)數(shù)集乔，在計(jì)算式不會(huì)丟失精度去件。2.占用空間由定義的寬度決定3.可用于存儲(chǔ)比bigint更大的整數(shù)數(shù)據(jù)坡椒。

2.5SQL開發(fā)規(guī)范

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

  
  
  
  

  優(yōu)點(diǎn)：只傳遞參數(shù)，比傳遞SQL語(yǔ)句更有效尤溜；相同語(yǔ)句可以一次解析倔叼，多次使用，提高處理效率宫莱；防止SQL注入

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

  
  
• 充分利用表上的索引丈攒，而不是盲目增加索引
  避免使用雙百分號(hào)進(jìn)行查詢；另外一個(gè)SQL只能利用到符合索引中的一列進(jìn)行范圍查詢授霸；使用left join和not exists來(lái)優(yōu)化not in操作巡验。
• 程序連接不同的數(shù)據(jù)庫(kù)使用不同的賬號(hào)，禁止跨庫(kù)查詢碘耳。這樣可以為數(shù)據(jù)庫(kù)遷移和分庫(kù)分表留出余地显设；降低業(yè)務(wù)耦合度；避免權(quán)限過(guò)大而產(chǎn)生的安全風(fēng)險(xiǎn)
• 禁止使用select *進(jìn)行查詢辛辨，必須使用select字段列表進(jìn)行查詢捕捂。select * 會(huì)消耗更多的CPU和IO以及網(wǎng)絡(luò)帶寬資源；無(wú)法使用覆蓋索引斗搞；可減少表結(jié)構(gòu)變更帶來(lái)的影響指攒。
• 禁止使用不含字段列表的INSERT語(yǔ)句
  insert into t values('a','b')這種是絕對(duì)禁止的。禁止的原因是可減少表結(jié)構(gòu)變更帶來(lái)的影響榜旦。
• 避免使用子查詢幽七，可以把子查詢優(yōu)化為join操作。這是因?yàn)樽硬樵兊慕Y(jié)果集無(wú)法使用索引溅呢；子查詢會(huì)產(chǎn)生臨時(shí)表操作澡屡，如果子查詢數(shù)據(jù)量大則嚴(yán)重影響效率，且消耗過(guò)多的CPU及IO資源咐旧。
• 避免使用JOIN關(guān)聯(lián)太多的表驶鹉。每join一個(gè)表多占用一部分內(nèi)存（join_buffer_size）。join會(huì)產(chǎn)生臨時(shí)表操作铣墨，影響查詢效率室埋，Mysql最多允許關(guān)聯(lián)61個(gè)表，建議不超過(guò)5個(gè)伊约。
• 減少同數(shù)據(jù)庫(kù)的交互次數(shù)姚淆。數(shù)據(jù)庫(kù)更適合處理批量操作；合并多個(gè)相同的操作到一起屡律，可以提高處理效率腌逢，比如alter table t1 add column c1 int,change column c2 c2 int...合并在一起
• 使用in代替or。in的值不要超過(guò)500個(gè)超埋，in操作可以有效的利用索引
• 禁止使用order by rand()進(jìn)行隨機(jī)排序搏讶。這樣會(huì)把表中所有符合條件的數(shù)據(jù)裝載到內(nèi)存中進(jìn)行排序佳鳖，會(huì)消耗大量的CPU和IO及內(nèi)存資源。推薦在程序中獲取一個(gè)隨機(jī)值媒惕，然后從數(shù)據(jù)庫(kù)中獲取數(shù)據(jù)的方式系吩。
• WHERE從句中禁止對(duì)列進(jìn)行函數(shù)轉(zhuǎn)換和計(jì)算。因?yàn)閷?duì)列進(jìn)行函數(shù)轉(zhuǎn)換或計(jì)算會(huì)導(dǎo)致無(wú)法使用索引妒蔚。
• 在明顯不會(huì)有重復(fù)值時(shí)使用UNION ALL而不是UNION穿挨。UNION會(huì)把所有數(shù)據(jù)放到臨時(shí)表中后再進(jìn)行去重操作，UNION ALL不會(huì)再對(duì)結(jié)果集進(jìn)行去重操作面睛。
• 拆分復(fù)雜的大SQL為多個(gè)小SQL絮蒿。Mysql一個(gè)SQL只能使用一個(gè)CPU進(jìn)行計(jì)算，SQL拆分后可以通過(guò)并行執(zhí)行來(lái)提高處理效率叁鉴。

2.6 數(shù)據(jù)庫(kù)操作規(guī)范

• 超100萬(wàn)行的批量寫操作，要分批多次進(jìn)行操作佛寿。因?yàn)榇笈坎僮骺赡軙?huì)造成嚴(yán)重的主從延遲幌墓；binlog日志為row格式時(shí)會(huì)產(chǎn)生大量的日志；避免產(chǎn)生大事務(wù)操作
• 對(duì)大表數(shù)據(jù)結(jié)構(gòu)的修改一定要謹(jǐn)慎冀泻，會(huì)造成嚴(yán)重的鎖表操作常侣，尤其是生產(chǎn)環(huán)境，是不能忍受的弹渔。對(duì)于大表使用pt-online-schema-change修改表結(jié)構(gòu)胳施。這樣可以避免大表修改產(chǎn)生的主從延遲；避免在對(duì)標(biāo)字段進(jìn)行修改時(shí)進(jìn)行鎖表肢专；
• 禁止為程序使用的賬號(hào)賦予super權(quán)限舞肆。當(dāng)達(dá)到最大連接數(shù)限制時(shí)，還允許1個(gè)有super權(quán)限的用戶連接博杖；super權(quán)限只能留給DBA處理問(wèn)題的賬號(hào)使用
• 對(duì)于程序連接數(shù)據(jù)庫(kù)賬號(hào)椿胯，遵循權(quán)限最小原則。程序使用數(shù)據(jù)庫(kù)賬號(hào)只能在一個(gè)DB下使用剃根，不準(zhǔn)跨庫(kù)哩盲；程序使用的賬號(hào)原則上不準(zhǔn)有drop權(quán)限。