MYSQL的join操作

现在的业务中不能避免的会使用联表查询的操作， MYSQL中对于联表查询也有自己的优化的。

• 在联表查询的时候，联表的字段一定要创建索引，
• 联表查询的时候选择小表作为驱动表， left join和right join 要按照语义设置驱动表。

• select * from t t1 right JOIN t_user t2 on t2.id =t1.id 这个语句中 t2表示驱动表
• select * from t t1 right JOIN t_user t2 on t2.id =t1.id 这个语句中t1表示驱动表

Block Nested-Loop Join

驱动表就是联表中把自己的记录取出来， 在把每条记录去被驱动表中查找关联记录组装结果返回。
在联表查询的时候， MYSQL会优化的， 会自己选择合适的驱动表的。

1. 把驱动表加载到内存中Buffer pool
2. 遍历每一条被驱动表记录，并把这个记录拿到内存中去比对， 符合的就组装好作为一条结果返回。

整个操作中，会对两个表都会做一次全表扫描， 扫描此时是M + N . 还会在内存中进行N* M 次内存比对， 内存中的操作耗时可以忽略。

还有一种情况， 如果驱动表无法一次性加载到buffer pool中怎么办。 会分批次操作的，会把驱动表加载一部分到内存中， 然后进行上面的1和2两步操作，返回部分结果。 之后在加载驱动表的另外的批次加载到内存中重复进行上面操作， 直到所有的数据执行完成。

分批次后， 算法的扫描次数是 N +（N / P） * M. 其中r是比例， P 代表内存能一次装入的记录数， 所以P 是越大越好。
当然内存操作也是会进行N* M次

缺点：

1. 分批次扫描被驱动表， 十分占用IO资源
2. 分批次扫描会把被驱动表的数据 推到缓存buffer pool的yuang区域， 致使本来应该呆在yuang区域的数据移动到old区域， 使其被淘汰。 就是影响了缓存中数据的命中率。
3. 会进行N*M次的内存匹配，如果表大， 会导致cpu占用过大

Multi-Range Read 优化

我们在使用普通索引查询整条记录的时候， 会使用到回表操作， 这个很难避免，好在回表也是根据id从主键索引中查找的， 速度本来就很快， 但是还是有优化空间的。

如果表的主键id是使用自增的， 那么主键索引中的数据页也是在磁盘中顺序存储的， 我们知道磁盘的特点就是顺序读写比较快， 随机读写慢。

由于普通索引的顺序和主键索引的顺序不一定是对应的， 这就会导致在回表的时候是id乱序查询主键索引。 MRR优化就是改变这种乱序情况的。

1. 先从普通索引中，查询出一批的id， 并放入到read_rnd_buffer缓存中
2. 在缓存中对id进行排序，再根据排序后的id去主键索引上回表操作

现在的MYSQL更倾向于不使用MRR. 如果需要稳定开启则要设置
set optimizer_switch="mrr_cost_based=off"

使用临时表优化join操作

如果A表示驱动表， B表示被驱动表， B表中有1000w条记录， 在进行join 查询的时候只是连接的B表的200条记录。 语句是这样的: selec * from A a inner join B b on a.id = b.id where b.id > 0 and b,id <=200

由于BNJ算法是

1. 把A驱动表的数据存入到buffer pool缓存中,
2. 再遍历把B表的每一行拿到内存中区比较找到符合id的联表记录
3. 拿到第二步符合id连接的记录， 在判断where条件， 符合where条件的记录返回。

在这个例子中如果使用BNL算法完全是浪费的，此时就可以先使用查询条件 select * from B where b.id>0 and b.id<=200创建临时表，在使用临时表来进行连接查询。