MySQL在5.6为啥引入索引条件下推
索引条件下推（Index Condition Pushdown, ICP） 是 MySQL 在 5.6 版本引入的一项优化技术。它通过将某些查询条件推送到存储引擎层来减少回表操作，从而提高查询效率。通常在使用范围查询或多列索引时，当查询条件没有完全匹配最左列，MySQL 会进行回表查询。而索引条件下推能够在扫描索引时直接过滤掉不符合条件的记录，减少不必要的回表操作。
一、索引条件下推原理
-
普通查询的过程：
- 在没有索引条件下推的情况下，MySQL 会扫描索引，找到符合索引的记录指针，然后回表读取完整的行数据，再应用查询条件。
- 如果索引条件较松散，MySQL 会进行大量回表操作，性能会较差。
-
索引条件下推的过程：
- MySQL 优化器将部分查询条件推到存储引擎层，使得这些条件在扫描索引时就能被应用。这样就可以在索引扫描过程中过滤掉不符合条件的记录，减少回表次数，提高查询效率。
二、索引条件下推的场景
- 联合索引：当查询使用了联合索引，但查询条件没有完全匹配最左列时，ICP 可以通过将查询条件下推到存储引擎层过滤掉不符合条件的记录。
- 范围查询：在范围查询中，ICP 可以减少对不符合条件的记录的回表操作。
三、Java 模拟索引条件下推
虽然在 Java 中没有 MySQL 的存储引擎和索引的直接概念，但我们可以模拟 MySQL 中的索引结构和查询过程，展示如何通过索引条件下推减少回表操作。
1. 设计模拟的索引结构和数据表
我们假设有一个包含联合索引的表，并设计出模拟的“回表”操作。然后通过索引条件下推优化查询。
2. Java 代码示例
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
class Record {
int id; // 模拟主键
String colA; // 索引列A
String colB; // 索引列B
String data; // 其他表数据
public Record(int id, String colA, String colB, String data) {
this.id = id;
this.colA = colA;
this.colB = colB;
this.data = data;
}
public String toString() {
return "ID: " + id + ", colA: " + colA + ", colB: " + colB + ", data: " + data;
}
}
class IndexConditionPushdownSimulator {
// 模拟数据库表记录
private List<Record> table = new ArrayList<>();
// 插入数据
public void insert(int id, String colA, String colB, String data) {
table.add(new Record(id, colA, colB, data));
}
// 模拟没有使用索引条件下推的查询
public List<Record> queryWithoutICP(String colACondition, String colBCondition) {
// 模拟索引扫描：只根据colA过滤，所有符合的记录都需要回表
List<Record> indexScanResult = table.stream()
.filter(record -> record.colA.equals(colACondition))
.collect(Collectors.toList());
// 模拟回表：从回表数据中再根据colB过滤
return indexScanResult.stream()
.filter(record -> record.colB.equals(colBCondition))
.collect(Collectors.toList());
}
// 模拟使用索引条件下推的查询
public List<Record> queryWithICP(String colACondition, String colBCondition) {
// 模拟索引扫描：在索引扫描时直接将条件下推，减少回表操作
return table.stream()
.filter(record -> record.colA.equals(colACondition) && record.colB.equals(colBCondition))
.collect(Collectors.toList());
}
public static void main(String[] args) {
IndexConditionPushdownSimulator simulator = new IndexConditionPushdownSimulator();
// 插入一些数据
simulator.insert(1, "A1", "B1", "Data1");
simulator.insert(2, "A2", "B2", "Data2");
simulator.insert(3, "A1", "B3", "Data3");
simulator.insert(4, "A2", "B1", "Data4");
simulator.insert(5, "A1", "B1", "Data5");
// 不使用索引条件下推的查询
System.out.println("Query without ICP:");
List<Record> resultWithoutICP = simulator.queryWithoutICP("A1", "B1");
for (Record record : resultWithoutICP) {
System.out.println(record);
}
// 使用索引条件下推的查询
System.out.println("\nQuery with ICP:");
List<Record> resultWithICP = simulator.queryWithICP("A1", "B1");
for (Record record : resultWithICP) {
System.out.println(record);
}
}
}
代码解析
-
模拟表记录和联合索引：
Record类代表表中的一行数据，包含colA和colB两个可以作为联合索引的列，data则是实际的表数据（需要回表时读取）。insert()方法用于插入记录。
-
不使用索引条件下推的查询：
queryWithoutICP()方法模拟了没有使用 ICP 的查询过程，首先根据colA列过滤，获取一部分符合条件的记录，然后回表，再根据colB进行过滤。- 这种方法在大数据量时效率较低，因为每次都需要回表。
-
使用索引条件下推的查询：
queryWithICP()方法模拟了使用 ICP 的查询过程。在索引扫描阶段就将colB的过滤条件下推，从而减少了回表操作的次数，提高了查询效率。
-
运行结果：
输出结果展示了两种不同方式的查询：
Query without ICP: ID: 1, colA: A1, colB: B1, data: Data1 ID: 5, colA: A1, colB: B1, data: Data5 Query with ICP: ID: 1, colA: A1, colB: B1, data: Data1 ID: 5, colA: A1, colB: B1, data: Data5结果虽然相同，但使用 ICP 的查询过程更加高效，因为在索引扫描时已经过滤掉了不符合
colB条件的记录，减少了不必要的回表操作。
四、索引条件下推的优缺点
优点：
- 减少回表操作：索引条件下推将更多的过滤工作放在存储引擎层完成，减少了回表次数，提升了查询性能。
- 优化范围查询：在范围查询和联合索引的情况下，索引条件下推可以显著提高查询效率。
缺点：
- 适用场景有限：索引条件下推只在某些特定场景下发挥作用，尤其是在使用联合索引或范围查询时。对于简单查询，ICP 可能不会带来明显的提升。
五、总结
- 索引条件下推 是 MySQL 优化器的一项优化技术，能够将部分查询条件在索引扫描阶段提前过滤，从而减少回表操作，提升查询性能。
- 在 Java 中，我们可以通过模拟 MySQL 的索引结构和查询流程，展示索引条件下推如何减少不必要的回表操作。
- 这种优化在联合索引和范围查询中尤为有效，尤其是在查询条件不完全匹配最左前缀的情况下。
原文地址：https://blog.csdn.net/qq_41520636/article/details/143051654
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！