Mysql中join连接算法和优化思路

查看SQL的explain的结果&＃xff0c;如果Extra字段里面出现“Block Nested Loop” 即这时候sql查询使用Block Nested-Loop Join算法&＃xff0c;就意味着连接表的字段没有索引&＃xff0c;这时候性能很差。

在写SQL时&＃xff0c;在join连接中应该遵循尽可能使用小表做为驱动表&＃xff0c;驱动表指的就是left join语句左边的表和right join语句右边的表&＃xff0c;如果SQL中没有指定join类型则是mysql根据表数据预估自动选择驱动表。

• 1. 如果是IndexNested-Loop Join算法&＃xff0c;应该选择小表做驱动表&＃xff1b;
• 2. 如果是Block Nested-Loop Join算法&＃xff1a; 在join_buffer_size足够大的时候&＃xff0c;是一样的&＃xff1b; 在join_buffer_size不够大的时候&＃xff08;这种情况更常见&＃xff09;&＃xff0c;应该选择小表做驱动表。

通过下面的join语句分别分析一下几种join连接算法的过程和性能 

SELECT * FROM A LEFTJOIN B ON A.ID&＃61;B.NO 

Index Nested-LoopJoin

Index Nested-LoopJoin算法是join连接最优的join算法&＃xff0c;如果使用join查询尽量使sql按照此算法执行才能保证性能。这个算法关键就在于检索过程中可以使用被驱动表的索引。

如果上面sql中的B表的NO字段是索引字段&＃xff0c;那么查询过程可以用上被驱动表的索引&＃xff0c;这时候sql查询就可以使用Index Nested-LoopJoin算法。 由于时LEFT join所以表A是驱动表。

流程如下&＃xff1a;

• 1. 查询驱动表A中符合where条件的数据放入net_buffer&＃xff0c;遍历读每一行数据 R&＃xff1b;
• 2. 从数据行R中&＃xff0c;取出NO字段到表B里去查找&＃xff0c;由于NO字段是索引所以走的是树搜索过程。NO值唯一的情况下每次的搜索过程都只扫描一行&＃xff1b;
• 3. 取出表B中满足条件的行&＃xff0c;跟R组成一行&＃xff0c;作为结果集的一部分&＃xff1b;
• 4. 如果net_buffer满了则返回客户端&＃xff0c;清空buf后重复执行步骤1到3&＃xff0c;直到表A的末尾循环结束 

如果不用join连接要实现同样的效果&＃xff0c;通常就是拆分sql查询两次的方式

1.查询SELECT * FROM A

2.遍历1中的结果取出A.ID记为id

3.使用A.ID作为条件执行SELECT * FROM B where B.NO&＃61;id

4.重复执行3查询

上面的过程与上面Index Nested-LoopJoin算法的实现区别是sql执行了多次&＃xff0c;多执行的次数取决于A表的行数。性能是不如Index Nested-LoopJoin算法的join连接的。

Block Nested-Loop Join

如果上面sql中的B表的NO字段不是索引&＃xff0c;这时候sql查询使用Block Nested-Loop Join算法。

这时候&＃xff0c;被驱动表上没有可用的索引&＃xff0c;算法的流程是这样的&＃xff1a;

• 1. 把表A的数据读入线程内存join_buffer中&＃xff0c;由于我们这个语句中写的是select *&＃xff0c;因此是把整 个表A放入了内存&＃xff1b;
• 2. 扫描表B&＃xff0c;把表B中的每一行取出来&＃xff0c;跟join_buffer中的数据做对比&＃xff0c;满足join条件的&＃xff0c;作为 结果集的一部分返回。

可以看到上面的流程相对于算法来说&＃xff0c;扫描了B表全表导致性能差了很多。

不仅如此&＃xff0c;当驱动表是大表时性能也会受到join_buffer的影响。join_buffer的大小是由参数join_buffer_size设定的&＃xff0c;默认值是256k。

如果在执行上面的sql时&＃xff0c;join_buffer中放不下表A的所有数 据话&＃xff0c;就会分段放。这时候由于表A被分成了两次放入join_buffer中&＃xff0c;导致表B会被扫描两次。

• 1. 扫描表t1&＃xff0c;顺序读取数据行放入join_buffer中&＃xff0c;放完第88行join_buffer满了&＃xff0c;继续第2步&＃xff1b;
• 2. 扫描表t2&＃xff0c;把t2中的每一行取出来&＃xff0c;跟join_buffer中的数据做对比&＃xff0c;满足join条件的&＃xff0c;作为结 果集的一部分返回&＃xff1b;
• 3. 清空join_buffer&＃xff1b;
• 4. 继续扫描表t1&＃xff0c;顺序读取最后的12行数据放入join_buffer中&＃xff0c;继续执行第2步。