SQLite高效获取指定用户消息发送量排名的方法

针对你的问题，不需要全量统计所有用户再排序，我们可以通过统计比目标用户消息数更多的用户数量来直接计算排名，同时获取该用户的消息总数，这样能大幅减少查询耗时。

核心解决方案 #

基础查询（跳跃排名） #

如果你的排名规则是「有N个用户消息数比目标用户多，目标用户排名就是N+1」（即并列用户会占据不同排名位，比如两个用户都是第一，下一个用户是第三），可以用以下SQL：

SELECT
    ? AS user_id,
    -- 统计消息数大于目标用户的用户数量，加1得到排名
    (
        SELECT COUNT(DISTINCT user_id)
        FROM message
        GROUP BY user_id
        HAVING COUNT(*) > (SELECT COUNT(*) FROM message WHERE user_id = ?)
    ) + 1 AS `order`,
    -- 获取目标用户的消息总数
    (SELECT COUNT(*) FROM message WHERE user_id = ?) AS count

把其中的?替换成你要查询的用户ID（比如1702633101）即可得到符合要求的结果。

密集排名版本 #

如果需要「并列用户共享同一排名，下一个用户排名连续」（比如两个用户都是第一，下一个用户是第二），可以调整统计逻辑为计算不同的消息数大于目标用户的数量：

SELECT
    ? AS user_id,
    (
        SELECT COUNT(DISTINCT stats.count)
        FROM (SELECT COUNT(*) AS count FROM message GROUP BY user_id) AS stats
        WHERE stats.count > (SELECT COUNT(*) FROM message WHERE user_id = ?)
    ) + 1 AS `order`,
    (SELECT COUNT(*) FROM message WHERE user_id = ?) AS count

关键性能优化 #

要让这个查询在200万条数据的表上跑得飞快，必须创建索引。因为SQLite在没有索引的情况下，GROUP BY user_id和WHERE user_id = ?都会触发全表扫描，速度极慢。

创建一个针对user_id的索引：

CREATE INDEX idx_message_user_id ON message(user_id);

这个索引可以让SQLite直接通过索引统计每个user_id的记录数，不需要回表查询原始数据，能把查询速度提升几个数量级。

为什么这个方法高效 #

你的原查询需要完成三个高开销步骤：

1. 扫描全表，按user_id分组统计消息数
2. 对所有分组结果进行排序
3. 返回全量数据

而新方法只需要：

1. 通过索引快速获取目标用户的消息数
2. 通过索引统计有多少用户的消息数超过这个值
3. 计算排名并返回单条结果

完全避免了全量排序的开销，在用户数量越多的场景下，性能提升越明显。

内容的提问来源于stack exchange，提问作者user18042207