很多項目前期做表結(jié)構(gòu)的時候圖快建表全靠“拍腦袋”等業(yè)務(wù)跑起來改表改到哭的日子才開始。我見過不少線上事故追根溯源很多都能回到數(shù)據(jù)庫設(shè)計最初的幾個決策上。數(shù)據(jù)庫設(shè)計原則聽起來像教科書概念實際上就是這些經(jīng)驗教訓(xùn)的總結(jié)怎么命名、怎么選字段類型、怎么定主鍵、怎么設(shè)計索引和關(guān)聯(lián)關(guān)系這些決策直接影響系統(tǒng)能不能扛住業(yè)務(wù)量以及后面加班改表的次數(shù)。這篇文章面向開發(fā)、架構(gòu)、也有準(zhǔn)備做數(shù)據(jù)建模的新人我把多年實操里驗證過的設(shè)計方法和踩過的坑一次性講透。1. 從業(yè)務(wù)出發(fā)梳理需求比建表更花時間1.1 先有業(yè)務(wù)事件再談實體和表很多同學(xué)一拿到需求就打開編輯器開始建表先把這個表建出來再把那個字段加上。這種做法往往到后期才發(fā)現(xiàn)要么缺字段要么一張表承載了完全不同的業(yè)務(wù)含義。我自己現(xiàn)在標(biāo)準(zhǔn)的做法是先把業(yè)務(wù)環(huán)節(jié)里面發(fā)生了什么“事件”全部列出來。什么叫業(yè)務(wù)事件拿電商訂單舉例用戶下單是一個事件支付是一個事件發(fā)貨是一個事件退款又是一個事件。每個事件會伴隨產(chǎn)生業(yè)務(wù)數(shù)據(jù)這些數(shù)據(jù)就是表的來源。比如下單事件需要記錄用戶、商品、數(shù)量、金額、收貨地址支付事件需要記錄支付流水號、支付方式、支付時間、支付金額。把這些事件拆清楚了哪些字段該進哪張表就非常自然了也不用反復(fù)和業(yè)務(wù)對需求。我在實際項目里還會給所有字段列一個來源清單這個字段是用戶在表單里填的還是系統(tǒng)后臺自動算出來的還是上游系統(tǒng)推送過來的如果來源不清楚這個字段就不應(yīng)該急著落表。來源不明的字段意味著業(yè)務(wù)規(guī)則存在理解偏差很多時候是需求方自己都沒有想清楚寫進表里再改就是一塊硬傷。1.2 區(qū)分主數(shù)據(jù)、操作數(shù)據(jù)和衍生數(shù)據(jù)這是設(shè)計表結(jié)構(gòu)時特別容易混淆的地方我早期也吃過虧。主數(shù)據(jù)是業(yè)務(wù)的核心實體比如客戶、商品、供應(yīng)商這類數(shù)據(jù)獨立存在不會因為一筆訂單而改變。操作數(shù)據(jù)是業(yè)務(wù)過程中的流轉(zhuǎn)記錄比如訂單、支付流水、庫存變動這類數(shù)據(jù)是持續(xù)累積的幾乎只增不改。衍生數(shù)據(jù)是計算出來的結(jié)果比如每日銷量統(tǒng)計、用戶累計消費額這類數(shù)據(jù)可以從其他數(shù)據(jù)推導(dǎo)得出。三者的設(shè)計思路完全不同。主數(shù)據(jù)要考慮穩(wěn)定性和維度擴展操作數(shù)據(jù)要考慮增量累積的控制比如分區(qū)、歸檔、防止單表過大衍生數(shù)據(jù)則要明確它是可重算的千萬別當(dāng)成不可變的原始數(shù)據(jù)來設(shè)計。我見過一個項目把累計積分和積分流水混在一張表里每次積分變動都去更新那一條累計記錄導(dǎo)致并發(fā)一高鎖等待嚴(yán)重后面把累計值單獨放到一張匯總表才解決問題。1.3 業(yè)務(wù)流程中的數(shù)據(jù)流向把數(shù)據(jù)從哪里來、到哪里去畫清楚能幫你發(fā)現(xiàn)設(shè)計里的漏洞。舉個例子數(shù)據(jù)源的可靠性決定字段是否需要默認(rèn)值是否允許為空。如果這個字段是外部回傳的那必須考慮回傳失敗的情況默認(rèn)值要給得合理。同理下游消費數(shù)據(jù)的場景決定約束的嚴(yán)格程度如果下游要按月出報表表里的時間字段就必須配索引而且不能允許為空。數(shù)據(jù)流向還有一個值得注意的點盡量讓數(shù)據(jù)只在一個地方產(chǎn)生通過引用在其他地方使用。比如用戶昵稱最佳實踐是只存在用戶表一份訂單表里存用戶ID去關(guān)聯(lián)。訂單表冗余一份用戶昵稱本身也不是完全不行只要你能接受昵稱變更時歷史訂單顯示舊名字如果接受不了就不要冗余。2. 范式與反范式?jīng)]有最好只有最合適2.1 三大范式到底解決了什么問題范式是對數(shù)據(jù)冗余和依賴的約束。第一范式要求字段原子性簡單說就是一張表的字段不能再拆分比如地址不要塞在一個字段里又讓業(yè)務(wù)去截取。第二范式要求非主鍵字段完全依賴主鍵不能只依賴主鍵的一部分。第三范式要求非主鍵字段之間不能有傳遞依賴比如課程名稱依賴課程編號而課程編號又依賴選課記錄主鍵這種就容易產(chǎn)生更新異常。這背后的真實目的是避免同一份信息在多個地方重復(fù)存儲。信息重復(fù)會導(dǎo)致三個維護問題更新的時候要改多處漏改一處就產(chǎn)生數(shù)據(jù)不一致刪除的時候刪掉一處其他地方的引用就懸空了插入的時候沒有完整信息還插不進去。這些在數(shù)據(jù)庫層面體現(xiàn)為插入異常、更新異常、刪除異常。但是范式不是越高越好的尤其在實際業(yè)務(wù)系統(tǒng)里。我見過強制把每張表都設(shè)計成第三范式以上的團隊表拆得極細(xì)查詢一個頁面要關(guān)聯(lián)七八張表數(shù)據(jù)庫性能頂不住。所以我的做法是交易、訂單這類對一致性要求高的核心鏈路優(yōu)先滿足第三范式報表、日志這類以查詢和展示為主的場景適當(dāng)反范式化。2.2 什么時候該主動反范式讀多寫少、統(tǒng)計匯總、需要頻繁查詢且關(guān)聯(lián)代價高昂的表是反范式最典型的場景。比如內(nèi)容社區(qū)的帖子列表頁每次查詢都要關(guān)聯(lián)用戶表拿作者頭像和昵稱如果刪掉關(guān)聯(lián)帖子表直接冗余一份作者昵稱和頭像地址列表查詢就少一次關(guān)聯(lián)。這種冗余帶來的代價是用戶修改昵稱后歷史帖子里的昵稱不會同步改變但大多數(shù)產(chǎn)品能夠接受。反范式設(shè)計時要控制冗余范圍。我常用的原則是只冗余需要展示和篩選的字段不做背包式全量冗余。同時還必須配套同步機制線上通常用監(jiān)聽數(shù)據(jù)變更后的異步同步或者在業(yè)務(wù)寫入時統(tǒng)一封裝了同時更新的邏輯。這些同步代碼要寫清楚注釋否則三個月后沒人記得這份冗余從哪來。2.3 更新異常是判定設(shè)計好壞的照妖鏡判斷表設(shè)計是否合理一個最簡單的方法就是在腦子里跑一遍更新場景。比如用戶換了手機號哪些表里需要跟著變?nèi)绻霈F(xiàn)在三張以上的表里都要變說明冗余失控了。反過來訂單表里存商品快照商品名稱、價格是合理的反范式因為商品改價后歷史訂單應(yīng)該保留當(dāng)時的快照但訂單表里存用戶的當(dāng)前積分就不合理積分變化會導(dǎo)致歷史訂單跟著變動。調(diào)整表結(jié)構(gòu)前我習(xí)慣先寫更新影響清單修改某一條數(shù)據(jù)時會牽動哪些表、哪些索引、哪些緩存。這個清單能提前暴露很多隱蔽問題。3. 表設(shè)計三件套命名、字段類型、主鍵3.1 命名不統(tǒng)一后期維護成本指數(shù)增長命名規(guī)范是數(shù)據(jù)庫設(shè)計里最不值錢也最容易被忽視的部分。一旦上線改表名或者批量改字段名在大多數(shù)數(shù)據(jù)庫里都不是小事涉及一堆代碼變更所以前期一定要定好規(guī)則。我常用的規(guī)則是表名用業(yè)務(wù)域加下劃線加實體名比如order_info、user_account字段名一律小寫字母加下劃線不用大小寫混拼布爾類型用is_前綴時間類型用_at后綴比如created_at、updated_at主鍵統(tǒng)一叫id外鍵統(tǒng)一以_id結(jié)尾。命名最怕的就是同義不同名。同一套系統(tǒng)里有的表用status有的表用state下次查詢時你根本想不起來哪個表用的是哪個。所有表建完后我會跑一遍字段清單看看有沒有同義字段在不同表里名字不一樣的有就趁業(yè)務(wù)還在初期趕緊統(tǒng)一。3.2 字段類型選擇的幾條實操經(jīng)驗字段類型直接決定了存儲空間、索引效率和計算正確性。選類型的核心原則是“最小夠用”在滿足業(yè)務(wù)范圍和可預(yù)見的未來擴展前提下選最小的存儲容量因為單行數(shù)據(jù)越小同樣內(nèi)存能緩存的行數(shù)越多查詢越快。以下是我在實際項目里驗證過的選型經(jīng)驗整型狀態(tài)碼、數(shù)量、次數(shù)用INT數(shù)值范圍極大的計數(shù)用BIGINT性別、開關(guān)這類枚舉型在MySQL場景下用TINYINT而不是CHAR(1)因為比較和存儲效率更高。金額一律用定點數(shù)DECIMAL不用FLOAT和DOUBLE因為浮點數(shù)在二進制下無法精確表示金額算錯是事故級別的。字符串定長且基本不變的信息用CHAR比如手機號、證件號長度不確定的內(nèi)容用VARCHAR比如昵稱、簡介、備注。這里注意VARCHAR是按字符存儲的要用合理的最大長度別一上來就VARCHAR(2000)。時間業(yè)務(wù)時間用DATETIME或TIMESTAMP不要用字符串存日期。字符串比較在索引上效率很低而且無法做日期函數(shù)運算。真到要處理跨時區(qū)或毫秒級精度的時候再加一個BIGINT存毫秒時間戳作為補充。3.3 主鍵設(shè)計自增、UUID 還是分布式 ID主鍵是表設(shè)計的核心決策之一。自增主鍵在單體應(yīng)用里最簡單、性能最好因為B樹索引天然有序插入頁分裂少。但問題是數(shù)據(jù)量大了做分庫分表時自增ID就無法保持全局唯一了。UUID 在分布式環(huán)境里唯一性有保障但作為主鍵有個致命問題——它是無序的插入時隨機寫導(dǎo)致頁分裂和碎片化嚴(yán)重索引膨脹得厲害。雪花ID類方案是行業(yè)里比較均衡的選擇趨勢遞增且全局唯一適合中大型項目。如果你是三五年內(nèi)業(yè)務(wù)量可控的項目直接用自增主鍵最省事。如果是面向未來可能增長到千萬級用戶以上的系統(tǒng)建議一開始就使用分布式ID生成器避免以后數(shù)據(jù)遷移時再改造。需要提醒的是不管用哪種ID主鍵都不要在業(yè)務(wù)上賦予含義比如不要用身份證號做用戶表主鍵因為證件號存在變更和敏感泄露風(fēng)險真實做法是內(nèi)部ID加唯一索引去約束業(yè)務(wù)編號。4. 索引設(shè)計先理解查詢再建避免上線后補4.1 索引不是越多越好很多初學(xué)者覺得查詢慢就加索引結(jié)果一張表堆了十幾個索引。索引存儲有代價寫入要同步更新索引索引越多寫入越慢存儲空間也越大。我處理過一張大表加完索引后寫入性能掉了一半的案例因為單條寫入要同時更新七個索引。真正的索引設(shè)計流程是先收集線上核心查詢SQL分析出查詢條件、排序字段、關(guān)聯(lián)字段再設(shè)計索引。記住一個核心原則索引是為了減少查詢掃描數(shù)據(jù)量它應(yīng)該和查詢模式匹配而不是和表結(jié)構(gòu)一一對應(yīng)。一張表哪怕有五十個字段只有三個高頻查詢那建三到五個索引就夠了其余字段的查詢即使全表掃描也完全可以接受因為它們要么極低頻要么是后臺管理功能不要求實時。4.2 聯(lián)合索引和覆蓋索引是最被低估的優(yōu)化手段聯(lián)合索引解決多條件查詢的場景。比如一個訂單列表頁要按用戶ID、訂單狀態(tài)、創(chuàng)建時間三個維度篩選如果你分別建三個單列索引數(shù)據(jù)庫只能挑選其中一個用得最好的其余條件回表再過濾效果并不理想。正確做法是建一個聯(lián)合索引(user_id, status, created_at)三個條件下一次索引查找就能鎖定數(shù)據(jù)范圍。聯(lián)合索引里還藏著一個重要知識點叫最左前綴原則索引從左到右逐列匹配查詢條件里不包含最左列索引就用不上。所以設(shè)計聯(lián)合索引時把篩選區(qū)分度最高的字段放最前面。建完索引再看查詢是否用到覆蓋索引如果SQL只需要索引列的數(shù)據(jù)不需要回表這就是覆蓋索引尤其適合大表查詢能減少大量隨機IO。統(tǒng)計類查詢盡量把查詢字段全部包含在索引內(nèi)。4.3 索引失效的硬傷和排查方法有幾個常見的索引失效寫法我此前項目里真實踩過字段做函數(shù)運算WHERE DATE(created_at) 2024-05-01會讓索引失效要改成created_at 2024-05-01 00:00:00 AND created_at 2024-05-02 00:00:00。隱式類型轉(zhuǎn)換字符串字段與數(shù)值比較或者數(shù)值字段與字符串比較數(shù)據(jù)庫無法匹配索引。模糊查詢左通配符LIKE %關(guān)鍵字無法使用索引改成LIKE 關(guān)鍵字%可以用上索引。OR條件id 1 OR status 2里如果只有id有索引整個查詢走不了索引要拆成UNION或者給status也建索引。排序字段與索引順序不一致索引是(user_id, created_at)排序用ORDER BY created_at索引也發(fā)揮不了作用。排查索引問題時一條條執(zhí)行EXPLAIN看type字段和key字段就清楚了。type最好是ref或者range如果看到all說明走了全表掃描。至于慢查詢?nèi)罩揪€上一定要開啟它是查找漏網(wǎng)索引的探測器。5. 數(shù)據(jù)安全、生命周期與擴展性設(shè)計5.1 軟刪除到底要不要用軟刪除指的是不直接執(zhí)行DELETE而是給數(shù)據(jù)標(biāo)記一個已刪除狀態(tài)讓它對業(yè)務(wù)“消失”但在表里還能查到。這個方案的好處是方便恢復(fù)數(shù)據(jù)也不會因刪數(shù)據(jù)影響關(guān)聯(lián)統(tǒng)計。壞處是表數(shù)據(jù)會無限膨脹每次查詢都要帶上deleted_at IS NULL條件而且一旦忘記這個條件被刪數(shù)據(jù)會重新出現(xiàn)在頁面上。我的建議是核心交易數(shù)據(jù)、有審計要求的數(shù)據(jù)用軟刪除操作日志、中間表數(shù)據(jù)直接物理刪除并做歸檔。一種我常用的折中方案是設(shè)計deleted_at字段DATETIME類型默認(rèn)NULL刪除時寫入時間替代is_deleted布爾字段。這樣既能過濾刪除記錄又能知道刪除時間還方便做數(shù)據(jù)清理任務(wù)。5.2 審計字段和狀態(tài)機的力量每個業(yè)務(wù)表里我都會加上created_at、updated_at、creator_id、updater_id四個通用字段用于定位數(shù)據(jù)產(chǎn)生時間、修改時間、操作人。這些字段不是擺設(shè)出了問題定位數(shù)據(jù)和排查糾紛都靠它們。另一個容易被忽略的設(shè)計是狀態(tài)遷移設(shè)計。不要隨意把狀態(tài)做成自由隨意的字符串而是定義好狀態(tài)枚舉值和合法遷移路徑比如訂單狀態(tài)從“待支付”只能進入“已支付”或“已取消”不能直接跳成“已退款”。如果業(yè)務(wù)流程調(diào)整頻繁還可以把狀態(tài)機單獨抽出來在代碼層面通過配置維護合法遷移。這一套做扎實后很多業(yè)務(wù)判斷和邏輯校驗都變得非常清晰比在字段上做一堆CHECK約束更靈活。5.3 預(yù)留字段是最常見的偽設(shè)計我見過很多表設(shè)計里預(yù)留了reserve_1、reserve_2這類字段理由是“以后可能用得到”。事實證明這種字段絕大多數(shù)時候都空在那里等到真有了新的擴展需求這些預(yù)留字段往往不夠用或者類型不匹配。預(yù)留字段不僅浪費存儲還會讓代碼里莫名出現(xiàn)奇怪的字段值排查問題時非常困惑。正確的擴展方式有三個一是直接新增字段或新表因為數(shù)據(jù)庫表的修改成本沒有想象中高二是使用通用擴展表用鍵值對方式存擴展屬性三是如果字段確實高度不確定可以存儲JSON格式的數(shù)據(jù)在查詢時按需解析注意解析邏輯要兼容空值。核心思路是讓表結(jié)構(gòu)清晰反映當(dāng)前真實業(yè)務(wù)而不是試圖預(yù)測未來。6. 評審與排查把設(shè)計缺陷攔在上線之前6.1 數(shù)據(jù)字典和設(shè)計文檔不是形式主義數(shù)據(jù)字典記錄每張表的用途、每個字段的業(yè)務(wù)含義、類型、約束、來源這份文檔的價值在項目交接和三個月后回看時體現(xiàn)得最明顯。我見過太多人接手一個老項目看著字段名猜業(yè)務(wù)含義猜錯了改出線上問題。數(shù)據(jù)字典不需要花哨一個表格就行表名、字段名、字段含義、是否可空、默認(rèn)值、備注。設(shè)計評審我建議多人參加哪怕圍繞一張表花二十分鐘過一遍。評審的核心問題就幾個這個表承載什么業(yè)務(wù)字段來源是什么更新時會影響什么查詢量預(yù)計多大索引能不能覆蓋核心查詢這幾個問題問完大部分設(shè)計缺陷就會暴露。6.2 表設(shè)計常見問題速查表我把實際項目中經(jīng)常出現(xiàn)的表設(shè)計問題整理成了速查表每次評審設(shè)計時對著過一遍問題歸類典型現(xiàn)象處理建議字段冗余失控同一字段散落多張表更新需要改多處明確冗余只是為了查詢寫清同步機制類型選型錯誤金額用FLOAT時間存字符串金額改DECIMAL時間改DATETIME命名不統(tǒng)一同義字段叫法不一致建庫前制定命名規(guī)則并嚴(yán)格執(zhí)行主鍵設(shè)計缺陷業(yè)務(wù)敏感字段做主鍵換成內(nèi)部代理主鍵業(yè)務(wù)編號加唯一索引索引失效函數(shù)運算、隱式轉(zhuǎn)換導(dǎo)致全表掃描改寫SQL遵守最左前綴原則無審計字段數(shù)據(jù)變更無法追溯補齊created_at、updated_at、creator_id預(yù)留字段濫用大量reserve空字段刪掉需要擴展時再真實增加字段或擴展表6.3 一次典型的設(shè)計評審流程我個人的習(xí)慣是一張新表在實際建表前至少走這樣一輪流程。第一步把業(yè)務(wù)事件和字段來源清單寫出來發(fā)給相關(guān)開發(fā)同事確認(rèn)。第二步根據(jù)字段清單確定實體表和關(guān)聯(lián)關(guān)系畫出表的草稿包括主鍵、外鍵關(guān)系每個字段類型和約束。第三步寫出這個表未來三個月內(nèi)可能出現(xiàn)的核心查詢SQL用這些SQL驗證索引設(shè)計。第四步拿著設(shè)計草稿過一遍速查表看看有沒有類型選型偏大、命名不一致、預(yù)留字段之類的問題。第五步把數(shù)據(jù)字典填入團隊統(tǒng)一維護的文檔平臺。這套流程看起來麻煩但一次又一次避免了上線以后在線ALTER TABLE的驚險時刻。尤其是做大表變更的時候動一次就要涉及停機維護或者使用特定工具在線變更代價非常大。寫在最后數(shù)據(jù)庫設(shè)計沒有銀彈所謂的原則都是在“數(shù)據(jù)一致性”和“查詢性能”之間做取舍。我個人的體會是寧可前期多花點時間做業(yè)務(wù)分析和設(shè)計評審也絕不上線后祈禱不要因為表結(jié)構(gòu)問題出事故。表結(jié)構(gòu)一旦流入線上再改動就是項目管理、數(shù)據(jù)遷移、兼容性處理的連環(huán)工程這個成本往往是最初建表的十倍以上。如果你能在建表之前想一想“三個月后這張表會承擔(dān)什么業(yè)務(wù)”很多未來的技術(shù)債都不用還。