MySQL学习笔记:组合索引-索引下推

索引下推

官网地址:Index Condition Pushdown Optimization

个人理解:

  • 开启ICP后,在查询索引时,会先检查无效索引(未走上的索引)是否满足条件,减少回表查询的次数,从而节省了查询 满足有效索引列条件下的 不符合无效索引列条件的所有行的查询时间。
    MySQL学习笔记:组合索引-索引下推

  • 很明显,索引没有走到uname

    • 在关闭ICP时,MySQL会回表查询`uname`!='5f686d59875ef'这个条件的完整行
    • 在开启ICP时,MySQL会先检查索引中的`uname`,然后在进行全表查询

实际查询,对比开启和关闭ICP时所用时间

SET profiling =  1; -- 开启profiling
SET query_cache_type =  0; -- 关闭query_cache
SET GLOBAL query_cache_size =  0;
SET optimizer_switch = 'index_condition_pushdown=off'; -- 关闭ICP
SET optimizer_switch = 'index_condition_pushdown=on'; -- 开启ICP
  • 先看看EXPLAIN,有效索引列:`dept_id`,无效索引列:`uname`

MySQL学习笔记:组合索引-索引下推

MySQL学习笔记:组合索引-索引下推

  • 再在开启关闭的情况下,分别执行三次SQL,查看查询时间;开启后比关闭后节省了大概75%的时间

MySQL学习笔记:组合索引-索引下推

ICP注意事项:

  • EXPLAIN中的type须是range, ref, eq_ref, ref_or_null之一
  • 支持引擎:Myisam,InnoDB,允许分区
  • 对于InnoDB,不支持聚簇索引,只支持二级索引
  • 不支持虚拟列,子查询,存储函数,触发条件
  • 索引覆盖时,不用ICP

EXPLAIN中的type是range:

  • EXPLAIN中,type为range时,MySQL会根据符合有效索引列条件的计算rows(InnoDB是估值),如果过大,即使用索引不划算,将不走索引,从而不使用ICP

MySQL学习笔记:组合索引-索引下推

索引覆盖

SELECT子句中查询的字段只有索引列,因此不需要根据二级索引查到的聚簇索引,再次回表查询完整行。

MySQL学习笔记:组合索引-索引下推

本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!