公司有个社区系统,和一般社区功能差不多,核心功能有发帖和回复。近年来公司业务增长较快,社区用户比较活跃,回复表记录数近1亿,为了提高系统的稳定性和可扩展性,我们用Atlas做分表,表结构如下:
CREATE TABLE `post_reply` (
`id` int(11) unsigned NOT NULL COMMENT '回复id',
`tid` int(11) unsigned NOT NULL COMMENT '帖子id' DEFAULT '0',
`user_id` int(11) unsigned NOT NULL COMMENT '用户id' DEFAULT '0',
`subject` varchar(80) NOT NULL COMMENT '回复标题' DEFAULT '',
`create_time` datetime NOT NULL COMMENT '发表时间' DEFAULT '0',
`message` mediumtext NOT NULL COMMENT '回复内容',
UNIQUE KEY `idx_id` (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Atlas配置如下:
tables = test.post_reply.tid.4
因为我们的业务场景大部分是查询某个帖子的回复,查询最多的是分页查询某个帖子的回复,所以根据帖子id进行分表,分成4张表(线上比这个大)。
最近有同事反馈一些用户在查看个人回复时,部分页取不到数据,原因是一些SQL直连Mysql有返回结果,而通过Atlas返回空,SQL如下:
SELECT * FROM post_reply WHERE `user_id`=123 AND `tid`
IN(228596, 225232, 228689, 228601, 228638, 209218, 224795, 228839) ORDER BY `create_time` DESC LIMIT 20, 20
为了调试,先把Atlas的sql日志打开,修改配置文件
sql-log = REALTIME
观察日志,Sql被重写如下
SELECT * FROM post_reply WHERE `user_id`=123 AND `tid`
IN(228596, 225232,) ORDER BY create_time DESC LIMIT 20, 20
SELECT * FROM post_reply WHERE `user_id`=123 AND `tid`
IN(228689, 228601) ORDER BY create_time DESC LIMIT 20, 20
SELECT * FROM post_reply WHERE `user_id`=123 AND `tid`
IN(228601, 228638) ORDER BY create_time DESC LIMIT 20, 20
SELECT * FROM post_reply WHERE `user_id`=123 AND `tid`
IN(224795, 228839) ORDER BY create_time DESC LIMIT 20, 20
这条SQL语句的语义是找出用户ID为123,按时间倒序排第2页(每页20)的回复。
为什么会出现为空呢?
因为要从总表取第21-40的记录,不能保证每个子表都有40条记录,即可能表0只有10条记录,表1、表2、表3都只有10条记录,所以需要从每个子表上取前40条记录,然后将每个子表返回的记录合并再排序;但Atlas直接从每个子表上取21-40的记录,这样每个子表都返回空,所以结果为空。
那能不能重写这块的代码,把偏移量的逻辑改下,即取偏移21-40的记录,先从每个子表取0-40的记录,然后再排序呢?
初步看了下代码,找到改写SQL的地方,文件名proxy-plugin.c,函数为combine_sql
改写了Limit 的地方,代码如下
for (i = 2; i < len; ++i) {
if (i < start_skip_index || i > end_skip_index) {
if (ts[i]->token_id != TK_OBRACE) g_string_append_c(sql, ' ');
if (i == table) {
//m是表的索引
g_string_append_printf(sql, "%s_%u", ts[i]->text->str, m);
} else if (i == property_index) {
g_string_append_printf(sql, "%s%s", ts[i]->text->str, tmp->str);
} else if (ts[i]->token_id == TK_STRING) {
g_string_append_printf(sql, "'%s'", ts[i]->text->str);
} else if (ts[i]->token_id == TK_COMMENT) {
g_string_append_printf(sql, "/*%s*/", ts[i]->text->str);
}else if (ts[i]->token_id == TK_SQL_LIMIT){
g_string_append(sql, ts[i]->text->str);
if ( ((len - i) >= 4)
&& (ts[i+1]->token_id == TK_INTEGER)
&& (ts[i+2]->token_id == TK_COMMA)
&& (ts[i+3]->token_id == TK_INTEGER)
){
int offset, len;
gchar buf[20];
offset = atoi(ts[i+1]->text->str);
len = atoi(ts[i+3]->text->str);
len += offset;
sprintf(buf, " 0, %d", len);
g_string_append(sql, buf);
i += 3;
}
} else {
g_string_append(sql, ts[i]->text->str);
}
}
}
重新编译之后,从日志上看SQL达到我们预期:
SELECT * FROM post_reply WHERE `user_id`=123 AND `tid`
IN(228596, 225232,) ORDER BY create_time DESC LIMIT 0, 40
SELECT * FROM post_reply WHERE `user_id`=123 AND `tid`
IN(228689, 228601) ORDER BY create_time DESC LIMIT 0, 40
SELECT * FROM post_reply WHERE `user_id`=123 AND `tid`
IN(228601, 228638) ORDER BY create_time DESC LIMIT 0, 40
SELECT * FROM post_reply WHERE `user_id`=123 AND `tid`
IN(224795, 228839) ORDER BY create_time DESC LIMIT 0, 40
但结果还是不正常,为什么呢,再看了合并的代码
原来重写对每个子表的查询之后,Atlas没有做排序,而直接将每个子表返回的结果集放到结果中,如果达到要求的记录数就直接返回了,也就是说如果是LIMIT 20,20,而第一个子表返回的记录数超过20,就直接返回了。
使用Atlas的同学如果有上面LIMIT的场景得注意了。
有解决没方案呢,方案肯定是有的,方法总比问题多.
记录所有用户发过的回复,然后先按用户id做分页查询回复,然后拿帖子id和回复id查询子表。
当然根本的方案还得从中间件上解决,这样对业务的侵入是最少的,后面再专门介绍数据库中间件相关。