sql - DBMS 优化器 - 最佳执行计划，无论查询的公式如何

Question

如果在关系 DBMS 中编写查询 Q，优化器是否会选择最佳执行方式(取决于多种因素)，而不管 Q 如何制定？我对 SQL Server 和 Oracle 很好奇。

例如，设 Q 为:

SELECT * 
FROM t1, t2
WHERE t1.some_column = t2.some_column

如果存在正确的索引(具有正确的选择性)，我们应该会看到索引查找之后可能是键查找。我们不会看到在执行计划中进行选择的叉积。

那为什么https://technet.microsoft.com/en-us/library/ms189575(v=sql.105).aspx声明“在 Transact-SQL 中，包含子查询的语句与不包含子查询的语义等效版本之间通常没有性能差异。但是，在某些必须检查是否存在的情况下，连接会产生更好的性能。”无论您如何编写查询 Q，无论 Q 的查询类(SPJ、SPJ + UNION、SPJ + 子查询等)，优化器都不会找到最佳的语义等效版本吗？

谢谢!

Answer 1

won't the optimizer choose the best way to execute it (depending on multiple factors) no matter how one formulates Q?

我想引用这本书中的 Itzik Ben-Gan 的话:Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

There are several reasons for this.

For one, SQL Server’s optimizer is not perfect. I don’t want to sound unappreciative—SQL Server’s optimizer is truly a marvel when you think of what this software component can achieve. But it’s a fact that it doesn’t have all possible optimization rules encoded within it.

Two, the optimizer has to limit the amount of time spent on optimization; otherwise, it could spend a much longer time optimizing a query than the amount of time the optimization shaves off from the run time of the query.

The situation could be as absurd as producing a plan in a matter of several dozen milliseconds without going over all possible plans and getting a run time of only seconds, but producing all possible plans in hopes of shaving off a couple of seconds might take a year or even several. You can see that, for practical reasons, the optimizer needs to limit the time spent on optimization.

Based on factors like the sizes of the tables involved in the query, SQL Server calculates two values: one is a cost consid- ered good enough for the query, and the other is the maximum amount of time to spend on optimization before stopping. If either threshold is reached, optimization stops, and SQL Server uses the best plan found at that point.

综上所述语句优化的少，没优化的少