LIMIT 20 OFFSET 100000 这类写法,MySQL 会先读出前 100020 行再丢掉前十万行。页码越大越慢,到后面几秒都出不来。
三种优化思路
- 延迟关联:先用覆盖索引查出主键,再回表取数据。
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 个主键取完整行。
- 游标分页:记住上一页最后一条的 id,下一页从它之后取。
SELECT * FROM articles WHERE id < 100000 ORDER BY id DESC LIMIT 20;这种方式每页都是常数时间,适合「无限滚动」和 API。缺点是不能跳到第 N 页,只能上一页/下一页。
- 限制可翻页数:搜索引擎式界面最多给 50 页,再往后让用户加筛选条件。这既是性能考虑,也符合真实使用习惯。
总数还要不要查
SELECT COUNT(*) 在大表上很贵。两种做法:缓存总数并定时更新;或者只查「有没有下一页」(多取一条,看是否够 21 条)。后者对列表页体验几乎没影响。
导出别用 fetchAll
导出十万行时,fetchAll 会把全部结果读进内存,很容易超过 memory_limit。用 fetch 逐行处理,边读边写文件。
$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。