MySQL-单表访问方法

MySQL

字数统计: 636阅读时长: 2 min

 2020/03/14 

访问方法 (access method)

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

工具表：

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;

const

通过主键定位一条记录
通过唯一二级索引定位一条记录

ref

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

1	`select * from single_table where key1 = 'abc';`

ref_or_null

1	`select * from single_table where key1= 'abc' or key1 IS NULL;`

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

range

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

index

遍历二级索引记录的执行方式称之为：index

1	`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。

原文作者：June

原文链接：https://jatshw.com/2020/03/14/MySQL-%E5%8D%95%E8%A1%A8%E8%AE%BF%E9%97%AE%E6%96%B9%E6%B3%95/

发表日期：March 14th 2020, 11:26:00 pm

更新日期：March 21st 2023, 9:49:34 pm