MySQL 獲取 GROUP BY 總記錄行數的高效方法

問題背景

在分頁查詢中，GROUP BY 聚合後若需同時獲取總分組數與當頁數據，傳統做法需執行兩條 SQL：一條 COUNT(*) 統計總數，一條 LIMIT 取數據。這不僅增加資料庫負載，還可能導致主從延遲下的數據不一致。

核心方案：SQL_CALC_FOUND_ROWS + FOUND_ROWS()

MySQL 內建 SQL_CALC_FOUND_ROWS 提示優化器計算無 LIMIT 時的總行數，隨後調用 FOUND_ROWS() 即可獲取該值，僅需一次查詢往返。

標準分頁寫法對比

-- 傳統兩次查詢
SELECT COUNT(*) FROM `table` WHERE ...;
SELECT * FROM `table` WHERE ... LIMIT 10, 20;

-- 優化後：單次往返
SELECT SQL_CALC_FOUND_ROWS * FROM `table` WHERE ... LIMIT 10, 20;
SELECT FOUND_ROWS() AS total_count;

結合 GROUP BY 的完整範例

SELECT SQL_CALC_FOUND_ROWS 
    t3.id, a, bunch, of, other, stuff 
FROM t1, t2, t3 
WHERE (關聯條件) 
GROUP BY t3.id 
LIMIT 10, 20;

SELECT FOUND_ROWS() AS total_groups;

補充：GROUP BY 基礎用法回顧

-- 統計每個 column_id 出現次數
SELECT column_id, COUNT(*) AS cnt 
FROM my_table 
GROUP BY column_id;

注意事項與避坑指南

• MySQL 8.0+ 棄用警告：SQL_CALC_FOUND_ROWS 在 8.0.17 標記為棄用，未來版本可能移除。新專案建議改用窗口函數 COUNT(*) OVER() 或兩條獨立查詢。
• 性能影響：大數據量下掃描全表計算總數仍較慢，配合覆蓋索引可緩解。
• 事務隔離級別：在 REPEATABLE READ 下兩次查詢結果一致，READ COMMITTED 可能出現幻讀導致總數與數據不符。

-- 使用窗口函數一次查詢獲取總數與分頁數據
SELECT *, COUNT(*) OVER() AS total_count
FROM (
    SELECT t3.id, ...
    FROM t1, t2, t3
    WHERE ...
    GROUP BY t3.id
) AS sub
LIMIT 10, 20;