覆盖索引

一、覆盖索引

1. 定义

覆盖索引是指查询使用了索引，且需要返回的所有列都能在该索引中找到，无需回表查询数据。

2. 示例说明

假设表tb_user中，id为主键索引，name为普通索引：

• SELECT * FROM tb_user WHERE id = 1;：使用主键索引，叶子节点直接存储完整数据，属于覆盖索引。
• SELECT id, name FROM tb_user WHERE name = 'Arm';：name索引的叶子节点包含name和主键id，查询字段全部在索引中，属于覆盖索引。
• SELECT id, name, gender FROM tb_user WHERE name = 'Arm';：gender字段不在name索引中，需要通过主键回表查询，不属于覆盖索引。

3. 核心优势

• 避免回表查询，减少磁盘IO次数，大幅提升查询性能。
• 仅需一次索引扫描即可获取所有数据，效率极高。
• 可通过避免SELECT *，只查询必要字段，更容易触发覆盖索引。

---

二、超大分页问题与优化

1. 什么是超大分页

指LIMIT分页查询中，偏移量（offset）非常大的场景，例如：

SELECT * FROM tb_sku LIMIT 9000000, 10;

MySQL需要先扫描前9000000条数据并丢弃，再返回后10条数据，效率极低。

2. 优化方案：覆盖索引+子查询

优化思路

先通过覆盖索引（主键索引）快速定位到目标数据的主键，再通过主键查询完整数据，避免大量数据扫描。

优化示例

SELECT *
FROM tb_sku t,
     (SELECT id FROM tb_sku ORDER BY id LIMIT 9000000, 10) a
WHERE t.id = a.id;

• 子查询SELECT id FROM tb_sku ORDER BY id LIMIT 9000000, 10：利用主键索引的有序性，快速定位目标主键（覆盖索引，无回表）。
• 主查询通过主键直接获取完整数据，避免了对前900万条数据的全表扫描。

---

面试相关问题

1. 问：什么是覆盖索引？它的作用是什么？ 答：覆盖索引是指查询所需的所有列都能在索引中找到，无需回表查询。作用是避免回表，减少IO次数，提升查询性能。

2. 问：如何判断一个查询是否使用了覆盖索引？ 答：通过EXPLAIN执行计划的Extra字段判断，若显示Using index，则表示使用了覆盖索引，无需回表。

3. 问：MySQL超大分页为什么会慢？如何优化？ 答：超大分页（如LIMIT 9000000, 10）需要先扫描并丢弃前900万条数据，效率极低。优化方案是使用覆盖索引+子查询，先通过索引定位目标主键，再通过主键查询完整数据，避免大量扫描。

4. 问：为什么SELECT *容易导致无法使用覆盖索引？ 答：SELECT *会查询所有字段，而普通二级索引的叶子节点仅存储索引列和主键，无法包含所有字段，因此必须回表查询，无法触发覆盖索引。

5. 问：覆盖索引在什么场景下效果最明显？ 答：在二级索引查询、分页查询、统计查询等场景下效果最明显，尤其是避免回表查询和优化超大分页时，能显著提升性能。