细品mysql之Join 语句的执行过程

背景

今天优化了一个，join关联查的语句，需要优化join的语句，那我们肯定得了解他的一个执行过程。正所谓知己知彼，百战百胜！！

join的查询算法

1. Simple Nested-Loop Join（简单的嵌套循环连接）

• 简单嵌套循环算法的查询过程是嵌套查询，这个关联查询语句首先不能确定那个是驱动表，因为使用join的话，mysql的优化器会自己进行索引的选择（这也时一般情况下DBA不让join查询的原因之一）。如果a 和 b字段 在都没有索引的情况下就会出现这种算法查询。
• 查询过程：先在t1表中将符合条件的字段a一条查出来然后遍历t2表遍历循环。（但是在mysql中并没有使用到这个算法）

在这里插入图片描述

select * from  t1 join t2  on t1.a = t2.b

2. Index Nested-Loop Join（索引嵌套循环连接）

• 在使用了straight_join的意思就是我们明确指出t1是驱动表，t2被驱动表。
• 查询过程：从t1中拿出一条数据，然后再从t2中使用索引b进行匹配，如果b是个覆盖索引且包含我们所需要的的字段那这就不用进行回表查询, 但是如果这些字段没有包含全部，那这就得再进行一次回表查询。那如果驱动表的字段有索引的话，那查询的算法是否一样呢。其实也是一样的只不过是驱动表先查走索引，然后再去扫描被驱动表。
• 那么上面的两种情况我们应该如何选择那个为驱动表呢，有索引的还没索引的表呢？我个人觉得这还真不一定，如果一个表只是作为查询的条件而不要表的字段且这个表有关联字段的索引

在这里插入图片描述

select * from  t1 straight_join t2  on t1.a = t2.b（t2中的表字段有索引）

3. Block Nested-Loop Join（缓存块嵌套循环连接）

• 刚说的 Simple Nested-Loop Join 算法在MySQl中没有使用，那要是两张表的关联字段都没有使用索引的话，那mysql是如何处理的呢？那就是使用Block Nested-Loop Join这个算法
• 查询过程：把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存；扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

在这里插入图片描述

• 但是这个joinbuffer 的大小是有限的，当这个joinbuffer放满了不能一次性完成查询的时候的策略是进行多次查询
  1. 扫描表 t1，顺序读取数据行放入 join_buffer 中，放完第 88 行 join_buffer 满了，继续第 2 步；
  2. 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；清空 join_buffer；
  3. 继续扫描表 t1，顺序读取最后的 12 行数据放入 join_buffer 中，继续执行第 2 步。
  4. 就这样循环进行得到最后的结果集返回。

4. Batched Key Access

• NLJ 算法是先从驱动表读出一行的数据，再去被驱动表去匹配数据。但是要是两张表的数据量太大的时候就会出现性能问题。数据库的算法优化中有一个MRR优化，其核心思想是进行顺序读，这个顺序读能快的原因就是，mysql索引的存储方式是以数据页的形式，每个数据页的大小是16kb，可以算一下能存储的数据有多少，如果你是顺序读的话，那就会减少数据页间的切换。也就是减少的IO操作了。不用多次进行访问磁盘能提高不少的效率。那要是能让NLJ能进行顺序读，且能进行批量匹配。那这不就是会快的起飞吗？
• 这个时候BKA算法来了，这个算法是在数据库版本在5.7以后出现的，也就是对BNL算法的优化版本，查询过程是批量读出驱动表的数据存入buffer中再者进行批量匹配（且这个关联id是排好序的）,然后进行批量匹配查询。

4. 驱动表的选择

• 为了高效使用上面所提到的三种join算法，这就涉及到了驱动表的选择。
• 如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；如果是 Block Nested-Loop Join 算法：在 join_buffer_size 足够大的时候，是一样的；在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。
• 所以一般遵循的规则就是选择小表作为驱动表，这里的小表并非是指数据量小的表，而是在进行where条件后进行join buffer里面的数据量少的表。

5. 日常使用

• 其实在我们平常的关联查询中，一般都是使用的是主键索引与另一个表的唯一索引做关联的，所以使用到的关联查询都是有索引的所以说大都是使用的是Index Nested-Loop Join（5.6版本之前）或者 BKA。所以在平时优化的时候主要还是看where条件。而并非是性能是浪费在了表关联上。我们在查询驱动表的时候直接过滤掉了一大部分，然后有根据主键id去直接查，这join性能能差吗？所以平时的优化还是主要是驱动表的选择和驱动表的查询性能。驱动表选择小表，驱动表的索引尽量的ok。

总结

1. 讲了四种算法的大概查询过程
2. mysql 3种查询算法，有索引 BKA（join buffer） NLJ 没有索引 BLN （join buffer）
3. 驱动表的选择，选择小的驱动表（参与joinbuffer数据量少的）
4. 日常优化join SQL主要还是驱动表的选择和驱动表的索引优化