探讨select in 在postgresql的效率问题
在知乎上看到这样一个问题:
MySQL查询select*fromtablewhereidin(几百或几千个id)如何提高效率?修改
电商网站,一个商品属性表,几十万条记录,80M,索引只有主键id,做这样的查询如何提高效率?
select*fromtablewhereidin(几百或几千个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)insertintot9values(generate_series(1000,500000,1),repeat('a',90),repeat('b',180));
之后生成一些随机数,Mac上用jot,Linux上用shuf
for((i=0;i<100000;i++)) do jot-r11000600000>>rand.file done
然后根据rand.file生成查询语句:
select*fromt9wherec1in( 494613, 575087, 363588, 527650, 251670, 343456, 426858, 202886, 254037, ... 1 );
分别生成3个sql文件,in内变量的数目分别是100,1000和10000个,执行这3个sql文件,看看时间
trypsqlstudy-ftest_100.sql-o/dev/null LOG:duration:2.879ms trypsqlstudy-ftest_1000.sql-o/dev/null LOG:duration:11.974ms trypsqlstudy-ftest_10000.sql-o/dev/null LOG:duration:355.689ms
可以看到只有在in内数据到了10,000个的时候数据时间会有比较大的变化,但也不过是在300多ms内完成。
那如果按照有些回答那样,先建一个临时表,然后用insubquery,并且希望这时候可以两表join呢?为了简单我直接用两表join了
droptablet_tmp; createtablet_tmp(idint); insertintot_tmp(id)values (494613), (575087), (363588), (345980),... (1); selectt9.*fromt9,t_tmp wheret9.c1=t_tmp.id;
时间如何呢?
trypsqlstudy-ftest_create_10000.sql-o/dev/null LOG:duration:2.078ms LOG:duration:1.233ms LOG:duration:224.112ms LOG:duration:322.108ms
除去drop和create的时间,依然花费了500+的时间,这里的前提还是我用的ssd盘,所以写LOG的时间会快很多。为什么会这么慢呢?用explain看一下,这时候数据量较大,直接走Mergejoin了
那1000行数据的效率如何呢?
trypsqlstudy-ftest_create_1000.sql-oexp.out LOG:duration:2.476ms LOG:duration:0.967ms LOG:duration:2.391ms LOG:duration:8.780ms
100行的数据如下:
trypsqlstudy-ftest_create_100.sql-o/dev/null LOG:duration:2.020ms LOG:duration:1.028ms LOG:duration:1.074ms LOG:duration:1.912ms
可以看到在100个值和1000个值的情况下createtable的方式不会比直接在in里面写所有的变量好多少,explain看的话是在用NLJ了。但在数据量更大(按照原问题,这里in的数量其实无法预知)的情况下效率只会更低,再加上额外的表维护成本和多余的SQL语句,DBA肯定不喜欢的,还是相信数据库,放心大胆直接用inlist来搞定这些问题吧。
以上内容是针对selectin在postgresql的效率问题,希望对大家有所帮助!