在我们平时写SQL时,如果遇到需要排除某些数据时,往往使用id <> xxx and id <> xxx,进而改进为id not in (xxx, xxx);
这样写没有问题,而且简化了SQL,但是往往有些极端情况,使用not in就会造成极大的性能损耗,例如:
select * from test where id not in (select id from test_back) and info like '%test%';
这样的话select id from test_back将成为一个子查询,而且不会走索引,每次走一遍全表扫描。
每一条满足info like '%test%'的记录都会去调用这个方法去判断id是否不在子查询中,具体的执行计划见下面的例子。
改进方法:
1)使用test和test_back进行联合查询,id <> id明显是不行的,这样只会判断同一关联条件下的一行中的id是否相同,无法做到排除某些id。
2)正确的方式应该使用not exists,将条件下推到里面,就不会出现子查询了:
select * from test t1 where info like '%test%' and not exits (select 1 from test_back t2 where t2.id = t1.id);
apple=# \d test Table "public.test" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | not null | info | text | | | Indexes: "test_pkey" PRIMARY KEY, btree (id) apple=# truncate test; TRUNCATE TABLE apple=# insert into test select generate_series(1, 100), 'test'||round(random()*10000)::text; INSERT 0 100 apple=# select * from test limit 1; id | info ----+---------- 1 | test9526 (1 row) apple=# insert into test select generate_series(101, 200), 'tes'||round(random()*10000)::text; INSERT 0 100 apple=# create table test_back as select * from test where id between 50 and 70; SELECT 21 apple=# explain select * from test where id not in (select id from test_back) and info like '%test%'; QUERY PLAN --------------------------------------------------------------------- Seq Scan on test (cost=25.88..30.88 rows=49 width=12) Filter: ((NOT (hashed SubPlan 1)) AND (info ~~ '%test%'::text)) SubPlan 1 -> Seq Scan on test_back (cost=0.00..22.70 rows=1270 width=4) (4 rows)
apple=# explain select * from test t1 where info like '%test%' and not exists (select 1 from test_back t2 where t2.id = t1.id); QUERY PLAN ------------------------------------------------------------------------- Hash Anti Join (cost=1.47..7.13 rows=89 width=12) Hash Cond: (t1.id = t2.id) -> Seq Scan on test t1 (cost=0.00..4.50 rows=99 width=12) Filter: (info ~~ '%test%'::text) -> Hash (cost=1.21..1.21 rows=21 width=4) -> Seq Scan on test_back t2 (cost=0.00..1.21 rows=21 width=4) (6 rows)
例子里面没有建索引,建索引后,这种优化方式效果更好。
那么进一步扩展来说:
1)!= 不是标准的SQL,<>才是,这两个在PostgreSQL中是等效的。
2)exits和not exits的意思是逐条将条件下放到判断条件,而jion方式是先对表进行笛卡尔积,然后判断同行之间的各列值是否满足关系。