youyichannel

志于道，据于德，依于仁，游于艺！

0%

重拾MySQL —— 单表访问方法

发表于 更新于 分类于 ，
本文字数： 8.6k 阅读时长 ≈ 8 分钟

《MySQL是怎样运行的 —— 从跟上理解MySQL》—— 第十章

MySQL Server中有查询优化器的模块，一条查询语句进行语法解析之后就会被交给查询优化器来进行优化，优化的结果就是生成一个执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是什么样的，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。执行计划就需要涉及到单表查询方法概念。

【示例表】

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 uk_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

这个single_table表中存在1个聚簇索引和4个二级索引，分别是：

  • 为id列建立的聚簇索引。
  • 为key1列建立的idx_key1二级索引。
  • 为key2列建立的uk_key2二级索引，而且该索引是唯一二级索引。
  • 为key3列建立的idx_key3二级索引。
  • 为key_part1、key_part2、key_part3列建立的idx_key_part二级索引，这也是一个联合索引。

一、访问方法的概念

我们平时所写的那些查询语句本质上只是一种声明式的语法，只是告诉MySQL我们要获取的数据符合哪些规则，至于MySQL是怎么把查询结果搞出来的那是MySQL自己的事儿。

对于单个表的查询来说，查询的执行方式大致分为下面两种：

  1. 使用全表扫描进行查询
  2. 使用索引进行查询
    • 针对主键或唯一二级索引的等值查询
    • 针对普通二级索引的等值查询
    • 针对索引列的范围查询
    • 直接扫描整个索引

MySQL执行查询语句的方式称为访问方法或者访问类型。同一个查询语句可能可以使用多种不同的访问方法来执行，虽然最后的查询结果可能是一样的，但是执行时间差别可能很大。

二、const

有时可以通过主键列来定位一条记录：

SELECT * FROM single_table WHERE id = 1438;

MySQL会直接利用主键值在聚簇索引中定位对应的用户记录：

类似的，我们根据唯一二级索引列来定位一条记录的速度也是很快的，

SELECT * FROM single_table WHERE key2 = 3841;

这个查询的执行分两步：

  1. 在uk_key2对应的B+树索引中，根据key2列与常数的等值比较条件定位到一条二级索引记录
  2. 再根据该记录的id值到聚簇索引中获取到完整的用户记录

这种通过主键或者唯一二级索引列来定位一条记录的访问方法定义为const，意思是常数级别的，代价是可以忽略不计的。

const访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个const访问方法才有效（这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录）。

对于唯一二级索引来说，查询该列为NULL值的情况比较特殊：

SELECT * FROM single_table WHERE key2 IS NULL;

因为唯一二级索引列并不限制 NULL 值的数量，所以上述语句可能访问到多条记录，也就是说上面这个语句不可以使用const访问方法来执行。

三、ref

有时候可以对某个普通的二级索引列与常数进行等值比较：

SELECT * FROM single_table WHERE key1 = 'abc';

对于这个查询，可以选择全表扫描来逐一对比搜索条件是否满足要求，也可以先使用二级索引找到对应记录的id值，然后再回表到聚簇索引中查找完整的用户记录。由于普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。如果匹配的记录较少，则回表的代价还是比较低的，所以MySQL可能选择使用索引而不是全表扫描的方式来执行查询。

这种搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为ref。

从图示中可以看出，对于普通的二级索引来说，通过索引列进行等值比较后可能匹配到多条连续的记录，而不是像主键或者唯一二级索引那样最多只能匹配1条记录，所以这种ref访问方法比const差了一点，但是在二级索引等值比较时匹配的记录数较少时的效率还是很高的（如果匹配的二级索引记录太多那么回表的成本就太大了）。

采用二级索引来执行查询的时候，其实每获取到一条二级索引记录，就会立刻对其执行回表查询，而不是将所有二级索引记录的主键值都收集起来后再统一执行回表操作。

但是需要注意：

  • 二级索引列值为NULL的情况：不论是普通的二级索引，还是唯一二级索引，它们的索引列对包含NULL值的数量并不限制，所以采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法，而不是const的访问方法。

  • 对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可以采用ref的访问方法：

    SELECT * FROM single_table WHERE key_part1 = 'god like';

    SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary';

    SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill';

    但是如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为ref了：

    SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';

四、ref_or_null

有时候不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来：

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

当使用二级索引而不是全表扫描的方式执行该查询时，对应的扫描区间就是[NULL, NULL]以及[abc, abc]，这种类型的查询使用的访问方法就称为ref_or_null。

可以看出，ref_or_null访问方法只是比ref访问方法多扫描了一些值为NULL的二级索引记录。

值为NULL的记录会被放在索引的最左边。

五、range

有时候我们面对的搜索条件更复杂：

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

然还可以使用全表扫描的方式来执行这个查询，不过也可以使用二级索引 + 回表的方式执行，如果采用二级索引 + 回表的方式来执行的话，那么此时的搜索条件就不只是要求索引列与常数的等值匹配了，而是索引列需要匹配某个或某些范围的值。如果使用uk_key2来执行该查询，那么对应的扫描区间就是[1438, 1438]、[6328, 6328]以及[38, 79]，这种使用索引执行查询，对应的扫描区间为若干个单点扫描区间或者范围扫描区间的访问方法称为range。

仅包含一个单点扫描区间的访问方法不能称为range访问方法，扫描区间为(-∞, +∞)的访问方法也不能称为range访问方法。

六、index

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

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

  • 它的查询列表只有3个列：key_part1, key_part2, key_part3，而索引idx_key_part又包含这三个列。
  • 搜索条件中只有key_part2列。这个列也包含在索引idx_key_part中。

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

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

另外，当通过全表扫描对使用InnoDB存储引擎的表执行查询时，如果添加了ORDER BY 主键的语句，那么该语句在执行时也会被人为地认定为使用的是index访问方法：

SELECT * FROM single_table ORDER BY id;

七、all

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

八、注意事项

8.1 索引合并

MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但是特殊情况除外，在这些特殊情况下也可能在一个查询中使用到多个二级索引，将这种使用到多个索引来完成一次查询的执行方法称之为：index merge。

8.1.1 Intersection合并

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

假设这个查询使用Intersection合并的方式执行的话，那这个过程就是这样的：

  • 从idx_key1二级索引对应的B+树中取出key1 = 'a'的相关记录。
  • 从idx_key3二级索引对应的B+树中取出key3 = 'b'的相关记录。
  • 二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个结果集中id值的交集。
  • 按照上一步生成的id值列表进行回表操作，也就是从聚簇索引中把指定id值的完整用户记录取出来，返回给用户。

问题：为什么不直接使用idx_key1或者idx_key3只根据某个搜索条件去读取一个二级索引，然后回表后再过滤另外一个搜索条件呢？

这里要分析一下两种查询执行方式之间需要的成本代价。

只读取一个二级索引的成本：

  • 按照某个搜索条件读取一个二级索引
  • 根据从该二级索引得到的主键值进行回表操作，然后再过滤其他的搜索条件

读取多个二级索引之后取交集成本：

  • 按照不同的搜索条件分别读取不同的二级索引
  • 将从多个二级索引得到的主键值取交集，然后进行回表操作

虽然读取多个二级索引比读取一个二级索引消耗性能，但是读取二级索引的操作是顺序I/O，而回表操作是随机I/O，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

MySQL在某些特定的情况下才可能会使用到Intersection索引合并：

  • 情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

    比方说下面这个查询可能用到idx_key1和idx_key_part这两个二级索引进行Intersection索引合并的操作：

    SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

    而下面这两个查询就不能进行Intersection索引合并：

    SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

    SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';

    第一个查询是因为对key1进行了范围匹配，第二个查询是因为联合索引idx_key_part中的key_part2列并没有出现在搜索条件中，所以这两个查询不能进行Intersection索引合并。

  • 情况二：主键列可以是范围匹配

    比方说下面这个查询可能用到主键和idx_key1进行Intersection索引合并的操作：

    SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

    对于InnoDB的二级索引来说，记录先是按照索引列进行排序，如果该二级索引是一个联合索引，那么会按照联合索引中的各个列依次排序。而二级索引的用户记录是由索引列 + 主键构成的，二级索引列的值相同的记录可能会有好多条，这些索引列的值相同的记录又是按照主键的值进行排序的。所以重点来了，之所以在二级索引列都是等值匹配的情况下才可能使用Intersection索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。

情况一和情况二只是发生Intersection索引合并的必要条件，不是充分条件。也就是说即使情况一、情况二成立，也不一定发生Intersection索引合并，这得依赖于优化器。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过Intersection索引合并后需要回表的记录数大大减少时才会使用Intersection索引合并。

8.1.2 Union索引合并

SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

Union是并集的意思，适用于使用不同索引的搜索条件之间使用OR连接起来的情况。与Intersection索引合并类似，MySQL在某些特定的情况下才可能会使用到Union索引合并：

  • 情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。

    比方说下面这个查询可能用到idx_key1和idx_key_part这两个二级索引进行Union索引合并的操作：

    SELECT * FROM single_table WHERE key1 = 'a' OR ( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

    而下面这两个查询就不能进行Union索引合并：

    SELECT * FROM single_table WHERE key1 > 'a' OR (key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

    SELECT * FROM single_table WHERE key1 = 'a' OR key_part1 = 'a';

    第一个查询是因为对key1进行了范围匹配，第二个查询是因为联合索引idx_key_part中的key_part2列并没有出现在搜索条件中，所以这两个查询不能进行Union索引合并。

  • 情况二：主键列可以是范围匹配

  • 情况三：使用Intersection索引合并的搜索条件

    搜索条件的某些部分使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取交集，比方说这个查询：

    SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');

    优化器可能采用这样的方式来执行这个查询：

    • 先按照搜索条件key1 = 'a' AND key3 = 'b'从索引idx_key1和idx_key3中使用Intersection索引合并的方式得到一个主键集合。
    • 再按照搜索条件key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'从联合索引idx_key_part中得到另一个主键集合。
    • 采用Union索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回给用户。

当然，查询条件符合了这些情况也不一定就会采用Union索引合并，也得依赖于优化器。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Union索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并。

8.1.3 Sort-Union索引合并

Union索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到，比方说下面这个查询就无法使用到Union索引合并：

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

这是因为根据key1 < 'a'从idx_key1索引中获取的二级索引记录的主键值不是排好序的，根据key3 > 'z'从idx_key3索引中获取的二级索引记录的主键值也不是排好序的，但是key1 < 'a'和key3 > 'z'这两个条件又特别让我们动心，所以可以这样：

  • 先根据key1 < 'a'条件从idx_key1二级索引总获取记录，并按照记录的主键值进行排序
  • 再根据key3 > 'z'条件从idx_key3二级索引总获取记录，并按照记录的主键值进行排序
  • 因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样了。

将上述这种先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并，很显然，这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

为什么有Sort-Union索引合并，就没有Sort-Intersection索引合并么？

是的，的确没有Sort-Intersection索引合并（MariaDB实现了）这么一说，Sort-Union的适用场景是单独根据搜索条件从某个二级索引中获取的记录数比较少，这样即使对这些二级索引记录按照主键值进行排序的成本也不会太高，而Intersection索引合并的适用场景是单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，合并后可以明显降低回表开销，但是如果加入Sort-Intersection后，就需要为大量的二级索引记录按照主键值进行排序，这个成本可能比回表查询都高了，所以也就没有引入Sort-Intersection这个玩意儿。

8.2 索引合并注意事项

联合索引替代Intersection索引合并

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

这个查询之所以可能使用Intersection索引合并的方式执行，是因为idx_key1和idx_key3是两个单独的B+树索引，要是把这两个列搞一个联合索引，那直接使用这个联合索引即可。

ALTER TABLE single_table drop index idx_key1, idx_key3, add index idx_key1_key3(key1, key3);

这样把没用的idx_key1、idx_key3都干掉，再添加一个联合索引idx_key1_key3，使用这个联合索引进行查询简直是又快又好，既不用多读一棵B+树，也不用合并结果，何乐而不为？

不过小心有单独对key3列进行查询的业务场景，这样子不得不再把key3列的单独索引给加上。

相关文章