尽量避免使用索引合并的场景问题解析

目录
  • 引言
  • 1. 问题重现
  • 2. 索引合并
    • 2.1 Using intersect(...)
    • 2.2 Using union(...)
    • 2.3 Using sort_union(...)
    • 2.4 索引合并原理
  • 3. 索引合并的问题

引言

在前面的文章中,松哥和小伙伴们分享了 MySQL 中,InnoDB 存储引擎的数据结构,小伙伴们知道,当我们使用索引进行搜索的时候,每一次的搜索都是在某一棵 B+Tree 中搜索的,如果使用了二级索引的话,可能还会涉及到回表。

那么现在问题来了,如果我们的搜索条件中包含两个字段,且这两个字段都有独立的索引,那么 MySQL 会怎么处理?今天我们就来讨论下这个话题。

1. 问题重现

为了方便小伙伴们理解,我先通过 SQL 来把我的问题重复一下。

我使用的测试数据是 MySQL 官网提供的测试数据,相关的介绍文档在:

相应的数据库脚本在:

小伙伴们可以自行下载这个数据库脚本并导入到自己的数据库之中。

在官方提供的案例中,有一个这样的表:

CREATE TABLE `film_actor` (
  `actor_id` smallint unsigned NOT NULL,
  `film_id` smallint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`,`film_id`),
  KEY `idx_fk_film_id` (`film_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

在这个表中有两个索引,其中一个是主键索引,主键索引是一个联合索引,还有一个是根据 film_id 建立的普通索引。现在假设我有如下 SQL 需要执行:

select * from film_actor where film_id=1 or actor_id=1;

那么问题来了,这个查询会用到索引吗?

想知道有没有用到索引,用 explain 关键字看一下就知道了:

explain select * from film_actor where film_id=1 or actor_id=1;

执行结果如下:

小伙伴们看到,此时 type 是 index_mergepossible_keys 和 key 中,都给出来了两个索引,Extra 中的值为 Using union(idx_fk_film_id,PRIMARY); Using where

看起来是用了索引,但是具体是怎么用的,这个执行计划该如何解读呢?

这个其实就是一个索引合并,接下来我们就来看下到底什么是索引合并。

2. 索引合并

index_merge 表示索引合并,当同一个表中的搜索条件中同时存在多个索引的时候,MySQL 会分别对这些索引进行扫描,然后将扫描结果进行合并,合并分三种情况:

  • 对各自扫描结果求并集(unions)。
  • 对各自扫描结果求交集(intersections)。
  • 前两者的组合。

在官方文档中给了四个可能会用到索引合并的例子:

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

有的时候,我们写的 SQL,明明可以合并,但是系统却没有合并,此时我们对查询条件做一些调整,例如:

  • (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 执行计划中,如果用到了索引合并,Extra 字段的值一般分为三种情况,分别是:

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

上文案例属于第二种情况。

那么接下来把这三种情况都来和小伙伴们聊一下。

2.1 Using intersect(...)

这个就是对多个扫描结果求交集。

并不是只要涉及到多个索引,且是 AND,就会触发 Using intersect,有两个条件:

  • 如果是二级索引,则必须是等值查询。如果二级索引是复合索引,则复合索引的每一列都必须覆盖到,不能只是其中的某几列。
  • 主键索引可以是范围查询。

我们来看官方给出的一个例子,如下:

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

key_part1 - key_partN 就是复合索引中的所有列(必须是所有列)。

对于第 2 点,如果涉及到主键索引,则主键索引可以是范围查询,例如下面这样(但是二级索引依然只能是等值查询):

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;

key1_part1 和 key1_part2 分别表示同一个复合索引的第一列和第二列(一共就两列),此时和 key2 一起作为查询条件,也有可能会用到索引合并。

上面这些情况都是在各自搜索完成之后求交集。

举一个简单的例子吧,还是 MySQL 官方的测试数据,sakila 库中有一个 actor 表,该表结构如下:

CREATE TABLE `actor` (
  `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb3;

可以看到,有一个主键,有一个普通索引,我执行如下 SQL:

select * from actor where actor_id<10 and last_name='WAHLBERG'

执行计划如下:

可以看到,用到了索引合并,且是 Using intersect

2.2 Using union(...)

求并集的跟求交集的比较像,就是 AND 变成了 OR。

当二级索引是等值查询,或者是组合索引,但是要求组合索引的每一列都必须覆盖到,不能只是覆盖到部分列,例如下面这个查询条件:

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

key_part1~key_partN 就是同一个复合索引的不同列,同时在该复合索引中,也一共就只有这 N 个字段,这种情况就会用到 Using union

InnoBD 表上的主键范围查询也有可能会触发 Using union

符合 2.1 小节的情况,将 AND 换成 OR 之后,也有可能会触发 Using union

这个例子就不用举了,文章一开始的就是。

2.3 Using sort_union(...)

很明显,2.2 小节的条件比较苛刻,二级索引必须是等值查询才能触发 Using union,而我们日常使用的时候,范围查询也是非常常见的,所以又有了 Using sort_union,这个的要求就宽松一些了:

  • 二级索引也可以按照范围匹配
  • 复合索引也不用覆盖所有列

举个例子,如下面的 SQL:

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;

二级索引范围搜索,也有可能触发 Using sort_union 的。

2.4 索引合并原理

在 2.1 小节和 2.2 小节,分别是求交集和求并集,为了 intersect 和 union 操作方便,在各个单独的索引扫描的时候,都是要获取到有序的主键值的合集,各个索引都获取到有序的主键,然后求交集或者并集就会比较方便。

因此,在 2.1 和 2.2 小节,都是主键索引可以范围搜索,因为主键索引本身主键就是有序的;二级索引则有诸多限制,这诸多限制的最终目的都是为了做到最终拿到的主键值是有序的。

例如:

  • 二级索引必须等值匹配,等值匹配意味着最终拿到的 B+Tree 的叶子上的主键值就是唯一的;二级索引如果可以按照范围查找,那么最终从二级索引的 B+Tree 的叶子结点上拿到的主键值就不是有序的了。
  • 类似的,复合索引必须覆盖到所有列也是相似的原因,因为如果没有覆盖到所有列,意味着最终拿到的主键值也是无序的。

2.3 小节允许二级索引按照范围搜索,这是因为在 Using sort_union 中,会先对拿到的主键值进行排序,然后才会去求交集或者并集,当然,相比于 2.1 和 2.2 小节,2.3 小节的性能也会降低一些。

3. 索引合并的问题

索引合并看着似乎提升了 MySQL 搜索的性能,然而,一般出现索引合并,大概率都是因为索引创建的不合理,我们需要重新审视自己的索引。

如上面 2.3 小节所述,这种方式在查询的过程中需要缓存临时数据、需要排序然后才能求交集或者并集,这些操作都会消耗掉大部分的 CPU 和内存资源。并且这些消耗不会被计算到查询成本中,因为 MySQL 优化器只关心随机页面的读取问题,并不会关心这里涉及到的这些额外计算问题,所以,在一些极端情况下,索引合并的性能可能还不如全表扫描。

因此,有时候如果我们确定自己不需要索引合并,那么可以通过 ignore index 来忽略掉一些索引,如下(对比 2.1 小节截图):

也可以通过 optimizer_switch 来关闭索引合并功能,如下:

好啦,索引合并就和小伙伴们聊这么多吧~感兴趣的小伙伴也可以尝试下哦!

以上就是尽量避免使用索引合并的场景问题解析的详细内容,更多关于避免使用索引合并分析的资料请关注我们其它相关文章!

(0)

相关推荐

  • Pandas 按索引合并数据集的方法

    如下所示: import numpy as np import pandas as pd from pandas import Series,DataFrame 一.merge函数 left1 = DataFrame({'水果':['苹果','梨','草莓'], '价格':[3,4,5], '数量':[9,8,7]}).set_index('水果') right1 = DataFrame({'水果':['苹果','草莓'], '产地':['美国','中国']}) print(left1) pri

  • Mysql执行原理之索引合并详解

    Mysql执行原理之索引合并详解 我们前边说过MySQL在一般情况下执行一个查询时最多只会用到单个二级索引,但存在有特殊情况,在这些特殊情况下也可能在一个查询中使用到多个二级索引,MySQL中这种使用到多个索引来完成一次查询的执行方法称之为:索引合并/index merge,在前面的成本计算中我们说到过这个概念:“我们需要分别分析单独使用这些索引执行查询的成本,最后还要分析是否可能使用到索引合并”.其实optimizer trace输出的文本中就有这个片段: 具体的索引合并算法有下边三种. In

  • SQLServer创建索引的5种方法小结

    目录 前期准备: 创建聚集索引 方法 1. 方法 2. 创建复合索引 创建覆盖索引 创建唯一索引 筛选索引 总结: 前期准备: create table Employee ( ID int not null primary key, Name nvarchar(4), Credit_Card_ID varbinary(max)); --- 小心这种数据类型. go 说明:本表上的索引,都会在创建下一个索引前删除. 创建聚集索引 方法 1. ALTER TABLE table_name ADD C

  • mysql 索引合并的使用

    索引合并是mysql底层为我们提供的智能算法.了解索引合并的算法,有助于我们更好的创建索引. 索引合并是通过多个range类型的扫描并且合并它们的结果集来检索行的.仅合并来自单个表的索引扫描,而不是跨多个表的索引扫描.合并会产生底层扫描的三种形式:unions(合并).intersections(交集).unions-of-intersections(先取交集再合并). 以下四个例子会产生索引合并: SELECT * FROM tbl_name WHERE key1 = 10 OR key2 =

  • MySQL数据库索引以及失效场景详解

    目录 1. MySQL索引概述 1.1 索引的概念 1.2 索引的特点 1.3 索引的分类 1.4 索引的使用场景 2. 索引失效场景 2.1 索引失效9种场景 2.2 索引失效场景总结 3. 索引失效验证 3.1 全值匹配 3.2 最佳左前缀 3.3 索引计算 3.4 索引范围:索引列上不能有范围查询 3.5 索引覆盖:尽量使用覆盖索引 3.6 不等: 使用不等于(!= 或者 <>)的时候 3.7 null:字段的is not null 与is null 3.8 like:like的前后模糊

  • 分享15个Mysql索引失效的场景

    目录 背景 数据库及索引准备 创建表结构 初始化数据 数据库版本及执行计划 1 联合索引不满足最左匹配原则 2 使用了select * 3 索引列参与运算 4 索引列参使用了函数 5 错误的Like使用 6 类型隐式转换 7.使用OR操作 8 两列做比较 9 不等于比较 10 is not null 11 not in和not exists 12 order by导致索引失效 13 参数不同导致索引失效 14 其他 小结 背景 无论你是技术大佬,还是刚入行的小白,时不时都会踩到Mysql数据库不

  • ArrayList和LinkedList区别及使用场景代码解析

    本文研究的主要是Java编程中ArrayList和LinkedList区别及使用场景的相关内容,具体介绍如下. 1.ArrayList是基于数组实现的,其构造函数为: private transient Object[] elementData; private int size; ArryList初始化时,elementData数组大小默认为10: 每次add()时,先调用ensureCapacity()保证数组不会溢出,如果此时已满,会扩展为数组length的1.5倍+1,然后用array.

  • go-cache的基本使用场景示例解析

    目录 什么是 go-cache 使用 导入 快速开始 常量与结构体 常量 结构体 Set() Get() 删除 其他 备份恢复数据 什么是 go-cache go-cache 是一个轻量级的基于内存的 K-V 储存组件,内部实现了一个线程安全的 map[string]interface{},适用于单机应用.具备如下功能: 线程安全,多 goroutine 并发安全访问: 每个 item 可以设置过期时间(或无过期时间): 自动定期清理过期的 item: 可以自定义清理回调函数: 这里的 item

  • InnoDB主键索引树和二级索引树的场景分析

    我们这里讨论InnoDB存储引擎,数据和索引存储在同一个文件student.ibd 场景1:主键索引树 uid是主键,其他字段没有添加任何索引 select * from student; 如果是这样查询,这表示整表搜索,从左到右遍历叶子节点链表,从小到大访问 select * from student where uid<5; 如果是这样查询,这表示范围查询,就直接在有序链表中遍历搜索就可以了,直到遍历到第一个不小于5的key结束遍历 select * from student where u

  • Javascript实用方法之json合并的场景分析

    场景 2个json合并, jQuery 的“extend()”方法有两个原型:合并的方法,分别是浅合并和深度合并,其实浅合并只是json的第一层被合并,而深度合并是全部层数都被合并,分别如下写法: 1.浅合并,target被object1...合并,只有一层 $.extend( target [, object1 ] [, objectN ] ) 深度合并,target被object1...合并,包含一层二层三层... 要深度合并, [deep]需要为true, 后面覆盖前面,所以如要要保留后面

  • MySQL中索引失效的常见场景与规避方法

    前言 之前有看过许多类似的文章内容,提到过一些sql语句的使用不当会导致MySQL的索引失效.还有一些MySQL"军规"或者规范写明了某些sql不能这么写,否则索引失效. 绝大部分的内容笔者是认可的,不过部分举例中笔者认为用词太绝对了,并没有说明其中的原由,很多人不知道为什么.所以笔者绝对再整理一遍MySQL中索引失效的常见场景,并分析其中的原由供大家参考. 当然请记住,explain是一个好习惯! MySQL索引失效的常见场景 在验证下面的场景时,请准备足够多的数据量,因为数据量少时

随机推荐