left join on 和 where

测试是on 先执行还是 where 先执行？
表 t1 id 是主键

mysql> select * from t1;
+----+-------+------+
| id | desc  | t2Id |
+----+-------+------+
|  1 | hello |    1 |
|  2 | frank |    2 |
|  3 | lucy  |    3 |
|  4 | mini  |    4 |
|  5 | cat   |    5 |
+----+-------+------+

表 t2 id 是主键

mysql> select * from t2
;
+----+------+
| id | desc |
+----+------+
|  1 | test |
|  2 | how  |
|  3 | are  |
|  4 | you  |
|  5 | what |
|  6 | is   |
|  7 | the  |
+----+------+

通过 left join

mysql> EXPLAIN
SELECT * from t1
LEFT JOIN t2 on t1.t2Id = t2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |    5 |      100 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | river.t1.t2Id |    1 |      100 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+

如果加上 where 语句呢，查找 t1.id = 1 的数据，看看先是进行了 join on 操作生成临时表后，再进行where 筛选吗？（网上给的思路都是这样）

mysql> EXPLAIN SELECT * from t1 LEFT JOIN t2 on t1.t2Id = t2.id where t1.id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |      100 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |      100 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

可以看到分析的结果 t1 表的 rows是 1 ，看来只用到了一行，说明是先进行了 where 筛选再进行了 join 操作，这样也符合优化的逻辑。不然临时数据会有大量的无用数据。

left join on 和 where

推荐阅读更多精彩内容