MySQL join?
使用 join 有什么问题呢?
1.如果可以使用 Index Nested-Loop Join 算法,也就是说可以用上被驱动表上的索引,其实是没问题的;
2.如果使用 Block Nested-Loop Join 算法,扫描行数就会过多。尤其是在大表上的 join 操作,这样可能要扫描被驱动表很多次,会占用大量的系统资源。所以这种 join 尽量不要用。
所以你在判断要不要使用 join 语句时,就是看 explain 结果里面,Extra 字段里面有没有出现“Block Nested Loop”字样。
有两个大小不同的表做 join,应该用哪个表做驱动表呢?
1.如果是 Index Nested-Loop Join 算法,应该选择小表做驱动表;
2.如果是 Block Nested-Loop Join 算法:
- 在 join_buffer_size 足够大的时候,是一样的;
- 在 join_buffer_size 不够大的时候(这种情况更常见),应该选择小表做驱动表。
- 所以,这个问题的结论就是,总是应该使用小表做驱动表。
什么叫作“小表”?
在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与 join 的各个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表。
Index Nested-Loop Join
select * from t1 straight_join t2 on (t1.a=t2.a);
straight_join 让 MySQL使用固定的连接方式执行查询,这样优化器只会按照我们指定的方式去 join。在这个语句里,t1 是驱动表,t2 是被驱动表。
这条语句里,被驱动表 t2 的字段 a 上有索引,join 过程用上了这个索引,因此这个语句的执行流程是这样的:
- 从表 t1 中读入一行数据 R;
- 从数据行 R 中,取出 a 字段到表 t2 里去查找;
- 取出表 t2 中满足条件的行,跟 R 组成一行,作为结果集的一部分;
- 重复执行步骤 1 到 3,直到表 t1 的末尾循环结束。
在这个流程里:
- 对驱动表 t1 做了全表扫描,这个过程需要扫描 100 行;
- 而对于每一行 R,根据 a 字段去表 t2 查找,走的是树搜索过程。由于我们构造的数据都是一一对应的,因此每次的搜索过程都只扫描一行,也是总共扫描 100 行;
- 所以,整个执行流程,总扫描行数是 200。