InnoDB单表访问方法

ADiligentMan
80 阅读3分钟

什么是单表访问方法？

执行MySQL查询语句的方式

给出一个示例表方便后续讲解。

CREATE TABLE `single_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `key2` int DEFAULT NULL,
  `key3` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `key_part1` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `key_part2` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `key_part3` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `common_field` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_key2` (`key2`),
  KEY `idx_key1` (`key1`),
  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`),
  KEY `idx_key3` (`key3`) USING BTREE
) ENGINE=InnoDB

const

主键或者唯一索列与常数进行比较。最多只有一条数据满足查询条件

注意：如果唯一索引列允许为Null, 则唯一索引列is null查询不是const。 是ref。后续会讲ref

ref

只有一个单点扫描区间[a,b]的查询 。 符合这个条件的查询有：

  • 普通索引列与常数比较
  • 允许为null的唯一索引和允许为null的普通索引列is null查询。唯一索引允许有多个null值，所以也属于单点扫描区间。
  • 复合索引，符合最左匹配的等值查询

注意：null 记录排在B+树叶子节点的最左边

ref_or_null

索引列等值查询和索引列is null 的OR查询

举例: select * from single_table where key1 = 'a' OR key1 is null

range

多个单点扫描区间 多个范围扫描区间

举例:

select * from single_table where key2 in (1,2)

elect * from single_table where key2 > 1 ADN key2 < 3

index

遍历二级索引记录

举例：

select key_part1, key_part2, key_part3 from single_table where key_part2 = 'a'

该SQL语句，key_part2不符合最左匹配原则且查询的列都可以从索引记录中获取，所以只需要遍历整个二级索引记录即可。相比于遍历主键索引，遍历二级索引的效率更高。因为此二级索引每条记录只存了4个列（key_part1, key_part2, key_part3，id）. 每一页可以容纳更多的记录，进而遍历更少的页，减少IO.

all

全表扫描

遍历聚簇索引记录

index_merge

index_merge访问方法。只有当有多个索引都可以使用的时候，才会使用这种访问方法。 这里注意的一点是， 当有多个索引可以使用的时候，是必要不充分条件。具体还要看优化器的优化结果。

  • intersection merge

select * from single_table where key1 = 1 AND key3 = 2.

对于这种SQL， 有两个索引可以选择。所以一种可行的方式就是，在key1的索引查询到主键ID与在key3索引上查询到主键取交集，然后在回表。这种方式可以减少需要回表的ID数量。

要注意的，只有当遍历索引记录的主键ID是有序的时候，因为这样可以用O(n)的算法取交集，才可以使用这种方式。这样就可以很容易得对两个有序的主键ID序列取交集，遇到满足当遍历到key1的主键和key2的主键相同时，回表，取出整条记录反回给客户端。

  • union merge select * from single_table where key1 = 1 OR key3 = 2.

与intersection类似。只不过是取并集。同样要求主键ID有序，因为这样可以用O(n)的算法取并集。

  • sort-union

与union merge类似，只不过不要求主键ID有序。这样的话需要对主键ID进行排序后，再进行union merge

参考

  • 小孩子4919. 《MySQL是怎样运行的》[M]. 北京: 人民邮电出版社, 2020
avatar
文章
阅读
粉丝