Mysql是怎样运行的：第十一章笔记

Mysql是怎样运行的：第十一章笔记

前置准备

准备以下两个表并为其填充一些数据：

-- 表 t1
CREATE TABLE t1 (m1 int, n1 char(1));
INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');

-- 表 t2
CREATE TABLE t2 (m2 int, n2 char(1));
INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');

连接简介

连接的本质

首先我们要简单介绍一下笛卡尔积：

假设有集合A={a,b}与集合B={0,1,2}，那么集合 A 和集合 B 的笛卡尔积（A x B）便为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。

计算过程如下：

集合 A \ 集合 B 0 1 2
a (a,0) (a,1) (a,2)
b (b,0) (b,1) (b,2)

注意：A x B 即集合 A 和集合 B 的笛卡尔积，B x A 即集合 B 和集合 A 的笛卡尔积。一般地说，笛卡尔积运算不满足交换律！即A x B ≠ B x A。

笛卡尔积用在 MySQL 的连接中，就是 MySQL 连接的本质，即连接的本质就是把各个连接表中的记录都取出来依次匹配、组合加入结果集中，而后将结果集返回给用户。这种将记录依次匹配组成新的更大的记录的查询过程，就是连接查询。

例如，我们把表 t1 和表 t2 连接起来的查询结果就是这样。

-- 连接表 t1 和表 t2 
SELECT * FROM t1, t2;

-- 连接结果
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    |    2 | b    |
|    2 | b    |    2 | b    |
|    3 | c    |    2 | b    |
|    1 | a    |    3 | c    |
|    2 | b    |    3 | c    |
|    3 | c    |    3 | c    |
|    1 | a    |    4 | d    |
|    2 | b    |    4 | d    |
|    3 | c    |    4 | d    |
+------+------+------+------+

连接过程简介

在连接查询的时候过滤掉特定记录组合是有必要的，连接查询中的过滤条件可以分成两种：

  1. 涉及单表的条件。即搜索条件，例如t1.m1 > 1，它是只针对 t1 表的过滤条件。
  2. 涉及两表的条件。例如t1.m1 = t2.m2，这个条件中涉及到了两个表，分别是 t1 和 t2 。

下面我们以一个查询语句为例，说明一下携带过滤条件的连接查询的大致执行过程。

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

第一个需要被查询的表我们称之为驱动表，此处假设 t1 表为驱动表。

在单表中执行查询语句，会选取代价最小的那种访问方法去执行，即从 const、ref、ref_or_null、range、index 和 all 这些执行方法中选取代价最小的去执行查询。

假设我们查询 t1 表的访问方法为 all ，即全表扫描。

从驱动表产生的结果集中的每一条记录，都需要到被驱动表中查找匹配的记录，很明显，当 t1 表被设定为驱动表后，t2 表便是被驱动表。

上述前提设定好之后，我们开始进行查询。

  1. 到 t1 表中找满足t1.m1 > 1的记录，这里对驱动表 t1 访问一次，得到两条记录。
  2. 因为上一步骤从驱动表中得到了两条记录，所以需要查询两次 t2 表（如果从上一步骤从驱动表中得到了三条记录，那么需要查询三次 t2 表），查询 t2 表的过程如下：
    • 当t1.m1 = 2时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 2，所以此时 t2 表相当于有了t2.m2 = 2、t2.n2 < 'd'这两个过滤条件，然后到t2表中执行单表查询。
    • 当t1.m1 = 3时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 3，所以此时 t2 表相当于有了t2.m2 = 3、t2.n2 < 'd'这两个过滤条件，然后到t2表中执行单表查询。

最终得到查询结果集为：

+------+------+------+------+ 
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    | 
|    3 | c    |    3 | c    | 
+------+------+------+------+

从上述过程中我们可以得知，在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。


内连接和外连接

现在我们给出两个有具有现实意义的表，表结构和表数据如下：

-- 表结构
CREATE TABLE student (
    number INT NOT NULL AUTO_INCREMENT COMMENT '学号',
    name VARCHAR(5) COMMENT '姓名',
    major VARCHAR(30) COMMENT '专业',
    PRIMARY KEY (number)
) Engine=InnoDB CHARSET=utf8 COMMENT '学生信息表';

-- 表数据
+----------+-----------+--------------------------+
| number   | name      | major                    |
+----------+-----------+--------------------------+
| 20180101 | 杜子腾     | 软件学院                  |
| 20180102 | 范统       | 计算机科学与工程           |
| 20180103 | 史珍香     | 计算机科学与工程           |
+----------+-----------+--------------------------+

-- 表结构
CREATE TABLE score (
    number INT COMMENT '学号',
    subject VARCHAR(30) COMMENT '科目',
    score TINYINT COMMENT '成绩',
    PRIMARY KEY (number, score)
) Engine=InnoDB CHARSET=utf8 COMMENT '学生成绩表';

-- 表数据
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180101 | 母猪的产后护理                |    78 |
| 20180101 | 论萨达姆的战争准备             |    88 |
| 20180102 | 论萨达姆的战争准备             |    98 |
| 20180102 | 母猪的产后护理                |   100 |
+----------+-----------------------------+-------+

知晓了上述的表结构和表数据，这里有一个需求：

要求查询出所有同学的考试成绩，并且没有考试成绩（缺考）的同学，也应该包含在结果集中。

单纯的通过两表的 number 字段连接两表，是无法查询出缺考同学的情况的。因为在被驱动表中没有与驱动表匹配的记录（驱动表的 number 字段值在 被驱动表中不存在），所以结果集是不会包含不匹配的记录的。但是，我们想即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集，怎么办呢？

这时候就要引入一些概念了，首先是内连接和外连接。

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

    例如，直接把需要连接的多个表都放到 FROM 子句后边是一种所谓的内连接，即 SELECT * FROM t1, t2;。

  • 对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

    同时，外连接又被分为左外连接（也称左连接）和右外连接（也称右连接）。左连接即选取左侧的表为驱动表，右连接即选取右侧的表为驱动表。

然后是 WHERE 和 ON 。

  • WHERE 子句中的过滤条件，不论是内连接还是外连接，凡是不符合 WHERE 子句中的过滤条件的记录都不会被加入最后的结果集。
  • ON 子句中的过滤条件（也称为连接条件），对于外连接驱动表的记录而言，如果无法在被驱动表中找到匹配 ON 子句中过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL 值填充。但是对于内连接，也就是把 ON 子句放到内连接中，MySQL 会把它和 WHERE 子句一样对待，即 WHERE 子句和 ON 子句是等价的。

一般而言，应该把只涉及单表的过滤条件放到 WHERE 子句中，把涉及两表的过滤条件都放到 ON 子句中。


左（外）连接的语法

以表 t1 和表 t2 为例，语法形式如下：

-- OUTER 可省略，ON 子句不可省略
SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

对于 LEFT JOIN 类型的连接，我们把放在左边的表称之为外表或者驱动表，右边的表称之为内表或者被驱动表。在上例中，表 t1 为外表或者驱动表，表 t2 为内表或者被驱动表。


右（外）连接的语法

以表 t1 和表 t2 为例，语法形式如下：

-- OUTER 可省略，ON 子句不可省略
SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

对于 RIGHT JOIN 类型的连接，我们把放在左边的表称之为内表或者被驱动表，右边的表称之为外表或者驱动表。在上例中，表 t1 为内表或者被驱动表，表 t2 为外表或者驱动表。


内连接的语法

以表 t1 和表 t2 为例，语法形式如下：

-- 由于在内连接中ON子句和WHERE子句是等价的，所以内连接中不强制要求写明 ON 子句
SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];

以内连接语法为标准，下面的写法在 MySQL 中等价：

SELECT * FROM t1 JOIN t2;

-- 推荐使用这种形式
SELECT * FROM t1 INNER JOIN t2;

SELECT * FROM t1 CROSS JOIN t2;
SELECT * FROM t1, t2;

对于内连接而言，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果。这是因为它不像外连接，驱动表中的记录即使在被驱动表中找不到符合 ON 子句连接条件的记录也会被加入到结果集，所以驱动表和被驱动表的互换，不影响最后的查询结果。

从上面我们也可以知道，左外连接和右外连接的驱动表和被驱动表是不能轻易互换的。


连接的原理

下面介绍几种连接查询算法。


嵌套循环连接（Nested-Loop Join）

还记得连接的过程吗？它可以大致的分成两个步骤。

  • 步骤 1：选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。
  • 步骤 2：对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

如果有 3 个表进行连接的话，那么步骤 2 中得到的结果集就像是新的驱动表，然后第三个表就成为了被驱动表，重复上面过程。

这个过程就像是一个嵌套的循环，所以这种驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为嵌套循环连接。

这是最简单，也是最笨拙的一种连接查询算法。


使用索引加快连接速度

在嵌套循环连接算法的基础上，我们可以在被驱动表上建立索引来加快查询速度。

还是以最开始介绍的 t1 表和 t2 表进行内连接的例子为例，即

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

查询驱动表 t1 后的结果集中有两条记录（驱动表只访问一次），这两条记录对应的结果集为{(2, 'b'), (3, 'c')}，嵌套循环连接算法需要对被驱动表查询2次（被驱动表却可能被多次访问）：

  • 当t1.m1 = 2时，去查询一遍 t2 表，对 t2 表的查询语句相当于：

    SELECT * FROM t2 WHERE t2.m2 = 2 AND t2.n2 < 'd';

  • 当t1.m1 = 3时，再去查询一遍 t2 表，对 t2 表的查询语句相当于：

    SELECT * FROM t2 WHERE t2.m2 = 3 AND t2.n2 < 'd';

这时候就转变成对被驱动表 t2 表进行单表扫描了，我们只需要考虑在哪一列上建立索引即可。

  • 在 m2 列上建立索引。

    1. 若 m2 列是 t2 表的主键或者唯一二级索引列，则因为对 m2 列的条件是等值查找，所以使用t2.m2 = 常数值这样的条件从 t2 表中查找记录的过程的代价就是常数级别的。这种在连接查询中对被驱动表使用主键值或者唯一二级索引列的值进行等值查找的查询执行方式称之为：eq_ref 。
    2. 若 m2 列不是 t2 表的主键或者唯一二级索引列，则因为对 m2 列的条件是等值查找，所以可能使用到 ref 访问方法。假设使用 ref 访问方法去执行对 t2 表的查询，需要回表之后再判断t2.n2 < d这个条件是否成立。

  • 在 n2 列上建立索引。

    在n2列上建立索引，涉及到的条件是t2.n2 < 'd'，可能用到 range 访问方法，假设使用 range 访问方法对 t2 表的查询的话，需要回表之后再判断在 m2 列上的条件是否成立。

  • 在 m2 和 n2 列上都建立索引。

    在 m2 和 n2 列上都建立索引，则会从这两个索引里挑一个代价更低的去执行对 t2 表的查询。当然，只有在 二级索引+回表 的代价比全表扫描的代价更低时才会使用索引。

另外，有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分列，而这些列都是某个索引的一部分，这种情况下会使用 index 访问方法来查询被驱动表。

所以，最好不要使用*作为查询列表，最好把真实用到的列作为查询列表。


基于块的嵌套循环连接（Block Nested-Loop Join）

扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。在采用嵌套循环连接算法的两表连接过程中，被驱动表可能需要被访问多次（取决于驱动表产生的结果集记录数）。

因为每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。所以如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那么就意味着我们要从磁盘上读相当于驱动表产生的结果集记录数次数的被驱动表，I/O 代价相当庞大！

综上所述，我们现在应该有了明确的目标，那就是：尽量减少访问被驱动表的次数。

首先我们分析问题。

在内存的被驱动表的每一条记录只会和驱动表结果集的一条记录做匹配，一对一匹配，可不可以变成一对多匹配呢？也就是说在内存的被驱动表的每一条记录一次性和多条驱动表中的记录做匹配，把一对一变成一对多，这样不就可以大大的提升效率了吗？

是的，在 MySQL 中存在一个 Join Buffer 的概念。Join Buffer 就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个 Join Buffer 中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和 Join Buffer 中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的 I/O 代价。

最好就是 Join Buffer 足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。

我们再来看看连接的过程：

  • 步骤 1：选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。
  • 步骤 2：对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

现在把步骤 2 的一对一匹配改成 Join Buffer 匹配，这种加入了 Join Buffer 的嵌套循环连接算法，即基于块的嵌套连接算法。

Join Buffer 的默认大小为 262144 Byte（即 256 KB），其大小可以通过启动参数或者系统变量join_buffer_size进行配置，最小可以设置为 128 Byte 。

也就是说，现在我们优化被驱动表的查询，有一下两种方式了：

  1. 为被驱动表加上效率高的索引。
  2. 调大join_buffer_size的值。

注意：并不是驱动表记录的所有列都会被放到 Join Buffer 中，只有查询列表中的列和过滤条件中的列才会被放到 Join Buffer 中。这再次说明了一个问题，那就是不要使用*作为查询列表，应该选取我们关心的列作为查询列表，这样 Join Buffer 就可能可以放置更多的记录，对于查询效率的提升也就越大。

posted @ 2024-07-11 09:44  苏沐问  阅读(38)  评论(0)    收藏  举报