索引原理深入学习

开始之前带着这些问题去看，会持续优化更新

索引是干嘛的？
索引为什么可以减少检索时间？
索引是怎么存储的？
为什么有些时候加了索引却没用？。。。

1、索引简介

索引就是排好序的快速查找数据结构，即索引 = 排序 + 查找

2、索引结构

2.1、数组

不什么不选用数组存储？

数组优点

• 数组是内存中一块连续的内存空间，定义一个数组对象，这个对象的指针指向了这块内存的起始地址，如果知道数组元素的下标，那么就可以计算出该下标所对应的元素的内存地址了，因此可以在 O(1)的时间复杂度内获取到元素，非常快速。如果使用二分查找对数组进行遍历查找，时间复杂度O(logn)也是相当可观的。

数组缺点

• 如果我们要往数组中间插入一个数据，我们需要将数组中要插入的目标位置后的所有元素先往后挪动一个位置，然后才能插入新的数据，也就是涉及到了数组的复制操作，要插入的数据越靠前，那么我们需要复制的数据就越多，这个不仅需要额外开辟内存，复制数据消耗的时间也很长。

虽然数组解决快速查找的问题，但是它的元素更新耗时，所以数组不适合做MySQl索引的数据结构

2.2、链表

为什么不选用链表？

链表优点

• 链表不用分配连续的内存空间，当链表节点增加删除时，只需要将节点指针指向地址发生改变即可，不会对链表其他造成变动。

链表缺点

• 查询元素时只能通过从头节点开始逐个遍历各个节点的方式查找，时间复杂度O(n)

虽然链表解决了增删繁琐的问题，但是链表查询效率低，所以不适合做为Mysql索引数据结构。

2.3、二叉排序树

二叉树优点

• 对于二叉搜索树而言，它的查找操作的时间复杂度就是树的高度，最理想的情况下，也就是满二叉树的情况下，查找的时间复杂度为 O(logn)。

二叉树缺点：

• 当我们在不停地动态地往树中插入数据、删除数据时，在极端情况下,插入大量有序数据，二叉搜索树可能退化成链表，它的查找时间复杂度就变成了 O(n)，性能不够稳定。

2.4、平衡二叉树（AVL）

平衡树是在二叉查找树的基础上，增加了一条限制，左右两个子树的高度差不能超过1，左右两边相对平衡，因此称之为平衡树。而平衡树在数据动态地删除、插入地过程中，为了维护平衡，避免树退化成链表，因此需要在删除或者插入数据后进行额外的旋转操作，会损耗一定的性能，但整体来讲，它的查找、删除、插入、更新的复杂度均为O(logn)。

AVL优点

• 这个方案很好的解决了二叉查找树退化成链表的问题，把插入，查找，删除的时间复杂度最好情况和最坏情况都维持在O(logN)。

AVL缺点

• 为了维护平衡，频繁旋转会使插入和删除牺牲掉O(logN)左右的时间。

解决了出现单链表的问题，但是旋转繁琐

2.5、红黑树

红黑树是一种近似平衡（不完全平衡），结点非黑即红的树，它的树高最高不会超过 2logn，因此查找的时间复杂度为 O(logn)，无论是增删改查，它的性能都十分稳定。工程上，很多地方都使用的是红黑树这种数据结构，例如 Java 中的 HashMap、TreeMap 等。

优点：

• 从根到叶子的最长的可能路径不多于最短的可能路径的两倍长。

• 红黑树放弃了追求完全平衡，追求大致平衡，在与平衡二叉树的时间复杂度相差不大的情况下，保证每次插入最多只需要三次旋转就能达到平衡，实现起来也更为简单。

缺点：

• 优缺点和AVL树是相辅相成的。

@磁盘读取分析

索引和表一样，有结构有数据，需要存储在磁盘中，页是 InnoDB存储引擎管理数据库的最小磁盘单位，初始化默认大小为16KB，而索引的一个节点的加载，即和磁盘进行一次IO磁盘块的读取，就是就会读取16KB大小的内容。

AVL树和红黑树在内存层面已经有相当广泛的使用，为什么索引的存储结构不使用这两种结构。原因在于，索引的节点是存储在磁盘中的，要考虑磁盘的IO效率问题，在尽量的减少磁盘的IO次数。
Innodb存储引擎管理数据库的最小磁盘单位是页，相当于在进行一次磁盘的IO读取就是一页，读取的一个磁盘块就是一页的大小，而索引节点的一次加载就会进行一次磁盘的IO操作，索引我们要尽可能的提高我们一次IO读取数据的效率，即提高一次IO读取到的数据量，从而较少IO次数。

2.6、多路平衡查找树B-Tree

B-Tree：

• 在树的节点上存储多个关键字和对应的内存指向地址，保证每次和磁盘的IO可以读取到更多的关键数据，从而来达到减少IO次数的目的，每个节点存储的关键字（n）数量和它的度（k）保持 n+1=k的关系。（度：节点的分支数量）

优点：

• 提高磁盘的IO效率，从而提高索引的检索效率。

缺点

• 当存储海量数据的时候，树的整个深度依然会特别大，当查询叶子节点上的元素时，依然会进行多次IO，效率也会大大降低，因为我们要继续减小树的整个深度。

2.7、B+Tree

B+Tree：

• 是B-Tree的plus版本，它将树节点存储的对应记录的内存地址去掉，将其保存在叶子节点上面，从而让根节点和枝节点有更多空间来保存关键字数量，从而每进行一次磁盘IO可以得到更多的数据，可以大大较少IO次数。
• 叶子节点专门有个指针指向下一个叶子节点，让叶子节点间形成一个有序的单链表结构。
• 进行扫库扫表时，可以直接通过遍历有序的叶子节点链表来进行。

2.8、Hash表

3、不同存储引擎下的索引

存储引擎：表类型，存储数据和管理数据的一个方式，针对于不同的数据和和管理有不同的存储引擎，这里说说常用的两个。

3.1、MyISAM

概述：

• 创建表时会生成三个文件：表结构文件、数据文件、索引文件。
• 主键索引和辅助索引（非主键索引）结构相同，采用B-Tree结构，叶子节点存放具体数据的内存地址。

3.2、Innodb

概述：

• 创建表时会生成两个文件：表结构文件、索引文件。
• 将表数据存储在主键索引的叶子节点中，而不是通过地址映射的方式存储数据。
• 主键索引又叫聚集索引，叶子节点是有序性是体现在主键上的，聚集索引的选择：1、当主键不为空时使用主键建立聚集索引。 2、当没有主键索引时，可以使用不为空的唯一约束字段建立索引作为聚集索引。3、前面两个都不存在时，使用表的隐藏字段“_RowId”建立索引作为聚集索引。
• 辅助做引和聚集索引不同，辅助索引节点存放的是索引和主键值
• Innodb必须有一个聚集索引。
• 回表：当查完辅助索引后，重新再去查询主键索引，这个过程就是回表，当我们索引在使用的时候，没有通过覆盖索引（select的字段没有被包含在建立的索引字段中）方式去使用时，就会进行回表。

4、索引规则

4.1、列最大散列度

离散度公式：count(distinct(column_name)) : count(*)
即：字段不重复的记录数量和总的记录数量的一个比例。

• 当离散度比较小时，就不太适合建立索引，因为字段数据重复值太多，进行索引的遍历时，会将多个节点进行遍历，可能会消耗大量时间，还不如直接遍历表。

4.2、最左匹配原则

当使用组合索引时，根据我们创建索引时的字段顺序，有一个从左往右的规则，即：当我们使用索引时，我们使用到的条件字段根据创建索引时的顺序从左往右排好序后，中间不能有缺失。举例：
index(name,age,school);可以有一下方式使用该索引：
where name…, age…,school；
where name…, age…；
where name…；

使用以下方式不能使用索引：
where age…;
where name…, school;
where school…;
where age…, school;

而在真正使用时，在排好序不缺失的情况下，可以是无序的去使用索引，即：
where name…, school, age…；
where age…, name…；
因为mysql有个Optimizer优化器，可以将字段顺序调整到最佳顺序来分配适合的索引，但是最好还是按建立索引时的顺序来使用比较好，这样可以减少优化器的工作。

5、索引失效

5.1、不遵守索引规则

不遵守4章节规则则会失效。

5.2、索引字段值范围不确定

• 使用 != (<>) 、not in 、not like，使得检索的范围不明确，只能全表去搜索；
• 索引字段使用函数或者计算、表达式，例如：where age + 1 = 3；
• 字符串不加引号，出现隐式转换；
• 当使用like “%XX” （最左前缀）时会失效，当使用like = “XX%”时不会失效，使用like时尽量要使用%最右前缀；

6、索引优化