MySQL索引基础

sermonlizhi

已于 2022-04-27 07:44:26 修改

阅读量1.4k

点赞数 2

分类专栏：神奇的MySQL 文章标签： mysql 索引 innodb

于 2022-04-26 19:02:14 首次发布

本文链接：https://blog.csdn.net/sermonlizhi/article/details/124434486

版权

神奇的MySQL 专栏收录该内容

16 篇文章 30 订阅

订阅专栏

索引作为MySQL最为重要的组成部分，它通过将数据按照一定规则进行排序，形成一种特定的数据结构，从而加速数据的访问效率。

一、索引数据结构

索引数据结构通常有如下几种选择：

二叉树、红黑树、Hash表、B树、B+树，这些数据结构都有各自的特性。

二叉树

二叉树是最普通的树结构，对结构平衡等都没有特别的实现，新增节点时只是按照已有树结构的排列顺序，比当前节点值大就放在右节点上，比当前节点值小就放在左节点上，通常二叉树的可以有如下结构：

如果数据是按照递增的方式插入就会出现上边左图的情况，数据的遍历就变成线性的了。

可以使用在线工具来演示常用数据结构的节点插入和删除。

红黑树

红黑树是一个平衡二叉树，与完成平衡二叉树相比，红黑树追求的是局部平衡而不是全局平衡，因此在插入或删除元素时，为了保持平衡，需要进行旋转，但它的旋转次数要比完成平衡二叉树少很多，但在节点数目相同的情况下，完全平衡二叉树的高度要低于红黑树。结构如下：

注：数据量很大的时候，树就会变得很深。而数据库的资源是放在磁盘文件的，树越深意味着查询时I/O的次数就越多，性能也就越低

Hash表

通过Hash算法计算hash值，然后计算元素出元素所在的位置，Hash表的数据结构通常采用数组+链表的方式实现，对于Hash冲突的元素，加在对应槽位的链表后面。

MySQL提供了Hash索引这样的实现，但它只能满足“=”和“IN”这样的查询，没法满足范围查询，同时有Hash冲突要解决，MySQL通过链地址法，当数据量很多是，Hash冲突的链表元素太多，查询效率也会大大降低

B树

B树是一个平衡多路查找树，即它的父节点可以有多个子节点，同时B树中所有节点的元素是不能重复的，如图所示：
在这里插入图片描述

平衡策略：

一颗m阶B-树的特征(m为最多子节点个数)：
①、一个节点最多有m个孩子。
②、除了叶子节点和根基点外，其他每个节点至少有Ceil(m/2)个孩子(让树的深度尽量的小)
③、若根节点不是叶子节点，则至少有2个孩子
④、所有叶子节点都在同一层，且不包含其他关键字信息
⑤、每个非终端节点包含n个关键字信息(P0,P1,P2,K1,K2,K3)
⑥、关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
⑦、Ki(i=1…n)为关键字，且关键字升序升序排列
⑧、Pi(i=1…n)为指向子树根节点的指针,P(i-1)所指的子树的所有节点关键字均小于Ki，但都大于K(i-1)
总结：中间节点，至少有(m/2)的子节点，每个节点中的关键字个数在(m/2)-1和(m-1)之间，就靠这两个条规则去平衡B树

在MySQL中，B树的每个节点中，不仅会存储关键字信息，还会存储关键字对应的数据信息。

B+树

B+树是对B树的增强，它们的平衡策略是一样的，只是结构有所不同，B+树的叶子节点通过链表连接，如下图所示：
在这里插入图片描述

在MySQL中，B+树与B树最大的不同在于，它的所有非叶子节点都不存储具体数据，而只是记录关键字信息，而叶子节点中才会记录完整的数据。
叶子节点之间通过链表连接，MyISAM引擎中，B+树还是单向链表，但在Innodb引擎中使用双向链表，子节点使用链表可以提高范围查询的效率

二、存储引擎与索引

MySQL支持多种存储引擎，用的比较广泛的是MyISAM和InnoDB两种存储引擎，这两种存储引擎在选择索引的数据结构时有所不同。

2.1 MyISAM

2.1.1 非聚簇索引(稀疏索引)

MyISAM使用的是非聚簇索引，即索引数据结构采用B树来实现，但对B树的结构进行了调整，叶子节点中的索引数据从左到右递增排列。每个叶子节点中，不仅会存放关键字信息，还会记录节点索引对应的磁盘地址。下面两张图展示了原始B树和MyISAM索引结构的一些区别：

B树：

MyISAM索引结构：

2.1.2 数据存储

MyISAM和InnoDB都是采用磁盘来存储数据，所以当新建一张表并插入数据时，一定会对应有具体的文件来记录这些数据。

MyISAM的数据存储文件分为三个，分别是xx.frm、xx.MYD、xx.MYI，其中xx表示数据库的表名称，其中.frm文件存储的是表结构，.MYD文件存储的是具体的数据，而.MYI文件存储的则是索引对应的磁盘地址。

2.2 InnoDB

2.2.1 聚簇索引(聚集索引)

InnoDB采用的是聚簇索引，即索引结构通过B+数来实现，其最大的特点就是叶子节点存储完整的数据。如下图所示：

对于主键索引(主键列索引)，它的叶子节点存储的是整条记录的完成数据，但对于二级索引(非主键索引)，比如唯一索引或联合索引，它的叶子节点存储的是主键的值，并不是整条记录的完成信息，其结构如下图所示：

对于非主键索引而言，如果想要获取记录全部的数据，就需要先获取主键值，然后根据主键值到主键索引的叶子节点中来获取全部数据，这个过程叫做回表。

聚簇索引的二级索引和主键索引的查找方式是一样的。

至于为什么非主键索引(二级索引)叶子节点存储的是主键值，主要是一致性和节省存储空间。

同样的数据如果在索引文件中存两份就太浪费了。一致性是如果修改数据(非主键列)，两个索引的数据都需要修改，而只存储主键值，只需要修改主键索引中的数据就可以了。对于主键列的修改，在后面关于undo log的文章中会着重介绍。

2.2.2 数据存储

InnoDB与MyISAM的数据存储又有所不同，InnoDB只使用两个文件来记录相关数据，其中xxx.frm记录表结构信息，xxx.ibd用来记录所有索引和数据，因为InnoDB采用聚簇索引，所以它的主键索引就包含了全部的数据。

MyISAM和InnoDB都采用多路平衡树来作为索引结构，但需要注意的是，这两个存储引擎操作磁盘数据是以页为单位，即每个节点就是一个页，页的大小是可以通过系统变量修改的，但默认是16KB，SHOW GLOBAL STATUS like 'Innodb_page_size’命令可以查看，文件系统中磁盘块的大小通常为4KB，也就是说一个页的大小等于4个磁盘块的大小。

估算一个页大小为16KB的B+树可以存储多少条记录：

假设一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；

再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针)
那么一颗高度为2的B+树能存储的数据为：1170×16=18720条

一颗高度为3的B+树能存储的数据为：1170×1170×16=21902400（千万级条）

2.2.3 使用建议

对于InnoDB存储引擎而言，建议主动建主键，并采用整型自增主键，因为B+树要保证关键字的唯一性，那么就一定需要一个唯一的列，如果数据表没有指定主键，数据库则会自行选择一个数据唯一的列作为主键，如果没有这样的列，则会隐式添加一列作为主键。

1、主键使用整型数据容易作比较，使用自增主键的目的是为了防止节点分裂(页分裂)。

2、因为每一个页的大小是固定的，所以当节点(页)元素的个数到达最大值时，节点(页)就会进行分裂，分裂意味这些元素需要重新分配磁盘空间，同时页分裂时，上层节点(页)会添加一个元素，而这样也会导致上层元素也出现页分裂

聚簇索引和非聚簇索引的区别：(数据存储方式的不同)
1、Innodb采用聚簇索引的方式，MyISAM采用非聚簇索引的方式
2、聚簇索引的数据物理地址是连续的，非聚簇索引不需要地址连续
3、聚簇索引的索引结构中，主键索引的叶子节点中存放的是数据，而非聚簇索引的叶子节点中存放的是数据的内存地址。

注：Innodb中如果二级索引查询多了，Innodb会生成自适应的哈希索引，这是InnoDB存储引擎的三大特性之一。

三、联合索引

可以为表里面的多个字段建立一个联合索引，比如为name、age、position字段建立联合主键索引idx_name_age_position，普通联合索引就是二级索引或非主键索引，其索引结构如下：

从上面的结构可以看出，叶子节点数据的排序方式是按照索引创建的字段顺序进行排序的，name相同就按照age排序，age相同就按照position。

联合索引的使用，除了where子句指定所有的字段外，还支持最左匹配原则的查询。

比如查询条件为name = 'Bill'，在idx_name_age_position的联合索引中，name列是有顺序的，所以就可以走索引，而查询条件为age = '32'时，在联合索引中，age列并不是有序的，所以就不能走索引。name= 'LiLei' AND age = 23就可以使用该联合索引。

是否可以走索引，完全就是看查询条件的字段是否在索引中是有序的。

比如name= 'LiLei' AND age > 22可以走索引，因为name为常量时，age字段是有序的，所以可以走索引；j而对于name= 'LiLei' AND age > 22 AND position ='manager'就只有前两个字段可以使用索引，而position不能使用索引，因为在age > 22的记录中，position列并不是有序的，所以该列就不能使用索引。

最左匹配原则就是对于联合索引，只有查询条件对应于联合索引的字段是有序的，就可以使用索引。