跳至主要內容

索引覆盖和索引条件下推

AruNi_Lu数据库MySQL约 2286 字大约 8 分钟
本文内容

前言

讲到索引优化的方式时，一般都会有索引覆盖和索引条件下推，那么这两个到底是什么？又是如何提高查询效率的？它们又有什么区别？

1. 索引覆盖

1.1 什么是索引覆盖

索引覆盖针对的是 二级索引（辅助索引），指 从辅助索引中就可以得到查询所需的字段，而不需要通过回表去聚簇索引中查询。

因为 辅助索引中只包含索引列和主键，所以其一条记录占用的大小要远远小于聚簇索引，那么 一页中就可以保存更多条记录，因此可以 减少大量的 IO 操作（磁盘扫描区间小）。

如果优化器进行了索引覆盖，那么在执行计划中的 Extra 列就会显示 Using index。

1.2 案例

Demo 表的设计很简单，如下：

CREATE TABLE `idx_demo`  (
  `id` int(0),
  `a` int(0),
  `b` int(0),
  `c` int(0),
  PRIMARY KEY (`id`),
  INDEX `idx_a_b`(`a`, `b`)
)
  • 主键索引：id；
  • 联合索引 idx_a_b：a, b；

下面会使用到 explain 的知识，如果你还不知道什么是 explain，建议先看 执行计划之 explainopen in new window

SQL 语句一：

explain select * from idx_demo;

image-20230216175151418

毫无疑问，使用了全表扫描。因为我们要查询所有的字段，走联合索引的话没有字段 c，是需要回表的。

SQL 语句二：

explain select id, b from idx_demo;

image-20230216175504841

可以发现，虽然 possible_keys 为 Null，但是实际上使用到了联合索引 idx_a_b。因为我们要查询的字段 id 和 b 都在联合索引中，扫描辅助索引效率更高，所以使用到了索引覆盖，也可以看见 Extra 列有 Using index。

SQL 语句三：

explain select id, b from idx_demo where a like '%2%';

image-20230216180257503

可能乍一看 like '%2%' 会认为索引失效了，需要走全表扫描。但是，我们要查询的字段可以用到索引覆盖，所以还是使用了联合索引 idx_a_b。

可以看到 type 列为 index，即全索引扫描，虽然此扫描方式效率也较低，但是还是比全表扫描好。

因此我们在判断一条查询语句走没走索引时，不要只顾及索引生不生效，还要多注意索引覆盖的情况。

2. 索引条件下推

2.1 什么是索引条件下推

索引条件下推（ICP：Index Condition Pushdown）是 MySQL 5.6 开始支持的，它也是针对 二级索引（辅助索引）的。

ICP 的提出只是为了 尽量减少二级索引在查询时回表的次数，从而减少 IO 操作。而聚簇索引不需要回表，它本身就包含所有的字段，所以 MySQL 规定 ICP 只适用于二级索引。

如果使用了 ICP，那么在执行计划中的 Extra 列就会显示 Using index condition。

下面通过例子来说明 ICP 是如何减少回表次数的，看完了下面的例子，你也就明白了什么是 ICP。

同样使用上面索引覆盖的 Demo 表，该表只有一个主键索引和一个联合索引 idx_a_b (a, b)。

我们的示例 SQL 如下：

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

联合索引遇到范围查询会停止匹配，所以上面的语句中只有 a 可以用到联合索引，b 是无法走联合索引的。

因为该联合索引是优先根据 a 排序，只有 a 相同时才根据 b 排序，所以当 a 有序时，b 不一定有序。

MySQL 是分为 Server 层和存储引擎层的。在 没有 ICP 时，优化器生成执行计划后，Server 层和存储引擎层是这样查询的：

  1. Server 层调用存储引擎的接口，以获取记录；

  2. 存储引擎定位到满足 a > 1 的第一条二级索引记录后，根据该二级索引记录的主键值进行回表，将完整的记录返回给 Server 层；

    注意：定位到记录后，如果没有 ICP，b 字段又无法走索引，所以会直接先进行回表，我们需要查询全部记录。

  3. Server 层再判断其他搜索条件是否成立（即 b = 2），如果成立则发给客户端，否则跳过该记录，然后向存储引擎层要下一条记录；

  4. 获取到下一条记录后，又要先执行回表，然后 Server 层再判断搜索条件；

  5. 如此重复，直到将 a > 1 的记录都读取完；

从上面的步骤可以看出，每次都需要回表后，才能进行搜索条件的判断。但是我们的 搜索条件 a > 1 和 b = 2 都是包含在联合索引里面的，所以，MySQL 对上面的执行步骤进行了改进：

  1. Server 层调用存储引擎的接口，以获取记录；

  2. 存储引擎定位到满足 a > 1 的第一条二级索引记录后，不着急执行回表，而是 先判断关于联合索引 idx_a_b 中包含的列的条件是否满足，也就是 a > 1 and b = 2（a，b 都是联合索引 idx_a_b 中的列）：

    • 如果条件 不成立，则直接 跳过该二级索引记录，然后去寻找下一条记录；
    • 如果条件 成立，才执行回表，将完整的记录返回给 Server 层（此时 Server 层就不需要判断联合索引中包含的列的条件了）。

  3. Server 层再判断其他搜索条件是否成立（本例中没有其他条件了），如果成立则发给客户端，否则跳过该记录，然后向存储引擎层要下一条记录；

    注意：有了 ICP 后，Server 只需要判断除联合索引列之外的列条件。

  4. 如此重复，直到将 a > 1 的记录都读取完；

可以发现，有了 ICP 后，即使联合索引的 b 列无法使用到联合索引，但是它包含在联合索引里面，所以直接在存储引擎层就先进行判断了，满足后才执行回表操作，否则就直接寻找下一条记录。所以 减少了很多回表次数。

一句话总结 ICP：二级索引查询时，存储引擎层定位到一条记录后，就会顺便判断 属于该二级索引的列的搜索条件 是否满足，不满足则直接取下一条记录。

也就是说，没有 ICP 时，即使是属于该二级索引的列不满足搜索条件时，也需要进行回表。不过如果判断条件中还有其他不属于该二级索引的列时，也是需要回表后再在 Server 层进行判断的。

2.2 案例

还是使用上面的 Demo 表，用一个例子来看看你到底掌握 ICP 没有。

SQL 语句：explain select * from idx_demo where a > 1 and b like '%2%' and c > 2;

image-20230216194115231

搜索条件 a > 1 用来形成扫描区间 (1, +∞)，b 无法走联合索引，c 字段不在联合索引中，也无法走联合索引。

存储引擎在定位到一条记录后，会先判断属于联合索引的列的搜索条件是否满足（即 a > 1 and b like '%2%'），满足后才会进行回表，然后将完整记录返回 Server 层，由于本例中还有一个搜索条件 c > 2 不在联合索引中，所以 Server 层还需要判断一下，所以看到 Extra 中还有 Using where（某个搜索条件需要到 Server 层进行判断）。

虽然上面的例子走联合索引时，在存储引擎判断了搜索条件后，在 Server 层还要判断，但是 ICP 让很多联合索引列不满足搜索条件的记录无需回表，大大增加了效率。

3. 总结

索引覆盖和 ICP 都是针对 二级索引 的，而且都能有效 减少回表次数。

但是它们也有区别：

  • 索引覆盖主要看 查询的字段，即 select 后面的字段。主要通过 查询字段在联合索引中就存在 来减少回表；
  • 而 ICP 主要看 搜索条件，即 where 后面的条件。主要通过 搜索条件在联合索引中就存在 来减少回表；
  • 使用了索引覆盖执行计划的 Extra 列会显示 Using index；而 ICP 则是 Using index condition。

4. 参考文章

  • 《MySQL 是怎样运行的》
  • 《MySQL 技术内幕：InnoDB 存储引擎》
上次编辑于: