记录一次sql语句优化经历

一大早DBA发来一个sql优化的需求：

SELECT rownum FROM (SELECT user_id, (@rownum:=@rownum+1) AS rownum FROM `world_boss_player` a, (SELECT @rownum:= 0) r ORDER BY a.`max_damage_season` DESC ,a.`user_id` ASC) AS u WHERE u.user_id = '30439853'; @PiuQiuPiu 麻烦看看这个sql, 感觉有点复杂. 效率低

去生产环境执行：

看需求是要查询某个user_id的玩家按照max_damage_season和user_id排序后的名次。

sql逻辑是先将所有玩家数据按照对应列排序后依次记录名次，然后从内存临时表中再获取对应user_id的名次，这里使用了用户变量@rownum，并且有频繁的递增赋值操作，这个过度消耗cpu和内存，如果数据量足够大，请求足够频繁，影响的确会很大。

理清楚需求和现状之后开始思考如何优化。。。。。。

首先想到的是窗口函数

窗口函数是在查询结果集内进行计算和排序的强大工具，它可以在不引入额外的变量和子查询的情况下，实现复杂的排序和分析操作。

相比之下，使用用户变量来模拟排名计算可能会产生额外的开销，因为它需要在查询过程中进行变量的递增和赋值操作。这可能导致在大型数据集和高并发环境下的性能问题。

窗口函数在MySQL 8.0及以上版本中引入，它们经过优化，可以更高效地处理排序和分析操作。它们能够利用数据库的内部优化策略，减少不必要的数据访问和计算操作，从而提高性能。

那么写吧

SELECT user_id, rank
FROM (
  SELECT user_id, max_damage_season,
         ROW_NUMBER() OVER (ORDER BY max_damage_season DESC, user_id ASC) AS rank
  FROM `world_boss_player`
) AS subquery
WHERE user_id = '30439853';

这个查询语句使用ROW_NUMBER()窗口函数来为每行分配一个排名，根据max_damage_season字段降序和user_id字段升序进行排序。然后，从子查询中选择指定user_id的行，并返回对应的user_id和排名。

或者方案2

新的思路是使用WITH子句（也称为公共表表达式）将子查询的结果存储为临时表，并在主查询中引用该临时表。以下是修正后的查询，使用WITH子句来避免重复执行子查询：

WITH player AS (
  SELECT max_damage_season
  FROM `world_boss_player`
  WHERE user_id = '30439853'
)
SELECT COUNT(*) + 1 AS rank
FROM `world_boss_player`
WHERE max_damage_season > (SELECT max_damage_season FROM player)
  OR (max_damage_season = (SELECT max_damage_season FROM player) AND user_id < '30439853');

在这个修正后的查询中，我们使用了WITH子句来创建名为player的临时表，其中存储了子查询的结果。然后，在主查询中，我们引用了这个临时表来避免重复执行子查询。

但是项目当前使用的是较旧的5.7版本，以上方案都不支持，所以只能考虑使用其他方法来实现类似的功能。。。。

最终解决方案是~~~~~~~~

使用内联视图（inline view）来存储子查询的结果，并在主查询中引用该内联视图。以下是一个优化后的查询示例：

SELECT COUNT(*) + 1 AS rank
FROM `world_boss_player` wbp
JOIN (
  SELECT max_damage_season
  FROM `world_boss_player`
  WHERE user_id = '30439853'
) AS subquery
WHERE wbp.max_damage_season > subquery.max_damage_season
  OR (wbp.max_damage_season = subquery.max_damage_season AND wbp.user_id < '30439853');

在这个优化后的查询中，我们使用了内联视图（子查询作为表）来存储子查询的结果。这样，在主查询中，我们只需要引用该内联视图一次，而不需要多次执行子查询。

对比优化前的语句在测试和线上环境测试，结果一致。

执行时间由1124s降低值269ms

肉眼可见效果：），再从explain分析下效果

优化前explain

explain SELECT rownum FROM (SELECT user_id, (@rownum:=@rownum+1) AS rownum FROM `world_boss_player` a, (SELECT @rownum:= 0) r ORDER BY a.`max_damage_season` DESC ,a.`user_id` ASC) AS u WHERE u.user_id = '30439853'
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>		ref	<auto_key0>	<auto_key0>	8	const	10	100	Using where
2	DERIVED	<derived3>		system					1	100	Using filesort
2	DERIVED	a		index		idx_max_damage_season	8		636,421	100	Using index
3	DERIVED										No tables used

优化后explain

explain SELECT COUNT(*) + 1 AS rank FROM `world_boss_player` wbp JOIN ( SELECT max_damage_season FROM `world_boss_player` WHERE user_id = '30439853' ) AS subquery WHERE wbp.max_damage_season > subquery.max_damage_season OR (wbp.max_damage_season = subquery.max_damage_season AND wbp.user_id < '30439853')
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	world_boss_player		const	PRIMARY,idx_max_damage_season	PRIMARY	8	const	1	100
1	SIMPLE	wbp		range	PRIMARY,idx_max_damage_season	idx_max_damage_season	16		2,560	100	Using where; Using index

通过对比这两个EXPLAIN结果，看到以下优化的变化：

• 优化后的查询使用了更合适的连接类型，减少了不必要的文件排序操作。
• 优化后的查询使用了更适合的索引，提高了查询的性能。
• 优化后的查询没有使用派生表，减少了不必要的查询开销。

综上所述，优化后的查询执行计划显示出更好的性能和效率。

特殊情况的处理，原sql在玩家数据不存在的情况下是不返回任何数据行的，所以新的查询也需要过滤一下，最终sql为：

SELECT COUNT(*) + 1 AS rownum
FROM `world_boss_player` wbp
JOIN (
  SELECT max_damage_season
  FROM `world_boss_player`
  WHERE user_id = '30439853'
) AS subquery
WHERE (wbp.max_damage_season > subquery.max_damage_season
        OR (wbp.max_damage_season = subquery.max_damage_season AND wbp.user_id < '30439853'))
HAVING EXISTS (
    SELECT 1
    FROM `world_boss_player`
    WHERE user_id = '30439853'
);