26. 什么是覆盖索引（Covering Index）？覆盖索引如何提高查询性能？

PangHu大约 4 分钟

覆盖索引（Covering Index） 是指一个索引包含了查询所需要的所有列的数据，因而不需要再访问表中的数据行。换句话说，当一个查询的所有字段（包括 SELECT 子句中的字段和 WHERE 子句中的字段）都被一个索引覆盖时，这个索引就称为覆盖索引。

覆盖索引的特点

减少I/O操作：当查询使用覆盖索引时，MySQL不需要读取表的数据行，可以直接从索引中返回所需的数据，从而减少了I/O操作。
提高查询速度：由于索引通常比表的数据行更小，且索引数据存储得更紧凑，因此使用覆盖索引可以显著加快查询速度。

覆盖索引如何提高查询性能？

覆盖索引可以通过以下几种方式提高查询性能：

1. 避免回表（回表：访问表的额外操作）

在没有覆盖索引的情况下，当查询条件匹配了某个索引但还需要查询其他列时，数据库引擎需要执行“回表”操作，即在找到索引匹配的行后，还需要通过索引指针回到表中取出其他列的数据。

而当使用覆盖索引时，所有查询的列都已经包含在索引中，数据库引擎只需访问索引即可满足查询需求，无需再进行回表操作。这大大减少了磁盘I/O，提升了查询速度。

示例：

假设有一个 employees 表，结构如下：

CREATE TABLE employees (
    employee_id INT,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    INDEX idx_employee (department, employee_name, salary)
) ENGINE=InnoDB;

如果我们执行以下查询：

SELECT employee_name, salary FROM employees WHERE department = 'HR';

在这个查询中，所有使用的列 (department, employee_name, salary) 都在 idx_employee 索引中。因此，MySQL只需要访问索引 idx_employee 即可满足查询的所有数据需求，而不需要访问表的实际数据行，这样的索引就称为覆盖索引。

2. 减少磁盘I/O

由于覆盖索引避免了回表操作，因此可以显著减少磁盘I/O。磁盘I/O通常是查询性能的主要瓶颈，因此减少I/O操作能够极大地提高查询效率。

3. 更快的查询执行

由于索引数据通常比完整的数据行更小，索引可以存储在更少的页面中。这样，在读取索引时，可以一次性从磁盘读取更多的数据，进一步提高查询的执行速度。

如何创建覆盖索引？

要创建一个覆盖索引，你需要确保索引包含查询中使用的所有列。你可以通过在单个索引中组合多个列来实现这一点。

示例：创建覆盖索引

CREATE INDEX idx_covering_employee ON employees (department, employee_name, salary);

在这个例子中，索引 idx_covering_employee 包含了 department, employee_name 和 salary 三个列。对于涉及这些列的查询，idx_covering_employee 将是一个覆盖索引。

如何确定查询是否使用了覆盖索引？

可以使用 EXPLAIN 语句来查看查询计划，并确定查询是否使用了覆盖索引。在 EXPLAIN 的输出中，如果看到 Extra 列包含 Using index，说明查询使用了覆盖索引。

示例：检查覆盖索引

EXPLAIN SELECT employee_name, salary FROM employees WHERE department = 'HR';

结果示例：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	employees	ref	idx_employee	idx_employee	152	const	10	Using index

在 Extra 列中看到 Using index，说明查询完全使用了索引，并且是一个覆盖索引。

覆盖索引的局限性

索引大小：覆盖索引通常包含多个列，这可能导致索引的大小显著增加，占用更多的存储空间。
维护成本：每次插入、更新或删除操作都可能影响索引，因此更多的列意味着更高的维护成本。

总结

覆盖索引 是一种索引类型，当查询中的所有列都在索引中时，称该索引为覆盖索引。
提高查询性能：覆盖索引通过减少回表操作、降低磁盘I/O、加快查询执行速度来提高查询性能。
创建覆盖索引：确保索引包含查询中涉及的所有列，通过合理设计索引结构，可以实现覆盖索引。

覆盖索引是优化MySQL查询性能的有效工具之一，但在设计索引时应权衡索引的大小和维护成本。

阅读全文