数据库必知必会：索引

写在前面

这篇文章是在网络上看到其他作者的优秀博文，自己消化理解之后所做的记录。文章基于 MySQL 中的 InnoDB 存储引擎。

原博文地址：点我

索引

在没有深入了解之前，我们对索引一般会有如下认知：

• 索引可以加快数据库的检索速度

• 索引会降低插入、删除和修改等「维护表数据」操作的速度

• 索引需要额外的存储空间

• 索引分为「聚集索引」和「非聚集索引」

• Mysql 的索引有「Hash索引」和「B+索引」两种数据结构

但是面试时，往往需要我们对索引有更多的了解：

• 为什么使用索引可以加快检索速度？

• 为什么索引会降低插入、删除、修改等维护表结构的速度

• 索引的「最左匹配原则」是什么

• 「Hash索引」和「B+索引」有什么区别？哪一个被使用地比较多？InnoDB 存储都支持吗？

• 「聚集索引」和「非聚集索引」有什么区别？

MySQL 如何存储

在了解索引之前，我们需要先看看数据库中数据以何种形式存储，MySQL 中，数据以「页」为单位进行存储：

如何存储数据

在磁盘中，『数据页』之间会以双向链表的数据结构进行连接和存储，而对于每个数据页，页之中的『记录』以单向链表的形式进行查询和存储。

如何检索数据

对于每个数据页，它在内部会为页内部的记录生成一个「页目录」，当我们通过主键进行检索时，数据库能够使用二分查找迅速定位正确目录到对应的记录槽，再遍历该记录槽中的记录从而快速找到目标记录。

如果我们以其他列(非主键)进行检索时，只能顺序遍历单链表中的所有记录。

所以说，如果我们执行SELECT * FROM user WHERE name = 'zhangsan'这样一个非主键检索的 sql 语句时，数据库会这样去检索：

1. 遍历双向链表，找到记录所在的页。

2. 遍历页中的单链表，从页中寻找对应的记录(如果使用「主键」，数据库就能够进行二分检索了)。

很明显，在数据量庞大的情况下，二分检索会远快于顺序检索。

索引：检索为什么快？

关于上面的情况，大家都知道我们对于非主键列上的检索可以通过「设置索引」来加快检索速度，实际上，索引所做的是将无序的数据变成有序(相对)。

虽然索引有「B+索引」和「Hash索引」两种，但目前主流使用的是B+索引，这里我们先对其进行描述，后续如果没有特别指出，默认涉及的也是 B+索引，关于「Hash索引」我们放在之后进行讨论。

下面是增加「B+索引」后的一种情况：

下面是查找一个 id 为8的记录的简要步骤：

没有使用索引的情况下，我们需要「遍历双向链表」来定位到对应的页。从上图中我们可以明显看到，索引实际上相当于给数据库中的页做了一个「目录」，现在能够通过索引对应的列很快地定位到对应的页上。

这里底层结构使用了 B+树，所以被称为『B+索引』，B+树是一种平衡树，这种树结构能够保证我们进行快速地查找，它进行查询的时间复杂度是O(logN)。

索引：为什么会降低增删改的速度？

上文中提到平衡树这一结构能保证我们快速地查询，这里我们需要先了解一下「平衡树」的概念。

平衡树

平衡树，它是一颗空树、或它的左右子树高度差的绝对值不超过1，并且左右两颗子树都是一颗「平衡二叉树」。

了解过树的都知道，在极端情况下，树结构会退化成「链表」，这样树结构的优点也就不复存在了。

B+树是一种平衡树，是不会退化成链表的，树的高度也会比较低，从上面查询相关的图中，我们也可以发现，建立B+索引的本质就是「建立一个B+树」。

那么，既然 B+树是一种特化结构的树，我们在对其进行增删改时，势必会破坏它原有的结构，而我们需要维护平衡树结构，就需要付出额外的代价，也就是让我们增删改的耗时变久了。

Hash索引

除了「B+索引」外，我们常见的索引还有一种「Hash索引」。

Hash索引就是采用哈希算法，用哈希值代替每个记录的键值(类似于实现了一个HashMap)，这样我们检索时可以直接计算哈希值找到对应的页，时间复杂度仅为O(1)。

Hash索引的不足

上面我们看到 Hash 索引通过设置唯一哈希键值，获得了非常快的查找速度，但是也由于哈希作为键值带来了一些不足：

1. 哈希值是无序的，因此没办法利用索引完成「排序」

2. 不支持「最左匹配原则」

3. 如果表中存在大量重复键值的情况下，Hash索引的效率是接近于遍历的，也就是存在「哈希碰撞」问题

4. 第1点中提到无法排序，既然无法排序，Hash索引自然也不支持「范围查询」

MySQL 的 InnoDB 存储引擎是自适应哈希索引的，它有一个特殊的功能叫做「自适应哈希索引」，当InnoDB注意到某些值被使用得非常频繁时，它会在内存中基于B-Tree 索引之上创建一个哈希索引，这样就让B-Tree 索引也具有了哈希索引的一些优点，比如快速的哈希查找 。

聚集索引和非聚集索引

简单来说：

• 『聚集索引』：以「主键」创建的索引

• 『非聚集索引』：以「非主键」创建的索引

两者有如下区别：

• 「聚集索引」在叶子节点中存储表中的数据(注意是叶子节点，中间节点存放的是每个子节点对应的页范围)

• 使用「聚集索引」时，我们通过主键直接拿到对应叶子节点中的数据

• 「非聚集索引」在叶子节点中存储主键和索引列

• 使用「非聚集索引」时，我们先拿到叶子节点中的主键，再使用这个主键，通过聚集索引去拿到对应的数据，拿到主键后的这种再次通过聚集索引查找过程便被称为回表

由于上面提到的回表操作，非聚集索引也被称为「二级索引」。

如果我们创建了多个单列(非聚集)索引，那么 InnoDB 会生成多个索引树，占用大量的磁盘空间(维护这些索引也会耗费大量时间)

在建立非聚集索引时，我们可以同时指定多个列来创建一个索引，例如我们可以对于 user 表指定(company, name)这两个列作为唯一索引，这是一个『联合索引』；当我们建立这样多列索引时，数据库会遵循『最左匹配原则』使用索引(后续再进行讲解)。

这里给出单列索引(图右)和多列索引(图左)的示例图：

而在创建多列索引时，这里涉及到一种特殊的情况--覆盖索引

覆盖索引

『覆盖索引』是非聚集组合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建立索引的字段正好是覆盖查询语句select子句与查询条件Where子句中所涉及的字段，也就是索引包含了查询正在查找的所有数据），例如对于前面的多列索引(company, name)，我们在执行SELECT name FROM user WHERE company='a'时，它能够直接从叶子节点的索引列中获取 name 列的数据。

前面提到，「非聚集索引」的叶子节点中存放主键和索引列，在实际查找时需要进行「回表」，需要通过主键进行二次查询。而对于「覆盖索引」发生的情况下，它无需进行回表，能够直接从对应的索引列中获取相关数据，因此我们在条件允许时应优先考虑使用「覆盖索引」。

最左匹配原则

『最左匹配原则』是针对「联合索引」而言的。指定了多个列的索引便是联合索引。

对「联合索引」进行查询的时候，数据库会自左向右地，按照联合索引中列的排列顺序，对给定的 key 进行匹配检索；但如果进行了<、>、between、like这样的范围查询之后，数据库便不能命中下一个索引列了，之后的 key 值会退化成线性查找。

例如联合索引(a, b, c, d)，查询条件a=1 and b=2 and c>3 and d=4，数据库能够依次命中a、b、c，但无法命中d(因为c是范围查询了，范围检索结果中有多个d的结构树，无法进行排序)。

因此我们在建立「联合索引」的时候，列的顺序会影响能够命中的索引数。

而在使用联合索引进行查询的时候，MySQL 会自动优化条件的顺序以命中尽可能多的索引。

例如对于索引(a, b, c, d)，查询条件c>3 and d=4 and a=1 and b=2和a=1 and c>3 and b=2 and d=4的执行效果是等价的，MySQL 会将顺序不同的条件自动优化成能匹配最多索引的情况，即a=1 and b=2 and c>3 and d=4，依次命中a、b、c。

合并索引

所谓『合并索引』便是使用多个单列索引来实现联合索引的效果，例如用(a)、(b)实现(a, b)的效果，但《高性能MySQL 第三版》的作者认为，应该建立比较好的索引，而不是依赖于「合并索引」。

索引的使用技巧

这里依照参考博文中的建议，给出以下使用技巧：

1. 注意最左匹配原则，MySQL 对于联合索引会一直向右匹配到范围查询<、>、between、like。

2. 尽量选择区分度高的列做索引，区分度的计算公式为COUNT(DISTINCT col) / COUNT(*)，也就是我们要尽量选择重复字段少的列作为索引。

3. 索引列不能参与运算。例如，对于索引(created_time)不能使用FROM_UNIXTIME(created_time)='2019-10-07'这样的条件，索引的本质是「让记录变有序」，对前面的条件进行检索时需要将所有字段进行计算之后再匹配，这样索引便无法发挥作用了，所以我们应该将条件写成created_time=UNIX_TIME('2019-10-07')。

4. 尽量扩展索引而不是新建索引。例如表中已经有了(a)索引，现在要增加(a, b)索引，我们只需要将原先的索引修改为(a, b)即可。