这里有新鲜出炉的 PostgreSQL 指南,程序狗速度看过来!
PostgreSQL 是以加州大学伯克利分校计算机系开发的 POSTGRES,现在已经更名为 PostgreSQL,版本 4.2 为基础的对象关系型数据库管理系统(ORDBMS)。
这篇文章主要介绍了探讨 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++))
- do
- jot -r 1 1000 600000 >>rand.file
- done
然后根据 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/null
- LOG: duration: 2.879 ms
- try psql study -f test_1000.sql -o /dev/null
- LOG: duration: 11.974 ms
- try psql study -f test_10000.sql -o /dev/null
- LOG: 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_tmp
- where t9.c1 = t_tmp.id;
时间如何呢?
- try psql study -f test_create_10000.sql -o /dev/null
- LOG: duration: 2.078 ms
- LOG: duration: 1.233 ms
- LOG: duration: 224.112 ms
- LOG: duration: 322.108 ms
除去 drop 和 create 的时间,依然花费了 500 + 的时间,这里的前提还是我用的 ssd 盘,所以写 LOG 的时间会快很多。为什么会这么慢呢?用 explain 看一下,这时候数据量较大,直接走 Merge join 了
那 1000 行数据的效率如何呢?
- try psql study -f test_create_1000.sql -o exp.out
- LOG: duration: 2.476 ms
- LOG: duration: 0.967 ms
- LOG: duration: 2.391 ms
- LOG: duration: 8.780 ms
100 行的数据如下:
- try psql study -f test_create_100.sql -o /dev/null
- LOG: duration: 2.020 ms
- LOG: duration: 1.028 ms
- LOG: duration: 1.074 ms
- LOG: duration: 1.912 ms
可以看到在 100 个值和 1000 个值的情况下 create table 的方式不会比直接在 in 里面写所有的变量好多少,explain 看的话是在用 NLJ 了。但在数据量更大(按照原问题,这里 in 的数量其实无法预知)的情况下效率只会更低,再加上额外的表维护成本和多余的 SQL 语句,DBA 肯定不喜欢的,还是相信数据库,放心大胆直接用 in list 来搞定这些问题吧。
以上内容是针对 select in 在 postgresql 的效率问题,希望对大家有所帮助!
来源: http://www.phperz.com/article/17/0819/339336.html