一大早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'
);