一文搞懂MySQL索引数据结构_数据库索引

前言

当我们在查询数据慢的情况下第一个想到的会是添加索引，索引在数据库的实际应用场景中十分常见，数据库的优化也离不开对索引的优化。而且索引相关的知识也是面试频率非常高的点之一，因此，本文将从基础理论出发，介绍MySQL按照逻辑角度的索引分类和实现。通过索引数据结构的实现原理阐述不同结构对建立索引带来的优劣势，希望通过这篇文章能给大家带来收获。

SQL执行慢的原因分析

• 磁盘空间不足、内存不足、I/O吞吐量小或者网络差等原因。
• 表没有创建索引或者索引已经失效。
• 分库分表也会带来SQL慢的原因
• 服务器调优及各个参数设置

# 定位原因的步骤

• 开启慢查询日志，并且设置多长时间的阈值就是慢查询，执行SQL语句，查看慢查询日志有哪些SQL是比较慢的。
• 定位到慢日志以后，使用Explain对SQL分析，看是否语句写得不合理，没有添加索引，或者关联查询太多等原因。
• 使用Show Profile（5.0开始支持），Show Profile能够帮我们了解在sql语句执行过程中时间耗费在了哪些地方。

什么是索引？

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引擎的主键索引、普通索引、组合索引、覆盖索引 --> 最左匹配原则讲解索引的相关知识点，希望这篇文章对小伙伴们有所收获。

最后就是如果小伙伴们想知道SQL调优的方法可以看一下我这篇文章：面试必备SQL调优方案