关联查询
前言
在写下这篇博客之前,我对关联查询对应得底层逻辑也是不清楚的,并且对关联查询及子查询的处理也是很陌生的,但是参考多篇文档之后,才开始慢慢的了解了其中的玄妙。所以特意找个时间好好写一写。
表结构
# 学生表
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL COMMENT '学生名',
`class_id` int(11) NOT NULL COMMENT '班级ID',
`monitor_id` int(11) DEFAULT NULL COMMENT '班长ID',
PRIMARY KEY (`id`),
KEY `index_class` (`class_id`) USING BTREE,
KEY `index_monitor` (`monitor_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
# 班级表
CREATE TABLE `class` (
`id` int(11) NOT NULL,
`class_name` varchar(255) DEFAULT NULL COMMENT '班级名',
`sort` int(11) DEFAULT NULL COMMENT '排序',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
什么是关联查询
在使用数据库查询是,发现单表的查询已经不能满足业务时,就要涉及到多个表的查询,这就是关联查询,它至少是两个表之间的关联。
关联查询的类型
内连接
SELECT s.id,c.class_name,s.name FROM student s JOIN class c ON s.class_id = c.id
左外连接
SELECT s.id,c.class_name,s.name FROM student s LEFT JOIN class c ON s.class_id = c.id
右外连接
SELECT s.id,c.class_name,s.name FROM student s INNER JOIN class c ON s.class_id = c.id
自连接
SELECT s.id,s.name, s2.name as montitor_name FROM student s LEFT JOIN student s2 ON s.id = s2.monitor_id
全外连接
SELECT s.id,s.name, c.class_name as montitor_name FROM student s LEFT JOIN class c ON s.class_id = c.id UNION SELECT s.id,s.name, c.class_name as montitor_name FROM student s LEFT JOIN class c ON s.class_id = c.id
关联的三种算法
- Index nested-Loop join 算法
- Simple Nested-Loop join 算法
- Block Nested-Loop Join 算法
Index nested-Loop join
翻译成中文:索引嵌套循环查询。
定义:当有两张表使用 join 关联的时候,它们用于关联的字段如果都用上了索引,不论是驱动表还是被驱动表,这时就会使用上 Index nested-Loop join 算法。简称 NLJ
示例
select * from student s join class c on s.class_id = c.id
t1 和 t2 它们的sql语句直接使用join关联,这个时候我们观察到 s.class_id 是有索引的。并且 join 过程也会用上这个索引的。那么我们来看看它的整体执行流程是怎样的。
执行流程:
- 从student 表中读入一行数据 Row;
- 从数据行 R 中,取出 class_id 字段到表 class 里去查找
- 取出表 class 中满足条件的行,跟 R 组成一行,作为结果集的一部分;
- 重复执行1 到 3 步骤,知道 student 的末尾循环结束。
我们可以从执行流程中看到,它的过程,很像我们用业务代码的嵌套查询逻辑,然后由于它又可以使用上被驱动表的索引,所以就称为 索引嵌套循环查询了。
注意:当使用 join 时会默认选择表的结果集小的为驱动表,这句话的意思就是 经过where条件后的结果集。
小结
- 使用join语句,性能比强行拆分成多个单表执行SQL语句的性能要好。
- 如果使用 Join 语句的话,需要让小表做驱动表。
Simple Nested-Loop Join
这个算法是比较少用的,因为它是两张表关联都没有使用上索引,会导致 student 和 class 两张表都是查询所有,然后再汇总成正确的结果集返回。举个例子:
比如 我查出 student.class_id = 2 的行数,但是我拿到这条数据却去跟 monitor_id 关联了,相当于 M * N的关系 所以会非常的笨重。不建议使用这种。
Block Nested-Loop Join
翻译: 块嵌套查询
这个算法跟上面方法很相似的,但是最大的区别是,BNL算法的数据获取是在内存中进行的。就拿student 和 class 表举例,相当于:
- 先将student表的数据全部读入缓存join_buffer 中,等join_buffer满了,就执行第二步
- 扫描 class 表中每一行与join_buffer中的数据做对比,满足join条件的 作为结果集的一部分返回。
- 清空 join_buffer
- 继续扫描表 student ,将剩余的行数读入join_buffer 中,继续与class表做对比的。
因为它是分段去查询的 不是一次就将所有需要的结果集返回,所以它就被称为块嵌套查询。
总结
- 如果可以使用被驱动表的索引,join语句还是有其优势的
- 不能使用被驱动表的索引,只能使用 Block Nested-Loop 算法,这样的语句尽量不要使用
- 在使用 join 的时候,应该让小表作为驱动表。
拓展
所谓小表的对比都是根据结果集以及参与Join的各个字段的总数据量的大小去做对比的,比如 student 表加了筛选条件过滤之后返回的结果集要比class表的返回结果集小的话,我们就说student 这个时候就是小表了,所以很多时候驱动表的选择是不固定的所以这个时候 我们就需要看业务需求而去选择驱动小表了。