MySQL 分頁查詢總行數優化：SQL_CALC_FOUND_ROWS + FOUND_ROWS() 替代 COUNT(*)

傳統痛點

分頁需兩次查詢：
SELECT COUNT(*) FROM t WHERE ... (總數)
SELECT * FROM t WHERE ... LIMIT 10, 20 (分頁資料)
大表 COUNT(*) 全掃描極慢。

MySQL 原生優化

-- 1. 查資料時帶上 SQL_CALC_FOUND_ROWS
SELECT SQL_CALC_FOUND_ROWS tid FROM cdb_threads WHERE fid = 14 LIMIT 1, 10;

-- 2. 緊接著查總數 (不受 LIMIT 影響)
SELECT FOUND_ROWS() AS rowcount;

原理

• SQL_CALC_FOUND_ROWS 告訴優化器：「雖然有 LIMIT，但請計算滿足 WHERE 的總行數」
• FOUND_ROWS() 返回該計數，無需再次掃描
• 兩條語句必須在**同一連線**連續執行

實戰封裝 (PHP/PDO 範例)

function mail_list_sent($uid, $start) {
    $sql = "SELECT SQL_CALC_FOUND_ROWS uid, real_name, current_city, msg_uid, sender_flag,
                   msg_title, msg_content
            FROM " . TT_DBTABLEPRE . "mailbox mb1
            JOIN " . TT_DBTABLEPRE . "user usr1 ON mb1.sender_id = usr1.uid
            WHERE mb1.sender_id = ? AND sender_flag > 0
            LIMIT ?, " . TT_PAGESIZE;

    $stmt = $this->db->prepare($sql);
    $stmt->execute([$uid, $start]);
    $mails = $stmt->fetchAll();

    $total = $this->db->query("SELECT FOUND_ROWS() AS rowcount")->fetchColumn();

    return json_encode([
        'state_code' => 200,
        'info' => 'OK',
        'list' => $mails,
        'total' => $total
    ]);
}

注意事項

• MySQL 8.0.17 起 SQL_CALC_FOUND_ROWS 標記棄用，未來版本可能移除
• 替代方案：
  1. 應用層緩存總數 (Redis、本地快取)
  2. 覆蓋索引 COUNT(*) (如 COUNT(fid) 走二級索引)
  3. 預估總數：EXPLAIN SELECT ... 讀 rows 欄位
  4. 分頁改用 WHERE id > last_id LIMIT 20 (游標分頁)，無需總數
• FOUND_ROWS() 不計算 LIMIT 後的行數，僅計算 WHERE 過濾後的總行數