最近遇到查詢一張大數(shù)據(jù)量表時(shí)，需要對(duì)一個(gè)字段做in查詢，in中的元素?cái)?shù)量可能達(dá)到幾千個(gè)，即使對(duì)這個(gè)字段加上索引，速度也慢到無(wú)法接受

示例表結(jié)構(gòu)如下：

?表中有幾十萬(wàn)的數(shù)據(jù)，且example_id和data_id字段加了聯(lián)合索引，只做一個(gè)簡(jiǎn)單的select查詢：

select * from TEST_TABLE01 where example_id=:exampleId and data_id in(:dataIds)

?其中in存在1000個(gè)元素，查詢速度很慢，因?yàn)閕n的個(gè)數(shù)太多，會(huì)全表掃描，導(dǎo)致索引失效。

優(yōu)化方案：

不使用in語(yǔ)法，將sql語(yǔ)句簡(jiǎn)化成下面這種，索引就生效了

select * from TEST_TABLE01 where example_id=:exampleId and data_id=:dataId

但是這樣一次只能查詢一條data_id匹配的數(shù)據(jù)，這就意味著程序要和數(shù)據(jù)庫(kù)交互1000次，但是我測(cè)試的速度要快于上面的in方式。

進(jìn)一步優(yōu)化，減少數(shù)據(jù)庫(kù)交互方式，使用union all拼接sql：?

select * from TEST_TABLE01 where example_id=:exampleId and data_id=:dataId0
union all
select * from TEST_TABLE01 where example_id=:exampleId and data_id=:dataId1
union all
select * from TEST_TABLE01 where example_id=:exampleId and data_id=:dataId2
union all
select * from TEST_TABLE01 where example_id=:exampleId and data_id=:dataId3
...
...
union all
select * from TEST_TABLE01 where example_id=:exampleId and data_id=:dataId999

?程序中對(duì)dataId的參數(shù)進(jìn)行組裝，這樣只和數(shù)據(jù)庫(kù)交互一次，索引也不會(huì)失效，這種方式解決了in查詢慢的問(wèn)題。

對(duì)于delete也可以使用類似的方式優(yōu)化：

delete from TEST_TABLE01 a
WHERE exists (select * from (select * TEST_TABLE01 where example_id=:exampleId and data_id=:dataId0union allselect * TEST_TABLE01 where example_id=:exampleId and data_id=:dataId1) b where a.id=b.id
)