MySQL 8.0 官方文档 第八章 优化（四）—— 索引合并优化

第八章 优化（四）—— 索引合并优化

8.2 优化SQL语句

8.2.1 优化 SELECT 语句

8.2.1.3 索引合并优化

索引合并访问方法使用对多个范围扫描来检索行，并将这些检索到的行合并在一起。此访问方法仅合并来自单个表的索引扫描，而不能来自跨多个表扫描。在这些基础扫描获取的行集基础上，合并可以产生并集、交集或交集后并集。

以下是可以使用索引合并的查询示例：（译者：keyN是索引，key1_part1 和 key1_part2表示多列复合索引的两个列，non_key表示无索引）

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);

注意：

索引合并优化算法有以下已知的限制:

• 如果您的查询带有 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)
  

• 索引合并不能应用于 full-text索引。

在EXPLAIN输出中，索引合并方法在type列中显示为index_merge。在本例中，key列包含所用索引的列表，key_len包含这些索引最长键部分的列表。

【译者：下面是译者进行的测试，先创建表：（译者使用的版本是：MySQL 8.0.24.0，要出现各种访问方法，需要进行多方测试才得的。）

CREATE TABLE `tbl_name` (
	  `id` int NOT NULL AUTO_INCREMENT,
	  `f1` int DEFAULT NULL,
	  `f2` varchar(20) DEFAULT NULL,
	  `non_key` char(20) NOT NULL,
	  PRIMARY KEY (`id`),
	  KEY `key1` (`f1`),
	  KEY `key2` (`f2`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

插入一些测试数据，注意需要在f1和f2字段中插入一些重复数据，以形成查询时的范围条件。具体数据如下：

SELECT * FROM tbl_name ; 

下面进行索引合并访问方法测试：

交集语句测试：

EXPLAIN SELECT * FROM tbl_name WHERE f1 = 1 AND f2 = 'abc'; 

其EXPLAIN输出如下：

交集后并集语句的测试如下：

EXPLAIN SELECT * FROM tbl_name WHERE (f1 = 1 OR f2 = 'abc') AND non_key = 'abc20';

EXPLAIN SELECT * FROM tbl_name WHERE (f1 = 1 AND non_key = 'abc20') 
								OR (f2 = 'abc' AND non_key = 'abc20');

EXPLAIN输出结果分别是：

以上两种方式在filtered列有所不同：

】

索引合并访问方法有几种算法，在EXPLAIN输出的Extra字段中它们显示为：

• Using intersect(…) # 使用交集

• Using union(…) # 使用并集

• Using sort_union(…) # 使用排序_并集

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

索引合并交集访问算法

当WHERE子句被转换为用AND连接的在不同键上的多个范围条件，并且每个条件是以下条件之一时，此访问算法适用：

• 这种形式的N部分表达式，其中索引恰好有N部分（即覆盖了所有索引部分。译者：即索引覆盖）：

  key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
  

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

示例：

SELECT * FROM innodb_table
  		WHERE primary_key < 10 AND key_col1 = 20;

SELECT * FROM tbl_name
  		WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;

【译者：自己进行测试：

EXPLAIN SELECT * FROM tbl_name WHERE id < 8 AND f1 = 2 ;

输入结果如下：

】

索引合并交集算法会对所有使用的索引执行同步扫描，并在合并索引扫描中接收的行序列基础上的生成交集。

如果查询中使用的所有列都被使用的索引覆盖，则不会检索全表中的行集（在这种情况下，EXPLAIN输出包含Extra字段中的Using index）。下面是这样一个查询的例子：

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

【译者：自己进行测试如下，没有出现Using index输出：

EXPLAIN SELECT COUNT(*) FROM tbl_name WHERE f1 = 1 AND f2 = 'abc';

】

如果使用的索引没有覆盖查询中使用的所有列，则只有在满足所有使用的键的范围条件时，才检索全部的行集。（译者：这句表示的是如果不满足所有键的范围条件，也就是没有符合条件的行，当然也不要去检索全表；只有出现符合所有有键的条件的行，才需要回表获取没有被索引覆盖的列的数据。）

如果合并的条件中有一个是InnoDB表的主键上的条件，那么它不用于行检索，而是用于过滤使用了其他条件检索出来的行集。

索引合并并集访问算法

该算法的规则类似于索引合并交集算法。当WHERE子句被转换为用OR连接的在不同键上的多个范围条件，并且每个条件是以下条件之一时，此访问算法适用：

• 这种形式的N部分表达式，其中索引恰好有N部分（即覆盖了所有索引部分。译者：即索引覆盖）：

• key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
  

  【译者：以上条件是原官方文档上的，可能复制粘贴而来，忘记修改了，是错误的，译者认为应该是以下：

  key_part1 = const1 OR key_part2 = const2 ... OR key_partN = constN
  

  】

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

• 索引合并交集算法适用的条件。

示例：

SELECT * FROM t1
  		WHERE key1 = 1 OR key2 = 2 OR key3 = 3;

SELECT * FROM innodb_table
		WHERE (key1 = 1 AND key2 = 2)
		   OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;

【译者：进行测试，结果是：

EXPLAIN SELECT * FROM tbl_name WHERE f1 = 1 OR f2 = 'abc';

】

索引合并排序_并集访问算法

当WHERE子句被转换为用OR连接的多个范围条件时，该访问算法适用，但不适用索引合并并集算法。

示例：

SELECT * FROM tbl_name
  		WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;

【译者：自己进行测试，具体输出为：

EXPLAIN SELECT * FROM tbl_name WHERE f1 < 2 OR f2 LIKE 'ab%';

EXPLAIN SELECT * FROM tbl_name WHERE (f1 < 2 OR f2 LIKE 'ab%') AND non_key = 'abc20';

】

排序并集算法和并集算法的区别在于，排序并集算法必须首先获取所有行的行号（id），并在返回任何行之前对它们进行排序。

影响索引合并优化

索引合并的使用取决于optimizer_switch系统变量中的index_merge（索引合并）、index_merge_intersection（索引合并交集）、index_merge_union（索引合并并集）和index_merge_sort_union（索引合并排序并集）标志的值。请参见8.9.2节，“Switchable Optimizations（可切换优化）”。默认情况下，所有这些标志都是开启的。想要只启用某些算法，请将index_merge设置为off，并只启用应允许的其他算法。

除了使用optimizer_switch系统变量来控制优化器在会话范围内对索引合并算法的使用之外，MySQL还支持优化器的提示来影响每条语句基础上的优化器。参见8.9.3节，“优化提示”。

上一章 MySQL 8.0 官方文档 第八章 优化（三）—— Range（范围）优化

下一章 MySQL 8.0 官方文档 第八章 优化（五）—— 哈希连接优化