企业官网建设流程全解析

一、什么是覆盖索引

覆盖索引不是一种特殊的索引类型，而是一种查询状态：

如果某个索引包含了一条 SQL 查询所需要的全部字段，那么 MySQL 只读取这个索引就能完成查询，不需要再读取完整的数据行。这个索引对该查询来说就是覆盖索引。

“查询需要的字段”不只是SELECT后面的字段，还可能包括：

WHERE过滤字段

JOIN ... ON关联字段

ORDER BY排序字段

GROUP BY分组字段

HAVING条件字段最终需要返回的字段

MySQL 官方将这种只读取索引树、不额外读取完整数据行的方式称为 index-only scan；传统格式的EXPLAIN通常会在Extra中显示Using index。

二、为什么覆盖索引能提高性

要理解它，需要先知道 InnoDB 的两类索引。

1. 聚簇索引

InnoDB 的主键索引是聚簇索引，它的叶子节点保存的是完整数据行：

主键 id | v 完整数据行

例如：

id=1001 user_id=20 status=1 amount=99.00 remark='新用户订单'

通过主键查询时，找到主键索引的叶子节点，就得到了完整数据。

2. 二级索引

除聚簇索引以外的普通索引、唯一索引，一般称为二级索引。

假设有索引：

KEY idx_user_status (user_id, status)

它的叶子节点大致保存：

user_id + status + 主键id

InnoDB 的二级索引会自动包含主键列。因此，即使创建索引时没有显式写id，二级索引中仍然可以取得主键值。

三、什么是“回表”

创建订单表：

CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, status TINYINT NOT NULL, amount DECIMAL(10, 2) NOT NULL, created_at DATETIME NOT NULL, remark VARCHAR(500), KEY idx_user_status (user_id, status) ) ENGINE = InnoDB;

执行：

SELECT amount FROM orders WHERE user_id = 100 AND status = 1;

索引idx_user_status只有：

user_id + status + id

但查询还需要amount，二级索引中没有这个字段，所以执行过程大致是：

1. 查找 idx_user_status 2. 找到符合条件的记录 3. 从二级索引中取得主键 id 4. 使用 id 查询聚簇索引 5. 从完整数据行中取得 amount

第 4 步就是通常所说的回表：

二级索引 -> 主键值 -> 聚簇索引 -> 完整数据行

如果符合条件的记录有 10 万条，就可能发生大量主键索引查找。

四、怎样变成覆盖索引

将索引改为：

CREATE INDEX idx_user_status_amount ON orders(user_id, status, amount);

再次执行：

SELECT amount FROM orders WHERE user_id = 100 AND status = 1;

这个索引中包含：

user_id + status + amount + id

查询需要的三个字段：

过滤需要user_id

过滤需要status

返回需要amount

全部可以从索引中得到，因此不需要回表。

执行过程变为：

1. 查找 idx_user_status_amount 2. 在索引叶子节点中取得 amount 3. 直接返回结果

此时，idx_user_status_amount对这条 SQL 来说就是覆盖索引。

五、主键可以被自动覆盖

由于 InnoDB 二级索引自动携带主键，下面的查询也是覆盖索引：

SELECT id, amount FROM orders WHERE user_id = 100 AND status = 1;

使用的索引仍然是：

(user_id, status, amount)

虽然定义中没有写id，但物理上二级索引包含主键，因此查询不需要回表。对于联合主键，InnoDB 会将主键的各个组成列加入二级索引。

一般不需要这样定义：

(user_id, status, amount, id)

因为id是主键时，通常已经自动包含在二级索引中。

六、同一个索引是否覆盖，取决于 SQL

索引：

KEY idx_user_status_amount (user_id, status, amount)

查询一：覆盖

SELECT amount FROM orders WHERE user_id = 100 AND status = 1;

需要的字段都在索引中。

查询二：依然覆盖

SELECT id, amount FROM orders WHERE user_id = 100 AND status = 1;

id是主键，二级索引自动包含它。

查询三：不覆盖

SELECT amount, remark FROM orders WHERE user_id = 100 AND status = 1;

remark不在索引中，需要根据主键回表读取。

查询四：通常不覆盖

SELECT * FROM orders WHERE user_id = 100 AND status = 1;

SELECT *需要所有字段，普通二级索引通常不包含所有列，因此需要回表。

所以准确的说法不是：

idx_user_status_amount是覆盖索引。

而是：

idx_user_status_amount覆盖了某条具体查询。

七、覆盖索引与最左前缀原则是两回事

假设有联合索引：

KEY idx_abc (a, b, c)

它的排序结构可以理解为：

先按 a 排序 a 相同时按 b 排序 a、b 都相同时按 c 排序

MySQL 可以直接利用的连续左前缀包括：

(a) (a, b) (a, b, c)

但通常不能直接利用：

(b) (c) (b, c)

来进行高效的 B-tree 定位。

考虑：

SELECT b, c FROM test WHERE b = 10;

查询需要的b、c都在idx_abc中，因此它可能是一个覆盖索引扫描。

但是查询没有使用最左侧的a，MySQL 可能无法通过索引快速定位，只能扫描大量甚至整个索引。

因此：

覆盖索引 != 一定能高效查找 使用了索引 != 一定是覆盖索引

判断性能至少要看两个问题：

1. 索引能否高效定位目标范围？
2. 索引能否覆盖查询，避免回表？

理想情况是两者同时满足。

八、如何通过 EXPLAIN 判断

执行：

EXPLAIN SELECT amount FROM orders WHERE user_id = 100 AND status = 1;

重点关注：

key: idx_user_status_amount Extra: Using index

Using index通常表示查询需要的信息可以直接从索引树取得，无须额外读取完整数据行。

不过要继续看type：

type=ref + Using index type=range + Using index

一般说明既利用索引定位，又避免了回表。

如果是：

type=index + Using index

可能表示扫描了整个索引。虽然没有回表，但扫描量仍可能很大。

MySQL 的树形执行计划也可能直接显示：

Covering index scan on orders using idx_user_status_amount

可以使用：

EXPLAIN FORMAT=TREE SELECT amount FROM orders WHERE user_id = 100 AND status = 1;

九、Using index和Using index condition的区别

这两个非常容易混淆。

Using index

表示使用了覆盖索引：

只读取索引，通常不读取完整数据行

Using index condition

表示使用了索引条件下推，也就是 ICP：

先在二级索引中判断部分条件 符合条件后，再读取完整数据行

它可以减少回表次数，但通常并没有彻底消除回表。官方文档明确区分了Using index和Using index condition。

例如：

SELECT * FROM users WHERE city = '上海' AND name LIKE '张%';

存在索引：

(city, name)

由于查询使用SELECT *，其他字段不在索引中，所以仍然需要完整数据行。MySQL 可以先在索引中判断city和name，过滤掉不符合的记录，再回表读取剩余记录。

十、前缀索引通常不能完整覆盖字段

例如：

CREATE INDEX idx_name ON users(name(10));

这个索引只保存name的前 10 个字符。

下面的查询通常不能仅通过该索引返回完整的name：

SELECT name FROM users WHERE name = '一个超过十个字符的完整姓名';

因为索引中没有完整字段值，MySQL可能需要读取完整数据行来确认和返回结果。MySQL 的前缀索引只保存指定长度的字符串前缀。

十一、覆盖索引的优点

1. 减少 B+Tree 查找次数

不覆盖：

查询二级索引 + 查询聚簇索引

覆盖：

只查询二级索引

2. 减少随机 I/O

大量回表可能访问不同的数据页。覆盖索引只扫描较紧凑的索引页，通常更利于缓存和顺序读取。

3. 索引通常比完整数据行小

一页中能容纳更多索引记录，因此读取相同数量的记录时，可能需要访问更少的数据页。官方文档也指出，索引树通常小于完整表数据，因此覆盖索引扫描一般比全表扫描更快。(dev.mysql.com)

例如：

SELECT id, created_at FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 1000;

如果索引可以同时完成过滤、排序和字段覆盖，就能明显减少读取完整数据行的成本。

十二、覆盖索引的缺点

不能为了覆盖查询就把所有字段都加入索引。

例如：

(user_id, status, created_at, amount, remark, address, description)

索引过宽会带来：

占用更多磁盘空间

占用更多 Buffer Pool

降低单个索引页能存放的记录数

增加 B+Tree 层级的可能性

降低插入和更新速度

更新索引字段时需要维护索引

增加优化器选择索引的成本