【MYSQL】索引覆盖&索引下推

每天学习一个小知识

索引覆盖

1、什么是索引覆盖？
覆盖索引是指一个索引包含了查询所需的所有字段，而不需要回表查询原始数据。

2、举个例子
假设我现在有一张表user，主键id，联合索引(A, B)

CREATE TABLE user (
id int NOT NULL AUTO_INCREMENT,
A varchar(255) DEFAULT NULL,
B int(11) DEFAULT NULL,
C varchar(11) DEFAULT NULL,
PRIMARY KEY (id)，
INDEX IDX_A_B (A, B)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假设我的查询语句为

select B from user where A = ?

我的查询条件和查询字段都在索引中，可以直接从索引中获取结果直接返回，不需要再去访问user表了

3、索引覆盖的优缺点
优点：

• 减少了磁盘I/O，提高了查询速度
• 减少了内存占用，节省了资源

缺点：

• 增加了索引的大小，占用了更多的磁盘空间
• 增加了数据更新的开销，因为每次更新数据都需要更新索引

因此，在使用索引覆盖时，我们需要注意一些限制和条件

• 选择合适的字段和顺序，避免创建过多或过大的索引

索引下推

1、什么是索引下推？
索引下推是指在使用联合索引时，将过滤条件下推到索引层，从而减少回表的次数

2、举个例子
假设我现在有一张表user，主键id，联合索引(A, B, C)

CREATE TABLE user (
id int NOT NULL AUTO_INCREMENT,
A varchar(255) DEFAULT NULL,
B int(11) DEFAULT NULL,
C varchar(11) DEFAULT NULL,
PRIMARY KEY (id)，
INDEX IDX_A_B_C (A, B, C)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

现在我往表里插入几条数据
id =1, A = 1, B=1, C=1
id =2, A = 1, B=2, C=2
id =3, A = 2, B=3, C=3

假设我需要查询 A = 1, C = 2的数据，根据最左匹配的原则，我可以查到id为1和2的数据其实A都满足我们的要求，本来需要回表id = 1, id= 2两条数据回表后判断C是否满足要求。
此时如果有索引下推的话，发现查询条件C刚好也在该联合索引上，我们就可以直接拿到索引上的数据来过滤C是否满足，这样就只需要查询id = 2的数据进行回表，从而减少回表的次数。

3、索引下推的优缺点
优点

• 减少了回表的次数和范围，提高了查询速度
• 减少了数据传输量和网络开销

缺点：

• 增加了索引扫描的复杂度和开销
• 可能会导致误判或漏判，因为索引层无法处理一些复杂或模糊的过滤条件

因此，在使用索引下推时，我们需要注意一些限制和条件

• 索引下推只适用于联合索引
• 索引下推只适用于部分数据类型和操作符
• 索引下推可能会受到优化器的影响