MySQL索引一

什么是索引

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

索引的作用就相当于书的目录。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。

索引的优缺点

优点:

提高数据检索效率，降低数据库IO成本
降低数据排序成本，降低CPU消耗
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点:

索引需要占物理空间
索引需要定期维护，增删改数据时需要维护索引，降低数据写入效率

索引底层数据结构选型

哈希表

哈希表是一种以键-值(key-value)存储数据的结构，只要输入待查找值即 key，就可以找到其对应的值即 value。因此哈希表可以快速检索数据（接近 O（1））。

为何能够通过 key 快速取出 value 呢？原因在于 哈希算法（也叫散列算法）。通过哈希算法，我们可以快速找到 key 对应的 index，找到了 index 也就找到了对应的 value。

avatar

但是, 哈希表有一个很大的缺点: 就是哈希冲突。

哈希冲突: 两个不同的 key 通过哈希算法得到了相同的结果，这种情况我们称为哈希冲突。

通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。

为了减少 Hash 冲突的发生，一个好的哈希函数应该“均匀地”将数据分布在整个可能的哈希值集合中。

MySQL 的 InnoDB 存储引擎不直接支持常规的哈希索引，但是，InnoDB 存储引擎中存在一种特殊的“自适应哈希索引”（Adaptive Hash Index），自适应哈希索引并不是传统意义上的纯哈希索引，而是结合了 B+Tree 和哈希索引的特点，以便更好地适应实际应用中的数据访问模式和性能需求。自适应哈希索引的每个哈希桶实际上是一个小型的 B+Tree 结构。这个 B+Tree 结构可以存储多个键值对，而不仅仅是一个键。这有助于减少哈希冲突链的长度，提高了索引的效率。

既然哈希表这么快，为什么 MySQL 没有使用其作为索引的数据结构呢？主要是因为 Hash 索引不支持顺序和范围查询。假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。并且，每次 IO 只能取一个。

二叉查找树

二叉查找树（BST）是一种特殊的二叉树，它具有以下几个特点：

左子树所有节点的值均小于根节点的值。
右子树所有节点的值均大于根节点的值。
左子树和右子树都是二叉查找树。

当二叉查找树是平衡的时候，也就是树的每个节点的左右子树深度相差不超过 1 的时候，查询的时间复杂度为 O(log2(N))，具有比较高的效率。然而，当二叉查找树不平衡时，例如在最坏情况下（有序插入节点），树会退化成线性链表（也被称为斜树），导致查询效率急剧下降，时间复杂退化为 O（N）。

二叉查找树可以支持顺序和范围查询，但是，二叉查找树有一个很大的缺点: 就是平衡性不好。这就导致其不适合作为 MySQL 底层索引的数据结构。

平衡二叉树(AVL树)

AVL 树的特点是保证任何节点的左右子树高度之差不超过 1，因此也被称为高度平衡二叉树，它的查找、插入和删除在平均和最坏情况下的时间复杂度都是 O(logn)。

AVL 树采用了旋转操作来保持平衡。主要有四种旋转操作：LL 旋转、RR 旋转、LR 旋转和 RL 旋转。其中 LL 旋转和 RR 旋转分别用于处理左左和右右失衡，而 LR 旋转和 RL 旋转则用于处理左右和右左失衡。

由于 AVL 树需要频繁地进行旋转操作来保持平衡，因此会有较大的计算开销进而降低了查询性能。并且，在使用 AVL 树时，每个树节点仅存储一个数据，而每次进行磁盘 IO 时只能读取一个节点的数据，如果需要查询的数据分布在多个节点上，那么就需要进行多次磁盘 IO。磁盘 IO 是一项耗时的操作，在设计数据库索引时，我们需要优先考虑如何最大限度地减少磁盘 IO 操作的次数。

实际应用中，AVL 树使用的并不多。

红黑树

红黑树是一种自平衡二叉查找树，通过在插入和删除节点时进行颜色变换和旋转操作，使得树始终保持平衡状态，它具有以下特点：

每个节点非红即黑；
根节点总是黑色的；
每个叶子节点都是黑色的空节点（NIL 节点）；
如果节点是红色的，则它的子节点必须是黑色的（反之不一定）；
从根节点到叶节点或空子节点的每条路径，必须包含相同数目的黑色节点（即相同的黑色高度）。

avatar

和 AVL 树不同的是，红黑树并不追求严格的平衡，而是大致的平衡。正因如此，红黑树的查询效率稍有下降，因为红黑树的平衡性相对较弱，可能会导致树的高度较高，这可能会导致一些数据需要进行多次磁盘 IO 操作才能查询到，这也是 MySQL 没有选择红黑树的主要原因。也正因如此，红黑树的插入和删除操作效率大大提高了，因为红黑树在插入和删除节点时只需进行 O(1) 次数的旋转和变色操作，即可保持基本平衡状态，而不需要像 AVL 树一样进行 O(logn) 次数的旋转操作。

红黑树的应用还是比较广泛的，TreeMap、TreeSet 以及 JDK1.8 的 HashMap 底层都用到了红黑树。对于数据在内存中的这种情况来说，红黑树的表现是非常优异的。

B 树& B+树

B 树也称 B-树,全称为多路平衡查找树，B+ 树是 B 树的一种变体。目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B 树& B+树两者有何异同呢？

B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+树的范围查询，只需要对链表进行遍历即可。

综上，B+树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

索引类型总结

分类

主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
普通索引：仅加速查询。
唯一索引：加速查询 + 列值唯一（可以有 NULL）。
覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

MySQL 8.x 中实现的索引新特性：

隐藏索引：也称为不可见索引，不会被优化器使用，但是仍然需要维护，通常会软删除和灰度发布的场景中使用。主键不能设置为隐藏（包括显式设置或隐式设置）。
降序索引：之前的版本就支持通过 desc 来指定索引为降序，但实际上创建的仍然是常规的升序索引。直到 MySQL 8.x 版本才开始真正支持降序索引。另外，在 MySQL 8.x 版本中，不再对 GROUP BY 语句进行隐式排序。
函数索引：从 MySQL 8.0.13 版本开始支持在索引中使用函数或者表达式的值，也就是在索引中可以包含函数或者表达式。

主键索引

not null +unique

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

二级索引

二级索引的叶子节点存储的是主键值。即通过二级索引可以定位主键.唯一索引，普通索引，前缀索引等索引属于二级索引

普通索引:仅加速查询。
唯一索引:列值唯一,但对NULL不生效
前缀索引:为了节省空间，对字符串类型的字段，可以只索引字段的前几个字符。
全文索引:基于分词,常用于大文本搜索关键词,match和against关键字

聚簇索引

索引结构和数据一起放的索引

非聚簇索引

索引结构和数据分开存放的索引,如二级索引

为什么主键索引最好设置为自增?

设置自增后,插入数据时,插入速度更快,因为每次插入数据时,数据库不用移动其他数据,只需要移动指针即可.

如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率

非自增主键:

avatar
自增主键: