關(guān)于論壇數(shù)據(jù)庫的設(shè)計
文章分類:數(shù)據(jù)庫
一個簡單的論壇系統(tǒng)
1：包含下列信息：
2：每天論壇訪問量300萬左右，更新帖子10萬左右。
請給出數(shù)據(jù)庫表結(jié)構(gòu)設(shè)計，并結(jié)合范式簡要說明設(shè)計思路。
一. 發(fā)帖主題和回復(fù)信息存放在一張表，并在這個表中增加user_name字段
對數(shù)據(jù)庫的操作而言，檢索數(shù)據(jù)的性能基本不會對數(shù)據(jù)造成很大的影響（精確查找的情況下），而對表與表之間的連接卻會產(chǎn)生巨大的影響， 特別在有巨量數(shù)據(jù)的表之間；因此對問題的定位基本可以確定：在顯示和檢索數(shù)據(jù)時，盡量減少數(shù)據(jù)庫的連接以及表與表之間的連接；
引用
1: user:用戶基本信息表
字段有：user_id,user_name,email,homepage,tel,add…
2: forum_item:主題和回復(fù)混合表
字段有：id,parent_id,user_id,user_name,title,content,….
parent_id=0或者null表示是主題，否則=n表示是id=n那條帖子的回復(fù)
UserName字段是冗余的，因此在用戶修改UserName的時候就會產(chǎn)生同步數(shù)據(jù)的問題，這個需要程序來進(jìn)行彌補(bǔ)
二. 主題表和主題回復(fù)分開保存
引用
1: user:用戶基本信息表
字段有：user_id,user_name,email,homepage,tel,add…
2: forum_topic:主題表
字段有：id,user_id,title,content,….
3: forum_topic_back:主題回復(fù)表
字段有：id,topic_id,user_id,title,content,….
三. 主題表的內(nèi)容單獨(dú)設(shè)計成一個表
引用
1: user:用戶基本信息表
字段有：user_id,user_name,email,homepage,tel,add…
2: forum_topic:主題表
字段有：id,user_id,title,….
3: forum_topic_content:主題內(nèi)容表
字段有：id,topic_id,content
4: forum_topic_back:主題回復(fù)表
字段有：id,topic_id,user_id,title,content,….
四.用戶信息分2個表保存，并對相關(guān)表進(jìn)行分表處理
引用
1: 簡單用戶表 tb_user:
id , username
2: 用戶詳細(xì)信息表 tb_userinfo
id,userid , email , homepage , phone , address …
3: 論壇主題表 tb_bbs
id , userid , title , ip , repleycount , replyuserid , createtime , lastreplytime
4: 論壇內(nèi)容標(biāo) tb_bbs_content (此表可按照bbsid進(jìn)行分表存儲)
id,bbsid , content;
5: 論壇回復(fù)表 tb_bbs_reply (此表可按照bbsid進(jìn)行分表存儲)
id , bbsid , userid , content , replytime , ip
五.增加一個主題緩存表，取每個區(qū)的前面100條記錄
引用
1: 簡單用戶表 tb_user:
字段有：id , username
2: 用戶詳細(xì)信息表 tb_userinfo
字段有：id,userid , email , homepage , phone , address …
3: 論壇主題表 tb_bbs
字段有：id , userid , title , ip , repleycount , replyuserid , createtime , lastreplytime
4: 論壇內(nèi)容標(biāo) tb_bbs_content (此表可按照bbsid進(jìn)行分表存儲)
字段有：id,bbsid , content;
5: 論壇回復(fù)表 tb_bbs_reply (此表可按照bbsid進(jìn)行分表存儲)
字段有：id , bbsid , userid , content , replytime , ip
6: 主題緩存表 tb_bbs_cache
字段有：id , userid , title , ip , repleycount , replyuserid , createtime , lastreplytime
——————————————————————————
下面是針對上面的方案展開的討論：
1：方案一表面上看起來好像少查了一張表，但由于冗余，因為帖子數(shù)量極大，會占用大量的空間。這種數(shù)據(jù)量大，但是對實時和數(shù)據(jù)絕對安全性要求較低的應(yīng)用，大量使用緩存的話可以極大提高處理能力。
2：方案一你這么設(shè)計的話，索引怎么建比較好呢，還有就是會不會造成這個表過熱，還有…… 我覺得像論壇這樣的系統(tǒng)，使用緩存可以大大降低數(shù)據(jù)庫的負(fù)載
3：大家的意思是分成主題表、回復(fù)表等多個表？ 還是合成一個表然后做物理分區(qū)？ 哪種更好呢？
4：再這么高插入更新的頻率下 索引就有些不實用了，創(chuàng)建索引會降低插入更新的速度而且訪問量這么大的情況下，索引不建議采用
5：就這樣的一個論壇，實時在更新、發(fā)帖、回帖。我覺得在數(shù)據(jù)庫上建立索引不太好，但是如果不建立索引如何來提高查詢等方面性能呢？
6：都是分布式數(shù)據(jù)庫了。放在多個表中，直接關(guān)聯(lián)一點(diǎn)都沒問題。重要是橫向切分
7：認(rèn)同分表，分庫，緩存的做法
引用
問題分析:
每天論壇訪問量300萬左右，更新帖子10萬左右。
1. 讀寫比例在30:1左右, 應(yīng)向讀取效率方面傾斜. 索引建立需參考常用讀取的主關(guān)鍵字.
2. 每月數(shù)據(jù)在10W*30=300W. 可按月分表
3. 每年帖子在300W*12=3600W, 推算數(shù)據(jù)不會小于30T. 可按年分庫
結(jié)構(gòu):
用戶信息：獨(dú)立表，userid主鍵
發(fā)帖、回帖：按月表存儲，帖子唯一ID主鍵，日期索引。
帖子內(nèi)容明細(xì)：按月存儲，帖子唯一ID主鍵
8：拿一張500萬的表來說事
引用
更新的時候如果沒有索引的話
更新時間大概需要30秒左右 指的是全表更新~~
而查詢某單行記錄 卻需要10秒左右~~
而加入索引的話
更新時間差不多慢了一倍有余
而查詢記錄則縮減到毫秒級~~
快了百倍有余~~
孰重孰輕 自己選
9：自己的一點(diǎn)經(jīng)驗:
引用
1.分表存儲;
2.建立索引;SQL按所以查詢的速度還是很快的;
3.避免整表掃描;先讀取主題,在按照主題ID讀取回復(fù);再按照用戶ID讀取用戶;而不要使用關(guān)聯(lián);
4.使用緩存;
10：需要分3張表，且建立索引。。。
理由如下：
引用
1：建立3張表可以避免冗余數(shù)據(jù)，維護(hù)起來方便。。。
2：每天論壇訪問量300萬左右，可見主要的壓力來自于查詢，sql查詢的效率在于避免全表掃描，可見建立索引是必須的。。。
3：關(guān)于創(chuàng)建索引會降低插入更新的速度這個問題是不存在的。。。 因為，索引之所以會降低更新的速度的速度，是因為在更新完對應(yīng)字段后還需要更新對應(yīng)字段的索引。
4：看到更新帖子10萬左右，這句話是說，我們可能對發(fā)帖標(biāo)題，發(fā)帖內(nèi)容，回復(fù)標(biāo)題，回復(fù)內(nèi)容這4個字段做更新。。。需要注意的是，這四個字段并不是用來建立表連接的字段，為了優(yōu)化查詢速度我們不會在這四個字段上建立索引，所以從這道題目出發(fā)，我們建立的索引不會影響更新帖子的性能。。。
所以，我認(rèn)為最后的答案是建立3張表，在連接用到的字段上建立索引。。。
11：
引用
兩個表然後建一個視圖是否可行呢？
視圖也是很慢的。
12：每天就更新10萬個帖子，每天訪問那么多，肯定是不能把所有的主貼放在一個表里，大表分小表，建立常用字段的索引，然后配置緩存。級聯(lián)關(guān)系最好不要配置，等需要的時候再查詢。
13：雖然題目中沒有說明，但實際應(yīng)用中，查閱帖子通常只會分頁顯示，而一頁最多也就顯示幾十個帖子，那么實際上只要SQL語句構(gòu)造得好，T_USER表其實只是跟一個只有幾十行結(jié)果集的的子查詢進(jìn)行連接，應(yīng)該基本不用擔(dān)心出現(xiàn)性能問題。
而且實際上，一個萬行級的表簡單關(guān)聯(lián)百萬行級的表（其實鎮(zhèn)魂歌數(shù)量級在我看來其實也算不上很大的表），在數(shù)據(jù)庫方面完全有很多優(yōu)化方式，甚至可以通過提高硬件配置來改善性能，實在沒有很大必要進(jìn)行結(jié)構(gòu)上的冗余。一旦結(jié)構(gòu)有冗余，為了保證數(shù)據(jù)一致性，往往你還要消耗更多的資源，反而得不償失。
14：分表有垂直和水平分表
引用
1：無論你拿多少記錄(甚至是1條),如果兩個大表關(guān)聯(lián)都可能會產(chǎn)生非常大的中間值,如果你排序(排序字段沒有用到索引),你都可能導(dǎo)致數(shù)據(jù)庫采用各種各樣的方式來計算。
2：索引會導(dǎo)致插入、更新記錄很慢,大家都是知道的。
3：水平分表可以解決這個問題,只要你能保證每個表只存適合的記錄數(shù)(例如100W一個表) (水平分區(qū)也可以解決IO的一些問題)
4：還有就讀寫分離,master是寫,slave是讀 (再加上cache,一般問題都還好了)
上面都是比較大的工作量,最好是保證你的數(shù)據(jù)庫設(shè)計是合理的(范式是第一步,然后考慮反范式),基本上也能滿足很多問題了。
15：方案四 把內(nèi)容與其它信息分開的好處就是可以讓每個表的文件最小化,對數(shù)據(jù)庫操作壓力會減小,操作速度會快,還可以搭配緩存,把內(nèi)容根據(jù)情況進(jìn)行緩存,可以盡量很少訪問表數(shù)據(jù)。
引用
1：對于上述分表方式也可以適用于分庫操作,這樣就降低了數(shù)據(jù)庫單庫的壓力,把壓力分散到各個機(jī)器
2：我的做法就是盡量避免表關(guān)聯(lián)
3：再就是對于sql語句盡量都保證索引有效,不能索引的sql,盡量采用能索引的高效方式解決
16：外圍的方案：
引用
1 讀方面，生成靜態(tài)頁，或者緩存最新最熱的帖子。
2 寫方面，估計主要是INSERT吧，這個可以異步操作的。所有的寫貼操作放到一個隊列然后批量執(zhí)行插入數(shù)據(jù)庫操作。
17：方案四比較靠譜，再加上定期轉(zhuǎn)儲，海量的cache，大型論壇就此搞定。
18：我覺得應(yīng)該還是使用3張表比較合適。
引用
1：業(yè)務(wù)上說,很可能主貼跟回復(fù)貼擁有不同的擴(kuò)展,比如附件什么的,都放在一張表里面,假如主貼跟回復(fù)存在個性需求,怎么辦？無限加字段么？
2：主貼跟回復(fù)在同一張表里,會增大鎖表的幾率。
3：索引的確會降低表更新的速度,但是帶來的查詢效率提升也是很可觀的,因此我覺得,索引不能不用,但是要少用。
4：建立表時,確實可以通過樓上某位仁兄回復(fù)所言,用水平分表的方式,其實原理就是用先算再查嘛。
5：在前端表現(xiàn)上,可以使用ajax等方式,分步驟取數(shù)據(jù),比如主貼的內(nèi)容先取出來,然后再逐步加載回復(fù)信息等。
19：提高速度的關(guān)鍵:
引用
1.建立索引并在查詢時充分利用;
2.避免使用關(guān)聯(lián),這樣避免整表掃描;使用關(guān)聯(lián)不如多次使用主鍵查詢來的快;
3.一些處理的功能盡可能放到內(nèi)存中來做,比如組織主題和回復(fù);
4.使用靜態(tài)頁面也是個不錯的做法;
20：方案三是延續(xù)了hibernate二級緩存的思想， 對于經(jīng)常更新的數(shù)據(jù)都設(shè)計成單獨(dú)表，這樣可以最大程度的利用hibernate緩存
21：沒有fast=true的設(shè)置,有人說or比in 好,exists比in 好,索引比全表掃描好,分區(qū)能提高查詢效率,但是分區(qū)要降低插入效率
我要說的是,沒有fast=true的選項, 如果能找到一步,或者幾步公式化的方法能提高效率,那么優(yōu)化器自己就會做了,根本不用用戶擔(dān)心。
假設(shè) or比in好,數(shù)據(jù)庫優(yōu)化器把in語法和or語法走的執(zhí)行計劃一樣就可以了,何必折磨用戶呢。
說點(diǎn)實際的,很多人張嘴就說,SQL優(yōu)化就是避免全表掃描,不知道大家有沒有了解過索引查找的原理.索引查找數(shù)據(jù),有兩步要做,第一步是索引中快速查詢,索引里只存儲了對應(yīng)表數(shù)據(jù)的rowid, 所以還有第二步,根據(jù)rowid去得到全部的數(shù)據(jù), 所以需要一次磁盤i/o, 不要小看磁盤I/O,通過索引查詢出的結(jié)果比較多的時候,磁盤i/o的時間是非常大的,這個時候比全表掃描慢得多, 實際上,oracle 10g基于成本的優(yōu)化器(CBO),選擇性不高的索引,優(yōu)化器根本不會使用,而自動采用全表掃描的方式來做.
22：這個量級的bbs我設(shè)計過,當(dāng)時是這樣做的(方案五)：
引用
共四個表：
1. 用戶表
2. 主題表(包含最后回復(fù)信息,最后回復(fù)人,最后回復(fù)id等)
3. 回復(fù)表
4. 主題緩存表(這個取每個區(qū)的前面100條記錄),一般來說負(fù)載最大的就是主題的第一頁,所以緩存表是個小表。
共3臺app集群,1臺web,2臺oracle一主一備,運(yùn)行下來速度還是可接受的。
23：不建議進(jìn)行表的設(shè)計冗余，感覺就想重復(fù)代碼一樣，有壞味道
引用
1：緩存常用的頁面和數(shù)據(jù)
2：讀寫表或庫分開(基于垂直分隔)
3：數(shù)據(jù)庫可以進(jìn)行垂直分隔(字段分到多個表中)，再進(jìn)行水平分隔（數(shù)據(jù)分到多個表中）
4：論壇功能可以進(jìn)行分隔，不同的服務(wù)器負(fù)責(zé)不同的功能,如圖片服務(wù)器，web服務(wù)器,郵件服務(wù)器等
總之，就是要細(xì)化分工
24：支持方案三的設(shè)計
讀取的操作：
引用
1：顯示帖子列表界面,如果主貼內(nèi)容放在forum_topic表,那么這就是冗余的,假設(shè)都要獲取100個帖子,一行的數(shù)據(jù)長度越大,數(shù)據(jù)庫需要掃描的數(shù)據(jù)塊就越多,性能也越差。
2：在打開一個帖子時,讀操作通過索引關(guān)聯(lián)到兩張表（forum_topic和forum_topic_content）性能消耗對整個數(shù)據(jù)庫來說不多。
寫帖子的操作：
引用
發(fā)表帖子,對標(biāo)題表和內(nèi)容表分別作一個插入
更新非索引列不會引起索引更新:
引用
只要被索引的列（例如回復(fù)表的標(biāo)題ID）不被頻繁更新,即使索引所在地行的其它列被頻繁update,索引也不會被更新從而產(chǎn)生性能消耗,一張表一天30萬次的索引更新,因它引起的性能消耗小到即使數(shù)據(jù)庫安裝在奔騰3單核CPU下都能輕松承擔(dān)下來, 為什么會有人對索引有這么大的誤解呢？。對一個論壇(或者絕大部分的系統(tǒng))來說,檢索(SELECT)數(shù)據(jù)耗費(fèi)的系統(tǒng)資源遠(yuǎn)遠(yuǎn)高于更新數(shù)據(jù)(INSERT/UPDATE)本身,而索引是專門為檢索數(shù)據(jù)服務(wù)的,難道就為了節(jié)省更新數(shù)據(jù)的小小的性能消耗,付出檢索100條數(shù)據(jù)時需要數(shù)據(jù)庫掃描幾千萬上億條數(shù)據(jù)進(jìn)行數(shù)據(jù)匹配的代價？如果是這樣的話,即使是有32核頂級CPU的數(shù)據(jù)庫作并行查詢都未必頂?shù)米 ?/p>
做數(shù)據(jù)庫設(shè)計,還是多了解數(shù)據(jù)庫的原理才好。
25：數(shù)據(jù)庫切分是必須的。
引用
1：垂直切分：用戶表、用戶信息表、主題表、主題內(nèi)容表、回復(fù)表
2：水平切分：主題1、主題2、主題3、…、主題n
3：緩存：緩存路由表
4：再配合數(shù)據(jù)庫讀寫分離和集群吧
另：其實論壇修改標(biāo)題、內(nèi)容的概率是很小的。大部分都是新增
聯(lián)系客服
