mysql 索引下推+索引合并

一、索引下推

        MySQL索引下推（Index Condition Pushdown，简称ICP）是MySQL在5.6及以上版本中引入的一个优化器特性，用于在存储引擎层面减少回表次数，进而提高查询性能。

        当你的查询中使用了复合索引，但在过滤条件中并不是使用索引的第一个字段，或者不是一个范围查询时，MySQL优化器可以利用索引下推来进一步减少回表次数。简单来说，就是让存储引擎在索引中过滤掉尽可能多的行，而不是仅仅停留在索引叶子节点上。

        在MYSQL5.6之前，这个sql只能在联合索引中匹配到name like 'LiLei%' 开头的索引，然后拿着叶子结点的主键一个个回表，到主键索引去查找相应的记录，再去对比age和position的两个字段的值是否符合。整个过程回表多次。

        在MYSQL5.6之后引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段做判断,过滤掉不符合条件的记录之后再回表，整个过程是只回表一次。有效减少回表次数。

        使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过 滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

        对于innodb引擎，索引下推只能用在二级索引，也就是非主键索引。主键索引的叶子节点上保留的是完整的数据，无需回表，索引下推不会起到减少查询全行数据的效果。

        explain 中extra列为 using index condition时，就是表示使用了索引下推。

关闭索引下推命令

set optimizer_switch='index_condition_pushdown=off';

二、索引合并

        MySQL在5.0版本加入了索引合并优化（Index Merge），索引合并可以同时查询多个索引的范围扫描，并将其结果合并为一个。索引合并只可以合并来自单个表的索引扫描，而不支持跨多个表的扫描。合并可以生成其基础扫描的并集（unions）、交集（intersections）或交集的并集（unions-of-intersections）。

        简单点说，索引合并，可以让一条SQL使用多个索引。然后对这些索引取交集、并集、交集的并集，从而减少读表次数，提高查询效率。

怎么确定使用了索引合并

如果SQL使用了索引合并，在执行计划explain输出中，type列会显示index_merge，key列会显示所有使用的索引，

extra列会显示：

• Using intersect(...)
• Using sort_union(...)
• Using union(...)

intersect、union、sort_union分别对于的是索引合并算法：

• Index Merge Intersection Access Algorithm
• Index Merge Union Access Algorithm
• Index Merge Sort-Union Access Algorithm