建表時需考慮的事情
在實際的業(yè)務(wù)場景中，利用關(guān)系表來構(gòu)建整個業(yè)務(wù)需求，那么我們需要考慮的是
范式規(guī)范（1NF，2NF，3NF，BCNF，4NF，5NF）；
表之間的關(guān)系（一對一，一對多，多對多）；
表用途（數(shù)據(jù)表，配置表）；
數(shù)據(jù)庫級別配置
表字段
索引使用
范式規(guī)范
1NF：關(guān)系中的每個屬性都不可再分。
1NF是所有關(guān)系型數(shù)據(jù)庫的最基本要求，不符合1NF的關(guān)系表是無法在數(shù)據(jù)庫中建立的。但是只滿足1NF的表數(shù)據(jù)冗余過大，而且會存在插入異常，刪除異常，修改異常的問題。我們舉一個金融系統(tǒng)中貸款申請的一個實際業(yè)務(wù)場景來說，現(xiàn)在設(shè)計一個貸款申請表，該表符合1NF
數(shù)據(jù)冗余過大：貸款渠道描述，貸款產(chǎn)品描述，所屬額度描述，這三個字段完全是冗余字段。
插入異常：如果我們想新增一個貸款渠道，但是沒有人從這個渠道貸款，我們便無法插入這個渠道。
刪除異常：我們要刪除李四的貸款信息，那么對應(yīng)的貸款產(chǎn)品p2，也被刪除了。
修改異常：我們要修改張三的貸款產(chǎn)品的話，那么產(chǎn)品對應(yīng)關(guān)系也被修改了。
總之一句話，1NF下的表，牽一發(fā)動全身。
2NF和3NF：消除了非主屬性對于碼的部分函數(shù)依賴（2NF）和傳遞函數(shù)依賴（3NF）
首先，理清概念
完全函數(shù)依賴：
部分函數(shù)依賴：
傳遞函數(shù)依賴：
碼：設(shè) K 為某表中的一個屬性或?qū)傩越M，若除 K 之外的所有屬性都完全函數(shù)依賴于 K（這個“完全”不要漏了），那么我們稱 K 為候選碼，簡稱為碼。在實際中我們通常可以理解為：假如當(dāng) K 確定的情況下，該表除 K 之外的所有屬性的值也就隨之確定，那么 K 就是碼。一張表中可以有超過一個碼。（實際應(yīng)用中為了方便，通常選擇其中的一個碼作為主碼）
表之間的關(guān)系
如果你設(shè)計的表符合BCNF范式的話，那么對于一對一，一對多，多對多這些表之間的關(guān)系就能理解了。之所以表之間有這些關(guān)系，就是為了讓表符合范式規(guī)范。
一對一關(guān)系
實際情況中，我們只有在垂直分表中使用。表中字段數(shù)過多，可以分為熱點字段和非熱點字段，我們可以根據(jù)這個，利用主鍵，將這些字段拆分到不同的表中。
一對多關(guān)系
實現(xiàn)2NF和3NF規(guī)范，我們就要用到一對多關(guān)系。
多對多關(guān)系
要想實現(xiàn)多對多關(guān)系，就需要引入一張中間表，中間表中的兩個字段存儲另外兩張表的主屬性（主鍵）
表用途
數(shù)據(jù)表
數(shù)據(jù)表（流水表）是有狀態(tài)的，多筆業(yè)務(wù)之間沒有關(guān)聯(lián)，會有大量insert，update和查詢操作。是我們優(yōu)化的重點對象。
配置表
一些業(yè)務(wù)數(shù)據(jù)的配置，特點是數(shù)據(jù)量較小，而且結(jié)構(gòu)簡單，一般為靜態(tài)數(shù)據(jù)，變化頻率很低，但CRUD的場景比較多。即使出現(xiàn)大量CRUD，但數(shù)據(jù)量小，其實影響不大。
其實配置表和數(shù)據(jù)表是一對多主外鍵關(guān)聯(lián)關(guān)系。但我們會面臨一個問題，就是配置表中的配置不是一成不變的，那這個時候數(shù)據(jù)表中外鍵關(guān)聯(lián)的該項配置就跟著變了。其實到底變還是不變是根據(jù)實際業(yè)務(wù)需求來看的。根據(jù)后視鏡原則，我思考了一個比較好的方案，能夠滿足表范式規(guī)范，又能回溯數(shù)據(jù)。
配置表：（id,code,version,status...業(yè)務(wù)相關(guān)配置）
其中id為表主鍵，唯一；code為業(yè)務(wù)編碼，可以唯一標(biāo)識一個業(yè)務(wù)配置；version為該業(yè)務(wù)版本的版本號（code+version為主屬性，可以唯一標(biāo)識一筆數(shù)據(jù)）stats為生效失效狀態(tài)（code+status也為主屬性，也可以唯一標(biāo)識一筆數(shù)據(jù)）。當(dāng)修改某一項配置的時候不進行update，而是insert一條新的數(shù)據(jù)，只是version+1，然后上一筆失效。
數(shù)據(jù)表：（id,foreign1,foreign2,...其他數(shù)據(jù)）
id為主鍵；對于外鍵關(guān)聯(lián)配置表的foreign，我們到底選配置表的id，還是code+status（或者是code+version）？如果我們要求數(shù)據(jù)表中配置項不需要關(guān)聯(lián)最新配置，而是保留當(dāng)時配置現(xiàn)場數(shù)據(jù)，那么我們就選id為foreign；如果要求實時最新配置數(shù)據(jù)的時候，我們就要選擇code+status為foreign。
數(shù)據(jù)庫級別配置
存儲引擎的選擇
InnoDB 存儲引擎支持事務(wù)，一般業(yè)務(wù)系統(tǒng)特別是交易類系統(tǒng)，都需要事務(wù)支持，所以只能選InnoDB。MyISAM特點是訪問速度快，如果事務(wù)沒有要求，但有大量insert和select操作，可以選擇。
字符集選擇
從應(yīng)用（前后端），到數(shù)據(jù)庫，我們都應(yīng)統(tǒng)一字符集，這樣可以避免字符集轉(zhuǎn)化帶來的性能損耗。推薦都使用UTF-8
事務(wù)隔離級別
MySql默認的隔離級別是Repeatable-Read，但建議改為Read-Committed，更適合OLTP 業(yè)務(wù)場景。
表字段
1.定長字符類型使用char()，但對于InnoDB存儲引擎，使用varchar要更好。
2.變長字符類型使用varchar()，但對于變長字符應(yīng)該有個預(yù)估，不能統(tǒng)一初始化為varchar(500)等。
3.整型定義中無需定義顯示寬度，比如：使用 INT，而不是 INT(4)。
4.存儲精確浮點數(shù)必須使用 DECIMAL 替代 FLOAT 和 DOUBLE。
5.盡可能不使用 TEXT、BLOB 類型，如果必須使用，建議將過大字段或是不常用的描述型較大字段拆分到其他表中；另外，禁止用數(shù)據(jù)庫存儲圖片或文件。數(shù)據(jù)庫處理 TEXT、BLOB 類型相當(dāng)耗時，特別是進行刪除操作。
6.日期類型選擇，如果是年，用year(4)；如果是年月日，用DATE；如果是年月日時分秒，用DATETIME。建議時間字段使用應(yīng)用生成，而不是數(shù)據(jù)庫。
7.建議字段定義為 NOT NULL。特別是索引列，因為MySQL對于null的列使得索引，索引統(tǒng)計和值的比較都更復(fù)雜。
8.每個字段都要有注釋
索引使用
對于不同的存儲引擎，支持的索引類型也不一樣，現(xiàn)在只討論InnoDB的B+樹索引，全文索引，HASH索引。但由于90%的情況都是使用B+樹索引，所以我們先討論。
首先現(xiàn)解釋幾個概念
B+樹：
是一個特殊的查找樹，本文不展開篇幅進行解釋，但可以去看程序員小灰講解的B+樹，里面講解了什么是B+樹，以及為什么數(shù)據(jù)庫索引使用B+樹。
聚集索引：
1.一張表有且只有一個聚集索引。
2.如果表有主鍵，按表的主鍵構(gòu)造一個B+樹，葉子節(jié)點存放表中行記錄數(shù)據(jù)，稱為數(shù)據(jù)葉，數(shù)據(jù)頁以雙向鏈表進行連接，數(shù)據(jù)頁邏輯有序。
3.如果沒有主鍵，MySQL也會建立一個聚集索引，但索引列是數(shù)據(jù)庫生成的。
非聚集索引（輔助索引）：
1.一張表可以有多個。
2.葉子節(jié)點存放的是一個指向聚集索引的指針，通過該表的聚集索引查詢到數(shù)據(jù)頁，返回查詢的行數(shù)據(jù)，即使用非聚集索引存在二次查詢問題。
Cardinality(索引基數(shù))：
索引列中不重復(fù)記錄數(shù)量的預(yù)估值。如果記錄都不重復(fù)（例如唯一約束字段，那么基數(shù)就為1）
單列索引：
定義一個索引對應(yīng)一個列。
聯(lián)合索引：
定義一個索引對應(yīng)多個列。聯(lián)合索引我們要考慮最左原則，舉例說明，建立一個聯(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.我們建表的時候一定要指定主鍵，而且主鍵最好使用連續(xù)的數(shù)值類型，不要使用UUID。
2.要考慮數(shù)據(jù)列的Cardinality(索引基數(shù))，如果過小，則想想是否必要建立索引。
3.根據(jù)業(yè)務(wù)場景查詢的情況，即where后的條件，來思考如何建立索引，是建立單列索引還是聯(lián)合索引。
