Mysql学习笔记 - 聚簇索引和回表查询的关系

2020-08-12

全文 ≈ 982字 | 预计阅读时长 3分钟

之前简单了解过Mysql的索引，今天来学习一下Mysql（InnoDB）的聚簇索引以及SQL为什么会产生回表查询？

1. 什么是回表查询？

都知道Mysql存储的数据结构是B+Tree，所以当查询数据的时候能最快找到叶子节点的检索方式时是最快的。比如：主键直接定位行记录，而有些查询需要先检索索引树找到叶子节点的主键值，再通过主键值定位行记录这种扫描2次索引树的方式就叫做回表查询。

如何确定SQL语句会不会造成回表查询？如下表：

CREATE TABLE `users`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(4) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `user`(`user`) USING BTREE,
  INDEX `name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;

2种查询方式：

# 未回表查询
EXPLAIN SELECT id,name FROM users WHERE name = 'test1';
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|    1 | SIMPLE      | users | ref  | name          | name | 202     | const |    1 | Using where; Using index |
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

# 回表查询
EXPLAIN SELECT id,user,name FROM users WHERE name = 'test1';
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | users | ref  | name          | name | 202     | const |    1 | Using index condition |
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

注意看Extra字段，当值为Using index condition时表示该SQL需要回表查询，那以上两条SQL到底有什么不同呢？

SQL1命中name索引并且在索引树的叶子节点找到主键id，满足了查询需求所以不需要回表查询其他字段。
SQL2同样是命中name索引并且在索引树叶子节点找到主键id，但还有一个user字段没有得到，所以需要拿着主键id去索引树查询user字段。

以上就是是否回表查询的区别，回表查询会额外产生一次查询的开销，故此效率较低。不过回表查询和聚簇索引又有什么关联呢？为什么回表查询需要遍历2次索引树呢？

2. 聚簇索引

InnoDB的索引类型之前有说过，多数使用B+Tree做索引但在实现上又区分为：聚簇索引和辅助索引。

聚簇索引（Clustered Index）

聚簇索引的叶子节点存储行记录，InnoDB有且只有一个聚簇索引。聚簇索引的每一个叶子节点都有一个指向相邻叶子节点的指针，所以面对Range查询聚簇索引效率很高。

InnoDB默认主键是聚簇索引，如果没有定义主键则第一个Not Null & Unique索引列为聚簇索引。如果以上条件都不满足，则会生成一个6字节的隐式自增长主键`row-id`。

（这也是为什么InnoDB引擎下要求数据表尽可能都要创建主键的原因。）

辅助索引（Secondary Index）

辅助索引的叶子节点存储主键值（聚簇索引）。

了解了索引树的结构，其实也就明白了为什么明明命中了索引却还会产生回表查询需要扫描2次索引树，即：先扫描辅助索引树拿到主键值，再扫描聚簇索引树获取行记录。

3. 如何避免回表查询？

避免回表查询这里有个概念：覆盖索引（Covering index），Mysql官方虽然没有明确定义覆盖索引但是有同样的概念出现。

查询的字段尽可能在一棵索引树都能获取到，避免回表。

（概念出处：Using index）

具体怎么操作呢？直接上SQL。

1 2	ALTER TABLE `users` DROP INDEX `name`; ALTER TABLE `users` ADD INDEX `user_name` ( `user`, `name` );

创建user、name复合索引，这样就能够覆盖索引不需要回表。

最后

埋个坑，后面有时间会继续学习相比辅助索引聚簇索引平均会减少多少次IO操作。