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 ）好友，待社区助手拉您进群。