OSChina 的留言表设计说明

红薯 发布于 2012/09/16 08:50
阅读 27K+
收藏 332

不少人来询问关于留言的设计,现饿着肚子分享出来,然后去吃早饭。

OSChina 的留言表 osc_msgs ,表结构如下:

字段说明:

id : 留言主键字段,自增长
user : 留言的主人
friend : 对方的ID
sender : 留言发送者
receiver : 留言接收者
type : 留言类型(普通消息、系统消息)
content : 留言内容
send_time : 发送时间
read_time : 阅读时间
status : 留言状态

其中 user 和 friend 稍显特殊,其他的字段意义已非常明确不再说明。

当 A 给 B 发送一条留言时,会往 osc_msgs 表中插入两条相同的记录,唯一不同的是 user 和 friend 这两个字段的值是对调的,当然 id 因为是自增长的所以也不同。

为什么要这么做?

1. 一条留言保存两条记录:因为每个人都有收到的留言和已发送留言,当发送人删除了已发送留言,不会影响到接收人查看收到的留言

2. user/friend/sender/receiver 这四个字段是不是多余?

关键的问题就在于此,你还记得 osc 的留言箱吗?进入留言箱里显示的是你最近的留言往来,包含你接收到的和你发出的,它们是按照时间进行排序的。

假设只有 sender/receiver 这两个字段,那么要将接收和发送的留言放在一起,就必须用 UNION 来合并两个查询结果,然后再做排序,而且你还必须有个字段来标注到底是接收到的留言还是发出的留言。这样的 SQL 可能会是这样:

SELECT * FROM (
    SELECT * FROM osc_msgs WHERE type=<接收> AND receiver=<我> 
    UNION 
    SELECT * FROM osc_msgs WHERE type=<发送> AND sender=<我>
) t ORDER BY send_time DESC

这样的 SQL 语句不用执行都知道性能很差。

那么以冗余来换性能的思路,我们对这个表进行了小改造。

增加两个字段 user 和 friend,当 A 发送留言给 B 时,会写入两条记录:

记录1. user=A,friend=B,sender=A,receiver=B
记录2. user=B,friend=A,sender=A,receiver=B

再来看看在新的表结构下,我们如何改写上面的语句:

SELECT * FROM osc_msgs WHERE user = <我> ORDER BY id DESC

这两个 SQL 语句孰优孰劣,相信大家能比较得出来。

如果是要列出我跟每个人的最后一条留言的话(就好象留言箱首页显示的内容)可以这样写 SQL 语句:

SELECT MAX(id) AS id, COUNT(id) AS msgCount FROM osc_msgs WHERE user = ? GROUP BY friend ORDER BY id DESC

解释完毕。

本文只是提供一种表结构设计的参考思路,这也不是放之四海而皆准的方法,关键的问题在于你想解决什么样的问题,对 OSC 来说性能很重要,如果能简单的通过冗余来提升性能,这很划算。

OSChina 用户动态设计说明>>

加载中
2
明月惊鹊
明月惊鹊
薯哥的奉献精神令人钦佩啊,以后大家在街边看到卖红薯的要买多几斤,以表谢意。
geeksun
geeksun
哈哈,带动红薯的销售啦
新青年
新青年
唔 笑了...
justjavac
justjavac
如果金山公司写篇文章的话,读者表示鸭梨很大啊。
阿伏流
阿伏流
哈哈,好想法。。。
Finlay
Finlay
哈哈
1
RobinsonLiao
RobinsonLiao
send_time : 发送时间
read_time : 阅读时间
一个timestamp,一个datetime,你怎么考虑的
f
fcyhaha23
顶一下
justjavac
justjavac
发送时间不能为空,接收时间可以为空,如果read_time用timestamp类型的话,设置为空时,应该是取当前时间吧。
1
Jooooooker
Jooooooker
以前看过文章说最好不用数据类型datetime 本人比较弱 求指点。。。
1
用户已屏蔽
用户已屏蔽

@红薯 为什么非得一条消息复制两份了? 好占空间!而且多步操作!

我在想  status 里面换种形式存!! 判断用个位运算

而用户删除并非真正删除! 这样不就OK了?

筱龙缘
筱龙缘
回复 @红薯 : 加个字段 标识是否删除?
水牛叔叔
水牛叔叔
红薯独到,佩服
winnie_tour
winnie_tour
回复 @红薯 : 这样设计确实讨巧了,其实我觉得这个问题倒不如像邮箱那样,现在我看osc留言收信和发信在一起,反而觉得有点小乱。。
a
alex81
当初项目中,打死也没想到过,一次发送两个信息,a,b互换角色。虽然数据貌似多了,但日后查询,标注阅读,删除的麻烦问题迎刃而解了。哈哈,厉害。
红薯
红薯
回复 @滔哥 : 只一条记录,会让查询变得复杂,你想想便知
下一页
0
jingshishengxu
jingshishengxu
content 要不要考虑单独一个表,感脚复制两份太占空间了
司贝特软件
司贝特软件
回复 @xiaoLongyuan : 限制content大小?
筱龙缘
筱龙缘
@红薯 看了OSCHINA的服务器 我汗颜了 为什么您的tomcat跑起来 就那么好呢?O(∩_∩)O
红薯
红薯
@xiaoLongyuan 目前只在程序里做了限制
筱龙缘
筱龙缘
回复 @红薯 : 红薯老大 content有大小限制么 如果太大的话 把content 弄成表?
fmxzhou
fmxzhou
回复 @红薯 : 用主键定位会很快!
下一页
0
红薯
红薯
@Jooooooker @achan2090 mysql 规定一个表里只能有一个 timestamp 字段。
司贝特软件
司贝特软件
回复 @窦骁年华 : 应该可以吧?
Minho
Minho
储存时间可不可以格式化成时间戳,保存成int类型呢?
fmxzhou
fmxzhou
回复 @Jooooooker : 占字节比timestamp大!
wartskcaj
wartskcaj
应该是只能有一个timestamp可以设为CURRENT_TIMESTAMP 吧?
wartskcaj
wartskcaj
回复 @Jooooooker :当把时间字段作为条件查询时,性能不高
下一页
0
用户已屏蔽
用户已屏蔽
@红薯 把时间字段全用 int 存时间CUO 不可以吗?
小本聪
小本聪
回复 @李飞麟 : 哈哈
Minho
Minho
我也是这么想的,之前研究一些CMS的时候,发现他们都是储存的时间戳,类型都是int!
阿伏流
阿伏流
chuo。。。
0
cswy
cswy

引用来自“红薯”的答案

@Jooooooker @achan2090 mysql 规定一个表里只能有一个 timestamp 字段。

为什么不设计为都是datetime

wartskcaj
wartskcaj
datetime占用更多空间
红薯
红薯
这是个人习惯问题,没有原因。这个不是本文的关键点:)
返回顶部
顶部