当前位置: 首页 > 编程笔记 >

探讨select in 在postgresql的效率问题

景震博
2023-03-14
本文向大家介绍探讨select in 在postgresql的效率问题,包括了探讨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的效率问题,希望对大家有所帮助!

 类似资料:
  • 本文向大家介绍Postgresql查询效率计算初探,包括了Postgresql查询效率计算初探的使用技巧和注意事项,需要的朋友参考一下 摘要 关系数据库很重要的一个方面是查询速度。查询速度的好坏,直接影响一个系统的好坏。 查询速度一般需要通过查询规划来窥视执行的过程。 查询路径会选择查询代价最低的路径执行。而这个代价是怎么算出来的呢。 主要关注的参数和表 参数:来自postgresql.conf文

  • 本文向大家介绍探寻Javascript执行效率问题,包括了探寻Javascript执行效率问题的使用技巧和注意事项,需要的朋友参考一下 Javascript是一门非常灵活的语言,我们可以随心所欲的书写各种风格的代码,不同风格的代码也必然也会导致执行效率的差异,开发过程中零零散散地接触到许多提高代码性能的方法,整理一下平时比较常见并且容易规避的问题  Javascript自身执行效率   Javas

  • 本文向大家介绍探讨Ajax中的一些小问题,包括了探讨Ajax中的一些小问题的使用技巧和注意事项,需要的朋友参考一下 1.ajax跨域传递值是所需要的回传的类型为jsonp dataType 类型:String 预期服务器返回的数据类型。如果不指定,jQuery 将自动根据 HTTP 包 MIME 信息来智能判断,比如 XML MIME 类型就被识别为 XML。在 1.4 中,JSON 就会生成一个

  • 本文向大家介绍js拼接html注意问题示例探讨,包括了js拼接html注意问题示例探讨的使用技巧和注意事项,需要的朋友参考一下 你想让别人给你解决什么, adress是什么, 下面又出现adress[j]。用法完全不对。 追问 我是想把 这句外面加个单引号,可是试了好多次都不成功,这句话加上单引号,里面改怎么变化呢 回答 用转义符, \" = ", \' = ' 追问 转义符不太会用,能给个列子吗

  • 本文向大家介绍Python的类实例属性访问规则探讨,包括了Python的类实例属性访问规则探讨的使用技巧和注意事项,需要的朋友参考一下 一般来说,在Python中,类实例属性的访问规则算是比较直观的。 但是,仍然存在一些不是很直观的地方,特别是对C++和Java程序员来说,更是如此。 在这里,我们需要明白以下几个地方: 1.Python是一门动态语言,任何实体都可以动态地添加或删除属性。 2.一个

  • 本文向大家介绍探讨js字符串数组拼接的性能问题,包括了探讨js字符串数组拼接的性能问题的使用技巧和注意事项,需要的朋友参考一下 我们知道,在js中,字符串连接是性能最低的操作之一。 例如: 早期浏览器没有对这种运算进行优化。 由于字符串是不可变的,这意味着要创建中间字符串来存储连接的结果。频繁地在后台创建和销毁字符串导制性能异常低下。 因此,可以利用数组对象进行优化。 例如: 在早期的浏览器中,没