性能优化（执行计划 / 慢查询 / 常见陷阱） · SQL 改写优化

深分页 LIMIT 100000, 20 怎么优化？(子查询主键、记录上次最大主键、覆盖索引)

How to optimize deep pagination like LIMIT 100000, 20? (

答案语言

考察要点

这道题旨在考察候选人对数据库底层工作原理的理解深度，而不仅仅是会写 SQL。面试官想看你是否懂：

LIMIT OFFSET 的工作机制：是否知道 MySQL 在处理大 OFFSET 时，需要先读取并丢弃 OFFSET 数量的行，导致了巨大的 I/O 和 CPU 成本。
索引与数据访问：是否理解覆盖索引（Covering Index）如何避免回表（Index Lookup），以及回表是主要的性能瓶-颈之一。
SQL 改写与优化能力：能否将一个性能低下的查询，通过逻辑等价但执行方式更优的 SQL 进行改写，展现出解决实际问题的能力。

核心概念

1. `LIMIT` M, N 的性能陷阱

LIMIT 100000, 20 实际上是 LIMIT 20 OFFSET 100000 的简写。MySQL（以 InnoDB 为例）的执行流程如下：

数据获取：根据 WHERE 和 ORDER BY 条件，利用索引（如果存在）或者全表扫描，从存储引擎逐行读取数据。
行计数与丢弃：在 Server 层，对读取的每一行进行计数，直到丢弃了前 100,000 行。
返回结果：继续向后读取 20 行，作为最终结果集返回给客户端。

性能瓶颈：MySQL 必须真实地读取 100,020 行数据，即使其中 100,000 行最终被丢弃。如果查询需要回表（即二级索引无法覆盖所有查询所需列），这将导致 100,020 次随机 I/O，随着 OFFSET 增大，性能会急剧下降，呈线性恶化。

2. 覆盖索引 (`Covering Index`)

当一个索引包含了查询所需的所有列时（SELECT、WHERE、ORDER BY、GROUP BY 涉及的列），该索引就被称为覆盖索引。

优点：查询可以直接从索引中获取所有数据，无需再根据主键去聚集索引中查找完整的行数据（即“回表”）。这大大减少了 I/O 操作，特别是随机 I/O。

示例：

sql

 1-- 原始表结构
 2CREATE TABLE `orders` (
 3  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 4  `user_id` INT UNSIGNED NOT NULL,
 5  `status` TINYINT NOT NULL,
 6  `amount` DECIMAL(10, 2) NOT NULL,
 7  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 8  PRIMARY KEY (`id`),
 9  KEY `idx_status_createtime` (`status`, `create_time`)
10) ENGINE=InnoDB;
11
12-- 这个查询无法使用覆盖索引，因为 SELECT * 需要 amount, user_id 等列
13SELECT * FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 100000, 20;
14
15-- 这个查询可以使用覆盖索引 idx_status_createtime，因为它只需要 id 列，而主键 id 默认包含在二级索引中
16SELECT id FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 100000, 20;

3. 书签/游标法 (Keyset Pagination)

这是一种无 OFFSET 的分页方式。客户端在请求下一页时，需要带上上一页最后一条记录的排序键值。

原理：将 OFFSET 的“跳过 N 行”操作，转化为 WHERE 条件的“从特定位置开始查找”操作。

示例：

第一页：

sql

 1SELECT id, create_time FROM orders WHERE status = 1 ORDER BY create_time DESC, id DESC LIMIT 20;

假设返回的最后一条记录是 (id=54321, create_time='2023-10-26 10:00:00')。

请求第二页：

sql

 1SELECT id, create_time
 2FROM orders
 3WHERE status = 1
 4  AND (create_time < '2023-10-26 10:00:00' OR (create_time = '2023-10-26 10:00:00' AND id < 54321))
 5ORDER BY create_time DESC, id DESC
 6LIMIT 20;

注意：ORDER BY 和 WHERE 条件中必须包含一个唯一键（如主键 id）作为 tie-breaker，防止 create_time 重复时导致数据错乱或丢失。

多种解法

假设我们的慢查询是：

sql

 1-- 慢查询：需要优化的目标
 2-- 索引: KEY `idx_status_createtime` (`status`, `create_time`)
 3SELECT id, user_id, status, amount, create_time
 4FROM orders
 5WHERE status = 1
 6ORDER BY create_time DESC
 7LIMIT 100000, 20;

解法 1：延迟关联 (Deferred Join)

思路：先通过覆盖索引快速定位到目标 20 条记录的主键 id，这个过程非常快，因为没有回表。然后将这 20 个 id 作为结果集，与原表进行 JOIN，获取完整的行数据。

版本：✅ 所有 MySQL 版本均支持。

SQL 代码：

sql

 1SELECT o1.*
 2FROM orders AS o1
 3JOIN (
 4    -- 步骤1: 使用覆盖索引快速定位ID
 5    SELECT id
 6    FROM orders
 7    WHERE status = 1
 8    ORDER BY create_time DESC
 9    LIMIT 100000, 20
10) AS o2 ON o1.id = o2.id;

复杂度：
- 子查询：在索引上进行扫描，成本远低于回表。复杂度近似 $O(\text{offset} + \text{limit})$ 的索引扫描。
- JOIN：对 20 个主键进行 JOIN，相当于 20 次主键查找，成本固定且极低。
索引依赖：必须有一个覆盖子查询的索引。对于本例，idx_status_createtime (status, create_time) 加上 InnoDB 自动附加的主键 id，正好覆盖了 SELECT id ... WHERE status ... ORDER BY create_time。

解法 2：书签/游标法 (Keyset Pagination)

思路：放弃 OFFSET，改造为基于上一页最后一条记录的 WHERE 条件查询。这适用于“加载更多”式的无限滚动场景，但不适用于需要跳转到任意页码的场景。

版本：✅ 所有 MySQL 版本均支持。

SQL 代码：假设前端从上一页获取了最后一条记录的 last_create_time 和 last_id。

sql

 1SELECT id, user_id, status, amount, create_time
 2FROM orders
 3WHERE status = 1
 4  AND (create_time < '...' OR (create_time = '...' AND id < ...)) -- 传入 last_create_time 和 last_id
 5ORDER BY create_time DESC, id DESC
 6LIMIT 20;

复杂度：时间复杂度与页码无关，始终是 $O(\log N + \text{limit})$ ，其中 $\log N$ 是索引树的查找深度。性能极高且稳定。
索引依赖：需要一个复合索引 (status, create_time, id) 以获得最佳性能。

常见低分回答

只提问题不给方案：“LIMIT 大偏移量很慢，因为要扫很多数据”，但说不出具体的优化方法。
在 WHERE 子句的列上使用函数：SELECT * FROM orders WHERE DATE(create_time) = '2023-10-26' LIMIT 100000, 20; 这会导致 create_time 列上的索引失效，是典型的反模式。
优化方案不彻底：提出“加索引 (status, create_time)”，这确实是第一步，但没有解决大 OFFSET 导致的大量回表问题，只解决了 filesort 的问题。
书签法忘记 Tie-Breaker：WHERE create_time < '...'，如果 create_time 有重复值，会导致分页数据不准（可能跳过或重复数据）。
滥用子查询：写出 SELECT * FROM orders WHERE id IN (SELECT id FROM ...) 这样的语句。虽然逻辑上可行，但在某些旧版 MySQL 中，IN 子查询的优化很差，可能会被转化为相关子查询，性能还不如 JOIN。

工程实践与优化

场景权衡：
- 延迟关联：适用于需要保留传统页码跳转功能（如 /list?page=5001）的后台管理、报表系统。
- 书签法：是移动端“无限滚动”、“加载更多”场景的唯一正确选择，性能最好。
产品层面的妥协：与产品经理沟通，是否真的需要提供到第 5001 页的功能？大部分场景下，用户不会访问这么深的页码。可以限制最大可访问页数（如 Google 只显示到前几十页），引导用户使用更精确的筛选条件来缩小结果集。
执行计划分析：
- 优化前：EXPLAIN 会显示 type: range，key: idx_status_createtime，但 rows 会是一个非常大的数字（约 100020）。
- 延迟关联：EXPLAIN 会显示一个 DERIVED 表。对派生表 o2 的查询，Extra 字段应为 Using index，表明使用了覆盖索引。外层查询 o1 的 type 是 eq_ref 或 ref，rows 接近 1，表明是高效的主键关联。
- 书签法：EXPLAIN 会显示 type: range，但 rows 会是一个非常小的数字（接近 20），表明它只扫描了必要的行。

常见追问与延伸题目

追问：如果数据量增长 100 倍，这几种方案的性能表现如何？
- 回答要点：
  - 原始慢查询：性能会灾难性下降。扫描和丢弃的行数等比例增长，查询时间可能也增长近 100 倍。
  - 延迟关联：性能会下降，但远好于原始方案。子查询的索引扫描部分会变慢（因为索引树更大），但决定性因素——回表次数，仍然固定为 20。
  - 书签法：性能几乎不受影响。B+ 树索引的查找时间复杂度为 $O(\log N)$ ，数据量增长 100 倍，树的高度可能只增加 1-2 层，查询时间几乎不变。这是扩展性最好的方案。
追问：如果 ORDER BY 的列 create_time 没有索引，会发生什么？如何处理？
- 回答要点：
  - 发生什么：MySQL 无法利用索引的有序性，必须先根据 WHERE status = 1 找出所有匹配的行，然后在内存或临时文件中（Using filesort）对这些行进行排序，最后再进行 LIMIT 操作。这个过程对 CPU 和 I/O 都是巨大消耗，性能极差。
  - 如何处理：首要且必须的步骤是创建合适的索引。CREATE INDEX idx_status_createtime ON orders (status, create_time); 这是解决排序性能问题的根本。
追问：如果换成 PostgreSQL 或者分布式数据库如 TiDB，这个问题还存在吗？解决方案有何不同？
- 回答要点：
  - PostgreSQL：问题同样存在，OFFSET 的实现原理类似，大偏移量同样导致性能问题。优化思路完全一致：延迟关联（在 PG 中称为 LATERAL JOIN 或 JOIN 子查询）和 Keyset Pagination（书签法）都是标准且高效的解决方案。
  - TiDB/OceanBase (分布式数据库)：问题不仅存在，而且可能更严重。因为数据分布在多个节点上，大 OFFSET 意味着需要从多个存储节点捞取大量数据到计算节点进行排序和丢弃，网络开销和计算压力更大。因此，延迟关联和书签法在这类数据库中是更加重要的优化手段，能有效减少跨节点的流量和计算量。其 SQL 语法和优化思路与 MySQL 基本兼容。