MySQL-7、连接的原理

杨文同学

已于 2024-06-14 23:02:08 修改

阅读量616

点赞数 18

分类专栏： MySQL 文章标签： mysql

于 2024-06-06 19:33:29 首次发布

本文链接：https://blog.csdn.net/qq_40950398/article/details/139508354

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

前言

前面介绍了MySQL执行查询语句其中访问方法，包括const、ref、ref_or_null、range、index、all、index_merge（索引合并）。索引合并又分三种情况，Intersection(交集)索引合并、Union(并集)索引合并、Sort-Union索引合并。

（如果没有看前面六篇文章，不建议看此篇文章）

传送门：

连接的本质

我们平时经常用到连接，也就是join，常用的有inner join 、left join、rigth join、full join。（full join不同数据库语法不同）

连接就是把各个表中的记录都取出来进行依次匹配，并把匹配后的组合发送给MySQL客户端，最后再有MySQL客户端整体发送给用户。

之前说过单表查询，因为只有一个表，唯一不知道的是，如果where后面筛选条件有多个索引，到底用哪一个。那join就涉及到多表了，那到底先查哪个表以及用哪个表的索引?？

说之前，先创建两个表

-- 创建学生表 students
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY, -- 学号，主键，自增
    name VARCHAR(50) NOT NULL,                 -- 学生姓名，非空
    gender ENUM('Male', 'Female') NOT NULL,    -- 性别，枚举类型，非空
    birth_date DATE,                           -- 出生日期
    email VARCHAR(100),                        -- 邮箱
    phone VARCHAR(15),                         -- 电话
    address VARCHAR(255)                       -- 地址
);

-- 创建成绩表 grades
CREATE TABLE grades (
    grade_id INT AUTO_INCREMENT PRIMARY KEY,   -- 成绩ID，主键，自增
    student_id INT,                            -- 学号，外键
    course_name VARCHAR(100) NOT NULL,         -- 课程名称，非空
    grade DECIMAL(5,2),                        -- 成绩，保留两位小数
    grade_date DATE                           -- 成绩日期
);

-- 插入学生表数据
INSERT INTO students (name, gender, birth_date, email, phone, address) VALUES
('John Doe', 'Male', '2000-01-15', 'john.doe@example.com', '1234567890', '123 Main St'),
('Jane Smith', 'Female', '2001-02-20', 'jane.smith@example.com', '0987654321', '456 Elm St');

-- 插入成绩表数据
INSERT INTO grades (student_id, course_name, grade, grade_date) VALUES
(1, 'Math', 85.50, '2024-01-10'),
(1, 'Science', 90.00, '2024-01-15'),
(2, 'Math', 88.00, '2024-01-12'),
(2, 'Science', 92.00, '2024-01-18');

比如下面一个连接查询

SELECT
    students.student_id,
    students.name,
    students.gender,
    students.birth_date,
    students.email,
    students.phone,
    students.address,
    grades.course_name,
    grades.grade,
    grades.grade_date
FROM
    students
LEFT JOIN
    grades
ON
    students.student_id = grades.student_id;

这个sql一看就知道很简单，就是一个简单的左连接查询。

连接的过程大致如下：

首先确认第一个需要查询的表，这个表称为驱动表。基于上面的sql，很明显驱动表是students表。在单表执行查询语句前面一章已经介绍了，这里就不多介绍。因为没有筛选条件，所以驱动表肯定是全表扫描。
从驱动表每获取到一条符合条件的记录，就需要到grades表中去查找符合条件的记录。所以grades在此次查询中是被驱动表。那被驱动表是如何查找呢，答案就是ON后面的条件。
根据表数据以及上面两步可以知道，students表中有两条数据。所以就要查询grades表两次。也就是说，在两表的连接查询中，驱动表只需要访问一次，被驱动表可能需要访问多次。

这里需要强调一下，在执行查询中，并不是将所有满足条件的驱动表记录先查询出来放到一个地方，然后再去被驱动表中查询。因为如果满足驱动表的数据很多，那就需要很大一片存储空间，这是不划算的。所以是每获得一条驱动表数据，就立刻到被驱动表中匹配。

内连接和外连接

内连接和外连接这是比较基础的知识，不知道的可以具体查一下。我这里稍微介绍下两者的区别：

对于内连接的相关表，若驱动表中的记录在被驱动表中找不到匹配的记录，则该记录不会加入到最后的结果集中。（就是关联的表，都要满足ON后面的条件）
对于外连接的相关表，即使驱动表中的记录在被驱动表中找不到匹配的记录，也仍然会加入到最后的结果集中。（就是关联的表，可以不满足ON后面的条件，只满足驱动表的条件）

外连接又包括左连接和右连接

左连接

select * from t1 left outer join t2 on t1.xx = t2.xx;

outer可有可无。驱动表就是left join 左边的表，右边的表即是被驱动表。

右连接

select * from t1 right outer join t2 on t1.xx = t2.xx;

outer可有可无。驱动表就是right join 右边的表，左边的表即是被驱动表。

内连接

select * from t1 inner join t2 on t1.xx = t2.xx;

inner可有可无，inner还可以换成cross。需要注意的是，对于内连接来说，驱动表和被驱动表是可以互换的，因为并不影响最后的查询结果。

连接的原理

前面说了内连接和外连接，这些都是基础的知识。下面来介绍一下，连接表与表是怎样执行的。

嵌套循环连接

前面说过，驱动表只会访问一次，被驱动表的访问次数，是根据驱动表有多少条记录符合条件。如果有筛选条件，驱动表会根据条件，执行不同的单表访问方法。

前面的例子是连接了两张表，那假如连接三张表、四张表呢，这在工作中很常见。如下sql：

select * from t1 
inner join t2 on t1.xx = t2.xx
inner join t3 on t2.xx=t3.xx;

后面还可以连接其他表，所以上面的sql执行步骤大致如下：

先确认第一张驱动表，即是t1，获取符合t1的数据。然后确认被驱动表，既是t2，找出符合ON后面条件的数据。
根据上一步中，找出t2的数据得到的结果，作为新的驱动表，t3作为被驱动表。如果后面还有连接，依次类推。

不难发现，上面的执行过程，用代码来表示就是，循环里套循环

for(int i=0...)
{
    for(int j=0...)
    {
        for(int k=0...)
        {
        }
    }
}

上面只是用伪代码描述一下。这种，驱动表只访问一次，但被驱动表却可能访问多次，并且次数取决于对驱动表执行单表查询后的结果集有多少条记录的连接执行方式称为嵌套循环连接。

这是最简单也是最笨拙的一种连接查询算法。那基于上面的嵌套循环连接如何优化呢。答案就是我们之前说的索引。

使用索引加快连接速度

我们知道，嵌套循环连接是根据驱动表所查询的记录数来确定的，所以可以对驱动表或被驱动表加where条件，并且条件中最好还是索引。从而减少扫描区间，就跟我们平时优化查询一样，如果数据量特别巨大，会默认加上时间筛选条件，默认半年或者一年。

我们这里说的驱动表加筛选条件，也适用于上面三张表连接查询，对于t3表来说，t2就是驱动表。

基于块的嵌套循环连接

在实际的工作中，像t1、t2的表，数据量可能几百万，几千万。假设驱动表或被驱动表加索引也不能提高连接速度。那该怎么办呢？究其根源还是被驱动表的访问次数不能太多，执行太多次，磁盘I/O压力也很大。

所以MySQL基于减少被驱动表的访问次数，设计了Join Buffer（连接缓冲区）。Join Buffer就是在执行连接查询前申请一块固定大小的内存。先把若干条驱动表结果装在这个Join Buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性地与Join Buffer中的多条驱动表记录进行匹配。由于Join Buffer是内存中的一片空间，所以Join Buffer在内存中比较就很快。

Join Buffer的嵌套循环连接算法称为基于块的嵌套循环连接算法。

Join Buffer的大下可以通过系统遍历 join_buffer_size进行配置。默认大小为256KB，最小可以设置128字节。还需要注意的是，Join Buffer中并不会存放驱动表记录的所有列，只有查询列表中列和过滤条件中的列才会放入到Join Buffer中。所以这也再次提醒我们，最好不要把 *作为查询列表。这样Join Buffer可以存放更多记录。

//查看join_buffer大小
show variables like 'join_buffer_size';

那我们怎么知道在查询中用到了哪种算法呢？？

还是EXPLAIN语句，后面会做介绍，这里简单说一下，EXPLAIN中有一列叫Extra，如果该列的值中有Using join buffer就表示使用了基于块的嵌套循环连接。

总结

介绍了连接的本质，说了驱动表和被驱动表、内连接和外连接的基本知识和区别。最后再介绍了连接的原理，以及嵌套循环连接和基于块的嵌套循环连接两种算法。

杨文同学

关注

18
点赞
踩
28

收藏

觉得还不错? 一键收藏
0
评论
MySQL-7、连接的原理

MySQL连接的本质与原理
复制链接

扫一扫

专栏目录