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

迷南。 2023-01-08 10:29 80阅读 0赞

**1.看explain的结果，如果Extra字段里面出现“Block Nested Loop” 即这时候sql查询使用Block Nested-Loop Join算法，就意味着性能较差，不建议使用。**

**2.join连接中尽可能使用小表做驱动表**

>  *  1. 如果是IndexNested-Loop Join算法，应该选择小表做驱动表；
>  *  2. 如果是Block Nested-Loop Join算法： 在join\_buffer\_size足够大的时候，是一样的； 在join\_buffer\_size不够大的时候（这种情况更常见），应该选择小表做驱动表。

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

> SELECT \* FROM A INNER JOIN B ON A.ID=B.NO

# Index Nested-LoopJoin #

如果上面sql中的B表的NO字段是索引字段，那么查询过程可以用上被驱动表的索引，这时候sql查询使用Index Nested-LoopJoin算法。 这个算法关键在于**检索过程中可以使用被驱动表的索引**。

**Index Nested-LoopJoin算法是join连接最优的join算法，如果使用join查询尽量使sql按照此算法执行才能保证性能。流程如下：**

>  *  1. 从表A中读入一行数据 R；
>  *  2. 从数据行R中，取出ID字段到表B里去查找，**由于NO字段是索引所以走的是树搜索过程。NO值唯一的情况下每次的搜索过程都只扫描一行**；
>  *  3. 取出表B中满足条件的行，跟R组成一行，作为结果集的一部分；
>  *  4. 重复执行步骤1到3，直到表A的末尾循环结束

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

**1.查询**SELECT \* FROM A

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

3.使用A.ID作为条件执行SELECT \* FROM B where B.NO=id

4.重复执行3查询

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

# Block Nested-Loop Join #

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

这时候，被驱动表上没有可用的索引，算法的流程是这样的：

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

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

**不仅如此，当驱动表是大表时性能也会受到join\_buffer的影响。join\_buffer的大小是由参数join\_buffer\_size设定的，默认值是256k。**

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

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