MySQL 覆盖索引优化详解
在深入探讨覆盖索引之前，我们先来回顾一下聚集索引（主键索引）和辅助索引（二级索引）。
索引基础
聚集索引 (主键索引)
聚集索引是根据表的主键构建的一棵 B+ 树。其叶子节点直接存储了整张表的数据记录。因此，聚集索引的叶子节点被称为数据页，数据本身也成为索引的一部分。
辅助索引 (二级索引)
辅助索引并非基于主键。其叶子节点存储的是索引键值以及一个"书签"。在 InnoDB 存储引擎中，这个书签就是对应行数据的主键索引值。
什么是覆盖索引？
覆盖索引（Covering Index）是指查询语句中需要的数据列（包括 SELECT、WHERE、ORDER BY 等子句）都能从索引中直接获取，而无需回溯到主表读取数据。换句话说，查询所需的数据被索引"覆盖"了。
- 核心概念： 查询所需字段均包含在索引中。
- 效率提升： 避免了回表查询，显著减少 I/O 操作。
- 适用索引类型： 覆盖索引仅适用于能够存储索引列值的索引类型。如哈希索引、全文索引和空间索引因不存储列值，无法实现覆盖索引。MySQL 主要依赖 B-Tree 索引实现覆盖索引。
当查询能够利用覆盖索引时，通过 EXPLAIN 命令查看执行计划，在 Extra 列会显示 Using index。
-- 示例：通过 EXPLAIN 确认覆盖索引
EXPLAIN SELECT column1, column2 FROM your_table WHERE condition_column = 'value';
Using index 表明该查询仅通过索引即可获取所有所需数据，无需访问实际的数据行，这就是覆盖索引的体现。
覆盖索引优化场景
1. 无 WHERE 条件的查询优化
考虑一个不带 WHERE 子句的聚合查询：
-- 原始查询
SELECT COUNT(staff_id) FROM t1;
其执行计划可能显示 type: ALL，意味着全表扫描。为了优化，可以为 staff_id 列创建索引：
-- 创建索引
ALTER TABLE t1 ADD KEY(staff_id);
优化后的执行计划：
EXPLAIN SELECT COUNT(staff_id) FROM t1;
执行计划将显示 type: index 和 Extra: Using index。虽然没有 WHERE 条件，但通过索引的额外优势——减少读取的数据块数量，依然实现了优化。
注意： 对于无 WHERE 条件的查询，要实现索引覆盖，查询返回的字段数必须非常少。避免 SELECT *，因为过长的索引会影响性能。
2. 二次检索优化
考虑一个查询，它在 inventory_id 上进行范围查询，并返回 rental_date：
-- 查询语句
SELECT rental_date FROM t1 WHERE inventory_id < 80000;
执行计划可能显示 Extra: Using index condition。这表示 MySQL 使用了索引（如 inventory_id 索引），但由于 rental_date 不在索引中，仍然需要回表查询（"二级检索"），这会带来一定的性能开销。
-- 优化：创建联合索引
ALTER TABLE t1 ADD KEY(inventory_id, rental_date);
优化后的执行计划：
EXPLAIN SELECT rental_date FROM t1 WHERE inventory_id < 80000;
此时，Extra 列应显示 Using index，表明查询已被覆盖，无需回表。
3. 分页查询优化
分页查询，特别是涉及 ORDER BY 和 LIMIT 时，如果未优化，可能导致全表扫描和额外的排序操作，性能较低。
-- 分页查询
SELECT tid, return_date FROM t1 ORDER BY inventory_id LIMIT 50000, 10;
在未优化前，执行计划可能显示 type: ALL (全表扫描)。
优化方法： 创建一个包含排序字段和返回字段的联合索引。由于 tid 是主键，一个包含 inventory_id 和 return_date 的索引可以满足覆盖要求。
-- 创建联合索引
ALTER TABLE t1 ADD INDEX liu(inventory_id, return_date);
优化后的查询速度将显著提升，执行计划会显示 type: index 和 Extra: Using index，表明通过索引直接获取数据，无需回表，也避免了排序。
另一种优化思路（消除排序）： 通过子查询和 JOIN 来利用索引进行排序。
-- 优化 SQL 示例 (需谨慎使用，取决于数据量和具体场景)
SELECT a.tid, a.return_date
FROM t1 a
INNER JOIN (
SELECT tid
FROM t1
ORDER BY inventory_id
LIMIT 800000, 10
) b ON a.tid = b.tid;
但这种方法仍可能涉及回表查询。如果 SELECT 返回的列较少且数据宽度较小，直接创建包含查询和返回列的复合索引通常是更优的选择。
4. 建了索引但查询不走索引
有时，即使为某个字段创建了索引，查询语句（特别是涉及 ORDER BY 和返回非索引列的查询）可能仍然不使用该索引。
场景：
CREATE TABLE `t_order` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`order_code` char(12) NOT NULL,
`order_amount` decimal(12,2) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uni_order_code` (`order_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 查询语句
SELECT order_code, order_amount FROM t_order ORDER BY order_code LIMIT 1000;
尽管 order_code 有索引，但如果查询返回 order_amount，且 order_amount 未包含在索引中，MySQL 可能会选择全表扫描或不使用 order_code 索引进行排序，因为需要回表读取 order_amount，这涉及随机 I/O。
解决方案： 创建一个覆盖查询所需所有列的复合索引。
-- 创建覆盖索引
ALTER TABLE `t_order` ADD INDEX `idx_ordercode_orderamount` USING BTREE (`order_code` ASC, `order_amount` ASC);
这样，查询就能利用该复合索引，实现覆盖查询。
覆盖索引的优势与限制
优势
- 减少 I/O： 索引通常比数据行小，读取索引比读取数据行需要更少的 I/O。
- 数据局部性： 索引按键值排序存储，相比随机访问数据行，I/O 更加顺序化。
- 缓存友好： 存储引擎（如 MyISAM）可以更有效地缓存索引。
- InnoDB 特别优势： 对于 InnoDB，如果二级索引包含了查询所需所有数据，则无需回溯到聚集索引查找，极大提高效率。
限制
- 索引类型限制： 仅适用于存储列值的索引类型（如 B-Tree）。哈希索引、全文索引等不适用。
- 存储引擎支持： 不同存储引擎对覆盖索引的支持程度可能不同。
- 避免 SELECT *： 覆盖索引的目的是只包含查询必需的列。使用
SELECT *会导致索引文件过大，反而降低性能。
