PHP 分頁查詢優化實踐：從傳統 mysql_* 到現代 PDO 實現

背景與問題

早期 PHP 專案常使用 mysql_* 函數配合自定義分頁類。隨著 PHP 7+ 棄用該擴展，且存在 SQL 注入風險、重複連接開銷大等問題，亟需重構。本文對比舊代碼並提供基於 PDO 的現代分頁解決方案。

舊代碼剖析（僅供參考）

原 Pager() 函數特點：

• 每次查詢重新建立 MySQL 連接，效能低。
• 直接拼接 SQL 字符串，極易注入。
• 使用 mysql_fetch_array 獲取總記錄數，再用 LIMIT offset, size 分頁。
• 全域變量 $dbuser 等耦合度高。

現代 PDO 分頁類實現

<?php
class Paginator {
    private PDO $pdo;
    private string $table;
    private int $perPage;

    public function __construct(PDO $pdo, string $table, int $perPage = 15) {
        $this->pdo = $pdo;
        $this->table = $table;
        $this->perPage = $perPage;
    }

    /**
     * 獲取分頁數據
     * @param int $page 當前頁碼 (從 1 開始)
     * @param array $where 條件 ['field' => value, ...]
     * @param string $orderBy 排序字段
     * @return array ['data' => [], 'total' => int, 'page' => int, 'lastPage' => int]
     */
    public function paginate(int $page = 1, array $where = [], string $orderBy = 'id DESC'): array {
        $page = max(1, $page);
        $offset = ($page - 1) * $this->perPage;

        // 構建 WHERE 子句
        $whereSql = '';
        $params = [];
        if ($where) {
            $clauses = [];
            foreach ($where as $col => $val) {
                $clauses[] = "`{$col}` = ?";
                $params[] = $val;
            }
            $whereSql = 'WHERE ' . implode(' AND ', $clauses);
        }

        // 總記錄數
        $totalStmt = $this->pdo->prepare("SELECT COUNT(*) FROM `{$this->table}` {$whereSql}");
        $totalStmt->execute($params);
        $total = (int)$totalStmt->fetchColumn();

        $lastPage = (int)ceil($total / $this->perPage);
        if ($page > $lastPage) $page = max(1, $lastPage);
        $offset = ($page - 1) * $this->perPage;

        // 獲取數據
        $dataStmt = $this->pdo->prepare(
            "SELECT * FROM `{$this->table}` {$whereSql} ORDER BY {$orderBy} LIMIT ?, ?"
        );
        // PDO 不支持 LIMIT 占位符綁定整數，需手動構建或使用 bindValue
        $sql = "SELECT * FROM `{$this->table}` {$whereSql} ORDER BY {$orderBy} LIMIT {$offset}, {$this->perPage}";
        $dataStmt = $this->pdo->prepare($sql);
        $dataStmt->execute($params);
        $data = $dataStmt->fetchAll(PDO::FETCH_ASSOC);

        return [
            'data' => $data,
            'total' => $total,
            'page' => $page,
            'lastPage' => $lastPage,
            'perPage' => $this->perPage,
        ];
    }
}

使用範例

<?php
// 連接配置
$dsn = 'mysql:host=127.0.0.1;dbname=test;charset=utf8mb4';
$pdo = new PDO($dsn, 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$paginator = new Paginator($pdo, 'articles', 10);

// 第 2 頁，條件：status=1
$result = $paginator->paginate(2, ['status' => 1], 'created_at DESC');

echo "總共 {$result['total']} 筆，第 {$result['page']}/{$result['lastPage']} 頁\n";
foreach ($result['data'] as $row) {
    echo "- {$row['title']}\n";
}

進階優化建議

1. 游標分頁：大數據量避免 OFFSET 深分頁性能下降，改用 `WHERE id > ? ORDER BY id LIMIT N`。
2. 緩存總數：總記錄數變化不頻繁時，用 Redis 緩存 COUNT(*) 結果。
3. 查詢構建器：引入 Laravel Database、Doctrine DBAL 或輕量級 Medoo 簡化 SQL 構建。