Mysql可以分为Server层和存储引擎层
所以,最终进行I/O的是存储引擎对文件系统进行I/O操作
索引下推(Index Condition Pushdown,简称 ICP)
对应InnoDB,索引下推适用于非聚簇索引(二级索引)。
当禁用ICP时,如果使用了索引a,b进行查询,Server会先使用a通过存储引擎去查索引B+树,通过索引拿到一个主键,这时候Server会使用这一个主键去回表再去查数据,所以每次都要回表,I/O频繁,大大降低了性能。然后Server层根据查回来的数据再使用索引b进行一次过滤,最终返回数据。
当启用ICP时,如果使用了索引a,b进行查询,Server会使用a和b通过存储引擎去查B+树,查到a的子节点,同时在a的子节点查符合b的主键,然后直接拿到同时符合条件a和b的主键,然后Server层进行一次回表查询,就可以直接返回数据。
补充:上述情况的前提是在where a = 1 and b like ''的情况,因为当b使用like的时候,会造成索引失效,所以最终在Server层进行过滤。但是如果开启了索引下推,引擎拿到符合a的所有主键和索引b的值(注意直接拿的叶子节点的数据,这里叶子节点会存放索引a,b和主键),然后对b的值进行过滤,最终用过滤之后的主键去回表查数据。注意,每一个主键就会发生一次回表查询。
疑问:索引下推是适用于二级索引,那未开启ICP,且联合索引(a,b,c)的情况,联合索引同时使用a,b去查的话,是使用a查回来的所有主键去回表,然后Server过滤 ,还是用同时满足a和b的主键去回表查数据呢?
解答:联合索引使用a,b去查的时候,会直接在索引B+树中查到符合a和b的主键,最后去回表查询。而索引下推是在where 第一个条件使用索引,后面条件,是索引,但是索引失效的情况,去使用索引下推在引擎层对索引b进行过滤,最终使用过滤后的主键去回表,这样可以大大减少回表的次数和I/O次数。