对非常大的数据集进行分页 [英] Paginating very large datasets

问题描述

我在MySQL中有一个数据集，其中使用limit已经是一个昂贵的查询，而查找结果的数量也很昂贵.因此，我想避免执行另一个查询来查找结果数.我不能使用MYSQL_CALC_FOUND_ROWS，因为该限制在子查询中:

I have a dataset in MySQL where using limit is already an expensive query, and finding the number of results is expensive as well. Thus, I'd like to avoid doing another query to find the number of results. I cannot use MYSQL_CALC_FOUND_ROWS because the limit is inside a subquery:

SELECT * FROM items,
(
  SELECT
    item_id
  FROM
    (etc)
  WHERE
    some.field=<parameter>
  AND (etc)
  GROUP BY (something)
  ORDER BY (something_else) DESC
  LIMIT 15
) subset
WHERE item.id=subset.item_id

我可以保留联接项并取消子查询，然后可以使用MYSQL_CALC_FOUND_ROWS，但这非常非常慢.我已经尝试了所有索引优化，并且让我们假设这是不可能的.

I could left join items and do away with the subquery, then be able to use MYSQL_CALC_FOUND_ROWS, however this is very, very, slow. I've tried all index optimizations and let's just assume it is out of the question.

现在这变成了一个设计问题... 当我不知道最后一页时，如何允许用户翻阅这些数据?我只知道它们是否走得太远(例如:查询未返回任何结果).

This now becomes more a design question... how do I allow the user to page through this data when I don't know the last page? I only know if they've gone too far (eg: query returns no results).

推荐答案

以下是MySQL大师Baron Schwartz的文章摘要:

Here's a summary of an article by MySQL guru Baron Schwartz:

http://www.mysqlperformanceblog .com/2008/09/24/four-way-to-optimize-paginated-displays/

1. 在第一个查询中，获取并缓存所有结果.

1. On the first query, fetch and cache all the results.

不显示所有结果.甚至Google都不能让您看到百万分之一的结果.

Don't show all results. Not even Google lets you see the millionth result.

不显示总数或指向其他页面的中间链接.仅显示下一个"链接.

Don't show the total count or the intermediate links to other pages. Show only the "next" link.

估计有多少个结果.再次，谷歌这样做，没有人抱怨.

Estimate how many results there are. Again, Google does this and nobody complains.

这篇关于对非常大的数据集进行分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！