来测一下你有没有SQL优化基因!

2017/07/14 15:26
阅读数 19

原文地址

 

原文

发布时间:2017-07-14 10:22:11218人关注5人参与

<震惊!慢SQL居然能优化到这种速度,我不服!>后,又一轮SQL挑战赛来了。这次是《决战紫禁之巅》,不服来战。

image

场景表述

一张小表A,里面存储了一些ID,大约几百个。

(比如说巡逻车辆ID,环卫车辆的ID,公交车,微公交的ID)。

另外有一张日志表B,每条记录中的ID是来自前面那张小表的,但不是每个ID都出现在这张日志表中,比如说一天可能只有几十个ID会出现在这个日志表的当天的数据中。

(比如车辆的行车轨迹数据,每秒上报轨迹,数据量就非常庞大)。

那么我怎么快速的找出今天没有出现的ID呢。

(哪些巡逻车辆没有出现在这个片区,是不是偷懒了?哪些环卫车辆没有出行,哪些公交或微公交没有出行)?

测试模型和数据

建表

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(0,1000), md5(random()::text);

-- b表插入1000万条,只包含aid的901个id。
insert into b select generate_series(1,10000000), random()*900, clock_timestamp();

参考SQL和查询性能

下面两条SQL都是满足查询条件的SQL,由PostgreSQL 10给出。

代表了没有做任何优化的情况下的查询性能。

postgres=# explain (analyze,timing) select * from a where id not in (select aid from b); 
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Seq Scan on a  (cost=179053.25..179074.76 rows=500 width=37) (actual time=4369.478..4369.525 rows=100 loops=1)
   Filter: (NOT (hashed SubPlan 1))
   Rows Removed by Filter: 901
   SubPlan 1
     ->  Seq Scan on b  (cost=0.00..154053.60 rows=9999860 width=4) (actual time=0.322..1829.342 rows=10000000 loops=1)
 Planning time: 0.094 ms
 Execution time: 4423.364 ms
(7 rows)

postgres=# explain (analyze,timing) select a.* from a left join b on (a.id=b.aid) where b.* is null;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Hash Right Join  (cost=31.52..280244.69 rows=49999 width=37) (actual time=4316.767..4316.790 rows=100 loops=1)
   Hash Cond: (b.aid = a.id)
   Filter: (b.* IS NULL)
   Rows Removed by Filter: 10000000
   ->  Seq Scan on b  (cost=0.00..154053.60 rows=9999860 width=44) (actual time=0.013..2544.321 rows=10000000 loops=1)
   ->  Hash  (cost=19.01..19.01 rows=1001 width=37) (actual time=0.342..0.342 rows=1001 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 76kB
         ->  Seq Scan on a  (cost=0.00..19.01 rows=1001 width=37) (actual time=0.009..0.137 rows=1001 loops=1)
 Planning time: 0.173 ms
 Execution time: 4316.828 ms
(10 rows)

地址

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部