MySQL索引下推

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

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

注:联合索引,又称复合索引,英文为Multiple-Column IndexesComposite 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 有23两种情况,也就是二者都有可能包含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 字段,剩余lastnameaddress仅能回表后通过 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的回表操作前过滤数据
  • 支持InnoDBMyISAM引擎
  • ICP 目的是减少回表读操作数(reduce the number of full-row reads),从而减少 I/O 操作
  • InnoDB中 ICP 仅支持二级索引,不支持聚簇索引。因InnoDB引擎下,聚簇索引的字段信息已全部在索引中。
  • 指向子查询的查询条件无法利用 ICP
  • 函数或触发器无法利用 ICP

参考文献