写给愿意动手的 PHP 开发者
PHP学习网 PHP学习网
公告PHP学习网为公益性网站,致力于为广大PHP爱好者服务
首页/ PHP 教程/ MySQL 与 PDO/ 分页与大数据量:LIMIT 越翻越慢怎么办
MySQL 与 PDO 分页性能大数据量

分页与大数据量:LIMIT 越翻越慢怎么办

LIMIT offset 为什么会慢、延迟关联与游标分页的写法、总数要不要每次都查、导出大结果集用逐行读。

LIMIT 20 OFFSET 100000 这类写法,MySQL 会先读出前 100020 行再丢掉前十万行。页码越大越慢,到后面几秒都出不来。

三种优化思路

  1. 延迟关联:先用覆盖索引查出主键,再回表取数据。
SQL
SELECT a.* FROM articles a
JOIN (SELECT id FROM articles WHERE cat_id = 3 ORDER BY id DESC LIMIT 20 OFFSET 100000) t
  ON a.id = t.id;

子查询只扫索引,不回表,代价小很多。外层再按 20 个主键取完整行。

  1. 游标分页:记住上一页最后一条的 id,下一页从它之后取。
SQL
SELECT * FROM articles WHERE id < 100000 ORDER BY id DESC LIMIT 20;

这种方式每页都是常数时间,适合「无限滚动」和 API。缺点是不能跳到第 N 页,只能上一页/下一页。

  1. 限制可翻页数:搜索引擎式界面最多给 50 页,再往后让用户加筛选条件。这既是性能考虑,也符合真实使用习惯。

总数还要不要查

SELECT COUNT(*) 在大表上很贵。两种做法:缓存总数并定时更新;或者只查「有没有下一页」(多取一条,看是否够 21 条)。后者对列表页体验几乎没影响。

导出别用 fetchAll

导出十万行时,fetchAll 会把全部结果读进内存,很容易超过 memory_limit。用 fetch 逐行处理,边读边写文件。

PHP
$st = $pdo->query('SELECT id, title FROM articles');
$fh = fopen('export.csv', 'w');
fputcsv($fh, ['ID', '标题']);
while ($row = $st->fetch()) {
    fputcsv($fh, $row);
}
fclose($fh);

PDO 默认会把整个结果集缓冲到客户端。真正的超大数据集要设 MYSQL_ATTR_USE_BUFFERED_QUERY=false,但这时不能在同一连接上再发查询。

分页还有一个隐藏问题:排序字段不唯一时,翻页会重复或漏数据。ORDER BY 后面永远补一个 id。

接着读