十二、MySQL索引模型和存储、运作规则

在日常开发过程中，一条查询语句面对的数据量过大时，查询的时间就会很长，通常这个时候最方便也最有效的做法就是根据查询语句给表建一个索引，查询效率则会成倍的提高。

数据库的索引，类似新华字典的目录，新华字典有不止一个目录，有拼音目录和笔画目录等，查找某个字的时候，根据实际情况选择不同的一个或多个目录可以快速找到该字的位置，如果没有目录，那查找效率是无法想象的。同样的，数据库表也是同新华字典一样的存在，即数据量会很大，那么不给个目录，自然查找效率就低了，而且为了应对不同筛选条件有时候也需要键多个索引，甚至是联合索引。

那么索引到底是长什么样的呢，它又是如何运作才能让数据库的查询操作提高数倍的效率呢？

索引模型

实现索引的方式有很多种，例如哈希表，有序数组，二叉树等。

哈希表

哈希表是一种以键值对形式存储数据的数据结构，通过 key 可以在 O(1) 的复杂度下查找到 value，具体实现细节见《散列表原理及其应用》

哈希表作为索引对于数据库增删改查效率有怎样的改善呢？在确定某个 key 需要增删改查时，效率最高，但是对于区间查询仍然需要全表扫描。

有序数组

有序数组，即数据已经基于某个字段进行了排序，在区间查找时效率最高，可以直接拿区间起始值二分查找即可。但是有序数组在删除和新增时效率过低，因为为了保持有序需要移动后面的数据。

有序数组索引适合于静态数据的场景下。

二叉搜索树

InnoDB 引擎使用的 B+ 树的实现细节见《MySQL数据库索引原理：B+树原理分析》

B+ 树实际上就是针对硬盘读写慢而改进的二叉搜索树，将二叉改为多叉树，查找时首先根据搜索树找到相应数据块，再从数据块中查找对应数据。

多叉树中的多究竟是多少呢？这是由数据块大小决定的，数据库在硬盘中存储数据都是以数据块为单位，这样就可以使得一个分叉为一个数据块。

以 InnoDB 的一个整数字段为例，大约是 1200 叉树，即一个数据块可存储约 1200 个整数。这样一个多叉树树深为3时，可存储 1200 的 3 次方个整数，即约 17 亿。而实际查询时最多 3 次磁盘 IO 即可。

InnoDB 的索引

InnoDB 引擎是以索引组织表的形式存储数据，一个表的数据都是以主键为索引存储（没有主键的情况下，InnoDB会创建一个默认主键：RowId），并且这个索引是 B+ 树索引。而其他字段创建索引也通常是 B+ 树。并且，主键索引中存储有每一条数据的全部字段值，而在非主键索引中，只存储有主键和相应字段的值，因此在使用非主键索引查找时，同样也需要主键索引提供每一条记录的全部字段值。

例如下表，主键 id，并且有一个 number 整型索引。

CREATE TABLE `test` (
    `id`  int(11) NOT NULL AUTO_INCREMENT ,
    `number`  int(11) NULL ,
    `string`  varchar(255) NULL ,
    PRIMARY KEY (`id`),
    INDEX `number` (`number`) 
);

INSERT INTO `test` VALUES ('1', '11', 'ab');
INSERT INTO `test` VALUES ('2', '6', 'cd');
INSERT INTO `test` VALUES ('3', '17', 'dd');
INSERT INTO `test` VALUES ('4', '3', 'aa');
INSERT INTO `test` VALUES ('5', '20', 'ty');

如下图的主键索引（也称为聚簇索引）示意图，一个表的数据就是以这样的形式存储的，每个叶子节点是一条记录。

下图为 number 字段的索引存储方式，可以看到普通索引只存储相应的字段值和主键。

通过下面两个 SQL 语句来分析一下使用主键索引和非主键索引的区别：

select * from test where id = 4;

select * from test where number = 17;

很明显，第一句 SQL 使用到了主键索引，那么直接通过主键索引查询得到id=4的记录返回即可。

再看第二条 SQL，where 条件是 number 字段，因此肯定会使用到number 索引，但是 number 索引上只能查到部分数据，怎么办呢？答案就是需要回表，也就是从 number 索引查到number=17的记录的主键值，在拿主键值去主键索引查询得到对应记录并返回。

维护索引

页分裂与页合并

我们知道索引存储在硬盘中的数据是一页一页分开的，B+ 树形式的索引分叉的数量也是由页的大小决定。

那么当插入数据时，有可能存在需要申请新页或者移动数据到新页的操作等，这会使得效率打折。同样的，在删除数据时，页内数据变少，空间利用率很低时，则可能通过多个页合并成一个页来提高空间利用率。

如何避免插入数据时偶尔效率低的问题？业务允许的情况下，自增主键是个不错的选择。

上面表 test 的主键便是自增主键，关键字：AUTO_INCREMENT，自增主键的特点就是从 1 开始累加，每次生成的主键 id 值都是当前最大的，因此在维护主键索引时只需要往后追加即可，就不存在数据移动的问题了。

自增主键的好处并不止这一点，观察普通索引存储的数据可知，主键长度越大，索引占用空间则越大，从空间角度看，自增主键能有效降低普通索引的空间占用。

在只有一个主键的情况下，并且是唯一索引，则主键适合使用业务字段。明白了普通索引查询的流程就能理解这里为什么直接使用业务字段作为主键了。