GreatSQL 优化技巧：最值子查询与窗口函数相互转换
GreatSQL 优化技巧：最值子查询与窗口函数相互转换
导语
近期 SQL 优化过程中遇到两个案例，一个是将最值子查询改写为窗口函数，另一个是将窗口函数改写为最值子查询，觉得很有意思，特此记录分享一下。
SQL案例
两个 SQL 语句
SQL1：
SELECT imei, c1
FROM (SELECT imei,
c1,
row_number() OVER(PARTITION BY imei ORDER BY statistic_time DESC) AS rn
FROM t1)
WHERE rn = 1;
SQL2：
SELECT *
FROM t1 a
WHERE to_char(statistic_time, 'yyyymmdd') =
(SELECT MAX(to_char(statistic_time, 'yyyymmdd'))
FROM t1 b
WHERE a.c2 = b.c2
)
AND a.imei = 'a';
这两个语句的真实场景并不是在一个系统中遇到的，这里只是用一张测试表来说明这两个 SQL 的问题。
测试表与测试数据如下：
CREATE TABLE t1(
imei VARCHAR(100),
statistic_time datetime,
c1 INT,
c2 INT,
PRIMARY KEY(imei,statistic_time)
);
SET sql_mode=oracle;
DELIMITER //
CREATE OR REPLACE PROCEDURE p1 IS
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO t1 VALUES('a',TO_DATE('2025-01-01','yyyy-mm-dd')+ (1/24/60/60)*i, TRUNC(RAND()*200000),TRUNC(RAND()*1000));
END LOOP;
FOR i IN 1..100000 LOOP
INSERT INTO t1 VALUES('b',TO_DATE('2025-01-01','yyyy-mm-dd')+ (1/24/60/60)*i, TRUNC(RAND()*200000),RAND()*1000);
END LOOP;
FOR i IN 1..100000 LOOP
INSERT INTO t1 VALUES('c',TO_DATE('2025-01-01','yyyy-mm-dd')+ (1/24/60/60)*i, TRUNC(RAND()*20000),RAND()*1000);
END LOOP;
END ;
//
DELIMITER ;
CALL p1;
数据统计分布如下：
greatsql> SHOW INDEX FROM T1;
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t1 | 0 | PRIMARY | 1 | imei | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
| t1 | 0 | PRIMARY | 2 | statistic_time | A | 298697 | NULL | NULL | | BTREE | | | YES | NULL |
| t1 | 1 | idx_c1 | 1 | c1 | A | 132721 | NULL | NULL | YES | BTREE | | | YES | NULL |
| t1 | 1 | idx_c2 | 1 | c2 | A | 1015 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)
t1表插入30万行数据，列imei的唯一值3个,列c1唯一值13万左右，列c2唯一值1000左右。
测试数据只是模拟了真实的业务数据分布特点,真实业务场景数据量是千万级别的。
SQL1 执行分析与优化
SQL1 执行计划：
greatsql> EXPLAIN ANALYZE
-> SELECT imei, c1
-> FROM (SELECT imei,
-> c1,
-> row_number() OVER(PARTITION BY imei ORDER BY statistic_time DESC) AS rn
-> FROM t1)
-> WHERE rn = 1\G
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on alias_temp_101454818022785039 using <auto_key0> (rn=1) (cost=0.35..3.50 rows=10) (actual time=1327.190..1327.192 rows=3 loops=1)
-> Materialize (cost=0.00..0.00 rows=0) (actual time=1327.172..1327.172 rows=300000 loops=1)
-> Window aggregate: row_number() OVER (PARTITION BY t1.imei ORDER BY t1.statistic_time desc ) (actual time=345.111..918.642 rows=300000 loops=1)
-> Sort: t1.imei, t1.statistic_time DESC (cost=31248.70 rows=298697) (actual time=345.091..374.978 rows=300000 loops=1)
-> Index scan on t1 using idx_c1 (cost=31248.70 rows=298697) (actual time=0.133..96.587 rows=300000 loops=1)
1 row in set (1.36 sec)
从执行计划可以看出，对t1表的索引idx_c1做了覆盖扫描，又按照 t1.imei, t1.statistic_time做排序，再做窗口聚合，物化处理，物化处理后的结果集是30万，而最终结果集只有3行。测试数据量只有30万，耗时1327ms，如果数据量放大100倍呢，这个代价就非常高了。
如何才能提升这个SQL的效率呢？
结果集只有3行，却扫描了整个索引，SQL 优化的核心思想，是减少I/O消耗，那就是要扫描的数据尽量少。分组排序列imei,statistic_time 是有主键联合索引的，这里窗口函数的目的就是取得前导列imei对应的statistic_time最大值的那条记录，而通过索引取最值是不用去扫描整个索引的。所以我们就利用索引有序这个特点，来减少扫描的数据页。
对SQL改写如下：
SELECT t1.imei, t1.c1
FROM t1
JOIN (SELECT imei, MAX(statistic_time) statistic_time
FROM t1
GROUP BY imei) t11
ON t1.imei = t11.imei
AND t1.statistic_time = t11.statistic_time;
先对t1表的imei分组取最值，这个结果集是非常小的，然后再与大表t1表关联。
执行计划如下：
EXPLAIN: -> Nested loop inner join (cost=6.14 rows=3) (actual time=0.300..0.313 rows=3 loops=1)
-> Filter: (t11.statistic_time is not null) (cost=4.15..2.84 rows=3) (actual time=0.280..0.281 rows=3 loops=1)
-> Table scan on t11 (cost=5.65..7.34 rows=3) (actual time=0.274..0.275 rows=3 loops=1)
-> Materialize (cost=4.80..4.80 rows=3) (actual time=0.270..0.270 rows=3 loops=1)
-> Covering index skip scan for grouping on t1 using PRIMARY (cost=4.50 rows=3) (actual time=0.118..0.226 rows=3 loops=1)
-> Single-row index lookup on t1 using PRIMARY (imei=t11.imei, statistic_time=t11.statistic_time) (cost=1.03 rows=1) (actual time=0.009..0.009 rows=1 loops=3)
1 row in set (0.00 sec)
从执行计划看出，修改后的SQL，分组取最值时用到了索引跳跃扫描(index skip scan)，结果集只有3行，这个小结果集物化的代价很小，而与大表关联时，大表循环执行3次(loops=3)，又使用了主键索引，整个SQL执行完毕耗时0.313毫秒，相较之前的1327毫秒，性能提升超过4000倍。
SQL2 执行分析与优化
SQL2 执行计划分析
greatsql> explain analyze
-> SELECT *
-> FROM t1 a
-> WHERE to_char(statistic_time, 'yyyymmdd') =
-> (SELECT MAX(to_char(statistic_time, 'yyyymmdd'))
-> FROM t1 b
-> WHERE a.c2 = b.c2
-> )
-> AND a.imei = 'a'\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (to_char(a.statistic_time,'yyyymmdd') = (select #2)) (cost=15037.65 rows=149898) (actual time=33379.186..38313.772 rows=13601 loops=1)
-> Index lookup on a using PRIMARY (imei='a') (cost=15037.65 rows=149898) (actual time=0.221..82.639 rows=100000 loops=1)
-> Select #2 (subquery in condition; dependent)
-> Aggregate: max(to_char(b.statistic_time,'yyyymmdd')) (cost=77.27 rows=1) (actual time=0.381..0.381 rows=1 loops=100000)
-> Covering index lookup on b using idx_c2 (c2=a.c2) (cost=46.74 rows=305) (actual time=0.047..0.187 rows=301 loops=100000)
1 row in set, 1 warning (38.33 sec)
从执行计划可以看出，条件imei='a'返回的结果集比较大，这里测试数据结果集是10万，占全表的1/3，子查询是根据c2列取statistic_time的最值，c2列的选择性不是太好，每次扫描，返回301行数据，耗时0.187毫秒，循环执行10万次，SQL总耗时38.33秒。
如何才能提升这个SQL的效率呢？
还是那句优化宗旨，要减少I/O消耗，就是避免循环执行子查询那么多次 ，怎么办呢，此处借助rank()窗口函数可以达到这一目的，窗口函数的好处****就是只需对表做一次扫描，就能得到想要的分组排序名次，再通过名次过滤取第一名，就能得到最值子查询想要的效果，rank()与row_number()的区别在于同一名次可以并列，而此处c2,statistics_time两列组合不具有唯一性，所以此处应使用rank()窗口函数才能与最值子查询等价。
SQL改写如下：
SELECT t11.imei, t11.statistic_time, t11.c1, t11.c2
FROM (SELECT t1.*,
rank() OVER(PARTITION BY c2 ORDER BY to_char(statistic_time, 'yyyymmdd') desc) rn
FROM t1
WHERE imei='a'
) t11
WHERE t11.rn = 1
改写后SQL执行计划如下：
EXPLAIN: -> Index lookup on t11 using <auto_key0> (rn=1) (cost=0.35..3.50 rows=10) (actual time=374.428..378.893 rows=13601 loops=1)
-> Materialize (cost=0.00..0.00 rows=0) (actual time=374.409..374.409 rows=100000 loops=1)
-> Window aggregate: rank() OVER (PARTITION BY t1.c2 ORDER BY to_char(t1.statistic_time,'yyyymmdd') desc ) (actual time=127.944..221.134 rows=100000 loops=1)
-> Sort: t1.c2, to_char(t1.statistic_time,'yyyymmdd') DESC (cost=15046.54 rows=149348) (actual time=127.896..137.878 rows=100000 loops=1)
-> Index lookup on t1 using PRIMARY (imei='a') (cost=15046.54 rows=149348) (actual time=0.159..46.607 rows=100000 loops=1)
1 row in set (0.40 sec)
从执行计划可以看出，修改后的SQL只需对t1表按条件扫描一次，再做排序，聚合，物化，SQL整体耗时0.40秒，与修改前的38.33秒相比，性能提升近100倍。
总结
SQL优化不要拘泥于规则，不需要死记是哪种写法快，重要的是懂SQL执行计划，明白SQL主要耗时在什么地方，以及使用何种技巧来降低I/O消耗，总之是原理与技巧不可或缺。
Enjoy GreatSQL :)
关于 GreatSQL
GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。
相关链接： GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区：
社区有奖建议反馈： https://greatsql.cn/thread-54-1-1.html
社区博客有奖征稿详情： https://greatsql.cn/thread-100-1-1.html
（对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~）
技术交流群：
微信&QQ群：
QQ群：533341697
微信群：添加GreatSQL社区助手（微信号：wanlidbc ）好友，待社区助手拉您进群。
