mysql聚簇索引abc_我所理解的MySQL之二：索引

mysql教程栏目今天介绍相关索引知识。

MySQL 系列的第二篇，主要讨论 MySQL 中关于索引的一些问题，包括索引种类、数据模型、索引执行流程、最左前缀原则、索引失效情况以及索引下推等内容。

最早知道索引应该是在大二的数据库原理这门课中，当一个查询语句非常慢时，可以通过为某些字段添加索引来提高查询效率。

还有一个非常经典的例子：我们可以把数据库想象成字典，把索引想象成目录，当我们借助字典的目录来查询一个字的时候，就能体现出索引的作用了。

1. 索引种类

在 MySQL 中，从索引的逻辑或者说字段特性来区分，索引大致分为以下几个种类：普通索引、唯一索引、主键索引、联合索引和前缀索引。普通索引：最基础的索引，没有任何限制。

唯一索引：索引列的值必须唯一。

主键索引：特殊的唯一索引，作为主键它的值不能为空。

联合索引：联合索引就是索引列为多个字段的普通索引，需要考虑最左前缀原则。

前缀索引：对字符类型的前几个字符或二进制类型的前几个字节建立索引。

还有另外一种从物理存储上来区分的索引分类：聚簇索引和非聚簇索引。聚簇索引：索引顺序与数据存储顺序一致，其叶子节点存储的是数据行。

非聚簇索引：非聚簇索引的叶子节点存储的是聚簇索引的值，同时它是基于聚簇索引创建的。

简单来说，所谓的聚簇索引就是索引 key 与数据行在一起，而非聚簇索引的索引 key 对应的值是聚簇索引的值。

2. 索引的数据结构

常见的用于实现索引的数据结构有哈希表、有序数组和搜索树。

2.1 哈希索引

哈希表是一个以 key-value 形式来存储数据的容器，和 HashMap 一样，哈希索引也会将 key 通过特定的哈希函数计算得到索引值，然后在数组的相应位置存放 key 对应的 value，如果有两个 key 通过哈希函数计算得到的索引值相同(发生哈希冲突)，那么数组的这个位置就会变成一个链表，存放所有哈希值相同的 value。

所以在一般情况下，哈希表进行等值查询的时间复杂度可以达到 O(1)，但是在发生哈希冲突的情况下，还需要额外遍历链表中的所有值，才能够找到符合条件的数据。

另外，考虑到经过哈希函数计算得到的索引是不规律的——哈希表希望所有的 key 能够得到充分散列，这样才能让 key 均匀分布，不浪费空间——即哈希表的 key 是非顺序的，所以使用哈希表来进行区间查询时很慢的，排序也是同样的道理。

所以，哈希表仅适用于等值查询。

2.2 有序数组

有序数组顾名思义是一个按照 key 的顺序进行排列的数组，它进行等值查询的时间复杂度使用二分查询可以达到O(logN)，这与哈希表相比逊色不少。

但是通过有序数组进行范围查询的效率较高：首先通过二分查询找到最小值(或最大值)，然后反向遍历，直到另一个边界。

至于排序，有序数组本来就是有序的，天然已经排好序了，当然排序字段不是索引字段就另说了。

但是有序数组有一个缺点，由于数组元素是连续且有序的，如果此时插入新的数据行，为了维持有序数组的有序性，需要将比此元素 key 大的元素都往后移动一个单位，给他腾出一个地方插入。而这种维护索引的方式的代价是很大的。

所以，有序数组适合存储衣服初始化过后就不再更新的数据。

2.3 搜索树

了解过数据结构的人应该会知道，搜索树是一个查询时间复杂度为O(logN)，更新的时间复杂度也是O(logN)的数据结构。所以搜索树相较于哈希表和有序数组来说兼顾查询与更新两方面。也正是由于这个原因，在 MySQL 中最常用的数据模型就是搜索树。

而考虑到索引是存放在磁盘中的，如果搜索树是一棵二叉树，那么它的子节点只能有左右两个，在数据比价多的情况下，这棵二叉树的树高可能会非常高，当 MySQL 进行查询的时候，可能由于树高导致磁盘I/O次数过多，查询效率变慢。

2.4 全文索引

除此之外，还有一种全文索引，它通过建立倒排索引，解决了判断字段是否包含的问题。

倒排索引是用来存储在全文搜索下某个单词在一个文档或者一组文档中的存储位置的映射，通过倒排索引可以根据单词快速获取包含这个单词的文档列表。

当通过关键词进行检索的时候，全文索引就会派上用场。

3. InnoDB 中的 BTree 索引

3.1 B+树

这是一棵比较简单的B+树。

图片来源: Data Structure Visualizations

从上面这张示例图也可以看到，这棵B+树最下面的叶子节点存储了所有的元素，并且是按顺序存储的，而非叶子节点仅存储索引列的值。

3.2 图解 BTree 索引

在 InnoDB 中，基于 BTree 的索引模型的最为常用的，下面以一个实际的例子来图解 InnoDB 中 BTree 索引的结构。CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(36) DEFAULT NULL, `age` int(11) DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE, INDEX `nameIndex`(`nam