测试 是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 操作，这样也符合优化的逻辑。不然临时数据会有大量的无用数据。
