LEFT JOIN（左连接）消除示例

示例

原始查询基于table1、table2和table3的LEFT JOIN得到最终的记录条数，该查询的执行时间为7.5秒。

EXPLAIN
SELECT count(*)
FROM `table1` `sc`
  LEFT JOIN `table2` `ca` ON `sc`.`car_id` = `ca`.`id`
  LEFT JOIN `table3` `co` ON `sc`.`company_id` = `co`.`id`;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | sc    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                  |    2 |   100.00 | NULL        |
|  1 | SIMPLE      | ca    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | je_test.sc.car_id     |    1 |   100.00 | Using index |
|  1 | SIMPLE      | co    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | je_test.sc.company_id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+

上述查询中，table1对于table2和table3的连接条件中都使用了表的主键，保证了输出的唯一性。且在投影列中没有引用table2和table3的任何表列，因此LEFT JOIN可以被安全的消除。
变换后的查询：

EXPLAIN
SELECT count(*)
FROM `table1` `sc`
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | sc    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

通过上述查询可以看到，在消除table2和table3后，只需要查询table1的记录。可以极大的节省计算资源，提升执行效率。该查询的执行时间是0.1秒，相比原查询提升了75倍。

相关文档

LEFT JOIN（左连接）消除