首先在mysql中发生了什么:join或where [英] What happend first in mysql: join or where

问题描述

假设我有两个表A和B以及以下查询:

Let's say I have two tables A and B and the following query:

select *
from A
inner join B on A.id = B.id
Where A.id = 5

mysql是先执行联接还是在哪里执行?

Does mysql first performs the join or the where?

例如，如果A包含1000行，那么在where条件之后它将仅包含1行. 在1行表上执行联接的效率要高得多，因此似乎首先执行where，然后再执行联接才更有效率.

Cause if for example A contains 1000 rows, after the where condition it'll contain only 1 row. Performing join on a 1 row table is much more efficient so it seems like performing the where first and only then the join is more efficient.

推荐答案

join发生在where之前，但是...

The join happens before the where, however...

where子句是联接返回的所有行的过滤器，但是优化器将识别出，如果A.id上存在索引，它将用于从A检索匹配的行，然后将发生联接，然后从理论上说where子句将过滤结果，但是优化器将再次认识到该条件已经满足，因此它将跳过它作为过滤器.

The where clause is a filter for all rows returned by the join, but the optimizer will recognise that if an index exists on A.id, it will be used to retrieve rows from A that match, then the join will happen, then theoretically the where clause will filter the results, but again the optimizer will recognise that the condition will already be met so it will skip it as a filter.

总而言之，优化器将始终返回与没有优化器时返回的结果相同的结果.

All that said, the optimizer will always return the same result as would be returned without the optimizer.

这篇关于首先在mysql中发生了什么:join或where的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！