MySQL超1亿行数据GROUP BY分页查询优化方案咨询

一、解决MySQL不支持LIMIT+IN子查询的报错 #

你的初始查询触发#1235错误，是因为低版本MySQL不允许IN子查询中嵌套LIMIT。直接把IN逻辑改成JOIN就能绕开这个问题：

MySQL 8.0+用CTE写法 #

WITH top_renters AS (
    SELECT renter, MAX(id) AS latest_support_id
    FROM support
    GROUP BY renter
    ORDER BY latest_support_id DESC
    LIMIT 20
)
SELECT sm.*
FROM support_message sm
JOIN top_renters tr ON sm.support = tr.latest_support_id;

低版本MySQL用子查询JOIN #

SELECT sm.*
FROM support_message sm
JOIN (
    SELECT renter, MAX(id) AS latest_support_id
    FROM support
    GROUP BY renter
    ORDER BY latest_support_id DESC
    LIMIT 20
) tr ON sm.support = tr.latest_support_id;

二、核心优化：添加针对性索引（必须执行） #

所有慢查询的根源都是缺少合适的索引，针对你的场景，这两个索引能直接把查询速度从分钟级压到毫秒级：

1. 给support表加复合索引：

CREATE INDEX idx_support_renter_id ON support(renter, id DESC);

这个索引让GROUP BY renter和MAX(id)操作直接走索引扫描，无需遍历全表——索引已经按租户分组，且id倒序排列，直接就能拿到每个租户的最新support记录ID。

2. 给support_message表加索引：

CREATE INDEX idx_sm_support ON support_message(support);

关联查询时能快速定位到对应support的所有消息，避免全表扫描。

如果需要取每组最新20条消息（而非仅最后一条），给support_message加这个复合索引：

CREATE INDEX idx_sm_renter_id ON support_message(renter, id DESC);

按租户分组取最新20条时，直接走索引就能拿到数据，无需额外排序。

三、替换低效分页方案：告别NOT IN和id< #

大数据量下，WHERE renter NOT IN(...)或WHERE id < ...的分页方式会越来越慢，因为每次都要过滤掉之前所有数据。换成以下两种高效方案：

方案1：基于租户ID的有序分页 #

如果租户ID（renter）是可排序的（如字符串、数字），直接按renter倒序分页：

-- 第一页
SELECT renter, MAX(id) AS latest_support_id
FROM support
GROUP BY renter
ORDER BY renter DESC
LIMIT 20;

-- 下一页：用上一页最后一个租户ID作为条件
SELECT renter, MAX(id) AS latest_support_id
FROM support
WHERE renter < '上一页最后一个renter值'
GROUP BY renter
ORDER BY renter DESC
LIMIT 20;

利用之前添加的idx_support_renter_id索引，查询全程走索引，速度极快。

方案2：游标分页（按消息最新程度排序） #

如果需要按"最新消息的时间/ID"排序分页，用游标记录上一页最后一条数据的特征：

-- 第一页
SELECT renter, MAX(id) AS latest_support_id
FROM support
GROUP BY renter
ORDER BY latest_support_id DESC, renter DESC
LIMIT 20;

记录下最后一条的latest_support_id和renter，下一页查询：

SELECT renter, MAX(id) AS latest_support_id
FROM support
GROUP BY renter
HAVING (MAX(id) < 上一页最后一个latest_support_id) 
   OR (MAX(id) = 上一页最后一个latest_support_id AND renter < 上一页最后一个renter)
ORDER BY latest_support_id DESC, renter DESC
LIMIT 20;

这种方式不会扫描已分页的数据，始终利用索引快速定位游标之后的内容。

四、终极优化：新增汇总表（适合非强实时场景） #

如果数据量过亿，实时分组查询仍有压力，新增一个汇总表存储每个租户的最新记录：

CREATE TABLE support_renter_latest (
    renter VARCHAR(64) PRIMARY KEY, -- 按实际renter类型调整
    latest_support_id BIGINT NOT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_latest_id (latest_support_id DESC)
);

然后用触发器或定时任务更新这个表：

-- 定时任务更新语句（比如每5分钟执行一次）
REPLACE INTO support_renter_latest(renter, latest_support_id)
SELECT renter, MAX(id) FROM support GROUP BY renter;

之后查询和分页直接从这个汇总表取——汇总表的数据量是租户数量，远小于1亿，查询速度会快几个数量级。

如果要存储每组最新20条消息，同理可以新增support_renter_top20表，定时同步每个租户的最新20条消息ID，查询直接从这里取即可。

五、修复现有JOIN查询的效率问题 #

原来的JOIN查询慢是因为没有索引且分组逻辑不合理，加上索引后改成下面的写法会大幅提速：

SELECT sm.*
FROM support_message sm
JOIN (
    SELECT id, renter FROM support
    ORDER BY id DESC
) s ON sm.support = s.id
GROUP BY s.renter
ORDER BY sm.id DESC
LIMIT 20;

不过这个方案的效率仍远不如前面的索引+汇总表方案。

内容的提问来源于stack exchange，提问作者Не Глеб