跟我学Mysql之索引篇

---

声明:这是我在大学毕业后进入第一家互联网公司学习的内容

---

索引是什么

索引用于快速查找具有特定列值的行。没有索引，MySQL必须从第一行开始，然后通读整个表以找到相关的行。表越大，花费越多。如果表中有相关列的索引，MySQL可以快速确定要在数据文件中间查找的位置，而不必查看所有数据。这比顺序读取每一行要快得多。

如果上面的描述还不足够让你理解，我可以先给大家讲一个例子，假如你需要在一本字典里查找一个生僻字（首先你不知道它的发音，你就不会去找字母目录，而是会去找偏旁目录）如果你不找目录，你就必须要把字典全部翻一遍一个一个对比这个字（我的天，有这么麻烦吗）前提是你确实不知道这个字的发音，而且你也不想找偏旁索引，那你只能这样麻烦地做了，然后你找到偏旁所在的页数后，你就会去那一页找到相关的字，最终找到你想查找的生僻字的全部信息。

数据库索引也是如此，查询一张表的某一行的信息（不是查全表数据）。

select * from test where a=“1”

如果你不用索引，那么查询过程是这样的：先获取这张表的所有数据，然后依次遍历，将a=1的行筛选出来，最后返回

如果你使用索引，那么查询过程是这样的：直接去索引里找到a=1的所有行，返回。

当然我这么说不太准确，只是为了帮助大家更好的理解索引，因为索引也分几种，具体实现的底层逻辑也不太一样，下面我就讲讲索引的分类。

索引分类

由于在Mysql5.7中，InnoDB是默认的MySQL存储引擎。所以下面我讲到的索引都是基于InnoDB下的。

聚簇索引

每个InnoDB表都有一个特殊的索引，称为聚簇索引（也叫主键索引、一级索引） ，用于存储行数据。通常，聚簇索引与主键同义，也就是说主键的字段默认给它上了聚簇索引 。使用InnoDB聚簇索引为每个表优化最常见的查找和DML操作。

官方解释：

• 在PRIMARY KEY表上定义a 时，InnoDB将其用作聚簇索引。为创建的每个表定义一个主键。如果没有逻辑唯一且非空的列或列集，请添加一个新的 自动递增列，其值将自动填充。
• 如果没有PRIMARY KEY为表定义，MySQL找到所有列都不为空且唯一的第一个UNIQUE索引，InnoDB将其用作聚集索引。
• 如果表没有索引PRIMARY KEY或没有合适的 UNIQUE索引，则在InnoDB 内部生成一个隐藏的聚集索引GEN_CLUST_INDEX，该索引在包含行ID值的合成列上命名 。这些行由InnoDB分配给该表中各行的ID排序 。行ID是一个6字节的字段，随着插入新行而单调增加。因此，按行ID排序的行实际上在插入顺序上。

下面有几点需要注意：

• 聚簇索引必须是唯一索引，也就是该字段的值必须唯一且非空。
• 如果一个表有主键，那么该主键也作聚簇索引。
• 如果一个表无主键，但是有很多索引，那么第一个非空且唯一的索引会被转化成聚簇索引。
• 如果一个表无主键（实战基本不可能），且整个表的所有索引都不满足（既不为空，而且唯一），则InnoDB为了保证表的完整性，会给隐藏字段中6字节的DB_ROW_ID上主键和聚簇索引，只不过不能被外部调用。

在实战中，一般来说主键最好设为自增id，而且尽量避免用业务id作为主键，这样会减少索引的存储空间以及提高搜索效率，具体原因我会我后面进行讲解。

二级索引

除聚簇索引之外的所有索引都称为二级索引（也叫辅助索引、非聚簇索引）。在中InnoDB，二级索引中的每个记录都包含该行的主键列以及为辅助索引指定的列。InnoDB使用此主键值在聚簇索引中搜索行。

也就是说如果你需要通过一个二级索引去查找一行的值，那么你得先通过二级索引去找到二级索引所存储的聚簇索引的值，然后再通过聚簇索引去找到该行的值，这个过程叫做回表。

如果主键较长，则辅助索引将使用更多空间，因此具有短的主键是有利的。这也是上文说的主键最好设为自增id，且与业务无关。

联合索引

由于二级索引的回表操作会再次查询聚簇索引的值，这是一件比较耗时的操作，如果你需要高频率地查找某些字段，这时候就可以用联合索引（覆盖索引），将你基于查询的字段和最终想查询到的字段组合一起。一个联合索引最多可以包含16列，可以被认为是排序数组，其行包含通过串联索引列的值而创建的值。

比如一张用户信息表的结构如下

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

id是主键，idcard是唯一信息，但是长度是varchar类型且长度很大不适合做聚簇索引，所以设为二级索引，这个时候你可以通过idcard去查询每个用户的所有信息。

此时你有需要通过名字去查年龄的需求，这个时候可以把名字和年龄上一个联合索引，名字在前，年龄在后（顺序很重要，后面在讲索引的结构会讲到为什么要这样排序）。你每次通过年龄索引查询的值为主键id和age，这个时候可以直接取到age的值，这样就不用重新通过主键id再去查询age一次了（回表操作）。

但是联合索引是比较占资源，如果你不需要高频请求，建议不要随便使用联合索引，索引的维护总是有代价的。

索引前缀

使用字符串列的索引规范中的语法，您可以创建仅使用列首字符的索引 。以这种方式仅索引列值的前缀可以使索引文件小得多。

例如给一个字符串的字段上索引，一般定义这个字段的前N位字符串来建立索引。
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

前缀最长可以为1000个字节（InnoDB表中为767个字节 ，除非已 innodb_large_prefix设置）。

如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。

例如在刚刚的那张表，执行
select * from test where blob_col=‘abcdefghijklmn’

索引会去匹配字段前10位为’abcdefghij’的所有行，然后依次比较剩下的匹配’klmn’的值，返回。

索引的数据结构

索引的数据结构是怎么都逃不走的一块，我比较难理解，所以也不在讲索引分类的时候讲了。

常见的几种索引数据结构

我先介绍常见的几种索引的数据结构

• key-value：这种数据结构应该比较常见，键值对也叫哈希表，实现也比较简单，用一个哈希函数把key换算成一个确定的value，然后把value放在数组这个位置，对于可能出现不同的key换算成了一个相同的value这种情况，可以在value处加上一个链表，保存相同value值的key信息。如果没有出现这种情况（不同的key换算成了相同的value），查询、插入速度是很快的。但是一旦出现上述情况，哈希索引在做链表区间查询是很慢的，因为你通过键值对查询后需要遍历后面链表的所有用户。

• 有序数组： 假设索引没有重复，那么数组会按着这个索引字段的值递增顺序保存。如果你需要查询某个字段的值，通过二分法可以快速找到，时间复杂度为O(log(N)。
  只看查询速度，有序数组是接近完美的，但是更新数据的时候比较麻烦，如果你在中间插入一条数据需要将后面所有数据的下标往后挪一位，这个成本是很高的。

• 二叉树：二叉树的规则是，每个节点的左儿子小于父节点，父节点又小于右儿子，这种数据结构的查询时间复杂度是O(log(N))，并且为了维持O(log(N))的查询速度，你还得保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是O(log(N))。单看查询和插入二叉树接近完美了，但是二叉树有一个坏处，就是占空间，因为索引不只是存在内存，还要写到磁盘上。假如你存储100w条数据，那么用平衡二叉树的话，树高就是20。一次查询你最多需要访问20个数据块，也就是最多读20次磁盘，为了让一个查询最少地读磁盘，我们就应该使用N叉树，这里的N取决于数据块的大小。这也是大多数数据库的存储选择的基础数据结构N叉树，并在基础上进行改造。

总结：

哈希表结构适合等值查询的场景，比如非关系型数据库

有序数组适合静态存储的场景，比如你保存某年某个城市所有人口信息，并且不会修改数据了。

N叉树适合大部分数据库引擎，有着良好的读写性能优点以及适配磁盘的访问模式。

InnoDB的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，InnoDB使用了B+树索引模型，所以数据都是存在B+树的。

每一个索引都对应一颗B+树,对于B+树这种数据结构,我简单的介绍一下它的特征和优势。参考资料里有详细介绍B+树的链接，有兴趣的可以学下具体是如何实现的。

B+树的特征：

1.有k个子树的中间节点包含有k个元素，每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。

2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3.所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

B+树的优势：

1.单一节点存储更多的元素，使得查询的IO次数更少。

2.所有查询都要查找到叶子节点，查询性能稳定。

3.所有叶子节点形成有序链表，便于范围查询。

总结：InnoDB的索引结构始终保持排序，从而可以快速查找精确匹配（等于运算符）和范围（例如，大于，小于和BETWEEN 运算符）

多个索引模型

前面我讲过索引的分类，一般实战中最常见的一张表结构都含有聚簇索引（主键）配合一个二级索引组成的，那么通过了解InnoDB的索引模型，我们应该能很清晰的看到二级索引的实现过程了。

如图所示，有这么一个表，ID是聚簇索引（主键），K是二级索引（唯一）。

select * from table where ID=100
mysql会用聚簇索引进行查找，通过B+树迅速查询到ID=100的叶子节点，拿到这一行的全部数据，返回。

select * from table where k=1
mysql会用二级索引进行查找，通过B+树迅速查询到k=1的叶子节点，拿到当k=1时，主键的ID的值=100，然后通过ID=100再去另外一颗B+树去查询到ID=100的叶子节点，拿到这一行的全部数据，返回。

所以基于聚簇索引查询只需要搜索一棵树，而基于二级索引需要多扫描一棵树。因此我们应该在应用中尽量使用主键查询。不过问题来了，为什么上文我说主键最好使用与业务无关的自增id，如果无关的话，你怎么知道你想查询数据的主键ID是什么呢？因为我们还需要考虑到维护索引的代价。

索引维护

B+树为了维护索引的有序性，在插入数据的时候会做必要的维护。比如上图，你要插入主键ID为700的行数据，你只需要在600后面插入一个新数据即可。但是你要是插入的主键ID为400，那么就需要逻辑上移动后面的数据，把这个数据空出来让ID=400的数据插入。

更麻烦的是如果600是数据页最后一个数据，即该数据页满了，为了插入ID=400 ，你必须重新创建一个新数据页，将ID=600的数据放过去，然后才能将ID=400的数据插入到原数据页。这个过程叫做页分裂，在这种情况，查询就会多扫一张数据页，而且空间利用率会降低百分之50，因为本来只用存放在一页的数据，现在分到2页了。

不过有分裂页有合并，相邻的两页由于删除了数据，利用率变低后，会将数据页做合并。

所以回到上一个问题，为什么虽然通过聚簇索引查询很快，但我们仍然使用二级索引查询，因为主键索引如果不是自增的话，很难保证你的插入是有序的，如果你插入不是有序，那么就可能触发页分裂，影响性能和存储空间。所以得使用自增主键，这样的话主键也就是无业务意义的，我们也不能通过一个无意义的字段去做查询吧。

最左前缀原则

上文我讲过对于联合索引的创建，顺序很重要，因为联合索引需要遵循最左前缀原则，具体看下面例子。

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

这个表，name为联合索引，它可用于查询指定在已知范围内的last_name和first_name 值组合的值。它也可以用于仅指定last_name值的查询， 因为该列是索引的最左前缀。

下面的sql是可以通过name索引进行查找的

SELECT * FROM test WHERE last_name='Jones';

SELECT * FROM test
  WHERE last_name='Jones' AND first_name='John';

SELECT * FROM test
  WHERE last_name='Jones'
  AND (first_name='John' OR first_name='Jon');

SELECT * FROM test
  WHERE last_name='Jones'
  AND first_name >='M' AND first_name < 'N';

但是，在以下查询中不能用name索引进行查找：

SELECT * FROM test WHERE first_name='John';

SELECT * FROM test
  WHERE last_name='Jones' OR first_name='John';

为什么必须通过联合索引的最左字段查询呢？因为索引的实质是B+树，对于非聚簇索引（联合索引属于非聚簇索引）叶子节点存储的是主键的值(如果是联合索引，还会存联合索引的非最左字段)

结论：如果表具有联合索引，则优化器可以使用索引的任何最左前缀来查找行。举例来说，如果你有一个三列的索引(col1, col2, col3)，你有索引的搜索功能 (col1)，(col1, col2)以及 (col1, col2, col3)。

检查索引是否被使用

始终检查所有查询是否真的使用您在表中创建的索引。可以使用 EXPLAIN语句

EXPLAIN可以为 SELECT， DELETE， INSERT， REPLACE，和 UPDATE语句工作。

当EXPLAIN与可解释的语句一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。也就是说，MySQL解释了它将如何处理该语句，包括有关如何连接表以及以何种顺序连接表的信息。

在EXPLAIN的帮助下，您可以看到应该在表中添加索引的位置，以便通过使用索引查找行来使语句更快地执行。

具体由EXPLAIN输出字段的详细意义请查看 EXPLAIN Output Columns

浅谈索引的构建过程

排序索引构建

在创建或重建索引时，InnoDB执行批量加载，而不是一次插入一个索引记录。这种索引创建方法也称为排序索引构建。排序索引构建不支持空间索引(Spatial Indexes)。

索引构建分为三个阶段

• 扫描聚簇索引，并生成索引条目并将其添加到排序缓冲区，当排序缓冲区已满时，将对条目进行排序并将其写到临时中间文件中。此过程也称为"RUN"
• 将一个或多个"RUN"写入临时中间文件后，将对文件中的所有条目执行合并排序。
• 排序后的条目将插入到B+树中。

修改数据

由于InnoDB多版本并发控制（MVCC）的存在，增删改的操作对二级索引的处理方式不同于对聚簇索引的处理方式。如果忘了MVCC请复习一下 跟我学Mysql之事务篇

• 聚簇索引

聚簇索引中的记录将就地更新，其隐藏的系统列指向撤消日志条目，可以从中重建记录的早期版本。

在聚簇索引中，DB_TRX_ID检查记录的记录，如果在启动读取事务后修改了记录，则从撤消日志中检索记录的正确版本。

• 二级索引

二级索引记录不包含隐藏的系统列，也不会就地更新。

更新二级索引列时，将对旧的二级索引记录进行删除标记，将新记录插入，并最终清除带有删除标记的记录。当二级索引记录被删除标记或二级索引页被较新的事务更新时，InnoDB在聚簇索引中查找数据库记录。

总结

本篇讲解了索引的基本数据结构与分类，和INNODB模型和底层实现基本原理，后期会更加详细的介绍INNODB索引的具体实现过程。

参考资料

B+树图文详解

INNODB索引-官方文档

---

版权声明：

原创不易，洗文可耻。除非注明，本博文章均为原创，转载请以链接形式标明本文地址。