MySQL是怎样运行的(二)：单表访问方法和连接查询

2021-09-30  本文已影响0人  susu2016

参考资料：[掘金] MySQL 是怎样运行的：从根儿上理解 MySQL
(https://juejin.cn/book/6844733769996304392/section)

第十一章- 条条大路通罗马 —— 单表访问方法
第十二章- 两个表的亲密接触 —— 连接的原理

单表访问方法

查询语句本质上只是一种声明式的语法，只是告诉MySQL我们要获取的数据符合哪些规则。至于MySQL如何将数据查出来，需要利用查询优化器进行分析，选出查询代价最低的方式。

下面介绍的单表访问方法以single_table表为例来说明

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

我们为这个single_table表建立了1个聚簇索引和4个二级索引，分别是：

const

查找过程的示意图如下：


无标题.png

ref

查找过程的示意图如下：


1.png

ref_or_null

有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来，就像下边这个查询：

SELECT * FROM single_table WHERE key1 = 'abc' OR key1 IS NULL;

当使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为ref_or_null，这个ref_or_null访问方法的执行过程如下：

2.png

range

索引列需要匹配某个或某些范围的值，如下列查询

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

从数学的角度看，每一个所谓的范围都是数轴上的一个区间，3个范围也就对应着3个区间：

我们可以把那种索引列等值匹配的情况称之为单点区间，上边所说的范围1和范围2都可以被称为单点区间，像范围3这种的我们可以称为连续范围区间。

range范围关键字：

对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者LIKE操作符连接起来，就可以产生一个所谓的区间。

利用LIKE进行范围查询只支持前缀查询，如 LIKE '%.com'

ref、ref_or_null、range 执行效率

ref、ref_or_null、range 查询能找到多条对应的记录，每条记录都需要回表。匹配到的记录越多，回表的代价越大，因此执行查询的代价取决于匹配到记录条数。
如果回表代价太大，MySQL可能会选择全表扫描的方式。

index

看下边这个查询：

  SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

由于key_part2并不是联合索引idx_key_part最左索引列，所以我们无法使用ref或者range访问方法来执行这个语句。但是这个查询符合下边这两个条件：

也就是说我们可以直接通过遍历 idx_key_part 索引的叶子节点的记录来比较 key_part2 = 'abc' 这个条件是否成立，把匹配成功的二级索引记录的 key_part1, key_part2, key_part3 列的值直接加到结果集中就行了。

由于二级索引记录比聚簇索记录小的多（聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列，而二级索引记录只需要存放索引列和主键），而且这个过程也不用进行回表操作，所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多，设计MySQL的大叔就把这种采用遍历二级索引记录的执行方式称之为：index。

all

最直接的查询执行方式就是我们已经提了无数遍的全表扫描，对于InnoDB表来说也就是直接扫描聚簇索引，设计MySQL的大叔把这种使用全表扫描执行查询的方式称之为：all。

单表访问-多条件查询

多个索引间的选择

一般情况下只能利用单个二级索引执行查询，比方说下边的这个查询：

  SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

查询优化器会识别到这个查询中的两个搜索条件，分别对应idx_key1、idx_key2两个索引：

选择索引：

查询步骤：

谨慎使用OR条件（无法使用索引的情况）

如何确定使用索引的区间：
把用不到索引的搜索条件替换为TRUE，进行化简。（因为我们不打算使用这些条件进行在该索引上进行过滤，所以先屏蔽这些条件，待到之后回表的时候再使用它们过滤）

以下SQL，key2添加了索引，common_filed没有添加索引，条件语句用AND连接。化简之后发现可以利用 idx_key2的范围区间 (100, +∞) 进行查询。

  SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';
  =>
  SELECT * FROM single_table WHERE key2 > 100 AND TRUE;
  =>
  SELECT * FROM single_table WHERE key2 > 100;

以下SQL，key2添加了索引，common_filed没有添加索引，条件语句用OR连接。化简之后发现需要进行全表搜索。

  SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';
  =>
  SELECT * FROM single_table WHERE key2 > 100 OR TRUE;
  =>
  SELECT * FROM single_table WHERE TRUE;

*索引合并

我们前边说过MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，特殊情况下也可能在一个查询中使用到多个二级索引。这种使用到多个索引来完成一次查询的执行方法称之为：index merge，具体的索引合并算法有下边三种。

连接查询

连接过程简介

以下列查询语句为例：

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

  1. 首先确定第一个需要查询的表，这个表称之为驱动表。
    选择t1为驱动表，则执行查询 SELECT * FROM t1 WHERE t1.m1 > 1 ，得到两条记录。


    image.png

  2. 针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要到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表中执行单表查询。

从上边两个步骤可以看出来，上边这个两表连接查询共需要查询1次t1表，2次t2表。在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。

内连接和外连接

选取驱动表和被驱动表：

ON 条件语句和 WHERE 条件语句：

使用索引加快连接速度：
连接查询的步骤2中可能需要访问多次被驱动表，查询t2表其实就相当于一次单表扫描，我们可以利用索引来加快查询速度。还是以这条SQL为例，以t1表为驱动表，以t2表为被驱动表，步骤2可以从 t2.m2 和 t2.n2 两个索引中选择查询代价较小的使用。

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

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

由于驱动表查询出的记录较多，每条记录都要对应一次被驱动表的查询，比较耗费资源。如果被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。

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

最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。这种加入了join buffer的嵌套循环连接算法称之为基于块的嵌套连接（Block Nested-Loop Join）算法。

这个join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置，默认大小为262144字节（也就是256KB），最小可以设置为128字节。当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大join_buffer_size的值来对连接查询进行优化。

上一篇下一篇

猜你喜欢

热点阅读