OSChina 的留言表设计说明和OSChina 用户动态设计

2019独角兽企业重金招聘Python工程师标准>>>

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 上做的一些动作，例如提问、回答问题、动弹、评论动弹、发表新闻评论、代码分享、写博客等等一系列的行为。好比说我们进入 @蟋蟀哥哥 的个人空间，就能看到他最近都做了些什么动作:

而这些不同的动作对应的数据其实是存在不同的表中，例如话题表、回帖表、评论表等等。

今天主要是介绍 OSChina 是如何将这些属于不同范围的数据汇总到用单一时间轴进行展示的动态。

动态表

首先要说明的是动态表，这个表在 OSChina 数据库中对应的表名是 osc_opt_logs ，从这个名字能看出意思是“操作记录表”，字段如下：

字段说明：

id 主键字段，动态记录的唯一标识
user 某人的动态
obj_type/obj_id 由这两个字段组合起来，表示对应不同类型的动作，例如是新闻、提问、回帖等，每一种动作都有唯一的 obj_type 对应，这些常量有：
parent_type/parent_id 这两个字段用来表示具有一些层次关系的动态，例如回帖：parent_type 和 parent_id 就会填充上回帖对应帖子的编号和类型
shown 转发标识，主要用于动弹的评论是否在空间中显示与否
reply_count 如果此动态是一条动弹，那么此字段存放动弹的评论次数
memo 如果此动态是一条动弹，那么此字段存放动弹的内容
attachment 动弹对应的图片
referer 保留用
appid 动弹的方式，可以是PC浏览器、手机版或者不同的手机客户端
create_time 动作发生的时间

obj_type 取值常量表：

public final static byte TYPE_PROJECT = 0x01;
public final static byte TYPE_QUESTION = 0x02;	//问题
public final static byte TYPE_ANSWER = 0x11;		//答案
public final static byte TYPE_BLOG = 0x03;
public final static byte TYPE_NEWS = 0x04;
public final static byte TYPE_CODE = 0x05;

public final static byte TYPE_JOB = 0x06;//招聘职位
public final static byte TYPE_GROUP_BUY = 0x0F;	//团购信息

public final static byte TYPE_NEWS_COMMENT 	= 0x10;	//新闻评论
public final static byte TYPE_BLOG_COMMENT 	= 0x12;	//博客评论
public final static byte TYPE_CODE_COMMENT 	= 0x13;	//代码评论
public final static byte TYPE_JOB_COMMENT 	= 0x14;	//招聘职位评论

public final static byte TYPE_TWEET = 100;//动弹
public final static byte TYPE_TWEET_REPLY = 101;//动弹评论

假设某个动态是回帖，那么 obj_type 值为 TYPE_ANSWER，而 obj_id 的值则为对应回帖的唯一编号。

动弹和动弹评论

动弹和动弹评论是比较特殊的动态，它没有对应到其他表中的关系。动弹对应的 obj_type 值为 TYPE_TWEET，动弹评论对应的 obj_type 值为 TYPE_TWEET_REPLY。而 memo 则存放了动弹或者评论的内容。

当我们进入某个用户空间的时候，会列出这个用户的所有动态，查询很简单：

SELECT * FROM osc_opt_logs WHERE user = ? ORDER BY id DESC

如果是查看某个类别的动态，例如我们只想看 @蟋蟀哥哥 的动弹，只需要：

SELECT * FROM osc_opt_logs WHERE user = ? AND obj_type = 100 ORDER BY id DESC

其中 100 就是 TYPE_TWEET 常量值。

这样简单的查询，在任何数据库上执行都是非常之快的。

唯一麻烦的在于，取出了动态列表后，如何加载它们对应的目标数据，如果是一个提问，那必须取出对应的帖子记录。

这个的确是很麻烦，代码量也比较大，只能简单的介绍一下思路：

首先将获取的动态列表按照 obj_type 进行分组，然后根据 obj_id 批量去 load 对应的数据，例如我们可组合从像下面这样的 SQL 语句来 load 数据：

SELECT * FROM osc_questions WHERE id IN (?,?,?,?,?,?)

这样在首次进入某个用户空间，光是动态这块可能需要执行七八个SQL语句来获取显示完整的动态列表，好在这些 SQL 语句都是最基本的查询，再结合缓存的使用，性能还是非常之好的。

查看自己的空间

以上都是关于看别人空间时的说明，但大家应该都发现了，在看自己的空间时，并不只是自己的动态，还会包含自己所关注的人的所有动态。

我自己的以及我关注的人的动态。这个查询条件看似简单，一般人会这样写 SQL 语句：

SELECT l.* FROM osc_opt_logs l,osc_friends f WHERE l.user = ? OR (l.user=f.friend AND f.user = ?) ORDER BY l.id DESC

请注意这里用到了 OR 查询，这个 OR 查询让你的所有优化都白做，索引也用不上。在你绞尽脑汁研究怎么改 SQL 语句的时候，不妨换一个角度来思考。

不就是差一个我自己的 id 吗？如果在 osc_friends 表里也有我自己的记录，那这个 SQL 不就可以简化为：

SELECT l.* FROM osc_opt_logs l,osc_friends f WHERE l.user=f.friend AND f.user = ? ORDER BY l.id DESC

这里没有 OR 查询，查询速度快多了。唯一需要处理的就是每次用户注册的时候往 osc_friends 表中插入一条自己是自己好友的记录，也就是 user=我的用户id，friend=我的用户id。

这还是我的偏好，通过冗余数据来提升查询性能。是好是坏，大家自己掂量。

我只能说目前这种方案是适合 OSChina 目前的状态，如果规模再大一点时候肯定还有改造，至于怎么改，到时候再说。涉及到用户动态的其他方面的都不值一提。

全文完，希望对大家有所帮助。

转载于:https://my.oschina.net/u/2822116/blog/888436