GreatSQL 优化技巧：最值子查询与窗口函数相互转换
<h1 id="greatsql-优化技巧最值子查询与窗口函数相互转换">GreatSQL 优化技巧：最值子查询与窗口函数相互转换</h1><h2 id="导语">导语</h2>
<p>近期 SQL 优化过程中遇到两个案例，一个是将最值子查询改写为窗口函数，另一个是将窗口函数改写为最值子查询，觉得很有意思，特此记录分享一下。</p>
<h2 id="sql案例">SQL案例</h2>
<h3 id="两个-sql-语句">两个 SQL 语句</h3>
<p>SQL1：</p>
<pre><code class="language-SQL">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;
</code></pre>
<p>SQL2：</p>
<pre><code class="language-SQL"> 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';
</code></pre>
<p>这两个语句的真实场景并不是在一个系统中遇到的，这里只是用一张测试表来说明这两个 SQL 的问题。</p>
<p>测试表与测试数据如下：</p>
<pre><code class="language-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;
</code></pre>
<p>数据统计分布如下：</p>
<pre><code class="language-SQL">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)
</code></pre>
<p>t1表插入30万行数据，列imei的唯一值3个,列c1唯一值13万左右，列c2唯一值1000左右。</p>
<p>测试数据只是模拟了真实的业务数据分布特点,真实业务场景数据量是千万级别的。</p>
<h3 id="sql1-执行分析与优化">SQL1 执行分析与优化</h3>
<p>SQL1 执行计划：</p>
<pre><code class="language-SQL">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)
</code></pre>
<p>从执行计划可以看出，对t1表的索引idx_c1做了覆盖扫描，又按照 t1.imei, t1.statistic_time做排序，再做窗口聚合，物化处理，物化处理后的结果集是30万，而最终结果集只有3行。测试数据量只有30万，耗时1327ms，如果数据量放大100倍呢，这个代价就非常高了。</p>
<p>如何才能提升这个SQL的效率呢？</p>
<p>结果集只有3行，却扫描了整个索引，<strong>SQL 优化的核心思想，是减少I/O消耗，那就是要扫描的数据尽量少</strong>。分组排序列imei,statistic_time 是有主键联合索引的，这里窗口函数的目的就是取得前导列imei对应的statistic_time最大值的那条记录，而通过索引取最值是不用去扫描整个索引的。所以我们就利用索引有序这个特点，来减少扫描的数据页。</p>
<p>对SQL改写如下：</p>
<pre><code class="language-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;
</code></pre>
<p>先对t1表的imei分组取最值，这个结果集是非常小的，然后再与大表t1表关联。</p>
<p>执行计划如下：</p>
<pre><code class="language-SQL">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)
</code></pre>
<p>从执行计划看出，修改后的SQL，分组取最值时用到了索引跳跃扫描(index skip scan)，结果集只有3行，这个小结果集物化的代价很小，而与大表关联时，大表循环执行3次(loops=3)，又使用了主键索引，<strong>整个SQL执行完毕耗时0.313毫秒，相较之前的1327毫秒，性能提升超过4000倍</strong>。</p>
<h3 id="sql2-执行分析与优化">SQL2 执行分析与优化</h3>
<p>SQL2 执行计划分析</p>
<pre><code class="language-SQL">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)
</code></pre>
<p>从执行计划可以看出，条件imei='a'返回的结果集比较大，这里测试数据结果集是10万，占全表的1/3，子查询是根据c2列取statistic_time的最值，c2列的选择性不是太好，每次扫描，返回301行数据，耗时0.187毫秒，循环执行10万次，SQL总耗时38.33秒。</p>
<p>如何才能提升这个SQL的效率呢？</p>
<p>还是那句优化宗旨，<strong>要减少I/O消耗，就是避免循环执行子查询那么多次</strong>，怎么办呢，此处借助rank()窗口函数可以达到这一目的，<strong>窗口函数的好处****就是只需对表做一次扫描</strong>，就能得到想要的分组排序名次，再通过名次过滤取第一名，就能得到最值子查询想要的效果，rank()与row_number()的区别在于同一名次可以并列，而此处c2,statistics_time两列组合不具有唯一性，所以此处应使用rank()窗口函数才能与最值子查询等价。</p>
<p>SQL改写如下：</p>
<pre><code class="language-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
</code></pre>
<p>改写后SQL执行计划如下：</p>
<pre><code class="language-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)
</code></pre>
<p>从执行计划可以看出，<strong>修改后的SQL只需对t1表按条件扫描一次，再做排序，聚合，物化，SQL整体耗时0.40秒，与修改前的38.33秒相比，性能提升近100倍</strong>。</p>
<h2 id="总结">总结</h2>
<p>SQL优化不要拘泥于规则，不需要死记是哪种写法快，重要的是懂SQL执行计划，明白SQL主要耗时在什么地方，以及使用何种技巧来降低I/O消耗，总之是原理与技巧不可或缺。</p>
<hr>
<p>Enjoy GreatSQL 😃</p>
<h2 id="关于-greatsql">关于 GreatSQL</h2>
<p>GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。</p>
<p>相关链接： GreatSQL社区 Gitee GitHub Bilibili</p>
<h2 id="greatsql社区">GreatSQL社区：</h2>
<blockquote>
<p>社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html</p>
</blockquote>
<p><img src="https://img2024.cnblogs.com/other/2630741/202509/2630741-20250926095318934-352795881.png"></p>
<h2 id="技术交流群">技术交流群：</h2>
<blockquote>
<p>微信：扫码添加<code>GreatSQL社区助手</code>微信好友，发送验证信息<code>加群</code>。</p>
</blockquote>
<p><img src="https://img2024.cnblogs.com/other/2630741/202509/2630741-20250926095319311-568117314.png"></p><br><br>
来源：https://www.cnblogs.com/greatsql/p/19112940
頁:
[1]