MySQL 分组后统计 TopN 优化思路

一、需求背景

在 MySQL 中我们需要分组统计数据时，大多都需要借助 group by 语句，但基本都是对一个组下的整体操作，比如计算最大最小值，计算总数，整体拼接等等，但如果想要对每个组下的数据排序后取出 topN 值便不是那么的方便了，本文就带大家探索下MySQL 分组后统计 TopN 的优化思路。

这里我们创建一个测试表，表结构如下：

CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1746687 DEFAULT CHARSET=utf8;

接着我们编写一个存储过程来生成测试数据，脚本如下，该脚本主要创建 100 个name，每个 name 生成 1000 条不重复的 score 数据，共计十万条数据：

CREATE PROCEDURE `generate_score_data`()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE j INT DEFAULT 0;
    DECLARE name VARCHAR(255);
    DECLARE score INT;
    DECLARE score_set VARCHAR(10000) DEFAULT '';

    WHILE i < 100 DO
        SET name = CONCAT('name', i);

        SET j = 0;
        SET score_set = '';
        WHILE j < 1000 DO
            REPEAT
                SET score = FLOOR(RAND() * 5001);
            UNTIL NOT FIND_IN_SET(score, score_set)
            END REPEAT;

            SET score_set = CONCAT(score_set, ',', score);

            INSERT INTO score (name, score) VALUES (name, score);

            SET j = j + 1;
        END WHILE;

        SET i = i + 1;
    END WHILE;
END

执行存储过程：

CALL generate_score_data();

可以查看表中的数据数量：

下面就可以提出我们的需求目的了：取出每个 name 下 score 的 top3 数据出来。

二、TopN 实现思路

2.1 子查询的方式

子查询的方式是最容易想到的也是效率最差的一种方式，也是网上普遍写方式，通过构造一个子查询，在子查询中判断相同 name 下的当前的 score 大于主 score 的数量，如果小于3 则肯定是位于 top3 的数据。

实现如下：

SELECT
    s1.*
FROM
    score s1 
WHERE
    ( SELECT count(*) FROM score s2 WHERE s1.`name` = s2.`name` AND s2.score > s1.score )< 3 
ORDER BY
    s1.id ASC

运行结果：

从结果中可以看到花费了 22.66s ，效率着实不高。

2.2 通过 ROW_NUMBER 优化（推荐）

使用窗口函数 ROW_NUMBER() 对每个姓名进行分组，并按照成绩降序进行排序。然后，在外部包装一层选择具有行号小于等于3的记录，这样就可以得到每个组的 top 3 记录。

实现如下：

SELECT
    s.id,
    s.`name`,
    s.score 
FROM
    ( SELECT id, `name`, score, ROW_NUMBER() OVER ( PARTITION BY NAME ORDER BY score DESC ) AS row_num FROM score ) AS s 
WHERE
    s.row_num <= 3
ORDER BY
    s.id ASC

运行结果：

可以看出使用该方式，仅 0.222s 就查出了数据。

2.3 通过 RANK() 优化

实现如下：

SELECT
    s.`name`,
    s.score 
FROM
    ( SELECT id, `name`, score, RANK() OVER ( PARTITION BY NAME ORDER BY score DESC ) AS rank_num FROM score ) AS s 
WHERE
    s.rank_num <= 3
ORDER BY
    s.id ASC

在这个查询中，将 ROW_NUMBER() 函数更改为 RANK() 函数。RANK() 函数在计算排名时会跳过平级项并产生相同的排名值。例如，如果有两个人的成绩都是第一名，它们的排名值都是1。

这种方式可以确保在并列排名的情况下，多个人都能被包含在 top 3 中。然而，如果有并列排名的记录超过了 top 3，它们可能会导致结果集超出预期的记录数，因此使用的时候需要注意是否合适。

运行结果：

从结果上可以看出比 ROW_NUMBER() 快了仅 0.002s 。

2.4 通过变量的方式

实现如下：

SELECT
    t.id,
    t.`name`,
    t.score 
FROM
    (
    SELECT
        s.*,
        @rn :=
    IF
        (
            @NAME = s.NAME,
            @rn + 1,
        IF
        ( @NAME := NAME, 1, 1 )) AS row_num 
    FROM
        score s
        CROSS JOIN ( SELECT @rn := 0, @NAME := '' ) AS vars 
    ORDER BY
        s.NAME,
        s.score DESC 
    ) AS t 
WHERE
    t.row_num <= 3
ORDER BY
    t.id ASC