MySQL索引

最新推荐文章于 2023-07-30 16:45:49 发布

还能坚持

最新推荐文章于 2023-07-30 16:45:49 发布

阅读量251

点赞数 2

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_35091353/article/details/116670496

版权

MySQL 专栏收录该内容

17 篇文章 6 订阅

订阅专栏

1、摘要

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如B+Tree索引，哈希索引，全文索引等等。为了避免混乱，本文将只关注于B+Tree索引。

2、索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

自己对索引的理解：索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

查询是数据库最主要功能之一，最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的。随着计算机科学的发展，提供了很多更优秀的查找算法，如二分查找（binary search）、二叉树查找（binary tree search）等。稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，因为数据本身的组织结构不可能完全满足各种数据结构，所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

图1展示了一种可能的索引方式。左边是数据表，一共有七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上并一定物理相邻）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log₂n)的复杂度内获取到相应数据。

3、B-树、B+树

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引

3.1 M阶B树（B-树）

3.1.1 特点

每个分支结点最多有M棵子树
在B+树中，有n个关键字的非叶子结点含有n棵子树（B树是n+1棵），即每个关键字对应一颗子树，这些关键字不保存数据，只用来索引，所有数据都保存在叶子节点（B树的每个关键字都保存数据）
除根节点外的所有非叶节点至少含有（M/2（向上取整）-1）个关键字，每个节点最多有M-1个关键字，并且以升序排列，所以，M阶B树的除根节点外的所有非叶节点的关键字取值区间为[M/2-1(向上取整)，M-1]
根节点至少含有1个关键字，每个节点最多有M-1个关键字，并且以升序排列，所以，M阶B树的根节点的关键字取值区间为[1，M-1]
又称多路平衡查找树

3.1.2 B树示例

使用下面的数字构造4阶B树

6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4

构造好的4阶B树：
构建过程

3.2 M阶B+树

3.2.1 特点

每个分支结点最多有M棵子树
在B+树中，有n个关键字的非叶子结点含有n棵子树（B树是n+1棵），即每个关键字对应一颗子树，这些关键字不保存数据，只用来索引，所有数据都保存在叶子节点（B树的每个关键字都保存数据）
除根节点外的所有非叶节点至少含有（M/2（向上取整））个关键字，每个节点最多有M个关键字，并且以升序排列，所以，M阶B+树的除根节点外的所有非叶节点的关键字取值区间为[M/2(向上取整)，M]
根节点至少含有1个关键字，每个节点最多有M个关键字，并且以升序排列，所以，M阶B树的根节点的关键字取值区间为[1，M]
所有的叶结点包含全部关键字，及指向相应记录的指针，且叶结点将关键字按照大小顺序排列，并且相邻叶节点按大小顺序相互链接起来（叶子节点组成一个链表。通常在B+树上有两个头指针，一个指向根结点，一个指向关键字最小的叶子结点。）
所有分支结点仅包含它的各个子结点中关键字的最大值及指向其子结点的指针
同一个关键字会在不同结点中重复出现，根节点的最大元素就是B+树的最大元素

3.2.2 B+树示例

在这里插入图片描述

3.3 B-树与B+树的区别

B树中每个结点（非根节点）关键字个数的范围为 [M/2-1(向上取整)，M-1] (根节点为[1, M-1]) ，并且具有n个关键字的节点包含（n+1）棵子树。B+树中每个结点（非根节点）关键字个数的范围为[M/2(向上取整)，M](根节点为[1,M])，具有n个关键字的节点包含n棵子树。
在B树中，叶结点包含的关键字和其它结点包含的关键字是不重复的。在B+树中，叶结点包含了全部关键字，即在非叶结点中出现的关键字也会出现在叶结点中
在B+树中，叶结点包含全部信息，所有非叶节点仅起到索引作用，非叶结点中的每个索引项只含有对应子树的最大关键字和指向该子树的指针，不含有该关键字对应记录的存储地址

3.4 B+树相对于B树的优点

B+树所有叶子结点使用链表相连，便于区间查找和遍历，而B树则需要进行递归遍历，且B树相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。
B+树的中间节点不保存数据，能容纳更多节点元素。

4、MySQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，这一部分主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

4.1 MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：
在这里插入图片描述
表一共有三列，假设以Col1为主键，上图是一个MyISAM表的主索引（Primary key）示意，可以看出，MyISAM的data域仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：
在这里插入图片描述
辅助索引同样也是一颗B+Tree，data域保存数据记录的地址。因此，在MyISAM中，首先按照B+Tree的搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分
InnoDB存储引擎用B+Tree作为索引结构，叶节点的data域存放的是数据记录
MyISAM存储引擎用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。MyISAM存储引擎可以不需要主键，这是因为MyISAM存储引擎会单独存储数据，索引上存放的是指向数据的指针。

4.2 InnoDB索引实现

InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据存储在叶结点上。MyISAM的叶结点仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶结点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
第二个与MyISAM索引的不同是InnoDB的辅助索引的data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助：

例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时，数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分
InnoDB存储引擎用B+Tree作为索引结构，叶节点的data域存放的是数据记录
MyISAM存储引擎用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。MyISAM存储引擎可以不需要主键，这是因为MyISAM存储引擎会单独存储数据，索引上存放的是指向数据的指针。

4、聚集索引、非聚集索引

索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构，主流索引结构有B+树以及Hash结构，聚集索引和非聚集索引用使用的就是B+树。

SQL Sever索引类型有：唯一索引，主键索引，聚集索引，非聚集索引
MySQL 索引类型有：唯一索引，主键（聚集）索引，非聚集索引，全文索引

4.1 聚集索引（也叫聚簇索引）

4.1.1 定义

数据行的物理顺序与索引（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引

4.1.2 聚集索引的形成

MySQL的InnoDB引擎必须要有主键（MyISAM可以没有），即使不设置主键，MySQL也会设置一个默认主键，InnoDB会按照如下规则进行处理：

如果一个主键被定义了，那么这个主键就是作为聚集索引
如果没有主键被定义，那么该表第一个唯一非空索引被作为聚集索引
如果没有主键也没有合适的唯一索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增

4.1.3 聚集索引数据结构

在这里插入图片描述
其中，树的所有结点（底部除外）的数据都是由主键字段中的数据构成，最下面部分是真正的数据。假如我们执行一个SQL语句：

select * from table where id = 1256;

首先根据索引定位到1256这个值所在的叶结点，然后再通过叶结点取到等于1256的数据行：
在这里插入图片描述

4.2 非聚集索引

4.2.1 定义

索引的逻辑顺序与磁盘上数据行的物理存储顺序不同，一个表中可以拥有多个非聚集索引
其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，它的结构顺序与实际存放顺序不一定一致

4.2.2 非聚集索引的形成

非聚集索引找到存储的主键值，再通过聚集索引定位到数据，聚集索引（主键）是通往真实数据的唯一路径
如果给表中多个字段加上索引，那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。

4.2.3 非聚集索引的数据结构

非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据，如下图：
在这里插入图片描述

4.3 区别

聚集索引的叶节点上存的是数据；非聚集索引的叶节点存的是数据的指针
聚集索引是物理上连续存在；非聚集索引是逻辑上的连续，物理存储不连续。
聚集索引每张表只能有一个；非聚集索引可以有多个
MySQL的InnoDB引擎必须要有主键（MyISAM可以没有），即使不设置主键，MySQL也会设置一个默认主键。聚集索引就是建立在主键（或唯一索引）上，数据存放在data域。其他索引也就是非聚集索引或者叫二级索引（辅助索引）存放的是主键的数据，从而根据主键值查找到数据
MyISAM 引擎是非聚集索引方式（叶子节点存放的是指针）；InnoDB引擎可以是聚集索引方式（叶子结点存放的是数据），也非聚集索引方式（叶子节点存放的是主键）；

参考：https://blog.csdn.net/weixin_42228338/article/details/97684517
参考：https://blog.csdn.net/xby7437/article/details/106519503
参考：https://zhuanlan.zhihu.com/p/23624390
参考：https://blog.csdn.net/a6686039/article/details/89019666
参考：http://blog.codinglabs.org/articles/theory-of-mysql-index.html
参考：https://xianshang.blog.csdn.net/article/details/85254407