Contents

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

索引下推

官网地址:Index Condition Pushdown Optimization

个人理解:

  • 开启ICP后,在查询索引时,会先检查无效索引(未走上的索引)是否满足条件,减少回表查询的次数,从而节省了查询 满足有效索引列条件下的 不符合无效索引列条件的所有行的查询时间。
https://static.duan1v.top/images/ugxmNVEaz8UcbJv.png
MySQL学习笔记:组合索引-索引下推
  • 很明显,索引没有走到uname
  • 在关闭ICP时,MySQL会回表查询`uname`!='5f686d59875ef'这个条件的完整行
  • 在开启ICP时,MySQL会先检查索引中的`uname`,然后在进行全表查询

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

1
2
3
4
5
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`
https://static.duan1v.top/images/Hbf6lrKLCOEsxGW.png
MySQL学习笔记:组合索引-索引下推
https://static.duan1v.top/images/wYDvVBx32I7LTbp.png
MySQL学习笔记:组合索引-索引下推
  • 再在开启关闭的情况下,分别执行三次SQL,查看查询时间;开启后比关闭后节省了大概75%的时间
https://static.duan1v.top/images/a3kwIErRglPn9UY.png
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
https://static.duan1v.top/images/VqpiY7aMRfk2HSU.png
MySQL学习笔记:组合索引-索引下推

索引覆盖

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

https://static.duan1v.top/images/JKR6epXSnsHN8WY.png
MySQL学习笔记:组合索引-索引下推
coffee