提高百万级SQL数据库in查询速度的方法

little-key

此方法只适用于在B表（百万条记录）中查询包括A表记录（有一定限制，见下文）的操作

表A：临时表，只有一个索引文本字段，一般不到1000条记录，主要用于比对。

表B：目标表，我这有800万条记录，3个索引文本字段

经常要查询表B中包含表A的记录，我用了4种方法

方法一：一般人喜欢用的IN语句

select * from 表B where 字段 in (select 字段 from 表A)

这个速度可想而知，我前年买的主流配置电脑，如果表A有200条以上记录，基本上要等近一分钟，如果超过1K，估计就当机了（曾经等过5分钟，电脑一直在读，CTRL+BREAK都停不了，直接结束进程）

但是此方法如果反用则很快，select * from 表A where 字段 in (select 字段 from 表B)只要3秒。说明小表里找大表记录用in还是可以的。

方法二：用exists

select * from 表B where exists(select 字段 from 表A where 表B.字段=表A.字段)

比方法一快点，表A超过1K记录基本也当机。

方法三：用left join

select * from 表B left join 表A on 表B.字段=表A.字段 where 表B.字段<>表A.字段

速度提升比较明显，表A中有2K条记录时，要45秒左右。

方法四：用IN，这个IN非方法一的IN，是select * from table where field in ("t1","t2","t3"...)的IN

这个过程稍微复杂，但速度最快，基本上3秒搞定，时间多花在取字符串上去了，另外由于我是动态加载表，表中各字段类型又不一致，所以我SQL语句中的字段前加了CVAR，这个可能也影响速度。

先将表A中的字段取成字符串，做成("值1“,"值2“,"值3"...)

后面大家应该明白了。

测试了一下，这个和记录数没有关系，但和字符串长度有关，我在ACCESS里检测，发现整个SQL语句最长不能超过32717，就是说in后面的括号里差不多不能超过32000吧，这个问题目前还没有解决。

不过我只是测试中在表1中插入了5K条记录，取成字符串后长度一度达到10万多，真正比对时一般没有这么多数据。

说到最后，无非就是说查询里最快的应该是select * from t where f in ("t1","t2"...)，这个可能很多人都知道，但是能把表字段取成这种样式，我如果不百度还真不一定想到，希望对程序员们有点帮助。

该贴已经同步到 little-key的微博

ae9480

总结的很好，感谢分享！

lhpyyh

学习了，谢谢

chentx

在数据库操作上一般都会尽量避免用IN和EXISTS，因为这会涉及到全表扫描，最好还是在关键字上用JOIN联结，当然，还需要对关键字段做好索引，你的第4种方法对真正的数据库使用就略显笨拙。

amazeyeli

[code=sql]SELECT * FROM A AS A
WHERE NOT EXISTS
(SELECT * FROM B AS B WHERE NOT EXISTS
(SELECT * FROM A AS C
WHERE A.ID = B.ID AND B.ID = C.ID))
[/code]

这个呢

amazeyeli

写错了,第一个A改成B
SELECT * FROM  B AS x
WHERE NOT EXISTS
(SELECT * FROM B AS y WHERE NOT EXISTS
(SELECT * FROM A AS z
WHERE x.ID = y.ID AND y.ID = z.ID))

amazeyeli

[code=sql]select * from test2 as a
where not exists
(select * from
(select * from test2 as b
where not exists
(select * from test3 as c
where b.id=c.id)) as d
where a.id=d.id)[/code]

amazeyeli

[code=sql]select * from test2 as a
where not exists
(select 1 from
(select * from test2 as b
where not exists
(select 1 from test3 as c
where b.id=c.id)) as d
where a.id=d.id)[/code]

xing1118

对大型的数据分析是有必要研究效率问题

chentx

百万级数据库用你这样的方法就死定了。