当前位置：首页 > news >正文

GreatSQL 优化技巧：最值子查询与窗口函数相互转换

news 2025/9/26 9:55:04

GreatSQL 优化技巧：最值子查询与窗口函数相互转换

导语

近期 SQL 优化过程中遇到两个案例，一个是将最值子查询改写为窗口函数，另一个是将窗口函数改写为最值子查询，觉得很有意思，特此记录分享一下。

SQL案例

两个 SQL 语句

SQL1：

SELECT imei, c1FROM (SELECT imei,c1,row_number() OVER(PARTITION BY imei ORDER BY statistic_time DESC) AS rnFROM t1)WHERE rn = 1;

SQL2：

 SELECT *FROM t1 aWHERE to_char(statistic_time, 'yyyymmdd') =(SELECT MAX(to_char(statistic_time, 'yyyymmdd'))FROM t1 bWHERE 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 ISBEGINFOR i IN 1..100000 LOOPINSERT 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 LOOPINSERT 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 LOOPINSERT 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.c1FROM t1JOIN (SELECT imei, MAX(statistic_time) statistic_timeFROM t1GROUP BY imei) t11ON t1.imei = t11.imeiAND 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) rnFROM 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倍。