深入浅出数据库索引

1. 索引的本质

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

举个例子：

上图展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在(O(log_2n))的复杂度内获取到相应数据。

虽然这是一个货真价实的索引，但是实际的数据库系统几乎没有使用二叉查找树或红黑树（red-black tree）实现的，原因会在下文介绍。

2. Mysql存储引擎

Mysql存储引擎有多种，比如Memory等，此处主要讲解MyISAM和InnoDB

• MyISAM
  它不支持事务，也不支持外键，尤其是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。
• InnoDB
  InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全（支持事务）。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
  【更多区别：】
• MyISAM是非事务安全型的，而InnoDB是事务安全型的。
• MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
• MyISAM支持全文类型索引，而InnoDB不支持全文索引。（在5.6版本及以后InnoDB是支持全文索引的）
• MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
• MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
• InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。

3. Mysql索引

3.1 数据结构 - B-Tree和B+Tree

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，

B-Tree：

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：
d为大于1的一个正整数，称为B-Tree的度。
h为一个正整数，称为B-Tree的高度。
每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。
每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。
所有叶节点具有相同的深度，等于树高h。
key和指针互相间隔，节点两端是指针。
一个节点中的key从左到右非递减排列。
所有节点组成树结构。
每个指针要么为null，要么指向另外一个节点。
如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于(v(key_1))，其中(v(key_1))为node的第一个key的值。
如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于(v(key_m))，其中(v(key_m))为node的最后一个key的值。
如果某个指针在节点node的左右相邻key分别是(key_i)和(key_{i+1})且不为null，则其指向节点的所有key小于(v(key_{i+1}))且大于(v(key_i))。
下图是一个d=2的B-Tree示意图。

B+Tree：

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。

与B-Tree相比，B+Tree有以下不同点：

1. 内节点不存储data，只存储key。
2. 叶子节点不存储指针。
3. 数据记录都存放在叶子节点中。

下图是一个简单的B+Tree示意。

由于并不是所有节点都具有相同的域，因此B+Tree中叶节点和内节点一般大小不同。这点与B-Tree不同，虽然B-Tree中不同节点存放的key和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中B-Tree往往对每个节点申请同等大小的空间。个人感觉这两者最主要的区别就是内节点上是否存储数据。

一般来说，B+Tree比B-Tree更适合实现外存储索引结构，具体原因与外存储器原理及计算机存取原理有关，将在下面讨论。

带有顺序访问指针的B+Tree

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。

如上图所示，在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

索引的物理存储

与B-Tree相比，B+Tree有以下不同点：

1. 非叶子结点的子树指针与关键字个数相同；
2. 非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）；
3. 为所有叶子结点增加一个链指针；
4. 所有关键字都在叶子结点出现；
5. 内节点不存储data，只存储key 如：（M=3）

为什么B+Tree更适合做文件索引

因为B+ Tree的内部节点并没有指向关键字具体信息的指针，所以他的内部节点比其他结构的更小，同样大小的盘块能够容纳更多的单位索引结构，一次性读入内存的关键字信息索引就更多，相对来说IO读写次数也就少很多。

为什么不用AVL数或者红黑树做索引

这个问题回答之前，我们往往评价程序、数据结构、算法的优良好坏由时间和空间决定

1. 空间：
   红黑树是二叉树的进化和演变而来的，是一种平衡二叉查找树：（红黑树）
   
   抛开红黑树的其他性质，红黑树每一个父节点都有一个或者两个子节点，那么我们知道，计算机存储结构的最小单元是页，那么就是我无论是否会占满整个页系统都会给分配一个最小存储单元：页（InnoDB一页大小固定默认是16k），那么这就很容易造成了空间上的大量浪费：因为很可能两个子节点根本就无法占满一页！这就造成了空间上的浪费！
2. 时间：
   二叉树，红黑树的最大的特点就是同样大小的盘块不能够容纳更多的单位索引结构（数据在内节点存放），一次性读入内存的关键字信息索引就更少一些，这就从根本上决定了红黑树I/O到内存上的次数>B树。一般的红黑树的高度h会大于b+树，而且红黑树上逻辑上相邻的记录在磁盘上也并不是一定物理相邻的（例如我第一个图里所展示的）。从而造成了时间上的浪费！这样一个空间和时间都不尽如人意的数据结构为何还要使用呢！

两种引擎的索引存储机制

下面就让我们从索引结构的角度来分析一下两种引擎的索引存储机制

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

上图是MyISAM表主索引的示意，我们可以看到仅仅MyISAM的索引文件仅仅保存数据记录的地址，在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的。

InnoDB索引实现

都是采用的B+ Tree作为的索引结构，但具体实现方式大相径庭。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，定义在Col3上的一个辅助索引：

索引失效条件

1. 模糊查询，即like以%开头的SQL
   解决：当like前缀没有%，后缀有%时，索引有效。
2. or语句前后没有同时使用索引
   解决办法：全部使用索引
3. 联合索引（a,b,c）生效的条件只有 ab ac abc即最左前缀原则
4. 在索引列上使用 IS NULL 或 IS NOT NULL操作
5. 在索引字段上使用not，<>，!=
6. 对索引字段进行计算操作、字段上使用函数
7. 全表扫描速度比索引速度快

补充

1. 索引类型

用于提高读写效率的数据结构有很多，这里先介绍常见的3种，分别是：

• 哈希表
• 有序数组
• 搜索树（重点）

1.1 哈希索引

哈希表是一种以键-值（key-value）的方式存储数据的结构，我们只要输入待查找的值（即key），就可以找到其对应的值（即Value）。哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置，即idx = Hash(key)。如果出现哈希冲突，就采用拉链法解决。

Hash算法是一个广义的算法，也可以认为是一种思想，使用Hash算法可以提高存储空间的利用率，可以提高数据的查询效率，也可以做数字签名来保障数据传递的安全性。所以Hash算法被广泛地应用在互联网应用中。

因为哈希表中存放的数据不是有序的，因此不适合做区间查询，适用于只有等值查询的场景。

1.2 有序数组

有序数组在等值查询和范围查询场景中的性能都非常优秀。用二分法就可以快速找到（时间复杂度为O(logN)）。但是如果要往中间插入一条数据，则必须挪动后面的所有记录，成本较高。因此，有序数组只适用于静态存储引擎，即数据表一旦建立后不再会修改。

1.3 B+树索引（INNODB）

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。

假设，有这样一张表：该表主键为ID，且还有一个字段为k，并在k上有索引。

CREATE TABLE T(
    id int primary key,
    k int not null,
    index (k)
)engine=InnoDB;

表中有5条记录，分别为R1~R5，(100,1)、(200,2)、(300,3)、(500,5)和(600,6)。则在InnoDB中的索引组织结构是这样的：

根据叶子结点的内容，索引类型分为主键索引和非主键索引。

• 主键索引的叶子结点存的是整条记录，主键索引也被称为聚簇索引（clustered index）
• 非主键索引的叶子结点存的是主键的值，非主键索引也被称为二级索引（secondary index）/普通索引/辅助索引。

那么，基于主键索引和非主键索引的查询有什么区别？

• 如果语句是 select * from T where ID=500，并且存储引擎是InnoDB，即主键查询，则只需要搜索ID这棵树。
• 如果语句是 select * from T where k=5，即非主键索引查询，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。从非主键索引回到主键索引的过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。而从存储空间的角度讲，因为非主键索引树的叶结点存放的是主键的值，那么，应该考虑让主键的字段尽量短，这样非主键索引的叶子结点就越小，非主键索引占用的空间也就越小。一般情况下，建议创建一个自增主键，这样非主键索引占用的空间最小。

2. 联合索引

联合索引是指对表上的多个列进行索引。下面以一个例子进行说明。假设有下面这样一张表，有这样一个需求，我们需要查询某个用户的购物情况，并按照时间进行排序，取出某用户近几次的购物情况。（注：例子来源于《MySQL技术内幕》）

// 表
CREATE TABLE buylog(
    userid int not null,
    buy_date DATE
)ENGINE=InnoDB;

// 插入数据
insert into buylog values(1, '2019-08-13');
insert into buylog values(2, '2019-08-14');
insert into buylog values(3, '2019-08-15');
insert into buylog values(1, '2019-08-11');
insert into buylog values(3, '2019-08-10');
insert into buylog values(1, '2019-08-12');

// 添加索引
alter table buylog add index(userid);
alter table buylog add index(userid, buy_date);
// （或用key关键字也一样的）
alter table buylog add key(userid);
alter table buylog add key(userid, buy_date);

上面的代码建立了两个索引，两个索引都包含了userid字段。

如果只对于userid进行查询，如：

select * from buylog where userid=2;

通过explain查看该语句的执行情况，如下

可以看到，possible_keys在这里有两个索引可供使用，分别是userid索引和(userid,buy_date)联合索引。优化器最终选择的索引（即key）是userid，因为该索引的叶子节点只包含单个键值，所以理论上一页能存放的记录会更多（意味着可以减少查询的次数）。

接着假定要查询userid为1的最近两次的购买记录，如：

select * from buylog where userid=1 order by buy_date desc limit 2;

同样的，我们看一下它的执行过程是怎样的，如下：

可以看到，这一次查询优化器选择的索引是userid_2（也就是(userid, buy_date)联合索引）。为什么呢？因为在这个联合索引中，记录已经分别根据userid和buy_date排好序了，利用这个索引则可以直接取出相应的数据而无需再对buy_date额外做一次排序操作了。如果强制使用userid索引，则它的执行计划如下：

从Extra字段可以看出，该语句的执行需要使用fliesort，也就是需要一次额外的排序操作才能完成查询。显然，这个排序就是对buy_date字段的排序，因为这里仅使用了userid索引，该索引未对buy_date进行排序。

总结：

联合索引(a, b)是根据a, b进行排序（先根据a排序，如果a相同则根据b排序）。因此，下列语句可以直接使用联合索引得到结果（事实上，也就是用到了最左前缀原则）：

select ... from xxx where a=xxx; 
select ... from xxx where a=xxx order by b;

而下列语句则不能使用联合查询：

select ... from xxx where b=xxx;

对于联合索引(a, b, c)，下列语句同样可以直接通过联合索引得到结果：

select ... from xxx where a=xxx order by b;
select ... from xxx where a=xxx and b=xxx order by c;

而下列语句则不行，需要执行一次filesort排序操作。

select ... from xxx where a=xxx order by c;

即：（a，b，c）生效的只有a ab abc

3. 最左前缀原则

对于有很多字段的一张表，查询的方式是多样的，难道要为了每一种可能的查询都定义索引吗？这样岂不是很浪费空间，毕竟建索引也是需要一些空间的。事实上，B+ 树这种索引结构，可以利用索引的“最左前缀”原则来定位记录，避免重复定义索引。

以下面的例子进行说明什么是“最左前缀原则”。

假设建立了一个联合索引(name,age)，可以看到，索引项是按照索引定义里面出现的字段顺序排序的，先根据名字排序，名字相同的就根据年龄排序。

当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。

如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张%’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

因此，基于最左前缀原则，我们在定义联合索引的时候，考虑如何安排索引内的字段顺序就至关重要了！评估的标准就是索引的复用能力，比如，当已经有了(a,b)字段的索引，一般就不需要再单独在a上建立索引了。

4. 覆盖索引

还是利用“B+树索引”提到过的表，如果执行的语句是：

select * from T where k between 3 and 5;

则这条SQL语句的执行流程如下：

1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
2. 再到 ID 索引树查到 ID=300 对应的 R3；
3. 在 k 索引树取下一个值 k=5，取得 ID=500；
4. 再回到 ID 索引树查到 ID=500 对应的 R4；
5. 在 k 索引树取下一个值k=6，不满足条件，循环结束。
   在这个过程中，回到主键索引树的过程，称为回表。在这个例子中，由于查询的结果是所有字段，所需要的数据只有主键上才有，所以不得不回表。但如果执行的语句是下面这样的，注意！这里查询的结果只是“ID”（恰好是主键），而不是所有字段了。

select ID from T where k between 3 and 5;

由于查询的值是ID，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里，索引k已经“覆盖了”我们的查询需求，故称为覆盖索引。

除了上面这种情况，针对某些统计问题时，覆盖索引也能发挥用处。还是以上面的例子，执行如下语句来统计表的记录总数（在此我们假设这张表数据量特别特别大，需要多次磁盘IO）：

select count(*) from T; 

如果没有对字段k设置索引，那么只能是通过聚簇索引来计算；如果对字段k设置了索引，那么，由于聚簇索引的叶结点存放的是整行记录的所有信息，而辅助索引的叶结点只存放主键，两者相比，对于一页内存，显然辅助索引能够存放的节点更多，意味着辅助索引可以减少IO次数，从而更快的计算出count(*)的值。

验证如下：

没有对字段k设置索引时，优化器会选择聚簇索引进行操作（即key为PRIMARY）。

对字段k设置了索引时，优化器会选择辅助索引进行操作（即key为k）

可见，如果建立了辅助索引，在有些场景下，优化器会自动使用辅助索引从而提升查询效率。

总结：覆盖索引就是从辅助索引中就能直接得到查询结果，而不需要回表到聚簇索引中进行再次查询，所以可以减少搜索次数（不需要从辅助索引树回表到聚簇索引树），或者说减少IO操作（通过辅助索引树可以一次性从磁盘载入更多节点），从而提升性能。

5. 索引下推

什么是索引下推（Index Condition Pushdown，ICP）呢？假设有这么个需求，查询表中“名字第一个字是张，性别男，年龄为10岁的所有记录”。那么，查询语句是这么写的：

select * from tuser where name like '张 %' and age=10 and ismale=1;

根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3），接下来是怎么处理的呢？当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。

但是MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

下面图1、图2分别展示这两种情况。

图一

图二

图 1 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

总结：如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

参考

文章参考1： 干货：mysql索引的数据结构

文章参考2：MySQL索引背后的数据结构及算法原理