0%

MySQL连接查询

CentOS(Community Enterprise Operating System)是Linux发行版之一,它是来自于Red Hat Enterprise Linux(RHEL)依照开放源代码规定发布的源代码所编译而成。由于出自同样的源代码,因此有些要求高度稳定性的服务器以CentOS替代商业版的Red Hat Enterprise Linux使用。两者的不同,在于CentOS并不包含封闭源代码软件。CentOS 对上游代码的主要修改是为了移除不能自由使用的商标。2014年,CentOS宣布与Red Hat合作,但CentOS将会在新的委员会下继续运作,并不受RHEL的影响。CentOS和RHEL一样,都可以使用Fedora EPEL来补足软件。

一、概念

      连接查询是关系数据库中最主要的查询,主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点,也是它区别于其它类型数据库管理系统的一个标志。在关系数据库管理系统中,表建立时各数据之间的关系不必确定,常把一个实体的所有信息存放在一个表中。当检索数据时,通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性,他们可以在任何时候增加新的数据类型。为不同实体创建新的表,然后通过连接进行查询。

二、使用

  1. 构造数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE `table_join_two` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into table_join_two values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();

create table table_join_one like table_join_two;
insert into table_join_one (select * from table_join_two where id<=100)

  1. NLJ
    • 查询语句 select * from table_join_one straight_join table_join_two on (table_join_one.a=table_join_two.a);
      • 如果直接使用 join 语句,MySQL 优化器可能会选择表 table_join_one 或 table_join_two 作为驱动表,这样会影响我们分析 SQL 语句的执行过程。所以,为了便于分析执行过程中的性能问题,我改用straight_join 让MySQL使用固定的连接方式执行查询,这样优化器只会按照我们指定的方式去 join。
      • 在这个语句里,table_join_one 是驱动表,table_join_two 是被驱动表。
    • 查看执行计划 explain select * from table_join_one straight_join table_join_two on (table_join_one.a=table_join_two.a);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: table_join_one
partitions: NULL
type: ALL
possible_keys: a
key: NULL
key_len: NULL
ref: NULL
rows: 100
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: table_join_two
partitions: NULL
type: ref
possible_keys: a
key: a
key_len: 5
ref: study.table_join_one.a
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
  • 分析:在这条语句里,被驱动表 table_join_two 的字段 a 上有索引,join 过程用上了这个索引,因此这个语句的执行流程是这样的:

    • 从表 table_join_one 中读入一行数据 R;
    • 从数据行 R 中,取出 a 字段到表 table_join_two 里去查找;
    • 取出表 table_join_two 中满足条件的行,跟 R 组成一行,作为结果集的一部分;
    • 重复执行步骤 1 到 3,直到表 table_join_one 的末尾循环结束。
  • 这个过程是先遍历表 table_join_one,然后根据从表 table_join_one 中取出的每行数据中的 a 值,去表 table_join_two 中查找满足条件的记录。在形式上,这个过程就跟我们写程序时的嵌套查询类似,并且可以用上被驱动表的索引,所以我们称之为“Index Nested-Loop Join”,简称 NLJ。

  • 执行流程

Index Nested-Loop Join

如果一条 join 语句的 Extra 字段什么都没写的话,就表示使用的是 Index Nested-Loop Join(简称 NLJ)算法。

  1. BNL
    • 查询语句 select * from table_join_one straight_join table_join_two on (table_join_one.a=table_join_two.b);
    • 查看执行计划 explain select * from table_join_one straight_join table_join_two on (table_join_one.a=table_join_two.b);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: table_join_one
partitions: NULL
type: ALL
possible_keys: a
key: NULL
key_len: NULL
ref: NULL
rows: 100
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: table_join_two
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 10.00
Extra: Using where; Using join buffer (Block Nested Loop)
  • 分析:在这个过程中,对表 table_join_one 和 table_join_two 都做了一次全表扫描,因此总的扫描行数是 1100。由于 join_buffer 是以无序数组的方式组织的,因此对表 table_join_two 中的每一行,都要做 100 次判断,总共需要在内存中做的判断次数是:100*1000=10 万次。这个语句的执行流程如下:
    • 扫描表 t1,顺序读取数据行放入 join_buffer 中,直到 join_buffer 满了,继续第 2 步;
    • 扫描表 t2,把 t2 中的每一行取出来,跟 join_buffer 中的数据做对比,满足 join 条件的,作为结果集的一部分返回;
    • 清空 join_buffer;
    • 继续扫描表 t1,顺序读取数据放入 join_buffer 中,继续执行第 2 步。
  • 执行流程

Block Nested-Loop Join

三、参考

  1. 参考一