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++))dojot -r 1 1000 600000 >>rand.filedone

然後根據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/nullLOG:  duration: 2.879 ms?  try psql study -f test_1000.sql -o /dev/nullLOG:  duration: 11.974 ms?  try psql study -f test_10000.sql -o /dev/nullLOG:  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_tmpwhere t9.c1 = t_tmp.id;

時間如何呢？

try psql study -f test_create_10000.sql -o /dev/nullLOG:  duration: 2.078 msLOG:  duration: 1.233 msLOG:  duration: 224.112 msLOG:  duration: 322.108 ms

除去drop和create的時間，依然花費了500+的時間，這裡的前提還是我用的ssd盤，所以寫LOG的時間會快很多。為什麼會這麼慢呢？用explain看一下，這時候資料量較大，直接走Merge join 了

那1000行資料的效率如何呢？

try psql study -f test_create_1000.sql -o exp.outLOG:  duration: 2.476 msLOG:  duration: 0.967 msLOG:  duration: 2.391 msLOG:  duration: 8.780 ms

100行的資料如下：

?  try psql study -f test_create_100.sql -o /dev/nullLOG:  duration: 2.020 msLOG:  duration: 1.028 msLOG:  duration: 1.074 msLOG:  duration: 1.912 ms

 

可以看到在100個值和1000個值的情況下create table的方式不會比直接在in裡面寫所有的變數好多少，explain看的話是在用NLJ了。但在資料量更大（按照原問題，這裡in的數量其實無法預知）的情況下效率只會更低，再加上額外的表維護成本和多餘的SQL語句，DBA肯定不喜歡的，還是相信資料庫，放心大膽直接用in list來搞定這些問題吧。

 

 

select in 在postgresql的效率問題