一、表信息
表结构如下:
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()
函数将变量与当前行的姓名进行比较,以确定分组和行号。
这种方法需要对每行都进行比较,因此在大型数据集上可能会更慢,但在分组数较少且每组记录数较多的情况下,它可以实现更快的查询速度。
运行结果: