mysql索引（九）索引合并

索引合并是mysql底层为我们提供的智能算法。了解索引合并的算法，有助于我们更好的创建索引。

索引合并是通过多个range类型的扫描并且合并它们的结果集来检索行的。仅合并来自单个表的索引扫描，而不是跨多个表的索引扫描。合并会产生底层扫描的三种形式：unions（合并）、intersections（交集）、unions-of-intersections（先取交集再合并）。

以下四个例子会产生索引合并：

索引合并有以下已知的局限性：

1、如果查询语句包含一个带有严重AND/OR嵌套的复杂的WHERE子句而MySQL没有选择最佳计划，那么可以尝试使用以下的标志符转换：

(x AND y) OR z =>(x OR z) AND (y OR z)

(x OR y) AND z =>(x AND z) OR (y AND z)

2、索引合并不适用于全文索引。

在 EXPLAIN 语句输出的信息中，索引合并在type列中表现为“index_merge”，在这种情况下，key列包含使用的索引列表。

索引合并访问方法有几种算法，表现在 EXPLAIN 语句输出的Extra字段中：

下面将更详细地描述这些算法。优化器根据各种可用选项的成本估计，在不同的索引合并算法和其他访问方法之间进行选择。

Index Merge Intersection算法

Index Merge Intersection算法对所有使用的索引执行同步扫描，并生成从合并的索引扫描接收到的行序列的交集。

这种算法适用于当WHERE子句被转换成多个使用AND连接的不同索引key上的范围条件，且条件是以下两种之一：

一、这种形式的N部分表达式，索引正好包括N个字段（所有索引字段都被覆盖），N>=1，N如果大于1就是复合索引：

二、InnoDB表主键上的任何范围条件。

例子：

Index Merge Union算法

该算法类似于Index Merge Intersection算法，适用于当WHERE子句被转换成多个使用OR连接的不同索引key上的范围条件，且条件是以下三种之一：

一、这种形式的N部分表达式，索引正好包括N个字段（所有索引字段都被覆盖），N>=1，N如果大于1就是复合索引：

二、InnoDB表主键上的任何范围条件。

三、符合Index Merge Intersection算法的条件。

例子：

Index Merge Sort-Union算法

该算法适用于当WHERE子句被转换成多个使用OR连接的不同索引key上的范围条件，但是不符合 Index Merge Union算法的。Index Merge Sort-Union和Index Merge Union算法的区别在于，Index Merge Sort-Union必须首先获取所有行的行id并在返回任何行之前对它们进行排序。

例子：

有好的建议，请在下方输入你的评论。

欢迎访问个人博客

https://guanchao.site

索引合并检索方法可以检索多个范围扫描并将结果合并。这种访问方法只能合并同一个表的索引扫描，不能合并跨表扫描。

合并可能生成基础扫描结果的"并集"，"交集"，或者"交集的并集"

示例：

这种方法适用于 WHERE 子句中的条件是通过 AND 结合的不同索引的范围条件时，其中的每个条件都需要满足下列条件之一：

示例：

索引合并交集算法在所有使用的索引上同时进行扫描，并从扫描结果中生成行的交集

如果查询中的所有列都被使用的索引覆盖，不需要检索所有表行（ EXPLAIN 输出中的 Extra 列中包括 Using index ）。例如这个语句：

SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1

如果使用的索引没有覆盖查询中所有的行，只有当所有使用的索引的范围条件满足时才检索整个行。

如果合并条件中包括 Innodb 表主键索引条件，主键并不用来检索数据，而是用来筛选使用其他条件检索出的行。 # 就是先通过其他的范围条件筛选出一部分数据，在从这部分数据中，通过主键来筛选出最终的结果

这种方法适用于 WHERE 子句中的条件是通过 OR 结合的不同索引的范围条件时，其中的每个条件都需要满足下列条件之一：

示例：

这种方法适用于 WHERE 子句中的条件是通过 OR 结合的不同索引的范围条件，但是不能使用 Index Merge Union 算法的情景

示例：

sort_union 和 union 算法的区别是， sort_union 必须在返回行数据前先获取行ID并对行ID进行排序。

在 optimizer_swith 中有4个关于 Index Merge 的变量：

index_merge,index_merge_intersection,index_merge_union,index_merge_sort_union

默认情况下都是启用的。要单独启用某个算法，设置 index_merge=off ，并将相应的标志设置为 on

使用视图的两种算法merge和temptable分别统计

表tb_phone中market_price大于4000的手机,然后查询视图查找出小于6000的手机

简单总结最终获取的结果:查询出market_price大于4000且小于6000的手机

表数据:

merge合并算法

合并的执行方式,每当执行的时候,先将视图的sql语句与外部查询视图的sql语句,合并在一起,最终执行.

以下是使用常规select语句模拟合并算法:

执行结果模拟与算法的结果相同:

temptable临时表算法

先执行视图再执行其他 *** 作的查询

临时表模式,每当查询的时候,将视图所使用的select语句生成一个结果的临时表,再在当前的临时表内进行查询

以下是使用常规select语句模拟临时表算法:

执行结果模拟与算法的结果相同:

比较两种算法结果也相同,似乎是这样的,接下来的实例将不同

实例2

使用视图的两种算法merge和temptable分别统计

表tb_phone , 同类(cid)手机的最高价格(market_price)的产品

表数据:

首先可以考虑基本select语句的实现方式(这里就不再赘述):

先排序再分组

接下来使用两种算法:

merge合并算法

最后一句select模拟合并算法有语法错误的,order by 不能在 group by之前,这时执行会报错,但在视图中不会报错,而是直接跳过了错误部分order by 直接执行了

select * from tb_phone group by cid

最终结果(错误):

temptable临时表算法

发现执行结果与普通语句相同,模拟的语句同样正确

最终结果(正确):

---