建表時(shí)需考慮的事情

在實(shí)際的業(yè)務(wù)場(chǎng)景中榜配，利用關(guān)系表來(lái)構(gòu)建整個(gè)業(yè)務(wù)需求，那么我們需要考慮的是
范式規(guī)范（1NF吕晌，2NF蛋褥，3NF，BCNF睛驳，4NF烙心，5NF）；
表之間的關(guān)系（一對(duì)一乏沸，一對(duì)多淫茵，多對(duì)多）；
表用途（數(shù)據(jù)表蹬跃，配置表）匙瘪；
數(shù)據(jù)庫(kù)級(jí)別配置
表字段
索引使用

范式規(guī)范

1NF：關(guān)系中的每個(gè)屬性都不可再分。

1NF是所有關(guān)系型數(shù)據(jù)庫(kù)的最基本要求蝶缀，不符合1NF的關(guān)系表是無(wú)法在數(shù)據(jù)庫(kù)中建立的丹喻。但是只滿足1NF的表數(shù)據(jù)冗余過(guò)大，而且會(huì)存在插入異常翁都，刪除異常驻啤，修改異常的問(wèn)題。我們舉一個(gè)金融系統(tǒng)中貸款申請(qǐng)的一個(gè)實(shí)際業(yè)務(wù)場(chǎng)景來(lái)說(shuō)荐吵，現(xiàn)在設(shè)計(jì)一個(gè)貸款申請(qǐng)表骑冗，該表符合1NF

貸款申請(qǐng)表

數(shù)據(jù)冗余過(guò)大：貸款渠道描述，貸款產(chǎn)品描述先煎，所屬額度描述贼涩，這三個(gè)字段完全是冗余字段。

插入異常：如果我們想新增一個(gè)貸款渠道薯蝎，但是沒(méi)有人從這個(gè)渠道貸款遥倦，我們便無(wú)法插入這個(gè)渠道。
刪除異常：我們要?jiǎng)h除李四的貸款信息占锯，那么對(duì)應(yīng)的貸款產(chǎn)品p2袒哥，也被刪除了。
修改異常：我們要修改張三的貸款產(chǎn)品的話消略，那么產(chǎn)品對(duì)應(yīng)關(guān)系也被修改了堡称。
總之一句話，1NF下的表艺演，牽一發(fā)動(dòng)全身却紧。

2NF和3NF：消除了非主屬性對(duì)于碼的部分函數(shù)依賴（2NF）和傳遞函數(shù)依賴（3NF）

首先桐臊，理清概念
完全函數(shù)依賴：
部分函數(shù)依賴：
傳遞函數(shù)依賴：
碼：設(shè) K 為某表中的一個(gè)屬性或?qū)傩越M，若除 K 之外的所有屬性都完全函數(shù)依賴于 K（這個(gè)“完全”不要漏了）晓殊，那么我們稱 K 為候選碼断凶，簡(jiǎn)稱為碼。在實(shí)際中我們通澄装常可以理解為：假如當(dāng) K 確定的情況下认烁，該表除 K 之外的所有屬性的值也就隨之確定，那么 K 就是碼介汹。一張表中可以有超過(guò)一個(gè)碼砚著。（實(shí)際應(yīng)用中為了方便，通常選擇其中的一個(gè)碼作為主碼）

表之間的關(guān)系

如果你設(shè)計(jì)的表符合BCNF范式的話痴昧，那么對(duì)于一對(duì)一，一對(duì)多冠王，多對(duì)多這些表之間的關(guān)系就能理解了赶撰。之所以表之間有這些關(guān)系，就是為了讓表符合范式規(guī)范柱彻。

一對(duì)一關(guān)系

實(shí)際情況中豪娜，我們只有在垂直分表中使用。表中字段數(shù)過(guò)多哟楷，可以分為熱點(diǎn)字段和非熱點(diǎn)字段瘤载，我們可以根據(jù)這個(gè)，利用主鍵卖擅，將這些字段拆分到不同的表中鸣奔。

一對(duì)多關(guān)系

實(shí)現(xiàn)2NF和3NF規(guī)范，我們就要用到一對(duì)多關(guān)系惩阶。

多對(duì)多關(guān)系

要想實(shí)現(xiàn)多對(duì)多關(guān)系挎狸，就需要引入一張中間表，中間表中的兩個(gè)字段存儲(chǔ)另外兩張表的主屬性（主鍵）

表用途

數(shù)據(jù)表

數(shù)據(jù)表（流水表）是有狀態(tài)的断楷，多筆業(yè)務(wù)之間沒(méi)有關(guān)聯(lián)锨匆，會(huì)有大量insert，update和查詢操作冬筒。是我們優(yōu)化的重點(diǎn)對(duì)象恐锣。

配置表

一些業(yè)務(wù)數(shù)據(jù)的配置，特點(diǎn)是數(shù)據(jù)量較小舞痰，而且結(jié)構(gòu)簡(jiǎn)單土榴，一般為靜態(tài)數(shù)據(jù)，變化頻率很低响牛，但CRUD的場(chǎng)景比較多鞭衩。即使出現(xiàn)大量CRUD学搜，但數(shù)據(jù)量小，其實(shí)影響不大论衍。

其實(shí)配置表和數(shù)據(jù)表是一對(duì)多主外鍵關(guān)聯(lián)關(guān)系瑞佩。但我們會(huì)面臨一個(gè)問(wèn)題，就是配置表中的配置不是一成不變的坯台，那這個(gè)時(shí)候數(shù)據(jù)表中外鍵關(guān)聯(lián)的該項(xiàng)配置就跟著變了炬丸。其實(shí)到底變還是不變是根據(jù)實(shí)際業(yè)務(wù)需求來(lái)看的。根據(jù)后視鏡原則蜒蕾，我思考了一個(gè)比較好的方案稠炬，能夠滿足表范式規(guī)范，又能回溯數(shù)據(jù)咪啡。
配置表：（id,code,version,status...業(yè)務(wù)相關(guān)配置）
其中id為表主鍵首启，唯一；code為業(yè)務(wù)編碼撤摸，可以唯一標(biāo)識(shí)一個(gè)業(yè)務(wù)配置毅桃；version為該業(yè)務(wù)版本的版本號(hào)（code+version為主屬性，可以唯一標(biāo)識(shí)一筆數(shù)據(jù)）stats為生效失效狀態(tài)（code+status也為主屬性准夷，也可以唯一標(biāo)識(shí)一筆數(shù)據(jù)）钥飞。當(dāng)修改某一項(xiàng)配置的時(shí)候不進(jìn)行update，而是insert一條新的數(shù)據(jù)衫嵌，只是version+1读宙，然后上一筆失效。
數(shù)據(jù)表：（id,foreign1,foreign2,...其他數(shù)據(jù)）
id為主鍵楔绞；對(duì)于外鍵關(guān)聯(lián)配置表的foreign结闸，我們到底選配置表的id，還是code+status（或者是code+version）酒朵？如果我們要求數(shù)據(jù)表中配置項(xiàng)不需要關(guān)聯(lián)最新配置膀估，而是保留當(dāng)時(shí)配置現(xiàn)場(chǎng)數(shù)據(jù)，那么我們就選id為foreign耻讽；如果要求實(shí)時(shí)最新配置數(shù)據(jù)的時(shí)候察纯，我們就要選擇code+status為foreign。

數(shù)據(jù)庫(kù)級(jí)別配置

存儲(chǔ)引擎的選擇

InnoDB 存儲(chǔ)引擎支持事務(wù)针肥，一般業(yè)務(wù)系統(tǒng)特別是交易類系統(tǒng)饼记，都需要事務(wù)支持，所以只能選InnoDB慰枕。MyISAM特點(diǎn)是訪問(wèn)速度快具则，如果事務(wù)沒(méi)有要求，但有大量insert和select操作具帮，可以選擇博肋。

字符集選擇

從應(yīng)用（前后端）低斋，到數(shù)據(jù)庫(kù)，我們都應(yīng)統(tǒng)一字符集匪凡，這樣可以避免字符集轉(zhuǎn)化帶來(lái)的性能損耗膊畴。推薦都使用UTF-8

事務(wù)隔離級(jí)別

MySql默認(rèn)的隔離級(jí)別是Repeatable-Read，但建議改為Read-Committed病游，更適合OLTP 業(yè)務(wù)場(chǎng)景唇跨。

表字段

1.定長(zhǎng)字符類型使用char()，但對(duì)于InnoDB存儲(chǔ)引擎衬衬，使用varchar要更好买猖。
2.變長(zhǎng)字符類型使用varchar()，但對(duì)于變長(zhǎng)字符應(yīng)該有個(gè)預(yù)估滋尉，不能統(tǒng)一初始化為varchar(500)等玉控。
3.整型定義中無(wú)需定義顯示寬度，比如：使用 INT狮惜，而不是 INT(4)高诺。
4.存儲(chǔ)精確浮點(diǎn)數(shù)必須使用 DECIMAL 替代 FLOAT 和 DOUBLE。
5.盡可能不使用 TEXT讽挟、BLOB 類型，如果必須使用丸冕，建議將過(guò)大字段或是不常用的描述型較大字段拆分到其他表中耽梅；另外，禁止用數(shù)據(jù)庫(kù)存儲(chǔ)圖片或文件胖烛。數(shù)據(jù)庫(kù)處理 TEXT眼姐、BLOB 類型相當(dāng)耗時(shí)，特別是進(jìn)行刪除操作佩番。
6.日期類型選擇众旗，如果是年，用year(4)趟畏；如果是年月日贡歧，用DATE；如果是年月日時(shí)分秒赋秀，用DATETIME利朵。建議時(shí)間字段使用應(yīng)用生成，而不是數(shù)據(jù)庫(kù)猎莲。
7.建議字段定義為 NOT NULL绍弟。特別是索引列，因?yàn)镸ySQL對(duì)于null的列使得索引著洼，索引統(tǒng)計(jì)和值的比較都更復(fù)雜樟遣。
8.每個(gè)字段都要有注釋

索引使用

對(duì)于不同的存儲(chǔ)引擎而叼，支持的索引類型也不一樣，現(xiàn)在只討論InnoDB的B+樹索引豹悬，全文索引葵陵，HASH索引。但由于90%的情況都是使用B+樹索引屿衅，所以我們先討論埃难。
首先現(xiàn)解釋幾個(gè)概念
B+樹：
是一個(gè)特殊的查找樹，本文不展開篇幅進(jìn)行解釋涤久，但可以去看程序員小灰講解的B+樹涡尘，里面講解了什么是B+樹，以及為什么數(shù)據(jù)庫(kù)索引使用B+樹响迂。
聚集索引：
1.一張表有且只有一個(gè)聚集索引考抄。
2.如果表有主鍵，按表的主鍵構(gòu)造一個(gè)B+樹蔗彤，葉子節(jié)點(diǎn)存放表中行記錄數(shù)據(jù)川梅，稱為數(shù)據(jù)葉，數(shù)據(jù)頁(yè)以雙向鏈表進(jìn)行連接然遏，數(shù)據(jù)頁(yè)邏輯有序贫途。
3.如果沒(méi)有主鍵，MySQL也會(huì)建立一個(gè)聚集索引待侵，但索引列是數(shù)據(jù)庫(kù)生成的丢早。
非聚集索引（輔助索引）：
1.一張表可以有多個(gè)。
2.葉子節(jié)點(diǎn)存放的是一個(gè)指向聚集索引的指針秧倾，通過(guò)該表的聚集索引查詢到數(shù)據(jù)頁(yè)怨酝，返回查詢的行數(shù)據(jù)，即使用非聚集索引存在二次查詢問(wèn)題那先。
Cardinality(索引基數(shù))：
索引列中不重復(fù)記錄數(shù)量的預(yù)估值农猬。如果記錄都不重復(fù)（例如唯一約束字段，那么基數(shù)就為1）
單列索引：
定義一個(gè)索引對(duì)應(yīng)一個(gè)列售淡。
聯(lián)合索引：
定義一個(gè)索引對(duì)應(yīng)多個(gè)列斤葱。聯(lián)合索引我們要考慮最左原則，舉例說(shuō)明揖闸，建立一個(gè)聯(lián)合索引indx_abc(a,b,c)苦掘，where a=XX;where a=XX and b=XX;where a=XX and b=XX and c=XX;都是可以使用到該索引的，但是where b=XX;where c=XX;where b=XX and c=XX是不能使用到索引的楔壤。

那么總結(jié)一下我們使用索引的原則：
1.我們建表的時(shí)候一定要指定主鍵鹤啡，而且主鍵最好使用連續(xù)的數(shù)值類型，不要使用UUID蹲嚣。
2.要考慮數(shù)據(jù)列的Cardinality(索引基數(shù))递瑰，如果過(guò)小祟牲，則想想是否必要建立索引。
3.根據(jù)業(yè)務(wù)場(chǎng)景查詢的情況抖部，即where后的條件说贝，來(lái)思考如何建立索引，是建立單列索引還是聯(lián)合索引慎颗。