前面講了SQL優(yōu)化以及索引的使用伐憾、設(shè)計(jì)優(yōu)化了，那么接下來就到表的設(shè)計(jì)與優(yōu)化啦：漳！Ｊ魉唷！真實(shí)地去設(shè)計(jì)優(yōu)化單表結(jié)構(gòu)以及講述多表設(shè)計(jì)基本原則（結(jié)合真實(shí)的生產(chǎn)環(huán)境的取舍來講述）瀑罗。
本系列：demo下載
（一）MySQL優(yōu)化筆記（一）--庫與表基本操作以及數(shù)據(jù)增刪改
（二）MySQL優(yōu)化筆記（二）--查找優(yōu)化（1）（非索引設(shè)計(jì)）
（三）MySQL優(yōu)化筆記（二）--查找優(yōu)化（2）（外連接胸嘴、多表聯(lián)合查詢以及查詢注意點(diǎn)）
（四） MySQL優(yōu)化筆記（三）--索引的使用、原理和設(shè)計(jì)優(yōu)化
（五） MySQL優(yōu)化筆記（四）--表的設(shè)計(jì)與優(yōu)化（單表廓脆、多表）
（六）MySQL優(yōu)化筆記（五）--數(shù)據(jù)庫存儲引擎
（七）MySQL優(yōu)化筆記（六）--存儲過程和存儲函數(shù)
（八）MySQL優(yōu)化筆記（七）--視圖應(yīng)用詳解
（九） MySQL優(yōu)化筆記（八）--鎖機(jī)制超詳細(xì)解析（鎖分類筛谚、事務(wù)并發(fā)、引擎并發(fā)控制）
文章結(jié)構(gòu)：（1）單表設(shè)計(jì)與優(yōu)化停忿；（2）基于單表設(shè)計(jì)的多表設(shè)計(jì)原則（含表拆分原則）驾讲；（均以實(shí)際生產(chǎn)開發(fā)環(huán)境下的環(huán)境為基準(zhǔn)）
文章目錄：
（1）單表設(shè)計(jì)與優(yōu)化
- 設(shè)計(jì)規(guī)范化表，消除數(shù)據(jù)冗余（以使用正確字段類型最明顯）
- 前三范式
- 所有字段類型
- 所有字段類型羅列
- 針對常用的varchar席赂，我們來思考幾個問題
- 給出幾個類型選取建議 - 適當(dāng)?shù)娜哂嗨泵黾佑?jì)算列：（實(shí)際開發(fā)中必須思考的點(diǎn)）
- 索引的設(shè)計(jì)
- 主鍵和外鍵的必要性（實(shí)際項(xiàng)目開發(fā)的重要取舍）
- 存儲過程、視圖颅停、函數(shù)的適當(dāng)使用（這些是優(yōu)化的方法谓晌，這幾個后面會講）
- 傳說中的‘三少原則’
- 分割你的表，減小表尺寸
- 字段設(shè)計(jì)原則
（2）基于單表設(shè)計(jì)的多表設(shè)計(jì)原則
- 表關(guān)系
- 一對一關(guān)系
- 一對多關(guān)系（多對一）
- 多對多關(guān)系
- 注意
- 外鍵與索引
- 建立關(guān)系 - 分表原則：（涉及分區(qū)分表問題探究癞揉，以后的篇章再補(bǔ)充實(shí)例）
- 表拆分方式
- 垂直切分
- 水平拆分（分表纸肉，分區(qū)）–按表中某一字段值的范圍劃分
- 散列庫表（基于hash算法的切分）
- 在了解完分表了，我們先來理解區(qū)分分區(qū)與分表吧
- 表拆分建議：（針對大系統(tǒng)）
一喊熟、單表設(shè)計(jì)與優(yōu)化：
此部分基于此博客去拓展柏肪，寫下更貼近生產(chǎn)環(huán)境的數(shù)據(jù)表經(jīng)驗(yàn)
（1）設(shè)計(jì)規(guī)范化表，消除數(shù)據(jù)冗余（以使用正確字段類型最明顯）：
數(shù)據(jù)庫范式是確保數(shù)據(jù)庫結(jié)構(gòu)合理芥牌，滿足各種查詢需要烦味、避免數(shù)據(jù)庫操作異常的數(shù)據(jù)庫設(shè)計(jì)方式。滿足范式要求的表壁拉，稱為規(guī)范化表谬俄，范式產(chǎn)生于20世紀(jì)70年代初柏靶，一般表設(shè)計(jì)滿足前三范式就可以，在這里簡單介紹一下前三范式溃论。
第一范式（1NF）無重復(fù)的列
所謂第一范式（1NF）是指在關(guān)系模型中屎蜓，對域添加的一個規(guī)范要求，所有的域都應(yīng)該是原子性的钥勋，即數(shù)據(jù)庫表的每一列都是不可分割的原子數(shù)據(jù)項(xiàng)梆靖，而不能是集合，數(shù)組笔诵，記錄等非原子數(shù)據(jù)項(xiàng)返吻。
第二范式（2NF）屬性
在1NF的基礎(chǔ)上，非碼屬性必須完全依賴于碼[在1NF基礎(chǔ)上消除非主屬性對主碼的部分函數(shù)依賴]
第三范式（3NF）屬性
在1NF基礎(chǔ)上乎婿，任何非主屬性不依賴于其它非主屬性[在2NF基礎(chǔ)上消除傳遞依賴测僵。
通俗點(diǎn)講：
第一范式：屬性(字段)的原子性約束，要求屬性具有原子性谢翎，不可再分割捍靠；
第二范式：記錄的惟一性約束，要求記錄有惟一標(biāo)識森逮，每條記錄需要有一個屬性來做為實(shí)體的唯一標(biāo)識榨婆，即每列都要和主鍵相關(guān)。
第三范式：屬性(字段)冗余性的約束褒侧，即任何字段不能由其他字段派生出來良风，在通俗點(diǎn)就是：主鍵沒有直接關(guān)系的數(shù)據(jù)列必須消除(消除的辦法就是再創(chuàng)建一個表來存放他們，當(dāng)然外鍵除外)闷供。即：確保每列都和主鍵列直接相關(guān),而不是間接相關(guān)烟央。
如果數(shù)據(jù)庫設(shè)計(jì)達(dá)到了完全的標(biāo)準(zhǔn)化，則把所有的表通過關(guān)鍵字連接在一起時歪脏，不會出現(xiàn)任何數(shù)據(jù)的復(fù)本(repetition)疑俭。標(biāo)準(zhǔn)化的優(yōu)點(diǎn)是明顯的，它避免了數(shù)據(jù)冗余婿失，自然就節(jié)省了空間钞艇，也對數(shù)據(jù)的一致性(consistency)提供了根本的保障，杜絕了數(shù)據(jù)不一致的現(xiàn)象豪硅，同時也提高了效率哩照。
尤其是正確字段類型的選擇：（先列出所有字段類型再寫建議）
所有字段類型：
（一）整型數(shù)值：
| 整數(shù)類型 | 字節(jié)數(shù) | 最小值 ~ 最大值 |
|---|---|---|
| tinyint | 1 | -128~127 或 0-255 |
| smallint | 2 | -32768~32767 或 0~65535 |
| mediumint | 3 | -8388608~8388607 或 0~1677215 |
| int | 4 | -2147483648~2147483647 或 0~4294967295 |
| bigint | 8 | -9223372036854775808~9223372036854775807 或 0~18446744073709551615 |
（二）浮點(diǎn)數(shù)類型
| 浮點(diǎn)數(shù)類型 | 字節(jié)數(shù) | 最小值 ~ 最大值 |
|---|---|---|
| double | 4 | ±1.175494351E-38 ~ ± 3.402823466E+38 |
| double | 8 | ±2.2250738585072014E-308 ~ ±1.7976931348623157E+308 |
（三）定點(diǎn)數(shù)類型
| 定點(diǎn)數(shù)類型 | 字節(jié)數(shù) | 最小值 ~ 最大值 |
|---|---|---|
| dec(m,d) | m+2 | 最大取值范圍與double相同，給定decimal的有效值取值范圍由m和d決定 |
關(guān)于浮點(diǎn)數(shù)與定點(diǎn)數(shù)有點(diǎn)看法：
浮點(diǎn)數(shù)相對于定點(diǎn)數(shù)的優(yōu)點(diǎn)是在長度一定的情況下舟误，浮點(diǎn)數(shù)能夠表示更大的數(shù)據(jù)范圍葡秒；它的缺點(diǎn)是會引起精度問題姻乓。
使用時我們要注意：
1. 浮點(diǎn)數(shù)存在誤差問題嵌溢；
2. 對貨幣等對精度敏感的數(shù)據(jù)眯牧，應(yīng)該用定點(diǎn)數(shù)表示或存儲；
3. 編程中赖草，如果用到浮點(diǎn)數(shù)学少，要特別注意誤差問題，并盡量避免做浮點(diǎn)數(shù)比較秧骑；
4. 要注意浮點(diǎn)數(shù)中一些特殊值的處理版确。
（四）位類型
| 位類型 | 字節(jié)數(shù) | 最小值 ~ 最大值 |
|---|---|---|
| bit(m) | 1~8 | bit(1) ~ bit(64) |
（五）日期時間類型
| 時間日期類型 | 字節(jié)數(shù) | 最小值 ~ 最大值 |
|---|---|---|
| date | 4 | 1000-01-01 ~ 9999-12-31 |
| datetime | 8 | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 |
| timestamp | 4 | 19700101080001 ~ 2038年某個時刻 |
| time | 3 | -838:59:59 ~ 838:59:59 |
| year | 1 | 1901 ~ 2155 |
mysql中用now()寫入當(dāng)前時間。
（六）字符串類型：
| 字符串類型 | 字節(jié)數(shù) | 取值范圍 |
|---|---|---|
| char(m) | m | m為0 ~ 255之間的整數(shù) |
| varchar(m) | 值長度+1 | m為0~65535之間的整數(shù) |
| tinytext | 值長度+2 | 允許長度0~255字節(jié) |
| text | 值長度+2 | 允許長度0~65535字節(jié) |
| mediumtext | 值長度+3 | 允許長度0~167772150字節(jié) |
| longtext | 值長度+3 | 允許長度0~4294967295字節(jié) |
| binary(m) | m | 允許0~m個字節(jié)定長的字符串 |
| varbinary(m) | 值長度+1 | 允許0~m個字節(jié)變長的字符串 |
| tinyblob | 值長度+1 | 允許長度0~255字節(jié) |
| blob | 值長度+2 | 允許長度0~65535字節(jié) |
| mediumblob | 值長度+3 | 允許長度0~167772150字節(jié) |
| longblob | 值長度+4 | 允許長度0~4294967295字節(jié) |
| enum | 1或2 | 1255個成員需要1個字節(jié)存乎折；25565535個成員绒疗，2個字節(jié)存 |
| set | 1/2/3/4/8 | 類似enum,set一次可以選取多個成員，而enum只能一個 |
針對常用的varchar骂澄，我們來思考幾個問題：
1）varchar的長度吓蘑？
MySQL的文檔，其中對varchar字段類型這樣描述：varchar(m) 變長字符串坟冲。m 表示最大列長度磨镶。m的范圍是0到65,535。(VARCHAR的最大實(shí)際長度由最長的行的大小和使用的字符集確定健提，最大有效長度是65,532字節(jié)）琳猫。
mysql varchar(50) 不管中文 還是英文 都是存50個的，但是一個表中所有varchar字段的總長度跟編碼有關(guān)私痹，如果是utf-8脐嫂，那么大概65535/3，如果是gbk紊遵，那么大概65535/2.
2）存儲限制雹锣？編碼長度限制？行長度限制癞蚕？超出了蕊爵，會變成怎樣？
針對第一個問題：varchar 字段是將實(shí)際內(nèi)容單獨(dú)存儲在聚簇索引之外桦山，實(shí)際存儲從第二個字節(jié)開始攒射，接著要用1到2個字節(jié)表示實(shí)際長度（長度超過255時需要2個字節(jié)），因此最大長度不能超過65535恒水。
針對第二個問題：字符類型若為gbk会放，每個字符最多占2個字節(jié)。字符類型若為utf8钉凌，每個字符最多占3個字節(jié)咧最。
針對第三個問題：導(dǎo)致實(shí)際應(yīng)用中varchar長度限制的是一個行定義的長度。 MySQL要求一個行的定義長度不能超過65535。若定義的表長度超過這個值矢沿，則提示
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs滥搭。
針對第四個問題：若定義的時候超過上述限制，則varchar字段會被強(qiáng)行轉(zhuǎn)為text類型捣鲸，并產(chǎn)生warning瑟匆。
3）與char的對比：
CHAR(M)定義的列的長度為固定的，M取值可以為0～255之間栽惶，當(dāng)保存CHAR值時愁溜，在它們的右邊填充空格以達(dá)到指定的長度。當(dāng)檢 索到CHAR值時外厂，尾部的空格被刪除掉冕象。在存儲或檢索過程中不進(jìn)行大小寫轉(zhuǎn)換。CHAR存儲定長數(shù)據(jù)很方便汁蝶，CHAR字段上的索引效率級高交惯，比如定義 char(10)，那么不論你存儲的數(shù)據(jù)是否達(dá)到了10個字節(jié)穿仪，都要占去10個字節(jié)的空間,不足的自動用空格填充席爽。
CHAR和VARCHAR最大的不同就是一個是固定長度，一個是可變長度啊片。由于是可變長度只锻，因此實(shí)際存儲的時候是實(shí)際字符串再加上一個記錄 字符串長度的字節(jié)(如果超過255則需要兩個字節(jié))。如果分配給CHAR或VARCHAR列的值超過列的最大長度紫谷，則對值進(jìn)行裁剪以使其適合齐饮。如果被裁掉 的字符不是空格，則會產(chǎn)生一條警告笤昨。如果裁剪非空格字符祖驱，則會造成錯誤(而不是警告)并通過使用嚴(yán)格SQL模式禁用值的插入。
4）char瞒窒、varchar與text的建議：
TEXT只能儲存純文本文件捺僻。
效率來說基本是char>varchar>text，但是如果使用的是Innodb引擎的話崇裁，推薦使用varchar代替char
char和varchar可以有默認(rèn)值匕坯，text不能指定默認(rèn)值
以下給出幾個類型選取建議
（一）數(shù)字類型：
1）不到不要使用DOUBLE，不僅僅只是存儲長度的問題拔稳，同時還會存在精確性的問題葛峻。
2）固定精度的小數(shù)，也不建議使用DECIMAL巴比，建議乘以固定倍數(shù)轉(zhuǎn)換成整數(shù)存儲术奖，可以大大節(jié)省存儲空間礁遵，且不會帶來任何附加維護(hù)成本。
3）對于整數(shù)的存儲采记，在數(shù)據(jù)量較大的情況下佣耐，建議區(qū)分開 TINYINT / INT / BIGINT 的選擇，因?yàn)槿咚加玫拇鎯臻g也有很大的差別挺庞，能確定不會使用負(fù)數(shù)的字段，建議添加unsigned定義稼病。當(dāng)然选侨，如果數(shù)據(jù)量較小的數(shù)據(jù)庫，也可以不用嚴(yán)格區(qū)分三個整數(shù)類型然走。
4）對于整型數(shù)值援制，mysql支持在類型名稱后面的小括號內(nèi)指定顯示寬度，例如int(5)表示當(dāng)數(shù)值寬度小于5位時候在數(shù)值前面填滿寬度芍瑞，一般配合zerofill屬性使用晨仑。如果一個列指定為zerofill,則MySQL自動為該列添加unsigned屬性。
5）在數(shù)據(jù)量較大時拆檬、建議把實(shí)數(shù)類型轉(zhuǎn)為整數(shù)類型洪己。原因很簡單：1. 浮點(diǎn)不精確；2.定點(diǎn)計(jì)算代價昂貴竟贯。例如：要存放財務(wù)數(shù)據(jù)精確到萬分之一答捕、則可以把所有金額乘以一百萬、然后存在BIGINT下屑那。
（二）字符類型：
1）盡量不要使用 TEXT 數(shù)據(jù)類型拱镐，其處理方式?jīng)Q定了他的性能要低于char或者是varchar類型的處理。定長字段持际，建議使用 CHAR 類型沃琅，不定長字段盡量使用 VARCHAR，且僅僅設(shè)定適當(dāng)?shù)淖畲箝L度蜘欲，而不是非常隨意的給一個很大的最大長度限定益眉，因?yàn)椴煌拈L度范圍，MySQL也會有不一樣的存儲處理姥份。
2）char會刪除字符串尾部的空格呜叫，varchar不會，varchar向前補(bǔ)1-2字節(jié)殿衰；char定長朱庆。binary類似于char,binary只能保存二進(jìn)制字符串。
char是固定長度闷祥，所以它的處理速度比varchar快得多娱颊，但缺點(diǎn)是浪費(fèi)存儲空間傲诵，不能在行尾保存空格。在MySQL中箱硕，MyISAM建議使用固定長度代替可變長度列拴竹；InnoDB建議使用varchar類型，因?yàn)樵贗nnoDB中剧罩，內(nèi)部行存儲格式?jīng)]有區(qū)分固定長度和可變長度惠昔。
3）enum類型忽略大小寫。
4）text與blob區(qū)別：blob保存二進(jìn)制數(shù)據(jù)镇防；text保存字符數(shù)據(jù)啦鸣，有字符集。text和blob不能有默認(rèn)值扑毡。
應(yīng)用：text與blob主要區(qū)別是text用來保存字符數(shù)據(jù)（如文章泉褐，日記等）疲眷，blob用來保存二進(jìn)制數(shù)據(jù)（如照片等）换淆。blob與text在執(zhí)行了大量刪除操作時候躁愿，有性能問題（產(chǎn)生大量的“空洞“）峡眶，為提高性能建議定期optimize table 對這類表進(jìn)行碎片整理。
關(guān)于text與blob我們有些看法建議：
1. BLOB和TEXT值也會引起自己的一些問題，特別是執(zhí)行了大量的刪除或更新操作的時候伞辛。刪除這種值會在數(shù)據(jù)表中留下很大的"空洞"于游，以后填入這些"空洞"的記錄可能長度不同,為了提高性能,建議定期使用 OPTIMIZE TABLE 功能對這類表進(jìn)行碎片整理.
2. 在不必要的時候避免檢索大型的BLOB或TEXT值蚜点。
3. 把BLOB或TEXT列分離到單獨(dú)的表中实辑。在某些環(huán)境中，如果把這些數(shù)據(jù)列移動到第二張數(shù)據(jù)表中藻丢，可以讓你把原數(shù)據(jù)表中 的數(shù)據(jù)列轉(zhuǎn)換為固定長度的數(shù)據(jù)行格式剪撬，那么它就是有意義的。這會減少主表中的碎片悠反，使你得到固定長度數(shù)據(jù)行的性能優(yōu)勢残黑。它還使你在主數(shù)據(jù)表上運(yùn)行 SELECT *查詢的時候不會通過網(wǎng)絡(luò)傳輸大量的BLOB或TEXT值馍佑。
（三）時間類型：
1）盡量使用TIMESTAMP類型，因?yàn)槠浯鎯臻g只需要 DATETIME 類型的一半梨水。對于只需要精確到某一天的數(shù)據(jù)類型拭荤，建議使用DATE類型，因?yàn)樗拇鎯臻g只需要3個字節(jié)疫诽，比TIMESTAMP還少舅世。不建議通過INT類型類存儲一個unix timestamp 的值，因?yàn)檫@太不直觀奇徒，會給維護(hù)帶來不必要的麻煩雏亚，同時還不會帶來任何好處。
2）根據(jù)實(shí)際需要選擇能夠滿足應(yīng)用的最小存儲日期類型摩钙。
3）timestamp罢低，日期類型中只有它能夠和實(shí)際時區(qū)相對應(yīng)。
（四）ENUM & SET：
對于狀態(tài)字段胖笛，可以嘗試使用 ENUM 來存放网持，因?yàn)榭梢詷O大的降低存儲空間，而且即使需要增加新的類型匀钧，只要增加于末尾翎碑，修改結(jié)構(gòu)也不需要重建表數(shù)據(jù)谬返。如果是存放可預(yù)先定義的屬性數(shù)據(jù)呢之斯？可以嘗試使用SET類型，即使存在多種屬性遣铝，同樣可以游刃有余佑刷，同時還可以節(jié)省不小的存儲空間。
（五）LOB類型：
強(qiáng)烈反對在數(shù)據(jù)庫中存放 LOB 類型數(shù)據(jù)酿炸，雖然數(shù)據(jù)庫提供了這樣的功能瘫絮，但這不是他所擅長的，我們更應(yīng)該讓合適的工具做他擅長的事情填硕，才能將其發(fā)揮到極致麦萤。
（2）適當(dāng)?shù)娜哂啵黾佑?jì)算列：（實(shí)際開發(fā)中必須思考的點(diǎn)）
數(shù)據(jù)庫設(shè)計(jì)的實(shí)用原則是：在數(shù)據(jù)冗余和處理速度之間找到合適的平衡點(diǎn)扁眯。
滿足范式的表一定是規(guī)范化的表壮莹，但不一定是最佳的設(shè)計(jì)。很多情況下會為了提高數(shù)據(jù)庫的運(yùn)行效率姻檀，常常需要降低范式標(biāo)準(zhǔn)：適當(dāng)增加冗余命满，達(dá)到以空間換時間的目的。比如我們有一個表绣版，產(chǎn)品名稱胶台，單價歼疮，庫存量，總價值诈唬。這個表是不滿足第三范式的韩脏，因?yàn)椤翱們r值”可以由“單價”乘以“數(shù)量”得到，說明“金額”是冗余字段铸磅。但是骤素，增加“總價值”這個冗余字段，可以提高查詢統(tǒng)計(jì)的速度愚屁，這就是以空間換時間的作法济竹。合理的冗余可以分散數(shù)據(jù)量大的表的并發(fā)壓力，也可以加快特殊查詢的速度霎槐，冗余字段可以有效減少數(shù)據(jù)庫表的連接送浊，提高效率。
其中"總價值"就是一個計(jì)算列丘跌，在數(shù)據(jù)庫中有兩種類型：數(shù)據(jù)列和計(jì)算列袭景，數(shù)據(jù)列就是需要我們手動或者程序給予賦值的列，計(jì)算列是源于表中其他的數(shù)據(jù)計(jì)算得來闭树，比如這里的"總價值"
在SQL中創(chuàng)建計(jì)算列：
create table goods(
id int auto_increment not null,
c1 int,
c2 int,
c3 int as (c1+c2), //這個就是計(jì)算列啦
primary key(id)
)
（3）索引的設(shè)計(jì)：
表優(yōu)化的重要途徑耸棒，百萬級別的表沒有索引，注定卡死报辱。
詳細(xì)請閱讀此文章
（4）主鍵和外鍵的必要性（實(shí)際項(xiàng)目開發(fā)的重要取舍）
概述：主鍵與外鍵的設(shè)計(jì)与殃，在全局?jǐn)?shù)據(jù)庫的設(shè)計(jì)中，占有重要地位碍现。 因?yàn)椋褐麈I是實(shí)體的抽象幅疼，主鍵與外鍵的配對，表示實(shí)體之間的連接昼接。
主鍵：根據(jù)第二范式爽篷，需要有一個字段去標(biāo)識這條記錄，主鍵無疑是最好的標(biāo)識慢睡，但是很多表也不一定需要主鍵逐工，但是對于數(shù)據(jù)量大，查詢頻繁的數(shù)據(jù)庫表漂辐，一定要有主鍵泪喊，主鍵可以增加效率、防止重復(fù)等優(yōu)點(diǎn)者吁。
主鍵的選擇也比較重要窘俺，一般選擇總的長度小的鍵，小的鍵的比較速度快，同時小的鍵可以使主鍵的B樹結(jié)構(gòu)的層次更少瘤泪。
主鍵的選擇還要注意組合主鍵的字段次序灶泵，對于組合主鍵來說，不同的字段次序的主鍵的性能差別可能會很大对途，一般應(yīng)該選擇重復(fù)率低赦邻、單獨(dú)或者組合查詢可能性大的字段放在前面。
外鍵：外鍵作為數(shù)據(jù)庫對象实檀，很多人認(rèn)為麻煩而不用惶洲，實(shí)際上犁嗅，外鍵在大部分情況下是很有用的赘娄，理由是：外鍵是最高效的一致性維護(hù)方法。
數(shù)據(jù)庫的一致性要求遥诉，依次可以用外鍵须床、CHECK約束铐料、規(guī)則約束、觸發(fā)器豺旬、客戶端程序钠惩，一般認(rèn)為，離數(shù)據(jù)越近的方法效率越高族阅。但是Ｂ恕！坦刀！要謹(jǐn)慎使用級聯(lián)刪除和級聯(lián)更新愧沟，因?yàn)榧壜?lián)刪除和級聯(lián)更新有些突破了傳統(tǒng)的關(guān)于外鍵的定義，功能有點(diǎn)太過強(qiáng)大求泰，使用前必須確定自己已經(jīng)把握好其功能范圍央渣，否則计盒，級聯(lián)刪除和級聯(lián)更新可能讓你的數(shù)據(jù)莫名其妙的被修改或者丟失渴频。從性能看級聯(lián)刪除和級聯(lián)更新是比其他方法更高效的方法。
實(shí)際項(xiàng)目中的主外鍵取舍設(shè)計(jì)：（在性能和可擴(kuò)展性之間尋求平衡）
邊緣模塊指的是小功能不常用需求很少再改的模塊北启；中心模塊是指關(guān)聯(lián)的東西太多的模塊卜朗、是很多表的主表；物理鍵指的是在表建立主外鍵關(guān)聯(lián)咕村，邏輯主外鍵指的是利用字段去實(shí)現(xiàn)邏輯主外鍵關(guān)聯(lián)场钉；熱點(diǎn)模塊指的是需求經(jīng)常要改的模塊
大型系統(tǒng)：
1. 針對性能要求不高，安全要求高的模塊懈涛，推薦使用物理主外鍵關(guān)聯(lián)逛万；針對性能要求高、安全自己控制的模塊批钠，推薦不用物理外鍵宇植；
2. 針對中心模塊和其他模塊的聯(lián)系得封，推薦使用物理主外鍵。
3. 針對熱點(diǎn)模塊指郁，必須使用邏輯主外鍵
4. 針對邊緣模塊忙上，推薦使用物理主外鍵
小系統(tǒng)？闲坎？
隨便你啦疫粥，也就是20張表以下的系統(tǒng)。邏輯不復(fù)雜都無所謂啦腰懂，不過推薦還是使用外鍵梗逮。
注意：
不用外鍵而用程序控制數(shù)據(jù)一致性和完整性時，應(yīng)該寫一層來保證绣溜，然后個個應(yīng)用通過這個層來訪問數(shù)據(jù)庫库糠。
外鍵是有性能問題的，不能過分追求涮毫。
（5）存儲過程瞬欧、視圖、函數(shù)的適當(dāng)使用（這些是優(yōu)化的方法罢防，這幾個后面會講）：
很多人習(xí)慣將復(fù)雜操作都放在應(yīng)用程序?qū)铀一ⅲ绻阋獌?yōu)化數(shù)據(jù)訪問性能，將SQL代碼移植到數(shù)據(jù)庫上(使用存儲過程咒吐，視圖野建，函數(shù)和觸發(fā)器)也是一個很大的改進(jìn)原因如下：
1）存儲過程減少了網(wǎng)絡(luò)傳輸、處理及存儲的工作量恬叹，且經(jīng)過編譯和優(yōu)化候生，執(zhí)行速度快，易于維護(hù)绽昼，且表的結(jié)構(gòu)改變時唯鸭，不影響客戶端的應(yīng)用程序
2）使用存儲過程，視圖硅确，函數(shù)有助于減少應(yīng)用程序中SQL復(fù)制的弊端目溉，因?yàn)楝F(xiàn)在只在一個地方集中處理SQL
3）使用數(shù)據(jù)庫對象實(shí)現(xiàn)所有的TSQL有助于分析TSQL的性能問題，同時有助于你集中管理TSQL代碼菱农，更好的重構(gòu)TSQL代碼缭付。
（6）傳說中的‘三少原則’：
1）數(shù)據(jù)庫的表越少越好
2）表的字段越少越好
3）字段中的組合主鍵、組合索引越少越好
這里的少是相對的循未，是減少數(shù)據(jù)冗余的重要設(shè)計(jì)理念而已陷猫。
實(shí)際上，我們?yōu)榱藴p少單表查詢壓力，會把去分表绣檬，從而分發(fā)記錄量舅巷，避免一個超級表的誕生。
（7）分割你的表河咽，減小表尺寸
如果你發(fā)現(xiàn)某個表的記錄太多钠右，例如超過一千萬條，則要對該表進(jìn)行水平分割忘蟹。水平分割的做法是飒房，以該表主鍵的某個值為界線，將該表的記錄水平分割為兩個表媚值。
如果你若發(fā)現(xiàn)某個表的字段太多狠毯，例如超過八十個，則垂直分割該表褥芒，將原來的一個表分解為兩個表
（8）字段設(shè)計(jì)原則：
字段是數(shù)據(jù)庫最基本的單位嚼松，其設(shè)計(jì)對性能的影響是很大的。需要注意如下：
1）數(shù)據(jù)類型盡量用數(shù)字型锰扶，數(shù)字型的比較比字符型的快很多献酗。
2）數(shù)據(jù)類型盡量小，這里的盡量小是指在滿足可以預(yù)見的未來需求的前提下的坷牛。
3）盡量不要允許NULL罕偎，除非必要，可以用NOT NULL+DEFAULT代替京闰。
NULL 類型比較特殊颜及，SQL 難優(yōu)化。雖然 MySQL NULL類型和 Oracle 的NULL 有差異蹂楣，會進(jìn)入索引中俏站，但如果是一個組合索引，那么這個NULL 類型的字段會極大影響整個索引的效率痊土。此外肄扎，NULL 在索引中的處理也是特殊的，也會占用額外的存放空間施戴。
4）少用TEXT和IMAGE反浓，二進(jìn)制字段的讀寫是比較慢的，而且赞哗，讀取的方法也不多，大部分情況下最好不用辆雾。
5）自增字段要慎用肪笋，不利于數(shù)據(jù)遷移
二、基于單表設(shè)計(jì)的多表設(shè)計(jì)原則：
（1）表關(guān)系：
一）一對一關(guān)系：
定義：
在這種關(guān)系中，關(guān)系表的每一邊都只能存在一個記錄藤乙。每個數(shù)據(jù)表中的關(guān)鍵字在對應(yīng)的關(guān)系表中只能存在一個記錄或者沒有對應(yīng)的記錄猜揪。這種關(guān)系和一對配偶之間的關(guān)系非常相似——要么你已經(jīng)結(jié)婚，你和你的配偶只能有一個配偶坛梁，要么你沒有結(jié)婚沒有配偶而姐。大多數(shù)的一對一的關(guān)系都是某種商業(yè)規(guī)則約束的結(jié)果，而不是按照數(shù)據(jù)的自然屬性來得到的划咐。如果沒有這些規(guī)則的約束拴念，你通常可以把兩個數(shù)據(jù)表合并進(jìn)一個數(shù)據(jù)表褐缠，而且不會打破任何規(guī)范化的規(guī)則政鼠。
一對一關(guān)系又分為：一對一外鍵關(guān)聯(lián)和一對一主鍵關(guān)聯(lián)。
一對一主鍵關(guān)聯(lián)：要求兩個表的主鍵必須完全一致队魏，通過兩個表的主鍵建立關(guān)聯(lián)關(guān)系公般。
可以看到下圖，很明顯的胡桨，班級編號作為主鍵的話官帘，就是一個主鍵關(guān)聯(lián)了。
這里寫圖片描述
一對一外鍵關(guān)聯(lián)：
下面又很明顯看到昧谊，以班主任ID作為外鍵關(guān)聯(lián)起來的一個表遏佣。
這里寫圖片描述
二）一對多關(guān)系（多對一）：
定義：
主鍵數(shù)據(jù)表中只能含有一個記錄，而在其關(guān)系表中這條記錄可以與一個或者多個記錄相關(guān)揽浙，也可以沒有記錄與之相關(guān)状婶。這種關(guān)系類似于你和你的父母之間的關(guān)系。你只有一位母親馅巷，但是你母親可以有幾個孩子膛虫。
下圖可以看到：一對多-班級表有多個學(xué)生；多對一-多個學(xué)生屬于一個班級钓猬。
這里寫圖片描述
三）多對多關(guān)系：
定義：
兩個數(shù)據(jù)表里的每條記錄都可以和另一個數(shù)據(jù)表里任意數(shù)量的記錄（或者沒有記錄）相關(guān)稍刀。例如，如果你有多個兄弟姐妹敞曹，這對你的兄弟姐妹也是一樣（有多個兄弟姐妹）账月，多對多這種關(guān)系需要引入第三個數(shù)據(jù)表，這種數(shù)據(jù)表稱為聯(lián)系表或者連接表澳迫，因?yàn)殛P(guān)系型系統(tǒng)不能直接實(shí)現(xiàn)這種關(guān)系局齿。
在RDBMS中，必須使用中間表來表示多對多的關(guān)系橄登。中間表我們可以分成兩種抓歼，一種是純粹表示關(guān)系的中間表讥此，一種是表示中間實(shí)體的中間表。
純粹表示關(guān)系的中間表很簡單谣妻，只需要兩列：AID和BID萄喳，AID以外鍵關(guān)聯(lián)到A表的主鍵，BID以外鍵關(guān)聯(lián)到B表的主鍵蹋半，然后這兩個列組成聯(lián)合主鍵他巨。這個中間表純粹是表示多對多關(guān)系而存在，在業(yè)務(wù)上不會有對應(yīng)的實(shí)體與之對應(yīng)减江。比如前面提到的學(xué)生和課程的關(guān)系染突，如果我們只需要知道哪些學(xué)生上哪些課，哪些課有哪些學(xué)生選您市，不需要有更多的信息的情況下觉痛，我們就可以建立“學(xué)生課程”中間表，里面只有學(xué)生ID和課程ID兩個字段茵休。
這里寫圖片描述
中間實(shí)體是在純粹的中間關(guān)系表的基礎(chǔ)上薪棒，加上了更多的屬性，從而形成了一個新的實(shí)體榕莺。比如上面提到的學(xué)生和課程的關(guān)系俐芯，如果我們需要記錄學(xué)生選課的時間、學(xué)生選擇這門課程后的考試成績钉鸯，那么我們就像建立一個“選課”實(shí)體吧史，該實(shí)體具有如下屬性：
選課ID，主鍵
學(xué)生ID唠雕，與學(xué)生表做外鍵關(guān)聯(lián)
課程ID贸营，與課程表做外鍵關(guān)聯(lián)
選課時間，DateTime類型
考試成績岩睁，記錄選修該課程后考試的最終成績
注意：
一）外鍵與索引：
外鍵是一種約束钞脂，與索引的概念不一樣，只是大多數(shù)情況下捕儒，我們建立外鍵時冰啃，都會在外鍵列上建立對應(yīng)的索引。外鍵的存在會在每一次數(shù)據(jù)插入刘莹、修改時進(jìn)行約束檢查阎毅，如果不滿足外鍵約束，則禁止數(shù)據(jù)的插入或修改点弯，這必然帶來一個問題扇调，就是在數(shù)據(jù)量特別大的情況下，每一次約束檢查必然導(dǎo)致性能的下降蒲拉。索引其實(shí)也有類似的問題肃拜，索引如果建多了痴腌，那么在插入刪除修改數(shù)據(jù)時也要去維護(hù)對應(yīng)的索引雌团，所以索引的存在也會導(dǎo)致數(shù)據(jù)操作變慢燃领。
不過外鍵與索引的優(yōu)點(diǎn)不同，外鍵只是保證數(shù)據(jù)的一致性锦援，并不能給系統(tǒng)性能帶來任何好處猛蔽，所以由于外鍵導(dǎo)致的插入數(shù)據(jù)變慢會隨著數(shù)據(jù)量的增長而越來越嚴(yán)重。而索引的目的是為了檢索數(shù)據(jù)更快灵寺，維護(hù)數(shù)據(jù)時導(dǎo)致的索引數(shù)據(jù)的變更曼库，對性能的影響不會像外鍵那樣隨著數(shù)據(jù)量增長而變得嚴(yán)重（當(dāng)然大數(shù)量時的索引樹維護(hù)會比小數(shù)據(jù)量的索引樹維護(hù)更麻煩，但至少不是像外鍵那樣）略板。
出于性能的考慮毁枯，如果我們的系統(tǒng)完全由我們開發(fā)的程序使用，而不需要提供數(shù)據(jù)庫給其他應(yīng)用系統(tǒng)寫入數(shù)據(jù)叮称，而且對性能要求較高种玛，那么我們可以考慮在生產(chǎn)環(huán)境中不使用外鍵，只需要建立能夠提高性能的索引瓤檐。由于整個數(shù)據(jù)庫的操作都是由我們開發(fā)的程序來完成的赂韵，所以我們程序可以在開發(fā)過程中做好各方面的一致性檢查，保證操作的數(shù)據(jù)是滿足外鍵約束的挠蛉，而不需要真正的存在這樣一個外鍵約束祭示。怎么做到這一點(diǎn)呢，首先谴古，我們在建立數(shù)據(jù)庫時有多個腳本质涛，包括創(chuàng)建表、創(chuàng)建初始化數(shù)據(jù)掰担、創(chuàng)建索引汇陆、創(chuàng)建外鍵等，我們在開發(fā)和測試環(huán)境中恩敌，都把這些腳本運(yùn)行了瞬测，以使開發(fā)測試環(huán)境中的數(shù)據(jù)庫是完整的，經(jīng)過大量測試保證應(yīng)用程序能夠維護(hù)數(shù)據(jù)之間的約束的情況下纠炮，那么我們在生產(chǎn)時月趟，并不需要運(yùn)行創(chuàng)建外鍵這個腳本文件，只需要創(chuàng)建表恢口、初始化數(shù)據(jù)孝宗、創(chuàng)建索引等即可。
二）建立關(guān)系
在開始著手考慮建立關(guān)系表之間的關(guān)系之前耕肩，你可能需要對數(shù)據(jù)非常熟悉因妇。只有在熟悉數(shù)據(jù)之后问潭，關(guān)聯(lián)會比你剛開始的時候更明顯。你的數(shù)據(jù)庫系統(tǒng)依賴于在兩個數(shù)據(jù)表中找到的匹配值來建立關(guān)系婚被。
進(jìn)行匹配的值都是主鍵和外鍵的值狡忙。（關(guān)系模型不要求一個關(guān)系必須對應(yīng)的使用一個主鍵來確定。你可以使用數(shù)據(jù)表中的任何備選關(guān)鍵字來建立關(guān)系址芯，但是使用主鍵是大家都已經(jīng)接受的標(biāo)準(zhǔn)灾茁。）主鍵（primary key）唯一的識別表中的每個記錄。而外鍵（foreign key）只是簡單的將一個數(shù)據(jù)表中的主鍵存放在另外一個數(shù)據(jù)表中谷炸。同樣地北专，對于你來說也不需要做太多的工作——只是簡單地將主鍵加到關(guān)系表中，并將其定義為外鍵旬陡。
<font color=#FF7F50 size=5>（2）分表原則：</font>（涉及分區(qū)分表問題探究拓颓，以后的篇章再補(bǔ)充實(shí)例）
分表主要目的是為突破單節(jié)點(diǎn)數(shù)據(jù)庫服務(wù)器的 I/O 能力限制，解決數(shù)據(jù)庫擴(kuò)展性問題描孟。 同時分表分庫等思想也將引出以后的數(shù)據(jù)庫集群驶睦，主從復(fù)制、讀寫分離方案.....
為什么我們要分表分區(qū)画拾？啥繁？？
日常開發(fā)中我們經(jīng)常會遇到大表的情況青抛，所謂的大表是指存儲了百萬級乃至千萬級條記錄的表旗闽。這樣的表過于龐大，導(dǎo)致數(shù)據(jù)庫在查詢和插入的時候耗時太長蜜另，性能低下适室，如果涉及聯(lián)合查詢的情況，性能會更加糟糕举瑰。分表和表分區(qū)的目的就是減少數(shù)據(jù)庫的負(fù)擔(dān)捣辆，提高數(shù)據(jù)庫的效率，通常點(diǎn)來講就是提高表的增刪改查效率此迅。
（一）表拆分方式：
1）垂直切分：
這里寫圖片描述
定義：
把主鍵和一些數(shù)據(jù)表的列放在一個表中汽畴，然后把主鍵和另一些數(shù)據(jù)表的列放在一個表中。
如果一個表的某些列常用耸序，另一些不常用忍些，則可以采用垂直拆分。垂直拆分可以使數(shù)據(jù)行變小坎怪，一個數(shù)據(jù)頁就可以存放更多的數(shù)據(jù)罢坝，在查詢時候可以減少I/O次數(shù)。其缺點(diǎn)是需要管理冗余列搅窿，查詢所有數(shù)據(jù)時候需要join查找嘁酿。
優(yōu)點(diǎn)：
使得行數(shù)據(jù)變小隙券，一個數(shù)據(jù)塊(Block)就能存放更多的數(shù)據(jù)，在查詢時就會減少I/O次數(shù)(每次查詢時讀取的Block 就少)闹司。
可以達(dá)到最大化利用Cache的目的娱仔。
缺點(diǎn)：
表垂直分割后，主碼(主鍵)出現(xiàn)冗余开仰，需要管理冗余列
會引起表連接JOIN操作（增加CPU開銷）需要從業(yè)務(wù)上規(guī)避
2）水平拆分（分表拟枚，分區(qū)）--按表中某一字段值的范圍劃分：
這里寫圖片描述
