本文探讨了SQL中IN子查询相对于=操作符或IN(value_list)操作符在性能上的不足，特别是在子查询返回少量行但外部查询返回大量行时。这种情况会导致查询执行时间从线性增长到二次方。通过例子展示了如何将不相关子查询重写为相关子查询，从而解释了性能下降的原因。建议在适当情况下使用IN(value_list)代替IN子查询以提高效率。
Subquery optimization for IN is not as effective as for the = operator
or for the IN(value_list) operator.
A typical case for poor IN subquery performance is when the subquery
returns a small number of rows but the outer query returns a large
number of rows to be compared to the subquery result.
The problem is that, for a statement that uses an IN subquery, the
optimizer rewrites it as a correlated subquery. Consider the following
statement that uses an uncorrelated subquery:
SELECT … FROM t1 WHERE t1.a IN (SELECT b FROM t2);
The optimizer rewrites the statement to a correlated subquery:
SELECT … FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);
If the inner and outer queries return M and N rows, respectively, the
execution time becomes on the order of O(M×N), rather than O(M+N) as
it would be for an uncorrelated subquery.
An implication is that an IN subquery can be much slower than a query
written using an IN(value_list) operator that lists the same values
that the subquery would return.
扫一扫
126
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
