2、mysql索引实现原理

引用百度百科对索引的定义：在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。从这个定义中我们可以知道3个信息：索引是一种数据结构；索引是有序的数据结构；索引可以提高数据检索效率。本文主要就这3点来进行展开介绍。

一、索引是一种数据结构
计算机专业的同学都学过数据结构这门课，适合来实现索引的常见的数据结构无非就那么几种：

• 有序列表（sorted list）
• 哈希表（Hash Tables）
• 二叉树（Binary Search Trees）
• 红黑树（Red-Black Trees）
• 平衡二叉树（AVL Trees）
• B树（B Trees）
• B+树（B+ Trees）
  ……

还有一些就不一一列举了，那么mysql索引是使用的哪种数据结构呢？相信大多人应该都知道：B+ Tree，mysql为什么使用B+树呢？那就需要先了解一下B+ Tree的特性。

1、B+ Tree特性

B+ Tree是从B Tree演化而来，它们都满足二叉树的基本特性：根节点左子树都比根节点小，右子树都比根节点大。而B+ Tree又具有其特有的一些特性：

• 每个节点都可以存放多个数据；
• 非叶子节点只做数据冗余，完整的数据都存放在叶子节点上，非叶子节点其实相当于指针的作用；
• 叶子节点之间都有指针指向下一个元素，并且都是从左到右排好序的，而mysql又对B+
  Tree做了一个小优化：叶子节点之间是双向指针，便于做范围查找；
• 可以通过设置每个节点存放的数据量来控制树的深度，比如想要让树深度最多只有3层，这样数据量越多，每个节点就会存放越多的数据。不过需要注意的是，虽然可以控制树深度为3，但实际上不可能永远绝对是3，mysql一页即一个树节点默认是16KB，所以一个节点存放的数据量也是有限的，当数据量足够大时，3层的B+树显然是存放不了的，因此需要综合考虑树深度、数据量来设置页大小，一般不建议设置mysql默认页大小，当数据量达到一定规模之后，就会考虑分库分表了。

而B Tree每个节点都存放了数据，并没有冗余，并且叶子节点之间没有指针，试想一下，如果mysql采用了B Tree，那么同样的数据量，B Tree比如比B+ Tree深度更深，而树的检索性能取决于树的深度，这样查询性能就远不及B+ Tree。同理，这也是没有使用二叉树、平衡二叉树、红黑树等数据结构的一个主要原因。

2、Innodb索引

Innodb是mysql种使用最多、应用场景最广泛的存储引擎，这里就主要介绍Innodb的索引原理。Myisam的索引也是采用的B+ Tree，其特点类似于Innodb的非聚簇索引。首先我新建一个用户表（user），并插入一些数据：

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(64) NOT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `sex` varchar(16) DEFAULT NULL COMMENT '性别',
  `identity_card` varchar(32) DEFAULT NULL COMMENT '身份证号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`, `identity_card`) VALUES ('1', 'lfl', '25', 'male', '340406199610256666');
INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`, `identity_card`) VALUES ('2', 'zhangsan', '28', 'male', '340406199310253356');
INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`, `identity_card`) VALUES ('3', 'lisi', '30', 'male', '340406199110252356');
INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`, `identity_card`) VALUES ('4', 'limei', '20', 'female', '340406200010258888');
INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`, `identity_card`) VALUES ('5', 'liuyifei', '29', 'male', '340406199210256666');
INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`, `identity_card`) VALUES ('6', 'lixiaolong', '33', 'male', '340406198809024598');
INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`, `identity_card`) VALUES ('7', 'wangwu', '40', 'male', '340406198103186666');
INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`, `identity_card`) VALUES ('8', 'lily', '18', 'female', '340406200312096666');
INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`, `identity_card`) VALUES ('9', 'ab', '25', 'male', '340406199610256666');
INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`, `identity_card`) VALUES ('10', 'dd', '35', 'male', '340406198606231234');

2.1 有序性

本文开头已经说了，索引是一种有序的数据结构，user表目前只有一个主键索引，插入了以上一些数据之后，这些数据默认是已经按照主键排好序的吗？不妨来查一下看看：select * from user；

可以看到数据插入时就已经安装主键id排好序了，mysql为什么插入的时候要进行排序呢？其实并不难理解，我们知道二叉树的遍历是基于二分查找法，B+ Tree也具备二叉树的基本特性，进行二分查找时首先会对数据进行排序，这样有利于提高查找效率。

2.2 数据存储结构

既然Innodb存储引擎索引采用的B+ Tree，那么user表里这10条数据具体是如何来存储的呢？我用一张图来表示如下：

上图中展示了主键索引的结构，每个节点都会包含一个指向左右子树的指针，为了方便画图，部分节点的page指针以及部分叶子节点存放的数据没有画出来。可以看到，叶子节点包含了所有10行数据的主键id和其所在行的data数据，每一页以及页之间的数据都是有序的。

2.2.1 数据页（page）

上面多次提到页（page）这个概念，对于操作系统，像CentOS一页数据是4KB，操作系统有这么一个概念：空间局部性原理，比如操作系统要执行一条指令，这条指令只需要从磁盘中取出一个int类型的数据10（int类型占4个字节）到内存，但是操作系统并不只会从磁盘取4个字节的数据到内存，而是会一下子取这个数据所在的一页（4KB）数据到内存中，这是因为操作系统需要使用到10这个数据时，它会认为下次大概率还会使用到10附近的其它数据，所以索性一下子取一页出来，这样做的好处就是减少磁盘IO，假设另一条指令又需要从磁盘取1KB的数据，而这1KB恰好也在刚才那一页中，这样就减少了一次磁盘IO，进而提升性能。

mysql也利用了这个局部性原理，不同的是mysql默认的一页是16KB，因此mysql中存储单位都是按页来存储的，上图中每个节点就代表了一页。mysql可以通过命令来查看一页默认大小（单位：字节）：show global status like 'Innodb_page_size‘；

2.2.2 数据文件

在上篇《mysql架构组成》一文中有介绍，Innodb存储引擎的数据文件只有两种：.frm和.ibd文件，也就是说，Innodb的数据和索引都存储在.ibd文件中，在这个例子中，对应user.ibd文件。

2.3 聚簇索引和非聚簇索引

上面介绍了主键索引，主键索引最大的特点就是叶子节点不仅包含了主键值，还包含该主键所在行的完整数据。每个InnoDB表都有一个特殊的索引，称为聚集索引或者聚簇索引，用来存储行数据，所以主键索引又叫聚簇索引。那么与之对应的就是非聚簇索引，就是用户自定义的非主键索引，又称辅助索引或二级索引（Secondary Indexes）。辅助索引的特点是叶子节点不仅包含索引值，还包含主键id值。

还是以例子说明，我在user表中name字段上创建索引：ALTER TABLE user ADD INDEX index_name (name)；此时user表就存在两个索引了，那么非聚簇索引的存储结构又是怎样的呢？还是用图表示：

可以看到，叶子节点存放的是辅助索引值和主键id的值，当执行这么一条sql时：select * from user where name = ‘lily’;就会走辅助索引进行检索，找到对应的叶子节点后再跟进主键id=8到主键索引里查询这一行的完整数据，我们把根据辅助索引里的id到主键索引里查找该行数据并返回这一过程叫做回表，回表需要磁盘IO，所以会影响查询性能，我们写sql时应尽量避免回表或者减少回表次数。

我们可以思考一下，为什么辅助索引要采用这样的存储方式？即叶子节点存放主键值而不是一整行的数据呢？

这个问题其实很容易理解，如果存放的是一整行的数据的话，就会占用更多的空间，这样同样的数据量，可能树深度更大，就会降低查询效率；另一个原因就是主键索引和辅助索引的叶子节点都是存放一整行的数据，那么当有数据变更时，就需要同时维护这两个索引的数据一致性，增加mysql复杂性，这样也会降低mysql的性能，当辅助索引数量越多时，这个劣势就越明显。

Innodb表表主键索引的最佳实践

在我们日常工作中，设计一张Innodb表时一般都会定义一个主键id，并且这个主键id是自增的、数值类型的列，一般都会使用int或者bigint类型，mysql官方也是这么建议的，参考官网：https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html，关于Innodb表的主键，mysql有这么一套逻辑：

• 如果创建表的时候定义了主键，就会使用你定义的作为表的主键，并且把它作为聚簇索引，Innodb表只有一个聚簇索引；
• 如果创建表的时候没有定义主键，Innodb存储引擎就会选择第一个唯一索引（UNIQUE
  index）作为主键，并且这个唯一索引的所有列都是非空的（NOTNULL）;
• 如果没有定义主键，也没有合适的唯一索引，那么Innodb就会自己生成一个名为GEN_CLUST_INDEX的隐藏列（隐藏列是指对用户不可见），该隐藏列是个自增的，占用6个字节，我们可以认为是rowId；

由此可见，对于Innodb表，不管你有没有显式的定义主键，都会有一个主键。那么关于主键的数据类型，mysql并没有强制规定，你可以用整型（int或者bigint），也可以用字符串类型比如UUID，但是mysql官方却偏偏建议设置成自增的数值类型，这是为什么呢？

（1）为什么设置成数整型？

需要从两个角度考虑：空间和性能。首先整型比较节省空间，比如int类型占4个字节，bigint类型占8个字节，作为主键，它所占空间大小直接决定了一颗固定深度的主键索引的B+ 树所能容纳的数据量。我们可以粗略估算一下：

• 如果主键id选用int类型，B+
  树深度为3，参考上文中user表主键索引的数据结构图来看，每个主键列中包含了一个int类型的id值和2个指向page页的指针（mysql一个地址占6个字节）：4+6+6=14个字节，mysql一页默认16KB，也就是说一页可以存放1170个索引；
• 主键索引叶子节点存放了整行数据，user表各个字段类型分别是：int、varchar(64)、int、varchar(16)、varchar(32)，以varchar(64)来说，是64个字符，采用的utf-8编码，一个字符占3个字节（这里为了估算方便，统一就按3字节算，其实utf-8编码下，数字和字母一个字符是占1个字节，汉字3个字节），那么一行数据最大占用344个字节（这里为了估算，仅仅计算了一行中包含的业务数据大小），也就是说一页可以存放16KB/344b
  = 47行数据，3层B+树一共存放数据量就是：1170*1170*47=64338300行数据。一般情况下，单表我们不可能会存放这么多数据，阿里巴巴的开发手册中要求单表超过500W就建议做分库分表，这个要求可能不适用部分应用场景，但是一般情况下我们单表顶多也就一两千万数据，否则就需要做优化了。这或许也是mysql一页默认16KB的其中一个原因，已经能满足绝大多数应用场景了。

当然我这个user表规模比较小，实际生产中的表肯定要更复杂一些，有些规模比较大的项目中主键采用了bigint类型，计算方法类似，总之可以看到，3层的B+树就能够存储较为庞大的数据量，足够绝大多数场景下使用了。也就是说相同的数据量，当主键占用空间越小，一个主键索引树的深度就越小，查询性能就越高。

另外，主键字段占用空间小还有一个好处就是辅助索引占用空间也会随之减少，因为辅助索引的叶子节点包含了主键id值，主键越小，占用空间就越小。

（2）为什么设置成自增的？

这个要结合主键索引树的结构来看，由于主键索引的B+树页内和页间数据都是有序的，假设使用UUID来作为主键值，UUID是无序的，那么每次新增一条记录时，就有可能在叶子节点中间插入，这样就会导致页的分裂和树的重平衡，这个过程需要加悲观锁，大大影响性能。相反，如果是自增的话，每次插入的时候id都是最大的，只需要直接在叶子节点的最后一页里插入，如果最后一页满了就直接在后面新开一页即可，不会涉及页的分裂和树重平衡，提升了性能。

3、Myisam索引
上文也有提到，Myisam存储引擎的索引结构类似于Innodb的非聚簇索引，不过有一些细节上的区别。根据上篇文章所介绍的，Myisam存储引擎的表的数据文件有3个：.frm、.MYD、.MYI三个文件，所以它的数据与索引是分别在两个数据文件中，这就从物理存储上决定了Myisam索引不可能是聚簇索引。以主键索引为例，Myisam的索引树的叶子节点存放的是主键id值和该行数据所在的磁盘地址，查询的时候就会根据这个地址到.MYD文件中查找该行数据。还是用user表为例，假设是user表使用了Myisam存储引擎：

现在已经介绍了Innodb和Myisam两种存储引擎的索引实现原理，那么我们就可以知道当mysql执行一条sql时，具体是怎样利用索引来进行查询的，根据这个索引原理就可以写出更高效的sql语句，关于索引及sql的优化，后面我会单独写一篇来介绍，本文就不展开了。

4、Hash索引
不知道大家有没有注意到，使用Navicat工具创建索引时，索引方法字段会有两个选择：BTREE和HASH两种索引：

BTREE索引就是上面介绍的B+ Tree实现的，那么HASH索引是如何实现的呢？这个有点类似于Java中的HashMap的数据结构，mysql的Hash索引存储的是索引列通过Hash运算计算的hash值，如果hash冲突的话，就在该hash值所在位置形成一个链表往后增加，链表每个节点存放的是索引列的值和该行数据所在磁盘的地址，通过这个地址就可以到数据文件中找到一行的完整数据。

假设user表的name字段创建了一个HASH索引，那么它的存储结构就会类似下图：

Hash索引最大的特点就是对于等值查询效率非常高，但是缺点也很明显：不支持范围查询，所以它的应用场景非常有限，另外如果一张表的数据量有百万甚至千万级，那么hash冲突的概率可能也会非常高，就会导致某个hash值所在的位置处的链表长度过长，遍历链表的性能就会大大降低。

5、总结
前面对Innodb和Myisam两种存储引擎的索引的实现原理做了简单的介绍，不管是Innodb还是Myisam，索引都是存储在磁盘上的，mysql执行sql前，会把索引树加载到内存中（对于主键索引，是把非叶子节点加载到内存），然后在内存中进行检索，对于辅助索引，通过内存检索出主键id之后，再经过一次磁盘IO到主键索引上把数据查出来（回表）。