从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.

你也可以

SELECT SQL_CALC_FOUND_ROWS * FROM tbl limit 0, 20

select语句的行数(没有限制)在同一个select语句中捕获，因此您不需要再次查询表大小。 使用SELECT FOUND_ROWS()获取行数;

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

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

这里的“234374”是您查看的上一页中最后一条记录的id。

这将使id上的索引能够用于查找第一条记录。如果你使用LIMIT偏移量，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.

如果您确实希望使用带有偏移量的LIMIT(如果用户直接导航到页10000，而不是逐个分页，这是必要的)，那么您可以阅读这篇关于晚期行查找的文章，以提高带有较大偏移量的LIMIT的性能。

本教程展示了一种进行分页的好方法。 使用MySQL进行高效分页

简而言之，避免使用OFFSET或较大的LIMIT

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

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

查询1对中小记录运行更快，如果记录数量等于5000或更高，结果相似。

500条记录的结果:

Query1需要9.9999904632568毫秒

Query2耗时19.999980926514毫秒

8,000条记录的结果:

Query1需要129.99987602234毫秒

Query2需要160.00008583069毫秒

有相关的文献:

优化了使用MySQL的分页，使计算行总数和分页之间的差异。 使用MySQL的高效分页，由雅虎公司在Percona性能会议2009。Percona MySQL团队还提供了一个Youtube视频:高效分页使用MySQL(视频)，

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

在Use the INDEX网站上有一些建议的解决方案，Luke:

“分页结果”。 “正确的分页方式”。

为查询定义OFFSET。例如

Page 1-(记录01-10):offset = 0, limit=10;

第2页-(记录11-20)offset =10, limit =10;

并使用以下查询:

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

第2页示例:

SELECT column FROM table
LIMIT 10 OFFSET 10;

如果您希望在存储过程中执行此操作，可以尝试此操作

SELECT * FROM tbl limit 0, 20.

不幸的是，使用公式不起作用，所以你可以执行一个准备好的语句，或者只是给过程的开始和结束值。

下面是我如何使用node.js和MySQL数据库解决这个问题。 首先，让我们声明变量!

    const 
        Key = payload.Key,
        NumberToShowPerPage = payload.NumberToShowPerPage,
        Offset = payload.PageNumber * NumberToShowPerPage;

NumberToShowPerPage是显而易见的，但是偏移量是页码。

现在SQL查询…

    pool.query("SELECT * FROM TableName WHERE Key = ? ORDER BY CreatedDate DESC LIMIT ? OFFSET ?", [Key, NumberToShowPerPage, Offset], (err, rows, fields) => {}));

我将把它分解一下。

Pool, is a pool of MySQL connections. It comes from mysql node package module. You can create a connection pool using mysql.createPool. The ?s are replaced by the variables in the array [PageKey, NumberToShow, Offset] in sequential order. This is done to prevent SQL injection. See at the end were the () => {} is? That's an arrow function. Whatever you want to do with the data, put that logic between the braces. Key = ? is something I'm using to select a certain foreign key. You would likely remove that if you don't use foreign key constraints.

希望这能有所帮助。