MySQL 高性能分页查询实战指南（含 Explain 分析 + 经典写法）

面试官：你怎么优化分页查询？
你：覆盖索引 + 子查询定位 + 延迟关联，一套组合拳，干就完了！

一、覆盖索引（Covering Index）

✅ 什么是覆盖索引？

当一个索引包含了查询所需的所有字段，不需要回表查聚簇索引的数据，称为“覆盖索引”。

比如：

-- 假设 email 是普通索引
SELECT email FROM users WHERE email = 'abc@example.com';

上面查询只用 email 字段，不需要访问表内其他数据，直接在索引上就能拿到结果 → 避免回表。

✅ 如何实现？

创建联合索引，将查询字段和 WHERE 条件字段都放进去。

例：

-- 假设业务频繁用 email 查询 name
CREATE INDEX idx_email_name ON users(email, name);

-- 然后执行：
SELECT name FROM users WHERE email = 'abc@example.com';

📌 查询字段 name 与过滤字段 email 都在联合索引中，查询时可完全在索引中完成，无需回表。

✅ 如何验证是否生效？

使用 EXPLAIN 查看 Extra 字段是否有：

Using index   ✅ --> 覆盖索引
Using where   ❌ --> 说明还要过滤
Using temporary, filesort ❌ --> 说明没有用到索引

二、超大分页优化（比如 page = 10000）

🧨 问题背景

常规分页：

SELECT * FROM articles ORDER BY id LIMIT 10000, 10;

会跳过前 10000 条 → 性能极差（全表扫描 + 数据丢弃）

✅ 优化方案一：子查询定位 + 延迟关联（推荐）

-- 第一步：先查出目标页的起始ID
SELECT id FROM articles ORDER BY id LIMIT 10000, 10;

-- 第二步：再回表查整行数据
SELECT * FROM articles WHERE id IN (
  结果中的10个id
);

📌 优点：

• 第一步在索引上完成，速度快
• 第二步只查10条，不会有全表扫描

✅ 优化方案二：使用“游标式”分页（适合大表）

-- 从上一页最后一条记录开始
SELECT * FROM articles WHERE id > 上一页最后的ID ORDER BY id LIMIT 10;

📌 原理：利用 索引的顺序性，比 offset 更高效。

✅ 优化方案三：只查 ID + join 查数据（延迟加载）

SELECT a.* FROM (
  SELECT id FROM articles ORDER BY id LIMIT 10000, 10
) t
JOIN articles a ON a.id = t.id;

📌 这是子查询 + join 延迟关联的一种写法，效率也很高。

三、常见面试高频问答

Q：什么是覆盖索引？如何避免回表？
A：查询字段都在索引中时，叫覆盖索引，不需要回表。设计联合索引时将查询字段一起放入即可。

Q：分页查10000页很慢怎么办？
A：使用 ID 游标法（WHERE id > ?）或子查询 + 延迟关联，减少数据跳过和回表次数。

四、总结一句话 🚀

覆盖索引是减少 IO 的利器，超大分页优化是高性能接口的必备技能！

📚 索引创建的8条黄金原则（MySQL为例）

✅ 原则一：最左前缀原则

联合索引中遵循“从左到右”的顺序使用

CREATE INDEX idx_name_age ON users(name, age);

• ✅ WHERE name = '张三' → 命中索引
• ✅ WHERE name = '张三' AND age = 18 → 命中索引
• ❌ WHERE age = 18 → 索引失效！

💡 联合索引本质上是一个排序的 B+ 树结构，只有从最左字段起连续使用才会触发索引。

✅ 原则二：选择性高的列建索引

选择性 = 唯一值 / 总行数，越接近1越合适建索引

例子：

• 手机号：高选择性，适合建索引 ✅
• 性别：选择性低，索引作用不大 ❌

✅ 原则三：频繁出现在 WHERE、JOIN、ORDER BY、GROUP BY 的字段优先建索引

SELECT * FROM orders WHERE user_id = 123;  -- ✅
SELECT * FROM orders ORDER BY created_at DESC;  -- ✅

它们都会触发全表扫描或排序临时表，用索引能有效避免。

✅ 原则四：避免对索引字段进行函数或表达式操作

-- ❌ 会导致索引失效
WHERE DATE(created_at) = '2024-01-01'
WHERE price + 10 > 100

-- ✅ 改写方式
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-02'
WHERE price > 90

✅ 原则五：避免在索引字段上使用不等于（<>、!=）、LIKE ‘%xxx’ 等模糊匹配

-- ❌ LIKE '%abc' 会导致索引失效
-- ✅ LIKE 'abc%' 可以使用索引

✅ 原则六：小表不需要建索引，频繁更新的字段慎用索引

• 小表全表扫描比建索引更快（例如字典表）
• 经常更新的字段如果建立索引，会有大量写开销

✅ 原则七：覆盖索引优先，减少回表

把查询字段都包含在索引中，避免回表开销

-- 索引包含所有查询字段
CREATE INDEX idx_user_email ON users(name, email);

SELECT email FROM users WHERE name = '张三';  -- ✅ 覆盖索引

✅ 原则八：控制索引数量，适度而非越多越好

• 每增加一个索引，写入时多维护一棵 B+ 树
• 会增加 INSERT / UPDATE / DELETE 的性能开销
• 推荐控制在3~5个有效索引，做定期审查

🧠 面试官常问：

Q：如何判断哪些字段需要建索引？

答：结合实际查询频率、慢 SQL 分析、字段选择性、排序分组操作、回表判断等综合考量。

Q：如何知道索引是否生效？

答：用 EXPLAIN 看 type 是否为 ref 或 range、Extra 中是否有 Using index。

✅ 总结：建索引不是越多越好，遵循“高频、高选择性、参与条件判断”的标准，适配业务才是王道！

如果你需要我帮你写一套“索引优化实战案例 + explain解析 + 图解B+树结构”，我可以继续安排成系列文章📘。需要的话跟我说一声～