mysql——索引数据结构学习(转)

转自：https://blog.csdn.net/wuhuayangs/article/details/121628119

 

什么是索引？
MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。我们可以简单理解为：快速查找排好序的一种数据结构。MySQL索引主要有两种结构：B+Tree索引和Hash索引。我们平常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，如果没有特别指明，默认都是使用B+树结构组织的索引。

索引优点
可以提高数据检索的效率，降低数据库的IO成本
在使用分组排序子句进行数据检索时，通过索引列对数据进行分组排序，可以显著减少查询中分组和排序的时间，降低CPU的消耗
索引缺点
创建索引和维护索引要耗费时间，并且随着数据量的增加而增加
索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
索引分类
主键索引
索引列中的值必须是唯一的，不允许重复，不允许空值

唯一索引
索引列的值必须是唯一的，允许空值。唯一索引不允许表中任何两行的值具有相同的索引值

普通索引
MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值

全文索引
只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MyISAM和InnoDB中都支持使用全文索引

组合索引
组合索引的使用，需要遵循最左前缀匹配原则。如果条件允许的情况下使用组合索引替代多个单列索引使用，
所以创建多列索引时，要根据业务场景，将where子句中使用最频繁的一列放在最左边

空间索引
MySQL在5.7之后的版本支持了空间索引，对空间数据类型的字段建立的索引，底层可通过R树实现。只不过使用较少

前缀索引
在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定

索引的数据结构
上面有讲过MySQL索引主要有两种结构：B+Tree索引和Hash索引，接下来我们会讲解到Hash表、二叉查找树、平衡二叉树、B树以及B+树。

Hash索引
顾名思义，哈希索引是通过哈希表实现的，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（HashCode)，并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

由于哈希结构的特殊性，其拥有非常高的检索效率，但是效率高的同时也会存在一些限制，如下：

只能用于等值查询，时间复杂度为O(1)，但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式
当出现Hash冲突的时候，存储引擎必须遍历整个链表中的所有行指针，逐行比较，直到找到所有的符合条件的行，若Hash冲突很多的话，其索引维护的代价很高，并且性能并不一定会比BTree索引高
Hash索引存储的是Hash码而不是键值，所以无法用于排序
如果是需要范围查询数据的，显然这种并不适合。

二叉查找树
如果是按照7，4，9，2，5，8，11的顺序添加节点。那么树的结构图如下：

二叉树的特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。

二叉树的实现主要为了每次查找都可以折半而减少IO次数，但还有种情况下很容易出现树不分叉了，这时候树退化成链表了，如下图：

平衡二叉树
平衡二叉树除了具备二叉树的特点之外，最主要的特征是它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。这个方案很好的解决了二叉查找树退化成链表的问题，在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。但是频繁旋转会使插入和删除牺牲掉O(logN)左右的时间，不过相对二叉查找树来说，时间上稳定了很多。

使用平衡二叉查找树查询时间复杂度是 O(log2n)。查询id=5，只需要两次IO。

平衡二叉树存在的一些问题：

平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高
树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘IO操作。树的高度就等于每次查询数据时磁盘IO操作的次数。磁盘每次寻道时间为10ms，在表数据量大时，查询性能就会很差
B树(B-Tree)
MySQL的数据是存储在磁盘文件中的，查询数据时需要先把磁盘中的数据加载到内存中，磁盘IO操作非常耗时，所以我们优化的重点就是尽量减少磁盘IO操作，所以，我们应当尽量减少从磁盘中读取数据的次数。另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。

如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块。我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。那说明什么？说明每个磁盘块仅仅存储一个键值和数据！那如果我们要存储海量的数据呢？

可以想象到二叉树的节点将会非常多，高度也会极其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！

为了解决平衡二叉树的这个弊端，B树应运而生， B树是一种多叉平衡查找树，主要的特点是：

叶子节点都在同一层，叶子节点没有指针连接
B树的节点中存储着多个元素，每个内节点有多个分叉
节点中的元素包含键值和数据，节点中的键值从大到小排列
下面用一张图对比平衡二叉树和B树：

从中我们可以看出，将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖，在从磁盘中查找数据（先读取到内存、后查找）的过程中，可以减少磁盘IO的次数，从而提升查找速度。

下面以一张图讲解在B树中查找数据的情况：

下面模拟下查找key为27的data的过程：

 

相比较二叉平衡查找树，虽然比较的次数没有明显的减少，但是磁盘IO次数大大减少，B树的高度一般2至3层就能满足大部分的应用场景，所以使用B树创建索引可以很好的提升查询的效率。但是在实际的数据库应用中仍存在一些问题无法解决。

B树中每个节点中包含key值以及data值，而每一个节点的存储空间是有限的(MySQL默认16K)，如果data中存放的数据较大时，将会导致每个节点能存储的key的数量很小，所以当数据量很多，且每行数据量很大的时候，同样会导致树的高度变得很高，增大查询时的磁盘IO次数，进而影响查询效率。
不支持范围查询的快速查找，而在实际的应用中，数据库范围查询的频率非常高，以下的一种情况是我查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。
问题总是推动改进的前提，为了解决以上问题，对B-Tree 进行改造优化，进而演变出了B+Tree。

B+树（B+Tree）
B+树作为B树的升级版，那么到底升级了什么呢，如下图：

 

对比B树和B+树，我们发现二者主要存在以下几点不同的地方：

数据都存放在叶子节点中
非叶子节点只存储键值信息，不再存储数据
所有叶子节点之间都有一个指针，指向下一个叶子节点,而且叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表
既然B+树解决了B树的范围查询问题，那么接下来我们来看一下等值查询和范围查询。

等值查询
下面模拟下查找key为9的data的过程：

 

范围查询
下面模拟下查找key的范围为9到26这个范围的data的过程：

从上面的结果，我们可以知道B+树作为索引结构带来的好处：

磁盘IO次数更少
数据遍历更为方便
查询性能更稳定
由于B+树优秀的结构特性，在MySQL中，存储引擎MyISAM和InnoDB的索引就采用了B+树的数据结构。

大家如果还想了解更多可以看一下这篇文章：图解红黑树的前世今生

以上介绍完索引结构后，接下来，分析MySQL的两种存储引擎的索引实现：MyISAM索引和InnoDB索引

MyISAM和InnoDB索引
MyISAM索引
MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树创建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。

创建user表Create table user （……），会生成三个文件：

.frm文件是所有存储引擎都会创建的，用来记录表结构
.myd记录存储的数据
.myi记录索引数据
主键索引

根据主键等值查询数据：

根据主键范围查询数据：

注意：这里是为了方便索引的分析过程，MyISAM在查询时会将索引缓存在缓存中，并不是每次都走磁盘。

InnoDB索引
InnoDB是默认的MySQL存储引擎，虽然InnoDB也使用B+树作为索引结构，但具体实现方式却与MyISAM截然不同。InnoDB的数据文件本身就是索引文件，而从上文中我们知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址,而在InnoDB中，表数据文件本身就是按B+树组织的一个索引结构，这棵树的叶子节点data保存了完整的数据记录。这个索引的key是表的主键，因此InnoDB表数据文件本身就是主索引，如下图所示：

 

创建user表Create table user （……），InnoDB生成两个文件：

.frm文件是所有存储引擎都会创建的，用来记录表结构
.ibd文件存储表的索引和数据信息
主键索引
主键索引，也称为”聚簇索引“，是InnoDB引擎中最重要的索引结构，主键索引使用B+树创建，树的子节点存储索引节点信息及关联关系，树的叶子节点存储的数据是整行记录。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6字节长整型的隐式字段 rowId字段构建主键索引。

这里需要说明一下，除主键索引之外的所有索引都称为辅助索引，主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。

根据主键等值查询数据：

辅助索引
除聚簇索引之外的所有索引都称为辅助索引，InnoDB中辅助索引data域存储相应记录主键的值而不是地址。也就是说InnoDB中的所有辅助索引都引用主键作为data域。

以年龄(age)为例：

 

使用普通索引需要检索两遍索引。第一次检索辅助索引获得主键，再使用主键到主键索引中检索获得数据。根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询，如下图:

辅助索引：

主键索引：

根据上图得到的主键38进行回表查询，如下图：

基于非主键索引的查询需要多扫描一遍索引树。因此，我们应该尽量使用主键查询。

组合索引

如果为每一种查询都设计一个索引，索引是不是太多了？假如查询where条件只有一个，我们完全可以用单列索引，这样的查询速度较快，但如果我们的业务场景是需要经常查询多个组合列，这时候创建多个单列的索引(虽然有多个单列索引，但是MySQL只能用到其中的那个它认为似乎最有效率的单列索引)，那该怎么做呢？比如我们可以建个（name，age）的组合索引来解决。如下图所示：

执行流程：

 

获取到主键38最后在回表查询就可以。

最左匹配原则
顾名思义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

例如：创建idx_name_age (name,age) 组合索引，如果查询age = 48，是匹配不到(name,age)索引的，但是如果查询条件是name = 48 and age = 张三6或者name=48(又或者是age = 48 and age = 54)就可以，因为优化器会自动调整name,age的顺序。

再比如a = 1 and b = 2 and c > 3 and d = 4如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。

比如上面创建idx_name_age (name,age) 组合索引,相当于创建了 (name)、（name,age）两个索引。

覆盖索引
覆盖索引是一种很常用的优化手段。因为在上面普通索引的例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表查询，那如何查询避免回表查询呢，如下SQL：

select age from student where age = 48;
1
在上面普通索引例子中，如果我只需要age字段，那是不是意味着我们查询到普通索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。

下面看一下执行计划：

未使用到覆盖索引：

覆盖索引的情况：

判断标准就是使用explain，可以通过Extra列来判断，显示为Using index（表示已经使用了覆盖索引），MySQL查询优化器在执行查询前会决定是否有索引覆盖查询。

总结
本文从—> 什么是索引 --> 索引优缺点 --> 索引的7种类型 – > 为什么使用B+树 --> InnoDB和MyISAM引擎的主键索引、普通索引、组合索引、覆盖索引 --> 最左匹配原则讲解索引的相关知识点，希望这篇文章对小伙伴们有所收获。