这两个查询之间会有性能差异吗？
1)
select * from student_tbl A ,result_tbl B where
A.student_name = B.student_name and
A.student_name = "xyz" ;
2)
select * from student_tbl A ,result_tbl B where
A.student_name = "xyz" and
A.student_name = B.student_name ;
我知道这些查询的结果是相同的。 有性能差异吗？如果是这样，请解释原因。
1)
select * from student_tbl A ,result_tbl B where
A.student_name = B.student_name and
A.student_name = "xyz" ;
2)
select * from student_tbl A ,result_tbl B where
A.student_name = "xyz" and
A.student_name = B.student_name ;
I know that the result of these queries are the same.
Is there any performance difference? If so, please explain why.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
查询不像程序那样执行。它们不是先执行步骤 1，然后执行步骤 2 的过程。相反，它们是关于您想要的结果的声明性语句。在大多数现代 RDBMS 中，任何给定的查询都可以通过许多不同的查询计划来执行。通常，会创建不同的查询计划，然后评估哪个计划运行速度最快。在创建一组查询计划时，它将考虑诸如应首先评估哪些条件、是否应在评估条件之前或之后进行连接以及其他类似的事情，以尝试找出最快的（基于其对以下方面的了解）表格大小以及对给定条件下将包含表格的百分比的猜测）。他们中的许多人还会查看以前的结果，以便在他们的近似值错误时为未来的决策提供信息。
最有可能的是，在任何现代 RDBMS 中，这两个查询将生成相同的查询计划集，因此将做出相同的选择，从而导致为两个查询执行相同的查询计划。根据您使用的 RDBMS，通常有一些工具可用于查看为给定查询选择的特定查询计划，因此您可以使用它来绝对回答特定数据库上两个特定查询的问题。
现在，我应该指出，这并不等于说“任何两个对相同数据总是产生相同答案的查询总是花费相同的时间”。有可能编写出非常糟糕的查询，主要是由于不必要的复杂性，并且不能保证查询规划器会意识到您已经做得过头了。它可能会捕获简单的情况。因此，例如：
也可能会产生相同的查询计划。这也可能是：
但是如果你做了一些非常复杂的事情，比如
它可能会运行更复杂的查询计划。 （尽管如此，完全不必要的复杂查询将产生与其他两个相同的结果（假设没有 NULL））。
因此，优化器不是无所不知的，但他们确实倾向于认识到 X AND Y 与 Y AND X 相同，并且 A=B AND B=C 与 A=C 和 A=B 相同，并进行相应调整对于那些情况。他们实际上会进行各种转换来尝试找到最佳查询，并且通常非常擅长找到它。可以覆盖查询规划器的决策，但是只有当您确定有更好的查询方法并且数据更改不太可能改变这一点时才应该这样做。
Queries are not executed like programs. They are not procedures which do step 1 and then step 2. Instead, they are declarative statements about what results you want. In most modern RDBMS's any given query can be executed via a number of different query plans. Generally, different query plans are created and then evaluated for which plan will run fastest. In creating the set of query plans, it will consider things like which conditions should be evaluated first, whether joins should be done before or after evaluating conditions and other things like that to try to figure out which will be fastest (based on its knowledge of the table sizes and guesses about what percentage of a table will be included in a given condition). Many of them also look at previous results to inform future decisions for when their approximations are wrong.
Most likely, in any modern RDBMS, those two queries would generate the same set of query plans, and hence the same choice would be made, resulting in the same query plan being executed for both queries. Depending on which RDBMS you are using, there are generally tools available to look at the particular query plans which are being selected for a given query, so you can use that to answer the question absolutely for two specific queries on a particular database.
Now, saying that, I should note that this is not equivalent to saying "Any two queries which will always produce the same answer on the same data will always take the same amount of time." It is possible to write really bad queries, mostly through needless complexity, and there's no guarantee that the query planner will realize that you've overdone it. It will probably catch simple cases. So, for instance:
will also probably produce the same query plan. And this is also likely to:
But if you do something really complex like
It may run a more complex query plan. (Even though, that completely unnecessarily complex query will produce the same results as the other two (assuming no NULLs)).
So, the optimizers are not omniscient, but they do tend to recognize that X AND Y is the same thing as Y AND X and that A=B AND B=C is the same thing as A=C and A=B and adjust accordingly for those cases. They actually do a variety of transformations to try to find the best query, and are generally quite good at finding it. It is possible to override the decisions of the query planner, but that should only be done when you're certain that there's a better way to do the query and that data changes aren't likely to change that.
这些是相同的。 where 子句中的条件表示 A.student_name 和 B.student_name 均为“xyz”。查询优化器很可能会为两者生成相同的执行计划，但您可以通过检查执行计划来检查这一点（例如，如果您使用 MS SQL Server，则在 SQL Server Management Studio 中）。
These are the same. The condition in the where clause means both A.student_name and B.student_name are "xyz". It's more than likely that the query optimizer will generate the same execution plan for both, but you can check this by examining the execution plan (for example in SQL Server Management Studio, if you use MS SQL Server).
假设您使用的是 Sql 服务器，您可以显示每个操作的执行计划并查看实际发生的情况，这将显示每个操作的成本以及查询对每个表实际执行的操作。
对于不太深入的了解，您也可以只运行查询并检查执行时间。
我怀疑这里真正的问题是“where 子句中的条件顺序是否会影响性能？”在这种情况下，您可能希望阅读这篇文章 WHERE 子句中的列顺序重要吗？
Assuming you are using Sql server you can display an execution plan for each and see what is actually happening, this will show you the cost for each operation and what the query actually does to each table.
For a less in depth look you could also just run the queries and check the execution time.
I suspect the real question here is "Does the order of conditions in the where clause affect performance?" in this case you may wish to read this SO post Does the order of columns in a WHERE clause matter?