关于mysql:索引合并能不用就不要用吧

36次阅读

共计 4248 个字符，预计需要花费 11 分钟才能阅读完成。

@[toc]
在后面的文章中，松哥和小伙伴们分享了 MySQL 中，InnoDB 存储引擎的数据结构，小伙伴们晓得，当咱们应用索引进行搜寻的时候，每一次的搜寻都是在某一棵 B+Tree 中搜寻的，如果应用了二级索引的话，可能还会波及到回表。

那么当初问题来了，如果咱们的搜寻条件中蕴含两个字段，且这两个字段都有独立的索引，那么 MySQL 会怎么解决？明天咱们就来探讨下这个话题。

1. 问题重现

为了不便小伙伴们了解，我先通过 SQL 来把我的问题反复一下。

我应用的测试数据是 MySQL 官网提供的测试数据，相干的介绍文档在：

  • https://dev.mysql.com/doc/employee/en/

相应的数据库脚本在：

  • https://github.com/datacharmer/test_db

小伙伴们能够自行下载这个数据库脚本并导入到本人的数据库之中。

在官网提供的案例中，有一个这样的表：

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_merge，possible_keys 和 key 中，都给进去了两个索引，Extra 中的值为 Using union(idx_fk_film_id,PRIMARY); Using where。

看起来是用了索引，然而具体是怎么用的，这个执行打算该如何解读呢？

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

2. 索引合并

index_merge 示意索引合并，当同一个表中的搜寻条件中同时存在多个索引的时候，MySQL 会别离对这些索引进行扫描，而后将扫描后果进行合并，合并分三种状况：

  1. 对各自扫描后果求并集（unions）。
  2. 对各自扫描后果求交加（intersections）。
  3. 前两者的组合。

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

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，有两个条件：

  1. 如果是二级索引，则必须是等值查问。如果二级索引是复合索引，则复合索引的每一列都必须笼罩到，不能只是其中的某几列。
  2. 主键索引能够是范畴查问。

咱们来看官网给出的一个例子，如下：

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 来敞开索引合并性能，如下：

好啦，索引合并就和小伙伴们聊这么多吧～感兴趣的小伙伴也能够尝试下哦！

正文完
mysql
发表至： mysql
2023-05-07
 0
关于mysql:AndroidJava-WebMySQL实现登录注册
MYSQL8创建删除用户和授权消权操作
关于mysql:Apache-Linkis自定义变量实践分享
关于mysql:技术分享-ProxySQL-搭配-MySQL-HA-下