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

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

SELECT * FROM tbl_name ; 
  • 1

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

交集语句测试:

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节,“优化提示”。



免责声明:

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用,书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权,请联系本站,本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源,请您支持作者,购买正版内容。

6、资源失效,请下方留言,欢迎分享资源链接

文章评论

0条评论