1. 數(shù)據(jù)庫范式的前世今生第一次接觸數(shù)據(jù)庫范式是在大學(xué)二年級(jí)的數(shù)據(jù)庫原理課上。記得當(dāng)時(shí)教授在黑板上畫了幾個(gè)相互嵌套的圓圈說這是數(shù)據(jù)規(guī)范化的魔法陣。十年過去了這個(gè)魔法陣成了我日常數(shù)據(jù)庫設(shè)計(jì)的必備工具。今天我想用最接地氣的方式聊聊范式那些事。范式(Normal Form)本質(zhì)上是一套設(shè)計(jì)規(guī)則用來減少數(shù)據(jù)冗余、避免異常。就像整理衣柜把襪子、襯衫、外套分類存放既節(jié)省空間又方便取用。數(shù)據(jù)庫范式從1NF到5NF每個(gè)級(jí)別都有特定的規(guī)范要求。但在實(shí)際工作中我們最常用的是前三個(gè)范式。提示不要被范式的數(shù)學(xué)定義嚇到它們解決的都是非常實(shí)際的問題。比如重復(fù)存儲(chǔ)導(dǎo)致更新困難或者不該存在的數(shù)據(jù)依賴關(guān)系。2. 第一范式(1NF)數(shù)據(jù)表的入場(chǎng)券2.1 什么是1NF1NF的要求簡單直接每個(gè)字段都是原子性的不可再分。就像Excel表格里一個(gè)單元格里不能放多個(gè)值。聽起來簡單來看看這個(gè)反例訂單ID客戶商品1001張三鼠標(biāo),鍵盤,耳機(jī)這里的商品列明顯違反了1NF因?yàn)樗硕鄠€(gè)值。正確的做法是訂單ID客戶商品1001張三鼠標(biāo)1001張三鍵盤1001張三耳機(jī)2.2 1NF的實(shí)戰(zhàn)要點(diǎn)在實(shí)際項(xiàng)目中我遇到過幾個(gè)典型的1NF問題地址字段有人喜歡把省市區(qū)街道全塞進(jìn)一個(gè)字段。正確的做法是拆分成多個(gè)字段。JSON存儲(chǔ)雖然現(xiàn)代數(shù)據(jù)庫支持JSON類型但如果這些數(shù)據(jù)需要頻繁查詢和索引還是應(yīng)該規(guī)范化。標(biāo)簽系統(tǒng)一個(gè)文章有多個(gè)標(biāo)簽時(shí)應(yīng)該建立關(guān)聯(lián)表而不是用逗號(hào)分隔的字符串。注意1NF是所有范式的基礎(chǔ)。如果連1NF都不滿足查詢和更新會(huì)遇到各種奇怪問題。我曾經(jīng)接手過一個(gè)系統(tǒng)因?yàn)檫`反1NF導(dǎo)致統(tǒng)計(jì)功能完全不準(zhǔn)花了整整兩周重構(gòu)。3. 第二范式(2NF)消除部分依賴3.1 2NF的核心思想2NF在1NF基礎(chǔ)上增加了一個(gè)要求所有非主鍵字段必須完全依賴于整個(gè)主鍵而不是部分依賴。這主要針對(duì)復(fù)合主鍵的情況。來看這個(gè)訂單明細(xì)表訂單ID(主鍵)商品ID(主鍵)商品名稱商品價(jià)格客戶ID客戶名稱1001A001鼠標(biāo)99C100張三1001A002鍵盤199C100張三問題很明顯客戶ID和客戶名稱只依賴于訂單ID與商品ID無關(guān)。這就是部分依賴。解決方案是拆分成兩個(gè)表訂單表訂單ID(主鍵)客戶ID客戶名稱1001C100張三訂單明細(xì)表訂單ID(主鍵)商品ID(主鍵)商品名稱商品價(jià)格1001A001鼠標(biāo)991001A002鍵盤1993.2 2NF的實(shí)戰(zhàn)經(jīng)驗(yàn)在實(shí)際項(xiàng)目中2NF問題常常出現(xiàn)在報(bào)表設(shè)計(jì)中。我曾經(jīng)優(yōu)化過一個(gè)銷售報(bào)表原始設(shè)計(jì)把所有信息都塞在一個(gè)大表里導(dǎo)致更新異常修改客戶信息需要更新所有相關(guān)記錄插入異常新建客戶但沒有訂單時(shí)無法錄入信息刪除異常刪除最后一個(gè)訂單會(huì)丟失客戶信息拆分后不僅解決了這些問題查詢性能還提升了3倍。記住這個(gè)原則如果一個(gè)字段只依賴于主鍵的一部分就該考慮拆表了。4. 第三范式(3NF)切斷傳遞依賴4.1 3NF的定義3NF要求任何非主鍵字段不能依賴于其他非主鍵字段。換句話說所有字段都應(yīng)該直接依賴于主鍵不能有間接依賴。看這個(gè)員工表例子員工ID(主鍵)姓名部門ID部門名稱部門經(jīng)理E001張三D01研發(fā)部李四E002王五D01研發(fā)部李四這里部門名稱和部門經(jīng)理都依賴于部門ID而不是直接依賴于員工ID。正確的3NF設(shè)計(jì)是員工表員工ID(主鍵)姓名部門IDE001張三D01E002王五D01部門表部門ID(主鍵)部門名稱部門經(jīng)理D01研發(fā)部李四4.2 3NF的取舍藝術(shù)在實(shí)際項(xiàng)目中完全遵守3NF有時(shí)會(huì)導(dǎo)致過多表連接影響性能。我的經(jīng)驗(yàn)法則是高頻查詢的表可以適當(dāng)冗余犧牲部分規(guī)范化換取性能低頻更新的字段更適合做冗余關(guān)鍵業(yè)務(wù)數(shù)據(jù)必須嚴(yán)格遵循3NF比如用戶表我經(jīng)常冗余部門名稱字段因?yàn)椴块T名稱很少變更幾乎每個(gè)用戶查詢都需要顯示部門名稱避免了每次查詢都要關(guān)聯(lián)部門表但像部門經(jīng)理這種可能頻繁變更的字段就必須嚴(yán)格遵循3NF。5. 更高階的范式BCNF、4NF、5NF5.1 BCNF加強(qiáng)版的3NFBCNF(Boyce-Codd范式)比3NF更嚴(yán)格要求所有決定因素都必須是候選鍵。在大多數(shù)情況下滿足3NF的表也滿足BCNF。我遇到過的BCNF問題主要出現(xiàn)在多對(duì)多關(guān)系中包含額外屬性復(fù)雜的權(quán)限系統(tǒng)設(shè)計(jì)特殊類型的配置表5.2 4NF和5NF處理多值依賴4NF處理多值依賴5NF處理連接依賴。在實(shí)際項(xiàng)目中除非設(shè)計(jì)非常復(fù)雜的系統(tǒng)否則很少需要考慮這些高階范式。我曾經(jīng)在一個(gè)醫(yī)療系統(tǒng)中使用過4NF用來處理醫(yī)生-患者-藥品之間的復(fù)雜關(guān)系。6. 范式的實(shí)戰(zhàn)應(yīng)用策略6.1 何時(shí)應(yīng)該反范式化雖然范式有很多優(yōu)點(diǎn)但有時(shí)為了性能需要故意違反范式這叫反范式化。常見場(chǎng)景包括報(bào)表系統(tǒng)大量預(yù)計(jì)算和冗余字段緩存表將頻繁查詢的結(jié)果預(yù)先計(jì)算好歷史數(shù)據(jù)存檔不再變更的數(shù)據(jù)可以冗余存儲(chǔ)6.2 我的范式檢查清單在設(shè)計(jì)新表時(shí)我會(huì)問自己這些問題是否有重復(fù)的字段值→ 可能違反1NF復(fù)合主鍵時(shí)是否有字段只依賴部分主鍵→ 可能違反2NF是否有字段依賴于其他非主鍵字段→ 可能違反3NF是否有奇怪的更新/插入/刪除問題→ 范式可能有問題6.3 工具輔助分析現(xiàn)代數(shù)據(jù)庫工具可以幫助發(fā)現(xiàn)范式問題MySQL Workbench的逆向工程SQL Server的數(shù)據(jù)庫關(guān)系圖Oracle的SQL Developer數(shù)據(jù)建模工具我個(gè)人的習(xí)慣是先用工具生成ER圖然后肉眼檢查潛在問題。7. 常見誤區(qū)與解答7.1 誤區(qū)一范式級(jí)別越高越好不是的。范式級(jí)別越高表拆分越細(xì)查詢時(shí)需要更多的連接操作。應(yīng)該根據(jù)實(shí)際需求平衡。7.2 誤區(qū)二必須嚴(yán)格遵守所有范式視情況而定。數(shù)據(jù)倉庫通常采用星型模式或雪花模式故意違反范式以提高查詢性能。7.3 誤區(qū)三范式只適用于關(guān)系型數(shù)據(jù)庫NoSQL數(shù)據(jù)庫雖然不強(qiáng)調(diào)范式但好的設(shè)計(jì)仍然需要考慮數(shù)據(jù)冗余和一致性問題。8. 從理論到實(shí)踐一個(gè)完整案例去年我重構(gòu)了一個(gè)電商系統(tǒng)的訂單模塊原始設(shè)計(jì)存在嚴(yán)重的范式問題訂單表包含客戶所有信息違反3NF訂單項(xiàng)用JSON存儲(chǔ)違反1NF促銷信息重復(fù)存儲(chǔ)違反2NF重構(gòu)步驟首先確保所有表滿足1NF然后處理部分依賴拆分成訂單主表和訂單明細(xì)表最后處理傳遞依賴把客戶信息、促銷信息等抽離成獨(dú)立表重構(gòu)后的效果數(shù)據(jù)庫大小減少40%訂單查詢性能提升2倍數(shù)據(jù)一致性錯(cuò)誤減少90%9. 性能與范式的平衡之道經(jīng)過多年實(shí)踐我總結(jié)出幾個(gè)平衡范式與性能的經(jīng)驗(yàn)OLTP系統(tǒng)交易型優(yōu)先考慮范式保證數(shù)據(jù)一致性O(shè)LAP系統(tǒng)分析型適當(dāng)反范式化優(yōu)化查詢性能混合系統(tǒng)核心業(yè)務(wù)數(shù)據(jù)嚴(yán)格范式化報(bào)表和統(tǒng)計(jì)可以反范式化一個(gè)實(shí)用的技巧創(chuàng)建視圖來保持邏輯上的范式化底層表可以適當(dāng)反范式化。這樣既保持了查詢的簡便性又獲得了性能提升。10. 新時(shí)代下的范式思考隨著分布式數(shù)據(jù)庫和NoSQL的興起范式的應(yīng)用也在變化分布式系統(tǒng)更強(qiáng)調(diào)最終一致性而非嚴(yán)格的范式文檔數(shù)據(jù)庫如MongoDB鼓勵(lì)適度的數(shù)據(jù)冗余圖數(shù)據(jù)庫用完全不同的方式處理關(guān)系但無論如何變化范式背后的核心思想——減少冗余、避免異?！匀皇菙?shù)據(jù)庫設(shè)計(jì)的黃金法則。