T/SQL 效率和执行顺序
关于SQL语句的执行顺序，以下性能方面有什么区别吗？
SELECT * FROM Persons
WHERE UserType = 'Manager' AND LastName IN ('Hansen','Pettersen')
并且：
SELECT * FROM Persons
WHERE LastName IN ('Hansen','Pettersen') AND UserType = 'Manager'
如果有任何区别，是否有一个链接等可以让人们了解更多信息？
非常感谢，
凯尔
In regards to the order of execution of statements in SQL, is there any difference between the following performance wise?
SELECT * FROM Persons
WHERE UserType = 'Manager' AND LastName IN ('Hansen','Pettersen')
And:
SELECT * FROM Persons
WHERE LastName IN ('Hansen','Pettersen') AND UserType = 'Manager'
If there is any difference, is there perhaps a link etc. that you may have where one can learn more about this?
Thanks a ton,
Kyle
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
在 SQL Server 2000 中存在差异。我对这个问题很头疼。显然，它按照定义的顺序评估 where 子句。要指定是否希望通过索引过滤数据，我必须在 FROM 语句之后添加WITH(INDEX(MY_INDEX_NAME1,MY_INDEX_NAME2)) 子句。
In SQL Server 2000 there is a difference. I had lots of headaches with this one. Apparently it evaluates where clauses in the order they are defined. To specify whether I wanted the data filtered through an index, I had to add the WITH(INDEX(MY_INDEX_NAME1,MY_INDEX_NAME2)) clause after FROM statement.
优化器，因为它使用基于成本的优化器，将决定哪条“路线”是最好的：它将根据统计数据计算选项的成本，然后从那里开始。术语的顺序应该没有区别（尽管嵌套方式可能会有所不同）。
编辑：Oracle 曾经有 - 直到最近 - 基于规则的优化器 (RBO)，但是 现已逐步淘汰。由于 RBO 不处理统计信息，因此可以根据谓词的顺序看到查询计划的差异。
The optimizer, since it uses a cost based optimizer, will decide which "route" is best: it will cost the options based on statistics and then go from there. The order of terms should make no difference (although how you nest things, might make a difference).
EDIT: Oracle used to have - until fairly recently - a rule-based optimizer (RBO), but that has now been phased out. As the RBO didn't work off statistics, it was possible to see differences in query plans depending on the order of predicates.
不会有什么区别。您可以在 SQL Server 中提取查询，然后单击“显示估计执行计划”图标并检查两个查询的执行计划。它们应该是相同的。
There will be no difference. You can pull the query up in SQL Server and click on the Display Estimated Execution Plan icon and check the execution plan for the two queries. They should be identical.
优化器可以自由地重新排列和执行谓词，因为它发现了最多的谓词
高效/经济有效地检索数据。
WHERE 子句的条件顺序重要吗？
The optimizer is free to rearrange and execute predicates as it finds most
efficient/cost effective to retrieve the data.
Does the order of criteria the WHERE clause matter?
我将添加以下链接。
查询计划是根据所涉及的表和索引的统计信息（给定需要执行的操作）来选择的。选择的查询执行计划是否是最好的取决于很多因素 - 但你的问题的答案是 MS SQL 无论如何都会产生相同的计划（它将考虑所有三个条件的最佳顺序，并最终找到相同的结果） ）。
但是，应该注意的是，规划器并不完美，它们仅估计成本，因此在某些情况下（如果您详细了解查询规划器的限制），您可能能够重写查询条件以帮助规划器查看更好的道路。
应该仅对被证明至关重要的查询尝试此操作（如果可能的话），并且还要注意，当数据统计信息发生变化时，这种优化可能会减慢速度。
此外，在大多数情况下，有更好的方法（更改索引）来优化查询，这应该留给查询规划者。
RDBMS 的要点之一是不指定如何检索数据（查询的声明性性质） - 在大多数情况下，当今的查询规划人员会为您找到一个好的计划。
I'll add the following link.
Query plan is selected based on statistics on the table and indexes involved (given the operations that need to be performed). Whether chosen query execution plan is the best depends on lot of factors - but the answer for your question is that MS SQL will produce the same plan regardless (it will consider the best order for all three conditions and will find the same result in the end).
However, it should be noted that planners are not perfect and that they only estimate the cost, so in some cases (providing you know how your query planner's limitations in details) you might be able to rewrite your query conditions to help the planner see a better path.
This (if even possible) should be attempted only for queries that are proven to be crucial and also note that this kind of optimization might slow things down when data statistics change.
Also, in most cases there are better ways (changing indexes) to optimize queries, and this should be left to the query planner.
One of the main points of RDBMS was not to specify how to retrieve data (declarative nature of queries) - and in most cases today's query planners will find a good plan for you.
在所有主要的sql数据库中，没有区别。
In all the major sql databases, there is no difference.
完全没有区别。两者都会产生相同的执行计划。
No difference at all. Both will result in the same execution plan.