據(jù)庫(kù)維度建模與表結(jié)構(gòu)設(shè)計(jì)實(shí)戰(zhàn))
1. 橫表與豎表兩種維度思維的起點(diǎn)做數(shù)據(jù)庫(kù)設(shè)計(jì)這些年我見過太多人在橫表和豎表之間反復(fù)橫跳。剛?cè)胄袝r(shí)覺得豎表是萬能的后來發(fā)現(xiàn)橫表才是多數(shù)業(yè)務(wù)的正解再到后來才想明白——這兩者根本不是誰替代誰的關(guān)系而是兩種截然不同的維度思維。你今天遇到的大部分表結(jié)構(gòu)問題本質(zhì)上都是在問自己一個(gè)問題你這個(gè)業(yè)務(wù)到底是以實(shí)體為中心還是以屬性為中心橫表的思維是一行一個(gè)完整對(duì)象豎表的思維是一行一個(gè)對(duì)象的一個(gè)屬性。前者像Excel里的一行記錄后者像鍵值對(duì)的堆疊。很多人一聽到豎表就想到EAVEntity-Attribute-Value覺得那是反模式、是坑但我可以負(fù)責(zé)任地說豎表在動(dòng)態(tài)表單、配置中心、權(quán)限系統(tǒng)、日志分析這些場(chǎng)景里至今仍然是不可替代的方案。關(guān)鍵在于你什么時(shí)候該用哪一套以及用了之后要付出什么代價(jià)。這篇文章我想從實(shí)際項(xiàng)目出發(fā)把橫表與豎表的適用場(chǎng)景、設(shè)計(jì)權(quán)衡、性能差異、遷移方案和避坑經(jīng)驗(yàn)一次講透。不管你是剛接觸數(shù)據(jù)庫(kù)設(shè)計(jì)的新手還是已經(jīng)在業(yè)務(wù)里被表結(jié)構(gòu)折磨過的老手這篇文章都能給你一些可以直接落地的參考。我會(huì)盡量少講教科書上的理論多說我在真實(shí)項(xiàng)目里踩過的坑和驗(yàn)證過的方法。2. 橫表設(shè)計(jì)符合直覺但容易被忽略的坑2.1 為什么橫表是大多數(shù)業(yè)務(wù)的默認(rèn)選擇橫表之所以是默認(rèn)選擇是因?yàn)樗钯N近人類的自然認(rèn)知。你看到一張用戶表里面有id、name、phone、email、created_at這些字段一眼就能明白這一行代表一個(gè)完整用戶。業(yè)務(wù)邏輯里要查用戶的手機(jī)號(hào)直接select phone from users where id 1不需要任何額外的join或者聚合。這種直接性帶來的好處是開發(fā)效率高、代碼可讀性強(qiáng)、ORM映射簡(jiǎn)單明了。從數(shù)據(jù)庫(kù)引擎的角度看橫表也是InnoDB這類行式存儲(chǔ)引擎最擅長(zhǎng)的形態(tài)。數(shù)據(jù)按行連續(xù)存放一次IO就能把一行數(shù)據(jù)的所有字段讀出來對(duì)于按主鍵查一行的全部字段這種高頻操作來說性能極好。結(jié)合二級(jí)索引后等值查詢、范圍查詢、排序、分頁這些操作都有成熟的優(yōu)化器路徑?？梢哉f只要業(yè)務(wù)字段是明確的、穩(wěn)定的、可以預(yù)先窮舉的橫表就是最省心的方案。我在電商后臺(tái)做過一個(gè)典型的橫表設(shè)計(jì)訂單主表字段包括訂單號(hào)、用戶id、商品快照、金額、支付狀態(tài)、收貨地址等。這些字段在下單那一刻就全部確定了以后可能增加但不會(huì)頻繁變動(dòng)。配合訂單號(hào)上的唯一索引所有的查詢都走主鍵或普通索引壓測(cè)的時(shí)候單表千萬級(jí)數(shù)據(jù)量下簡(jiǎn)單查詢基本都在毫秒級(jí)別。這種場(chǎng)景下沒有任何理由用豎表。2.2 橫表的字段擴(kuò)展困境橫表最大的痛點(diǎn)是加字段這件事本身。MySQL的ALTER TABLE ADD COLUMN雖然從8.0開始有了INSTANT算法加字段不需要重建表但遇到需要設(shè)置默認(rèn)值、需要回填歷史數(shù)據(jù)、需要加在中間位置這些情況時(shí)照樣會(huì)產(chǎn)生鎖表或全表掃描的風(fēng)險(xiǎn)。而且字段一旦加到幾十上百個(gè)一張表變得非常寬行溢出到溢出頁的概率增加查詢性能也會(huì)受到影響。我經(jīng)歷過一個(gè)真實(shí)案例一個(gè)客戶管理系統(tǒng)的客戶表早期設(shè)計(jì)了30個(gè)字段后來因?yàn)椴煌袠I(yè)的客戶需要記錄不同的定制屬性產(chǎn)品經(jīng)理今天加一個(gè)是否VIP明天加一個(gè)所屬渠道來源后天又加一個(gè)上次跟進(jìn)時(shí)間。半年下來表里躺了70多個(gè)字段其中大量字段對(duì)某類客戶來說永遠(yuǎn)是NULL。表的體積膨脹得很厲害索引也越來越多寫入性能肉眼可見地下降。這就是典型的橫表字段膨脹病——不是橫表本身錯(cuò)了而是把不該放進(jìn)橫表的可變屬性硬塞了進(jìn)去。這時(shí)候你可能會(huì)想到用豎表解決。但先別急豎表也不是銀彈。豎表的代價(jià)同樣很大只是它的代價(jià)發(fā)生在另一個(gè)維度上——查詢的復(fù)雜度。2.3 橫表設(shè)計(jì)時(shí)的字段規(guī)劃方法在動(dòng)手設(shè)計(jì)橫表前先把字段分成三類核心穩(wěn)定字段、可枚舉擴(kuò)展字段、不可窮舉的動(dòng)態(tài)字段。核心穩(wěn)定字段是業(yè)務(wù)的主干比如訂單號(hào)、用戶id、金額、時(shí)間任何記錄都一定有且值穩(wěn)定?？擅杜e擴(kuò)展字段是取值范圍可以預(yù)見的比如狀態(tài)、類型、來源渠道這些可以在有限集合內(nèi)窮舉。不可窮舉的動(dòng)態(tài)字段才是最麻煩的比如不同行業(yè)的自定義屬性、不同客戶需要的不同標(biāo)簽這類字段數(shù)量不確定、命名不確定、類型也不確定。我的經(jīng)驗(yàn)是核心穩(wěn)定字段放心放進(jìn)橫表可枚舉擴(kuò)展字段盡量用整型或短字符串表示配合字典表做映射不可窮舉的動(dòng)態(tài)字段一開始就不要考慮進(jìn)橫表直接用JSON列或者豎向擴(kuò)展表去承載。這樣橫表始終保持一個(gè)可控的寬度不會(huì)因?yàn)闃I(yè)務(wù)需求的增長(zhǎng)而無限膨脹。3. 豎表設(shè)計(jì)靈活性的背后是更高的使用門檻3.1 豎表的核心價(jià)值動(dòng)態(tài)屬性不重建表豎表的核心價(jià)值可以濃縮成一句話**業(yè)務(wù)加屬性的時(shí)候不需要改表結(jié)構(gòu)只需要插數(shù)據(jù)行。**這一點(diǎn)在動(dòng)態(tài)表單、多租戶SaaS、配置平臺(tái)等領(lǐng)域是決定性的。試想一個(gè)問卷系統(tǒng)每創(chuàng)建一個(gè)新問卷題目都不一樣如果每個(gè)問卷都建一張橫表那數(shù)據(jù)庫(kù)里會(huì)散落幾千張結(jié)構(gòu)不同的表運(yùn)維直接崩潰。但用豎表就很簡(jiǎn)單一張問卷表、一張題目表、一張答案表題目和答案都用實(shí)體id 屬性名 屬性值的形態(tài)存儲(chǔ)新增問卷就是新增數(shù)據(jù)完全不需要?jiǎng)颖斫Y(jié)構(gòu)。我在一個(gè)多租戶SaaS項(xiàng)目里用豎表做過自定義字段功能。租戶A的客戶需要記錄行業(yè)類型租戶B的客戶需要記錄員工規(guī)模兩家需求完全不同。用豎表方案每創(chuàng)建一個(gè)新客戶就插入若干行自定義屬性記錄如果用橫表就要在客戶主表里為每個(gè)租戶的不同需求加列最終結(jié)果必然是一張字段極其稀疏的超級(jí)寬表查詢和索引都會(huì)變成噩夢(mèng)。豎表還有一個(gè)容易被忽略的優(yōu)勢(shì)它天然支持稀疏數(shù)據(jù)。橫表里某一行可能90%的字段都是NULL這些NULL不僅浪費(fèi)存儲(chǔ)還會(huì)讓索引失效、讓查詢條件寫起來別扭。豎表只存儲(chǔ)實(shí)際存在的屬性數(shù)據(jù)是稠密的不存在NULL泛濫的問題。3.2 豎表查詢的復(fù)雜度爆炸問題豎表的代價(jià)集中體現(xiàn)在查詢上。你要查一個(gè)用戶的所有屬性需要group by用戶id然后聚合你要篩選出滿足多個(gè)屬性條件的用戶需要多次join自己或者做條件分支聚合你要排序需要把屬性值cast成目標(biāo)類型再排序。這些操作在橫表里都是天然支持的在豎表里卻要繞很大一圈。舉個(gè)我實(shí)際遇到過的例子。需要篩選出行業(yè)為金融且員工規(guī)模大于100的客戶。豎表存儲(chǔ)下SQL會(huì)寫成類似這樣SELECT entity_id FROM customer_attributes WHERE (attr_name industry AND attr_value finance) OR (attr_name staff_size AND CAST(attr_value AS SIGNED) 100) GROUP BY entity_id HAVING COUNT(DISTINCT attr_name) 2;這條SQL本身還能跑但當(dāng)customer_attributes表達(dá)到千萬級(jí)attr_name和attr_value上的索引設(shè)計(jì)又不合理時(shí)查詢會(huì)退化成一次大范圍掃描。而且隨著篩選條件從2個(gè)變成5個(gè)、10個(gè)SQL的復(fù)雜度和性能開銷會(huì)成倍增長(zhǎng)。豎表的即席查詢能力本質(zhì)上是被犧牲掉的。更麻煩的是排序。橫表里按金額排序就是order by amount走索引那叫一個(gè)快。豎表里你得先保證所有行的amount都存儲(chǔ)成同一類型再在應(yīng)用層或SQL里做一次類型轉(zhuǎn)換和子查詢。這種操作的性能天花板很低數(shù)據(jù)量稍大就會(huì)讓人頭疼。所以豎表方案上線時(shí)我一般會(huì)同步評(píng)估兩個(gè)兜底手段一是把高頻的過濾和排序?qū)傩匀哂喑蓹M表字段二是引入搜索引擎或OLAP引擎做即席查詢。兩個(gè)手段都意味著架構(gòu)復(fù)雜度上升這是豎表方案必須提前納入考量的一部分。3.3 豎表設(shè)計(jì)時(shí)容易忽略的類型與索引問題豎表里屬性值列的類型選擇是我見過最多人踩坑的地方。為了通用性很多人直接varchar到底不管存的是數(shù)字、日期還是布爾值。短期看問題不大時(shí)間一長(zhǎng)就暴露了屬性值為100和99的時(shí)候varchar排序會(huì)按字典序排99排在100后面完全不符合數(shù)字直覺篩選age 30的時(shí)候varchar比較的結(jié)果也是錯(cuò)的。解決方案一般有幾個(gè)加一列attr_type記錄這個(gè)屬性的數(shù)據(jù)類型查詢時(shí)按類型轉(zhuǎn)換屬性值列拆成字符串、數(shù)字、日期三列常見的方案是attr_value_str、attr_value_num、attr_value_date寫入時(shí)按類型落到不同列查詢時(shí)直接走對(duì)應(yīng)列避免每次cast干脆在應(yīng)用層把值序列化成JSON但這樣就沒法在數(shù)據(jù)庫(kù)層面做條件查詢了。我用得比較多的是第二種方案三個(gè)值列配合attr_name上的索引既能保證靈活又不至于讓查詢徹底沒法走索引。代價(jià)是代碼邏輯變多一點(diǎn)寫入時(shí)要判斷類型落到哪個(gè)列但相比查詢時(shí)全表cast劃算得多。豎表的索引設(shè)計(jì)也和橫表思路不同。橫表一般圍繞業(yè)務(wù)查詢建二級(jí)索引豎表的高頻查詢模式是按實(shí)體查屬性和按屬性查實(shí)體所以索引通常以(實(shí)體id, 屬性名)和(屬性名, 屬性值)為前綴。前者適合一次取出一個(gè)實(shí)體的全部屬性后者適合反向檢索某個(gè)屬性值對(duì)應(yīng)的實(shí)體列表。這兩類索引務(wù)必提前規(guī)劃不要指望在設(shè)計(jì)表的時(shí)候塞一個(gè)通用索引就能包打天下豎表的索引一旦缺失查詢代價(jià)是橫表的好幾倍。4. 橫表與豎表的適用場(chǎng)景判斷與混合方案4.1 一張表直接說清楚什么場(chǎng)景選橫表什么場(chǎng)景選豎表我在給團(tuán)隊(duì)做表結(jié)構(gòu)評(píng)審的時(shí)候經(jīng)常直接用下面這張表對(duì)照判斷判斷維度適合橫表適合豎表屬性數(shù)量確定、有限、可窮舉不確定、動(dòng)態(tài)增長(zhǎng)屬性變化頻率低加字段是低頻事件高新屬性隨時(shí)出現(xiàn)查詢模式按實(shí)體查多個(gè)固定字段按實(shí)體查全部/按屬性反查實(shí)體稀疏度低很少有NULL高大量行為沒有對(duì)應(yīng)屬性排序/聚合需求強(qiáng)直接走索引弱或可用外部引擎補(bǔ)充典型場(chǎng)景訂單、用戶、商品、交易流水動(dòng)態(tài)表單、配置項(xiàng)、用戶標(biāo)簽、屬性擴(kuò)展判斷方法很簡(jiǎn)單如果你的業(yè)務(wù)屬性在需求文檔里就能全部列出來而且未來一兩年內(nèi)不太會(huì)新增橫表就是最優(yōu)解如果屬性列表在產(chǎn)品文檔里寫不完或者寫完了但今天加一個(gè)明天刪一個(gè)豎表才是對(duì)的選擇。大多數(shù)業(yè)務(wù)系統(tǒng)是前者的狀態(tài)少數(shù)平臺(tái)型系統(tǒng)是后者的狀態(tài)。4.2 混合方案一張主橫表加一張屬性豎表在實(shí)際項(xiàng)目里我很少純粹地只用橫表或只用豎表絕大多數(shù)生產(chǎn)系統(tǒng)的合理形態(tài)是兩者結(jié)合的混合結(jié)構(gòu)。核心穩(wěn)定的字段繼續(xù)用橫表承載保持查詢的簡(jiǎn)單高效動(dòng)態(tài)擴(kuò)展的字段放進(jìn)一張垂直屬性表用實(shí)體id關(guān)聯(lián)回主表。還是拿那個(gè)多租戶SaaS項(xiàng)目舉例。客戶主表里只有id、租戶id、客戶名稱、創(chuàng)建時(shí)間、更新時(shí)間這些核心字段。自定義字段全部放在customer_attributes表里結(jié)構(gòu)是(tenant_id, customer_id, attr_name, attr_value_str, attr_value_num, attr_value_date, updated_at)。查客戶基礎(chǔ)信息的時(shí)候走主表秒出查自定義屬性的時(shí)候走屬性表按customer_id一次性取出全部屬性在應(yīng)用層組裝成Map返回給前端。這樣既保住了主表的整潔又滿足了租戶各自的擴(kuò)展需求。唯一要注意的是主表和屬性表之間的數(shù)據(jù)一致性需要靠應(yīng)用層事務(wù)保證要么在同一個(gè)事務(wù)里分別寫兩張表要么通過消息隊(duì)列異步同步否則容易出現(xiàn)主表有記錄但屬性表缺行的情況。混合方案還有一種變體把高頻使用的幾個(gè)動(dòng)態(tài)屬性冗余回主表。比如測(cè)試后發(fā)現(xiàn)用戶等級(jí)這個(gè)屬性是查詢頻率最高的篩選條件之一雖然它是動(dòng)態(tài)出現(xiàn)的但我把它提升為主表的一個(gè)正式列屬性表里仍然保留它作為審計(jì)記錄。這樣查詢走主表索引屬性表只負(fù)責(zé)存歷史。這個(gè)動(dòng)態(tài)屬性轉(zhuǎn)正的過程是橫豎混合方案長(zhǎng)期演進(jìn)中很常見的操作。4.3 混合方案的數(shù)據(jù)一致性與組裝策略混合方案最大的坑在于應(yīng)用層的組裝邏輯。橫表的數(shù)據(jù)查出來就是一行對(duì)象豎表的數(shù)據(jù)查出來是多行鍵值對(duì)要在應(yīng)用層把它們合并成完整對(duì)象這一步最容易寫出性能爛的代碼。我見過有人用循環(huán)逐條查屬性表——查出100個(gè)客戶再循環(huán)100次去查每個(gè)客戶的屬性直接搞出SQL的N1問題接口響應(yīng)時(shí)間從50ms飆升到3秒。正確做法是一次性查出所有相關(guān)實(shí)體的屬性按實(shí)體id分組在內(nèi)存里組裝SELECT customer_id, attr_name, attr_value_str, attr_value_num, attr_value_date FROM customer_attributes WHERE customer_id IN (1, 2, 3, ...);然后在應(yīng)用層用一個(gè)MapLong, MapString, Object接收結(jié)果。這樣查詢次數(shù)從N1降為2次數(shù)據(jù)量雖然翻倍但網(wǎng)絡(luò)和數(shù)據(jù)庫(kù)的往返次數(shù)大幅減少響應(yīng)時(shí)間完全可控。另外我強(qiáng)烈建議對(duì)組裝邏輯做統(tǒng)一的工具類封裝團(tuán)隊(duì)里所有人共用同一套實(shí)體屬性的讀取和組裝入口避免每個(gè)人各自寫一套導(dǎo)致緩存失效、鍵名不統(tǒng)一、類型轉(zhuǎn)換出錯(cuò)等問題。5. 橫表轉(zhuǎn)豎表、豎表轉(zhuǎn)橫表的遷移實(shí)戰(zhàn)5.1 什么時(shí)候需要遷移什么時(shí)候不該遷移現(xiàn)實(shí)中你很少有機(jī)會(huì)在項(xiàng)目一開始就選對(duì)形態(tài)更多情況是業(yè)務(wù)跑了一半才發(fā)現(xiàn)表結(jié)構(gòu)不對(duì)勁。我遇到過兩類典型訴求一類是橫表字段實(shí)在太多想拆成豎表減輕寬度壓力一類是豎表查詢實(shí)在痛苦想把高頻屬性轉(zhuǎn)成橫表列。在動(dòng)手遷移之前我建議你先回答三個(gè)問題現(xiàn)在的路徑能不能靠加索引、加緩存、加只讀從庫(kù)撐住遷移期間業(yè)務(wù)能否容忍停寫或降級(jí)遷移后性能收益是否可量化如果三個(gè)答案都是否那就不該遷移先改造查詢方式或冗余字段更劃算。如果至少兩個(gè)答案是肯定的遷移才有執(zhí)行的基礎(chǔ)。5.2 橫表轉(zhuǎn)豎表的兩種典型路徑橫表轉(zhuǎn)豎表的場(chǎng)景通常發(fā)生在老系統(tǒng)改造中。豎表的目標(biāo)一般很明確讓新業(yè)務(wù)屬性不用再改表結(jié)構(gòu)。執(zhí)行時(shí)有兩條路徑可選。路徑一是全量翻轉(zhuǎn)把橫表的每一行拆成多行豎表記錄字段名變成attr_name字段值變成attr_value。適合屬性數(shù)量少但行數(shù)巨大的表SQL寫起來直接回填快。路徑二是增量同步業(yè)務(wù)雙寫先在新豎表里寫入新增和變更的數(shù)據(jù)再把歷史數(shù)據(jù)分批掃描回填。適合不能停機(jī)的大表。我實(shí)際用過的是第二種一個(gè)千萬級(jí)的老用戶表每小時(shí)避開業(yè)務(wù)高峰期跑一批回填任務(wù)預(yù)計(jì)兩天跑完期間業(yè)務(wù)無感。回填過程中有個(gè)細(xì)節(jié)必須特別注意橫向表字段的取值類型五花八門有的是int、有的是datetime、有的是varchar落到豎表時(shí)必須明確類型映射規(guī)則。我前面提到過用三值列str/num/date來承接回填腳本里必須對(duì)每個(gè)源字段配置目標(biāo)類型不能用一個(gè)統(tǒng)一的toString糊弄過去否則后面查詢cast時(shí)一堆坑。5.3 豎表轉(zhuǎn)橫表高頻屬性轉(zhuǎn)正法豎表轉(zhuǎn)橫表則相反一般是豎表查詢性能已經(jīng)讓業(yè)務(wù)無法接受了或者在線分析場(chǎng)景的需求越來越重。最穩(wěn)妥的做法是高頻屬性轉(zhuǎn)正法先通過統(tǒng)計(jì)和業(yè)務(wù)溝通圈定查詢次數(shù)最多的5到10個(gè)屬性把它們作為真實(shí)列加入主表再寫一個(gè)回填任務(wù)從豎表里查出這些屬性的值填充到新列最后修改應(yīng)用代碼讓寫操作同時(shí)更新主表列和豎表行讀操作優(yōu)先走主表列豎表行作為歷史數(shù)據(jù)留檔。我做過一次這樣的遷移結(jié)果非常直觀原本一個(gè)需要join三層的動(dòng)態(tài)屬性篩選接口從1.8秒降到40毫秒左右業(yè)務(wù)方基本沒感知到切換過程。注意用不到的屬性不要轉(zhuǎn)否則你只是把之前的豎表靈活性換成了一種新的橫表膨脹反而兩頭不討好。5.4 遷移期間的兼容與回滾方案任何表結(jié)構(gòu)遷移都有風(fēng)險(xiǎn)我執(zhí)行遷移時(shí)的標(biāo)準(zhǔn)動(dòng)作是先新老并行新舊兩套同時(shí)寫入和讀取做數(shù)據(jù)對(duì)比再灰度切換先讓5%流量走新結(jié)構(gòu)觀察監(jiān)控和錯(cuò)誤率最后才全量切換并保留舊表數(shù)據(jù)至少30天以備隨時(shí)回滾。這里說的回滾不只是代碼回滾還包括數(shù)據(jù)回滾——如果新結(jié)構(gòu)在灰度期間暴露出數(shù)據(jù)不一致你要能快速把舊表數(shù)據(jù)翻回來。遷移中有一類問題最容易忽視應(yīng)用代碼里如果存在對(duì)舊字段或舊屬性名的硬編碼依賴切換后sql直接報(bào)錯(cuò)。所以遷移前要做一次全局搜索把所有涉及舊結(jié)構(gòu)的SQL和ORM映射梳理清楚最好用統(tǒng)一的DAO層收口避免散落各處的query直接訪問物理表結(jié)構(gòu)。6. 高級(jí)視角維度建模思維與未來擴(kuò)展6.1 橫豎之爭(zhēng)背后是實(shí)體與屬性的維度認(rèn)知把橫表和豎表放到更大的視野里看它們其實(shí)是兩種維度建模思維的體現(xiàn)。橫表把實(shí)體的每個(gè)屬性作為一個(gè)維度展開在列上豎表把屬性本身當(dāng)成一種數(shù)據(jù)維度值和實(shí)體之間的關(guān)系退化為一行記錄。這個(gè)區(qū)別在業(yè)務(wù)建模階段就應(yīng)該想清楚而不是等物理表建好了再糾結(jié)。我常用的建模思路是先做概念模型實(shí)體是誰屬性有哪些哪些屬性是這個(gè)實(shí)體之所以成為它的本質(zhì)屬性哪些屬性是附屬在不同場(chǎng)景下才存在的擴(kuò)展屬性。本質(zhì)屬性進(jìn)橫表擴(kuò)展屬性進(jìn)豎表或JSON列。這一步想清楚了后面物理設(shè)計(jì)基本就是水到渠成的事。6.2 JSON列作為橫豎表之外的第三條路現(xiàn)在主流數(shù)據(jù)庫(kù)都支持JSON類型MySQL從5.7開始、PostgreSQL更是把JSONB玩出了花。JSON列可以理解成一種折中的豎表它把動(dòng)態(tài)屬性打包成一整個(gè)文檔存在一個(gè)列里免去了豎表的join保留了靈活擴(kuò)展的能力。代價(jià)是查詢能力受限——MySQL的JSON函數(shù)雖然能查、能索引部分字段但復(fù)雜查詢性能和可維護(hù)性都不如真正的列。我現(xiàn)在的習(xí)慣是動(dòng)態(tài)屬性數(shù)量少比如5到10個(gè)內(nèi)、查詢模式簡(jiǎn)單優(yōu)先用JSON列動(dòng)態(tài)屬性數(shù)量多、且需要頻繁作為篩選和聚合條件還是老實(shí)上豎表或干脆用文檔數(shù)據(jù)庫(kù)。JSON列適合做不需要深度查詢的擴(kuò)展信息不適合做核心業(yè)務(wù)篩選字段。這一點(diǎn)在實(shí)際項(xiàng)目中反復(fù)驗(yàn)證過希望你不要等踩了坑再回頭。6.3 從OLTP到OLAP橫豎表選擇在分析場(chǎng)景中的延伸還有一個(gè)延伸維度值得提一下同樣橫豎之爭(zhēng)在OLAP分析場(chǎng)景里結(jié)論可能會(huì)反轉(zhuǎn)。分析場(chǎng)景下數(shù)據(jù)量巨大、列數(shù)眾多列式存儲(chǔ)引擎比如ClickHouse、Parquet文件天然更擅長(zhǎng)按列掃描豎表模型反而會(huì)極大放大存儲(chǔ)和掃描開銷。所以在數(shù)倉(cāng)和數(shù)據(jù)分析領(lǐng)域橫表寬表幾乎是絕對(duì)的主流豎表幾乎只在鍵值對(duì)日志和指標(biāo)采集場(chǎng)景里出現(xiàn)。你在做OLTP業(yè)務(wù)設(shè)計(jì)時(shí)用豎表可能很靈活但一旦要把數(shù)據(jù)同步到數(shù)倉(cāng)做分析通常需要把豎表再透視成橫表或者用ETL時(shí)的聚合邏輯把屬性展開為列。這個(gè)規(guī)律我自己體會(huì)很深同一個(gè)業(yè)務(wù)OLTP側(cè)重靈活性時(shí)選豎表OLAP側(cè)重掃描效率時(shí)選橫表寬表兩者中間靠ETL銜接。如果你在業(yè)務(wù)系統(tǒng)里用了豎表提前設(shè)計(jì)好豎轉(zhuǎn)橫的導(dǎo)出視圖會(huì)給后續(xù)的數(shù)據(jù)分析省下大量時(shí)間。7. 橫表與豎表的選型清單和避坑心得7.1 一張選型自查清單拿去就能用寫了這么多最后整理一份可以直接拿去用的自查清單核心字段是否穩(wěn)定且可以窮舉是→橫表。業(yè)務(wù)是否需要頻繁新增屬性且不希望改表結(jié)構(gòu)是→豎表。屬性值是否存在大量NULL是→優(yōu)先考慮豎表或JSON列。查詢是否經(jīng)常按幾個(gè)固定字段篩選排序是→橫表加索引最香。查詢是否需要根據(jù)任意屬性組合反查實(shí)體是→豎表要慎重建議考慮搜索引擎或轉(zhuǎn)正高頻屬性。是否允許應(yīng)用層做屬性組裝是→混合方案可行否→老老實(shí)實(shí)橫表。7.2 我在實(shí)戰(zhàn)中反復(fù)踩過的五個(gè)坑第一個(gè)坑豎表字段值類型不統(tǒng)一。這個(gè)問題最隱蔽初期數(shù)據(jù)量小看不出問題數(shù)據(jù)量大了之后排序、范圍篩選全部翻車。解決方案就是三值列和類型映射千萬別怕麻煩。第二個(gè)坑橫表加字段太隨意?？傁胫燃觽€(gè)字段試試結(jié)果字段越來越多表越來越寬等到想收手時(shí)已經(jīng)動(dòng)不了了。橫表加字段前必須走審批和評(píng)估至少要問一句這個(gè)字段能窮舉嗎能不能放到JSON列或擴(kuò)展表里。第三個(gè)坑混合方案缺少一致性保障。主表和屬性表之間如果不在同一個(gè)事務(wù)里寫入并發(fā)場(chǎng)景下很容易出現(xiàn)主表已有記錄但屬性表遲了幾百毫秒才寫入的情況一旦中間讀操作發(fā)生就會(huì)拿到不完整的數(shù)據(jù)。事務(wù)保底雙寫必須同步。第四個(gè)坑遷移回填不考慮存量數(shù)據(jù)的類型和格式。源系統(tǒng)里同一個(gè)字段在不同時(shí)期格式可能不一樣有2024-01-01也有2024/01/01回填時(shí)統(tǒng)一格式是逃不掉的一步否則分析時(shí)還是要返工。第五個(gè)坑為了靈活過度設(shè)計(jì)。有些團(tuán)隊(duì)一聽豎表靈活什么屬性都往豎表里塞結(jié)果最核心的用戶名列都在豎表里查詢?nèi)孔兂删酆喜僮鞯貌粌斒?。豎表是給動(dòng)態(tài)屬性準(zhǔn)備的不是給核心屬性準(zhǔn)備的。7.3 回顧維度思維才是核心能力回首這些年的數(shù)據(jù)庫(kù)設(shè)計(jì)經(jīng)歷我覺得橫表與豎表之爭(zhēng)真正比拼的不是孰優(yōu)孰劣而是設(shè)計(jì)者對(duì)業(yè)務(wù)的理解深度。你越早想清楚業(yè)務(wù)的屬性是穩(wěn)定的還是動(dòng)態(tài)的、查詢是固定的還是即席的、擴(kuò)展是低頻還是高頻就越能在第一時(shí)間做出對(duì)的選擇。橫表不丟人豎表也不丟人丟人的是不分析業(yè)務(wù)就盲目開搞搞了兩三個(gè)月才發(fā)現(xiàn)方向錯(cuò)了返工。我自己現(xiàn)在接任何新項(xiàng)目動(dòng)手建表之前都會(huì)先花半天時(shí)間做一次屬性分類和查詢模式梳理哪怕只是在一張白紙上寫寫畫畫。這一步看著不起眼但可以省掉后面數(shù)不清的加班和改表。希望這篇文章里的經(jīng)驗(yàn)和踩坑記錄能幫你少走幾步彎路。 橫表與豎表如何選？數(shù)據(jù)庫(kù)維度建模與表結(jié)構(gòu)設(shè)計(jì)實(shí)戰(zhàn)
1. 橫表與豎表兩種維度思維的起點(diǎn)做數(shù)據(jù)庫(kù)設(shè)計(jì)這些年我見過太多人在橫表和豎表之間反復(fù)橫跳。剛?cè)胄袝r(shí)覺得豎表是萬能的后來發(fā)現(xiàn)橫表才是多數(shù)業(yè)務(wù)的正解再到后來才想明白——這兩者根本不是誰替代誰的關(guān)系而是兩種截然不同的維度思維。你今天遇到的大部分表結(jié)構(gòu)問題本質(zhì)上都是在問自己一個(gè)問題你這個(gè)業(yè)務(wù)到底是以實(shí)體為中心還是以屬性為中心橫表的思維是一行一個(gè)完整對(duì)象豎表的思維是一行一個(gè)對(duì)象的一個(gè)屬性。前者像Excel里的一行記錄后者像鍵值對(duì)的堆疊。很多人一聽到豎表就想到EAVEntity-Attribute-Value覺得那是反模式、是坑但我可以負(fù)責(zé)任地說豎表在動(dòng)態(tài)表單、配置中心、權(quán)限系統(tǒng)、日志分析這些場(chǎng)景里至今仍然是不可替代的方案。關(guān)鍵在于你什么時(shí)候該用哪一套以及用了之后要付出什么代價(jià)。這篇文章我想從實(shí)際項(xiàng)目出發(fā)把橫表與豎表的適用場(chǎng)景、設(shè)計(jì)權(quán)衡、性能差異、遷移方案和避坑經(jīng)驗(yàn)一次講透。不管你是剛接觸數(shù)據(jù)庫(kù)設(shè)計(jì)的新手還是已經(jīng)在業(yè)務(wù)里被表結(jié)構(gòu)折磨過的老手這篇文章都能給你一些可以直接落地的參考。我會(huì)盡量少講教科書上的理論多說我在真實(shí)項(xiàng)目里踩過的坑和驗(yàn)證過的方法。2. 橫表設(shè)計(jì)符合直覺但容易被忽略的坑2.1 為什么橫表是大多數(shù)業(yè)務(wù)的默認(rèn)選擇橫表之所以是默認(rèn)選擇是因?yàn)樗钯N近人類的自然認(rèn)知。你看到一張用戶表里面有id、name、phone、email、created_at這些字段一眼就能明白這一行代表一個(gè)完整用戶。業(yè)務(wù)邏輯里要查用戶的手機(jī)號(hào)直接select phone from users where id 1不需要任何額外的join或者聚合。這種直接性帶來的好處是開發(fā)效率高、代碼可讀性強(qiáng)、ORM映射簡(jiǎn)單明了。從數(shù)據(jù)庫(kù)引擎的角度看橫表也是InnoDB這類行式存儲(chǔ)引擎最擅長(zhǎng)的形態(tài)。數(shù)據(jù)按行連續(xù)存放一次IO就能把一行數(shù)據(jù)的所有字段讀出來對(duì)于按主鍵查一行的全部字段這種高頻操作來說性能極好。結(jié)合二級(jí)索引后等值查詢、范圍查詢、排序、分頁這些操作都有成熟的優(yōu)化器路徑?？梢哉f只要業(yè)務(wù)字段是明確的、穩(wěn)定的、可以預(yù)先窮舉的橫表就是最省心的方案。我在電商后臺(tái)做過一個(gè)典型的橫表設(shè)計(jì)訂單主表字段包括訂單號(hào)、用戶id、商品快照、金額、支付狀態(tài)、收貨地址等。這些字段在下單那一刻就全部確定了以后可能增加但不會(huì)頻繁變動(dòng)。配合訂單號(hào)上的唯一索引所有的查詢都走主鍵或普通索引壓測(cè)的時(shí)候單表千萬級(jí)數(shù)據(jù)量下簡(jiǎn)單查詢基本都在毫秒級(jí)別。這種場(chǎng)景下沒有任何理由用豎表。2.2 橫表的字段擴(kuò)展困境橫表最大的痛點(diǎn)是加字段這件事本身。MySQL的ALTER TABLE ADD COLUMN雖然從8.0開始有了INSTANT算法加字段不需要重建表但遇到需要設(shè)置默認(rèn)值、需要回填歷史數(shù)據(jù)、需要加在中間位置這些情況時(shí)照樣會(huì)產(chǎn)生鎖表或全表掃描的風(fēng)險(xiǎn)。而且字段一旦加到幾十上百個(gè)一張表變得非常寬行溢出到溢出頁的概率增加查詢性能也會(huì)受到影響。我經(jīng)歷過一個(gè)真實(shí)案例一個(gè)客戶管理系統(tǒng)的客戶表早期設(shè)計(jì)了30個(gè)字段后來因?yàn)椴煌袠I(yè)的客戶需要記錄不同的定制屬性產(chǎn)品經(jīng)理今天加一個(gè)是否VIP明天加一個(gè)所屬渠道來源后天又加一個(gè)上次跟進(jìn)時(shí)間。半年下來表里躺了70多個(gè)字段其中大量字段對(duì)某類客戶來說永遠(yuǎn)是NULL。表的體積膨脹得很厲害索引也越來越多寫入性能肉眼可見地下降。這就是典型的橫表字段膨脹病——不是橫表本身錯(cuò)了而是把不該放進(jìn)橫表的可變屬性硬塞了進(jìn)去。這時(shí)候你可能會(huì)想到用豎表解決。但先別急豎表也不是銀彈。豎表的代價(jià)同樣很大只是它的代價(jià)發(fā)生在另一個(gè)維度上——查詢的復(fù)雜度。2.3 橫表設(shè)計(jì)時(shí)的字段規(guī)劃方法在動(dòng)手設(shè)計(jì)橫表前先把字段分成三類核心穩(wěn)定字段、可枚舉擴(kuò)展字段、不可窮舉的動(dòng)態(tài)字段。核心穩(wěn)定字段是業(yè)務(wù)的主干比如訂單號(hào)、用戶id、金額、時(shí)間任何記錄都一定有且值穩(wěn)定?？擅杜e擴(kuò)展字段是取值范圍可以預(yù)見的比如狀態(tài)、類型、來源渠道這些可以在有限集合內(nèi)窮舉。不可窮舉的動(dòng)態(tài)字段才是最麻煩的比如不同行業(yè)的自定義屬性、不同客戶需要的不同標(biāo)簽這類字段數(shù)量不確定、命名不確定、類型也不確定。我的經(jīng)驗(yàn)是核心穩(wěn)定字段放心放進(jìn)橫表可枚舉擴(kuò)展字段盡量用整型或短字符串表示配合字典表做映射不可窮舉的動(dòng)態(tài)字段一開始就不要考慮進(jìn)橫表直接用JSON列或者豎向擴(kuò)展表去承載。這樣橫表始終保持一個(gè)可控的寬度不會(huì)因?yàn)闃I(yè)務(wù)需求的增長(zhǎng)而無限膨脹。3. 豎表設(shè)計(jì)靈活性的背后是更高的使用門檻3.1 豎表的核心價(jià)值動(dòng)態(tài)屬性不重建表豎表的核心價(jià)值可以濃縮成一句話**業(yè)務(wù)加屬性的時(shí)候不需要改表結(jié)構(gòu)只需要插數(shù)據(jù)行。**這一點(diǎn)在動(dòng)態(tài)表單、多租戶SaaS、配置平臺(tái)等領(lǐng)域是決定性的。試想一個(gè)問卷系統(tǒng)每創(chuàng)建一個(gè)新問卷題目都不一樣如果每個(gè)問卷都建一張橫表那數(shù)據(jù)庫(kù)里會(huì)散落幾千張結(jié)構(gòu)不同的表運(yùn)維直接崩潰。但用豎表就很簡(jiǎn)單一張問卷表、一張題目表、一張答案表題目和答案都用實(shí)體id 屬性名 屬性值的形態(tài)存儲(chǔ)新增問卷就是新增數(shù)據(jù)完全不需要?jiǎng)颖斫Y(jié)構(gòu)。我在一個(gè)多租戶SaaS項(xiàng)目里用豎表做過自定義字段功能。租戶A的客戶需要記錄行業(yè)類型租戶B的客戶需要記錄員工規(guī)模兩家需求完全不同。用豎表方案每創(chuàng)建一個(gè)新客戶就插入若干行自定義屬性記錄如果用橫表就要在客戶主表里為每個(gè)租戶的不同需求加列最終結(jié)果必然是一張字段極其稀疏的超級(jí)寬表查詢和索引都會(huì)變成噩夢(mèng)。豎表還有一個(gè)容易被忽略的優(yōu)勢(shì)它天然支持稀疏數(shù)據(jù)。橫表里某一行可能90%的字段都是NULL這些NULL不僅浪費(fèi)存儲(chǔ)還會(huì)讓索引失效、讓查詢條件寫起來別扭。豎表只存儲(chǔ)實(shí)際存在的屬性數(shù)據(jù)是稠密的不存在NULL泛濫的問題。3.2 豎表查詢的復(fù)雜度爆炸問題豎表的代價(jià)集中體現(xiàn)在查詢上。你要查一個(gè)用戶的所有屬性需要group by用戶id然后聚合你要篩選出滿足多個(gè)屬性條件的用戶需要多次join自己或者做條件分支聚合你要排序需要把屬性值cast成目標(biāo)類型再排序。這些操作在橫表里都是天然支持的在豎表里卻要繞很大一圈。舉個(gè)我實(shí)際遇到過的例子。需要篩選出行業(yè)為金融且員工規(guī)模大于100的客戶。豎表存儲(chǔ)下SQL會(huì)寫成類似這樣SELECT entity_id FROM customer_attributes WHERE (attr_name industry AND attr_value finance) OR (attr_name staff_size AND CAST(attr_value AS SIGNED) 100) GROUP BY entity_id HAVING COUNT(DISTINCT attr_name) 2;這條SQL本身還能跑但當(dāng)customer_attributes表達(dá)到千萬級(jí)attr_name和attr_value上的索引設(shè)計(jì)又不合理時(shí)查詢會(huì)退化成一次大范圍掃描。而且隨著篩選條件從2個(gè)變成5個(gè)、10個(gè)SQL的復(fù)雜度和性能開銷會(huì)成倍增長(zhǎng)。豎表的即席查詢能力本質(zhì)上是被犧牲掉的。更麻煩的是排序。橫表里按金額排序就是order by amount走索引那叫一個(gè)快。豎表里你得先保證所有行的amount都存儲(chǔ)成同一類型再在應(yīng)用層或SQL里做一次類型轉(zhuǎn)換和子查詢。這種操作的性能天花板很低數(shù)據(jù)量稍大就會(huì)讓人頭疼。所以豎表方案上線時(shí)我一般會(huì)同步評(píng)估兩個(gè)兜底手段一是把高頻的過濾和排序?qū)傩匀哂喑蓹M表字段二是引入搜索引擎或OLAP引擎做即席查詢。兩個(gè)手段都意味著架構(gòu)復(fù)雜度上升這是豎表方案必須提前納入考量的一部分。3.3 豎表設(shè)計(jì)時(shí)容易忽略的類型與索引問題豎表里屬性值列的類型選擇是我見過最多人踩坑的地方。為了通用性很多人直接varchar到底不管存的是數(shù)字、日期還是布爾值。短期看問題不大時(shí)間一長(zhǎng)就暴露了屬性值為100和99的時(shí)候varchar排序會(huì)按字典序排99排在100后面完全不符合數(shù)字直覺篩選age 30的時(shí)候varchar比較的結(jié)果也是錯(cuò)的。解決方案一般有幾個(gè)加一列attr_type記錄這個(gè)屬性的數(shù)據(jù)類型查詢時(shí)按類型轉(zhuǎn)換屬性值列拆成字符串、數(shù)字、日期三列常見的方案是attr_value_str、attr_value_num、attr_value_date寫入時(shí)按類型落到不同列查詢時(shí)直接走對(duì)應(yīng)列避免每次cast干脆在應(yīng)用層把值序列化成JSON但這樣就沒法在數(shù)據(jù)庫(kù)層面做條件查詢了。我用得比較多的是第二種方案三個(gè)值列配合attr_name上的索引既能保證靈活又不至于讓查詢徹底沒法走索引。代價(jià)是代碼邏輯變多一點(diǎn)寫入時(shí)要判斷類型落到哪個(gè)列但相比查詢時(shí)全表cast劃算得多。豎表的索引設(shè)計(jì)也和橫表思路不同。橫表一般圍繞業(yè)務(wù)查詢建二級(jí)索引豎表的高頻查詢模式是按實(shí)體查屬性和按屬性查實(shí)體所以索引通常以(實(shí)體id, 屬性名)和(屬性名, 屬性值)為前綴。前者適合一次取出一個(gè)實(shí)體的全部屬性后者適合反向檢索某個(gè)屬性值對(duì)應(yīng)的實(shí)體列表。這兩類索引務(wù)必提前規(guī)劃不要指望在設(shè)計(jì)表的時(shí)候塞一個(gè)通用索引就能包打天下豎表的索引一旦缺失查詢代價(jià)是橫表的好幾倍。4. 橫表與豎表的適用場(chǎng)景判斷與混合方案4.1 一張表直接說清楚什么場(chǎng)景選橫表什么場(chǎng)景選豎表我在給團(tuán)隊(duì)做表結(jié)構(gòu)評(píng)審的時(shí)候經(jīng)常直接用下面這張表對(duì)照判斷判斷維度適合橫表適合豎表屬性數(shù)量確定、有限、可窮舉不確定、動(dòng)態(tài)增長(zhǎng)屬性變化頻率低加字段是低頻事件高新屬性隨時(shí)出現(xiàn)查詢模式按實(shí)體查多個(gè)固定字段按實(shí)體查全部/按屬性反查實(shí)體稀疏度低很少有NULL高大量行為沒有對(duì)應(yīng)屬性排序/聚合需求強(qiáng)直接走索引弱或可用外部引擎補(bǔ)充典型場(chǎng)景訂單、用戶、商品、交易流水動(dòng)態(tài)表單、配置項(xiàng)、用戶標(biāo)簽、屬性擴(kuò)展判斷方法很簡(jiǎn)單如果你的業(yè)務(wù)屬性在需求文檔里就能全部列出來而且未來一兩年內(nèi)不太會(huì)新增橫表就是最優(yōu)解如果屬性列表在產(chǎn)品文檔里寫不完或者寫完了但今天加一個(gè)明天刪一個(gè)豎表才是對(duì)的選擇。大多數(shù)業(yè)務(wù)系統(tǒng)是前者的狀態(tài)少數(shù)平臺(tái)型系統(tǒng)是后者的狀態(tài)。4.2 混合方案一張主橫表加一張屬性豎表在實(shí)際項(xiàng)目里我很少純粹地只用橫表或只用豎表絕大多數(shù)生產(chǎn)系統(tǒng)的合理形態(tài)是兩者結(jié)合的混合結(jié)構(gòu)。核心穩(wěn)定的字段繼續(xù)用橫表承載保持查詢的簡(jiǎn)單高效動(dòng)態(tài)擴(kuò)展的字段放進(jìn)一張垂直屬性表用實(shí)體id關(guān)聯(lián)回主表。還是拿那個(gè)多租戶SaaS項(xiàng)目舉例。客戶主表里只有id、租戶id、客戶名稱、創(chuàng)建時(shí)間、更新時(shí)間這些核心字段。自定義字段全部放在customer_attributes表里結(jié)構(gòu)是(tenant_id, customer_id, attr_name, attr_value_str, attr_value_num, attr_value_date, updated_at)。查客戶基礎(chǔ)信息的時(shí)候走主表秒出查自定義屬性的時(shí)候走屬性表按customer_id一次性取出全部屬性在應(yīng)用層組裝成Map返回給前端。這樣既保住了主表的整潔又滿足了租戶各自的擴(kuò)展需求。唯一要注意的是主表和屬性表之間的數(shù)據(jù)一致性需要靠應(yīng)用層事務(wù)保證要么在同一個(gè)事務(wù)里分別寫兩張表要么通過消息隊(duì)列異步同步否則容易出現(xiàn)主表有記錄但屬性表缺行的情況。混合方案還有一種變體把高頻使用的幾個(gè)動(dòng)態(tài)屬性冗余回主表。比如測(cè)試后發(fā)現(xiàn)用戶等級(jí)這個(gè)屬性是查詢頻率最高的篩選條件之一雖然它是動(dòng)態(tài)出現(xiàn)的但我把它提升為主表的一個(gè)正式列屬性表里仍然保留它作為審計(jì)記錄。這樣查詢走主表索引屬性表只負(fù)責(zé)存歷史。這個(gè)動(dòng)態(tài)屬性轉(zhuǎn)正的過程是橫豎混合方案長(zhǎng)期演進(jìn)中很常見的操作。4.3 混合方案的數(shù)據(jù)一致性與組裝策略混合方案最大的坑在于應(yīng)用層的組裝邏輯。橫表的數(shù)據(jù)查出來就是一行對(duì)象豎表的數(shù)據(jù)查出來是多行鍵值對(duì)要在應(yīng)用層把它們合并成完整對(duì)象這一步最容易寫出性能爛的代碼。我見過有人用循環(huán)逐條查屬性表——查出100個(gè)客戶再循環(huán)100次去查每個(gè)客戶的屬性直接搞出SQL的N1問題接口響應(yīng)時(shí)間從50ms飆升到3秒。正確做法是一次性查出所有相關(guān)實(shí)體的屬性按實(shí)體id分組在內(nèi)存里組裝SELECT customer_id, attr_name, attr_value_str, attr_value_num, attr_value_date FROM customer_attributes WHERE customer_id IN (1, 2, 3, ...);然后在應(yīng)用層用一個(gè)MapLong, MapString, Object接收結(jié)果。這樣查詢次數(shù)從N1降為2次數(shù)據(jù)量雖然翻倍但網(wǎng)絡(luò)和數(shù)據(jù)庫(kù)的往返次數(shù)大幅減少響應(yīng)時(shí)間完全可控。另外我強(qiáng)烈建議對(duì)組裝邏輯做統(tǒng)一的工具類封裝團(tuán)隊(duì)里所有人共用同一套實(shí)體屬性的讀取和組裝入口避免每個(gè)人各自寫一套導(dǎo)致緩存失效、鍵名不統(tǒng)一、類型轉(zhuǎn)換出錯(cuò)等問題。5. 橫表轉(zhuǎn)豎表、豎表轉(zhuǎn)橫表的遷移實(shí)戰(zhàn)5.1 什么時(shí)候需要遷移什么時(shí)候不該遷移現(xiàn)實(shí)中你很少有機(jī)會(huì)在項(xiàng)目一開始就選對(duì)形態(tài)更多情況是業(yè)務(wù)跑了一半才發(fā)現(xiàn)表結(jié)構(gòu)不對(duì)勁。我遇到過兩類典型訴求一類是橫表字段實(shí)在太多想拆成豎表減輕寬度壓力一類是豎表查詢實(shí)在痛苦想把高頻屬性轉(zhuǎn)成橫表列。在動(dòng)手遷移之前我建議你先回答三個(gè)問題現(xiàn)在的路徑能不能靠加索引、加緩存、加只讀從庫(kù)撐住遷移期間業(yè)務(wù)能否容忍停寫或降級(jí)遷移后性能收益是否可量化如果三個(gè)答案都是否那就不該遷移先改造查詢方式或冗余字段更劃算。如果至少兩個(gè)答案是肯定的遷移才有執(zhí)行的基礎(chǔ)。5.2 橫表轉(zhuǎn)豎表的兩種典型路徑橫表轉(zhuǎn)豎表的場(chǎng)景通常發(fā)生在老系統(tǒng)改造中。豎表的目標(biāo)一般很明確讓新業(yè)務(wù)屬性不用再改表結(jié)構(gòu)。執(zhí)行時(shí)有兩條路徑可選。路徑一是全量翻轉(zhuǎn)把橫表的每一行拆成多行豎表記錄字段名變成attr_name字段值變成attr_value。適合屬性數(shù)量少但行數(shù)巨大的表SQL寫起來直接回填快。路徑二是增量同步業(yè)務(wù)雙寫先在新豎表里寫入新增和變更的數(shù)據(jù)再把歷史數(shù)據(jù)分批掃描回填。適合不能停機(jī)的大表。我實(shí)際用過的是第二種一個(gè)千萬級(jí)的老用戶表每小時(shí)避開業(yè)務(wù)高峰期跑一批回填任務(wù)預(yù)計(jì)兩天跑完期間業(yè)務(wù)無感。回填過程中有個(gè)細(xì)節(jié)必須特別注意橫向表字段的取值類型五花八門有的是int、有的是datetime、有的是varchar落到豎表時(shí)必須明確類型映射規(guī)則。我前面提到過用三值列str/num/date來承接回填腳本里必須對(duì)每個(gè)源字段配置目標(biāo)類型不能用一個(gè)統(tǒng)一的toString糊弄過去否則后面查詢cast時(shí)一堆坑。5.3 豎表轉(zhuǎn)橫表高頻屬性轉(zhuǎn)正法豎表轉(zhuǎn)橫表則相反一般是豎表查詢性能已經(jīng)讓業(yè)務(wù)無法接受了或者在線分析場(chǎng)景的需求越來越重。最穩(wěn)妥的做法是高頻屬性轉(zhuǎn)正法先通過統(tǒng)計(jì)和業(yè)務(wù)溝通圈定查詢次數(shù)最多的5到10個(gè)屬性把它們作為真實(shí)列加入主表再寫一個(gè)回填任務(wù)從豎表里查出這些屬性的值填充到新列最后修改應(yīng)用代碼讓寫操作同時(shí)更新主表列和豎表行讀操作優(yōu)先走主表列豎表行作為歷史數(shù)據(jù)留檔。我做過一次這樣的遷移結(jié)果非常直觀原本一個(gè)需要join三層的動(dòng)態(tài)屬性篩選接口從1.8秒降到40毫秒左右業(yè)務(wù)方基本沒感知到切換過程。注意用不到的屬性不要轉(zhuǎn)否則你只是把之前的豎表靈活性換成了一種新的橫表膨脹反而兩頭不討好。5.4 遷移期間的兼容與回滾方案任何表結(jié)構(gòu)遷移都有風(fēng)險(xiǎn)我執(zhí)行遷移時(shí)的標(biāo)準(zhǔn)動(dòng)作是先新老并行新舊兩套同時(shí)寫入和讀取做數(shù)據(jù)對(duì)比再灰度切換先讓5%流量走新結(jié)構(gòu)觀察監(jiān)控和錯(cuò)誤率最后才全量切換并保留舊表數(shù)據(jù)至少30天以備隨時(shí)回滾。這里說的回滾不只是代碼回滾還包括數(shù)據(jù)回滾——如果新結(jié)構(gòu)在灰度期間暴露出數(shù)據(jù)不一致你要能快速把舊表數(shù)據(jù)翻回來。遷移中有一類問題最容易忽視應(yīng)用代碼里如果存在對(duì)舊字段或舊屬性名的硬編碼依賴切換后sql直接報(bào)錯(cuò)。所以遷移前要做一次全局搜索把所有涉及舊結(jié)構(gòu)的SQL和ORM映射梳理清楚最好用統(tǒng)一的DAO層收口避免散落各處的query直接訪問物理表結(jié)構(gòu)。6. 高級(jí)視角維度建模思維與未來擴(kuò)展6.1 橫豎之爭(zhēng)背后是實(shí)體與屬性的維度認(rèn)知把橫表和豎表放到更大的視野里看它們其實(shí)是兩種維度建模思維的體現(xiàn)。橫表把實(shí)體的每個(gè)屬性作為一個(gè)維度展開在列上豎表把屬性本身當(dāng)成一種數(shù)據(jù)維度值和實(shí)體之間的關(guān)系退化為一行記錄。這個(gè)區(qū)別在業(yè)務(wù)建模階段就應(yīng)該想清楚而不是等物理表建好了再糾結(jié)。我常用的建模思路是先做概念模型實(shí)體是誰屬性有哪些哪些屬性是這個(gè)實(shí)體之所以成為它的本質(zhì)屬性哪些屬性是附屬在不同場(chǎng)景下才存在的擴(kuò)展屬性。本質(zhì)屬性進(jìn)橫表擴(kuò)展屬性進(jìn)豎表或JSON列。這一步想清楚了后面物理設(shè)計(jì)基本就是水到渠成的事。6.2 JSON列作為橫豎表之外的第三條路現(xiàn)在主流數(shù)據(jù)庫(kù)都支持JSON類型MySQL從5.7開始、PostgreSQL更是把JSONB玩出了花。JSON列可以理解成一種折中的豎表它把動(dòng)態(tài)屬性打包成一整個(gè)文檔存在一個(gè)列里免去了豎表的join保留了靈活擴(kuò)展的能力。代價(jià)是查詢能力受限——MySQL的JSON函數(shù)雖然能查、能索引部分字段但復(fù)雜查詢性能和可維護(hù)性都不如真正的列。我現(xiàn)在的習(xí)慣是動(dòng)態(tài)屬性數(shù)量少比如5到10個(gè)內(nèi)、查詢模式簡(jiǎn)單優(yōu)先用JSON列動(dòng)態(tài)屬性數(shù)量多、且需要頻繁作為篩選和聚合條件還是老實(shí)上豎表或干脆用文檔數(shù)據(jù)庫(kù)。JSON列適合做不需要深度查詢的擴(kuò)展信息不適合做核心業(yè)務(wù)篩選字段。這一點(diǎn)在實(shí)際項(xiàng)目中反復(fù)驗(yàn)證過希望你不要等踩了坑再回頭。6.3 從OLTP到OLAP橫豎表選擇在分析場(chǎng)景中的延伸還有一個(gè)延伸維度值得提一下同樣橫豎之爭(zhēng)在OLAP分析場(chǎng)景里結(jié)論可能會(huì)反轉(zhuǎn)。分析場(chǎng)景下數(shù)據(jù)量巨大、列數(shù)眾多列式存儲(chǔ)引擎比如ClickHouse、Parquet文件天然更擅長(zhǎng)按列掃描豎表模型反而會(huì)極大放大存儲(chǔ)和掃描開銷。所以在數(shù)倉(cāng)和數(shù)據(jù)分析領(lǐng)域橫表寬表幾乎是絕對(duì)的主流豎表幾乎只在鍵值對(duì)日志和指標(biāo)采集場(chǎng)景里出現(xiàn)。你在做OLTP業(yè)務(wù)設(shè)計(jì)時(shí)用豎表可能很靈活但一旦要把數(shù)據(jù)同步到數(shù)倉(cāng)做分析通常需要把豎表再透視成橫表或者用ETL時(shí)的聚合邏輯把屬性展開為列。這個(gè)規(guī)律我自己體會(huì)很深同一個(gè)業(yè)務(wù)OLTP側(cè)重靈活性時(shí)選豎表OLAP側(cè)重掃描效率時(shí)選橫表寬表兩者中間靠ETL銜接。如果你在業(yè)務(wù)系統(tǒng)里用了豎表提前設(shè)計(jì)好豎轉(zhuǎn)橫的導(dǎo)出視圖會(huì)給后續(xù)的數(shù)據(jù)分析省下大量時(shí)間。7. 橫表與豎表的選型清單和避坑心得7.1 一張選型自查清單拿去就能用寫了這么多最后整理一份可以直接拿去用的自查清單核心字段是否穩(wěn)定且可以窮舉是→橫表。業(yè)務(wù)是否需要頻繁新增屬性且不希望改表結(jié)構(gòu)是→豎表。屬性值是否存在大量NULL是→優(yōu)先考慮豎表或JSON列。查詢是否經(jīng)常按幾個(gè)固定字段篩選排序是→橫表加索引最香。查詢是否需要根據(jù)任意屬性組合反查實(shí)體是→豎表要慎重建議考慮搜索引擎或轉(zhuǎn)正高頻屬性。是否允許應(yīng)用層做屬性組裝是→混合方案可行否→老老實(shí)實(shí)橫表。7.2 我在實(shí)戰(zhàn)中反復(fù)踩過的五個(gè)坑第一個(gè)坑豎表字段值類型不統(tǒng)一。這個(gè)問題最隱蔽初期數(shù)據(jù)量小看不出問題數(shù)據(jù)量大了之后排序、范圍篩選全部翻車。解決方案就是三值列和類型映射千萬別怕麻煩。第二個(gè)坑橫表加字段太隨意?？傁胫燃觽€(gè)字段試試結(jié)果字段越來越多表越來越寬等到想收手時(shí)已經(jīng)動(dòng)不了了。橫表加字段前必須走審批和評(píng)估至少要問一句這個(gè)字段能窮舉嗎能不能放到JSON列或擴(kuò)展表里。第三個(gè)坑混合方案缺少一致性保障。主表和屬性表之間如果不在同一個(gè)事務(wù)里寫入并發(fā)場(chǎng)景下很容易出現(xiàn)主表已有記錄但屬性表遲了幾百毫秒才寫入的情況一旦中間讀操作發(fā)生就會(huì)拿到不完整的數(shù)據(jù)。事務(wù)保底雙寫必須同步。第四個(gè)坑遷移回填不考慮存量數(shù)據(jù)的類型和格式。源系統(tǒng)里同一個(gè)字段在不同時(shí)期格式可能不一樣有2024-01-01也有2024/01/01回填時(shí)統(tǒng)一格式是逃不掉的一步否則分析時(shí)還是要返工。第五個(gè)坑為了靈活過度設(shè)計(jì)。有些團(tuán)隊(duì)一聽豎表靈活什么屬性都往豎表里塞結(jié)果最核心的用戶名列都在豎表里查詢?nèi)孔兂删酆喜僮鞯貌粌斒?。豎表是給動(dòng)態(tài)屬性準(zhǔn)備的不是給核心屬性準(zhǔn)備的。7.3 回顧維度思維才是核心能力回首這些年的數(shù)據(jù)庫(kù)設(shè)計(jì)經(jīng)歷我覺得橫表與豎表之爭(zhēng)真正比拼的不是孰優(yōu)孰劣而是設(shè)計(jì)者對(duì)業(yè)務(wù)的理解深度。你越早想清楚業(yè)務(wù)的屬性是穩(wěn)定的還是動(dòng)態(tài)的、查詢是固定的還是即席的、擴(kuò)展是低頻還是高頻就越能在第一時(shí)間做出對(duì)的選擇。橫表不丟人豎表也不丟人丟人的是不分析業(yè)務(wù)就盲目開搞搞了兩三個(gè)月才發(fā)現(xiàn)方向錯(cuò)了返工。我自己現(xiàn)在接任何新項(xiàng)目動(dòng)手建表之前都會(huì)先花半天時(shí)間做一次屬性分類和查詢模式梳理哪怕只是在一張白紙上寫寫畫畫。這一步看著不起眼但可以省掉后面數(shù)不清的加班和改表。希望這篇文章里的經(jīng)驗(yàn)和踩坑記錄能幫你少走幾步彎路。