如何結合PDOStatement::rowCount 和LIMIT 實現分頁查詢優化

gitbox 2025-05-28

在进行 Web 应用开发时，分页查询是非常常见的需求之一，尤其是在需要展示大量数据的后台管理系统、评论区或产品列表等模块中。MySQL 提供了 LIMIT 子句用于分页，而 PHP 的 PDO 中 PDOStatement::rowCount() 方法则可以用来获取结果集的数量。如果两者结合得当，不仅可以提高代码的可读性，也能显著优化查询效率。

一、分页的常见问题

传统的分页查询通常有两个步骤：

获取当前页的数据：使用 LIMIT offset, count。
获取总记录数：使用 SELECT COUNT(*) FROM table。

虽然这种方式非常常见，但当数据表很大时，COUNT(*) 的开销可能会很大，尤其是在高并发环境下，会对数据库造成较大压力。

二、PDOStatement::rowCount 的作用与局限

PDOStatement::rowCount() 在 SELECT 查询中表现不一致，它并不是用来统计总记录数，而是返回该次执行的语句影响的行数。在使用 SELECT 时，某些数据库（例如 PostgreSQL）支持它返回查询结果的行数，但在 MySQL 中，只有在设置了 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 选项的情况下，才有可能返回准确的结果数。

$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'pass', [
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
]);

三、结合 LIMIT 和 rowCount 优化分页逻辑

虽然不能完全依赖 rowCount() 来获取总数，但我们可以在某些场景下结合 LIMIT 和智能逻辑来优化分页体验。例如，我们可以在每页多查询一条数据，用于判断“是否还有下一页”，从而避免执行 COUNT(*) 的操作。

示例代码

<?php
$pdo = new PDO('mysql:host=localhost;dbname=exampledb', 'user', 'password', [
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$pageSize = 10;
$offset = ($page - 1) * $pageSize;
$limitPlusOne = $pageSize + 1;

$sql = "SELECT * FROM articles ORDER BY created_at DESC LIMIT :offset, :limit";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':limit', $limitPlusOne, PDO::PARAM_INT);
$stmt->execute();

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
$hasNextPage = false;

if (count($results) > $pageSize) {
    $hasNextPage = true;
    array_pop($results); // 移除多餘的一條
}

// 渲染數據
foreach ($results as $article) {
    echo "<h2>{$article['title']}</h2>";
    echo "<p>{$article['summary']}</p>";
    echo "<a href=\"https://gitbox.net/articles/{$article['id']}\">閱讀全文</a><hr>";
}

if ($hasNextPage) {
    echo "<a href=\"https://gitbox.net/list?page=" . ($page + 1) . "\">下一頁</a>";
}
?>

四、优点分析

避免 COUNT(*) 查询，减轻数据库压力；
利用 LIMIT N+1 判断是否有下一页；
数据处理逻辑简洁，性能表现好；
尤其适合数据量大、分页深度低的场景。

五、何时仍需要 COUNT(*)？

上述方法虽然高效，但并非适用于所有情况。如果你的页面需要展示总页数、总记录数等信息，那么还是不可避免地需要使用 COUNT(*)：

$totalCount = $pdo->query("SELECT COUNT(*) FROM articles")->fetchColumn();
$totalPages = ceil($totalCount / $pageSize);

一种折中方式是缓存总数信息，定时刷新，避免每次都实时计算。

六、结语

在进行分页优化时，没有“一刀切”的最佳实践。合理地结合 LIMIT、rowCount() 和业务需求，才是高性能分页查询的关键。如果你追求极致性能，可以考虑基于主键或时间戳的“游标分页（cursor-based pagination）”，它在大数据集下有更好的表现。

通过本文所介绍的技巧，你可以在多数场景下避免昂贵的 COUNT(*) 查询，同时保持良好的用户体验和分页准确性。

PDOStatement::rowCount