欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

探討select in 在postgresql的效率問(wèn)題

 更新時(shí)間:2016年04月06日 09:56:41   作者:db2zos  
這篇文章主要介紹了探討select in 在postgresql的效率問(wèn)題 的相關(guān)資料,需要的朋友可以參考下

在知乎上看到這樣一個(gè)問(wèn)題:

MySQL 查詢 select * from table where id in (幾百或幾千個(gè) id) 如何提高效率?修改

電商網(wǎng)站,一個(gè)商品屬性表,幾十萬(wàn)條記錄,80M,索引只有主鍵id,做這樣的查詢?nèi)绾翁岣咝剩?br />

select * from table where id in (幾百或幾千個(gè)id)

這些id沒(méi)啥規(guī)律,分散的。。。。

看了一下答案,感覺(jué)有好多不靠譜的,但是口說(shuō)無(wú)憑,所以在我的電腦上寫(xiě)了幾個(gè)查詢測(cè)試一下。我用的是Postgresql9.4,但感覺(jué)mysql應(yīng)該也差不多,首先創(chuàng)建一個(gè)簡(jiǎn)單表,只有簡(jiǎn)單的3列,在這個(gè)問(wèn)題的下面好多人提到了需要看表的大小,其實(shí)這個(gè)問(wèn)題和表大小無(wú)關(guān),只和index的大小有關(guān),因?yàn)槭莍ndex是建立在int上的,所以只和紀(jì)錄數(shù)目有關(guān)。

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)); 

之后生成一些隨機(jī)數(shù),Mac上用jot,Linux上用shuf

for ((i=0;i<100000;i++))
do
jot -r 1 1000 600000 >>rand.file
done 

然后根據(jù)rand.file 生成查詢語(yǔ)句:

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

分別生成3個(gè)sql文件,in內(nèi)變量的數(shù)目分別是100,1000和10000個(gè),執(zhí)行這3個(gè)sql文件,看看時(shí)間

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內(nèi)數(shù)據(jù)到了10,000個(gè)的時(shí)候數(shù)據(jù)時(shí)間會(huì)有比較大的變化,但也不過(guò)是在300多ms內(nèi)完成。

那如果按照有些回答那樣,先建一個(gè)臨時(shí)表,然后用in subquery,并且希望這時(shí)候可以兩表join呢?為了簡(jiǎn)單我直接用兩表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; 

時(shí)間如何呢?

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的時(shí)間,依然花費(fèi)了500+的時(shí)間,這里的前提還是我用的ssd盤(pán),所以寫(xiě)LOG的時(shí)間會(huì)快很多。為什么會(huì)這么慢呢?用explain看一下,這時(shí)候數(shù)據(jù)量較大,直接走M(jìn)erge join 了

那1000行數(shù)據(jù)的效率如何呢?

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行的數(shù)據(jù)如下:

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個(gè)值和1000個(gè)值的情況下create table的方式不會(huì)比直接在in里面寫(xiě)所有的變量好多少,explain看的話是在用NLJ了。但在數(shù)據(jù)量更大(按照原問(wèn)題,這里in的數(shù)量其實(shí)無(wú)法預(yù)知)的情況下效率只會(huì)更低,再加上額外的表維護(hù)成本和多余的SQL語(yǔ)句,DBA肯定不喜歡的,還是相信數(shù)據(jù)庫(kù),放心大膽直接用in list來(lái)搞定這些問(wèn)題吧。

以上內(nèi)容是針對(duì)select in 在postgresql的效率問(wèn)題,希望對(duì)大家有所幫助!

相關(guān)文章

最新評(píng)論