数据库核心术语知识库

定义

**索引合并 (Index Merge)**是 MySQL 5.0 引入的一种查询优化技术。当查询的 WHERE 条件涉及多个列,且这些列上分别存在单列索引时,优化器可以选择同时使用多个索引,然后将各个索引的扫描结果进行合并(取交集、并集或去重),最后再回表获取完整数据。

索引合并在某些场景下可以避免全表扫描,但它通常不是最优方案,复合索引往往能提供更好的性能。

详细笔记

核心原理

索引合并的三种算法

Intersection (交集):用于 AND 条件
Union (并集):用于 OR 条件
Sort-Union (排序并集):用于 OR 条件,需要先排序

示例说明

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  city VARCHAR(50),
  INDEX idx_name (name),
  INDEX idx_age (age),
  INDEX idx_city (city)
);

Intersection 示例

sql

SELECT * FROM users WHERE name = '张三' AND age = 25;

执行流程:

1. 扫描 idx_name 索引,找到 name = '张三' 的主键列表: [1, 5, 10, 20]
2. 扫描 idx_age 索引,找到 age = 25 的主键列表: [5, 10, 15, 25]
3. 对两个主键列表取交集: [5, 10]
4. 对交集的主键回表,获取完整数据
5. 返回结果

Union 示例

sql

SELECT * FROM users WHERE name = '张三' OR age = 25;

执行流程:

1. 扫描 idx_name 索引,找到 name = '张三' 的主键列表: [1, 5, 10]
2. 扫描 idx_age 索引,找到 age = 25 的主键列表: [5, 10, 15, 25]
3. 对两个主键列表取并集: [1, 5, 10, 15, 25]
4. 对并集的主键回表,获取完整数据
5. 返回结果

Sort-Union 示例

sql

SELECT * FROM users WHERE name = '张三' OR city = '北京';

当单个索引扫描结果无序时,需要先排序再合并:

1. 扫描 idx_name,得到主键列表: [10, 5, 1]
2. 对主键列表排序: [1, 5, 10]
3. 扫描 idx_city,得到主键列表: [8, 3, 15]
4. 对主键列表排序: [3, 8, 15]
5. 合并两个有序列表: [1, 3, 5, 8, 10, 15]
6. 回表获取完整数据
7. 返回结果

EXPLAIN 分析

sql

EXPLAIN SELECT * FROM users WHERE name = '张三' AND age = 25;

输出示例:

+----+-------------+-------+-------------+---------------------+---------------------+---------+------+------+--------------------------------------------------+
| id | select_type | table | type    | possible_keys   | key       | key_len | ref  | rows | Extra                |
+----+-------------+-------+-------------+---------------------+---------------------+---------+------+------+--------------------------------------------------+
|  1 | SIMPLE  | users | index_merge | idx_name,idx_age  | idx_name,idx_age  | 152,5 | NULL |  2 | Using intersect(idx_name,idx_age); Using where |
+----+-------------+-------+-------------+---------------------+---------------------+---------+------+------+--------------------------------------------------+

关键标识:

type: index_merge:表示使用了索引合并
key: idx_name,idx_age:同时使用了两个索引
Extra: Using intersect(idx_name,idx_age):使用交集合并算法
其他可能的值:Using union(...), Using sort_union(...)

索引合并 vs 复合索引

性能对比

sql

-- 场景:查询 name = '张三' AND age = 25

-- 方案一:索引合并
-- 现有索引:idx_name(name), idx_age(age)
-- 优化器选择:index_merge(idx_name, idx_age)
-- 开销:扫描两个索引 + 合并 + 回表

-- 方案二:复合索引(推荐)
CREATE INDEX idx_name_age ON users(name, age);
-- 优化器选择:range 扫描复合索引
-- 开销:扫描一个索引 + 回表

-- 性能差异:
-- 索引合并:约 100ms
-- 复合索引:约 10ms (快 10 倍)

为什么复合索引更好?

I/O 次数更少:只需扫描一个索引树
无需合并操作:避免了内存中的集合运算
更好的选择性:复合索引可以利用最左前缀原则精确过滤
减少回表次数:复合索引可以直接过滤更多记录

索引合并的局限性

局限性一:无法利用索引下推

sql

-- 索引合并无法使用 ICP 优化
SELECT * FROM users WHERE name = '张三' AND age > 25;

-- 即使有 idx_name 和 idx_age,也无法下推 age > 25 的条件

局限性二:不适用于范围查询

sql

-- 以下查询无法使用索引合并
SELECT * FROM users WHERE name > '张' AND age > 25;

-- 原因:范围查询会导致索引扫描结果集过大,合并成本高

局限性三:优化器可能误判

sql

-- 优化器可能错误地选择索引合并,而忽略更好的复合索引
SELECT * FROM users WHERE name = '张三' AND age = 25;

-- 即使存在 idx_name_age 复合索引,优化器仍可能选择
-- index_merge(idx_name, idx_age)

解决方案:删除冗余的单列索引,或强制使用复合索引

sql

-- 方法一:删除冗余索引
DROP INDEX idx_name ON users;
DROP INDEX idx_age ON users;

-- 方法二:强制使用复合索引
SELECT * FROM users FORCE INDEX(idx_name_age) 
WHERE name = '张三' AND age = 25;

何时使用索引合并?

适用场景

已有单列索引,无法修改表结构:

sql

-- 遗留系统,已有多个单列索引
-- 临时查询需要多条件过滤
SELECT * FROM orders WHERE user_id = 100 AND status = 1;

查询条件不固定,难以设计复合索引:

sql

-- 动态查询,条件组合多变
SELECT * FROM products 
WHERE category_id = 10 
  AND (brand_id = 5 OR supplier_id = 8);

低频查询,不值得创建复合索引:

sql

-- 偶尔执行的报表查询
SELECT * FROM logs WHERE log_level = 3 AND app_id = 100;

不适用场景

高频查询:应该创建合适的复合索引
大表深度分页:索引合并效率低
需要排序的查询:索引合并无法优化 ORDER BY

控制索引合并

查看和优化器配置

sql

-- 查看 optimizer_switch 配置
SHOW VARIABLES LIKE 'optimizer_switch';

-- 输出包含:
-- index_merge=on
-- index_merge_intersection=on
-- index_merge_union=on
-- index_merge_sort_union=on

禁用索引合并

sql

-- Session 级别禁用
SET SESSION optimizer_switch = 'index_merge=off';

-- 全局禁用
SET GLOBAL optimizer_switch = 'index_merge=off';

-- 只禁用特定算法
SET SESSION optimizer_switch = 'index_merge_intersection=off';

使用 Index Hint

sql

-- 强制不使用索引合并
SELECT * FROM users IGNORE INDEX(idx_name, idx_age)
WHERE name = '张三' AND age = 25;

-- 强制使用特定索引
SELECT * FROM users FORCE INDEX(idx_name_age)
WHERE name = '张三' AND age = 25;

实际案例分析

案例一:电商订单查询

sql

-- 表结构
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  user_id INT,
  status TINYINT,
  create_time DATETIME,
  amount DECIMAL(10, 2),
  INDEX idx_user_id (user_id),
  INDEX idx_status (status),
  INDEX idx_create_time (create_time)
);

-- 问题查询
SELECT * FROM orders 
WHERE user_id = 12345 AND status = 1;

-- EXPLAIN 显示使用了 index_merge
-- type: index_merge
-- key: idx_user_id,idx_status
-- Extra: Using intersect(idx_user_id,idx_status)

-- 优化方案:创建复合索引
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 优化后
-- type: ref
-- key: idx_user_status
-- 性能提升:约 5 倍

案例二:日志系统多条件查询

sql

CREATE TABLE application_logs (
  log_id BIGINT PRIMARY KEY,
  app_id INT,
  log_level TINYINT,
  create_time DATETIME,
  message TEXT,
  INDEX idx_app_id (app_id),
  INDEX idx_log_level (log_level),
  INDEX idx_create_time (create_time)
);

-- 动态查询(条件可选)
SELECT * FROM application_logs 
WHERE app_id = 100 
  AND log_level IN (1, 2, 3)
  AND create_time > '2024-01-01';

-- 分析:
-- 1. 三个条件都有单列索引
-- 2. 优化器可能选择 index_merge
-- 3. 但更适合创建复合索引

-- 优化方案
CREATE INDEX idx_app_level_time ON application_logs(app_id, log_level, create_time);

-- 优化效果:
-- 1. 符合最左前缀原则
-- 2. 可以 ICP 优化
-- 3. 避免索引合并的开销

监控与诊断

检查索引合并使用情况

sql

-- 查看执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users 
WHERE name = '张三' AND age = 25;

-- JSON 输出:
{
  "query_block": {
  "table": {
  "access_type": "index_merge",
  "used_indexes": ["idx_name", "idx_age"],
  "index_merge_details": {
    "merge_algorithm": "intersect",
    "indexes": ["idx_name", "idx_age"]
  }
  }
  }
}

Performance Schema 统计

sql

-- 查看索引使用频率
SELECT 
  OBJECT_NAME,
  INDEX_NAME,
  COUNT_READ,
  SUM_TIMER_READ
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_NAME = 'users'
ORDER BY COUNT_READ DESC;

最佳实践

优先使用复合索引:对于高频查询,设计合适的复合索引优于依赖索引合并
避免冗余索引:如果已有复合索引 (a, b),则单列索引 (a) 通常是冗余的
定期审查执行计划:检查是否有查询意外使用了索引合并
权衡索引数量:每个索引都会增加写入开销,不要过度创建
使用索引合并作为临时方案:在无法立即优化索引结构时,索引合并可以提供一定的性能保障

关联术语

[[回表]]
[[执行计划]]
[[索引选择性]]
[[最左前缀原则]]
[[索引下推]]

参考资料

MySQL 官方文档: Index Merge Optimization
《高性能 MySQL》第 5 章:索引优化策略
Percona Blog: Understanding MySQL Index Merge
MariaDB 文档: Index Merge Algorithm

定义 ​

详细笔记 ​

核心原理 ​

索引合并的三种算法 ​

示例说明 ​

Intersection 示例 ​

Union 示例 ​

Sort-Union 示例 ​

EXPLAIN 分析 ​

索引合并 vs 复合索引 ​

性能对比 ​

为什么复合索引更好? ​

索引合并的局限性 ​

局限性一:无法利用索引下推 ​

局限性二:不适用于范围查询 ​

局限性三:优化器可能误判 ​

何时使用索引合并? ​

适用场景 ​

不适用场景 ​

控制索引合并 ​

查看和优化器配置 ​

禁用索引合并 ​

使用 Index Hint ​

实际案例分析 ​

案例一:电商订单查询 ​

案例二:日志系统多条件查询 ​

监控与诊断 ​

检查索引合并使用情况 ​

Performance Schema 统计 ​

最佳实践 ​

关联术语 ​

参考资料 ​

定义