MySQL索引下推

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

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

注:联合索引,又称符合索引,英文为 Multiple-Column IndexesComposite Indexes

联合索引存储原理

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

查询 SQL 如下:

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:

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 时的查询
  • 开启 ICP 时的查询,仅图中「红色箭头」标识部分发生回表,减少 IO 操作

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

我来评几句
登录后评论

已发表评论数()

相关站点

+订阅
热门文章