MySQL 分组后统计 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`()
BEGINDECLARE 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 DOSET name = CONCAT('name', i);SET j = 0;SET score_set = '';WHILE j < 1000 DOREPEATSET 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 的数据。

实现如下：

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

运行结果：

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

2.2 通过 ROW_NUMBER 优化（推荐）

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

实现如下：

SELECTs.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 
WHEREs.row_num <= 3
ORDER BYs.id ASC

运行结果：

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

2.3 通过 RANK() 优化

实现如下：

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

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

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

运行结果：

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

2.4 通过变量的方式

实现如下：

SELECTt.id,t.`name`,t.score 
FROM(SELECTs.*,@rn :=IF(@NAME = s.NAME,@rn + 1,IF( @NAME := NAME, 1, 1 )) AS row_num FROMscore sCROSS JOIN ( SELECT @rn := 0, @NAME := '' ) AS vars ORDER BYs.NAME,s.score DESC ) AS t 
WHEREt.row_num <= 3
ORDER BYt.id ASC

在这个查询中，使用了两个 MySQL 变量 @name 和 @rn 来跟踪当前分组和每个分组中的行号。在内部查询中，对表进行排序，并使用 CROSS JOIN 子句创建了一个包含两个变量的虚拟表。然后，使用 IF() 函数将变量与当前行的姓名进行比较，以确定分组和行号。

这种方法需要对每行都进行比较，因此在大型数据集上可能会更慢，但在分组数较少且每组记录数较多的情况下，它可以实现更快的查询速度。

运行结果：