看 MySQL 的执行计划,如果 Extra 中只有 using index 则说明使用了覆盖索引查询,如果 Extra 中出现了 using indexcondition 或 using index & using where 则说明进行了回表查询
ICP
Index Condition Pushdown,MySQL 5.6 中引入的一种优化策略
那么究竟是将什么从哪 Push Down 到哪,优化了什么?要弄清楚这 4 个问题,我们需要先弄清楚 where 条件的提取与应用,具体可查看:神奇的 SQL 之 WHERE 条件的提取与应用
where 条件会被提取成 3 部分: Index Key, Index Filter, Table Filter ,在 MySQL 5.6 之前,并不区分 Index Filter 与 Table Filter,统统将 Index First Key 与 Index Last Key 范围内的索引记录,回表读取完整记录,然后返回给 MySQL Server 层进行过滤,而在 MySQL 5.6 之后,Index Filter 与 Table Filter 分离,Index Filter 下降到引擎层(InnoDB和MyISAM)的索引层面进行过滤,减少了回表与返回 MySQL Server 层的记录交互开销,提高了 SQL 的执行效率
ICP 优化过程
假设我们有表: tbl_icp
create table tbl_icp (a int primary key, b int, c int, d int, e varchar(50));create index idx_bcd on tbl_icp(b, c, d);insert into tbl_icp values (4,3,1,1,'a');insert into tbl_icp values (1,1,1,2,'d');insert into tbl_icp values (8,8,7,8,'h');insert into tbl_icp values (2,2,1,2,'g');insert into tbl_icp values (5,2,2,5,'e');insert into tbl_icp values (3,3,2,1,'c');insert into tbl_icp values (7,4,0,5,'b');insert into tbl_icp values (6,5,2,4,'f');
没有使用 ICP 时,引擎层会将满足 Index Key 范围限制的所有数据记录(示例中一共 6 条)逐条返回给 Server 层,然后由 server 层应用 Index Filter 和 Table Filter (MySQL 5.6 之前不区分 Index Filter 和 Table Filter),最后将满足条件的数据返回给客户端;
使用了 ICP,Server 层会将 Index Filter 下推到引擎层,引擎层在对 Index First Key 与 Index Last Key 范围内的索引项逐条进行过滤的时候,会应用上 Index Filter,对不满足 Index Filter 条件的索引项直接过滤掉,无需回表操作,也无需返回给 Server 层,从而提供执行效率;上图中的索引项: 3 1 1 、 3 2 1 不满足 Index Filter 中的 d != 1 , 4 0 5 不满足 c > 0 ,所以这 3 个索引项无需进行回表操作,也不需要返回给 Server 层