blocks|key|410560|text|这些都是一样的。where子句中的条件表示A.student_name和B.student_name都是"xyz“。查询优化器很可能会为这两者生成相同的执行计划，但您可以通过检查执行计划来检查这一点(例如，在SQL+Server+Management+Studio中，如果您使用MS+SQL+Server)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|410561|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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).

blocks|key|412653|text|查询不像程序那样执行。它们不是先做第一步再做第二步的过程，相反，它们是关于你想要什么结果的声明。在大多数现代RDBMS中，任何给定的查询都可以通过许多不同的查询计划来执行。通常，创建不同的查询计划，然后评估哪个计划运行得最快。在创建查询计划集时，它将考虑应该首先评估哪些条件，应该在评估条件之前还是之后进行连接，以及其他类似的事情，以试图找出哪些将被加速(基于它对表大小的了解和对给定条件中表的百分比的猜测)。他们中的许多人还会查看以前的结果，以便在他们的近似错误时为未来的决策提供信息。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|412654|在任何现代RDBMS中，这两个查询很可能会生成相同的查询计划集，因此会做出相同的选择，从而导致为两个查询执行相同的查询计划。根据您使用的RDBMS，通常有工具可用于查看为给定查询选择的特定查询计划，因此您可以使用该工具为特定数据库上的两个特定查询绝对回答问题。|412655|现在，我应该注意到，这并不等同于说“任何两个总是在相同数据上产生相同答案的查询总是花费相同的时间”。可能会编写非常糟糕的查询，主要是通过不必要的复杂性，并且不能保证查询规划器会意识到您做得太过了。它可能会捕获简单的案例。所以，举个例子：|412656|SELECT+*+FROM+student_tbl+A,+result_tbl+B+WHERE+
A.student_name+=+B.student_name+AND
A.student_name+=+'xyz'+AND
B.student_name+=+A.student_name|code-block|syntax|javascript|412657|也可能会产生相同的查询计划。这也有可能：|412658|SELECT+*+FROM+student_tbl+A,+result_tbl+B+WHERE+
A.student_name+=+B.student_name+AND
A.student_name+=+'xyz'+AND
B.student_name+=+'xyz'|412659|但是如果你做一些非常复杂的事情，比如|412660|(SELECT+*+FROM+student_tbl+A,+result_tbl+B+WHERE
+A.student_name+=+B.student_name+AND
+A.student_name+=+'xyz')
UNION
(SELECT+*+FROM+student_tbl+A,+result_tbl+B+WHERE
+A.student_name+=+B.student_name+AND
+B.student_name+=+'xyz')
INTERSECT
(SELECT+*+FROM+student_tbl+A,+result_tbl+B+WHERE
+A.student_name+=+'xyz')|412661|它可能运行更复杂的查询计划。(尽管如此，这个完全不必要的复杂查询将产生与其他两个相同的结果(假设没有NULL))。|412662|因此，优化器并不是无所不知的，但它们确实倾向于认识到X和Y与Y和X是一回事，A=B和B=C与A=C和A=B是一回事，并根据这些情况进行相应的调整。他们实际上做了各种转换，试图找到最好的查询，并且通常相当擅长找到它。可以覆盖查询规划器的决策，但只有当您确定有更好的方法来执行查询，并且数据更改不太可能改变这一点时，才应该这样做。|412663|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|11|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|13|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|H|7|15|8|@]|9|@]|A|$I|J]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|17|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

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 fasted (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:

<pre><code>SELECT * FROM student_tbl A, result_tbl B WHERE 
A.student_name = B.student_name AND
A.student_name = 'xyz' AND
B.student_name = A.student_name
</code></pre>

will also probably produce the same query plan. And this is also likely to:

<pre><code>SELECT * FROM student_tbl A, result_tbl B WHERE 
A.student_name = B.student_name AND
A.student_name = 'xyz' AND
B.student_name = 'xyz'
</code></pre>

But if you do something really complex like

<pre><code>(SELECT * FROM student_tbl A, result_tbl B WHERE
 A.student_name = B.student_name AND
 A.student_name = 'xyz')
UNION
(SELECT * FROM student_tbl A, result_tbl B WHERE
 A.student_name = B.student_name AND
 B.student_name = 'xyz')
INTERSECT
(SELECT * FROM student_tbl A, result_tbl B WHERE
 A.student_name = 'xyz')
</code></pre>

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.

blocks|key|691064|text|假设您使用的是Sql+server，您可以显示每个表的执行计划并查看实际发生了什么，这将显示每个操作的成本以及查询对每个表的实际操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|691065|对于不太深入的查看，您也可以只运行查询并检查执行时间。|691066|我怀疑这里真正的问题是“where子句中条件的顺序是否会影响性能？”在这种情况下，您可能希望阅读这篇文章，以便发布Does+the+order+of+columns+in+a+WHERE+clause+matter?|offset|length|691067|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/642784/does-the-order-of-columns-in-a-where-clause-matter^0|0|0|1L|1F|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@$F|S|G|T|1|U]]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]]]

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 <a href="https://stackoverflow.com/questions/642784/does-the-order-of-columns-in-a-where-clause-matter">Does the order of columns in a WHERE clause matter?</a>

1)

<pre><code>select * from student_tbl A ,result_tbl B where
A.student_name = B.student_name and
A.student_name = "xyz" ;
</code></pre>

2)

<pre><code>select * from student_tbl A ,result_tbl B where
A.student_name = "xyz" and
A.student_name = B.student_name ;
</code></pre>

I know that the result of these queries are the same.
Is there any performance difference? If so, please explain why.

Will there be any performance difference between these 2 Queries?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

1)select * from student_tbl A ,result_tbl B whereA.student_name = B.student_name andA.student_name = "xyz" ;2)select * from student_tbl A ,result_tbl B whereA.s...

问这两个查询之间会有性能上的差异吗？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问这两个查询之间会有性能上的差异吗？EN