MySQL之索引原理

一、索引以及存储结构

（一）索引以及其种类

1、何为索引

索引，简单来说就是数据库管理系统中一个排序的数据结构，以便协助快速查询、更新数据库中数据。
注意：索引是数据结构，这个是核心，后面会讲具体是什么数据结构。

2、索引的种类

在InnoDB中索引分为三种：普通索引、唯一索引、全文索引。其中主键索引是特殊的唯一索引。
普通索引（normal index）：也叫非唯一索引，是最普通的索引，没有任何限制；
唯一索引（unique index）：唯一索引要求键值不能有重复。上面说了主键是特殊的唯一索引，主键不仅唯一还不能为空；
全文索引（fulltext index）：针对较大的数据，比如我们存放的是消息内容，有几KB的数据，这时用like进行模糊查询效率就比较低了，可以使用全文索引。只有文本类型的字段才可以创建全文索引，例如char，varchar，text。InnoDB和MyISAM都支持全文索引。

这里大致讲一下全文索引如何使用的：
select * from table where match(建立全文索引的列名d) against(‘需要查询的内容’ IN NATURAL LANGUAGE MODE);

（二）MySQL的存储结构

在介绍索引可能使用的存储结构以及真实使用的存储结构之前，我们先大致介绍一下MySQL的存储结构。
MySQL的存储结构分为5级：表空间、段、簇（区）、页、行，大致结构图如下：

1、表空间

表空间可以看作是InnoDB存储引擎逻辑结构的最高层，所有的数据都存储在表空间，分为：系统表空间、独占表空间、通用表空间、临时表空间、Undo表空间。其物理存储结构分为三个文件：ibd文件、frm文件、日志文件组。
表空间是由各个段组成。

2、段

创建一个索引会创建两个段，一个是索引段：leaf node segment；一个是数据段：non-leaf node segment。索引段管理非叶子节点的数据，数据段管理叶子节点的数据。也就是说一个表的段说是索引数乘以2。
段由很多簇组成。每个段最少一个簇，段的管理空间是无限大的，可以一直扩展下去，但是扩展最小的单位就是簇。

3、簇（区）

每个簇的大小为1MB=64个连续的页。
簇是由连续的页组成的。

4、页

每个页的大小为16KB，一个簇中有64个连续的页。这些页在物理上和逻辑上都是连续的。
InnoDB也有页的概念，也可以称为块，页是InnoDB存储引擎磁盘管理的最小单位，可以通过innodb_page_size进行设置。
注意：文件系统中也有页的概念，其大小通常为4KB，操作系统和内存打交道最小的单位就是页。
一个InnoDB存储引擎的页存储的数据等于4个文件系统的页。

往表中插入数据时，如果一个页已经被写满了数据，那么会产生一个新的页。如果一个簇的页都被用完了，那么会从当前簇所属的段中重新分配一个簇。
如果数据是不连续的，往已经写满数据的页中插入数据会导致页分裂。

5、行

这里就不具体介绍了，只要记得InnoDB是按照行进行存放数据的，行格式有两种不同的格式就行了。

（三）索引存储可能使用的数据结构

1、二叉查找树（Binary Search Tree）

特点：左子树的节点<父节点；右子树的节点>父节点
跟树相关的查找效率都是跟树的深度有关系，树的深度约大，查找效率越低。
二叉查找树在极端的情况下会演变成复杂度为O(n)的树，也就是一个链表如下图：

ps：在这里给大家介绍一个能动态展示数据的网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html，有兴趣的可以进去看看，操作很简单

由于二叉查找树当复杂度为O(n)的情况下跟顺序查找的效率是没有区别的，那么我们就使用它的进化版本平衡二叉查找树。

2、平衡二叉树（AVL Tree）

平衡二叉树的定义：左右子树深度差绝对值不能超过1。
例如左子树的深度为4，则右子树最多为3或者5。那么当我们插入上述同样的数据，就会是下图的情形：

主要是利用了左旋和右旋来保持平衡的，这里就不讲怎么旋了，感兴趣的可以自己搜索。

平衡的问题解决了，怎么来查找数据呢？首先我们要考虑的是一个节点里面可以存储什么数据呢？
它应该存储三块数据：
索引对应的键值：当我们在id上创建了索引，当我们使用id=n来查询数据时就会找到索引里面id对应的这个值n；
数据对应的磁盘地址：索引的作用就是去查找数据存放的地址来获取数据；
左右子树的引用：当查找的数据比当前节点的数据大或者小时要继续走到下一个节点，所以就得持有下个节点的引用。

索引数据是存储在磁盘上的。当我们用树存储数据时，访问一个节点就会跟磁盘进行一次IO，而上面我们说过InnoDB操作磁盘的最小单位是页，一页大小是16k，也就是树的一个节点能存储16k的数据。如果一个树节点存储键值+磁盘地址+子节点的引用，例如整型数据，可能只用了几十k的空间，当每个节点存储的数据比较少，意味着树的层级就会变多，查询一个数据就会访问更多的层级，就意味着进行更多的IO操作，而且也会浪费磁盘空间。

这个时候为了减少IO次数以及空间浪费，可以在一个节点存储多个储键值+磁盘地址+子节点的引用，增加多个叉路，之前是二叉树，现在是多个叉树，这就是更优化的存储方式，多路平衡查找树。

3、多路平衡查找树（B Tree）

B Tree跟AVL树一样，在节点上存储的都是键值、数据地址、节点的引用。不同之处是：分叉树永远比关键字多1。

这样同样深度的树能存储更多的数据，查找的的节点更少，与磁盘进行IO的次数相应的减少，增加了效率。
至于增加和删除数据的时候树怎么保持平衡，这个会由树本身的特性来进行调整。一旦索引发生了更新，其对应的数据结构为了保持平衡会进行调整，这就是为什么不要在频繁更新的列上创建索引。

4、加强版多路平衡查找树（B+ Tree）

B树已经能够提高效率了，为什么InnoDB中还使用了更加改良版的B+树呢？我们先看看B+树的结构：

MySQL中B+Tree的特点：
1、它的关键字和分叉路数的数量是一致的；
2、非叶子节点都不存储数据，只有叶子节点才存储数据。
3、B+ Tree的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表结构。
4、它是根据左闭右开的区间来检索数据的。

假如索引字段是bigint类型，长度为8字节，指针大小在InnoDB源码中设置为6字节，一个非叶子节点可存储16384/14=1170个（键值+引用）。
当树的深度为2时，有11701170个叶子节点，例如每行数据存储为1k，则可以存储11701170*16=21902400条的数据，也就是2000w左右的数据，也就是说查找一张2000w左右的表最多需要访问3次磁盘。所以在InnoDB中B+树深度一般为1-3层就能满足千万级的数据存储。

再次总结一下B+ Tree的特点：
1）B Tree能解决的问题，B+Tree都能解决：每个节点存储更多的关键字；叉路更多
2）扫库扫表能力强，只需要遍历叶子节点就可以，不需要遍历整棵树就能拿到全部数据；
3）B+Tree相对于B Tree的磁盘读写能力更强，因为非叶子节点不存储数据，所以非叶子节点可以存储更多的关键字；
4）排序能力更强，叶子结点上有下一个数据区的指针，数据形成了链表；
5）效率更加稳定。B+Tree永远是在叶子节点拿到数据，所以IO次数是稳定的。

5、为什么不用红黑树

1、只有两路；2、不够平衡
红黑树一般只放在内存中使用，例如Java的TreeMap。

（四）索引存储数据结构

通过上面分析，我们可以知道存储使用的是B+ Tree。
当我们使用MySQL创建表的时候，如果存储引擎使用的是InnoDB则会生成两个文件.frm和.ibd文件；如果存储引擎使用的是MyISAM，则生成.frm、.MYD、.MYI文件。
其中有一个是相同的，就是.frm文件，.frm文件是MySQL里面表结构定义的文件，不管你建表的时候选择任何一个存储引擎都会生成。

1、MyISAM

MyISAM会生成三个文件，.frm、.MYD、.MYI文件。其中MYD文件是数据文件，存放数据记录；MYI文件是索引文件，存放索引。
因此可以看出，MyISAM存储引擎索引和数据是分开的，我们要通过索引文件找到对应数据文件在磁盘上的地址，然后找到对应数据。也就是说MyISAM存储引擎的叶子节点存储的只是数据文件的地址，并不是直接存储数据文件。

2、InnoDB

InnoDB只有.frm和.ibd文件，其所有的索引文件和数据文件都存储在.ibd文件中。也就是说InnoDB存储引擎叶子节点既存储索引又存储我们需要的数据。

3、聚簇索引和辅助索引

聚簇索引又称为聚集索引，其不是一种索引而是一种数据的存储方式，聚簇索引的索引键值逻辑顺序和表数据行的物理存储顺序一致。聚集索引B+Tree的叶子节点存储一整行的数据，InnoDB用的就是聚簇索引，而MyISAM用的就是非聚簇索引也就是辅助索引。
InnoDB的主键索引就是一种聚簇索引，其他的非聚簇索引都是复制索引，例如复合索引，唯一索引。

1、如何通过辅助索引查找数据呢？
InnoDB中主键索引和辅助索引是有主次之分的。辅助索引存储的是索引和主键值，会通过促织索引找到对应的主键值，然后通过主键找到对应的数据。

2、为什么辅助索引存储的是主键值而不是主键的磁盘地址呢？
因为B+Tree为了保持平衡有分叉和合并的操作，页会发生分裂合并，存储的数据地址发生改变，而主键的索引键值不会发生改变。
3、如果一张表没有主键怎么办？
1）如果我们定了主键，那么InnoDB会选择主键作为聚簇索引；
2）如果没有显示的定义主键，InnoDB会选择第一个不包含NULL值的唯一索引作为主键
3）如果也没有这样的唯一索引，那么InnoDB会选择内置6字节常的ROWID作为隐藏的聚簇索引，它会随着行记录的写入而逐渐递增。

二、索引的使用

1、索引的使用规则

1）离散度：重复的越少离散度越大，扫描的数据越少，扫描的越多越接近全表。因此在离散度比较高的字段上建立索引；
2）复合索引：多个字段作为查询条件联合查询，可以使用复合索引。复合从最左边开始匹配，不能从中间开始。即使条件相反也能用到复合索引，优化器会使其使到。
例如创建了name_phone索引：
select * from table where name = #{name} and phone = #{phone}，会使用到复合索引name_phone
select * from table where phone = #{phone} and name = #{name}，会使用到复合索引name_phone
select * from table where name = #{name} ，不会使用到复合索引name_phone
select * from table where phone = #{phone}，不会使用到复合索引name_phone
3）覆盖索引：查询的列跟条件对应的列一样
例如创建了name_phone索引：
select name,phone from table where name = #{name} and phone = #{phone}会使用到覆盖索引
原因：覆盖索引也就是辅助索引，其查询的数据的是其所在的B+Tree上就有的数据，不用再通过其对应的主键id查找主键的B+Tree。

注：索引的过滤在存储引擎中，数据的过滤在MySQL服务器上。

2、索引的创建

1、在用于where判断、order by排序、join的on字段上创建索引
2、索引的个数不要太多
3、离散度低的字段不要创建索引，例如性别
4、频繁更新的值不要作为主键或者索引
5、组合索引把散列度高的值放在前面
6、创建复合索引，而不是修改单列索引

3、索引的失效的情况

1、索引列上使用函数、表达式、计算都会导致索引失效；
2、字符串不加引号出现隐式转换会导致索引失效；
3、like前面加%会导致索引失效；
4、负向查询会导致索引失效，NOT LIKE会使索引失效，但是!=、<>、NOT IN某些情况下会走索引，某些情况下又不走索引，是否使用索引都是优化器说了算。优化器基于开销，所以会根据性能会决定有的时候不走索引。

4、MySQL中索引的优化

MySQL中索引的优化可以通过执行计划来查看，有兴趣的可以看我另一篇博客：MySql执行计划explain的解读