索引与包含 IN 语句的 OUTER JOIN 的使用
SELECT a.*, b.*
FROM a
LEFT OUTER JOIN b
ON b.user IN (:userlist)
AND b.key = a.fk_to_b
WHERE
a.user IN (:userlist)
OR b.user IN (:userlist)
- 表 b 的索引为:(user, key)
仅当 :userlist 参数包含单个值时,数据库才使用该索引。当 :users 包含多个值(内部扩展为多个 OR 语句?)时,不使用索引并执行(b 的)表扫描。
当提供多个 :userlist 值时,为什么数据库不使用索引?
有谁知道这个查询的更优化版本?
SELECT a.*, b.*
FROM a
LEFT OUTER JOIN b
ON b.user IN (:userlist)
AND b.key = a.fk_to_b
WHERE
a.user IN (:userlist)
OR b.user IN (:userlist)
- Table b has an index of: (user, key)
The database only uses the index when the :userlist parameter contains a single value. When :users contains multiple values (which internally expands to multiple OR statements?) the index is not used and a table scan (of b) is performed.
Why isn't the database using the index when multiple :userlist values are supplied?
Does anyone know of a more optimal version of this query?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
此查询适用于所有主要系统,并且可能会更高效:
您能告诉我您使用的是哪个
RDBMS
吗?This query will work in all major systems and probably will be more efficient:
Could you please tell which
RDBMS
do you use?简单的回答是:这取决于情况。
如果您在 :userlist 中指定多个值,那么数据库服务器可能会选择以不同的方式优化查询,例如可能会选择全表扫描。
大多数时候,最好的选择是通过
为了给您提供更多帮助,我们确实需要知道您正在使用哪个数据库。
The quick answer is: It depends.
If you specify multiple values in :userlist, then the database server may choose to optimise the query in a different way, it may choose a full table scan for instance.
Most of the time, the best option is to see how the query is optimised, by doing
To help you more, we really need to know which database you're using.
IN (:userlist) 扩展为多个 OR 语句。
查询优化器忽略 OR 行/子句。
如果数据库是Oracle,该怎么做
......
:
IN (:userlist) expands to multiple OR statements.
The query optimizer disregards OR lines/clauses.
Here's what to do if the DB is Oracle:
...
...