mysql表连接和子查询效率_mysql left join 与子查询的性能比较 例子

介绍：

使用到了employees的两个表，分别是员工表(employees， 30w24数据)，部门经理表(dept_manager 24数据)，两张表都有emp_no，员工编号字段，并且设置了主键索引。

要求查询所有非经理的员工数据：

左连接

select e.* from employees e LEFT JOIN dept_manager d on e.emp_no = d.emp_no where d.emp_no is null

执行结果：0.651s

执行计划：

子查询

select * from employees e where e.emp_no not in (select emp_no from dept_manager )

执行结果：0.260s

执行计划：

===============================================

不像一般讨论的那样，这个场景子查询性能更好，但是具体原因只是看执行计划体现不出，只感觉Extra列可能是重要原因。

实际分析涉及到了Mysql内部实现原理了，咱目前的水平不足，无法解释。

先放一波猜测记录，若未来能力够了再回来验证。

「

子查询的子部分非常快，查询的结果作为外层where条件，使得外层可以走索引，实际并没有遍历30W数据，即：

1. 查询子表所有数据(24条)(非常快，单独执行0.032s，在子查询中应该会增加耗时)

2. 返回子表的主键(24条)(非常快)

3. 用子表返回的主键 索引查找外层数据( < 0.23s )

而

左连接SQL 由于必须连接后才能过滤右侧为空的数据，导致遍历了1遍的左表，左表每行数据索引1次右表，相当于至少遍历了2次左表

1. 遍历左表所有数据 ( < 0.2s )

2. 所有左表数据关联右表( 这部耗时最多，总 < 0.6s，两倍于步骤1)

3. 遍历左表过滤非空 ( 非常快)

」

在贴吧看到某个贴后有感而发，测了下。