连接中的条件顺序是否会影响查询性能?

I'm writing some SQL and a question appeared in my mind.

我正在写一些SQL语句，脑海中出现了一个问题。

SQL is like this:

SQL是这样的:

select
    t1.column1,
    t2.column2
from
    table1 t1
    inner join table2 t2
    on  t1.keyA = t2.keyA
where
    t1.name = 'kim kardashian'

Does the order in on clause affect SQL performance?

on子句中的顺序会影响SQL性能吗?

on t1.keyA = t2.keyA and on t2.keyA = t1.keyA is no difference?

在t1。凯亚•= t2。凯亚•t2。凯亚•= t1。凯亚•没有区别吗?

Result of the SQL is same regardless the order.

无论顺序如何，SQL的结果都是相同的。

3 个解决方案

#1

The order of conditions in the on clause should not affect performance. Why not? At a high level are three steps to SQL execution:

on条款中的条件顺序不应影响性能。为什么不呢?在高级水平上，SQL执行有三个步骤:

Parse the query
解析查询
Construct and optimize the "executable" code
构建和优化“可执行”代码。
Execute the code
执行代码

The second level optimizes the query and should take into account different methods of executing the query. The join conditions are part of this optimization -- all at once.

第二层优化查询，并应考虑执行查询的不同方法。连接条件是这个优化的一部分——全部同时进行。

In theory, it does not matter what the order of the joins are either, although in a very complex query, it could matter.

在理论上，连接的顺序也不重要，尽管在一个非常复杂的查询中，它可能很重要。

#2

In the case of an inner join, only records matching on both sides of the join will be retained. A logical consequence of this is that the join order does not affect the result set. With most databases, the optimizer will therefore choose the join order with the best performance. To your direct question, if you examine the query plan, you could very well see either table1 or table2 on the left side of the join.

对于内部连接，只保留连接两边匹配的记录。这样做的逻辑结果是，连接顺序不会影响结果集，因此对于大多数数据库，优化器将选择性能最好的连接顺序。对于您的直接问题，如果您检查查询计划，您很可能会在连接的左侧看到表1或表2。

#3

No, it doesn't. Query optimizer will transform your code anyway. You better choose a convention and go with it. I prefer to write the ID of the joined table first, because it is more fluid for readers.

不,它不是。查询优化器将改变您的代码。你最好选择一个惯例，然后就去做。我更喜欢先写出连接表的ID，因为它对读者来说更灵活。

#1