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

索引下推

官网地址：Index Condition Pushdown Optimization

个人理解：

• 开启ICP后，在查询索引时，会先检查无效索引（未走上的索引）是否满足条件，减少回表查询的次数，从而节省了查询 满足有效索引列条件下的 不符合无效索引列条件的所有行的查询时间。

• 很明显，索引没有走到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`

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

ICP注意事项：

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

EXPLAIN中的type是range：

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

索引覆盖

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