如何优化带分页、空白行统计及多类合计的SQL查询以提升大数据量处理效率

核心问题定位 #

• 原有代码在分页时重复执行SUM查询，频繁和数据库交互是性能瓶颈
• 行配额（blankRow计算）、分页分组、三类合计逻辑全部放在PHP层处理，大量遍历和重复计算拖慢速度

优化方案 #

方案1：PHP层内存计算（最易实现、性能提升最明显） #

移除循环内的数据库SUM查询，直接用变量在内存中累计统计三类数值，5000行数据处理耗时可降至1秒以内：

<?php
$query = "SELECT trip.*, vehicle.color, vehicle.name FROM trip INNER JOIN vehicle ON vehicle.vehicleId=trip.vehicleId ORDER BY date ASC";
$stmt = $pdo->prepare($query);
$stmt->execute();

$used_quota = 0;
// 三类合计变量
$current_page_day = $current_page_night = 0;
$prev_page_day = $prev_page_night = 0;
$total_day = $total_night = 0;

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $row_quota = $row['blankRow'] + 1;
    // 超出配额则触发分页
    if ($used_quota + $row_quota > 5) {
        // 输出本页统计
        echo "本页合计：durationDay={$current_page_day}, durationNight={$current_page_night}\n";
        echo "上一页合计：durationDay={$prev_page_day}, durationNight={$prev_page_night}\n";
        echo "累计总合计：durationDay={$total_day}, durationNight={$total_night}\n";
        echo "New Page -------------------------- \n";
        // 重置分页状态
        $prev_page_day = $current_page_day;
        $prev_page_night = $current_page_night;
        $current_page_day = $current_page_night = 0;
        $used_quota = 0;
    }
    // 输出行数据
    echo $row["name"] . " + other data \n";
    // 累计数值
    $used_quota += $row_quota;
    $current_page_day += $row['durationDay'];
    $current_page_night += $row['durationNight'];
    $total_day += $row['durationDay'];
    $total_night += $row['durationNight'];
}
// 输出最后一页统计
echo "本页合计：durationDay={$current_page_day}, durationNight={$current_page_night}\n";
echo "上一页合计：durationDay={$prev_page_day}, durationNight={$prev_page_night}\n";
echo "累计总合计：durationDay={$total_day}, durationNight={$total_night}\n";
?>

方案2：SQL层一次性计算（适合需要直接返回分页分组数据的场景） #

适配MySQL5.7版本，利用用户变量一次查询完成行配额计算、分页分配、三类合计统计：

SELECT 
    t.*,
    v.color,
    v.name,
    page_sum.page_day,
    page_sum.page_night,
    IFNULL(prev_sum.prev_day,0) as prev_day,
    IFNULL(prev_sum.prev_night,0) as prev_night,
    running_sum.total_day,
    running_sum.total_night
FROM (
    SELECT 
        trip.*,
        @page := IF(@cum + blankRow +1 >5, @page+1, @page) as page_num,
        @cum := IF(@cum + blankRow +1 >5, blankRow+1, @cum + blankRow +1) as used_quota
    FROM trip
    ORDER BY date ASC
) t
INNER JOIN vehicle v ON t.vehicleId = v.vehicleId
-- 关联本页合计
LEFT JOIN (
    SELECT page_num, SUM(durationDay) page_day, SUM(durationNight) page_night
    FROM (
        SELECT 
            @page2 := IF(@cum2 + blankRow +1 >5, @page2+1, @page2) as page_num,
            durationDay, durationNight,
            @cum2 := IF(@cum2 + blankRow +1 >5, blankRow+1, @cum2 + blankRow +1)
        FROM trip ORDER BY date ASC
    ) t2 GROUP BY page_num
) page_sum ON t.page_num = page_sum.page_num
-- 关联累计合计
LEFT JOIN (
    SELECT p1.page_num, SUM(p2.page_day) total_day, SUM(p2.page_night) total_night
    FROM (
        SELECT page_num, SUM(durationDay) page_day, SUM(durationNight) page_night
        FROM (
            SELECT @page3 := IF(@cum3 + blankRow +1 >5, @page3+1, @page3) as page_num, durationDay, durationNight,
            @cum3 := IF(@cum3 + blankRow +1 >5, blankRow+1, @cum3 + blankRow +1) FROM trip ORDER BY date ASC
        ) t3 GROUP BY page_num
    ) p1
    INNER JOIN (
        SELECT page_num, SUM(durationDay) page_day, SUM(durationNight) page_night
        FROM (
            SELECT @page4 := IF(@cum4 + blankRow +1 >5, @page4+1, @page4) as page_num, durationDay, durationNight,
            @cum4 := IF(@cum4 + blankRow +1 >5, blankRow+1, @cum4 + blankRow +1) FROM trip ORDER BY date ASC
        ) t4 GROUP BY page_num
    ) p2 ON p2.page_num <= p1.page_num
    GROUP BY p1.page_num
) running_sum ON t.page_num = running_sum.page_num
-- 关联上一页合计
LEFT JOIN (
    SELECT page_num+1 as next_page, page_day prev_day, page_night prev_night
    FROM (
        SELECT page_num, SUM(durationDay) page_day, SUM(durationNight) page_night
        FROM (
            SELECT @page5 := IF(@cum5 + blankRow +1 >5, @page5+1, @page5) as page_num, durationDay, durationNight,
            @cum5 := IF(@cum5 + blankRow +1 >5, blankRow+1, @cum5 + blankRow +1) FROM trip ORDER BY date ASC
        ) t5 GROUP BY page_num
    ) p5
) prev_sum ON t.page_num = prev_sum.next_page
CROSS JOIN (SELECT @page:=1,@cum:=0,@page2:=1,@cum2:=0,@page3:=1,@cum3:=0,@page4:=1,@cum4:=0,@page5:=1,@cum5:=0) vars
ORDER BY t.date ASC

额外性能建议 #

• 给trip表添加(vehicleId, date)联合索引，关联和排序速度可提升30%以上
• 数据量超过10万行时，可按批次查询数据，避免一次性加载过多数据到内存

内容的提问来源于stack exchange，提问作者pilotman