首页 死磕 Java 优质系列 Java 技术总览 Java 面试宝典 关于我
logo

Java 面试宝典：什么是索引合并？

原
 2024-11-09
©版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。 本文链接：https://www.skjava.com/mianshi/baodian/detail/1099868078

回答

索引合并（Index Merge）是 MySQL5.1 推出的优化技术，允许查询在没有复合索引的情况下，利用多个单列索引来提高查询性能。当一个查询的where子句包含多个条件，且这些条件分别适用于不同的索引，MySQL 会将这些索引合并起来使用，减少回表次数，提升性能。

索引合并支持合并单表索引，不能合并跨表的索引。

扩展

索引合并案例

1、初始化数据

CREATE TABLE t_employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT NOT NULL,
    contact_phone VARCHAR(20),
    department VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX idx_age ON t_employee(age);
CREATE INDEX idx_department ON t_employee(department);

INSERT INTO t_employee (name, age, contact_phone, department) VALUES
('John', 30, '18012346789', 'HR'),
('Jane', 25, '18012346780', 'Engineering'),
('Alice', 35, '18012346710','HR'),
('Bob', 28, '18012346410', 'Engineering'),
('Charlie', 32, '18012346510', 'Marketing'),
('James', 24, '18012346410', 'SoftWare'),
('Cat', 22, '18012346610', 'AI'),
('Jean', 30, '18012346530', 'Big Data'),
('Coca', 19, '18012946710', 'SoftWare'),
('Mary', 34, '18032846510', 'SoftWare'),
('Maly', 28, '18012447510', 'Engineering'),
('Robet', 40, '18012546510', 'AI');

2、执行计划结果

mysql> EXPLAIN select * from t_employee where department = 'Engineering' AND age = 28;
+----+-------------+------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+------------------------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                                                |
+----+-------------+------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+------------------------------------------------------+
|  1 | SIMPLE      | t_employee | NULL       | index_merge | idx_age,idx_department | idx_age,idx_department | 4,203   | NULL |    1 |   100.00 | Using intersect(idx_age,idx_department); Using where |
+----+-------------+------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+------------------------------------------------------+
  • type：访问类型，如上的 index_merge 表示使用了索引合并策略。
  • key：实际使用的索引，如上为 idx_age ，idx_department 。
  • Extra：额外信息，如上为 Using intersect(idx_age,idx_department); Using where 表示使用了索引合并（索引交集）和过滤条件。

上述案例中，MySQL 优化器基于idx_age 和 idx_department 两个单列索引，通过索引合并技术优化查询。有效减少需要扫描的行数，提升查询性能。**Using intersect(idx_age, idx_department) 表示 MySQL 使用了索引 idx_age 和 idx_department 的交集，保留满足所有条件的记录**。

索引合并流程

  1. 条件分析：MySQL 优化器分析查询的where子句，确定其中包含哪些独立条件。
  2. 索引选择：对于where子句中的独立条件，优化器检查是否存在可用的索引。如果存在，它会评估使用索引的成本。
  3. 索引扫描：优化器决定使用哪些索引后，它会分别对这些索引进行扫描，获取满足条件的记录集。
  4. 结果合并：扫描完所有选定的索引后，MySQL 将记录集合并得到最终的结果集。其中合并的方式有交集（Intersection）、并集（Union）和排序并集（Sort-Union）。
  5. 返回结果：最终，优化器将合并后的结果集返回给客户端。

索引合并类型

  1. 交集合并

当查询需满足多个条件（AND 连接），并且每个条件都可以使用不同的索引时，MySQL 优化器会分别扫描这些索引，然后取结果的交集。

mysql> EXPLAIN select * from t_employee where department = 'Engineering' AND age = 28;
+----+-------------+------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+------------------------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                                                |
+----+-------------+------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+------------------------------------------------------+
|  1 | SIMPLE      | t_employee | NULL       | index_merge | idx_age,idx_department | idx_age,idx_department | 4,203   | NULL |    1 |   100.00 | Using intersect(idx_age,idx_department); Using where |
+----+-------------+------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+------------------------------------------------------+

如上，department 和 age 是两个独立的条件，每个条件都有对应的索引。MySQL 会决定使用交集合并策略，分别扫描idx_department 和 idx_age 索引，并对取得的结果取交集返回客户端。

  1. 并集合并

在某些场景，查询只需满足多个条件的任意一个（OR 连接）。MySQL会分别扫描这些索引，然后取结果的并集。

mysql> EXPLAIN select * from t_employee where department = 'Engineering' OR age = 28;
+----+-------------+------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+--------------------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                                            |
+----+-------------+------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+--------------------------------------------------+
|  1 | SIMPLE      | t_employee | NULL       | index_merge | idx_age,idx_department | idx_department,idx_age | 203,4   | NULL |    5 |   100.00 | Using union(idx_department,idx_age); Using where |
+----+-------------+------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+--------------------------------------------------+

如上，只需要满足department = 'Engineering'和age = 28中任意一条件，记录便会被选中。MySQL会使用并集合并策略，分别扫描idx_department 和 idx_age 索引，然后合并结果集返回客户端。

备注：对于包含OR导致索引失效的场景可以利用索引合并可以优化查询。

  1. 排序并集合并

如果需要对结果进行排序，并且排序字段也有也有索引。MySQL 会分别扫描索引，然后合并并排序结果。

mysql> EXPLAIN select * from t_employee where department = 'HR' or age > 30  order by age;
+----+-------------+------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+-----------------------------------------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                                                                 |
+----+-------------+------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+-----------------------------------------------------------------------+
|  1 | SIMPLE      | t_employee | NULL       | index_merge | idx_age,idx_department | idx_department,idx_age | 203,4   | NULL |    6 |   100.00 | Using sort_union(idx_department,idx_age); Using where; Using filesort |
+----+-------------+------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+-----------------------------------------------------------------------+

索引合并会有额外开销吗？

当然。索引合并是查询优化器的执行策略，它可以提高查询性能，但也会带来一些额外的开销。

mysql> EXPLAIN select * from t_employee where department = 'Engineering' OR age = 28;
+----+-------------+------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+--------------------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                                            |
+----+-------------+------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+--------------------------------------------------+
|  1 | SIMPLE      | t_employee | NULL       | index_merge | idx_age,idx_department | idx_department,idx_age | 203,4   | NULL |    5 |   100.00 | Using union(idx_department,idx_age); Using where |
+----+-------------+------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+--------------------------------------------------+
  1. 索引扫描开销

对于上面的 SQL，MySQL 需要分表扫描idx_age和idx_department索引，然后合并两个索引的结果集。这意味着需要进行两次索引扫描，而非一次。

  1. 合并结果集开销

同理，MySQL 需要将多个索引的结果集进行合并。包括将结果集排序、去重以及合并等操作，这些操作会消耗额外的 CPU 和内存资源。

  1. 内存开销

再者，索引合并时 MySQL 需要在内存中存储多个索引的结果集，当结果集较大时，会占用较多的内存资源。

  1. 总结

索引合并在某些情况下可以提高查询性能，但它也会带来一定额外开销。总体来说，它不如复合索引高效，故工作中应合理设计索引，尽量使用复合索引来覆盖常见的查询场景。

阅读全文