5万5千倍性能提升,这种SQL优化技能你一定要掌握！
digoal约 18 分钟文中参考文档在github需点击阅读原文打开, 同时推荐2个学习环境:
1、懒人Docker镜像, 已打包200+插件:《最好的PostgreSQL学习镜像》
2、有web浏览器就能用的云起实验室: 《免费体验PolarDB开源数据库》
3、PolarDB开源数据库内核、最佳实践等学习图谱: https://www.aliyun.com/database/openpolardb/activity
5万5千倍性能提升,你需要掌握这个能力!
有一个这样的场景，一张小表A，里面存储了一些ID，大约几百个。
（比如说巡逻车辆ID，环卫车辆的ID，公交车，微公交的ID）。
另外有一张日志表B，每条记录中的ID是来自前面那张小表的，但不是每个ID都出现在这张日志表中，比如说一天可能只有几十个ID会出现在这个日志表的当天的数据中。
（比如车辆的行车轨迹数据，每秒上报轨迹，数据量就非常庞大）。
那么我怎么快速的找出今天没有出现的ID呢。
（哪些巡逻车辆没有出现在这个片区，是不是偷懒了，哪些环卫车辆没有出行，哪些公交或微公交没有出行）
select id from A where id not in (select id from B where time between and ); select (300 ids) not in (select ids from 300万)
这个QUERY会很慢，有什么优化方法呢。
当然，你还可以让车辆签到的方式来解决这个问题，但是总有未签到的，或者没有这种设计的时候，那么怎么解决呢
优化方法1
其实方法也很精妙，和我之前做的两个CASE很相似。
《时序数据合并场景加速分析和实现 - 复合索引，窗口分组查询加速，变态递归加速》
《distinct xx和count(distinct xx)的变态递归优化方法 - 索引收敛(skip scan)扫描》
插入例子
传感器例子
假设传感器数据不断的上报，用户需要查询当前最新的，每个传感器上报的值。
创建测试表如下，
create unlogged table sort_test(
id serial8 primary key, -- 主键
c2 int, -- 传感器ID
c3 int -- 传感器值
); 写入1000万传感器测试数据
postgres=# insert into sort_test (c2,c3) select random()*100000, random()*100 from generate_series(1,10000000);
INSERT 0 10000000
查询语句如下
postgres=# explain (analyze,verbose,timing,costs,buffers) select id,c2,c3 from (select id,c2,c3,row_number() over(partition by c2 order by id desc) rn from sort_test) t where rn=1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on t (cost=10001512045.83..10001837045.83 rows=50000 width=16) (actual time=23865.363..44033.984 rows=100001 loops=1)
Output: t.id, t.c2, t.c3
Filter: (t.rn = 1)
Rows Removed by Filter: 9899999
Buffers: shared hit=54055, temp read=93801 written=93801
-> WindowAgg (cost=10001512045.83..10001712045.83 rows=10000000 width=24) (actual time=23865.351..41708.460 rows=10000000 loops=1)
Output: sort_test.id, sort_test.c2, sort_test.c3, row_number() OVER (?)
Buffers: shared hit=54055, temp read=93801 written=93801
-> Sort (cost=10001512045.83..10001537045.83 rows=10000000 width=16) (actual time=23865.335..31540.089 rows=10000000 loops=1)
Output: sort_test.id, sort_test.c2, sort_test.c3
Sort Key: sort_test.c2, sort_test.id DESC
Sort Method: external merge Disk: 254208kB
Buffers: shared hit=54055, temp read=93801 written=93801
-> Seq Scan on public.sort_test (cost=10000000000.00..10000154055.00 rows=10000000 width=16) (actual time=0.021..1829.135 rows=10000000 loops=1)
Output: sort_test.id, sort_test.c2, sort_test.c3
Buffers: shared hit=54055
Planning time: 0.194 ms
Execution time: 44110.560 ms
(18 rows)
优化手段，新增复合索引，避免SORT，注意，id需要desc
postgres=# create index sort_test_1 on sort_test(c2,id desc);
CREATE INDEX
优化后的SQL性能
postgres=# explain (analyze,verbose,timing,costs,buffers) select id,c2,c3 from (select id,c2,c3,row_number() over(partition by c2 order by id desc) rn from sort_test) t where rn=1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on t (cost=0.43..542565.80 rows=50000 width=16) (actual time=0.048..33844.843 rows=100001 loops=1)
Output: t.id, t.c2, t.c3
Filter: (t.rn = 1)
Rows Removed by Filter: 9899999
Buffers: shared hit=10029020 read=1
-> WindowAgg (cost=0.43..417564.59 rows=10000097 width=24) (actual time=0.042..30490.662 rows=10000000 loops=1)
Output: sort_test.id, sort_test.c2, sort_test.c3, row_number() OVER (?)
Buffers: shared hit=10029020 read=1
-> Index Scan using sort_test_1 on public.sort_test (cost=0.43..242562.89 rows=10000097 width=16) (actual time=0.030..18347.482 rows=10000000 loops=1)
Output: sort_test.id, sort_test.c2, sort_test.c3
Buffers: shared hit=10029020 read=1
Planning time: 0.216 ms
Execution time: 33865.321 ms
(13 rows)
如果被取出的数据需要后续的处理，可以使用游标，分批获取，因为不需要显示sort，所以分批获取速度很快，从而加快整个的处理速度。
\timing
begin;
declare c1 cursor for select id,c2,c3 from (select id,c2,c3,row_number() over(partition by c2 order by id desc) rn from sort_test) t where rn=1;
postgres=# fetch 100 from c1;
id | c2 | c3
---------+----+-----
9962439 | 0 | 93
9711199 | 1 | 52
9987709 | 2 | 65
9995611 | 3 | 34
9998766 | 4 | 12
9926693 | 5 | 81
....
9905064 | 98 | 44
9991592 | 99 | 99
(100 rows)
Time: 31.408 ms -- 很快就返回
优化前，需要显示SORT，所以使用游标并不能加速，拿到第一条记录是在SORT后的。
drop index sort_test_1;begin;
declare c1 cursor for select id,c2,c3 from (select id,c2,c3,row_number() over(partition by c2 order by id desc) rn from sort_test) t where rn=1;
postgres=# fetch 100 from c1;
....
Time: 22524.783 ms -- sort结束后才开始返回，很慢
增量合并数据同步例子
类似Oracle的物化视图，apply时，对于同一条记录的update并不需要每次update的中间过程都需要执行，只需要执行最后一次的。
因此，也可以利用类似的操作手段，分组取最后一条，
create extension hstore;create unlogged table sort_test1(
id serial8 primary key, -- 主键
c2 int, -- 目标表PK
c3 text, -- insert or update or delete
c4 hstore -- row
);
create index idx_sort_test1_1 on sort_test1(c2,id desc);
select c2,c3,c4 from (select c2,c3,c4,row_number() over(partition by c2 order by id desc) rn from sort_test1) t where rn=1;
postgres=# explain select c2,c3,c4 from (select c2,c3,c4,row_number() over(partition by c2 order by id desc) rn from sort_test1) t where rn=1;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Subquery Scan on t (cost=0.15..46.25 rows=4 width=68)
Filter: (t.rn = 1)
-> WindowAgg (cost=0.15..36.50 rows=780 width=84)
-> Index Scan using idx_sort_test1_1 on sort_test1 (cost=0.15..22.85 rows=780 width=76)
(4 rows)
稀疏列的变态优化方法
我们看到前面的优化手段，其实只是消除了SORT，并没有消除扫描的BLOCK数。
如果分组很少时，即稀疏列，还有一种更变态的优化方法，递归查询。
优化方法与这篇文档类似,
《distinct xx和count(distinct xx)的变态递归优化方法》
例子
create type r as (c2 int, c3 int);postgres=# explain (analyze,verbose,timing,costs,buffers) with recursive skip as (
(
select (c2,c3)::r as r from sort_test where id in (select id from sort_test where c2 is not null order by c2,id desc limit 1)
)
union all
(
select (
select (c2,c3)::r as r from sort_test where id in (select id from sort_test t where t.c2>(s.r).c2 and t.c2 is not null order by c2,id desc limit 1)
) from skip s where (s.r).c2 is not null
) -- 这里的where (s.r).c2 is not null 一定要加, 否则就死循环了.
)
select (t.r).c2, (t.r).c3 from skip t where t.* is not null;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CTE Scan on skip t (cost=302.97..304.99 rows=100 width=8) (actual time=0.077..4184.770 rows=100001 loops=1)
Output: (t.r).c2, (t.r).c3
Filter: (t.* IS NOT NULL)
Rows Removed by Filter: 1
Buffers: shared hit=800947, temp written=476
CTE skip
-> Recursive Union (cost=0.91..302.97 rows=101 width=32) (actual time=0.066..3970.580 rows=100002 loops=1)
Buffers: shared hit=800947
-> Nested Loop (cost=0.91..2.95 rows=1 width=32) (actual time=0.064..0.066 rows=1 loops=1)
Output: ROW(sort_test_1.c2, sort_test_1.c3)::r
Buffers: shared hit=8
-> HashAggregate (cost=0.47..0.48 rows=1 width=8) (actual time=0.044..0.044 rows=1 loops=1)
Output: sort_test_2.id
Group Key: sort_test_2.id
Buffers: shared hit=4
-> Limit (cost=0.43..0.46 rows=1 width=12) (actual time=0.036..0.036 rows=1 loops=1)
Output: sort_test_2.id, sort_test_2.c2
Buffers: shared hit=4
-> Index Only Scan using sort_test_1 on public.sort_test sort_test_2 (cost=0.43..267561.43 rows=10000000 width=12) (actual time=0.034..0.034 rows=1 loops=1)
Output: sort_test_2.id, sort_test_2.c2
Index Cond: (sort_test_2.c2 IS NOT NULL)
Heap Fetches: 1
Buffers: shared hit=4
-> Index Scan using sort_test_pkey on public.sort_test sort_test_1 (cost=0.43..2.45 rows=1 width=16) (actual time=0.011..0.012 rows=1 loops=1)
Output: sort_test_1.id, sort_test_1.c2, sort_test_1.c3
Index Cond: (sort_test_1.id = sort_test_2.id)
Buffers: shared hit=4
-> WorkTable Scan on skip s (cost=0.00..29.80 rows=10 width=32) (actual time=0.037..0.038 rows=1 loops=100002)
Output: (SubPlan 1)
Filter: ((s.r).c2 IS NOT NULL)
Rows Removed by Filter: 0
Buffers: shared hit=800939
SubPlan 1
-> Nested Loop (cost=0.92..2.96 rows=1 width=32) (actual time=0.034..0.035 rows=1 loops=100001)
Output: ROW(sort_test.c2, sort_test.c3)::r
Buffers: shared hit=800939
-> HashAggregate (cost=0.49..0.50 rows=1 width=8) (actual time=0.023..0.023 rows=1 loops=100001)
Output: t_1.id
Group Key: t_1.id
Buffers: shared hit=400401
-> Limit (cost=0.43..0.48 rows=1 width=12) (actual time=0.021..0.021 rows=1 loops=100001)
Output: t_1.id, t_1.c2
Buffers: shared hit=400401
-> Index Only Scan using sort_test_1 on public.sort_test t_1 (cost=0.43..133557.76 rows=3333333 width=12) (actual time=0.019..0.019 rows=1 loops=100001)
Output: t_1.id, t_1.c2
Index Cond: ((t_1.c2 > (s.r).c2) AND (t_1.c2 IS NOT NULL))
Heap Fetches: 100000
Buffers: shared hit=400401
-> Index Scan using sort_test_pkey on public.sort_test (cost=0.43..2.45 rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=100000)
Output: sort_test.id, sort_test.c2, sort_test.c3
Index Cond: (sort_test.id = t_1.id)
Buffers: shared hit=400538
Planning time: 0.970 ms
Execution time: 4209.026 ms
(54 rows)
依旧支持快速的FETCH
postgres=# begin;
BEGIN
Time: 0.079 ms
postgres=# declare cur cursor for with recursive skip as (
(
select (c2,c3)::r as r from sort_test where id in (select id from sort_test where c2 is not null order by c2,id desc limit 1)
)
union all
(
select (
select (c2,c3)::r as r from sort_test where id in (select id from sort_test t where t.c2>(s.r).c2 and t.c2 is not null order by c2,id desc limit 1)
) from skip s where (s.r).c2 is not null
) -- 这里的where (s.r).c2 is not null 一定要加, 否则就死循环了.
)
select (t.r).c2, (t.r).c3 from skip t where t.* is not null;
DECLARE CURSOR
Time: 1.240 ms
postgres=# fetch 100 from cur;
r
----------
(0,93)
(1,52)
(2,65)
.....
(97,78)
(98,44)
(99,99)
(100 rows)Time: 4.314 ms
使用变态的递归优化，性能提升了10倍，仅仅花了4秒，完成了1000万记录的筛选。
继续正文
在B表中，其实ID的值是很稀疏的，只是由于是流水，所以总量大。
优化的手段就是对B的取值区间，做递归的收敛查询，然后再做NOT IN就很快了。
例子
建表
create table a(id int primary key, info text);create table b(id int primary key, aid int, crt_time timestamp);
create index b_aid on b(aid);
插入测试数据
-- a表插入1000条
insert into a select generate_series(1,1000), md5(random()::text);-- b表插入500万条，只包含aid的500个id。
insert into b select generate_series(1,5000000), generate_series(1,500), clock_timestamp();
优化前的性能
\timingexplain (analyze,verbose,timing,costs,buffers) select * from a where id not in (select aid from b);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Seq Scan on public.a (cost=0.00..67030021.50 rows=500 width=37) (actual time=2932.080..618776.881 rows=500 loops=1)
Output: a.id, a.info
Filter: (NOT (SubPlan 1))
Rows Removed by Filter: 500
Buffers: shared hit=27037, temp read=4264454 written=8545
SubPlan 1
-> Materialize (cost=0.00..121560.00 rows=5000000 width=4) (actual time=0.002..298.049 rows=2500125 loops=1000)
Output: b.aid
Buffers: shared hit=27028, temp read=4264454 written=8545
-> Seq Scan on public.b (cost=0.00..77028.00 rows=5000000 width=4) (actual time=0.009..888.427 rows=5000000 loops=1)
Output: b.aid
Buffers: shared hit=27028
Planning time: 0.969 ms
Execution time: 618794.299 ms
(14 rows)
另外你有一种选择是使用outer join, b表同样需要全扫一遍，有很大的改进，不过还可以更好，继续往后看。
postgres=# explain (analyze,verbose,timing,costs,buffers) select a.id from a left join b on (a.id=b.aid) where b.* is null;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Hash Right Join (cost=31.50..145809.50 rows=25000 width=4) (actual time=2376.777..2376.862 rows=500 loops=1)
Output: a.id
Hash Cond: (b.aid = a.id)
Filter: (b.* IS NULL)
Rows Removed by Filter: 5000000
Buffers: shared hit=27037
-> Seq Scan on public.b (cost=0.00..77028.00 rows=5000000 width=44) (actual time=0.012..1087.997 rows=5000000 loops=1)
Output: b.aid, b.*
Buffers: shared hit=27028
-> Hash (cost=19.00..19.00 rows=1000 width=4) (actual time=0.355..0.355 rows=1000 loops=1)
Output: a.id
Buckets: 1024 Batches: 1 Memory Usage: 44kB
Buffers: shared hit=9
-> Seq Scan on public.a (cost=0.00..19.00 rows=1000 width=4) (actual time=0.010..0.183 rows=1000 loops=1)
Output: a.id
Buffers: shared hit=9
Planning time: 0.302 ms
Execution time: 2376.934 ms
(18 rows)
递归收敛优化后的性能
explain (analyze,verbose,timing,costs,buffers)
select * from a where id not in
(
with recursive skip as (
(
select min(aid) aid from b where aid is not null
)
union all
(
select (select min(aid) aid from b where b.aid > s.aid and b.aid is not null)
from skip s where s.aid is not null
) -- 这里的where s.aid is not null 一定要加,否则就死循环了.
)
select aid from skip where aid is not null
); QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on public.a (cost=54.98..76.48 rows=500 width=37) (actual time=10.837..10.957 rows=500 loops=1)
Output: a.id, a.info
Filter: (NOT (hashed SubPlan 5))
Rows Removed by Filter: 500
Buffers: shared hit=2012
SubPlan 5
-> CTE Scan on skip (cost=52.71..54.73 rows=100 width=4) (actual time=0.042..10.386 rows=500 loops=1)
Output: skip.aid
Filter: (skip.aid IS NOT NULL)
Rows Removed by Filter: 1
Buffers: shared hit=2003
CTE skip
-> Recursive Union (cost=0.46..52.71 rows=101 width=4) (actual time=0.037..10.104 rows=501 loops=1)
Buffers: shared hit=2003
-> Result (cost=0.46..0.47 rows=1 width=4) (actual time=0.036..0.036 rows=1 loops=1)
Output: $1
Buffers: shared hit=4
InitPlan 3 (returns $1)
-> Limit (cost=0.43..0.46 rows=1 width=4) (actual time=0.031..0.032 rows=1 loops=1)
Output: b_1.aid
Buffers: shared hit=4
-> Index Only Scan using b_aid on public.b b_1 (cost=0.43..131903.43 rows=5000000 width=4) (actual time=0.030..0.030 rows=1 loops=1)
Output: b_1.aid
Index Cond: (b_1.aid IS NOT NULL)
Heap Fetches: 1
Buffers: shared hit=4
-> WorkTable Scan on skip s (cost=0.00..5.02 rows=10 width=4) (actual time=0.019..0.019 rows=1 loops=501)
Output: (SubPlan 2)
Filter: (s.aid IS NOT NULL)
Rows Removed by Filter: 0
Buffers: shared hit=1999
SubPlan 2
-> Result (cost=0.47..0.48 rows=1 width=4) (actual time=0.018..0.018 rows=1 loops=500)
Output: $3
Buffers: shared hit=1999
InitPlan 1 (returns $3)
-> Limit (cost=0.43..0.47 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=500)
Output: b.aid
Buffers: shared hit=1999
-> Index Only Scan using b_aid on public.b (cost=0.43..66153.48 rows=1666667 width=4) (actual time=0.017..0.017 rows=1 loops=500)
Output: b.aid
Index Cond: ((b.aid > s.aid) AND (b.aid IS NOT NULL))
Heap Fetches: 499
Buffers: shared hit=1999
Planning time: 0.323 ms
Execution time: 11.082 ms
(46 rows)
采用收敛查询优化后，耗时从最初的 618794毫秒 降低到了 11毫秒 ，感觉一下子节约了好多青春。
优化方法2
此方法来自SQL性能挑战赛，书写更简洁：
https://yq.aliyun.com/roundtable/56354
采用sub query，A表数据量小，查询A表的QUERY中使用SUB QUERY使得SUB QUERY的扫描次数下降到与A行数一致，SUB QUERY中采用LIMIT 1限定返回数，is null限定得出B表中未出现的aid。妙!!!
如下
postgres=# explain analyze
select * from
(
select
a.* ,
(select aid from b where b.aid=a.id limit 1) as aid -- sub query, limit 1控制了扫描次数
from a -- a表很小
) as t
where t.aid is null; QUERY PLAN
Seq Scan on a (cost=0.00..4137.84 rows=5 width=41) (actual time=18.232..18.904 rows=100 loops=1)
Filter: ((SubPlan 2) IS NULL)
Rows Removed by Filter: 901
SubPlan 1
-> Limit (cost=0.43..4.09 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=100)
-> Index Only Scan using b_aid on b (cost=0.43..40614.63 rows=11099 width=4) (actual time=0.002..0.002 rows=0 loops=100)
Index Cond: (aid = a.id)
Heap Fetches: 0
SubPlan 2
-> Limit (cost=0.43..4.09 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=1001)
-> Index Only Scan using b_aid on b b_1 (cost=0.43..40614.63 rows=11099 width=4) (actual time=0.017..0.017 rows=1 loops=1001)
Index Cond: (aid = a.id)
Heap Fetches: 901
Planning time: 0.297 ms
Execution time: 18.980 ms
(15 rows)
或者
postgres=# explain analyze
select * from a
where (select aid from b where b.aid=a.id limit 1) is null; -- sub query is NULL, 是不是很给力呢QUERY PLAN
Seq Scan on a (cost=0.00..4117.37 rows=5 width=37) (actual time=18.346..18.699 rows=100 loops=1)
Filter: ((SubPlan 1) IS NULL)
Rows Removed by Filter: 901
SubPlan 1
-> Limit (cost=0.43..4.09 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1001)
-> Index Only Scan using b_aid on b (cost=0.43..40614.63 rows=11099 width=4) (actual time=0.017..0.017 rows=1 loops=1001)
Index Cond: (aid = a.id)
Heap Fetches: 901
Planning time: 0.181 ms
Execution time: 18.755 ms
(10 rows)
小结
1、递归查询，A表全扫，B表索引扫描了若干次（若干 = 唯一AID在B中出现的次数）。
2、SUB QUERY，A表全扫，B表索引扫描了若干次（若干 = A表记录数）。
由于B表都是索引扫，两种方法差别不大(递归扫描的次数更少一些)。
文章中的参考文档请点击阅读原文获得.
欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号: