MySQL 数据 - 实现分页的最佳方式？

MySQL Data - Best way to implement paging?

提问by aryaxt

My iPhone app connects to my PHP web service to retrieve data from a MySQL database. A request can return 500 results.

我的 iPhone 应用程序连接到我的 PHP Web 服务以从 MySQL 数据库中检索数据。一个请求可以返回 500 个结果。

What is the best way to implement paging and retrieve 20 items at a time?

实现分页和一次检索 20 个项目的最佳方法是什么？

Let's say I receive the first 20 ads from my database. Now how can I request for the next 20 ads?

假设我从我的数据库中收到了前 20 个广告。现在我如何请求接下来的 20 个广告？

回答by Faisal Feroz

From the MySQL documentation:

从 MySQL 文档：

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

With one argument, the value specifies the number of rows to return from the beginning of the result set:

SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

LIMIT 子句可用于限制 SELECT 语句返回的行数。LIMIT 接受一个或两个数字参数，它们都必须是非负整数常量（使用准备好的语句时除外）。

有两个参数，第一个参数指定要返回的第一行的偏移量，第二个参数指定要返回的最大行数。初始行的偏移量为 0（不是 1）：

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

要检索从某个偏移量到结果集末尾的所有行，您可以为第二个参数使用一些大数字。此语句检索从第 96 行到最后一行的所有行：

SELECT * FROM tbl LIMIT 95,18446744073709551615;

使用一个参数，该值指定从结果集的开头返回的行数：

SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

换句话说，LIMIT row_count 等价于 LIMIT 0, row_count。

回答by Mark Byers

For 500 records efficiency is probably not an issue, but if you have millions of records then it can be advantageous to use a WHERE clause to select the next page:

对于 500 条记录，效率可能不是问题，但是如果您有数百万条记录，那么使用 WHERE 子句来选择下一页可能是有利的：

SELECT *
FROM yourtable
WHERE id > 234374
ORDER BY id
LIMIT 20

The "234374" here is the id of the last record from the prevous page you viewed.

此处的“234374”是您查看的上一页的最后一条记录的 ID。

This will enable an index on id to be used to find the first record. If you use LIMIT offset, 20you could find that it gets slower and slower as you page towards the end. As I said, it probably won't matter if you have only 200 records, but it can make a difference with larger result sets.

这将启用 id 上的索引来查找第一条记录。如果您使用，LIMIT offset, 20您会发现随着您翻页到最后，它变得越来越慢。正如我所说的，如果您只有 200 条记录可能并不重要，但它可以对更大的结果集产生影响。

Another advantage of this approach is that if the data changes between the calls you won't miss records or get a repeated record. This is because adding or removing a row means that the offset of all the rows after it changes. In your case it's probably not important - I guess your pool of adverts doesn't change too often and anyway no-one would notice if they get the same ad twice in a row - but if you're looking for the "best way" then this is another thing to keep in mind when choosing which approach to use.

这种方法的另一个优点是，如果数据在两次调用之间发生变化，您将不会错过记录或获得重复记录。这是因为添加或删除一行意味着它改变后所有行的偏移量。在您的情况下，这可能并不重要 - 我猜您的广告池不会经常变化，无论如何，如果他们连续两次收到相同的广告，没有人会注意到 - 但如果您正在寻找“最佳方式”那么这是在选择使用哪种方法时要记住的另一件事。

If you do wish to use LIMIT with an offset (and this is necessary if a user navigates directly to page 10000 instead of paging through pages one by one) then you could read this article about late row lookupsto improve performance of LIMIT with a large offset.

如果您确实希望使用带偏移量的 LIMIT（如果用户直接导航到第 10000 页而不是一页一页地翻页，这是必要的），那么您可以阅读这篇关于后期行查找以提高 LIMIT 性能的文章抵消。

回答by Prabodh Hend

Define OFFSETfor the query. For example

为查询定义OFFSET。例如

page 1 - (records 01-10): offset = 0, limit=10;

第 1 页 -（记录 01-10）：偏移量 = 0，限制 = 10；

page 2 - (records 11-20) offset = 10, limit =10;

第 2 页 -（记录 11-20）偏移 = 10，限制 =10；

and use the following query :

并使用以下查询：

SELECT column FROM table LIMIT {someLimit} OFFSET {someOffset};

example for page 2:

第 2 页的示例：

SELECT column FROM table
LIMIT 10 OFFSET 10;

回答by Luchostein

There's literature about it:

有关于它的文献：

• Optimized Pagination using MySQL, making the difference between counting the total amount of rows, and pagination.

• Efficient Pagination Using MySQL, by Yahoo Inc. in the Percona Performance Conference 2009. The Percona MySQL team provides it also as a Youtube video: Efficient Pagination Using MySQL (video),

• 使用 MySQL 优化分页，在计算总行数和分页之间产生差异。

• Efficient Pagination Using MySQL，由 Yahoo Inc. 在 Percona Performance Conference 2009 中提供。Percona MySQL 团队还提供了一个 Youtube 视频：Efficient Pagination Using MySQL（视频），

The main problem happens with the usage of large OFFSETs. They avoid using OFFSETwith a variety of techniques, ranging from idrange selections in the WHEREclause, to some kind of caching or pre-computing pages.

主要问题发生在使用大OFFSETs 时。他们避免使用OFFSET各种技术，从子句中的id范围选择WHERE到某种缓存或预计算页面。

There are suggested solutions at Use the INDEX, Luke:

在Use the INDEX, Luke有建议的解决方案：

• "Paging Through Results".

• "Pagination done the right way".

• “翻阅结果”。

• “以正确的方式进行分页”。

回答by Bao Le

This tutorial shows a great way to do pagination. Efficient Pagination Using MySQL

本教程展示了一种很好的分页方式。 使用 MySQL 的高效分页

In short, avoid to use OFFSET or large LIMIT

总之，避免使用 OFFSET 或大的 LIMIT

回答by surajz

you can also do

你也可以这样做

SELECT SQL_CALC_FOUND_ROWS * FROM tbl limit 0, 20

The row count of the select statement (without the limit) is captured in the same select statement so that you don't need to query the the table size again. You get the row count using SELECT FOUND_ROWS();

select 语句的行数（无限制）在同一个 select 语句中捕获，这样您就不需要再次查询表大小。您可以使用 SELECT FOUND_ROWS(); 获得行数。

回答by Huy

Query 1: SELECT * FROM yourtable WHERE id > 0 ORDER BY id LIMIT 500

查询 1： SELECT * FROM yourtable WHERE id > 0 ORDER BY id LIMIT 500

Query 2: SELECT * FROM tbl LIMIT 0,500;

查询 2： SELECT * FROM tbl LIMIT 0,500;

Query 1 run faster with small or medium records, if number of records equal 5,000 or higher, the result are similar.

对于小型或中型记录，查询 1 运行得更快，如果记录数等于 5,000 或更多，则结果相似。

Result for 500 records:

500 条记录的结果：

Query1 take 9.9999904632568 milliseconds

Query1 耗时 9.9999904632568 毫秒

Query2 take 19.999980926514 milliseconds

Query2 耗时 19.999980926514 毫秒

Result for 8,000 records:

8,000 条记录的结果：

Query1 take 129.99987602234 milliseconds

Query1 耗时 129.99987602234 毫秒

Query2 take 160.00008583069 milliseconds

Query2 需要 160.00008583069 毫秒