关于论坛数据库的设计

柔光的暖阳◎ 2022-07-16 05:12 212阅读 0赞

一个简单的论坛系统   
  
1：包含下列信息：   
![3e121eed-8e65-3172-aae2-820559e03d77.png][]    
2：每天论坛访问量300万左右，更新帖子10万左右。   
  
请给出数据库表结构设计，并结合范式简要说明设计思路。   
  
一. 发帖主题和回复信息存放在一张表，并在这个表中增加user\_name字段    
对数据库的操作而言，检索数据的性能基本不会对数据造成很大的影响（精确查找的情况下），而对表与表之间的连接却会产生巨大的影响， 特别在有巨量数据的表之间；因此对问题的定位基本可以确定：在显示和检索数据时，尽量减少数据库的连接以及表与表之间的连接；

引用

1: user:用户基本信息表   
字段有：user\_id,user\_name,email,homepage,tel,add...   
  
2: forum\_item:主题和回复混合表   
字段有：id,parent\_id,user\_id,user\_name,title,content,....

parent\_id=0或者null表示是主题，否则=n表示是id=n那条帖子的回复   
UserName字段是冗余的，因此在用户修改UserName的时候就会产生同步数据的问题，这个需要程序来进行弥补   
  
二. 主题表和主题回复分开保存

引用

1: user:用户基本信息表   
字段有：user\_id,user\_name,email,homepage,tel,add...   
  
2: forum\_topic:主题表   
字段有：id,user\_id,title,content,....   
  
3: forum\_topic\_back:主题回复表   
字段有：id,topic\_id,user\_id,title,content,....

三. 主题表的内容单独设计成一个表

引用

1: user:用户基本信息表   
字段有：user\_id,user\_name,email,homepage,tel,add...   
  
2: forum\_topic:主题表   
字段有：id,user\_id,title,....   
  
3: forum\_topic\_content:主题内容表   
字段有：id,topic\_id,content   
  
4: forum\_topic\_back:主题回复表   
字段有：id,topic\_id,user\_id,title,content,....

四.用户信息分2个表保存，并对相关表进行分表处理

引用

1: 简单用户表 tb\_user:   
id , username   
  
2: 用户详细信息表 tb\_userinfo   
id,userid , email , homepage , phone , address ...   
  
3: 论坛主题表 tb\_bbs   
id , userid , title , ip , repleycount , replyuserid , createtime , lastreplytime   
  
4: 论坛内容标 tb\_bbs\_content (此表可按照bbsid进行分表存储)   
id,bbsid , content;   
  
5: 论坛回复表 tb\_bbs\_reply (此表可按照bbsid进行分表存储)   
id , bbsid , userid , content , replytime , ip

五.增加一个主题缓存表，取每个区的前面100条记录

引用

1: 简单用户表 tb\_user:   
字段有：id , username   
  
2: 用户详细信息表 tb\_userinfo   
字段有：id,userid , email , homepage , phone , address ...   
  
3: 论坛主题表 tb\_bbs   
字段有：id , userid , title , ip , repleycount , replyuserid , createtime , lastreplytime   
  
4: 论坛内容标 tb\_bbs\_content (此表可按照bbsid进行分表存储)   
字段有：id,bbsid , content;   
  
5: 论坛回复表 tb\_bbs\_reply (此表可按照bbsid进行分表存储)   
字段有：id , bbsid , userid , content , replytime , ip   
  
6: 主题缓存表 tb\_bbs\_cache   
字段有：id , userid , title , ip , repleycount , replyuserid , createtime , lastreplytime

\------------------------------------------------------------------------------   
下面是针对上面的方案展开的讨论：   
  
1：方案一表面上看起来好像少查了一张表，但由于冗余，因为帖子数量极大，会占用大量的空间。这种数据量大，但是对实时和数据绝对安全性要求较低的应用，大量使用缓存的话可以极大提高处理能力。   
  
2：方案一你这么设计的话，索引怎么建比较好呢，还有就是会不会造成这个表过热，还有…… 我觉得像论坛这样的系统，使用缓存可以大大降低数据库的负载   
  
3：大家的意思是分成主题表、回复表等多个表？ 还是合成一个表然后做物理分区？ 哪种更好呢？   
  
4：再这么高插入更新的频率下 索引就有些不实用了，创建索引会降低插入更新的速度而且访问量这么大的情况下，索引不建议采用   
  
5：就这样的一个论坛，实时在更新、发帖、回帖。我觉得在数据库上建立索引不太好，但是如果不建立索引如何来提高查询等方面性能呢？   
  
6：都是分布式数据库了。放在多个表中，直接关联一点都没问题。重要是横向切分   
  
7：认同分表，分库，缓存的做法

引用

问题分析:   
  每天论坛访问量300万左右，更新帖子10万左右。   
  1. 读写比例在30:1左右, 应向读取效率方面倾斜. 索引建立需参考常用读取的主关键字.   
  2. 每月数据在10W\*30=300W. 可按月分表   
  3. 每年帖子在300W\*12=3600W, 推算数据不会小于30T. 可按年分库   
  
结构:   
  用户信息：独立表，userid主键   
  发帖、回帖：按月表存储，帖子唯一ID主键，日期索引。   
  帖子内容明细：按月存储，帖子唯一ID主键

8：拿一张500万的表来说事

引用

更新的时候如果没有索引的话    
更新时间大概需要30秒左右 指的是全表更新~~   
而查询某单行记录 却需要10秒左右~~   
  
而加入索引的话   
更新时间差不多慢了一倍有余   
而查询记录则缩减到毫秒级~~   
快了百倍有余~~

孰重孰轻 自己选   
  
9：自己的一点经验:

引用

1.分表存储;   
2.建立索引;SQL按所以查询的速度还是很快的;   
3.避免整表扫描;先读取主题,在按照主题ID读取回复;再按照用户ID读取用户;而不要使用关联;   
4.使用缓存;

10：需要分3张表，且建立索引。。。   
理由如下：

引用

1：建立3张表可以避免冗余数据，维护起来方便。。。   
2：每天论坛访问量300万左右，可见主要的压力来自于查询，sql查询的效率在于避免全表扫描，可见建立索引是必须的。。。   
3：关于创建索引会降低插入更新的速度这个问题是不存在的。。。 因为，索引之所以会降低更新的速度的速度，是因为在更新完对应字段后还需要更新对应字段的索引。   
4：看到更新帖子10万左右，这句话是说，我们可能对发帖标题，发帖内容，回复标题，回复内容这4个字段做更新。。。需要注意的是，这四个字段并不是用来建立表连接的字段，为了优化查询速度我们不会在这四个字段上建立索引，所以从这道题目出发，我们建立的索引不会影响更新帖子的性能。。。

所以，我认为最后的答案是建立3张表，在连接用到的字段上建立索引。。。   
  
11：

引用

兩個表然後建一個視圖是否可行呢？

视图也是很慢的。   
  
12：每天就更新10万个帖子，每天访问那么多，肯定是不能把所有的主贴放在一个表里，大表分小表，建立常用字段的索引，然后配置缓存。级联关系最好不要配置，等需要的时候再查询。   
  
13：虽然题目中没有说明，但实际应用中，查阅帖子通常只会分页显示，而一页最多也就显示几十个帖子，那么实际上只要SQL语句构造得好，T\_USER表其实只是跟一个只有几十行结果集的的子查询进行连接，应该基本不用担心出现性能问题。   
  
而且实际上，一个万行级的表简单关联百万行级的表（其实镇魂歌数量级在我看来其实也算不上很大的表），在数据库方面完全有很多优化方式，甚至可以通过提高硬件配置来改善性能，实在没有很大必要进行结构上的冗余。一旦结构有冗余，为了保证数据一致性，往往你还要消耗更多的资源，反而得不偿失。   
  
14：分表有垂直和水平分表

引用

1：无论你拿多少记录(甚至是1条),如果两个大表关联都可能会产生非常大的中间值,如果你排序(排序字段没有用到索引),你都可能导致数据库采用各种各样的方式来计算。   
  
2：索引会导致插入、更新记录很慢,大家都是知道的。   
  
3：水平分表可以解决这个问题,只要你能保证每个表只存适合的记录数(例如100W一个表) (水平分区也可以解决IO的一些问题)   
  
4：还有就读写分离,master是写,slave是读 (再加上cache,一般问题都还好了)

上面都是比较大的工作量,最好是保证你的数据库设计是合理的(范式是第一步,然后考虑反范式),基本上也能满足很多问题了。   
  
15：方案四 把内容与其它信息分开的好处就是可以让每个表的文件最小化,对数据库操作压力会减小,操作速度会快,还可以搭配缓存,把内容根据情况进行缓存,可以尽量很少访问表数据。

引用

1：对于上述分表方式也可以适用于分库操作,这样就降低了数据库单库的压力,把压力分散到各个机器   
2：我的做法就是尽量避免表关联   
3：再就是对于sql语句尽量都保证索引有效,不能索引的sql,尽量采用能索引的高效方式解决

16：外围的方案：

引用

1 读方面，生成静态页，或者缓存最新最热的帖子。   
2 写方面，估计主要是INSERT吧，这个可以异步操作的。所有的写贴操作放到一个队列然后批量执行插入数据库操作。

17：方案四比较靠谱，再加上定期转储，海量的cache，大型论坛就此搞定。   
  
18：我觉得应该还是使用3张表比较合适。

引用

1：业务上说,很可能主贴跟回复贴拥有不同的扩展,比如附件什么的,都放在一张表里面,假如主贴跟回复存在个性需求,怎么办？无限加字段么？   
2：主贴跟回复在同一张表里,会增大锁表的几率。   
3：索引的确会降低表更新的速度,但是带来的查询效率提升也是很可观的,因此我觉得,索引不能不用,但是要少用。   
4：建立表时,确实可以通过楼上某位仁兄回复所言,用水平分表的方式,其实原理就是用先算再查嘛。   
5：在前端表现上,可以使用ajax等方式,分步骤取数据,比如主贴的内容先取出来,然后再逐步加载回复信息等。

19：提高速度的关键:

引用

1.建立索引并在查询时充分利用;   
2.避免使用关联,这样避免整表扫描;使用关联不如多次使用主键查询来的快;   
3.一些处理的功能尽可能放到内存中来做,比如组织主题和回复;   
4.使用静态页面也是个不错的做法;

20：方案三是延续了hibernate二级缓存的思想， 对于经常更新的数据都设计成单独表，这样可以最大程度的利用hibernate缓存   
  
21：没有fast=true的设置,有人说or比in 好,exists比in 好,索引比全表扫描好,分区能提高查询效率,但是分区要降低插入效率   
我要说的是,没有fast=true的选项, 如果能找到一步,或者几步公式化的方法能提高效率,那么优化器自己就会做了,根本不用用户担心。   
假设 or比in好,数据库优化器把in语法和or语法走的执行计划一样就可以了,何必折磨用户呢。   
说点实际的,很多人张嘴就说,SQL优化就是避免全表扫描,不知道大家有没有了解过索引查找的原理.索引查找数据,有两步要做,第一步是索引中快速查询,索引里只存储了对应表数据的rowid, 所以还有第二步,根据rowid去得到全部的数据, 所以需要一次磁盘i/o, 不要小看磁盘I/O,通过索引查询出的结果比较多的时候,磁盘i/o的时间是非常大的,这个时候比全表扫描慢得多, 实际上,oracle 10g基于成本的优化器(CBO),选择性不高的索引,优化器根本不会使用,而自动采用全表扫描的方式来做.   
  
22：这个量级的bbs我设计过,当时是这样做的(方案五)：

引用

共四个表：   
1. 用户表   
2. 主题表(包含最后回复信息,最后回复人,最后回复id等)   
3. 回复表   
4. 主题缓存表(这个取每个区的前面100条记录),一般来说负载最大的就是主题的第一页,所以缓存表是个小表。

共3台app集群,1台web,2台oracle一主一备,运行下来速度还是可接受的。   
  
23：不建议进行表的设计冗余，感觉就想重复代码一样，有坏味道

引用

1：缓存常用的页面和数据   
2：读写表或库分开(基于垂直分隔)   
3：数据库可以进行垂直分隔(字段分到多个表中)，再进行水平分隔（数据分到多个表中）   
4：论坛功能可以进行分隔，不同的服务器负责不同的功能,如图片服务器，web服务器,邮件服务器等

总之，就是要细化分工   
  
24：支持方案三的设计   
读取的操作：

引用

1：显示帖子列表界面,如果主贴内容放在forum\_topic表,那么这就是冗余的,假设都要获取100个帖子,一行的数据长度越大,数据库需要扫描的数据块就越多,性能也越差。   
2：在打开一个帖子时,读操作通过索引关联到两张表（forum\_topic和forum\_topic\_content）性能消耗对整个数据库来说不多。

写帖子的操作：

引用

发表帖子,对标题表和内容表分别作一个插入

更新非索引列不会引起索引更新:

引用

只要被索引的列（例如回复表的标题ID）不被频繁更新,即使索引所在地行的其它列被频繁update,索引也不会被更新从而产生性能消耗,一张表一天30万次的索引更新,因它引起的性能消耗小到即使数据库安装在奔腾3单核CPU下都能轻松承担下来, 为什么会有人对索引有这么大的误解呢？。对一个论坛(或者绝大部分的系统)来说,检索(SELECT)数据耗费的系统资源远远高于更新数据(INSERT/UPDATE)本身,而索引是专门为检索数据服务的,难道就为了节省更新数据的小小的性能消耗,付出检索100条数据时需要数据库扫描几千万上亿条数据进行数据匹配的代价？如果是这样的话,即使是有32核顶级CPU的数据库作并行查询都未必顶得住。

做数据库设计,还是多了解数据库的原理才好。   
  
25：数据库切分是必须的。

引用

1：垂直切分：用户表、用户信息表、主题表、主题内容表、回复表   
2：水平切分：主题1、主题2、主题3、...、主题n   
3：缓存：缓存路由表   
4：再配合数据库读写分离和集群吧

另：其实论坛修改标题、内容的概率是很小的。大部分都是新增

[3e121eed-8e65-3172-aae2-820559e03d77.png]: /images/20220716/ecea2d50de354e72a0910248d4e48f17.png