这篇文章就简单的给大家介绍下索引下推、索引合并

1. 索引下推（Index Condition Pushdown, ICP）

定义

索引下推是 MySQL 5.6 引入的一项优化，用于减少回表次数。它将部分查询条件的过滤工作推到存储引擎层，而不是在 MySQL 服务层处理，从而提升查询效率。

工作机制

在范围查询或复合索引场景下，MySQL 会利用索引列中的更多信息进行过滤，仅回表获取满足条件的数据。这减少了不必要的回表操作。

实现过程

1. 未使用索引下推

   • 查询语句：

     SELECT * FROM user1 WHERE name LIKE 'A%' AND age = 40;
     

   • 执行过程：

     1. 存储引擎通过索引 name 定位到匹配 name LIKE 'A%' 的数据范围。
     2. 每条记录都回表获取完整行数据。
     3. 回表后，在服务层进一步过滤 age = 40 的条件。

2. 使用索引下推

   • 查询语句：

     SELECT * FROM user1 WHERE name LIKE 'A%' AND age = 40;
     

   • 执行过程：

     1. 存储引擎在二级索引中先判断 name LIKE 'A%' 和 age = 40 的条件。
     2. 仅当二级索引满足所有条件时才回表获取完整行数据。

   • 优化效果：通过在存储引擎层过滤不符合条件的数据，回表次数大幅减少。

优化的典型场景

在范围查询中，通过复合索引的多个字段过滤条件，尤其是 SELECT * 查询。

---

2. 索引合并（Index Merge）

定义

索引合并是 MySQL 从 5.1 开始支持的一种优化技术，允许 SQL 查询同时使用多个单列索引，通过合并结果提高查询性能。

索引合并方式

1. 交集（INTERSECT）

   • 使用场景：查询条件是 AND。

   • 示例：

     SELECT * FROM user WHERE name = '赵六' AND age = 22;
     

   • 执行过程：

     • 分别通过 idx_name 和 idx_age 获取符合条件的主键 ID 列表。
     • 对主键 ID 列表取交集。
     • 根据交集中的 ID 回表查询。

2. 并集（UNION）

   • 使用场景：查询条件是 OR。

   • 示例：

     SELECT * FROM user WHERE name = '赵六' OR age = 22;
     

   • 执行过程：

     • 分别通过 idx_name 和 idx_age 获取符合条件的主键 ID 列表。
     • 对主键 ID 列表取并集，并去重。
     • 根据去重后的 ID 回表查询。

3. 排序后取并集（SORT-UNION）

   • 使用场景：当主键 ID 无序时（上面2种id是有序的）。

   • 示例：

     SELECT * FROM user WHERE name = '赵六' OR age > 22;
     

   • 执行过程：

     • 对主键 ID 进行排序。
     • 然后取并集。
     • 根据并集中的 ID 回表查询。

使用限制

• 索引顺序要求：取交集和并集都要求各索引列返回的主键 ID 是有序的。
• 优化范围有限：对于范围条件（如 age > 22），如果无法返回有序的主键 ID，则可能无法直接使用索引合并。

---

3. 对比与应用场景

特性	索引下推	索引合并
MySQL版本	5.6+	5.1+
优化目标	减少回表次数	组合使用多个索引
适用场景	复合索引、多条件过滤	单列索引、多条件组合查询
典型查询条件	AND	AND 或 OR
性能提升原理	存储引擎层提前过滤数据	合并多个索引结果

选用建议

1. 索引下推：优先在复合索引设计中，充分利用索引列的过滤能力。
2. 索引合并：适用于无法设计复合索引，但查询涉及多个单列索引的情况。

---

通过索引下推和索引合并的组合优化，可以显著提升查询性能，尤其是在大数据量的场景中表现尤为明显。

博客首页：总是学不会.