MySQL的单表访问方法

IT小东北
140 阅读6分钟

首先提醒一下！！！！！！本文不是介绍索引的。也不是介绍锁的（当然啦，后续会慢慢讲的啦）文本只是简单介绍一下MySQL怎么执行单表查询的

最近在学习MySQL，突然发现一个名字‘访问方法’。看了看是工作当中经常会被我们忽视的。这里就简单总结一下吧。如果不喜欢的话！！ 出门左转 。 如果有错误欢迎大家改正哦！！

二话不说进入正题！！先创建个表吧

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;

我们为这个表建立了一个聚簇索引和4个二级索引。

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

这个表一定要记住啊！！！！

访问方法的概念

MySQL把执行查询语句的方式称之为访问方法或者访问类型。看到这里是不是很蒙圈，感觉是废话但是感觉又有点道理。哈哈哈哈哈 我也是这样觉得的。其实MySQL把访问查询的执行方式大致分为以下俩种

  • 使用全表扫描
  • 使用索引进行扫描
    • 使用索引扫描又分为很多种
      • 针对主键或唯一二级索引的等值查询
      • 针对普通二级索引的等值查询
      • 针对索引列的范围查询
      • 直接扫描整个索引

接下来让我们看一下MySQL对于单表来说访问方法都有哪些吧。

const

有的时候我们可以通过主键来定位一条记录。例如

SELECT * FROM single_table WHERE id = 22;

这个执行方式就不跟老家唠叨了吧。很明显id是主键嘛 主键对应聚簇索引。聚簇索引的非叶子结点是按照主键进行排序的，然后叶子结点就是对应的行记录，直接就查出来了嘛 。

SELECT * FROM single_table WHERE key2 = 3841;

这个SQL呢 不就是在二级索引找到id 然后回表嘛 so easy

MySQL认为通过主键或者唯一二级索引列与常数值做比较特别快。因此把这种通过主键或者唯一二级索引列来定位一条记录的访问方法定义为： const ，意思是常数级别的

注意啦 SELECT * FROM single_table WHERE key2 IS NULL; 这种情况就不是const。虽然key2是唯一二级索引但是唯一二级索引并不限制NULL值的数量。所以他不是const。

ref

普通的二级索引列与常数进行等值比较

SELECT * FROM single_table WHERE key1 = 'abcd';

对于这个查询我们可以全表扫描也可以用二级索引搜索然后在进行回表操作。当然啦具体采用哪种还是听人家MySQL的。如果使用二级索引进行匹配的条数特别少，回表的代价还是很低的。这时候MySQL会采用使用key1的普通二级索引方式。

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

  • 二级索引列值为 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';

例如上面这俩个

ref_or_null

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

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

当使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为ref_or_null 上边的查询相当于先分别从 idx_key1 索引对应的 B+ 树中找出 key1 IS NULL 和 key1 = 'abc' 的两个连续的记录范围，然后根据这些二级索引记录中的 id 值再回表查找完整的用户记录。

range

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

这个SQL仍然可以使用全表扫描或者二级索引+回表。咱们这就默认让他使用二级索引+回表方式了。这种情况索引列需要匹配某个或某些范围的值。

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

是不是有点蒙圈。key_part2 不是联合索引 idx_key_part 最左索引列。怎么还能用idx_key_part这个索引呢。其实这块仔细想想就是相当于完整的遍历了一次这个索引树。。当然这种情况是比较少见的

all

全表扫描。四个字概括

总结一下吧

  • const：通过主键或者唯一二级索引列来定位一条记录的访问方法定义为： const
  • ref：搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为： ref 。
  • ref_or_null：当使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为ref_or_null 
  • range利用索引进行范围匹配的访问方法称之为： range 。
  • index：采用遍历二级索引记录的执行方式称之为： index 。 
  • all：全表扫描

注意这里面的效率从上往下是越来越低的奥！！

最后有的萌新就会问了 啊！！我咋不知道这个东西啥时候用到了呢！！！ 当我们用explain 看语句执行计划时

image.png type对应的就是这个计划的访问方法。如果你的SQL都是all。恭喜你，下一个毕业的人可能就是你了。

avatar
文章
阅读
粉丝