分享

MySQL核心:索引结构原理

 美好生活谷 2019-05-06
MySQL核心:索引结构原理

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

常见索引类型

Hash 索引

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

MySQL核心:索引结构原理

从上面结构可以看出,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=utf8INSERT INTO `t_user` VALUES ('100', '10'), ('200', '20'), ('300', '30'), ('500', '50'), ('600', '60');

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

MySQL核心:索引结构原理

从上图可以看出,有 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 = ? 都可以使用到这个联合索引。下面我们来看一下其结构,看一下为什么是可以做到这一点的。

MySQL核心:索引结构原理

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

索引下推

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

MySQL核心:索引结构原理
MySQL核心:索引结构原理

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


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

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多