MySql-join实战

简述

Join语句在Mysql多表联查的使用中非常广泛，通常来说，开发人员的意识中都会觉得Join的查询效率比子查询要高，但是如果滥用时，Join的效率并不如人意。

Index Nested-Loop Join

首先看一个sql语句：

select * from t1 straight_join t2 on t1.id = t2.id

这里直接使用straight_join指定t1作为驱动表，t2作为被驱动表。

执行流程：

1. 从t1中读入一行数据R；
2. 从R中取出a字段去t2表中查找；
3. 取出t2中满足条件的行，跟R组成一行，作为结果集的一部分；
4. 重复执行1~3，直到表t1的末尾循环结束。

在这个流程里：

1. 扫描t1时候时全表扫描n；
2. a字段去t2表中搜索走的树搜索m，总的扫描行数时n+m；

如果将join改成单表查询：

select * from t1;
select * from t2 where a=$R.a

1. 查出t1所有数据n；
2. 循环遍历n行数据；
3. 从每一行取出a，到t2中去查；
4. 把返回的结果和R构成结果集的一行。

扫描行数也是m+n，但是客户端要多发100条语句，同时自己拼接语句和结果。

可以看到在INL中，驱动表都是全表扫描n；被驱动表需要先扫描索引a（假如用到了索引），再搜索主键索引
，每一个索引树所花费的时间都是log₂M，所以总的是log₂M*2*N+N，可以看出N对于时间复杂度的影响要更大。

Simple Nested-Loop Join

select * from t1 straight_join t2 on (t1.a=t2.b)

由于t2的字段b上没有索引，所以不会再走树搜索，而是会全表扫描并且每一都需要做判断，次数为m*n，这样效率就会很低，也就是SNLJ算法。

Block Nested-Loop Join

如果被驱动表t2的条件字段没有索引，那么流程就成了：

1. t1的数据读入线程内存join_buffer中，由于这里是select * ，因此整个t1放入了内存；
2. 扫描t2，把表t2中每一行取出来，跟join_buffer中数据做对比，满足join条件的，作为结果集的一部分返回。
   由于会对t1和t2都做一次全表扫描，因此总的扫描行数是m+n，对于表t2中的每一行，都需要与t1中的每一行做一次判断，因此总的是m*n次判断，但是由于是内存的join_buffer里的判断，所以速度很快，性能很好。

join_buffer的大小是由参数join_buffer_size设定的，默认是256k，如果放不下表t1的所有数据就会采取分段放的策略，这样执行过程就变成了：

1. 扫描表t1，顺序读取数据行仿佛join_buffer中，放完某一行join_buffer满了，执行第2步；
2. 扫描表t2，把t2中的每一行取出来，跟join_buffer中数据对比，满足join条件的，作为结果集的一部分返回；
3. 清空join_buffer；
4. 继续扫描表t1，读取第一步该行后面的行放入到join_buffer中，继续执行第2步。

假设驱动表的数据行数是N，需要分K段才能完成算法流程，被驱动表的数据行数是M；N越大，需要分的段数K就越大，所以这里把K表示为u*N,那么在这个过程中：

1. 扫描行数是N+u*N*M；
2. 内存判断N*M次。

结论是：N对时间的影响更大，所以N小一些更好，即驱动表小一些更好。

对于小表的定义：

select * from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=50;
select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;

第一个语句是t1作为驱动表，但是条件里是t2.id，所以第一个语句t2是小表；

select t1.b,t2.* from  t1  straight_join t2 on (t1.b=t2.b) where t2.id<=100;
select t1.b,t2.* from  t2  straight_join t1 on (t1.b=t2.b) where t2.id<=100;

t2表数据和t1表数据相差不多的情况下，由于t1只查一个字段，而t2需要查所有字段，所以第一个语句使用t2做驱动表时，需要放入join_buffer_size的字段就越多，那么能够放入的数据就越少，所以t1是小表2。

准确说：决定驱动表的时候，应该是按照两个表各自的条件过滤，过滤完成以后，计算参与join的各字段的总数据量，数据量小的表，就是“小表”。

join优化

Muti-Range Read

在查询的时候经常会回表，而回表需要首先在普通索引上找到主键，然后根据主键索引一条条查询整行数据，MRR优化就是将需要查询的数据的id顺序排列然后依次查询，这样对磁盘的读就变成了顺序读，提升读性能。

如果想要稳定地使用read_rnd_buffer_size，根据官方文档，需要设置optimizer_switch="mrr_cost_based=off"

流程：

1. 根据普通索引，找到id，放入到read_rnd_buffer中；
2. 将read_rnd_buffer中的id进行递增排序；
3. 排序以后到主键索引中依次查找记录，并作为结果返回。

Batched Key Access

对NLJ的一种优化：join_buffer在NLJ中没有用上，所以5.6引入这个优化算法，用上了join_buffer，将驱动表里的数据尽可能多地取出放到join_buffer中，原来地NLJ是一行一行读取。
首先需要开启一个设置:set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';BAK算法依赖于MRR。

总结

能够使用索引时，可以使用join；

使用join尽量用小表做驱动表；

不能使用被驱动表索引的语句尽量不要用。