MySQL索引下推

MySQL 联合索引仅支持按「最左匹配」原则使用索引。在遇到范围查询情况时，会停止利用后面的索引字段。

本文针对这一问题对联合索引原理进行说明，并引出官方对这种情况下的优化方案：ICP 索引下推机制。

注：联合索引，又称复合索引，英文为Multiple-Column Indexes或Composite Indexes

联合索引存储原理

multiColumnIndex

index (a,b)的联合索引结构如上图（出自《MySQL技术内幕-InnoDB存储引擎》），观察到每个节点中均同时包含a,b两个字段信息，且字段a全局有序，字段b局部有序（仅在字段a值相同时，字段b是有序的）。

查询 SQL 如下：

1
2
3
select * from xxx
where a>1
and b=2

此时，查询过程如下：

  • 根据a>1定位到最小值(2,1)，最大值为∞
    • 这一过程，利用Index Filter
  • 此时 a 有2、3两种情况，也就是二者都有可能包含b=2的记录
  • 所以只能遍历a>1下的所有索引，才能确定b=2的记录项有哪些：
    • 这一过程因为字段b局部有序，非全局有序，必须遍历所有索引
    • 所以这一操作并未充分利用到索引的特性
    • 这也就是我们常说的，最左匹配，遇到非等值判断时匹配停止
  • 在未开启 ICP 的情况下，存储引擎并未利用索引上的 b 值进行判断。而是进行回表查询，将a>1的所有数据读出、返回至 MySQL Server 层，由 Server 通过Using where根据b=2筛选目标记录。
    • 这一过程，利用Table Filter

可见，未开启 ICP 时，不能完全利用索引树及索引上存储的信息，而是愚笨的通过回表取数据（b 字段数据已在索引树上存在），通过Using where进行数据过滤。
显著的改进：利用索引树上的字段信息，进行查找过滤，减少回表IO数据。

索引下推 ICP 原理

看下官方的这个例子：
联合索引包含zipcode, lastname, firstname三个字段，查询下述 SQL：

1
2
3
4
5
INDEX (zipcode, lastname, firstname)
SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

在未开启 ICP 优化时，按「最左匹配原则」，上述查询 SQL 仅能利用联合索引中的zipcode 字段，剩余lastname和address仅能回表后通过 where 过滤数据。此时通过 explain 查看执行计划，Extra 字段为Using where。
我们通常认为，这种情况下并未充分发挥索引的利用率，因为所需三字段信息在索引树上均包含全部信息，但却仅利用了索引树上的zipcode一个字段；其他两个字段是通过回表后，过滤的数据。
既然，索引树已包含全部三个字段的信息，那为何不直接通过索引树的三个字段来完成查询，避免发生回表呢？原因是，联合索引原理中只能保证局部有序，一旦有非等值查询后，后续字段无法直接通过索引树确定范围。

但是，索引树上已包含所有字段信息，是否可在回表前进行过滤，确定或减少回表数据范围呢？
答案是可以的，这也是 MySQL 在5.6版本后加入的功能。

  • 未开启 ICP 时的查询
    index-access-2phases
  • 开启 ICP 时的查询，仅图中「红色箭头」+「对勾√」标识部分发生回表，减少 IO 操作
    index-access-with-icp

开启 ICP，查看执行计划时，Extra 字段会有Using index condition说明，表示 ICP 生效，减少了回表数据。这会改善 IO 操作数，提升处理效率。

ICP 注意事项

  • ICP 适用于range, ref, eq_ref, and ref_or_null的回表操作前过滤数据
  • 支持InnoDB和MyISAM引擎
  • ICP 目的是减少回表读操作数（reduce the number of full-row reads），从而减少 I/O 操作
  • InnoDB中 ICP 仅支持二级索引，不支持聚簇索引。因InnoDB引擎下，聚簇索引的字段信息已全部在索引中。
  • 指向子查询的查询条件无法利用 ICP
  • 函数或触发器无法利用 ICP

参考文献