MySQL取每个分组后的top N优化

小白辰的Blog

表主要结构如下，需求为取时间范围内的某品牌的每个平台下的view_or_engagement最大的3条

CREATE TABLE `word_cloud` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `brand` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '品牌',
  `platform` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '平台',
  `view_or_engagement` int(11) DEFAULT NULL COMMENT '阅读或订阅',
  `article` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '文章',
  `title` text COLLATE utf8mb4_unicode_ci COMMENT '标题',
  `url` text COLLATE utf8mb4_unicode_ci COMMENT '链接',
  `time` date DEFAULT NULL COMMENT '时间',
  `data_type` tinyint(1) DEFAULT NULL COMMENT '类型',
  `md5_flag` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'md5标志',
  PRIMARY KEY (`id`),
  KEY `K` (`data_type`) USING BTREE,
  KEY `k_md5` (`md5_flag`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=123698 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

优化前的SQL

SELECT brand,platform,view_or_engagement,title,url  FROM (
    SELECT * FROM word_cloud WHERE brand = 'Siemens' and time >= '2020-04-01' and time<'2020-04-20'
) a WHERE (
    SELECT count(*) FROM (
    SELECT * FROM word_cloud WHERE  brand = 'Siemens' and time >= '2020-04-01' and time<'2020-04-20'
    ) b WHERE b.view_or_engagement > a.view_or_engagement and a.data_type = b.data_type
    and a.platform = b.platform and a.brand = b.brand
) < 3 
ORDER BY view_or_engagement desc;

优化后的SQL

SELECT brand,platform,view_or_engagement,title,url 
FROM( 
  SELECT brand,platform,view_or_engagement,title,url, 
   @order_rank := IF(@curr_ = platform,@order_rank + 1, 1) AS order_rank, 
   @curr_ := platform 
   FROM word_cloud 
   WHERE  brand = 'Siemens'
   and time >= '2020-04-01' and time < '2020-04-20' 
   ORDER BY platform,view_or_engagement desc  
) b WHERE order_rank <= 3

主要是使用了SQL变量，参考文章如下，点击查看

MySQL: How to Write a Query That Returns the Top Records in a Group