需求
新闻网站、博客或其他列表页面可能包含过多条目，一页放不下。于是把它们分成每页 10 条，并提供“下一页”按钮。看到 MariaDB 的 OFFSET 和 LIMIT，你可能会直接采用下面的方式：
SELECT *
FROM items
WHERE messy_filtering
ORDER BY date DESC
OFFSET $M LIMIT $N实际需求是让用户通过“下一页”逐步浏览数据，未必需要“跳到第 N 页”。跳到首页或末页倒可能有用。
问题
列表只有少量数据时，一切正常。然而有 50,000 条数据、共 5,000 页时，有人可能遍历全部页面——例如搜索引擎爬虫。
问题在于性能。页面执行 SELECT ... OFFSET 49990 LIMIT 10，或等价的 LIMIT 49990,10，MariaDB 必须找到全部 50,000 行，跨过前 49,990 行，才返回那一页的 10 行。如果爬虫读取全部 5,000 页，累计触及的条目数约为 125,000,000。
为了获取靠后的页面而扫描整张表，会产生大量 I/O，可能让网页超时，也可能干扰其他工作，使其他操作变慢。
其他缺陷
- 浏览相邻页面期间，若有条目插入或删除，可能遗漏条目，也可能重复看到同一条目。
- 内容随着时间移动，分页地址不容易稳定地保存为书签或分享给别人。
- WHERE 条件与 ORDER BY 的组合，甚至可能让数据库为了第一页的 10 条数据也读取全部 50,000 条。
应该怎么做
升级硬件只能暂时缓解问题，数据继续增长后仍会遇到瓶颈。改进索引也不能改变“为了第 5,000 页读取全部数据”的根本问题。另建一张表记录各页起点，会增加昂贵且复杂的维护工作。
应避免基于大 OFFSET 的翻页，改为记住上一次浏览到的位置：
# First page (latest 10 items):
SELECT ... WHERE ... ORDER BY id DESC LIMIT 10
# Next page (second 10):
SELECT ... WHERE ... AND id < $left_off ORDER BY id DESC LIMIT 10有 INDEX(id) 时，这种访问方式会高效得多。
实现：去掉 OFFSET
原来的查询可能是 ORDER BY datetime DESC LIMIT 49990,10。表通常具有唯一的 id，可以考虑将它用作继续浏览的位置。
旧“下一页”地址可能类似 ?topic=xyz&page=4999&limit=10。topic、tag、provider、user 等参数决定显示哪个集合，page*limit 给出 OFFSET。limit 放在 URL 里还是硬编码，与本讨论无关。
新地址改成 ?topic=xyz&id=12345&limit=10。注意，不能从 4999 计算出 12345。通过 INDEX(topic, id)，可以高效执行这样的查询：
WHERE topic = 'xyz'
AND id >= 1234
ORDER BY id
LIMIT 10它只需访问 10 行，越靠后的页面，改进越明显。下面讨论具体细节。
实现：记录继续浏览的位置
如果当前页之后恰好没有更多数据，最好禁用或隐藏“下一页”。判断方法是用 LIMIT 11 替代 LIMIT 10：前 10 条显示在当前页，第 11 条说明还有下一页，并提供下一页的起点 id。
把第 11 个 id 写入“下一页”按钮：
<a href=?topic=xyz&id=$id11&limit=10>Next实现：不止“下一页”
可以扩展上述技巧，找到后续 5 页的起点并生成链接。
方案 A 使用 LIMIT 51。若当前是第 12 页，第 11 条的 id 对应第 13 页，第 51 条的 id 对应第 17 页。
方案 B 执行两次查询：一次取当前页的 10 条数据，另一次取后续 41 个 id，即 LIMIT 10,41，生成后面 5 页的链接。选择哪种方案取决于许多因素，应通过基准测试比较。
合理的分页链接
向前、向后各提供 5 页链接，工作量不大。两个方向的 id 需要分别查询。首页、末页链接也容易生成，无须具体 id：
<a href=?topic=xyz&id=FIRST&limit=10>First</a>
<a href=?topic=xyz&id=LAST&limit=10>Last</a>界面识别这些特殊值后，生成类似的 SELECT 条件：
WHERE topic = 'xyz'
ORDER BY id ASC -- ASC for First; DESC for Last
LIMIT 10末页的条目会按逆序返回。可以在界面层调整，也可以采用更复杂的查询：
( SELECT ...
WHERE topic = 'xyz'
ORDER BY id DESC
LIMIT 10
) ORDER BY id ASC假设有很多页，当前在第 12 页，可以显示：
[First] ... [7] [8] [9] [10] [11] 12 [13] [14] [15] [16] [17] ... [Last]这里的省略号就是界面中的省略号。边界情况如下：
# Page one of three:
First [2] [3]
# Page one of many:
First [2] [3] [4] [5] ... [Last]
# Page two of many:
[First] 2 [3] [4] [5] ... [Last]
# If you jump to the Last page, you don't know what page number it is.
# So, the best you can do is perhaps:
# [First] ... [Prev] Last为什么有效
目标是只访问相关行，而不扫描目标行之前的所有行。除了构造“后续 5 页”链接之外，通常能很好地达到这个目标。前述简单 SELECT id 能否高效构造这些链接，还取决于 WHERE 条件。
比较索引之前，先明确本文的假设：datetime 可能重复，这会引发问题；id 唯一；id 的顺序与 datetime 足够接近，能够代替 datetime 排序。
以下方式非常高效，所有工作都可以在索引中完成：
INDEX(topic, id)
WHERE topic = 'xyz'
AND id >= 876
ORDER BY id ASC
LIMIT 10,41它访问 51 个连续索引项，不访问数据行。
以下方式效率较低，因为还必须读取表数据：
INDEX(topic, id)
WHERE topic = 'xyz'
AND id >= 876
AND is_deleted = 0
ORDER BY id ASC
LIMIT 10,41它至少访问 51 个连续索引项，还要访问至少 51 个位置不连续的数据行。
调整索引后，可以恢复前一种方式的效率：
INDEX(topic, is_deleted, id)
WHERE topic = 'xyz'
AND id >= 876
AND is_deleted = 0
ORDER BY id ASC
LIMIT 10,41WHERE 中的等值条件对应索引最前面的列，之后的 >= 和 ORDER BY 都针对 id。这样，索引可以覆盖全部过滤条件与排序。
“共 12345 条，显示第 11—20 条”
数据量大时，通常不再精确统计总条数。可以改为显示：
Items 11-20 out of Many另一种方法是：只有少数搜索结果多得难以计数，因此另建表记录搜索条件和计数，由后台脚本每日或每小时计算。发现某个主题的数据量很大时，就查这张表，显示近似数量：
Items 11-20 out of about 49,000后台脚本可以对计数取整。快速获取 InnoDB 表行数估计值的方式是：
SELECT table_rows
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database_name'
AND TABLE_NAME = 'table_name'但这个估计无法考虑实际查询中可能存在的 WHERE 条件。
复杂 WHERE 或 JOIN
如果搜索条件无法限制在单张表的一个索引内，本文技巧就不适用了。作者另有讨论“Lists”的文章，介绍需要额外开发工作、但还能进一步改善性能的方案。
能快多少
这取决于总行数、WHERE 条件是否阻碍 ORDER BY 高效使用索引，以及数据是否大于缓存。当生成一页所需读取的磁盘数据超过缓存容量时，瓶颈会从 CPU 转成 I/O，页面加载可能突然慢一个数量级。
代价
- 无法任意跳到第 N 页。
- 从末尾向前浏览时，不知道页码。
- 代码更复杂。
后记与延伸阅读
方案约设计于 2007 年，文章发表于 2012 年。
来源与许可
原文：MariaDB：Pagination Optimization，作者 Rick James；MariaDB 文档经作者许可收录，原始出处：pagination。作者网站还有其他技巧、操作指南、优化与调试资料。
原页面标明许可为 CC BY-SA / Gnu FDL，未标出版本。本中文译稿保留该署名及许可声明；原文中的 SQL 片段和示意查询保持原样，界面示意中的 HTML 展示标签已整理为纯文本。
暂无评论内容