有哪些用于复杂查询数据集分页的策略？由于count(*)需要约1.5秒，因此我们不希望每次页面查看都访问数据库。当前查询返回了大约45k行。
以下是我考虑过的一些方法：
- 缓存行数并每X分钟更新一次
- 将计数的行限制（和偏移）为41（例如），并将页面选择器显示为“1 2 3 4 ...”，然后如果有人实际转到第4页，则重新计算并显示“... 3 4 5 6 7 ...”
- 获取行数一次并将其存储在用户的会话中
- 去掉页面选择器，只有一个“下一页”链接
复杂（缓慢）数据集的分页策略
6
- Jerry Fernholz
5个回答
4
我曾经为一个每天浏览量超过一百万的网站，使用PHP和MySQL工程化了几个分页策略。我分阶段执行以下策略：
多列索引在尝试物化视图之前，我应该首先使用这个策略。
生成物化视图。我创建了一个定时任务，对我正在使用的文档表进行常见的反规范化处理。我会 SELECT ... INTO OUTFILE ... 然后创建新表，并将其轮换进来：
SELECT ... INTO OUTFILE '/tmp/ondeck.txt' FROM mytable ...;
CREATE TABLE ondeck_mytable LIKE mytable;
LOAD DATA INFILE '/tmp/ondeck.txt' INTO TABLE ondeck_mytable...;
DROP TABLE IF EXISTS dugout_mytable;
RENAME TABLE atbat_mytable TO dugout_mytable, ondeck_mytable TO atbat_mytable;
这使得写入争用的锁定时间保持在最低限度，分页查询可以在atbat物化视图上快速执行。我已经简化了上面的内容，省略了实际操作，这些都不重要。
然后我创建了一个关于我的数据库连接的包装器，将这些分页结果缓存到memcache中。这是一个巨大的性能优势。然而，它仍然不够好。
我编写了一个PHP守护进程，并将分页逻辑提取出来。它会检测mytable的更改，并定期从最旧的更改记录到最新的记录重新生成所有页面到web服务器的文件系统中。通过一些mod_rewrite，我可以检查页面是否存在于磁盘上，并提供服务。这也允许我有效地利用反向代理，通过让Apache检测If-Modified-Since头，并响应304响应代码。(显然，我删除了任何允许用户选择每页结果数量的选项，这是一个不重要的功能。)
更新： 当使用MyISAM表时，COUNT不会在我能够减少表上读写争用量时创建问题。如果我正在使用InnoDB，则会创建一个触发器，将行计数更新到相邻的表中。该触发器将根据INSERT或DELETE语句进行+1或-1操作。
当我转移到激进的查询缓存时，拇指轮查询也被缓存了，当涉及批量生成页面时，我使用临时表，因此计算拇指轮没有问题。许多拇指轮计算简化了，因为它成为一个可预测的文件系统模式，实际上只需要最大的页面编号。最小的页面编号始终为1。
你上面给出的窗口拇指轮示例(<<4 [5] 6>>)应该很容易做到，而无需任何查询，只要您知道最大页数即可。
- memnoch_proxy
4
我的建议是在每个查询中向MySQL请求比您实际需要的多1行，根据结果集中的行数决定是否显示 下一页 链接。
- gnud
1
MySQL 在优化 LIMIT 查询方面做得相当不错。
这意味着它会选择适当的联接缓冲区、文件排序缓冲区等，足以满足 LIMIT 语句的要求。
此外，请注意，在有 45k 行数据时，您可能不需要精确计数。可以使用针对索引字段的单独查询来确定近似计数。例如，以下查询：
SELECT COUNT(*)
FROM mytable
WHERE col1 = :myvalue
AND col2 = :othervalue
可以用这个进行近似：
SELECT COUNT(*) *
(
SELECT COUNT(*)
FROM mytable
) / 1000
FROM (
SELECT 1
FROM mytable
WHERE col1 = :myvalue
AND col2 = :othervalue
LIMIT 1000
)
使用MyISAM会更加高效。
如果您提供一个复杂查询的示例，我可能可以更明确地说明如何改进其分页。
- Quassnoi
0
我绝不是MySQL专家，但也许放弃COUNT(*)，改用COUNT(id)会更好？
- metrobalderas
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接