如何探讨select in 在postgresql的效率问题

共计 2234 个字符，预计需要花费 6 分钟才能阅读完成。

如何探讨 select in 在 postgresql 的效率问题，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

在知乎上看到这样一个问题：

MySQL 查询 select * from table where id in (几百或几千个 id) 如何提高效率？修改

电商网站，一个商品属性表，几十万条记录，80M，索引只有主键 id，做这样的查询如何提高效率？

select * from table where id in (几百或几千个 id)

这些 id 没啥规律，分散的。。。。

看了一下答案，感觉有好多不靠谱的，但是口说无凭，所以在我的电脑上写了几个查询测试一下。我用的是 Postgresql9.4，但感觉 mysql 应该也差不多，首先创建一个简单表，只有简单的 3 列，在这个问题的下面好多人提到了需要看表的大小，其实这个问题和表大小无关，只和 index 的大小有关，因为是 index 是建立在 int 上的，所以只和纪录数目有关。

Table  public.t9 Column | Type | Modifiers--------+----------------+-----------c1 | integer |c2 | character(100) |c3 | character(200) |Indexes: i1  UNIQUE, btree (c1)insert into t9 values(generate_series(1000,500000,1),repeat(a ,90),repeat(b ,180));

之后生成一些随机数，Mac 上用 jot，Linux 上用 shuf

for ((i=0;i 100000;i++))dojot -r 1 1000 600000  rand.filedone

然后根据 rand.file 生成查询语句：

select * from t9 where c1 in (494613,575087,363588,527650,251670,343456,426858,202886,254037,...1);

分别生成 3 个 sql 文件，in 内变量的数目分别是 100，1000 和 10000 个，执行这 3 个 sql 文件，看看时间

try psql study -f test_100.sql -o /dev/nullLOG: duration: 2.879 mstry psql study -f test_1000.sql -o /dev/nullLOG: duration: 11.974 mstry psql study -f test_10000.sql -o /dev/nullLOG: duration: 355.689 ms

可以看到只有在 in 内数据到了 10，000 个的时候数据时间会有比较大的变化，但也不过是在 300 多 ms 内完成。

那如果按照有些回答那样，先建一个临时表，然后用 in subquery，并且希望这时候可以两表 join 呢？为了简单我直接用两表 join 了

drop table t_tmp;create table t_tmp(id int);insert into t_tmp (id) values(494613),(575087),(363588),(345980),...(1);select t9.* from t9, t_tmpwhere t9.c1 = t_tmp.id;

时间如何呢？

try psql study -f test_create_10000.sql -o /dev/nullLOG: duration: 2.078 msLOG: duration: 1.233 msLOG: duration: 224.112 msLOG: duration: 322.108 ms

除去 drop 和 create 的时间，依然花费了 500+ 的时间，这里的前提还是我用的 ssd 盘，所以写 LOG 的时间会快很多。为什么会这么慢呢？用 explain 看一下，这时候数据量较大，直接走 Merge join 了

那 1000 行数据的效率如何呢？

try psql study -f test_create_1000.sql -o exp.outLOG: duration: 2.476 msLOG: duration: 0.967 msLOG: duration: 2.391 msLOG: duration: 8.780 ms

100 行的数据如下：

try psql study -f test_create_100.sql -o /dev/nullLOG: duration: 2.020 msLOG: duration: 1.028 msLOG: duration: 1.074 msLOG: duration: 1.912 ms

可以看到在 100 个值和 1000 个值的情况下 create table 的方式不会比直接在 in 里面写所有的变量好多少，explain 看的话是在用 NLJ 了。但在数据量更大（按照原问题，这里 in 的数量其实无法预知）的情况下效率只会更低，再加上额外的表维护成本和多余的 SQL 语句，DBA 肯定不喜欢的，还是相信数据库，放心大胆直接用 in list 来搞定这些问题吧。

看完上述内容，你们掌握如何探讨 select in 在 postgresql 的效率问题的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！