MySQL-join的实现原理、优化及NLJ算法
案例分析：
select c.* from hotel_info_original c left join hotel_info_collection h on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id where h.hotel_id is null
这个sql是用来查询出 c 表中有 h 表中无的记录，所以想到了用 left join 的特性（返回左边全部记录，右表不满足匹配条件的记录对应行返回 null）来满足需求，不料这个查询非常慢。先来看查询计划：
rows代表这个步骤相对上一步结果的每一行需要扫描的行数，可以看到这个sql需要扫描的行数为35773*8134，非常大的一个数字。
在EXPLAIN结果中，第一行出现的表就是驱动表。
NLJ 算法
即 Nested Loop Join，就是扫描一个表（外表，也叫驱动表），每读到一条记录，就根据 join 字段上的索引去另一张表（内表）里查找。内表（一般是带索引的表）被外表（也叫驱动表，一般为小表，不仅相对其他表为小表，而且记录数的绝对值也小，不要求有索引）驱动，外表返回的每一行都要在内表中检索与其匹配的行，因此整个返回的结果集不能太大（大于 1 万不适合）。
驱动表：就是在嵌套循环连接和哈希连接中，用来最先获得数据，并以此表的数据为依据，逐步获得其他表的数据，直至最终查询到所有满足条件的数据的第一个表。驱动表不一定是表，有可能是数据集，即由某个表中满足条件的数据行，组成子集合后，再以此子集合作为连接其他表的数据来源。这个子集合，才是真正的驱动表，有时候为了简洁，直接将最先按照条件或得子集合的那张表叫做驱动表。我们常说，驱动表一定是小表，指的是根据条件获得的子集合一定要小，而不是说实体表本身一定要小，大表如果获得的子集合小，一样可以简称这个大表为驱动表。
如果有三个及以上的表，则会先使用 NLJ 算法得到一、二个表的结果集，并将该结果集作为外层数据，遍历结果集到后第三个表中查询数据。
一个简单的嵌套循环联接（NLJ）算法，循环从第一个表中依次读取行，取到每行再到联接的下一个表中循环匹配。这个过程会重复多次直到剩余的表都被联接了。假设表t1、t2、t3用下面的联接类型进行联接：
Table Join Type
t1 range
t2 ref
t3 ALL
如果使用的是简单NLJ算法，那么联接的过程像这样：
for each row in t1 matching range { for each row in t2 matching reference key { for each row in t3 { if row satisfies join conditions, send to client } } }
因为NLJ算法是通过外循环的行去匹配内循环的行，所以内循环的表会被扫描多次。
由此可知道，on a.id = b.aid 代表着驱动表无法使用此索引，是给被驱动表用的。
BLJ 算法
即 Block Nested-Loop Join，是MySQL 自己创建的方式。将指定的外层键对应的被驱动表缓存起来以提高性能。
Join操作使用内存(join_buffer_size)：应用程序经常会出现一些两表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的时候（all/index join），为了减少参与Join的“被驱动表”的读取次数以提高性能，需要使用到 Join Buffer 来协助完成 Join操作（具体 Join 实现算法请参考：MySQL中的 Join基本实现原理）。当 Join Buffer太小，MySQL不会将该 Buffer存入磁盘文件，而是先将Join Buffer中的结果集与需要 Join 的表进行 Join操作，然后清空 Join Buffer中的数据，继续将剩余的结果集写入此 Buffer中，如此往复。这势必会造成被驱动表需要被多次读取，成倍增加 IO访问，降低效率。
for each row in t1 matching range { for each row in t2 matching reference key { store used columns from t1, t2 in join buffer if buffer is full { for each row in t3 { for each t1, t2 combination in join buffer { if row satisfies join conditions, send to client } } empty buffer } } } if buffer is not empty { for each row in t3 { for each t1, t2 combination in join buffer { if row satisfies join conditions, send to client } } }
对上面的过程解释如下：
1. 将t1、t2的联接结果放到缓冲区，直到缓冲区满为止；
2. 遍历t3，内部再循环缓冲区，并找到匹配的行，发送到客户端；
3. 清空缓冲区；
4. 重复上面步骤，直至缓冲区不满；
5. 处理缓冲区中剩余的数据，重复步骤2。
设S是每次存储t1、t2组合的大小，C是组合的数量，则t3被扫描的次数为：
(S * C)/join_buffer_size + 1
由此可见，随着join_buffer_size的增大，t3被扫描的次数会较少，如果join_buffer_size足够大，大到可以容纳所有t1和t2联接产生的数据，t3只会被扫描1次。
实例1:
mysql> show create table c; +-------+---------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------+ | c | CREATE TABLE `c` ( `id` int(11) NOT NULL, `name` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+---------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table d; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | d | CREATE TABLE `d` ( `id` int(11) NOT NULL, `score` int(11) DEFAULT NULL, `stuid` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> explain select c.id,d.score from c,d where c.id=d.stuid; +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | 1 | SIMPLE | c | ALL | NULL | NULL | NULL | NULL | 42 | | | 1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 61 | Using where; Using join buffer | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ 2 rows in set (0.00 sec)
MySQL 会根据条件选用不同的执行策略。比如说在上面的 d 和 c 表中，如果按照当前的 c 和 d 的结构，执行 explain 之后，是 c 驱动 d 表，因为 c 表较小。
那么如果在c的id上加一个index之后，mysql就会采用d驱动c表了。
【因为此时，在Nested Loop Join算法中，内部循环可以使用c表上的索引，加速执行c表的查询。内部查询每加快一点，对整个join来说都是效率上比较大的提升】
mysql> alter table c add index(id); Query OK, 0 rows affected (0.94 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select c.id,d.score from c,d where c.id=d.stuid; +----+-------------+-------+------+---------------+------+---------+--------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+--------------+------+-------------+ | 1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 61 | | | 1 | SIMPLE | c | ref | id | id | 4 | test.d.stuid | 1 | Using index | +----+-------------+-------+------+---------------+------+---------+--------------+------+-------------+ 2 rows in set (0.00 sec)
实例2：
表结构：
create table `user_group` ( `user_id` int(11) NOT NULL, `group_id` int(11) not null, `user_type` int(11) not null, `gmt_create` datetime not null, `gmt_modified` datetime not null, `status` varchar(16) not null, key `idx_user_group_uid` (`user_id`) ) engine=innodb default charset=utf8; create table `group_message` ( `id` int(11) not null auto_increment, `gmt_create` datetime not null, `gmt_modified` datetime not null, `group_id` int(11) not null, `user_id` int(11) not null, `author` varchar(32) not null, `subject` varchar(128) not null, primary key (`id`), key `idx_group_message_author_subject` (`author`,`subject`(16)), key `idx_group_message_author` (`author`), key `idx_group_message_gid_uid` (`group_id`,`user_id`) ) engine=innodb auto_increment=97 default charset=utf8; create table `group_message_content` ( `group_msg_id` int(11) not null, `content` text NOT NULL, KEY `group_message_content_msg_id` (`group_msg_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
查询：
explain select m.subject msg_subject, c.content msg_content from user_group g, group_message m, group_message_content c where g.user_id = 1 and m.group_id = g.group_id and c.group_msg_id = m.id\G
结果：
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: g type: ref possible_keys: user_group_gid_ind,user_group_uid_ind,user_group_gid_uid_ind key: user_group_uid_ind key_len: 4 ref: const rows: 2 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: m type: ref possible_keys: PRIMARY,idx_group_message_gid_uid key: idx_group_message_gid_uid key_len: 4 ref: example.g.group_id rows: 3 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: c type: ref possible_keys: idx_group_message_content_msg_id key: idx_group_message_content_msg_id key_len: 4 ref: example.m.id rows: 2 Extra:
MySQL Query Optimizer 选择了 user_group 作为驱动表，首先利用我们传入的条件 user_id 通过 该表上面的索引 user_group_uid_ind 来进行 const 条件的索引 ref 查找，然后以 user_group 表中过滤出来的结果集的 group_id 字段作为查询条件，对 group_message 循环查询，然后再通过 user_group 和 group_message 两个表的结果集中的 group_message 的 id 作为条件 与 group_message_content 的 group_msg_id 比较进行循环查询，才得到最终的结果。没啥特别的，后一个引用前一个的结果集作为条件。
如果去掉 group_message_content 上面的 idx_group_message_content_msg_id 这个索引，然后再看看会是什么效果：
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: g type: ref possible_keys: idx_user_group_uid key: idx_user_group_uid key_len: 4 ref: const rows: 2 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: m type: ref possible_keys: PRIMARY,idx_group_message_gid_uid key: idx_group_message_gid_uid key_len: 4 ref: example.g.group_id rows: 3 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: c type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 96 Extra: Using where; Using join buffer
我们看到不仅仅 group_message_content 表的访问从 ref 变成了 ALL，此外，在最后一行的 Extra信息从没有任何内容变成为 Using where; Using join buffer，也就是说，对于从 ref 变成 ALL 很容易理解，没有可以使用的索引的索引了嘛，当然得进行全表扫描了，Using where 也是因为变成全表扫描之后，我们需要取得的 content 字段只能通过对表中的数据进行 where 过滤才能取得，但是后面出现的 Using join buffer 是一个啥呢?
我们知道，MySQL 中有一个供我们设置的参数 join_buffer_size ，这里实际上就是使用到了通过该参数所设置的 Buffer 区域。那为啥之前的执行计划中没有用到呢?
实际上，Join Buffer 只有当我们的 Join 类型为 ALL(如示例中)，index，rang 或者是 index_merge 的时候 才能够使用，所以，在我们去掉 group_message_content 表的 group_msg_id 字段的索引之前，由于 Join 是 ref 类型的，所以我们的执行计划中并没有看到有使用 Join Buffer。
join 优化：
用小结果集驱动大结果集，尽量减少join语句中的Nested Loop的循环总次数；
优先优化Nested Loop的内层循环，因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；
对被驱动表的join字段上建立索引；
当被驱动表的join字段上无法建立索引的时候，设置足够的Join Buffer Size
参考：
http://www.jasongj.com/2015/03/07/Join1/ #强烈推荐读，本文没写全里面的 Hash Join、Merge Join 等分析
http://www.cnblogs.com/weizhenlu/p/5970392.html
http://blog.csdn.net/ghsau/article/details/43762027
http://database.51cto.com/art/200904/117947.htm
http://blog.csdn.net/ys_565137671/article/details/6361730
