探讨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的效率问题，希望对大家有所帮助！

赞 (0)

相关推荐

  • insert into select和select into的使用和区别介绍

    insert into ... select ...:可将表1中的全部数据或者部分数据复制到表2中. eg: 复制代码 代码如下: insert into t2(id,name,pwd) select id,name,pwd from t1 注:t2必须存在.t1中查询的列名可不与t1列名相同.无 values select...into:查询t1中的数据,插入到t2中. eg: 复制代码 代码如下: select * into t2 from t1 注:t2被创建并填充数据.

  • 在Spring中用select last_insert_id()时遇到问题

    在Spring中用select last_insert_id()时遇到问题

    今天在使用MySQL时却不知如何处理,插入记录后不知怎样获得刚刚插入的id,查过文档后发现了select last_insert_id(),在插入之后执行此查询,即可获得自增id,喜出望外.可用到自己的程序中之后却得不到想要的结果,于是就怀疑到了Spring头上,因为通过基本JDBC测试是没有任何问题的,所以就去跟踪Spring JDBC, 看过源码之后才豁然开朗,原来Spring中如此获得数据库Connection的:Connection con = DataSourceUtils.getCo

  • 数据库插入数据之select into from与insert into select区别详解

    数据库插入数据之select into from与insert into select区别详解

    可能第一次接触select...into...from...和insert into...select...有很多人都会误解, 从表面上看都是把相关信息查询出来,然后添加到一个表里,其实还远远没有这么简单,接下来,小猪就用最普通的表述给大家介绍一下这两者间的区别. 步骤/方法 1.首先,我们来看一下insert into select语句,其语法形式为:Insert into Table2(field1,field2,...) select value1,value2,... from Tabl

  • PostgreSQL教程(七)：函数和操作符详解（3）

    九.序列操作函数: 序列对象(也叫序列生成器)都是用CREATE SEQUENCE创建的特殊的单行表.一个序列对象通常用于为行或者表生成唯一的标识符.下面序列函数,为我们从序列对象中获取最新的序列值提供了简单和并发读取安全的方法. 函数 返回类型 描述 nextval(regclass) bigint 递增序列对象到它的下一个数值并且返回该值.这个动作是自动完成的.即使多个会话并发运行nextval,每个进程也会安全地收到一个唯一的序列值. currval(regclass) bigint 在当

  • 解析MySQL中INSERT INTO SELECT的使用

    1. 语法介绍有三张表a.b.c,现在需要从表b和表c中分别查几个字段的值插入到表a中对应的字段.对于这种情况,可以使用如下的语句来实现:INSERT INTO db1_name (field1,field2) SELECT field1,field2 FROM db2_name 上面的语句比较适合两个表的数据互插,如果多个表就不适应了.对于多个表,可以先将需要查询的字段JOIN起来,然后组成一个视图后再SELECT FROM就可以了: INSERT INTO a (field1,field2)

  • 解决Hibernate JPA中insert插入数据后自动执行select last_insert_id()

    今天做项目遇到了一个问题,是以前没注意的.我用的是Spring MVC+ Hibernate JPA + MySQL数据库.在插入数据后SQL执行日志中会多出一条select语句: 复制代码 代码如下: Hibernate: insert into click_statstic (logDate, memoId, src, typeId) values (?, ?, ?, ?)Hibernate: select last_insert_id() 表中有个主键是自增列.可是在以往的项目中,没发现有

  • Mysql select in 按id排序实现方法

    表结构如下: mysql> select * from test; +----+-------+ | id | name | +----+-------+ | 1 | test1 | | 2 | test2 | | 3 | test3 | | 4 | test4 | | 5 | test5 | +----+-------+ 执行以下SQL: mysql> select * from test where id in(3,1,5); +----+-------+ | id | name | +-

  • 解决IE下select标签innerHTML插入option的BUG（兼容IE,FF,Opera,Chrome,Safari)

    前言: 这是一个老bug了,现在提供一个完美解决方案.由于我一直是用createElement来创建动态的option并添加,所以一直没有遇到这个问题,但是每个人写代码风格不同,有的人就喜欢写字符串形式的标签并用innerHTML插入,这不就有问题了,为了方便不同编码风格的人,我封装了一个方法,用于解决ie的这个bug和兼容5大浏览器,这样大家都可以用一个方法来实现不同的风格,便于维护管理. bug描述: 在ie下面使用innerHTML来插入option选项的话,ie会去掉前面的<option

  • PostgreSQL教程(六)：函数和操作符详解（2）

    六.模式匹配: PostgreSQL中提供了三种实现模式匹配的方法:SQL LIKE操作符,更近一些的SIMILAR TO操作符,和POSIX-风格正则表达式.     1. LIKE:   复制代码 代码如下: string LIKE pattern [ ESCAPE escape-character ]     string NOT LIKE pattern [ ESCAPE escape-character ] 每个pattern定义一个字串的集合.如果该string包含在pattern代

  • linux使用select实现精确定时器详解

    在编写程序时,我们经常会用到定时器.首先看看select函数原型如下: 复制代码 代码如下: int select(int nfds, fd_set *readfds, fd_set *writefds,                  fd_set *exceptfds, struct timeval *timeout); 参数说明:slect的第一个参数nfds为fdset集合中最大描述符值加1,fdset是一个位数组,其大小限制为__FD_SETSIZE(1024),位数组的每一位代表其

  • input+select(multiple) 实现下拉框输入值

    input+select(multiple) 实现下拉框输入值

    于是尝试了用input+select来实现,结果如下: 具体代码及演示地址:www.homdian.com/test.aspx 这种方法其实是在下拉框上压一个文本框,能够实现输入,也能够实现下拉框的选择.但根据输入值用用异步的方式绑定下拉框选项时, 无法展开下拉框,到网上找了段代码: 复制代码 代码如下: document.form1.ddlMovName.focus(); var WshShell = new ActiveXObject("Wscript.Shell"); try{

  • PostgreSQL教程(十六)：系统视图详解

    一.pg_tables: 该视图提供了对有关数据库中每个表的有用信息地访问. 名字 类型 引用 描述 schemaname name pg_namespace.nspname 包含表的模式名字. tablename name pg_class.relname 表的名字. tableowner name pg_authid.rolname 表的所有者的名字. tablespace name pg_tablespace.spcname 包含表的表空间名字(如果是数据库缺省,则为 NULL). has

随机推荐