MySQL索引原理

MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

• 索引是物理数据页存储，在数据文件中（InnoDB引擎为ibd文件、MyISAM引擎为MYI文件），利用数据页(page)存储。
• 索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

所以要想理解MySQL的索引原理需要了解一些数据结构的理论知识

二叉树

二叉搜索树的特点是：每个节点的左侧子节点小于父节点，父节点又小于右侧子节点。如图所示：

假如我们需要执行 select * from T where Col2 = 89这条语句

如果没有索引的情况下，需要对表进行逐行查找，而数据库中的表数据均是放在磁盘中，每次查找都需要与磁盘进行一次IO交互，所以要找到Col=2的数据就需要进行6次磁盘IO，这种方式性能是比较差的。

如果MySQL索引使用了二叉树这种数据结构，执行上面的SQL语句需要从根节点起开始查询，发现要找的值89比34大，需要向右侧节点查询，这样经历两次磁盘IO就能找到，89这条数据了

那为什么最终没有选择二叉树作为索引结构呢，原因是有些场景不太适合，假如以Col1作为索引列，而这一列的数据是依次递增的，最终发现二叉树会单边增长，如果要查询Col1=6的数据，依然会进行6次磁盘IO，因此二叉树不太适合单边增长的序列字段

红黑树

红黑树又叫二叉平衡树，同样以单边依次增长的数据为例，我们发现如果叶子节点的一边高度比另一边高度大2的话，它会有个自旋的动作，自动平衡高度，这样就解决了二叉树不太适合单边增长的序列字段的问题

那为什么Mysql依然没有选择红黑树为索引的数据结构呢？

如果说表的数据量特别大的话，那么以红黑树为索引字段，它的高度会特别高，如果查询最底层的数据依然会进行多次磁盘IO，查询速度依然没有多大提升

B树

B树特点：

1. 索引值和data数据分布在整棵树结构中
2. 每个节点可以存放多个索引值及对应的data数据
3. 树节点中的多个索引值从左到右升序排列

我们发现虽然B树的每个节点可以存储多个索引值，大小默认16k，但是如果需要获取多个字段的值，data的大小就会变大，每个节点存储的索引元素就会变少，一定程度上树的高度也是不可控的

B+树

B+树的特点：

1. 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
2. 叶子节点包含了所有的索引值和data数据
3. 叶子节点用指针连接，提高区间的访问性能

相比于B树，B+树非叶子节点不存储data数据，只存储索引值，那么它横向就会存储更多的索引元素，B+树的高度会远远小于B树，即使3层高度的B+树，就能支持千万级别的数据

而MySQL在B+树的基础上又作了一层优化，把叶子节点的连接指针改为双向，方便Col1>= 18，Col2<=50这种查询

Hash结构

Mysql也支持Hash索引，把索引值经过Hash运算生成HashCode能快速定位到磁盘中的数据，
Hash索引可以方便的提供等值查询，但是对于范围查询就需要全表扫描了

MyISAM存储引擎索引实现

MyISAM索引文件和数据文件是分离的(非聚集)，索引存储在MYI文件中，表数据存储在MYD文件中，
而这种存储引擎对应的索引结构就如上图，它的data元素中存储的是数据所在行的磁盘文件指针，

搜索过程：假如where条件Col1=49，首先会在MYI文件中找到对应数据的磁盘文件指针，然后根据指针在MYD文件中快速定位数据，把整行数据加载到内存中

InnoDB存储引擎索引实现

从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引。

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引。

根据上面的索引结构说明，我们来讨论一个问题：基于主键索引和普通索引的查询有什么区别？

• 如果语句是select * from T where ID=15，即主键查询方式，则只需要搜索ID这棵B+树；
• 如果语句是select * from T where name=‘Bob’，即普通索引查询方式，则需要先搜索二级索引树，得到ID的值为15，再到主键索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

InnoDB存储引擎的表数据及索引均存储在ibd文件中，它的索引结构与MyISAM存储引擎区别在于data元素存储的非索引字段的所有数据

为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

因为InnoDB表数据存储在data元素中，且二级索引依赖于主键索引，推荐自增整形的自增主键，假如使用uuid作为主键，而查找过程中需要大量比较大小的操作，整形的效率肯定比字符串高，且整形的大小比字符串小，一个节点能存储更多的索引值，而自增是由于B+树的特性是从左到右依次递增的