索引的底层实现及应用和失效场景

简介

 

合适的索引能让查询语句性能得到极大的优化，它只是优化业务的一种方式，不能为了建立索引而建立索引。

---

索引是什么？

索引是一种为了快速查找数据的数据结构，是帮助数据库快速获取数据的 排好序的数据结构 (B+Tree)。

---

索引的好处

假设我们有一个表 t，它有两个字段，col1 和 col2，如下：

1. 不加索引的情况

不加索引的情况下，我们要检索 col2 = 89 的数据，SQL：select * from t where col2 = 89，需要从第一行遍历 6 次，比对 col2 等于 89 的记录才能查到。倘若数据量百万上千万级别的表呢？那查询的速度相当的慢。

1. 加索引的情况

如果 col2 能加索引，如图中二叉树，MySQL 内部维护一个二叉树的数据结构简历索引，那么检索时间将大大减少，查询 col2 = 89 的元素仅仅需要两次磁盘 IO 即可。

这就是索引的好处，它加快了数据库查询的效率。索引使用巧妙的数据结构，利用数据结构的特性来优化查询减少查询遍历的次数。

---

索引底层数据结构的探索

既然索引的底层是利用一些巧妙的数据结构的特性来起到优化查询的，那么应该使用什么样的数据结构呢？又是怎样来维护我们的数据结构呢？

索引可选的数据结构

• 二叉树

• 红黑树

• hash

• B-Tree

MySQL 索引的底层用的并不是二叉树和红黑树，因为二叉树和红黑树在一定的场景下会暴露出一些弊端或者说是缺点：

1. 二叉树退化成链表、数据量大层级高等

2. 红黑树层级过高

二叉树

假设我们把前面的查询 SQL 的条件改成 col1，那么 SQL: select * from t where t.col1 = 6。

假设把 col1 作为索引，底层结构使用二叉树，col1 的数据特点是从上到下依次递增，类似自增主键，在每一行的插入中维护成一个二叉树，我们可以看看这个二叉树维护成什么样子。

Binary Search Tree Visualization

通过这个网站，我们可以发现，这个二叉树一直在单边增长，也就是说二叉树在这种场景下退化成了链表，并没有起到辅助快速查询的作用。

这个时候，加索引和不加索引的效果是一样的，都需要去依次遍历，这就是使用二叉树的弊端。且在数据量大时层级过高，即使使用 avl tree (平衡二叉查找树，带有平衡条件：每个节点的左右子树的高度差的绝对值最多是 1)，在大数据量的情况下，依旧不适用。

红黑树

红黑树也是一种平衡二叉树，JDK 1.8 的 HashMap 就用到了红黑树。

Red/Black Tree Visualization

一次插入 1、2、3、4、5、6、7，可以看到当有单边增长趋势时，红黑树会进行一个自平衡旋转，这时候查询 col1 = 6 时只需要查 3 次即可查到数据，相较于二叉树有了改进。

 

那么 MySQL 为什么不使用红黑树来作为索引的数据结构呢？结论前面已经下了，百万千万级数据量的情况下，层级过高，导致查询效率变慢。

那么能不能做一点改造呢？如上述的结论，层级过高导致查询效率变慢。所以我们要解决的问题是层级过高的问题，如果控制层级不让树的高度增长，又想多存数据，也就是说限制垂直的增长，那就可以考虑横向的增加，从二叉发展到三叉、四叉、...、多叉树，每个节点再分叉，那么同样高度的树存储的元素的数量级更大，这样的改造结果就是 B-Tree。当然 MySQL 使用的不是 B-Tree 而是它的变种 B+Tree。

Hash

MySQL 绝大多数情况下使用的都是 B+Tree ，有些情况下使用 hash（精确查找）。

假如插入一个元素，会把我们的索引字段做一次哈希计算，把运算得到的结果和这一行所在的磁盘地址做一个映射，这样对索引元素进行一次 hash 运算就可以查到对应磁盘地址的数据了，这种查询的速度是相当快的，那为什么大多数场景用 B+Tree 而不用它呢？

hash 不支持范围查找，只适合精确查找的场景！！！！！！

另外 Hash 出现冲突时，InnerDB 用的也类似 HashMap 的解决方案，采用的是拉链法。

B-Tree

• 叶节点具有相同的深度，叶节点的指针为空

• 所有索引元素不重复

• 节点中的数据索引从左到右递增升序

B-Tree 如上结构，也就是说在一个节点上可以存储更多的元素，key 就是索引字段，data 就是索引数据所在的那一行的数据或者那一行数据所在的磁盘地址，去查询的时候一次 load （一此磁盘 IO）一个大节点的数据到内存中，再在内存中去比对。

如果要查找 49 这个元素，实际上是从根节点开始查找，首先将根节点 load 到内存，比较 49 > 15 < 56，15 和 56 之间有一个位置存储是下一个节点的磁盘地址指向，于是将下一个节点 load 到内存，再去找 49 这个元素，即可找到。

按照以上说法，树的高度越小，对于查询越快，那按照这个思路可不可以把一个表的数据都放到内存上，查询直接在内存中比较？

答案是否定的，假如一张表有几百兆甚至几个 G 的数据在磁盘上，要全部放到内存上是不可能的，且内存是有限的资源。

一次磁盘 IO 是非常慢的，所以这个节点大小要设置的合适，不能太大也不能太小，MySQL 对这个节点大小的设置是 16K，可以用 show global status like 'Innodb_page_size' 查询。

 

 为啥设置 16K 呢？为什么不是更大？16M ？

因为 16K 完全够用了，MySQL 使用的也不是 B-Tree 这个数据结构作为索引，而是其变种 B+Tree。

B+Tree(B-Tree 变种)

• 非叶子结点不存储 data，只存储索引（冗余，在叶子节点也存在），可以放更多的索引

• 叶子节点包含所有索引字段

• 叶子节点用指针相连接，提高区间访问的性能（这里根据相关资料，叶子节点包含所有数据且通过指针链接成双向链表）

正规的图例，后续的图脑补

比如要查询 30 这个元素，先把根节点 load 到内存，查找到 30 > 15 < 56，于是加载到 15 - 56 中间存储的节点指向，比较 30 > 20 < 49，如前一个步骤加载到 20 - 49 中的指向的叶子节点，去比对，就 OK 了。

和 B-Tree 有啥区别？

1. 非叶子节点不存储数据

2. 数据都存储在叶子节点

3. 叶子节点之间还有指针相连

为啥 data 元素放到叶子节点？

非叶子结点只存储索引元素，叶子节点存储了一份完整表的所有行的索引字段，data 元素是每个索引元素对应要查找的行记录的位置或行数据，这样非叶子节点能存储更多的索引元素。如上图，15/20/49 使用的是处于中间位置的索引数据。

为什么要把中间元素提取出来冗余呢？

是为了优化查询的效率，使得效率更高，想象一下，B+Tree 不像 B-Tree 上，每个节点都存储元素，B+Tree 只有叶子节点才存储元素，而一个节点大小是 16K，把 data 挪走之后，能存储更多的冗余元素，更多的冗余元素意味着能分出更多的叶子节点，也就意味着能存储更多的元素，并且树的高度也不高，查询速度更快。

16K 的大小节点能存储多少元素呢？

1. 假设索引字段的类型是bigint 8bit，每个元素之间的存的是下一个节点的地址，MySQL 分配的是 6 bit，也就是说一个索引后面配对一个节点地址，成对出现

2. • 8b + 6b = 14bit ，16K / 14b = 1170 个索引

3. 假设叶子节点有索引有 data 元素，占 1K

4. • 那一个节点就放 16K/1K=16 个元素

5. 假设树高是 3，所有节点都放满，能放多少数据？

6. • 1170 * 1170 * 16 = 21902400，2000 万条数据

由上可知 16K 的大小，能存储 2000 万条数据，且树高可控，基于 B+Tree 的特点，一次 load 的数据也不多能保证磁盘 IO 的性能。

所以到这就能解释几千万的表正确的使用索引后，查询能几十毫秒几百毫秒就出数据了，一个两千万的表，查询数据仅仅需要 2 次磁盘 IO 就可以搞定了。

---

MySQL 是如何存储索引和数据的？

 

存储引擎修饰的是表，而不是数据库

MyIsam 存储引擎是 B+树

B+Tree 的叶节点存储的是指向数据所在磁盘行行号/指针。它的主键索引和非主键索引结构是一样的。

 

如上其中数据存储在 MYD 文件中，带箭头的指针和索引信息存储在 MYI 文件中，表结构存储在 sdi 文件中(截图自 Mac)。

1. 通常称 MyIsam 的索引实现为非聚簇索引。

2. 它的查询过程查找到叶子节点后，还需要根据叶子节点存储的文件地址的指针去 MYD 文件中查询到需要的记录。这个步骤称为回表。

InnoDB 存储引擎用的是 B+树

B+Tree 的叶节点存储的是具体数据。它的主键索引和非主键索引结构不同，辅助索引存储的是指向主键索引的指针信息。

如上使用辅助索引搜索数据，查询 SQL: select * from t where k = 700，首先在非主键索引中加载到根节点，再 load 叶子节点，通过叶子节点存储的主键索引的节点指针，找到对应的数据。

1. 表数据文件和索引文件本身按照 B+Tree 存储的，通常 InnoDB 的主键索引被称为聚簇索引

2. 聚簇索引 - 叶子节点包含了完整的数据记录

3. InnoDB 存储引擎必须有主键，且推荐使用整型自增主键

4. 非主键索引的结构的叶子节点存储的是主键值（为了节省空间，也为了一致性考虑，避免维护多份数据）

5. 对比 MyIsam 存储引擎，InnoDB 不需要回表，性能比 MyIsam 高

---

聚簇索引和非聚簇索引

• 聚簇索引：叶子节点包含完整的数据记录

• 非聚簇索引：索引和数据是分开存储的

---

为什么 InnoDB 表必须有主键，并且推荐使用整型自增？

设计需要，它的存储引擎就是通过主键索引来将数据整合成 B+Tree 的。

为什么推荐使用整型自增呢？

1. 可以想象一下查找过程中需要将数据加载到内存中，如果使用 UUID 来作为主键的话，范围查找需要使用 ASCII 一位一位的比较，相较于整型的范围查找来说肯定慢得多。再说存储上 UUID 浪费的空间更多，整型更小更节约空间

2. 自增，可以提高插入性能，避免 页（B+Tree 一个大节点） 的分裂，自增主键会把数据自动向后插入，避免插入过程中的聚簇索引的排序问题。聚簇索引的排序必然带来大范围的数据物理移动，带来较大的磁盘 IO 的损耗

为什么没有建立主键创建表也可以成功呢？

不建立主键不代表没有主键，没有建立主键的表，存储引擎默认会选定一个数据不重复的字段作为唯一索引来维护整个表的数据，如果没有那么会生成一个唯一列，类似 rowID，用这个唯一列来维护 B+Tree 的结构。

---

联合索引

尽量建立联合索引，少建立单值索引。多个索引会维护多个 B+ 树，非主键索引的子节点存储的是指向主键索引的指针。

联合索引的好处（InnoDB）

1. 减少建立索引的开销。建立一个 (a, b, c) 的联合索引相当于建立了 (a)、(a, b)、(a, b, c) 三个索引，上面提到多个索引会维护多个 B+ 树，会增加写操作的开销和磁盘空间的开销。

2. 索引覆盖。同样的符合索引 (a, b, c)，如果有这样的查询：select a,b,c from table where a = 1 and b = 1。那么 MySQL 可以直接遍历辅助索引取出数据，而无须再去主键索引查询数据。覆盖索引是主要提升性能的优化手段之一。

3. 缩小查询范围。有 1000w 条数据的表，select * from table where a = 1 and b = 1 and c = 1，假设每个条件可以筛选出 10% 的数据，如果使用单值索引，那么通过索引能筛选出来的数据就是 1000w * 10% = 100w 条数据，然后再去主键索引去这 100w 条数据中筛选 b = 1 and c = 1 的数据；如果使用联合索引，通过筛选后的数据就是 1000w * 10% * 10% * 10% = 1w，你觉得谁的查询范围小，谁快呢？

最左匹配原则

所谓最左匹配原则就是说如果你用到了联合索引的最左边的索引列，那么这个 SQL 就可以利用这个联合索引去匹配，需要注意的是，当出现范围查询的时候就会停止匹配（>、<）

2023年6月1日 ---> between、>=、<= 不会停止匹配，索引：[q_id, p_id] 都为bigint 占字节 8 位，可以看到 key_len 为 16

 

假如我们建立一个联合索引 (a, b, c) ，当 where 条件写成

a = 1 a = 1 and b = 1 a = 1 and b = 1 and c = 1

就可以匹配索引，但是如果写成

b = 1 and a = 1 and c = 1 c = 1 and b = 1 and a = 1

也可以匹配到索引，没错，因为 MySQL 查询优化器会帮你把条件自动调整为与索引的顺序一致（也可以理解为查询优化器会优化你的 SQL 成它认为正确的）。相反你若写成 b = 2 就匹配不到索引了。这就是最左匹配原则，因为在 a 不确定的情况下，b 不是有序的 (下面原理解释)。还有如果 where 条件中出现了范围查找那么在范围查找后面的一个索引字段将不会使用索引。

a = 1 and b > 1 and c = 1

如上 c 将不会走索引，因为遇到了范围查找。但是如果写成

a = 1 and c > 1 and b = 1

这样 a b 字段使用索引，c 不使用索引，这是因为查询优化器会自动优化调整为和索引的顺序保持一致。

联合索引的结构

索引的底层是一颗 B+Tree ，而构建一个 B+Tree 只能根据一个值来确定索引关系，所以数据库依赖联合索引的最左侧的字段来构建这颗辅助索引树。

比如创建一个 (a, b) 的联合索引，可以看到 a 的值是按顺序来排列的，而 b 的值并没有按照顺序排列，但是可以看到在 a 确定的情况下，b 又是按照顺序排序的，这是因为 MySQL 创建联合索引的规则首先会对联合索引最左侧的字段排序，在第一个字段的顺序的前提下对第二个字段排序，所以上面的 b = 2 这种查询条件无法利用索引。

 

---

关于碰到范围查找为什么会停止匹配？

如上图，当 a 的值是确定的，b 是有序的

a = 1 b = 1, 2

倘若 a 的值是个范围查找

a > 1 b = 1, 4, 1, 2

可以发现 b 的值没有顺序，因此 b 不能用上索引。

explain 参数详解

explain 只能解释 select 查询

参数解释

id

• id 代表执行 select 子句或操作表达式顺序的优先级

• • id 相同，执行顺序从上往下

  • id 不同，如果是子查询，id 序号会递增，id 越大优先级越高，越先被执行

  • id 既有相同又有不同(1, 1, 2)，优先级高的先执行，相同层级的从上往下顺序执行

select_type

• select_type 代表查询类型，用于区分普通查询、联合查询、子查询等

• • SIMPLE 代表简单的查询语句

  • PRIMARY 查询包含复杂子查询，最外层查询则被标记为 PRIMARY

  • SUBQUERY 代表 select 或者 where 中有子查询

  • DERIVED 代表 from 列表中包含的子查询被标记为 DERIVED，MySQL 会递归这些子查询，并将结果集放在临时表中

  • UNION ，第二个查询语句出现在 UNION 后面则被标记为 UNION，若 UNION 包含在 from 子句的子查询中，外层的 select 将被标记为 derived

  • UNION RESULT，从 union 表中获取 select 的结果

type

• table 显示查询的数据是关于哪张表的

• type 表示查询的类型，从好到差依次为，system > const > eq_ref > ref > range > index > ALL，一般情况下，至少要保证达到 range 级别，最好达到 ref 级别。

• • system，表只有一行记录，这是 const 类型的特例，平时不会出现

  • const，即常数，通过索引一次就能找到

• eq_ref，唯一性索引扫描，对于索引字段的值，表中只有一条记录与之对应，比如主键索引还有唯一索引的扫描

• ref，非唯一性索引扫描，可能返回多条记录

• range，只需要检索固定范围内的数据，使用一个索引来选择行，比如常见的 between、>、<、in 等语句，这种范围查找比全表扫描要好得多，因为只需要匹配索引范围内的数据即可，不用扫描全表

• index，只遍历索引树，通常比全表扫描要快，因为索引树的大小通常比叶子节点的全部数据要小

• ALL，全表扫描，最差的一种查询方式

possible_keys

• possible_keys（possible: 可能的） 表示查询中可能使用到的索引

key

• key 查询中实际用到的索引，如果 key 等于 null 说明压根没有用到索引，因此，可能出现 possible_keys 有值但是 key  没有值的情况

key_len

• key_len， 表示索引中使用的字节数，而通过该列计算查询中使用的索引长度，在不丢失精度的情况下，长度越短越好，key_len 显示的值为索引字段的最大可能长度，并非实际使用的长度，即，key_len 是根据表定义计算的，而不是通过表检索得到的

ref

• ref，显示索引的哪一列被使用了，如果可能的话是一个 const

extra

• Extra

• • Using filesort：mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取

  • Using temporary：使用了临时表保存中间结果，mysql对查询结果排序时使用临时表，常见于order by和group by

  • Using index：表示相应的 select 操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现 Using where，表示索引被用来执行索引键值的查询；如果没有同时出现 Using where，表示索引用来读取数据而非执行查找。

  • Using where：表示使用了 where 过滤

  • Using join buffer：表示使用了连接缓存，如在查询的时候有多次 join，则可能会产生临时表

  • impossible where：表示 where 子句的值总是false，不能用来获取任何数据

  • select tables optimized away：在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

  • distinct：优化 distinct 操作，在查找第一匹配的数据后停止找同样值的动作。

---

索引的使用

常用的题型介绍

题型一

如果 SQL 如下，如何建立索引？

select * from table where a = 1 and b = 2 and c = 2;

• (a, b, c)

• (c, b, a)

• (b, c, a)

以上都可，重点是实际业务中，最左侧的字段应该放易于区分的字段，区分度低的应该放后面，比如性别、状态等。

题型二

如果 SQL 如下，如何建立索引？

select * from table where a > 1 and b = 2;

• (b, a)

注意，如果建立(a, b) 那么只有 a 用上索引，因为范围查找之后的字段不会用上索引，而创建 (b, a) 的联合索引，两个字段都能用上索引。

题型三

如果 SQL 如下如何创建索引？

select * from table where a > 1 and b = 2 and c > 3;

• (b, a)

• (b, c)

如果

select * from table where a = 1 and b = 2 and c > 3;

• (a, b, c)

• (b, a, c)

题型四

如果 SQL 如下如何创建索引？

select * from table where a = 1 order by b;

• (a, b)

这里创建 (a, b) 联合索引，在 a 固定的情况下，b 相对有序，可以避免再次排序！！

如果

select * from table where a > 1 order by b;

• (a)

这里 a 是一个范围查询，b 在 a 不固定的情况下，没有顺序，所以建立单值索引即可，没必要创建联合索引

再如果

select * from table where a = 1 and b = 2 and c > 3 order by c;

• (a, b, c)

• (b, a, c)

这里 c 做范围查询，a/b 固定，c 相对有序，无需再次排序，仅做范围匹配即可

题型五

如果 SQL 如下如何创建索引？

select * from table where a in (1, 2, 3) and b > 1;

• (a, b)

因为 in 在这里可以认为是等值匹配，并不会终止索引匹配

如果

select * from table from a = 1 and b in (1, 2, 3) and c > 3 order by c;

• (a, b, c)

• (b, a, c)

---

索引失效的场景

所有脱离版本号的优化，都是扯淡。不同版本号下的差异也是明显的。

信息说明

• MySQL 8.0

• 表 table

• 字段 a/b

• 索引 a

• varchar

• not null

索引失效是不确定的

like 语句，左边使用 “%”

-- 无法使用索引 explain select * from table where a like '%index' -- 可以使用索引 explain select * from table where a like 'index%'

隐式类型转换，索引字段和条件字段类型不一致

• varchar -> int

• int -> varchar 不受影响

-- 无法使用索引 explain select * from table where a = 1; -- 可以使用索引 explain select * from table where a = '1';

条件中对索引进行运算或者使用函数

-- 无法使用索引 explain select * from table where substr(a, 1) = '1'; -- 可以使用索引 explain select * from table where a = 1 + 1;

使用 OR，且条件中有非索引列

-- 无法使用索引 explain select * from table where a > 1 OR b = '1';

使用 OR 时，OR 包含的所有列必须都是独立索引才有可能用到索引

使用 NOT IN、IN、IS NULL 且返回值中不止包含条件索引列

• 部分情况下可以使用索引

• • 当表里没有数据时不使用索引

  • 优化器会根据数据的总量来选择是否走索引，这里测试 10 条数据，查询出 8 条就会走索引，超过就全表扫描

explain select * from table where a in ('1'); explain select * from table where a not in ('1'); explain select * from table where a is null; explain select * from table where a is not null;

MySQL 环境变量 eq_range_index_five_limit 的值对 in 语法影响很大，该参数表示使用索引情况下最大的数量。MySQL 5.7.3 以及之前的版本默认值是 10。之后的版本 为 200。

1. 当 in 查询的数量大于 200 这个查询一定不会走索引。

2. 当 in 查询的数量小于 200 时可能用到索引，这个要看查询优化器的选择。

不可为空索引列使用 is not null，仅当只查询这个列时才会使用索引

-- 无法使用索引 explain select * from table where a is not null; -- 可以使用索引 explain select a from table where a is not null;

总结

1. MySQL 优化器的不同选择可能导致不同的结果，同一条语句，数据量不同、筛选率不同等等原因都可能导致失效。

2. 所有简单查询，只要 where 条件列中包含了索引列，且返回值中包含了该索引列，都会用到索引。根据执行计划中的 extra 可以区分索引的用途：

   1. extra = Using index 表示覆盖索引

   2. extra = Using index, Using where 表示存在回表操作

---

拓展

为什么只要返回值只包含索引和主键就会用到索引？

众所周知，InnoDB保存数据是通过 B+ 树结构存储的。且只有主键索引所在的 B+ 树的叶子节点会保存实际数据，其他辅助索引只保存指向主键索引的指针，这种数据与索引在一起的索引我们称之为聚簇索引。

所以当我们通过辅助索引查询数据时，第一步先通过辅助索引查询到对应的指针；再通过指针到主键索引中查询对应的实际数据，这个过程我们称之为回表。

而回表操作是随机 IO，所以性能较差，当需要回表的数据量比较大时，优化器可能就会选择不走索引，直接全表扫描，因为走全表是顺序IO，指不定走全表比走索引还快。 (这也解释了为什么同样的SQL，表数据不同查询策略也不同)

其中一个特殊情况是当我们的查询只涉及到索引列和主键的时候，我们就不需要再回表查询实际数据了，因为辅助索引中保存了索引列的数据，这个时候就肯定会走索引了。

---

强制指定索引

explain select * from table force index (table_idx_a_index) where a between 1 and 2;

---