Mysql索引

索引基本知识

索引是什么？

索引（Index）是帮助 Mysql 高效获取数据的数据结构。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指针指向）数据。这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引，可以理解为索引是排好序的快速查找数据结构。

一般来说，索引本身也很大，不可能全部存在内存中，因此索引往往以索引文件的方式存储在磁盘上。

我们平时所说的索引，一般都指B树（多路搜索树）组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引都是使用B+树索引。当然，除了B+树索引外，还有哈希索引等。

索引优势与劣势

优势：

• 提高数据检索的效率，降低数据库的IO成本。

• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：

• 降低更新表的速度，当更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

• 占用额外磁盘空间：实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

Mysql 索引底层数据结构

1. 哈希表

哈希表是做数据快速检索时的有效利器。

哈希算法：也称为散列算法，可以把任意值（key）通过哈希函数映射成固定长度的key地址。

哈希算法只需要计算一次便可得到地址，其时间复杂度为O(1)，检索速度非常快。但是Mysql并没有采用哈希作为其底层算法，这是因为考虑到数据检索有一个常用手段就是范围查找，比如查找id > 3的用户信息，如果使用哈希算法实现的索引，就需要把数据一次性读到内存，然后在内存中筛选目标范围数据。这样的效率十分低。

所以，使用哈希算法实现的索引虽然可以做到快速检索，但是无法做到数据高效范围查找，因此哈希索引不适合作为Mysql的底层数据结构。

2. 二叉查找树

二叉查找树是一种支持数据快速查找的数据结构，如图下所示:

二叉查找树的时间复杂度是 O(lgn)，比如针对上面这个二叉树结构，我们需要计算比较 3 次就可以检索到 id=7 的数据，从检索效率上看来是能做到高速检索的。此外二叉树的结构还可以解决哈希索引不能提供的范围查找功能。观察上面的图，二叉树的叶子结点都是按序排列的，从左到右依次升序排列，如果我们需要找 id>5 的数据，那我们取出结点为 6 的结点以及其右子树就可以了，范围查找也算是比较容易实现。

但是普通的二叉查找树有个致命缺点：极端情况下会退化为线性链表，二分查找也会退化为遍历查找，时间复杂退化为 O（N），检索性能急剧下降。比如以下这个情况，二叉树已经极度不平衡了，已经退化为链表了，检索速度大大降低。此时检索 id=7 的数据的所需要计算的次数已经变为 7 了。

在数据库中，数据的自增是一个很常见的形式，比如一个表的主键是 id，而主键一般默认都是自增的，如果采取二叉树这种数据结构作为索引，那上面介绍到的不平衡状态导致的线性查找的问题必然出现。因此，简单的二叉查找树存在不平衡导致的检索性能降低的问题，是不能直接用于实现 Mysql 底层索引的。

3. AVL树与红黑树

AVL与红黑树是一种能自平衡树高的二叉查找树，具体细节便不在此赘述。

AVL树与红黑树能自动平衡树高，因此他既具有二叉查找树的优点，也不用担心在极端情况下退化成链表。但AVL树或红黑树也不适合作为Mysql索引的数据结构，因为每遍历树上的一个结点都要进行一次 IO 访问（一次IO获取1kb或1b数据时间相近），如果使用AVL树或红黑树，一次 IO 访问只能获取到一个数据，为了让一次 IO 可以获取到更多的数据，我们需要在一个结点上放很多数据，这就是B树、B+树的原理。

4. B树

B树与红黑树最大的不同之处在于B树的结点可以有很多个孩子。

B树定义如下：

B树的每个结点可以有 n 个数据非降序存放，同时有 n+1 指针指向孩子结点。

B树的每个叶结点有相同的深度，即树高相同。

可以看出，每进行一次 IO 访问，可以获取比AVL树更多的信息，节省了大量 IO 访问。

5. B+树

B+树是B树的变种，B+树与B树区别如下：

1. B树的结点既存储数据，由存放指针，B+树的非叶子结点只存放指针，叶子结点只存放数据；

2. B+树的叶子结点用双向指针相连，更利于范围查找。

由于B+树只存放指针，在单个结点容量有限的情况下，单节点也能存储大量的索引，是的整个B+树高度降低，树高降低，IO查询次数也降低；B+树存放数据的叶子结点用双指针连接，形成了一条有序的双链表，在数据范围查找时效率更高；因此Mysql使用B+树作为索引的数据结构。

Mysql索引总结：

1. Mysql的索引数据结构是B+树

2. 不使用哈希表作为索引数据结构
   因为哈希表虽然有很高的查询效率，但是无法高效的执行范围查找

3. 不使用二叉查找树作为索引数据结构
   因为二叉查找树在极端情况下会退化成链表，使检索效率大幅下降

4. 不使用AVL树或红黑树作为索引数据结构
   因为AVL树每个结点能存放的数据太少，以致于要经历多次 IO 访问

5. 不使用B树作为索引数据结构
   因为B+树结点只存放指针，因此树高更低，相较于B树由更少的 IO 访问；B+树叶子结点用双链表相连，范围查找更高效。

Mysql 引擎的索引实现

聚簇索引

• 聚簇索引：主索引文件和数据文件为同一份文件，将数据存储与索引放到了一块，找到 索引也就找到了数据。

• 非聚簇索引：索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。

InnoDB 和 MyISAM 引擎的索引实现

创建 InnoDB 引擎的表后会生成两个文件：

• frm：创建表的SQL语句

• idb：表中的数据与索引

创建 MyISAM 引擎的表后会生成三个文件：

• frm：创建表的SQL语句

• MYD（myisam data）：表中的数据文件

• MYI（myisam index）：表中的索引文件

从生成的文件来看，InnoDB 引擎中的数据和索引在同一文件内，这种方式是聚簇索引；MyISAM 引擎把数据和索引分开了，这种方式是非聚集索引。

图解

现有如下 user 表：

InnoDB 引擎实现方式：

1. InnoDB 使用的是聚簇索引，将主键组织到一棵B+树种，而行数据存储在存储在叶子节点上。若使用where id = 1这样的条件查找，其检索顺序为 3 -> 2 -> 1 确定叶子结点后获得行数据。
2. 若对 Name 进行查找时，InnoDB会先在辅助索引中检索出 Name 对应的主键 id，然后在聚簇索引种查找。若使用where name = "Ellison"这样的条件查找，其检索顺序为 辅助索引：F -> C -> E 确定主键 id 为4后，聚簇索引： 3 -> 4 -> 4

MyISAM 引擎实现方式：

MyISAM 使用的是非聚簇索引，非聚簇索引与聚簇索引最大的不同之处在于非聚簇索引的叶子结点存放的是地址。当对 Name 进行查找时 MyISAM只需查找一次B+树。MyISAM 把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据。

聚簇索引的优劣势

优势：

1. 由于行数据和叶子结点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了。而非聚簇索引每次都需要去磁盘中查找（这个过程也被称为回表）。
2. 聚簇索引更适合用在排序、分组的时候。聚簇索引会按主键进行排序，这会使相邻主键的地址也相邻。而非聚簇索引相邻的主键地址可能不相邻，每次都需要回表。

劣势：

维护成本高，当增删改时不仅要修改索引，还要根据主键的变化移动数据（修改大），而非聚簇索引只需要修改索引（修改小）。

需要创建索引的情况

1. 主键自动建立唯一索引；

2. 频繁作为查询条件的字段应该创建索引

3. 查询中与其它表关联的字段，外键关系建立索引

4. 单键/复合索引的选择问题， 复合索引性价比更高

5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

6. 查询中统计或者分组字段

不要创建索引的情况

1. 表记录太少

2. 经常增删改的表或者字段

3. Where 条件里用不到的字段不创建索引

4. 过滤性不好的不适合建索引（数据重复太高）