易县做网站企业解决方案顾问

最近遇到查询一张大数据量表时，需要对一个字段做in查询，in中的元素数量可能达到几千个，即使对这个字段加上索引，速度也慢到无法接受

示例表结构如下：

 表中有几十万的数据，且example_id和data_id字段加了联合索引，只做一个简单的select查询：

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

 其中in存在1000个元素，查询速度很慢，因为in的个数太多，会全表扫描，导致索引失效。

优化方案：

不使用in语法，将sql语句简化成下面这种，索引就生效了

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

但是这样一次只能查询一条data_id匹配的数据，这就意味着程序要和数据库交互1000次，但是我测试的速度要快于上面的in方式。

进一步优化，减少数据库交互方式，使用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

 程序中对dataId的参数进行组装，这样只和数据库交互一次，索引也不会失效，这种方式解决了in查询慢的问题。

对于delete也可以使用类似的方式优化：

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
)