MySQL 5.7大表分页时如何保证group_id分组不被拆分？

针对你需要分页获取完整group_id分组、避免拆分的需求，这里提供两种可行的实现方案：

方法一：基于分组ID的分页（推荐） #

核心思路是先分页筛选出完整的group_id集合，再关联原表取出这些分组的所有行，从根源上保证分组不被拆分。

步骤说明 #

1. 计算每页需要的分组数量：由于每组最多3行，要获取约2000行数据，可设置每页取CEIL(2000/3)=667个分组（最终返回行数最多为667×3=2001行，基本符合需求）。
2. 分页获取目标group_id列表。
3. 通过JOIN关联原表，取出这些分组的全部数据。

代码示例 #

分页获取分组ID

-- 第1页：获取前667个group_id
SELECT group_id
FROM your_table
GROUP BY group_id
ORDER BY group_id
LIMIT 0, 667;

-- 第2页：获取后续667个group_id
SELECT group_id
FROM your_table
GROUP BY group_id
ORDER BY group_id
LIMIT 667, 667;

关联原表获取完整分组数据

SELECT t.id, t.group_id, t.data
FROM your_table t
INNER JOIN (
    -- 替换这里的offset和group_count实现分页
    SELECT group_id
    FROM your_table
    GROUP BY group_id
    ORDER BY group_id
    LIMIT {offset}, 667
) g ON t.group_id = g.group_id
ORDER BY t.group_id, t.id;

性能优化提示 #

务必给group_id字段添加索引，否则超大表的分组查询会极慢。

方法二：使用变量跟踪分组边界 #

通过MySQL用户变量跟踪分组内的行计数，结合累计行数量确定当前页应包含的完整分组。

代码示例 #

SELECT id, group_id, data
FROM (
    SELECT 
        id, group_id, data,
        @current_group := group_id AS current_group,
        @row_num := IF(@prev_group = group_id, @row_num + 1, 1) AS row_num,
        @prev_group := group_id
    FROM your_table, (SELECT @prev_group := NULL, @row_num := 0) vars
    ORDER BY group_id, id
) t
WHERE (SELECT COUNT(*) FROM (
    SELECT group_id, COUNT(*) AS cnt
    FROM your_table
    GROUP BY group_id
    ORDER BY group_id
) g1
JOIN (
    SELECT group_id, COUNT(*) AS cnt, @total_rows := @total_rows + cnt AS total_rows
    FROM your_table, (SELECT @total_rows := 0) vars
    GROUP BY group_id
    ORDER BY group_id
) g2 ON g1.group_id = g2.group_id
WHERE g2.total_rows <= {start} + 2000) >= (SELECT COUNT(*) FROM (
    SELECT group_id, COUNT(*) AS cnt, @total_rows2 := @total_rows2 + cnt AS total_rows2
    FROM your_table, (SELECT @total_rows2 := 0) vars
    GROUP BY group_id
    ORDER BY group_id
) g3 WHERE g3.total_rows2 <= {start});

注意事项 #

该方法逻辑较复杂，性能不如方法一，仅适合无法修改分页逻辑的场景。

内容的提问来源于stack exchange，提问作者sakura-bloom