我的 iPhone 应用程序连接到我的 PHP Web 服务以从 MySQL 数据库中检索数据,一个请求最多可以返回 500 个结果。
一次实现分页和检索 20 个项目的最佳方法是什么?
假设我从数据库中收到前 20 个条目,我现在如何请求接下来的 20 个条目?
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; # 检索前 5 行 换句话说,LIMIT row_count 等价于 LIMIT 0, row_count。
对于 500 条记录,效率可能不是问题,但是如果您有数百万条记录,那么使用 WHERE 子句来选择下一页可能是有利的:
SELECT *
FROM yourtable
WHERE id > 234374
ORDER BY id
LIMIT 20
这里的“234374”是您查看的上一页中最后一条记录的 id。
这将使 id 上的索引能够用于查找第一条记录。如果您使用 LIMIT offset, 20
,您会发现当您翻页时它会变得越来越慢。正如我所说,如果您只有 200 条记录,这可能并不重要,但它可以对更大的结果集产生影响。
这种方法的另一个优点是,如果数据在调用之间发生变化,您不会错过记录或获得重复记录。这是因为添加或删除一行意味着它更改后所有行的偏移量。在您的情况下,这可能并不重要-我猜您的广告池不会经常变化,无论如何,如果他们连续两次收到相同的广告,没人会注意到-但如果您正在寻找“最佳方式”那么这是在选择使用哪种方法时要记住的另一件事。
如果您确实希望使用带偏移量的 LIMIT(如果用户直接导航到第 10000 页而不是逐页翻页,这是必要的),那么您可以阅读这篇关于 late row lookups 的文章以提高 LIMIT 的性能抵消。
limit 1000000, 10
并希望它能够工作不会让您有任何收获。
为查询定义 OFFSET。例如
第 1 页 -(记录 01-10):偏移量 = 0,限制 = 10;
第 2 页 -(记录 11-20)偏移量 = 10,限制 =10;
并使用以下查询:
SELECT column FROM table LIMIT {someLimit} OFFSET {someOffset};
第 2 页的示例:
SELECT column FROM table
LIMIT 10 OFFSET 10;
有关于它的文献:
使用 MySQL 优化分页,计算总行数和分页之间的差异。
使用 MySQL 进行高效分页,雅虎公司在 2009 年 Percona 性能会议上。Percona MySQL 团队还提供了一个 Youtube 视频:使用 MySQL 进行高效分页(视频),
主要问题发生在使用大型 OFFSET
上。他们避免将 OFFSET
与各种技术一起使用,从 WHERE
子句中的 id
范围选择到某种缓存或预计算页面。
在 Use the INDEX,Luke 中有建议的解决方案:
“通过结果分页”。
“以正确的方式进行分页”。
本教程展示了一种进行分页的好方法。 Efficient Pagination Using MySQL
总之,避免使用 OFFSET 或大的 LIMIT
OFFSET
而是使用 ORDER BY
并在用于排序的列上放置一个索引。现在我们可以使用 WHERE indexedColumn > lastSeenValue ORDER BY indexedColumn DESC LIMIT pageSize
进行过滤/分页。然后,对网络服务器的请求必须包含 lastSeen 值。
你也可以
SELECT SQL_CALC_FOUND_ROWS * FROM tbl limit 0, 20
select 语句的行数(没有限制)在同一个 select 语句中被捕获,因此您无需再次查询表大小。您使用 SELECT FOUND_ROWS(); 获得行数;
*
导致获取的列多于必要的列,而 SQL_CALC_FOUND_ROWS
导致从表中的 所有 行中读取这些列,即使它们未包含在结果中。在不读取所有这些列的单独查询中计算行数会更有效。然后您的主查询可以在读取 20 行后停止。
查询 1:SELECT * FROM yourtable WHERE id > 0 ORDER BY id LIMIT 500
查询 2:SELECT * FROM tbl LIMIT 0,500;
查询 1 对中小型记录运行得更快,如果记录数等于或大于 5,000,则结果相似。
500 条记录的结果:
Query1 耗时 9.9999904632568 毫秒
Query2 耗时 19.999980926514 毫秒
8,000 条记录的结果:
Query1 耗时 129.99987602234 毫秒
Query2 耗时 160.00008583069 毫秒
id
上放置一个索引。
id > 0
有什么用处?
offset
(limit 的第一个参数是偏移量),您仍然选择限制的所有数据,然后丢弃该偏移量,然后返回 offset
和 limit
之间的部分。另一方面,使用 where
子句,您正在为查询和查询 ONLY
该特定部分设置一种起点。
limit X, Y
,本质上会发生 X+Y 行是检索,然后从头开始删除 X 行,并返回剩下的任何内容。重申一下:limit X, Y
导致扫描 X+Y 行。OFFSET
;-)