MySQL索引详解

1. 什么是索引

索引可以理解为把数据按照某种数据结构组织起来，从而提高查询效率，例如把数据构建为二叉搜索树，可以把查询时间复杂度降为O(log2n)。

如果不构建索引进行查询会有两个问题：

1. 时间复杂度高，需要遍历对比每一个数据
2. 磁盘操作多，数据量过大，不能一次把全部数据加载到内存，就需要分批次加载数据，导致大量的磁盘操作降低性能

MySQL中的索引就可以很好的解决这两个问题 

2. B树/B+ 树

MySQL中构建索引采用的数据结构是B+树，讲解B+树前，我们先了解一下B树：

B树是一个多叉搜索树，每个节点中可以有多个元素，叶子节点都在同一层，具体的形式如下：

例如我们要查找元素5，从根节点开始比较，发现5 < 9，往左走，发现 2 < 5 < 6，于是往 和 6之间走，在[3, 5]节点中即可找到5。由于每个节点都可以包含多个元素，就意味着每次都可以从硬盘中读取多个元素，也就减少了硬盘IO次数。

B树的缺点，进行范围查询时不方便，例如查询 7 ~ 11之间的数据。

B+树是B树的升级，它的结构设计为范围查询提供了便利：

B+树同样是多叉搜索树，与B树不同点在于，B+树的数据仅保存在叶子节点，非叶子节点仅保存元素用于判断大小的特征值，叶子节点中包含了全部的元素，并且之间用双向指针连接：

注意：只有叶子节点保存完整的数据，例如这是一个学生表的id的索引，那么只有叶子节点会保存学生的完整信息，其他节点只保存id

此时我们想要查询 7 < id <11的数据就可以先查最小的，即7找到元素之后通过双向指针往后查询即可。

由于B+树的非叶子节点中只存特征值，并没有数据，那么所占空间也比较小，部分场景就可以先把所有非叶子节点加载到内存中，查询到具体的数据位置时，再从硬盘中加载叶子节中的数据，这样只需要两次硬盘操作（加载非叶子节点和加载数据）就可以完成查询。

3. MySQL索引

一个表的索引通常是有多个的，按照name构造的索引只有按照name查询时才能用上，如果实际上还需要根据别的字段查询那么就需要在查询的字段上再建索引。

1. 主索引

主索引即主键索引，主索引上的叶子节点存放的是完整的数据 

2. 辅助索引（二级索引）  

 辅助索引的叶子节点上存放的是主键，而不是完整的数据（为了避免浪费空间），通过辅助索引查询完整的数据，需要先查询到主键，再利用主键去主键索引中查询完整的数据，这个操作叫回表

3. 组合索引

当字段的值区分度比较高时，构建的索引查询性能才会好，当单个列区分度不足时可以把多个列组合在一起做成组合索引。

组合索引是有顺序的，例如：

create indext idx_name on student(name, age);

这个索引就会先按照name排在按照age排，例如两个人都叫小明，但是年龄不同，就会把年龄小的排前面。

4. 前/后缀索引

构建索引时，如果索引列很长，可能会导致频繁分页分裂页合并操作降低性能。

数据页：数据页是数据库传输和存储的基本单位，每次传输只能操作一个数据页大小的数据，同时存储也是以数据页为单位，当某个数据页中的数据超过了阈值就会分裂为两个数据页，过少又会合并，所以索引字段过大时，频繁的插入删除操作也会导致频繁的页分裂，页合并操作，降低性能。这里的页分裂页合并通常是和索引节点的分裂合并同步的。

所以对于长的字符串，我们可以通过前缀或者后缀作为索引字段，例如只取前20个字符作为索引字段

• 索引会降低inserti语句的性能，因为执行inserti操作时需要额外维护索引。
• update和deletei语句也需要维护索引，但它们通常都会带where条件，相对于维护索引的额外开销，where条件从索引获得的收益通常更大。
• 如果索引字段很长，会导致更频繁的页分裂页合并，影响性能另外，大量的随机插入会导致MySQL需要换入换出不同的页，也会影响性能，所以用UUID这种又长又无序的的数据做索引是非常糟糕的选择。