因为pgsql的查询规划器可以智能地分解条件,比如
SELECT * FROM tk WHERE tk.id = 1 OR tk.id = 2
规划器可以智能地分解成两条针对有索引的id字段的查询,然后作UNION。
那IN的情况如何呢?
SELECT * FROM tk WHERE tk.id IN (1,2,3)
explain 结果居然不是想象中的分解而是使用了pgsql的数组类型来处理
Bitmap Heap Scan on tk (cost=12.77..21.82 rows=3 width=72)
Recheck Cond: (id = ANY ('{1,2,3}'::integer[]))
-> Bitmap Index Scan on tk_pkey (cost=0.00..12.77 rows=3 width=0)
Index Cond: (id = ANY ('{1,2,3}'::integer[]))
Time: 0.015s
效率比手动分解还要高!
SELECT * FROM tk WHERE tk.id = 1 OR tk.id = 2 OR tk.id =3
Bitmap Heap Scan on tk (cost=12.78..21.83 rows=3 width=72)
Recheck Cond: ((id = 1) OR (id = 2) OR (id = 3))
-> BitmapOr (cost=12.78..12.78 rows=3 width=0)
-> Bitmap Index Scan on tk_pkey (cost=0.00..4.26 rows=1 width=0)
Index Cond: (id = 1)
-> Bitmap Index Scan on tk_pkey (cost=0.00..4.26 rows=1 width=0)
Index Cond: (id = 2)
-> Bitmap Index Scan on tk_pkey (cost=0.00..4.26 rows=1 width=0)
Index Cond: (id = 3)
Time: 0.047s
对于mysql来说两种方式没区别,它的查询优化器没那么智能和强大,连OR都不能分解的。所以叫优化器而不是规划器——要靠人肉来做优化 :p