共計(jì) 2234 個(gè)字符，預(yù)計(jì)需要花費(fèi) 6 分鐘才能閱讀完成。
如何探討 select in 在 postgresql 的效率問(wèn)題，相信很多沒(méi)有經(jīng)驗(yàn)的人對(duì)此束手無(wú)策，為此本文總結(jié)了問(wèn)題出現(xiàn)的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。
在知乎上看到這樣一個(gè)問(wèn)題：
MySQL 查詢(xún) select * from table where id in (幾百或幾千個(gè) id) 如何提高效率？修改
電商網(wǎng)站，一個(gè)商品屬性表，幾十萬(wàn)條記錄，80M，索引只有主鍵 id，做這樣的查詢(xún)?nèi)绾翁岣咝剩?/p>
select * from table where id in (幾百或幾千個(gè) id)
這些 id 沒(méi)啥規(guī)律，分散的。。。。
看了一下答案，感覺(jué)有好多不靠譜的，但是口說(shuō)無(wú)憑，所以在我的電腦上寫(xiě)了幾個(gè)查詢(xún)測(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)槭?index 是建立在 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++))dojot -r 1 1000 600000 rand.filedone然后根據(jù) rand.file 生成查詢(xún)語(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/nullLOG: duration: 2.879 mstry psql study -f test_1000.sql -o /dev/nullLOG: duration: 11.974 mstry psql study -f test_10000.sql -o /dev/nullLOG: 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í)候可以?xún)杀?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_tmpwhere t9.c1 = t_tmp.id;時(shí)間如何呢？
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 的時(shí)間，依然花費(fèi)了 500+ 的時(shí)間，這里的前提還是我用的 ssd 盤(pán)，所以寫(xiě) LOG 的時(shí)間會(huì)快很多。為什么會(huì)這么慢呢？用 explain 看一下，這時(shí)候數(shù)據(jù)量較大，直接走 Merge join 了
那 1000 行數(shù)據(jù)的效率如何呢？
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 ms100 行的數(shù)據(jù)如下：
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 個(gè)值和 1000 個(gè)值的情況下 create table 的方式不會(huì)比直接在 in 里面寫(xiě)所有的變量好多少，explain 看的話(huà)是在用 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)容，你們掌握如何探討 select in 在 postgresql 的效率問(wèn)題的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道，感謝各位的閱讀！
