如何设计和优化MySQL中的大表分页查询方案-mysql教程-PHP中文网

如何设计和优化mysql中的大表分页查询方案

设计和优化MySQL中的大表分页查询，核心在于规避传统

LIMIT OFFSET

登录后复制

在大偏移量下的性能瓶颈，转而采用更高效的基于索引的查询策略，也就是我们常说的键集（Keyset）分页，辅以合理的索引设计和查询优化，确保数据获取的效率与准确性。这不仅仅是技术选择，更是对用户体验和系统资源消耗的深思熟虑。

解决方案

处理MySQL大表分页查询，我们首先要认识到

LIMIT offset, limit

登录后复制

模式的局限性。当

offset

登录后复制

值非常大时，数据库需要扫描

offset + limit

登录后复制

行，然后丢弃前面的

offset

登录后复制

行，这在数据量巨大时会造成显著的性能下降。因此，解决方案的核心是避免这种全量扫描，转而利用索引的有序性，从上一次查询的“末尾”继续向后查找。

最推荐的方案是键集（Keyset）分页，也被称为“游标分页”或“基于主键/唯一键分页”。这种方法不是通过跳过行数来定位，而是通过记录上一页最后一条记录的某个唯一标识（如主键ID或排序字段的组合），作为下一页查询的起始点。

例如，如果你的表有一个自增主键

id

登录后复制

，并且你希望按

id

登录后复制

升序分页：第一页：

SELECT * FROM your_table ORDER BY id ASC LIMIT 10;

登录后复制

假设第一页最后一条记录的

id

登录后复制

是100。第二页：

SELECT * FROM your_table WHERE id > 100 ORDER BY id ASC LIMIT 10;

登录后复制

以此类推。

如果你的排序字段不是唯一的，或者需要多字段排序，例如按

create_time

登录后复制

降序，再按

id

登录后复制

降序：第一页：

SELECT * FROM your_table ORDER BY create_time DESC, id DESC LIMIT 10;

登录后复制

假设第一页最后一条记录的

create_time

登录后复制

是'2023-10-26 10:00:00'，

id

登录后复制

是500。第二页：

SELECT * FROM your_table WHERE (create_time < '2023-10-26 10:00:00') OR (create_time = '2023-10-26 10:00:00' AND id < 500) ORDER BY create_time DESC, id DESC LIMIT 10;

登录后复制

这种复合条件查询利用了MySQL的行比较（tuple comparison）特性，能够高效地定位下一页的起始位置。

这种方案的关键在于，

WHERE

登录后复制

子句能够直接利用索引进行快速定位，避免了对大量不必要行的扫描和排序，性能表现与

offset

登录后复制

大小无关，始终保持高效。

LIMIT OFFSET

登录后复制

在大表查询中为什么效率低下？

这个问题其实挺直观的，但很多人在实际开发中容易忽视。当我们在MySQL里写

SELECT * FROM some_table ORDER BY some_column LIMIT 100000, 10;

登录后复制

这样的语句时，数据库引擎并不是魔术般地直接跳到第100000条记录。它实际上需要做的是：首先，根据

ORDER BY

登录后复制

子句对所有符合条件的行进行排序（如果数据量大，这本身就是个耗时操作，可能涉及文件排序）。接着，它会从排序后的结果集中，从第一行开始，逐行扫描，直到它跳过了100000行。只有在跳过这些行之后，它才会开始收集接下来的10行数据。

这就像你让一个朋友去图书馆找一本在第10万页的某个单词。他不能直接翻到第10万页，他得一页一页地翻过去，直到找到那一页。在这个过程中，前面99999页的内容他都看了，但都是为了“跳过”而看。对于数据库来说，这意味着大量的磁盘I/O和CPU计算资源被消耗在那些最终会被丢弃的数据上。特别是当

offset

登录后复制

值变得巨大时，这种浪费就非常惊人，查询响应时间会急剧增加，甚至可能导致数据库负载过高。在我看来，这简直就是一种资源浪费的典范，尤其在互联网应用中，用户可不会等那么久。

如何基于键集（Keyset）实现高效分页？

键集分页，或者说游标分页，是我个人在处理大表分页时最喜欢用的方案，因为它真的能带来质的飞跃。它的核心思想是“我不需要知道我是第几页，我只需要知道上一页的最后一项是什么，然后从那里开始找下一页”。这和我们日常阅读一本书，记住上次读到哪里，下次从那儿接着读，是异曲同工的。

具体实现上，我们不再使用

offset

登录后复制

。取而代之的是，我们利用一个或一组唯一标识符（键）来标记当前页的结束位置，作为下一页查询的起始点。

1. 基于单一主键（通常是自增ID）的键集分页： 这是最简单也最常见的场景。假设你的表

products

登录后复制

有一个自增主键

id

登录后复制

。

首次查询（第一页）：
```
SELECT id, name, price FROM products ORDER BY id ASC LIMIT 20;
```
登录后复制
假设查询结果的最后一条记录的
```
id
```
登录后复制
是
```
last_id_on_page_1
```
登录后复制
。

美图设计室
5分钟在线高效完成平面设计，AI帮你做设计

29

查看详情
查询下一页：
```
SELECT id, name, price FROM products WHERE id > last_id_on_page_1 ORDER BY id ASC LIMIT 20;
```
登录后复制
这种方式利用了
```
id
```
登录后复制
上的B+树索引，MySQL可以直接定位到
```
last_id_on_page_1
```
登录后复制
之后的第一个记录，然后顺序读取20条，效率极高。

2. 基于复合键（多列）的键集分页： 当你的排序条件不止一个，或者排序字段本身不唯一时，就需要用到复合键。例如，按

created_at

登录后复制

降序，然后按

id

登录后复制

降序。

首次查询（第一页）：

SELECT id, name, created_at FROM orders ORDER BY created_at DESC, id DESC LIMIT 20;

登录后复制

假设查询结果的最后一条记录是

(last_created_at_on_page_1, last_id_on_page_1)

登录后复制

。

查询下一页：
```
SELECT id, name, created_at
FROM orders
WHERE (created_at < 'last_created_at_on_page_1') OR
      (created_at = 'last_created_at_on_page_1' AND id < last_id_on_page_1)
ORDER BY created_at DESC, id DESC
LIMIT 20;
```
登录后复制
这里利用了MySQL的行比较特性。
```
WHERE
```
登录后复制
子句的逻辑是：找到
```
created_at
```
登录后复制
比上一页最后一条记录的
```
created_at
```
登录后复制
更小的记录（因为是降序），或者
```
created_at
```
登录后复制
相同但
```
id
```
登录后复制
更小的记录。这要求
```
created_at
```
登录后复制
和
```
id
```
登录后复制
上有一个复合索引，例如
```
INDEX (created_at, id)
```
登录后复制
，这样查询才能高效地利用索引。

优点： 性能稳定，无论翻到多远的页，查询效率几乎不变。它避免了扫描大量无用数据。 缺点： 无法直接跳到任意页（比如“第500页”）。用户只能“下一页”、“上一页”地浏览。这通常需要在产品设计上进行权衡，很多时候用户并不真的需要跳到任意页。

除了键集分页，还有哪些优化策略和注意事项？

当然，键集分页虽好，但它也不是万能药，尤其是在一些特定的业务场景下，比如用户真的需要快速跳转到某个特定页码。即便如此，我们还有其他一些辅助策略和需要注意的地方，来共同构建一个健壮的分页方案。

1. 索引的精确设计： 这是任何查询优化的基石。确保你的

ORDER BY

登录后复制

和

WHERE

登录后复制

子句中使用的字段都有合适的索引。

覆盖索引 (Covering Index): 如果你的
```
SELECT
```
登录后复制
列表只包含索引中的字段，那么MySQL可以直接从索引中获取所有需要的数据，而无需回表查询实际数据行。这能极大提升性能。
```
-- 假设你经常查询id, name, created_at，并且分页通常是按created_at排序
CREATE INDEX idx_created_at_id_name ON orders (created_at, id, name);
-- 如果name字段不需要排序，但经常被查询，可以作为索引的额外列
```
登录后复制
如果只查询
```
id
```
登录后复制
和
```
created_at
```
登录后复制
，那么
```
INDEX (created_at, id)
```
登录后复制
就是一个覆盖索引。
复合索引的顺序： 复合索引的列顺序很重要。通常，将最常用于
```
WHERE
```
登录后复制
子句过滤的列放在前面，然后是
```
ORDER BY
```
登录后复制
子句中的列。

*2. 避免 `SELECT `：** 这是一个老生常谈但极其重要的优化点。只查询你真正需要的列。减少数据传输量可以显著降低网络I/O和数据库的内存消耗。尤其是在大表分页中，每一行的数据量累积起来都可能很可观。

3. 对

LIMIT OFFSET

登录后复制

的“曲线救国”式优化： 如果业务场景确实无法放弃任意页码跳转，并且

LIMIT OFFSET

登录后复制

性能瓶颈明显，可以考虑一种结合了子查询的优化方法。它的思路是先通过主键或索引快速定位到需要的

id

登录后复制

范围，然后再回表查询完整数据。

SELECT t.*
FROM your_table AS t
JOIN (
    SELECT id FROM your_table ORDER BY id ASC LIMIT 100000, 10
) AS sub ON t.id = sub.id;

登录后复制

这种方式的原理是，内层的子查询

SELECT id FROM your_table ORDER BY id ASC LIMIT 100000, 10

登录后复制

只查询

id

登录后复制

，如果

id

登录后复制

上有索引，这个查询会相对较快，因为它只涉及索引扫描，不回表。外层再通过

JOIN

登录后复制

将这些

id

登录后复制

对应的完整数据取出来。虽然比纯粹的

LIMIT OFFSET

登录后复制

有所改善，但它依然需要扫描

offset + limit

登录后复制

个索引项，所以在超大偏移量下，性能依然不如键集分页。

4. 缓存策略的引入： 对于那些数据不经常变动，但访问频率很高的分页查询结果，可以考虑在应用层（比如使用Redis）进行缓存。第一次查询时将结果缓存起来，后续请求直接从缓存中获取。但这需要处理好缓存的失效和一致性问题。

5. 业务需求与技术实现的权衡： 很多时候，用户真的需要跳转到“第500页”吗？还是说“上一页/下一页”的浏览体验已经足够？产品设计上，可以引导用户使用更符合键集分页特性的交互模式，比如无限滚动加载。这不仅能提升性能，也能带来更流畅的用户体验。我发现，很多时候所谓的“任意页码跳转”只是开发人员的惯性思维，而非用户的真实痛点。

6. 数据库层面的参数调整（谨慎使用）： 虽然不是首要优化手段，但了解一些数据库参数对分页性能的影响也是有益的，例如