【笔记】记一次mysql IN 大量ID优化方案

背景

基于某些业务开发过程中，会遇到这样的查询语句：SELECT * FROM table where id in(1, 2, 3)， 但是当IN里面的数据量非常大的时候，有些数据库会对IN 的数据量有大小限制，比如Oracel大小限制是1000，因此如果能有一种替代IN 大量数据，并且性能还不错的方案就好了。

实战

1、使用IN方案查询结果，查询时间为：0.078s，总数据量60多万。

select * from designmaterial ds where ds.materialid in(
“00010448”,
“00010450”,
“00010454”,
“00010455”,
“00010457”,
“00010460”,
“00010466”,
“00010470”,
“00010474”,
“00010475”,
“00010477”,
“00010484”,
“00010487”,
“00010489”,
“00010491”,
“00010492”,
“00010493”,
“00011018”,
“00011019”,
“00011020”,
“00011071”,
“00011072”,
“00011073”)

2、改用替代方案，查询时间为：0.053S左右，总数据量60多万

SELECT *
FROM (
SELECT “00010448” cid UNION ALL
SELECT “00010450” UNION ALL
SELECT “00010454” UNION ALL
SELECT “00010455” UNION ALL
SELECT “00010457” UNION ALL
SELECT “00010460” UNION ALL
SELECT “00010466” UNION ALL
SELECT “00010470” UNION ALL
SELECT “00010474” UNION ALL
SELECT “00010475” UNION ALL
SELECT “00010477” UNION ALL
SELECT “00010484” UNION ALL
SELECT “00010487” UNION ALL
SELECT “00010489” UNION ALL
SELECT “00010491” UNION ALL
SELECT “00010492” UNION ALL
SELECT “00010493” UNION ALL
SELECT “00011018” UNION ALL
SELECT “00011019” UNION ALL
SELECT “00011020” UNION ALL
SELECT “00011071” UNION ALL
SELECT “00011072” UNION ALL
SELECT “00011073”
) AS tmp,designmaterial t
WHERE tmp.cid = t.materialid;

总结

经过mysql数据库大量测试结果替代方案比IN语句快了不少，特别是数据量比较大的时候，所以以后如果遇到IN查询过程可以使用该替代方案。