MySQL索引合并Index Merge 一条SQL使用多个索引情况分析，以及索引合并失效情况

原创

已于 2023-04-23 15:48:50 修改 · 4.2k 阅读

于 2023-04-23 14:47:16 首次发布

索引合并介绍

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

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

怎么确定使用了索引合并

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

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

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

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

可以使用索引合并的查询示例

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2
WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2
WHERE t1.key1 = 1
AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

索引合并具有以下限制

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.索引合并不适用于全文索引。

交集索引合并算法（Index Merge Intersection Access Algorithm）

交集索引合并算法的使用原则

1.二级索引的等值查询，如果是联合索引查询条件必须包含所有联合索引列。
2.聚簇索引的范围查询。

索引合并实战分析

CREATE TABLE `t_demo2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `a` int(15) DEFAULT NULL,
  `b` varchar(15) DEFAULT NULL,
  `c` varchar(15) DEFAULT NULL,
  `d` bigint(15) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二级索引（单列索引）测试

删除其他索引，重新新建两个单列的二级索引

ALTER TABLE `t_demo` ADD INDEX `idx_a`(a) USING BTREE;
ALTER TABLE `t_demo` ADD INDEX `idx_b`(b) USING BTREE;

生效情况

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = 5132 AND b = 'ss';
+----+-------------+--------+------------+-------------+---------------+-------------+---------+------+------+----------+-------------------------------------------+
| id | select_type | table  | partitions | type        | possible_keys | key         | key_len | ref  | rows | filtered | Extra                                     |
+----+-------------+--------+------------+-------------+---------------+-------------+---------+------+------+----------+-------------------------------------------+
|  1 | SIMPLE      | t_demo | NULL       | index_merge | idx_a,idx_b   | idx_a,idx_b | 5,48    | NULL |    1 |   100.00 | Using intersect(idx_a,idx_b); Using where |
+----+-------------+--------+------------+-------------+---------------+-------------+---------+------+------+----------+-------------------------------------------+

mysql> EXPLAIN SELECT * FRO

标签