MySQL中的join语句

MySQL中的join语法

在MySQL中，join语句想必大家都不陌生，今天我们围绕join语句展开，说一些可能平时不关注的知识点。

首先我们创建两张表，一张表叫t1，另一张表叫t2，表的结构如下：

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

其中，t1和t2的表结构一模一样，不同的是t1表里面有100条记录，而t2表里面有1000条记录。先看下面这个SQL，t1和t2进行关联：

mysql> explain select * from t1 join t2 on (t1.a=t2.a);
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref       | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | a             | NULL | NULL    | NULL      |  100 |   100.00 | Using where |
|  1 | SIMPLE      | t2    | NULL       | ref  | a             | a    | 5       | test.t1.a |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

从执行计划中我们可以看到，t1表的扫描是全表扫描的，t2表的扫描方式是ref类型，用到了普通索引a，这里我要强调的两点是：

1、在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值是相同的，出现在前边的表表示驱动表，出现在后边的表表示被驱动表

2、当驱动表没有where条件的时候，即使扫描条件中用到了索引列，扫描过程中驱动表还是会采用的全表扫描的方式，被驱动表上如果有二级索引，则二级索引可以被用到。

整个join语句的执行过程如下：

a、从表t1中拿到一条记录的字段a值

b、拿a的值去t2表中查找，查找匹配的行

c、找到结果，和表t1中的行拼接成一行记录，作为结果的一条记录

d、重复以上三个步骤，直到t1的数据全部被遍历一遍。

在这个过程中，因为t2表使用到了索引，而且执行的过程是循环执行的，所以MySQL把这种情况下的join查询称之为index Nested-Loop join。姑且简称INLJ，可以发现，因为可以使用被驱动表的索引，因此这个执行过程是非常快的。

整个过程的复杂度如下：

a、扫描表t1的所有100行记录

b、一行一行的用t1的字段a去t2中进行查找，查找的过程中会用到t2的索引，所以在t2上一共也只扫描了100行。

c、整个join连接的过程中，一共扫描了200行记录，就结束了连接查询。

这里，我们简单推一下复杂度的公式：

假设驱动表的记录为M，被驱动表的值是N，因为被驱动表使用了索引，在一棵b+树上索引的查找效率近似logN,因为我们的语句时select * ，要牵扯到回表到聚集索引查询所有字段，所以每次查询要遍历两棵树，也就是2*logN的复杂度，对于驱动表的所有记录M，被驱动表都要经过2*logN的复杂度，那么总的复杂度就是M*2*logN，再加上驱动表本身要访问M次，所以，总的扫描次数就是M+M*2logN。

显然，M对于复杂度的提升是大于N的，而M是我们驱动表的记录，这也就意味着，为了降低复杂度，M的值越小越好。也就是我们常说的，连接查询时，最好要"小表驱动大表"。

上面我们讲了INLJ算法，下面说说另外两种算法，我们知道，INLJ算法指的是被驱动表能够用上索引，通过循环的方法进行join查询的，如果被驱动表不能使用索引，通过循环的方法进行join查询的，MySQL称之为simple Nested-Loop join，简称SNLJ算法，SNLJ由于不能使用索引，所以需要扫描100*1000=10w行记录。相比INLJ，可以说比较笨重了。

如果t1和t2的数据量都比较大，应用上面的SNLJ就不合适了，举个例子，每个表都有10w数据，而且被驱动表上没有可以使用的索引，如果使用SNLJ算法，那么我们需要扫描10w*10w=100亿次！！！这肯定是不合适的，事实上，MySQL也不会这么处理，在这种数据量比较大的情况下，MySQL会使用一种叫做Block Nested-Loop join的算法（简称BNLJ）来代替SNLJ，BNLJ和SNLJ不同的地方在于：

1、BNLJ算法会将驱动表t1的记录先放在join buffer中，然后从t2上一条一条获取记录，和join buffer中的记录匹配，找到符合条件的记录放入结果集；

2、如果join buffer不够，那么就先把t1表的一部分放上去，等到循环比对完毕，清空join buffer，再把另外一部分放到join buffer中

3、虽然总的扫描行数不变，但是BNLJ的操作是在内存中进行比较的，相比SNLJ，速度上比较快，性能也比较好。

在我们使用BNLJ的时候，如果join buffer比较小，那么被驱动表就会访问多次，join buffer越大，那么被驱动表的扫描次数就越少，join的性能就越高。

最后介绍下，MySQL中通过下面的参数来控制join buffer的大小：

mysql> show variables like '%join_buffer%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set, 1 warning (0.00 sec)