面试必问系列：MySQL 索引合并优化及底层原理

原创

Lorin 洛林

发布于 2023-11-23 21:06:33

6790

发布于 2023-11-23 21:06:33

文章被收录于专栏：MySQL 数据库MySQL 数据库

hello，大家好，我是 Lorin，最近无意中看到一道 MySQL 经典面试题，什么是索引合并，也许有的小伙伴比较疑惑，心里想难道是联合索引？其实并不是，下面我们就来看看什么是索引合并。

什么是索引合并

我们在使用 Explain 语句有时候可能会遇到查询类型为：index_merge，正如字面意思所示，这就是我们常说的索引合并。

什么是索引合并呢？索引合并优化是一种查询优化技术，它利用多个索引来加速查询的执行。当一个查询中包含多个条件，并且这些条件分别适用于不同的索引时，MySQL 可以将这些索引合并起来使用，减少了回表的次数，以加速查询的执行。
仅限合并来自单个表的索引扫描，而不是跨多个表的扫描。

如上图所示，将 n 次回表查询合并为一次处理，加速查询的执行，接下来我们看看索引合并的几种具体情况。

准备

准备我们需要的数据，并插入10条数据：
注：数据量太少会导致优化器选择其它执行计划，大家可以插入1000条左右数据或使用强制索引。

CREATE TABLE `test_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT '',
  `merchant_id` bigint(20) NOT NULL,
  `area` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_id` (`user_id`) USING BTREE,
  KEY `idx_merchant_id` (`merchant_id`) USING BTREE,
  KEY `idx_area` (`area`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test_table` (`user_id`, `name`, `merchant_id`, `area`) VALUES (1, '1', 1, 1);
INSERT INTO `test_table` (`user_id`, `name`, `merchant_id`, `area`) VALUES (2, '2', 2, 2);
INSERT INTO `test_table` (`user_id`, `name`, `merchant_id`, `area`) VALUES (3, '3', 3, 3);
INSERT INTO `test_table` (`user_id`, `name`, `merchant_id`, `area`) VALUES (4, '4', 4, 4);
INSERT INTO `test_table` (`user_id`, `name`, `merchant_id`, `area`) VALUES (5, '5', 5, 5);
INSERT INTO `test_table` (`user_id`, `name`, `merchant_id`, `area`) VALUES (6, '6', 6, 6);
INSERT INTO `test_table` (`user_id`, `name`, `merchant_id`, `area`) VALUES (7, '7', 7, 7);
INSERT INTO `test_table` (`user_id`, `name`, `merchant_id`, `area`) VALUES (8, '8', 8, 8);
INSERT INTO `test_table` (`user_id`, `name`, `merchant_id`, `area`) VALUES (9, '9', 9, 9);
INSERT INTO `test_table` (`user_id`, `name`, `merchant_id`, `area`) VALUES (10, '10', 10, 10);

合并算法

Index Merge 合并有几种常见算法，显示在输出Extra字段中EXPLAIN，优化器会根据成本估算选择合适的算法：交集算法Using intersect(...) 并集算法 Using union(...) 排序并集算法Using sort_union(...)

交集算法

使用交集算法有两种场景：

// 1、多个由下列表达式构造的组合（每个索引包含部分）
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN

EXPLAIN SELECT * FROM test_table WHERE merchant_id = 3 AND area = 3;

// 2、主键范围查询 和 上述表达式组合
EXPLAIN SELECT * FROM test_table WHERE merchant_id = 3 AND id < 3;

并集算法

// 1、多个由下列表达式构造的组合（每个索引包含部分）
key_part1 = const1 OR key_part2 = const2 ... OR key_partN = constN

EXPLAIN SELECT * FROM test_table WHERE merchant_id = 3 AND area = 3;

// 2、主键范围查询 和 上述表达式组合
EXPLAIN SELECT * FROM test_table WHERE merchant_id = 3 OR id < 3;

排序并集算法

适用于多个范围查询使用 OR 条件查询。（因此不要再说 OR 条件无法使用索引，而是 OR 条件前后存在无索引的列会导致索引失效）
index merge union 的使用前提是主键有序，当辅助索引获取列对应主键值无序时，先对获取的主键进行排序，然后进行合并。

EXPLAIN select * from test_table where merchant_id < 3 or area<3;

EXPLAIN select * from test_table where merchant_id < 3 or area=3;

影响索引合并的因素

配置项

Index Merge 的使用受制于系统变量的index_merge、 index_merge_intersection、 index_merge_union和 index_merge_sort_union flags 的值optimizer_switch 。默认情况下，所有这些标志都是on. 要仅启用某些算法，请设置index_merge 为off，并启用允许的算法。
配置项查看：

SHOW VARIABLES LIKE 'optimizer_switch';

优化器

优化器会根据成本分析结果选择成本最低的执行计划。比如下列语句在数据量比较少了会选择全表扫描。

EXPLAIN select * from test_table where merchant_id < 3 or area<3;

参考

https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html

个人简介

? 你好，我是 Lorin 洛林，一位 Java 后端技术开发者！座右铭：Technology has the power to make the world a better place.

? 我对技术的热情是我不断学习和分享的动力。我的博客是一个关于Java生态系统、后端开发和最新技术趋势的地方。

? 作为一个 Java 后端技术爱好者，我不仅热衷于探索语言的新特性和技术的深度，还热衷于分享我的见解和最佳实践。我相信知识的分享和社区合作可以帮助我们共同成长。

? 在我的博客上，你将找到关于Java核心概念、JVM 底层技术、常用框架如Spring和Mybatis 、MySQL等数据库管理、RabbitMQ、Rocketmq等消息中间件、性能优化等内容的深入文章。我也将分享一些编程技巧和解决问题的方法，以帮助你更好地掌握Java编程。

? 我鼓励互动和建立社区，因此请留下你的问题、建议或主题请求，让我知道你感兴趣的内容。此外，我将分享最新的互联网和技术资讯，以确保你与技术世界的最新发展保持联系。我期待与你一起在技术之路上前进，一起探讨技术世界的无限可能性。

? 保持关注我的博客，让我们共同追求技术卓越。

我正在参与2023腾讯技术创作特训营第三期有奖征文，组队打卡瓜分大奖！

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

2023腾讯·技术创作特训营第三期

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

2023腾讯·技术创作特训营第三期

#mysql

登录后参与评论

0 条评论

热度