MySQL优化-index merge （索引合并）从MySQL5.1版本开始，MySQL引入了index merge优

从MySQL5.1版本开始，MySQL引入了index merge优化技术，使得对同一个表可以使用多个索引分别进行条件扫描，然后将结果进行合并处理，从而改善对单个表的查询性能。

索引合并 通过多个 range 扫描检索记录，并将多个结果合并为一个结果，这种情况只作用于单表的索引扫描，不支持多表，这种合并在底层有三种扫描方式：unions（联合）, intersections（交叉）, unions-of-intersections（交叉联合）。

假设现在有一张表：t_user_order，总行数10480，通过以下SQL查询给出表结构：

select 
    COLUMN_NAME as '字段名',
    IS_NULLABLE as '允许为空', 
    COLUMN_TYPE as '字段类型', 
    COLUMN_KEY as '字段索引', 
    EXTRA as '额外属性', 
    COLUMN_COMMENT as '字段注释'
from 
    information_schema.COLUMNS
where TABLE_NAME = 't_user_order' order by ORDINAL_POSITION

字段名	允许为空	字段类型	字段索引	额外属性	字段注释
id	NO	int unsigned	PRI	auto_increment	主键自增id
code	NO	varchar(64)	UNI		订单号（唯一索引）
uid	NO	varchar(64)	MUL		用户uid（普通索引）
pay_amount	NO	int			支付金额
pay_status	NO	tinyint			支付状态，1：支付中，2：支付成功，4：支付关闭
order_status	NO	tinyint	MUL		订单状态，1：已创建，2：已收款，4：已完成，8：已关闭

表中有四个索引，id主键索引，code唯一索引，uid普通索引，order_status普通索引

Note
索引合并优化算法，有以下限制：

如果查询中有一个复杂的 WHERE 子句，其中包含比较深的 AND/OR 嵌套，而 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)

不适用于全文索引

在 EXPLAIN 输出中，如果使用了索引合并，在 type 列中显示为 index_merge ，Extra 列会显示具体的算法：

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

能使用到索引合并优化的几个栗子：

union算法

该算法适用于将表中的 WHERE 子句转换为不同索引上的多个范围条件，并与 OR 结合使用，且每个条件都是以下条件之一时：

where 条件中每个筛选条件都单独用了一个索引：

select * from t_user_order where uid = 'a6f9dd21ef094f09976cae64dfddfd0d' or order_status = 4

执行计划：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_user_order		index_merge	idx_uid,idx_order_status	idx_uid,idx_order_status	258,1		4272	100	Using union(idx_uid,idx_order_status); Using where

innodb表主键上的任意范围查询:

select * from t_user_order where id < 15000 or uid = 'a6f9dd21ef094f09976cae64dfddfd0d';

执行计划：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_user_order		index_merge	PRIMARY,idx_uid	PRIMARY,idx_uid	4,258		5203	100	Using union(PRIMARY,idx_uid); Using where

intersect算法

这种算法适用于将 WHERE 子句转换为不同索引上的多个范围条件并结合 AND 的情况，且每个条件都是以下条件之一：

where 条件中每个筛选条件都单独用了一个索引：

select * from t_user_order where uid = 'a6f9dd21ef094f09976cae64dfddfd0d' and order_status = 4

执行计划：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_user_order		index_merge	idx_uid,idx_order_status	idx_uid,idx_order_status	258,1		11	99.35	Using intersect(idx_uid,idx_order_status); Using where

innodb表主键上的任意范围查询（滚动分页场景）：

select * from t_user_order where id < 15000 and uid = 'a6f9dd21ef094f09976cae64dfddfd0d'

执行计划：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_user_order		index_merge	PRIMARY,idx_uid	idx_uid,PRIMARY	262,4		1	100	Using intersect(idx_uid,PRIMARY); Using where

这种算法会对所有使用的索引执行同步扫描，从扫描出的多个结果集中取交集。如果所使用的索引覆盖了查询中使用的所有列，则不会回表查询整行数据，在这种情况下，EXPLAIN 输出的 Extra 字段中会多一个 use index，栗子：

select count(*) from t_user_order where  uid = 'a6f9dd21ef094f09976cae64dfddfd0d' and order_status = 2

执行计划：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_user_order		index_merge	idx_uid,idx_order_status	idx_uid,idx_order_status	258,1		6	90.95	Using intersect(idx_uid,idx_order_status); Using where; Using index

sort-union算法

这种访问算法适用于 WHERE 子句转换为由 OR 组合的多个范围条件时，但不适用union算法时，栗子：

select * from t_user_order
where (
        uid in ('755d01d793c94835a75ea3922e73fe42', '2baf6fd6d1274a56af1f212e20cd129d')
        or
        code like 'MC20241205%'
    )

执行计划：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_user_order		index_merge	uniq_code,idx_uid	idx_uid,uniq_code	258,258		38	100	Using sort_union(idx_uid,uniq_code); Using where

sort-union 算法与 union 算法的区别在于，sort-union 算法必须首先获取所有行的 ID 并对其进行排序，然后才返回。

索引合并优化相关配置

optimizer_switch

索引合并的使用受制于 optimizer_switch 系统变量的 index_merge、index_merge_intersection、index_merge_union 和 index_merge_sort_union 的值。默认情况下，所有这些标志都处于开启状态。要只启用某些算法，可将 index_merge 设置为关闭，并只启用其他允许的算法，使用以下命令可以查看mysql上的配置：

show variables like 'optimizer_switch'

输出结果：

Variable_name	Value
optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on

Optimizer Hints（优化器提示）

除了使用 optimizer_switch 系统变量在整个会话范围内控制优化器对索引合并算法的使用外，MySQL 还支持优化器提示，在每条语句的基础上影响优化器，在优化器提示中有两个可用的提示名：

Hint Name	Description	Applicable Scopes
`INDEX_MERGE`, `NO_INDEX_MERGE`	Affects Index Merge optimization	Table, index

具体使用栗子：

select
    /*+ NO_INDEX_MERGE(t_user_order idx_uid,uniq_code) */ 
    *
from t_user_order
where (
        uid = '755d01d793c94835a75ea3922e73fe42'
        or
        code like 'MC20241205%'
    );

执行计划：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_user_order		ALL	uniq_code,idx_uid				10379	11.22	Using where

可以看到，在使用了 /*+ NO_INDEX_MERGE(t_user_order idx_uid,uniq_code) */ 优化器提示之后，执行计划采用了全表扫描的方式查询数据，也可以把 NO_INDEX_MERGE 替换成 INDEX_MERGE 来提示优化器使用索引合并优化来加快查询速度，INDEX_MERGE 提示会强制优化程序使用指定的索引集对指定的表进行索引合并。如果没有指定索引，优化器会考虑所有可能的索引组合，并选择成本最低的一个。如果索引组合不适用于给定语句，则可以忽略该提示。

对于 INDEX_MERGE 和 NO_INDEX_MERGE 优化器提示，适用这些优先级规则：

如果指定了优化器提示，则该提示优先于 optimizer_switch 系统变量中与index_merge相关的标志，即使 optimizer_switch 中相关的index_merge变量配置都为 off，但是索引提示的优先级要更高。
USE INDEX、FORCE INDEX 和 IGNORE INDEX 索引提示的优先级高于 INDEX_MERGE 和 NO_INDEX_MERGE 的优化器提示。

索引合并可能导致的问题

性能下降

索引合并与单个复合索引的效率对比：在某些情况下，索引合并可能不如使用单个复合索引高效。这是因为索引合并涉及多个索引查找和行ID的合并，可能比直接通过一个更适合的复合索引进行查找更耗时。
查询优化器的选择： MySQL的查询优化器可能错误地选择了索引合并，而不是单个更有效的索引。这种选择可能导致查询性能下降。
资源消耗：索引合并可能导致更高的CPU和内存消耗，尤其是在处理大量数据时。这会增加数据库服务器的负载，进而影响整体性能。

死锁

锁竞争：在使用事务的数据库中，索引合并可能增加锁竞争，特别是在高并发环境下。多个事务可能同时尝试访问同一个表的不同索引，导致锁冲突。
复杂的锁定模式：当使用索引合并时，MySQL可能会锁定多个索引涉及的行。这可能导致比单个索引使用时更复杂的锁定模式，从而增加死锁的风险。
锁定顺序不一致：不同的事务可能以不同的顺序获取多个索引上的锁。当两个或多个事务相互等待对方释放锁时，就会发生死锁。
增加的行锁数量：索引合并可能涉及更多的行锁，尤其是在范围查询或多个索引覆盖大量行时。这进一步增加了死锁的可能性。

综合来说，索引合并优化虽然可以提高某些查询的效率，但在某些情况下也可能导致性能下降和死锁问题。因此，在使用索引合并优化时，需要谨慎评估其利弊，并根据实际情况进行优化和调整。

参考文档：

dev.mysql.com/doc/refman/… dev.mysql.com/doc/refman/…