当前位置： 首页 > article >正文

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

article 2025/2/26 14:48:54

索引合并介绍

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 * FROM `t_demo` WHERE a = 5132 AND b = 'ss' AND c = 'bYJYf';
+----+-------------+--------+------------+-------------+---------------+-------------+---------+------+------+----------+-------------------------------------------+
| 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 |    10.00 | Using intersect(idx_a,idx_b); Using where |
+----+-------------+--------+------------+-------------+---------------+-------------+---------+------+------+----------+-------------------------------------------+

失效情况

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a IN (5132, 5133) AND b = 'ss';
+----+-------------+--------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+--------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | t_demo | NULL       | range | idx_a,idx_b   | idx_a | 5       | NULL |   49 |     0.15 | Using index condition; Using where |
+----+-------------+--------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+

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       | ref  | idx_a,idx_b   | idx_b | 48      | const |  288 |    50.00 | Using where |
+----+-------------+--------+------------+------+---------------+-------+---------+-------+------+----------+-------------+

失效原因：
1.使用了范围查询 IN
2.使用了范围查询 <=

联合索引测试

删除其他索引，重新新建两个联合索引

ALTER TABLE `t_demo` ADD INDEX `idx_ab`(a,b) USING BTREE;
ALTER TABLE `t_demo` ADD INDEX `idx_cd`(c,d) USING BTREE;

生效情况

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = 5132 AND b = 'ss' AND c = 'bYJYf' AND d = 1621331318479;
+----+-------------+--------+------------+-------------+---------------+---------------+---------+------+------+----------+----------------------------------------------------------+
| id | select_type | table  | partitions | type        | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                                    |
+----+-------------+--------+------------+-------------+---------------+---------------+---------+------+------+----------+----------------------------------------------------------+
|  1 | SIMPLE      | t_demo | NULL       | index_merge | idx_ab,idx_cd | idx_cd,idx_ab | 57,53   | NULL |    1 |   100.00 | Using intersect(idx_cd,idx_ab); Using where; Using index |
+----+-------------+--------+------------+-------------+---------------+---------------+---------+------+------+----------+----------------------------------------------------------+

失效情况

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = 5132 AND c = 'bYJYf';
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | idx_ab,idx_cd | idx_cd | 48      | const |    5 |     1.00 | Using where |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------+

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = 5132 AND b = 'ss' AND c = 'bYJYf';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref         | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | idx_ab,idx_cd | idx_ab | 53      | const,const |    5 |     1.00 | Using where |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------+

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a <= 5132 AND b = 'ss' AND c = 'bYJYf' AND d = 1621331318479;
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref         | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | idx_ab,idx_cd | idx_cd | 57      | const,const |    2 |     5.00 | Using where |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------+

失效原因：
1.查询条件未覆盖所有联合索引列
2.查询条件未覆盖第二个联合索引列
3.使用了范围查询 <=

聚簇索引

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

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

生效情况

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE id <= 10000 AND a = 5132;
+----+-------------+--------+------------+-------------+---------------+---------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table  | partitions | type        | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                       |
+----+-------------+--------+------------+-------------+---------------+---------------+---------+------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | t_demo | NULL       | index_merge | PRIMARY,idx_a | idx_a,PRIMARY | 13,8    | NULL |    1 |   100.00 | Using intersect(idx_a,PRIMARY); Using where |
+----+-------------+--------+------------+-------------+---------------+---------------+---------+------+------+----------+---------------------------------------------+

失效情况

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE id = 87760 AND a = 5132;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | const | PRIMARY,idx_a | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

失效原因：
1.聚簇索引的等值查询直接使用聚簇索引效率会更高，不需要使用索引合并。

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

执行流程和交集算法类似，先根据不同索引查询出有序的主键集合，然后求并集。

例如：WHERE a = 5132 OR b = 'ss'

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = 5132 OR 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 |  320 |   100.00 | Using union(idx_a,idx_b); Using where |
+----+-------------+--------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+

需要注意的是，OR连接的条件必须都有索引。如果c字段没有索引，那么加上 OR c = 'bYJYf1' 后，不会走索引合并

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = 5132 OR b = 'ss' OR c = 'bYJYf1';
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t_demo | NULL       | ALL  | idx_a,idx_b   | NULL | NULL    | NULL | 187882 |    19.01 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+

排序并集索引合并算法（Index Merge Sort-Union Access Algorithm）

执行流程和并集算法类似，但是是范围查询，联合索引也可以不覆盖所有的索引列。

排序并集算法和并集算法的区别在于，排序并集算法必须首先获取所有行的行主键，并在返回任何行之前对其进行排序。

例如：WHERE a < 200 OR b < 'aa'

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a < 200 OR b < 'aa';
+----+-------------+--------+------------+-------------+---------------+-------------+---------+------+------+----------+--------------------------------------------+
| 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 | 4114 |   100.00 | Using sort_union(idx_a,idx_b); Using where |
+----+-------------+--------+------------+-------------+---------------+-------------+---------+------+------+----------+--------------------------------------------+

需要注意的是，并不是所有范围查询都能命中排序并集算法，当查询范围扩大，索引合并会失效。

例如，将上面例子修改一下：a < 2000 OR b < 'aa'，将不会走索引合并

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a <= 2000 OR b <= 'aa';
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t_demo | NULL       | ALL  | idx_a,idx_b   | NULL | NULL    | NULL | 187882 |    55.55 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+


http://www.kler.cn/a/14479.html

相关文章：

  • ROS学习第二十九节——URDF之joint
  • 系统分析师考试，信息安全常错题
  • 还在精神内耗？还在焦虑？可以看看这个
  • 选择营销自动化软件时的3个常见错误
  • 春秋云境：CVE-2022-25401（任意文件读取漏洞）
  • 真题详解(UML图)-软件设计（五十五)
  • new的操作符具体做了什么？
  • 第6章：集合
  • MySQL-中间件mycat（二）
  • 必备装机软件，软件推荐
  • Tell me the following codes meaning “rate = rospy.Rate(10)“
  • 记录一次webdav协议磁盘挂载经验总结
  • Java阶段二Day10
  • 【是C++，不是C艹】 什么是C++ | C++从哪来 | 学习建议
  • QMS-云质说质量 - 7 IATF 16949哪个条款严重不符合项最多？
  • GO 游戏网络开发工程师招聘面试题
  • JS规范及常见问题
  • 5.6 Vue中的localStorage和sessionStorage浏览器端数据存储机制
  • API测试| 了解API接口测试| API接口测试指南（一）
  • Redis相关整理