MySQL索引结构

数据库的索引就像一本书的目录一样，它可以快速定位你所需要的信息。下面来详细说一下MySQL的索引结构。

常见索引类型

Hash索引

Hash索引的底层实现是由Hash表来实现的，非常适合以 key-value 的形式查询，也就是单个key 查询，或者说是等值查询。其结构如下所示：

从上面结构可以看出，Hash 索引可以比较方便的提供等值查询的场景。但是对于范围查询的话，就需要进行全表扫描了。

B+索引

Hash结构的索引比较适合缓存的存储。对于使用关系型数据库而言，笔者更多的使用的是B+ 索引。当然对于MySQL 我们最常用的存储引擎就是InnoDB 了，对于B+ 索引后面将详细介绍一下。

InnoDB的索引结构

首先先创建一个简单的表，结构如下：

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `age` int(10) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO `t_user` VALUES ('100', '10'), ('200', '20'), ('300', '30'), ('500', '50'), ('600', '60');

上面表和数据的存储结构大致如下所示：

从上图可以看出，有 2 个索引结构：主键ID 索引和普通索引。主键索引的叶子节点存储的是行数据的内容（聚簇索引），普通索引的叶子节点存储的是主键的值（非聚簇索引/二级索引）。

主键索引和普通索引的区别

当我们使用主键索引查询记录时，查询语句如下所示。此时只需要一次主键索引树的查找即可返回数据行。

SELECT * FROM t_user WHERE id = 100;

如果使用普通索引，idx_age 查询记录，如下所示。此时就会查找2 个索引树的结构。首先根据idx_age 查找到记录的主键值为 100，然后再根据主键索引树查找到对应的记录行，这个过程称为回表。

SELECT * FROM t_user WHERE age = 10;

索引维护

B+ 树为了维持索引的有序性，在新插入记录时需要有一定的开销。如上图所示，如果需要再插入一个id = 700 的记录行，此时只需要在 User5 后面新增一条记录即可。但是如果需要新增一个 id = 400 的记录行时，此时就需要移动数据了，这个和有序数组的插入类似。

比较极端的一种场景是，此时User5 所在的数据页已经满了。此时如果再插入一条记录，就需要移动部分数据行到新页上面去。这种情况下，性能会受到一定的影响。除此之外，页分裂还存在着空间利用率的问题。

当然，有页分裂就有数据页的合并，当空间利用率低到一定程度的时候，就会触发分页数据的合并。

主键ID自增

从上面的描述我们可以看出，主键ID的乱序插入或者删除可能对性能造成很大的影响。这就是为什么，我们在大多数场景下对于主键都是自增的。这样一来，就可以充分的利用分页数据块的空间了，也不会对性能造成影响。

覆盖索引

上面我们已经提到了 回表的概念了，也就是普通索引的查询，可能会再到主键索引上面再搜索一遍。但是如果我们执行如下语句：

SELECT id FROM t_user WHERE age = 10;

此时，普通索引 idx_age 的叶子节点上面，就已经包含了id 的value值了，此时就不需要回表了，这个就称之为“覆盖索引”（覆盖索引是一种优化查询的方式，不是索引的分类）。

联合索引

我们创建索引时，也会经常创建如 idx_name_age (name, age) 这样的索引结构。并且还知道 WHERE 条件中 name = ? AND age = ? 和 name = ? 都可以使用到这个联合索引。下面我们来看一下其结构，看一下为什么是可以做到这一点的。

从上面结构可以看出，数据是按照 联合索引 从左到右的顺序进行排序的。由此看来，不论使用 name AND age 或者name 来查询，不论等值或者 左前缀模糊查询，都可以用到复合索引。这里面需要注意的是，只有左前缀的模糊匹配才可以使用此联合索引。因为从索引结构看来，符合左前缀的顺序排序。

索引下推

前面的部分我们知道，左前缀的模糊查询可以使用索引。还是上面的例子，索引(name, age) ，当我们 WHERE条件中使用 name LIKE ‘张%’ AND age = 10 时。MySQL 5.6 及以后的版本可以对查询做下推的优化，如下图所示：


从上图可以看出，当做了下推优化后，MySQL会隔断一些不满足条件的记录 进行回表操作，从一定程度上有了性能的提升。

---

参考：《极客时间：MySQL实战》、《高性能MySQL》

2018年除夕夜

于上海浦东
链接：http://moguhu.com/article/detail?articleId=117