MySQL-join的实现原理、优化及NLJ算法

时间:2023-12-20 22:45:44

案例分析:

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)来满足需求,不料这个查询非常慢。先来看查询计划:

MySQL-join的实现原理、优化及NLJ算法

  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