mysql 显示前10名发帖最多的用户名字及帖子数量

Question

有下面两张表，user(用户表)和thread(帖子表), 假设有100W用户, 500W帖子, 写一条SQL,
显示前10名发帖最多的用户名字及帖子数量, 并针对该语句指出如何设计合理的索引字段。
如何确认你写的sql会用到哪个索引，另外请说明下你写的 sql 是否是最优解。

表名	字段
user	uid, usernmae, password, create_time
thread	tid, uid, title, content, create_time

Answer 1

这个需求用 MySQL 实现的话，无论怎么优化，至少要扫描一次索引树。这算是一个常见场景，我就抛砖引玉。

常见实现

常见实现是在 thread 表的 uid 上建索引, 并通过下面这条 SQL 查出前10名发帖最多的 uid 及帖子数量

select uid,count(*) as total from thread group by uid order by total desc limit 10;

然后通过查到的 uid 获取用户名(uid有索引)

select uid, usernmae from user where uid in (%s, %s, %s);

查出数据后缓存 1 到 2 分钟，性能也还可以。如果要写成一条 SQL 就加个联表查询。

其他方案

我见过有这样的实现，可以参考一下： 增加一个统计表，索引为(total, uid), 有人发帖就更新这个表（异步更新），然后前端直接读取这个表