在知乎上看到這樣一個問題:
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的效率問題,希望對大家有所幫助!