索引

索引其实就是为了提高数据查询的效率，就像书的目录一样

 

索引常见的三种模型：

①哈希表：k-v结构,适用于只有等值查询的场景，范围查询效率低 【插入速度很快：计算key值即可存储】

 

②有序数组：等值查询和范围查询的性能就都非常优秀

【查询性能最好，效率都是二分法O(log(N)),但插入成本太高（插入一个记录就必须挪动后面所有记录）】

∴ 有序数组索引只适用于静态存储引擎

 

③搜索树：数据库使用的都是N叉搜索树

【由于读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中】

 

使用N叉而不使用二叉的原因: 

虽然二叉搜索树效率最高，但是索引不止存在内存中，还要写到磁盘上。树高决定访问数据块的时间，

在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。对于一个 100 万行的表，

如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间。

一个查询想尽量少读磁盘，查询过程必须访问尽量少的数据块

 

所以虽然二叉树搜索效率高，但往往查询可能很慢；

为了提升查询效率，使用N叉树，“N 叉”树中的“N”取决于数据块的大小

 

以 InnoDB 的一个整数字段索引为例，N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。

 

而树根的数据块总在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需访问 3 次磁盘。

其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

 

其它模型：跳表，LSM 树，有兴趣可以自行了解。

 

数据库底层存储的核心就是基于这些数据模型的。

每碰到一个新数据库，我们需要先关注它的数据模型，这样才能从理论上分析出这个数据库的适用场景

 

 

在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。

 

而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

 

 

InnoDB 的索引模型（B+树）

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。

InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

每一个索引在 InnoDB 里面对应一棵 B+ 树。

 

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为非聚簇索引(二级索引)

 

基于主键索引和普通索引的查询有什么区别:

主键查询方式直接查找主键索引的这颗B+树，而非主键索引需要先查找非主键索引这颗B+树，拿到对应的主键值再去查找主键索引树（这个过程称为“回表”）

即 基于非主键索引的查询需要多扫描一棵索引树。所以“尽量使用主键查询”

 

索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。

如果插入值大于当前所有值，则只需要在现有的记录后面插入一个新记录。

在中间插入新值有可能引起页分裂（即数据页占满，需要申请一个新的数据页），除了性能还会影响数据页的利用率（原本一个页的数据分到两页，整体空间利用率下降50%）

（有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。）

 

基于上面的索引维护过程说明，我们来讨论：

哪些场景下应该使用自增主键，而哪些场景下不应该

 

我们知道，自增主键的插入数据模式是递增插入，正符合了我们前面提到的插入值大于当前所有值的场景。

每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

 

而业务逻辑字段做主键，则往往不容易保证有序插入，写数据成本相对较高。

 

 

除了考虑性能外，我们还可以从存储空间的角度来看。假设表中确实有一个唯一字段，比如身份证号，那应该用身份证号用自增字段做主键呢？

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，

而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。

 

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

从性能和存储空间方面考量，自增主键往往是更合理的选择

 

什么场景适合用业务字段直接做主键：

1. 只有一个索引；

2.该索引必须是唯一索引。

 

即典型的k-v场景

由于没有其他索引，所以不用考虑其他索引的叶子节点大小的问题。

另外优先考虑“尽量使用主键查询”原则，直接将这个索引设置为主键，减少回表操作

 

 

覆盖索引：

举个例子：

初始化一个表

create table T (ID int primary key,k int NOT NULL DEFAULT 0, s varchar(16) NOT NULL DEFAULT '',index k(k))engine=InnoDB;

插入一些数据

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

一个问题：

如果执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？

答：

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，不满足条件，循环结束。

这里需要注意的是，在引擎内部使用覆盖索引在索引 k 上其实读了三个记录（k=3,5,6），

但是对于 MySQL 的 Server 层来说，它就是找引擎拿到了两条记录，因此 MySQL 认为扫描行数是 2。

 

这个过程中回表了两次，于是我们会有一个思路：是否可以通过索引优化，减少甚至避免回表过程呢？

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。

这个查询里面，k索引树  已经“覆盖了”我们的查询需求，我们称为覆盖索引。

 

覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

 

基于上面对索引覆盖的说明，我们再讨论一个场景：

问题：在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？

市民表如下：

CREATE TABLE `tuser` (
  `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

身份证号是市民的唯一标识。即如果有根据身份证号查询市民信息的需求，只要在身份证号字段上建立索引就够了。

如果要再建立一个（身份证号、姓名）的联合索引，是不是浪费空间？

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。这个高频请求能用到覆盖索引。

当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

 

 

最左前缀原则:

上面这个例子可能引起我们的另一个疑问：如果为每一种查询都设计一个索引，索引是不是太多了。

如果现在要按照市民的身份证号去查他的家庭地址呢？虽然这个查询需求在业务中出现的概率不高，但总不能让它走全表扫描吧？

反过来说，单独为一个不频繁的请求创建一个（身份证号，地址）的索引又感觉有点浪费。应该怎么做呢？

 

答案：B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录

 

为了直观地说明这个概念，我们用（name，age）这个联合索引来分析。

 

索引项是按照索引定义里面出现的字段顺序排序的。 

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

如果要查的是所有名字第一个字是“张”的人，也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

 

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。

这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

 

基于上面对最左前缀索引的说明，我们来讨论一个问题：在建立联合索引的时候，如何安排索引内的字段顺序。

评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。

因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

所以前面的问题里，要为高频请求创建 (身份证号，姓名）这个联合索引，并用这个索引支持“根据身份证号查询地址”的需求就有明确方案了。

 

如果既有联合查询，又有基于 a、b 各自的查询呢？

查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时不得不维护另外一个索引，也就是需要同时维护 (a,b)、(b) 两个索引。

这时我们要考虑的就是第二原则就是空间了。比如上面name 字段是比 age 字段大的 ，那就创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

 

 

索引下推：

上面讲了最左前缀原则。那么那些不符合最左前缀的部分，会怎么样呢？

我们还是以索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。

那么，SQL 语句是这么写的：

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

按照最左前缀原则，在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3。

当然，这还不错，总比全表扫描要好。接着判断其他条件是否满足：

1. 在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。

2. 而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

图1    图2

 

 图1是无索引下推执行流程，图2是索引下推执行流程

 图1特意去掉了age值，是因为这个过程 InnoDB 并不会去看 age 的值,此时需要回表四次。

 图2因为判断了age值，只需回表2次。

 

从实现层面来讲，是因为MySQL5.6以前，过滤逻辑是放在server层的，这样意味着存储引擎层会把满足“like '张%'”的记录全部返回到server层中，再在server层做处理；

而MySQL5.6开始，过滤逻辑放在了搜索引擎层，这样一来返回给server层的就是已过滤的数据。

 

小结：

1.InnoDB 是索引组织表（表都是根据主键顺序以索引的形式存放的）

2.一般情况建议创建一个自增主键（k-v场景除外）

3.利用覆盖索引，左前缀原则，索引下推可以有效减少回表次数，提升查询性能

 

 

本文参考 极客时间林晓斌老师的《MySQL45讲》，也极力向大家推荐这门课程。

本篇目前算做笔记，后面学习到更多索引相关的知识都会归结到这篇文章来