如何设计一个排行榜？

1 MySQL 的 ORDER BY 关键字

第⼀种要介绍的实现⽅式就是直接使⽤ MySQL 的 ORDER BY 关键字。 ORDER BY 关键字可以对查询出来的数据按照指定的字段进⾏排序。

SELECT col1, col2, ...
FROM talename
ORDER BY col1, col2, ... ASC/DESC;

这种⽅式的优缺点也⽐较明显。好处是⽐较简单，不需要引⼊额外的组件，成本⽐较低。坏处就是每次⽣成排⾏榜都⽐较耗时，对数据库的性能消耗⾮常之⼤，数据量⼀⼤，业务场景稍微复杂⼀点就顶不住了。

我们这⾥创建⼀个名为 cus_order 的表，来实际测试⼀下这种排序⽅式。为了测试⽅便， cus_order 这张表只有 id 、 score 、 name 这 3 个字段。

CREATE TABLE `cus_order` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`score` int(11) NOT NULL,
`name` varchar(11) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=utf8mb4;

我们定义⼀个简单的存储过程（PROCEDURE）来插⼊ 100w 测试数据。

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `BatchinsertDataToCusoder`(IN `start_num` INT, IN `max_num` INT)
BEGIN
    DECLARE i INT DEFAULT start_num;
    WHILE i < max_num DO
        INSERT INTO `cus_order`(`id`, `score`, `name`)
        VALUES (i, RAND() * 1000000, CONCAT('user', i));
        SET i = i + 1;
    END WHILE;
END;;
DELIMITER ;

存储过程定义完成之后，我们执⾏存储过程即可！

mysql> CALL BatchinsertDataToCusoder(1, 1000000);
Query OK, 1 row affected (1 min 19.93 sec)

等待⼀会，100w 的测试数据就插⼊完成了！

为了能够对这 100w 数据按照 score 进⾏排序，我们需要执⾏下⾯的 SQL 语句。

SELECT `score`, `name` FROM `cus_order` ORDER BY `score` DESC;

为了能够查看这套 SQL 语句的执⾏时间，我们需要通过 show profiles 命令。

不过，请确保你的 profiling 是开启（on）的状态（可以通过 show variables 命令查看）。
默认情况下， profiling 是关闭（off）的状态，你直接通过 set@@profiling=1 命令即可开启。
然后，我们就查询到了具体的执⾏速度。

mysql>     SELECT * FROM INFORMATION_SCHEMA.PROFILING ORDER BY SEQ;
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
| QUERY_ID | SEQ | STATE                          | DURATION | CPU_USER | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION         | SOURCE_FILE          | SOURCE_LINE |
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
|        1 |   2 | starting                       | 0.003161 | 0.000238 |   0.001357 |                 0 |                  11 |            0 |             0 |             0 |                 0 |                13 |                22 |     0 | NULL                    | NULL                 |        NULL |
|        1 |   3 | Executing hook on transaction  | 0.000032 | 0.000008 |   0.000024 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 1 |     0 | launch_hook_trans_begin | rpl_handler.cc       |        1478 |
|        1 |   4 | starting                       | 0.000040 | 0.000018 |   0.000022 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | launch_hook_trans_begin | rpl_handler.cc       |        1480 |
|        1 |   5 | checking permissions           | 0.000043 | 0.000012 |   0.000030 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 3 |     0 | check_access            | sql_authorization.cc |        2148 |
|        1 |   6 | Opening tables                 | 0.002119 | 0.000112 |   0.000836 |                 0 |                  10 |            0 |             0 |             0 |                 0 |                 8 |                31 |     0 | open_tables             | sql_base.cc          |        5854 |
|        1 |   7 | init                           | 0.000035 | 0.000011 |   0.000022 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 2 |     0 | execute                 | sql_select.cc        |         771 |
|        1 |   8 | System lock                    | 0.000231 | 0.000017 |   0.000165 |                 0 |                   2 |            0 |             0 |             0 |                 0 |                 2 |                 2 |     0 | mysql_lock_tables       | lock.cc              |         331 |
|        1 |   9 | optimizing                     | 0.000107 | 0.000007 |   0.000074 |                 0 |                   1 |            0 |             0 |             0 |                 0 |                 1 |                 0 |     0 | optimize                | sql_optimizer.cc     |         353 |
|        1 |  10 | statistics                     | 0.000809 | 0.000028 |   0.000514 |                 0 |                   6 |            0 |             0 |             0 |                 0 |                 6 |                 8 |     0 | optimize                | sql_optimizer.cc     |         693 |
|        1 |  11 | preparing                      | 0.002779 | 0.000064 |   0.000921 |                 0 |                  14 |            0 |             0 |             0 |                 0 |                14 |                 5 |     0 | optimize                | sql_optimizer.cc     |         777 |
|        1 |  12 | executing                      | 0.616958 | 0.455261 |   0.052201 |               348 |                1602 |            0 |             0 |          4003 |                 0 |                 6 |              2812 |     0 | ExecuteIteratorQuery    | sql_union.cc         |        1669 |
|        1 |  13 | end                            | 0.000009 | 0.000004 |   0.000005 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | execute                 | sql_select.cc        |         804 |
|        1 |  14 | query end                      | 0.000003 | 0.000002 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command   | sql_parse.cc         |        4956 |
|        1 |  15 | waiting for handler commit     | 0.002583 | 0.000015 |   0.000620 |                 5 |                   6 |            0 |             0 |             0 |                 0 |                 1 |                 2 |     0 | ha_commit_trans         | handler.cc           |        1611 |
|        1 |  16 | closing tables                 | 0.000052 | 0.000005 |   0.000046 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 2 |     0 | mysql_execute_command   | sql_parse.cc         |        5020 |
|        1 |  17 | freeing items                  | 0.000007 | 0.000005 |   0.000003 |                 0 |                   0 |            0 |             0 |             1 |                 0 |                 0 |                 0 |     0 | dispatch_sql_command    | sql_parse.cc         |        5489 |
|        1 |  18 | cleaning up                    | 0.000013 | 0.000007 |   0.000005 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 2 |     0 | dispatch_command        | sql_parse.cc         |        2532 |
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
17 rows in set, 1 warning (0.05 sec)

mysql> SELECT QUERY_ID, SUM(DURATION) AS total_duration
    -> FROM INFORMATION_SCHEMA.PROFILING
    -> GROUP BY QUERY_ID
    -> ORDER BY total_duration DESC;
+----------+----------------+
| QUERY_ID | total_duration |
+----------+----------------+
|        1 |       0.628981 |
|        2 |       0.044224 |
+----------+----------------+
2 rows in set, 1 warning (0.05 sec)

可以看到，⼀共耗时了接近 4s。

如何优化呢？加索引并且限制排序数据量是⼀种⽐较常⻅的优化⽅式。

我们对 score 字段加索引，并限制只排序 score 排名前 500 的数据。

{
  "Query ID": 38,
  "Duration": 0.0102915,
  "Query": "SELECT `score`, `name` FROM `cus_order` ORDER BY `score` DESC LIMIT 500;"
}

这个时候，我们再执⾏下⾯的 SQL 语句，速度就快了很多，只需要 0.01 秒就排序了前 500 名的数据。

当然了，这只是⼀个最简单的场景，实际项⽬中的复杂度要⽐我这⾥列举的例⼦复杂很多，执⾏速度也会慢很多。

不过，能不⽤ MySQL 的 ORDER BY 关键字还是要看具体的业务场景。如果说你的项⽬需要排序数据量⽐较⼩并且业务场景不复杂的话（⽐如你对你博客的所有⽂章按照阅读量来排序），我觉得直接使⽤ MySQL 的 ORDER BY 关键字就可以了。

2 Redis 的 sorted set

了解过 Redis 常⻅数据结构的⼩伙伴，都知道 Redis 中有⼀个叫做 sorted set 的数据结构经常被⽤在各种排⾏榜的场景下。

通过 sorted set，我们能够轻松应对百万级别的⽤户数据排序。这简直就是专⻔为排⾏榜设计的数据结构啊！

Redis 中 sorted set 有点类似于 Java 中的 TreeSet 和 HashMap 的结合体，sorted set 中的数据会按照权重参数 score 的值进⾏排序。

我们这⾥简单来演示⼀下。我们把上表中的数据添加到 sorted set 中。

127.0.0.1:6379> ZADD cus_order_set 112.0 user1 100.0 user2 123.0 user3 100.0  user4 33.0 user5 993.0 user6

sorted set 基本可以满⾜⼤部分排⾏榜的场景。

如果我们要查看包含所有⽤户的排⾏榜怎么办？通过 ZRANGE (从⼩到⼤排序) / ZREVRANGE （从⼤到⼩排序）

127.0.0.1:6379> ZREVRANGE cus_order_set 0 -1
1) "user6"
2）"user3"
3）"user1"
4）"user4"
5）"user2"
6）"user5"

如果我们要查看只包含前 3 名的排⾏榜怎么办？限定范围区间即可。

127.0.0.1:6379> ZREVRANGE cus_order_set 0 2
1) "user6"
2）"user3"
3）"user1"

如果我们需要查询某个⽤户的分数怎么办呢？通过 ZSCORE 命令即可。

127.0.0.1:6379> ZSCORE cus_order_set "user1"
"112"

如果我们需要查询某个⽤户的排名怎么办呢？通过 ZREVRANK 命令即可。

127.0.0.1:6379> ZREVRANK cus_order_set "user3"
(integer) 1

如何对⽤户的排名数据进⾏更新呢？通过 ZINCRBY 命令即可。

# 对 user1 的分数加2
127.0.0.1:6379> ZINCRBY cus_order_set +2 "user1"
"114"
 
# 对 user1 的分数减1
127.0.0.1:6379> ZINCRBY cus_order_set -1 "user1"
"113"
 
# 查看 user1 的分数
127.0.0.1:6379> ZSCORE cus_order_set "user1"
"113"

除了我上⾯提到的之外，还有⼀些其他的命令来帮助你解决更多排⾏榜场景的需求，想要深⼊研究的⼩伙伴可以仔细学习哦！

不过，需要注意的⼀点是：Redis 中只保存了排⾏榜展示所需的数据，需要⽤户的具体信息数据的话，还是需要去对应的数据库（⽐如 MySQL）中查。

你以为这样就完事了？不存在的！还有⼀些⽆法仅仅通过 Redis 提供的命令解决的场景。

⽐如，如何实现多条件排序？其实，答案也⽐较简单，对于⼤部分场景，我们直接对 score 值做⽂章即可。

更具体点的话就是，我们根据特定的条件来拼接 score 值即可。⽐如我们还要加上时间先后条件的话，直接在 score 值添加上时间戳即可。

再⽐如，如何实现指定⽇期（⽐如最近 7 天）的⽤户数据排序？

我说⼀种⽐较简单的⽅法：我们把每⼀天的数据都按照⽇期为名字，⽐如 20350305 就代表 2035 年 3 ⽉ 5 号。
如果我们需要查询最近 n 天的排⾏榜数据的话，直接 ZUNIONSTORE 来求 n 个 sorted set 的并集即可。

ZUNIONSTORE destination numkeys key1 key2...：求并集，将给定所有有序集合的并集存储在 destination 中，对相同元素对应的 score 值进行 SUM 聚合操作（可以通过 AGGREGATE 参数指定聚合函数，默认为 SUM），numkeys 为集合数量。

ZUNIONSTORE last_n_days n 20350305 20350306 ...

我不知道⼤家看懂了没有，我这⾥还是简单地造⼀些数据模拟⼀下吧！

# 分别添加了 3天的数据
127.0.0.1:6379> ZADD 20350305 112.0 user1 user2 123.0 user3 100.0
(integer)  3
127.0.0.1:6379> ZADD 20350306 80.0 user1 100.0 user4
(integer)   2
127.0.0.1:6379> ZADD 20350307 33.0 user4 993.0 user5
(integer)  2

通过 ZUNIONSTORE 命令来查看最近 3 天的排⾏榜情况：

127.0.0.1:6379> ZUNIONSTORE last_3_days n 20350305 20350306 20350307
(integer) 5

现在，这 3 天的数据都集中在了 last_n_days 中。

127.0.9.1:6379> ZRANGE last_3_days 0 -1 WITHSCORES
1） "user2"
2） "100"
3） "user3"
4） "123"
5） "user4"
6） "133"
7） "user1"
8） "192"
9） "user5"
10） "993"

如果⼀个⽤户同时在多个 sorted set 中的话，它最终的 score 值就等于这些 sorted set 中该⽤户的 score 值之和。

如果说你不想对 score 值求和也没关系，我们通过 AGGREGATE 参数指定聚合函数：

SUM：对于不同集合的相同元素使用和作为结果集中对应元素的 score。默认使用 SUM，如果不指定的话。
MIN：对于不同集合的相同元素使用某个元素最小的 score 值作为结果集中对应元素的 score
MAX：对于不同集合的相同元素使用某个元素最大的 score 值作为结果集中对应元素的 Score

既然可以求并集，那必然也可以求交集。你可以通过 ZINTERSTORE 命令来求多个 n 个 sorted set 的交集。

ZINTERSTORE destination numkeys key [key ...] [WEIGHTS weight [weight ...]] [AGGREGATE SUM|MIN|MAX]

有哪些场景可以⽤到多个 sorted set 的交集呢？⽐如每⽇打卡的场景，你对某⼀段时间每天打卡的⼈进⾏排序。

这个命令还有⼀个常⽤的权重参数 weights （默认为 1）。在进⾏并集/交集的过程中，每个集合中的元素会将⾃⼰的 score * weights 。

我下⾯演示⼀下这个参数的作⽤。

# staff_set 存放员工的排名信息
127.0.0.1:6379> ZADD staff_set 3.0 staff1 4.0 staff2
(integer) 2
# staff_set 存放管理者的排名信息
127.0.0.1:6379 ZADD manager_set 1.0 manager1 2.0 manager2
(integer) 2

如果，我们需要将员⼯和管理者放在⼀起⽐较，不过，两者权重分别为 1 和 3。

# staff_set 的权重为1 manager_set的权重为3
127.0.0.1:6379> ZUNIONSTORE al1_user_set 2 staff_set manager_set WEIGHTS 1 3
(integer) 4

最终排序的结果如下：

127.0.0.1:6379> ZREVRANGE al1_user_set 0 -1 WITHSCORES
1）"manager2"
2） "6"
3） "staff2"
4）"4"
5）"staffi"
6） "3"
7）"manager"
8）"3"

3 总结

上面我⼀共提到了两种设计排⾏榜的⽅法：

MySQL 的 ORDER BY 关键字
Redis 的 sorted set

其实，这两种没有孰好孰坏，还是要看具体的业务场景。如果说你的项⽬需要排序数据量⽐较⼩并且业务场景不复杂的话（⽐如你对你博客的所有⽂章按照阅读量来排序），我觉得直接使⽤ MySQL 的 ORDER BY 关键字就可以了，没必要为了排⾏榜引⼊⼀个 Redis。

另外，在没有分⻚并且数据量不⼤的情况下，直接在前端拿到所有需要⽤到的数据之后再进⾏排序也是可以的。

4 相关阅读

43 | 如何使用 Redis 搭建玩家排行榜？https://time.geekbang.org/column/article/134595—《SQL 必知必会》
构建一个科学的排行榜体系https://time.geekbang.org/column/article/5933—《推荐系统三十六式》
如何设计一个排行榜？https://mp.weixin.qq.com/s/2p84utXldMaeR4wU5guK1Q—why 技术
亿级用户游戏排行榜设计方案 https://mp.weixin.qq.com/sjWozyJ9vk9vM6u5_1WLGdw 一老周聊架构

🪴 Obsidian Publish

探索

如何设计一个排行榜？

1 MySQL 的 ORDER BY 关键字

2 Redis 的 sorted set

3 总结

4 相关阅读

目录