优化:WHERE x IN(1,2 ...,100.000)vs INNER JOIN tmp_table USING(x)？

我最近参观了一个有趣的求职面试.在那里,我被问到一个关于使用WHERE..IN包含长标量列表(数千个值,即)的子句来优化查询的问题.这个问题不是关于IN子句中的子查询,而是关于标量的简单列表.

我马上回答说,这可以使用INNER JOIN另一个表(可能是临时表)来优化,这个表只包含那些标量.我的回答被接受,并且评论员发表了一条说明,"目前没有数据库引擎可以优化长期WHERE..IN条件以保证足够的性能".我点了头.

但是当我走出去时,我开始有些怀疑.这种情况似乎相当微不足道,并且广泛用于现代RDBMS,无法对其进行优化.所以,我开始挖掘了一些东西.

PostgreSQL的:

看来,PostgreSQL将标量IN()结构解析为ScalarArrayOpExpr结构,这是排序的.稍后在索引扫描期间使用此结构来定位匹配的行.EXPLAIN ANALYZE对于此类查询仅显示一个循环.没有加入.所以,我希望这样的查询比INNER JOIN更快.我在现有数据库上尝试了一些查询,我的测试证明了这个位置.但我并不关心测试纯度,而且Postgres处于Vagrant之下,所以我可能错了.

MSSQL服务器:

MSSQL Server 从常量表达式列表构建哈希结构,然后与源表进行哈希连接.我认为即使没有进行排序,这也是性能匹配.我没有做过任何测试,因为我对这个RDBMS没有任何经验.

MySQL服务器:

这些幻灯片中的第13个说,在5.0之前,这个问题确实发生在MySQL的某些情况下.但除此之外,我没有发现任何与不良IN ()治疗有关的其他问题.不幸的是,我没有找到任何相反的证据.如果你这样做,请踢我.

SQLite的:

文档页面提示了一些问题,但我倾向于认为那里描述的内容确实存在于概念层面.没有找到其他信息.

所以,我开始认为我误解了我的采访者或误用了谷歌;)或者,可能是因为我们没有设定任何条件,我们的谈话变得有点模糊(我们没有具体说明任何具体的RDBMS或其他条件)那只是抽象的谈话.

看起来很久以前,数据库重写IN()为一组OR语句(有时可能会导致NULL列表中的值出现问题,btw).或不？

当然,如果标量列表比允许的数据库协议包长,则INNER JOIN可能是唯一可用的解决方案.

我认为在某些情况下,查询解析时间(如果没有准备好)可能会导致性能下降.

此外,数据库可能无法准备IN(?)查询,这将导致一次又一次地重新解析(这可能会导致性能下降).实际上,我从未尝试过,但我认为即使在这种情况下,与查询执行相比,查询解析和规划也不是很大.

但除此之外,我没有看到其他问题.好吧,除了遇到这个问题的问题.如果您有查询,其中包含数千个ID,那么您的架构就会出现问题.

你呢？