數據庫查詢優化：分離數據集與總數統計避免重復掃描

問題

分頁查詢需同時返回List<Data>和totalCount。單條SQL用COUNT(*) OVER()或子查詢導致計劃複雜、執行慢；應用層list.size()需先全量加載，內存壓力大。

標準解法：兩條獨立SQL

-- 1. 獲取分頁數據（利用索引、Limit/Offset）
SELECT id, name, ... 
FROM large_table 
WHERE conditions 
ORDER BY create_time DESC 
LIMIT 20 OFFSET 0;

-- 2. 獲取總數（可只掃描索引、並行執行）
SELECT COUNT(1) 
FROM large_table 
WHERE conditions;

優勢

• 執行計劃簡單：各自走最優索引，無窗口函數/子查詢開銷。
• 並行可控：應用層CompletableFuture並行發起，總耗時≈max(查數據, 查總數)。
• 緩存友好：總數變化較慢，可Redis緩存COUNT結果（TTL 1-5分鐘），極大降低DB壓力。

進階優化

1. 覆蓋索引：COUNT查詢建立INDEX(conditions_columns)實現Index Only Scan。
2. 估算總數：超大表（億級+）可用EXPLAIN SELECT ...的rows估算值，或information_schema.TABLES.TABLE_ROWS（誤差±20%）。
3. 游標分頁：替代OFFSET，用WHERE id > last_id ORDER BY id LIMIT 20避免深分頁掃描。

代碼示例

public PageResult queryPage(QueryParam param) {
    // 並行執行
    CompletableFuture> dataFuture = CompletableFuture.supplyAsync(() -> 
        dao.selectPage(param), executor);
    CompletableFuture countFuture = CompletableFuture.supplyAsync(() -> 
        dao.countTotal(param), executor);
    
    List data = dataFuture.join();
    Long total = countFuture.join();
    return new PageResult<>(data, total, param.getPageNo(), param.getPageSize());
}

避坑指南

• 兩條SQL的WHERE條件**必須完全一致**，否則總數與數據不對應。
• 事務一致性要求高時，需在同一事務中執行（隔離級別Repeatable Read），但會加長鎖持有時間。
• MySQLSQL_CALC_FOUND_ROWS + FOUND_ROWS()已棄用（8.0移除），不推薦使用。