xyZGHio

樱花同载酒，终似少年游

0%

MySQL之InnoDB存储引擎：单表查询之Access Method访问方法

发表于 更新于 分类于 阅读数： 评论数：
本文字数： 7.3k 阅读时长 ≈ 15 分钟

这里我们了解下MySQL的单表查询，即所谓Access Method访问方法。其指的是MySQL下执行查询语句的方式。这里来介绍几种常见的Access Method访问方法

abstract.png

all

该查询执行的方式是直接通过扫描聚簇索引来实现的，即我们通常所说的全表扫描。显然这种查询执行方式的效率是比较低的

这里，我们建立一张表

1
2
3
4
5
CREATE TABLE access_method1 (
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NULL,
    PRIMARY KEY (id)
);

然后通过explain语句来查看下面查询语句的执行计划

1
2
explain select * from access_method1
	where name = 'bob';

从下图结果type列中我们即可看到其方式为all。原因也很好理解，因为name字段并没有建立索引，故只能通过全表扫描的方式来查找

figure 1.png

const

我们知道对于主键、唯一键而言，MySQL会自动帮我们分别建立聚簇索引、唯一二级索引。故如果我们使用主键或唯一键来作为查询条件进行等值查找的话，大多数情况下只会查到一条记录(因为对于唯一约束而言，其并不限制NULL值的数量)。所以，对于这种通过聚簇索引或唯一二级索引进行等值查找唯一一条记录的访问方式，称之为const。当然如果使用唯一键进行查找的话，还需要进行一次回表过程

为了保证只会查找一条记录，需注意以下几点

  1. 使用主键或唯一键作为查询条件，必须为等值查找
  2. 如果主键或唯一键是由多个列组成时，必须在查询条件给出主键或唯一键中所有列的查询条件
  3. 使用唯一键作为查询条件时，不可以使用NULL值。因为对于唯一约束而言，其并不限制NULL值的数量

这里我们同样先建立一张表，并指定id字段为主键、name字段为唯一键。与此同时向该表插入一条记录

1
2
3
4
5
6
7
8
9
10
CREATE TABLE access_method2 (
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NULL,
    iphone_number VARCHAR(100) NULL,
    PRIMARY KEY (id),
    UNIQUE (name)
);

insert into access_method2
	value (1, 'Aaron', 110);

首先，我们利用主键进行等值查询

1
explain select * from access_method2 where id=1;

显然执行计划中显示为const方式

figure 2.png

类似地，我们再用唯一键进行等值查询

1
explain select * from access_method2 where name='Aaron';

结果依然符合预期

figure 3.png

最后，我们来试试对唯一键使用NULL值进行查询，看看会发生什么

1
explain select * from access_method2 where name is null;

从下图我们可以看到，其使用的是我们下面将要介绍的ref方式

figure 4.png

ref

除了唯一(二级)索引外，对于其他二级索引而言，当我们使用索引列中字段来进行等值查询时，可以使用通过该二级索引+回表的方式来进行查询的，也可以直接选择全表扫描的方式进行查询。显然这里的查询是无法保证只有一条记录是符合查询条件的。故MySQL具体使用哪种方式很大程度取决于 需要回表的记录数量，如果数量较少则回表成本低，倾向于选择二级索引+回表的方式；反之，则倾向于使用全表扫描的方式。这里，我们将利用二级索引进行等值查询的方式称之为ref。既然需要使用二级索引，则其同样需要满足最左匹配原则

同样，这里我们建立一张access_method3表，并建立一个使用name、iphone_number字段的二级索引。为了后面的演示，这里我们先插入一些记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE access_method3 (
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NULL,
    iphone_number VARCHAR(100) NULL,
    country	VARCHAR(255) NULL,
    PRIMARY KEY (id),
    index index1(name, iphone_number)
);
-- 插入记录
insert into access_method3 (id, name, iphone_number, country) value
    (1, 'Aaron', 110, 'CN'),
    (2, 'Bob', 123, 'US'),
    (3, NULL, 432, 'JP'),
    (4, 'Tom', 768, NULL),
    (5, 'Tony', 233, 'UK');

好，现在让我们来看看下面这个符合最左匹配原则的等值查询语句是否会利用索引index1进行查询

1
explain select * from access_method3 where name='Aaron';

下图结果中的ref表明其是利用索引index1来进行查询的

figure 5.png

同理，对于下面这个查询而言，其同样可能会选择使用ref方式进行查询

1
explain select * from access_method3 where name='Aaron' and iphone_number=110;

结果如下，符合预期

figure 6.jpeg

现在如果我们只对iphone_number字段进行等值查询，由于不满足最左匹配原则，其查询方式肯定不是ref了

1
explain select * from access_method3 where iphone_number=110;

结果如下，符合预期。可以看到，由于无法利用索引进行查询，故其使用全表扫描的all方式进行查询

figure 7.png

ref_or_null

该查询方式与ref类似，只不过MySQL会额外搜索包含NULL值的字段

index

在ref一节中，我们对access_method3表的iphone_number字段进行等值查询时，发现其查询方式为all全表扫描。显然这种情况下的查询效率非常低。那有没有办法来优化这个查询呢？其实是有的。当索引是该查询的覆盖索引时，其可通过遍历该索引所有的叶子节点来进行查询。这就是所谓的index方式。相比较遍历聚簇索引的叶子节点而言，遍历二级索引的叶子节点相对而言会更快。因为后者的叶子节点中只存放索引包含的各字段和主键，要比聚簇索引小很多。与此同时，因为索引覆盖，故也无需再进行回表操作

好了，我们继续使用access_method3表进行查询。只不过这里查询获得的字段信息只包含了index1索引覆盖的字段

1
explain select id,name,iphone_number from access_method3 where iphone_number=110;

结果如下，符合预期

figure 8.jpeg

同理，该查询语句同样被索引index1覆盖了

1
2
3
explain
    select name,iphone_number from access_method3
    where iphone_number>110 and iphone_number<222;

故，其查询方式同样为index

figure 9.png

range

众所周知B+树的各叶子节点之间构成了一个双向链表，所以当我们使用索引(聚簇索引、二级索引)来进行范围查询时，只需先确定首尾两条记录数据，然后通过链表关系即可快速地获取中间的记录数据。当然既然使用索引了，同样也需要遵循最左匹配原则。与ref方式类似，如果在range方式下回表的记录数量过多、成本过高，MySQL则更倾向于使用all方式来进行查询

这里，我们依然还是通过access_method3表来进行演示，下面即是一个利用二级索引来进行范围查询的示例。当然其还需要进行回表操作

1
explain select * from access_method3 where name > 'aaron' and name <'Tony';

可以看到，其查询方式为range

figure 10.png

同样地，对于聚簇索引也是适用的

1
explain select * from access_method3 where id>12 and id<22;

结果如下，符合预期

figure 11.png

在使用索引范围查询时，需要特别注意的下面这种情况。如果通过or连接另外一个没有使用该索引的查询条件后，是无法使用该索引进行查询的。下面我们通过例子来具体的进行演示，这里我们对index1索引的name字段进行范围查询，同时还通过or连接了一个非index1索引的country字段的查询条件

1
explain select * from access_method3 where name > 'Tony' or country='JP';

从下图结果中，我们可以看到，其无法使用range来进行范围查询，而是通过all扫描全表的方式来查询

figure 12.png

其实理解上述结果的关键在于，在确定范围查询的范围区间时，需要先将用不到索引的查询条件替换为TRUE。也就是说在对索引进行范围查询时，先不使用该条件，而是在回表后再利用该条件进行过滤

所以对上面的sql语句country字段的查询条件替换为TRUE，即为

1
2
3
select * from access_method3 where name > 'Tony' or TRUE;
-- 对上面的SQL语句做进一步化简
select * from access_method3 where TRUE;

至此，相信大家都明白了为什么不适用range方式了。因为其需要对所有二级索引的记录进行回表。这还不如直接扫描全表呢

而如果改用and来进行连接，则情况就大不一样了。分析思路与上面如出一辙，将无法利用该索引的查询条件替换为TRUE并进行简化

1
2
3
4
5
select * from access_method3 where name > 'Tony' and country='JP';
-- 将country字段的查询条件替换为TRUE
select * from access_method3 where name > 'Tony' and TRUE;
-- 对上面的SQL语句作进一步简化
select * from access_method3 where name > 'Tony';

从上面的简化SQL中，现在我们可以看出其可以使用 name > ‘Tony’ 条件先进行范围查询，然后在回表结果中使用 country=’JP’ 条件来进行过滤。结合下图也可以看出，其使用的是range方式

figure 13.png

Note

对于like操作符而言，其只有在匹配完整字符串或字符串前缀时才可以使用索引通过range来进行查询

1
2
3
4
-- 匹配字符串前缀
select * from access_method3 where name like 'aa%';
-- 匹配完整字符串
select * from access_method3 where name like 'aa';

上述两个查询语句的执行计划结果一样，均为下图

figure 14.png

所以如果是匹配字符串后缀，即无法通过range方式来进行查询

1
explain select * from access_method3 where name like '%aa';

结果如下所示，可以看到其不是range方式

figure 15.png

index merge

前面我们说的都是在单表只使用一个索引的查询方式，其实在单表查询中还可以同时使用多个索引，即所谓的index merge方式。而在该方式下具体又有Intersection、Union、Sort-Union等算法

Intersection 交集算法

所谓Intersection交集算法指的是，某个SQL查询语句可以同时利用多个索引，则从这多个索引的查询结果中计算主键的交集。然后再利用交集中的主键进行回表。即如果使用Intersection交集算法能够大大减少回表的数量，则MySQL会更倾向于使用此种方式来进行查询

这里需要特别注意的是，为了保证在计算多个索引查询结果的主键交集时的时间效率，该算法要求各自索引的查询结果均是按主键排序。此时类似于对多个有序数组求交集的问题，其时间复杂度一般为线性级别。介绍了Intersection交集算法在计算交集时的要求后，就很好理解该算法的两个适用场景

  1. 查询条件使用二级索引中所有字段进行等值查询。因为二级索引中，只有在各字段相同的情况下，才会使用主键进行排序
  2. 查询条件对主键进行范围查询。因为聚簇索引下，本身就是使用主键进行排序，故可以对主键使用范围查询

最后需要强调的是，究竟是否使用index merge方式的Intersection交集算法，是需要MySQL本身自己根据实际情况来进行判断的。即上面的两个场景是适用Intersection交集算法的必要条件，而非充分条件

现在，我们建立access_method4表，结构如下。同时分别对create_time字段、recv_time字段建立二级索引。同时为了方便复现此种查询方式，我们向表中插入了大量记录数据

1
2
3
4
5
6
7
8
9
create table access_method4 (
    id              int auto_increment,
    recv_time       datetime       null,
    create_time     varchar(255)   null,
    name            varchar(255)   null,
    PRIMARY KEY (id),
    index index1(create_time),
    index index2(recv_time)
);

这里，我们使用两个二级索引中的全部字段进行等值查询

1
2
3
4
explain
select * from access_method4
where create_time='2019-03-26 23:59:35.492'  -- 对index1索引中所有字段进行等值查询
and recv_time='2019-03-26 23:59:32';            -- 对index2索引中所有字段等进行值查询

从下图的type列中，我们可以看到其使用index_megre方式进行查询，具体的算法则体现在Extra列中

figure 16.jpeg

类似地，当我们使用一个聚簇索引进行范围查询和一个二级索引全部的字段进行等值查询

1
2
3
4
explain
select * from access_method4
where create_time='2019-03-26 23:59:35.492' -- 对index1索引中所有字段进行等值查询
and id>11 and id<20;                        -- 对主键(聚簇索引)进行范围查询

结果如下，符合预期

figure 17.jpeg

Union 并集算法

类似地，对于Union 并集算法而言，是某个SQL查询语句可以同时利用多个索引，则从这多个索引的查询结果中计算主键的并集。然后再利用并集中的主键进行回表。该算法的适用场景与Intersection算法一致，因为计算若干组有序的主键的并集显然时间成本更低。同样地，它们亦是适用Union并集算法的必要条件，而非充分条件

1
2
3
4
explain
select * from access_method4
where create_time='2019-03-26 23:59:35.492' -- 对index1索引中所有字段进行等值查询
or recv_time='2019-03-26 23:59:32';             -- 对index2索引中所有字段等进行值查询

结果如下，符合预期

figure 18.png

类似地，我们也可以对主键进行范围查询

1
2
3
4
explain
select * from access_method4
where create_time='2019-03-26 23:59:35.492' -- 对index1索引中所有字段进行等值查询
or id>11 and id<20;                                  -- 对主键(聚簇索引)进行范围查询

结果如下，符合预期

figure 19.png

Sort-Union 排序-并集算法

从前面的Union并集算法我们看到，其适用场景还是比较苛刻的。为了保证二级索引的查询结果是按主键排序的，其要求二级索引中所有列只能进行等值查询。为此index merge方式中还提供了一种索引合并算法——Sort-Union 排序-并集算法。其与Union并集算法的区别在于，其会对二级索引的查询结果先按主键进行排序。后面的操作(求并集、回表)就和Union算法一样了。这样对于该算法而言，其对二级索引就不再强制要求全部字段进行等值查询了。当然即使条件满足了，MySQL是否使用该方式也是要取决于查询优化器

例如下面的SQL查询语句使用两个二级索引进行范围查询

1
2
3
4
explain
select * from access_method4
where create_time>'2019-03-26 23:59:35.492' -- 使用index1索引进行范围查询
or recv_time<'2019-03-22 23:59:32';             -- 使用index2索引中进行范围查询

结果如下，符合预期

figure 20.jpeg

类似地，也可以与聚簇索引搭配使用

1
2
3
4
explain
select * from access_method4
where create_time>'2019-03-26 23:59:35.492' -- 使用index1索引进行范围查询
or id>11 and id<20;                                  -- 对主键(聚簇索引)进行范围查询

结果如下，符合预期

figure 21.jpeg

参考文献

  1. MySQL是怎样运行的
推荐文章
请我喝杯咖啡捏～

欢迎关注我的微信公众号：青灯抽丝