连接原理

28 阅读1分钟

数据准备

t1表

image.png

t2表

image.png

笛卡尔积

连接查询的结果集中包含一个表中的每一条记录另一个表中的每条记录相互匹配的组合

select * from t1, t2

image.png image.png

连接查询的大致过程

select * from t1, t2 where t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd'
  • 确定驱动表,只需要选取代价最小的访问方法执行单表查询
  • 从驱动表中获取到的每一条记录,都需要到t2表中查找匹配的记录。假如从驱动表t1获取到2条记录,那么需要查询2次t2表

image.png 在两表的连接查询中,驱动表只需访问一次,被驱动表可能需要访问多次。 每获得一条驱动表记录,立即到被驱动表中查找匹配的记录

内连接和外连接

内连接inner join...on

对于内连接的两个表,若驱动表中的记录在被驱动表中找不到匹配的记录,则该记录不会加入到结果集。

外连接

对于外连接的两个表,若驱动表中的记录在被驱动表中找不到匹配的记录,该记录也会加入到结果集。

外连接细分为2种,分别是左外连接left join...on和右外连接right join...on

  • where子句的过滤条件:对于外连接和内连接,不符合where子句的过滤条件的记录不会加入到结果集中。
  • on子句的过滤条件:对于外连接的驱动表记录,在被驱动表中找不到匹配on子句的过滤条件的记录,该记录也会加入结果集中。

on子句是为外连接的使用场景,如果内连接使用on,与where子句等价

连接原理

驱动表的选择

  • 内连接:哪个执行成本低选哪个
  • 外连接:左外连接选左表,右外连接选右边

嵌套循环连接

驱动表只访问一次,被驱动表可能访问多次,且访问次数取决于对驱动表执行单表查询后的结果集中有多少条记录

步骤1:选取驱动表,使用与驱动表相关的过滤条件,选取代价最低的单表访问方法来执行对驱动表的单表查询。

步骤2:对上一步骤中查询驱动表得到的结果集中每一条记录,都分别到被驱动表中查找匹配的记录。

image.png

使用索引加快连接速度

利用索引,加快查询被驱动表的速度。

基于块的嵌套循环连接

目的是减少被驱动表的访问次数
Join Buffer连接缓冲区,是执行查询前申请一块固定大小的内存,存放驱动表的若干条结果集,扫描被驱动表时,每一条被驱动表的记录一次性与Join Buffer中多条驱动表记录进行匹配。

image.png

思考

where子句的过滤条件和on子句的过滤条件有什么区别?

为什么不建议使用*作为查询列?

  • 使用二级索引列作为查询列表,减少回表次数
  • 查询列表的列和过滤条件的列会存放在Join Buffer中,使用*作为查询列,占用空间