【数据库】Mysql索引总结-面试这一篇就够了

棱角码农

已于 2023-12-10 22:26:17 修改

阅读量880

点赞数 3

文章标签：数据库 mysql

于 2022-03-30 11:03:00 首次发布

本文链接：https://blog.csdn.net/saxon_li/article/details/123830881

版权

一、索引的作用、优缺点、设计原则

1、作用

索引是一种特殊的文件，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

2、优点（就是加速）

（1）极大地加速了索引过程，减少 IO 次数
（2）创建唯一索引，保证了数据库表中的唯一性
（3）加速了表与表之间的连接
（4）针对分组和排序检索时，能够显著减少查询查询中的分组和排序时间

3、缺点

（1）索引表占据物理空间
（2）数据表中的数据增加、修改、删除的同时需要去动态维护索引表，降低了数据的维护速度

4、设计原则

（1）基数（整个集合中不同的个数）较小的类，索引效果较差，没必要建立索引，例如性别

（2）更新十分频繁的字段上不宜建立索引：因为更新操作会变更B+树，重建索引。这个过程是十分消耗数据库性能的

（3）表数据太少的时候，不需要创建索引

（4）不可过度索引，结合业务适量使用。

二、存储引擎与索引

本文的话题是索引，为什么会提出存储引擎这个东西呢？因为不同的存储引擎支持的索引也不同。下表罗列出不同的存储引擎之间的区别：

引擎类型	支持的索引类型
MyISAM	B+树索引、全文索引
InnoDB	Hash索引、B+树索引、全文索引

其中， InnoDB 中的 hash 索引是不需要手动设置的， InnoDB 存储引擎的表会自动根据使用情况，在内存中基于 B+ 树索引创建一个 “自适应的 hash 索引”。

注意， Mysql 5.6 后 InnoDB 才支持全文索引。

三、索引的分类及简介

网上针对索引类型的文章大都不全面且混乱，较为完整的索引分类可以参考我自己整理的下面这张图：

从数据结构角度出发索引可分为 B+ 树索引、 hash 索引、空间索引、全文索引 4 种类型。其中 B+ 树索引使用情况也是最多的。后文重点也是针对该索引类型进行总结整理，但是本节先对这 4 种索引进行一个简单介绍。

1、B+树索引

MyISAM 和 InnoDB 引擎都支持 B 树索引，它底层使用的是 B+ 树这种数据结构来存储数据的，所以本文就暂且称其为 B+ 树索引。B+ 树是一种平衡多路查找的数据结构类型。顾名思义，它是树形结构，有根节点、叶子结点、枝干等元素。

从 B+ 树数据结构再去分类， InnoDB 中的索引类型又可分为聚集索引和非聚集索引。聚集索引包含主键索引，而非聚集索引包含了唯一索引、前缀索引、复合/组合/联合索引和普通索引。详细索引原理和具体分类第四节再进行展开。

2、 hash 索引

hash 索引是基于 hash 表实现的，只有查询条件精确匹配 hash 索引中的所有列的时候，才能用到 hash 索引。对于 hash 索引中的所有列，存储引擎都会为每一行计算一个 hash 值， hash 索引中存储的就是 hash 值。 hash 索引包括键值、 hash 值和指针。因为 hash 索引本身只需要存储对应的 hash 值，所以索引的结构十分紧凑，这也让 hash 索引查找的速度非常快。

然而， hash 索引也是存在其限制的。（可以用于回答：为什么 Mysql 优先选择 B+ 树索引而不是 hash 索引呢？ ）

首先， hash 索引存储的是 hash 值而不是键值，所以无法用于外排序，不能进行排序后索引。
二是，hash 索引是无序的，故不能进行范围查找。
三是，hash 索引不支持部分索引查找，只能用到等值查询，不能模糊查询。
四是， hash 索引中的 hash 值的计算可能存在 hash 冲突，当出现 hash 冲突的时候，存储引擎必须遍历冲突 hash 值整个链表中的所有行指针，逐行比较，直到找到所有的符合条件的行，若 hash 冲突很多的话，一些 hash 索引的维护代价会很高，所以说 hash 索引不适用于重复值很多的列上，例如性别。

　　上文提及的 InnoDB 的“自适应 hash 索引”。就是当 InnoDB 注意到某些索引值被使用的非常频繁时，它会在内存中基于 B+ 树索引上再创建一个 hash 索引，这样就让 B+ 树索引也具有hash索引的一些优点。这是一个完全自动的内部的行为，用户无法控制或配置，不过该功能可被关闭。

3、空间索引

MySQL 在5.7之后的版本支持了空间索引，而且支持 OpenGIS 几何数据模型。（比较新，国内相关文档比较少，可以直接去 MySQL 官网了解相关信息，本文不做赘述。）

4、全文索引

全文索引适用于基于相似度的查询，而不是精确数值比较的应用场景上。使用 like + % 模糊匹配方法可以实现上述功能，但是对于数据量很大的文本数据检索上，全文索引与 like + % 的效率不是一个数量级的，会快很多，但是全文索引也可能存在精度问题（搜索引擎的原理与全文索引就很类似）。而且，全文索引使用时和常用的模糊匹配使用 like + % 不同，它有自己的语法格式，利用 match 和 against 关键字来实现搜索功能。

注意，我们只能在文本类型 CHAR 、 VARCHAR 、 TEXT 类型字段上创建全文索引。MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎才均支持全文索引。

四、重点索引解析（ B+ 树）

（一）B+ 树索引的原理及分类

1、为什么选择 B+ 树而不是二叉树、平衡二叉树、红黑树做 Mysql 的索引呢？

因为在数据量很大的时候，索引无法全部装入内存，由于无法装入内存，则必然依赖磁盘存储。而内存的读写速度是磁盘的成千上万倍，因此核心问题是“如何减少磁盘 IO 次数”。而二叉树、平衡二叉树、红黑树的深度会非常大，会造成磁盘 IO 频繁读写，进而导致效率低下的情况。

2、 B 树和 B+ 树索引的原理及对比

（1） B 树的原理

如上图所示，B 树是一棵平衡多路查找树，它的性质如下：

每个节点最多有 m 个孩子
每个中间节点最少有 m/2 孩子
根节点至少有两个子节点（树的高度大于 1 的情况下）
有k个关键字(关键字按递增次序排列)的非叶结点恰好有k+1个孩子
所有叶子节点在同一层，即所有叶子节点高度一致

很多第一次接触这个数据结构的同学，只看定义是不是有点蒙。其实在作为数据库索引的时候，我们只需要知道 B 树像一棵“m叉搜索树”（m是子树的最大个数），这种数据结构对比前面提到的二叉树等结构可以大大降低树高，并且

非叶子结点的关键字： $k^{1},k^{2},...,k^{m}$ 中， $k^{i}< k^{i+1}$
非叶子结点的指针可以指向下级索引。当子结点的指针指向下级索引时： $p^{1},p^{2},...,p^{m}$ ；其中 $p^{1}$ 指向关键字小于 $k^{1}$ 的子树， $p^{m}$ 指向关键字大于 $k^{m}$ 的子树，其它 $p^{i}$ 指向关键字属于 $(k^{i-1},k^{i})$ 的子树
所有节点均可含有指向主文件的数据记录的指针（data部分）

（2） B+ 树的原理

B+ 树是在 B 树基础上的一种优化，使其更适合实现外存储索引结构， B+ 树的性质如下：

非叶节点的子树指针与关键字个数相同
为所有叶子节点增加一个链指针
所有关键字都在叶子节点出现，且链表中的关键字恰好是有序的
非叶子节点相当于是叶子节点的索引,叶子节点相当于是存储(关键字)数据的数据层

从 B 树结构图中可以看到每个节点中不仅包含数据的关键字值（key），还有主文件中的数据记录值（ data ）。但是每一个节点的存储空间是有限的，如果 data 较大时将会导致每个节点能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B 树的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在 B+ 树中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+ 树的高度。

（3） B 树和 B+ 树对比

通过前面对 B 树和 B+ 树的介绍，下对二者的区别做个总结：

B+ 树非叶子节点只存储关键字信息和指向子节点的指针，而 B 树可能还会存储数据记录
B+ 树所有叶子节点之间都有一个链指针，用于加速查询
B+ 树数据记录都存放在叶子节点中，而且数据是按照顺序排列的
B+ 树中同一关键字可能会在不同节点中出现多次，而 B 树只会出现一次

这些区别会导致 B+ 树比 B 树更加适合作为数据库的索引，理由如下：

B 树只适合随机检索，而 B+ 树同时支持随机检索和顺序检索。B 树在提高了磁盘 IO 性能的同时并没有解决元素遍历的效率低下的问题。 B+ 树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的操作。
B+ 树空间利用率更高，可减少I/O次数，磁盘读写代价更低（高度更低）。
B+ 树的查询效率更加稳定。因为 B 树查询有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在 B+ 树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
B+ 树增删文件（节点）时，效率更高。因为 B+ 树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

3、聚簇索引和非聚簇索引的特点及对比

从 B+ 树数据结构再去分类， InnoDB 中的索引类型又可分为聚集索引和非聚集索引。

（1）聚集索引的特点

聚簇索引是物理索引，索引文件和数据文件为同一份文件。即表数据是按照聚簇索引的顺序存储的，索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子节点即存储了真实的数据行，不再有单独的数据页，一张表最多只能创建一个聚集索引。

（2）非聚集索引的特点

B+ 树的叶子节点上的 data ，并不是数据本身，而是数据存放的地址。表数据存储顺序与索引顺序无关。叶子节点包含索引字段值以及指向数据行的逻辑指针。

（3）聚集索引和非聚集索引的对比

聚簇索引的查找记录要比非聚簇索引块，因为聚簇索引查找到索引就查找到了数据位置，而非聚簇索引查找到索引之后，根据记录的数据地址，再去查找数据（回表，但是如果非聚簇索引命中所有查询，就不必回表）
一个数据表只能有一个聚簇索引，但可以有多个非聚簇索引
聚簇索引和非聚簇索引都可以加快查询速度，但同时也都对写入速度会有影响，聚簇索引对写入的速度影响更大一些，因为需要调整数据表的物理存储

（二）聚簇索引分类

1、主键索引

主索引，使用数据表的一个或者多个列组成建立索引列，要求该索引列是唯一的（不允许重复）、自增的、不能为 NULL 的。一般是在建表的时候指定了主键，就会创建主键索引， CREATE INDEX 不能用来创建主键索引，使用 ALTER/CREATE TABLE 时指定主键来代替。

（三）非聚簇索引分类

1、唯一索引

使用数据表的一个或者多个列的组合建立索引列，要求该索引列是唯一的（不允许重复），但可以为 NULL （但是只允许存在一条记录是 NULL 的，不然就不唯一了）。

2、前缀索引

在数据表中需要建立索引的列内均为很长的字符串，这会让索引变得大且慢。通常可以索引开始的部分字符（不一定非要从首位开始），这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指不重复的索引值（也称为基数，cardinality)和数据表的记录总数的比值，索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

3、复合/组合/联合索引

使用数据表中两个或者两个以上的列组成的一个新索引，但是如果在使用过程中操作不当，容易致使索引失效，下给出引起复合索引失效的条件及样例：

不遵循最佳左前缀失效

范围查询右侧失效
like 使用不当失效

具体为什么会导致复合索引失效，可以参考下面这篇文章，讲的非常清晰明了（当然有一些地方有些小笔误，不过很容易发现并理解）。复合索引失效原理

应用：覆盖索引

A:select age from table_name where name= '张三'
B:select phonenumber from table_name where name= '张三'

继续利用上述例子。

语句A：因为索引内保存有 name 和 age 的值，所以通过 name 在索引中查找到 age 后，可以直接返回查询结果，不需要回表。即在这个查询中，索引已经 “覆盖了” 我们的查询需求，称该类索引为覆盖索引
语句B：执行过程中先在索引中找到 name=‘张三’ 对应的主键, 通过回表在主键索引中找到满足条件的数据 phonenumber

故当 sql 语句的所求查询字段（ select 列）和查询条件字段（ where 子句）全都包含在一个索引中（复合索引），可以直接使用索引查询而不需要回表，这就是覆盖索引。

4、普通索引

使用数据表中的普通列构建索引列，是最基本的索引，没有任何限制。

棱角码农

关注

3
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
【数据库】Mysql索引总结-面试这一篇就够了

一、索引的作用、优缺点1、作用索引是一种特殊的文件，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。2、优点（就是加速）（1）极大地加速了索引过程，减少 IO 次数（2）创建唯一索引，保证了数据库表中的唯一性（3）加速了表与表之间的连接（4）针对分组和排序检索时，能够显著减少查询查询中的分组和排序时间3、缺点（1）索引表占据物理空间（2）数据表中的数据增加、修改、删除的同时需要去动态维护索引表，降低...
复制链接

扫一扫