SQL优化 -- 利用Rownum条件Count Stop特性优化SQL的一个案例

原创人大金仓三线支持团队 2023-08-21

9513

Rownum 条件用于限制返回结果集数据量。对于两表连接的场景，如果没有rownum限制，则需要对两张表进行完整地关联，返回所有的行。是否有Rownum，对于mergejoin影响不大（hashjoin则视hash table的大小而定），而对于采用nestloop的场景影响是非常大的（没有必要等待所有连接结果返回后再根据取rownum取部分数据）。如果rownum能与 nestloop 或索引相结合，可以有效地提升SQL效率。对于同时包含order by 的场景，可以以含有排序列的表为驱动表，先进行排序，再与其他表进行关联。本例场景是客户现场一个POC案例，涉及多张表关联，排序列也涉及多表的列。语句看起来有点复杂，但实际是同一个优化思路。

PS：由于用户信息保密需要，本例只是模拟现场的语句逻辑及数据量，构造了相应的案例。同时，简化案例也为了让语句更容易读懂。

一、构造测试案例数据

create table t1(t1id1 integer, t1id2 integer, t1name text);
create table t2(t2id1 integer, t2id2 integer, t2name text);
create table t3(t3id1 integer, t3id2 integer, t3name text);
insert into t1 select id,mod(id,500),repeat('a',1000) from generate_series(1,10000) id;

begin
  for i in 1..9 loop
    insert into t1 select * from t1;
  end loop;
end;

insert into t2 select id,mod(id,1000),repeat('a',1000) from generate_series(1,10000) id;
insert into t3 select id,mod(id,100),repeat('a',1000) from generate_series(1,10000) id;
create index idx_t1_t1id1 on t1(t1id1);

其中 t1 表数据量较大。

二、优化前SQL执行计划

select * from (
select t1id2,t2id2,t3id2 from t1,t2,t3
where t1id1=t2id1 and t2id1=t3id1 order by t2id2 desc nulls last,t3id2 asc nulls first 
) limit 100

SQL 涉及3张表的关联，排序的列涉及到t2 与 t3 两张表。

优化前的执行计划如下：可以看到SQL先进行3表关联，最后再排序取100条。

                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1602047.36..1602048.61 rows=100 width=12) (actual time=11369.060..11369.088 rows=100 loops=1)
   ->  Sort  (cost=1602047.36..1614847.37 rows=5120003 width=12) (actual time=11369.059..11369.075 rows=100 loops=1)
         Sort Key: t2.t2id2 DESC NULLS LAST, t3.t3id2 NULLS FIRST
         Sort Method: top-N heapsort  Memory: 34kB
         ->  Hash Join  (cost=3445.50..856474.57 rows=5120003 width=12) (actual time=14.213..10325.444 rows=5120000 loops=1)
               Hash Cond: (t1.t1id1 = t2.t2id1)
               ->  Seq Scan on t1  (cost=0.00..782629.03 rows=5120003 width=8) (actual time=0.010..7558.438 rows=5120000 loops=1)
               ->  Hash  (cost=3320.50..3320.50 rows=10000 width=16) (actual time=14.186..14.192 rows=10000 loops=1)
                     Buckets: 16384  Batches: 1  Memory Usage: 597kB
                     ->  Hash Join  (cost=1654.00..3320.50 rows=10000 width=16) (actual time=5.182..11.919 rows=10000 loops=1)
                           Hash Cond: (t2.t2id1 = t3.t3id1)
                           ->  Seq Scan on t2  (cost=0.00..1529.00 rows=10000 width=8) (actual time=0.004..2.726 rows=10000 loops=1)
                           ->  Hash  (cost=1529.00..1529.00 rows=10000 width=8) (actual time=5.159..5.160 rows=10000 loops=1)
                                 Buckets: 16384  Batches: 1  Memory Usage: 519kB
                                 ->  Seq Scan on t3  (cost=0.00..1529.00 rows=10000 width=8) (actual time=0.005..3.184 rows=10000 loops=1)
 Planning Time: 0.494 ms
 Execution Time: 11369.173 ms

从执行计划可以看到，实际语句最耗时的部分是t1表的访问，优化的方向也集中在了如何避免t1表的全表扫描上。由于语句只需要返回100条记录，自然就会考虑到用索引。由于排序列不涉及t1表，因此，可以先对t2, t3 表连接结果进行排序，再以排序的结果集为驱动表，与t1进行nestloop，这样就可以避免对t1 表的全表访问。可问题在于，本例的排序列涉及到两个表，靠优化器还无法这么智能地按以上思路执行，只能通过人为改写实现。

以下是同样SQL在oracle环境的执行情况

三、优化后的SQL及执行计划

基于思路，我们通过CTE语法进行改写，改写后的SQL如下：

with tmp_query1 as (select t2id1,t2id2,t3id2 from t2,t3 where t2id1=t3id1 order by t2id2 desc nulls last,t3id2 asc nulls first)
select t1id2,t2id2,t3id2 from t1, tmp_query1 where t1id1=t2id1 order by t2id2 desc nulls last,t3id2 asc nulls first limit 100

tmp_query1 内部先对 t2, t3 的连接结果进行排序，然后再以 tmp_query1 为驱动表与 t1 进行nestloop 连接。修改后的 SQL 有两个order by（优化器实际会忽略外层的order by , 没必要），目的是为了保证语句结果的一致性，实际并不会执行两次排序。

执行计划如下：

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3985.32..4132.93 rows=100 width=12) (actual time=11.379..11.464 rows=100 loops=1)
   ->  Nested Loop  (cost=3985.32..7561910.61 rows=5120003 width=12) (actual time=11.378..11.458 rows=100 loops=1)
         ->  Sort  (cost=3984.89..4009.89 rows=10000 width=12) (actual time=11.349..11.351 rows=1 loops=1)
               Sort Key: t2.t2id2 DESC NULLS LAST, t3.t3id2 NULLS FIRST
               Sort Method: quicksort  Memory: 853kB
               ->  Hash Join  (cost=1654.00..3320.50 rows=10000 width=12) (actual time=4.995..8.964 rows=10000 loops=1)
                     Hash Cond: (t2.t2id1 = t3.t3id1)
                     ->  Seq Scan on t2  (cost=0.00..1529.00 rows=10000 width=8) (actual time=0.011..1.718 rows=10000 loops=1)
                     ->  Hash  (cost=1529.00..1529.00 rows=10000 width=8) (actual time=4.960..4.961 rows=10000 loops=1)
                           Buckets: 16384  Batches: 1  Memory Usage: 519kB
                           ->  Seq Scan on t3  (cost=0.00..1529.00 rows=10000 width=8) (actual time=0.004..2.692 rows=10000 loops=1)
         ->  Index Scan using idx_t1_t1id1 on t1  (cost=0.43..750.65 rows=513 width=8) (actual time=0.023..0.090 rows=100 loops=1)
               Index Cond: (t1id1 = t2.t2id1)
 Planning Time: 0.168 ms
 Execution Time: 11.498 ms