MySQL 是怎样运行的 - 单表访问方法

手辰_
549 阅读5分钟

一、前期准备

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个二级索引，分别是：

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

  • 然后我们需要为这个表插入10000行记录，除id列外其余的列都插入随机值就好了

二、访问方法的概念

  • 设计MySQL的大叔把查询的执行方式大致分为下边两种：

    • 使用全表扫描进行查询

      • 这种执行方式很好理解，就是把表的每一行记录都扫一遍嘛，把符合搜索条件的记录加入到结果集就完了。不管是啥查询都可以使用这种方式执行，当然，这种也是最笨的执行方式。

    • 使用索引进行查询

      • 如果查询语句中的搜索条件可以使用到某个索引，那直接使用索引来执行查询可能会加快查询执行的时间

  • 一个查询语句可能可以使用多种不同的访问方法来执行，虽然最后的查询结果都是一样的，但是执行的时间可能差老鼻子远了

1、const

  • 直接使用主键索引
SELECT * FROM single_table WHERE id = 1438;
  • 使用二级索引,可能需要回表
SELECT * FROM single_table WHERE key2 = 3841;

image.png

  • 把这种通过主键或者唯一二级索引列来定位一条记录的访问方法定义为：const，意思是常数级别的，代价是可以忽略不计的。不过这种const访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个const访问方法才有效（这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录）。

2、ref

  • 有时候我们对某个普通的二级索引列与常数进行等值比较，比如这样：
SELECT * FROM single_table WHERE key1 = 'abc';
  • 我们也可以先使用二级索引找到对应记录的id值，然后再回表到聚簇索引中查找完整的用户记录。由于普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数

image.png

3、ref_or_null

  • 把该列的值为NULL的记录也找出来，就像下边这个查询：
SELECT * FROM single_table WHERE key1 = 'abc' OR key1 IS NULL;
  • 当使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为ref_or_null，这个ref_or_null访问方法的执行过程如下：

image.png

4、range

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

image.png

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

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

5、all

  • 全表扫描

6、index merge

  • 特殊情况下也可能在一个查询中使用到多个二级索引

（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值的完整用户记录取出来，返回给用户。

（2）Union合并

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

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

avatar
文章
阅读
粉丝