登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 安全 ›GreatSQL 优化技巧：最值子查询与窗口函数相互转换 ...

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

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

导语

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

两个 SQL 语句

SQL1：
  1. SELECT imei, c1
  2.   FROM (SELECT imei,
  3.                c1,
  4.                row_number() OVER(PARTITION BY imei ORDER BY statistic_time DESC) AS rn
  5.           FROM t1)
  6. WHERE rn = 1;
复制代码
SQL2：
  1. SELECT *
  2.    FROM t1 a
  3.   WHERE to_char(statistic_time, 'yyyymmdd') =
  4.         (SELECT MAX(to_char(statistic_time, 'yyyymmdd'))
  5.            FROM t1 b
  6.           WHERE a.c2 = b.c2
  7.                 )
  8.     AND a.imei = 'a';
复制代码
这两个语句的真实场景并不是在一个系统中遇到的，这里只是用一张测试表来说明这两个 SQL 的问题。
测试表与测试数据如下：
  1. CREATE TABLE t1(
  2. imei VARCHAR(100),
  3. statistic_time datetime,
  4. c1 INT,
  5. c2 INT,
  6. PRIMARY KEY(imei,statistic_time)
  7. );
  9. SET sql_mode=oracle;
  10. DELIMITER //
  11. CREATE OR REPLACE PROCEDURE p1 IS
  12. BEGIN
  13.    FOR i IN 1..100000 LOOP
  14.       INSERT INTO t1 VALUES('a',TO_DATE('2025-01-01','yyyy-mm-dd')+ (1/24/60/60)*i, TRUNC(RAND()*200000),TRUNC(RAND()*1000));
  15.    END LOOP;
  16.     FOR i IN 1..100000 LOOP
  17.       INSERT INTO t1 VALUES('b',TO_DATE('2025-01-01','yyyy-mm-dd')+ (1/24/60/60)*i, TRUNC(RAND()*200000),RAND()*1000);
  18.    END LOOP;
  19.     FOR i IN 1..100000 LOOP
  20.       INSERT INTO t1 VALUES('c',TO_DATE('2025-01-01','yyyy-mm-dd')+ (1/24/60/60)*i, TRUNC(RAND()*20000),RAND()*1000);
  21.    END LOOP;
  22. END ;
  23. //
  24. DELIMITER ;
  26. CALL p1;
复制代码
数据统计分布如下：
  1. greatsql> SHOW INDEX FROM T1;
  2. +-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
  3. | Table | Non_unique | Key_name | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
  4. +-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
  5. | t1    |          0 | PRIMARY  |            1 | imei           | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
  6. | t1    |          0 | PRIMARY  |            2 | statistic_time | A         |      298697 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
  7. | t1    |          1 | idx_c1   |            1 | c1             | A         |      132721 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
  8. | t1    |          1 | idx_c2   |            1 | c2             | A         |        1015 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
  9. +-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
  10. 4 rows in set (0.00 sec)
复制代码
t1表插入30万行数据，列imei的唯一值3个,列c1唯一值13万左右，列c2唯一值1000左右。
测试数据只是模拟了真实的业务数据分布特点,真实业务场景数据量是千万级别的。
SQL1 执行分析与优化

SQL1 执行计划：
  1. greatsql> EXPLAIN ANALYZE
  2.     -> SELECT imei, c1
  3.     ->   FROM (SELECT imei,
  4.     ->                c1,
  5.     ->                row_number() OVER(PARTITION BY imei ORDER BY statistic_time DESC) AS rn
  6.     ->           FROM t1)
  7.     ->  WHERE rn = 1\G
  8. *************************** 1. row ***************************
  9. EXPLAIN: -> Index lookup on alias_temp_101454818022785039 using  (rn=1)  (cost=0.35..3.50 rows=10) (actual time=1327.190..1327.192 rows=3 loops=1)
  10.     -> Materialize  (cost=0.00..0.00 rows=0) (actual time=1327.172..1327.172 rows=300000 loops=1)
  11.         -> 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)
  12.             -> Sort: t1.imei, t1.statistic_time DESC  (cost=31248.70 rows=298697) (actual time=345.091..374.978 rows=300000 loops=1)
  13.                 -> Index scan on t1 using idx_c1  (cost=31248.70 rows=298697) (actual time=0.133..96.587 rows=300000 loops=1)
  15. 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改写如下：
  1. SELECT t1.imei, t1.c1
  2.   FROM t1
  3.   JOIN (SELECT imei, MAX(statistic_time) statistic_time
  4.           FROM t1
  5.          GROUP BY imei) t11
  6.     ON t1.imei = t11.imei
  7.    AND t1.statistic_time = t11.statistic_time;
复制代码
先对t1表的imei分组取最值，这个结果集是非常小的，然后再与大表t1表关联。
执行计划如下：
  1. EXPLAIN: -> Nested loop inner join  (cost=6.14 rows=3) (actual time=0.300..0.313 rows=3 loops=1)
  2.     -> Filter: (t11.statistic_time is not null)  (cost=4.15..2.84 rows=3) (actual time=0.280..0.281 rows=3 loops=1)
  3.         -> Table scan on t11  (cost=5.65..7.34 rows=3) (actual time=0.274..0.275 rows=3 loops=1)
  4.             -> Materialize  (cost=4.80..4.80 rows=3) (actual time=0.270..0.270 rows=3 loops=1)
  5.                 -> 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)
  6.     -> 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)
  8. 1 row in set (0.00 sec)
复制代码
从执行计划看出，修改后的SQL，分组取最值时用到了索引跳跃扫描(index skip scan)，结果集只有3行，这个小结果集物化的代价很小，而与大表关联时，大表循环执行3次(loops=3)，又使用了主键索引，整个SQL执行完毕耗时0.313毫秒，相较之前的1327毫秒，性能提升超过4000倍。
SQL2 执行分析与优化

SQL2 执行计划分析
  1. greatsql> explain analyze
  2.     ->  SELECT *
  3.     ->    FROM t1 a
  4.     ->   WHERE to_char(statistic_time, 'yyyymmdd') =
  5.     ->         (SELECT MAX(to_char(statistic_time, 'yyyymmdd'))
  6.     ->            FROM t1 b
  7.     ->           WHERE a.c2 = b.c2
  8.     ->                 )
  9.     ->     AND a.imei = 'a'\G
  10. *************************** 1. row ***************************
  11. 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)
  12.     -> Index lookup on a using PRIMARY (imei='a')  (cost=15037.65 rows=149898) (actual time=0.221..82.639 rows=100000 loops=1)
  13.     -> Select #2 (subquery in condition; dependent)
  14.         -> Aggregate: max(to_char(b.statistic_time,'yyyymmdd'))  (cost=77.27 rows=1) (actual time=0.381..0.381 rows=1 loops=100000)
  15.             -> 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)
  17. 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改写如下：
  1. SELECT t11.imei, t11.statistic_time, t11.c1, t11.c2
  2.    FROM (SELECT t1.*,
  3.                 rank() OVER(PARTITION BY c2 ORDER BY to_char(statistic_time, 'yyyymmdd') desc) rn
  4.            FROM t1
  5.            WHERE imei='a'
  6.           ) t11
  7.   WHERE t11.rn = 1
复制代码
改写后SQL执行计划如下：
  1. EXPLAIN: -> Index lookup on t11 using  (rn=1)  (cost=0.35..3.50 rows=10) (actual time=374.428..378.893 rows=13601 loops=1)
  2.     -> Materialize  (cost=0.00..0.00 rows=0) (actual time=374.409..374.409 rows=100000 loops=1)
  3.         -> 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)
  4.             -> 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)
  5.                 -> Index lookup on t1 using PRIMARY (imei='a')  (cost=15046.54 rows=149348) (actual time=0.159..46.607 rows=100000 loops=1)
  7. 1 row in set (0.40 sec)
复制代码
从执行计划可以看出，修改后的SQL只需对t1表按条件扫描一次，再做排序，聚合，物化，SQL整体耗时0.40秒，与修改前的38.33秒相比，性能提升近100倍。
总结

SQL优化不要拘泥于规则，不需要死记是哪种写法快，重要的是懂SQL执行计划，明白SQL主要耗时在什么地方，以及使用何种技巧来降低I/O消耗，总之是原理与技巧不可或缺。

Enjoy GreatSQL
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

使用道具 举报
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
事值
6 天前

0

粉丝关注

14

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫