MySQL在5.6为啥引入索引条件下推
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);
}
}
}