分页查询慢了 40 秒：两张明细表同层 JOIN 乘出 396 万行，聚合又压回 1,220 行

一个 Spring Boot + MySQL 8.0 的分页列表接口，绝大多数数据集上不到一秒返回，只有最大的那个数据集要跑 40 多秒，调用方直接超时。

文中表名和业务字段做了脱敏改写，结构与线上一致。线上耗时来自接口观测，其余数据量和副本耗时取自 2026-08-31 至 09-01 的只读副本实测；下面会把直接观测值和由它们算出的平均值、乘积分开说明。

现象#

接口做的事很普通：按数据集分页返回条目列表，每个条目带三串逗号分隔的聚合字段，分别是支持的语言、平台和币种，页大小 500。线上的表现是，同一条 SQL，别的数据集毫秒级到秒级，最大的那个数据集单次请求约 42.7 秒；链路上没有锁等待、没有重试，时间基本都花在数据库里。前一次有人碰到它时的处理方式是把数据库超时调大，这次它涨过了新的超时线，又回来了。

后面会看到，慢的原因不在索引也不在锁：两张粒度不同的明细表在同一层 JOIN，中间集被乘到近 400 万行，而这近 400 万行要到聚合时才收敛回 1,220 行。拆开这一步之后，同一副本上从 95.1 秒降到 14.4 秒。

排查#

五节是一条收敛线：从表结构和查询长什么样开始，量出中间集有多大，再定位时间花在哪一步，最后发现 countQuery 把同样的代价又付了一遍。

涉及的表#

先交代表结构，后面所有 SQL 都建立在这几张表上。列已裁剪到正文用得到的部分，行数是只读副本上 information_schema 的统计估算值：

CREATE TABLE items (                  -- 条目主表，全表约 3.4 万行
  id          INT PRIMARY KEY,
  code        VARCHAR(200) NOT NULL,  -- 对外编码
  name        VARCHAR(255),
  category_id INT NOT NULL,
  catalog_id  INT NOT NULL,           -- 条目所属的数据集
  -- 其余业务列略
  UNIQUE KEY uk_items_code (code)     -- 后文分页排序靠它保证翻页稳定
);

CREATE TABLE item_locales (           -- 明细表一：一行 = 条目 × 语言 × 平台，全表约 150 万行
  id          INT PRIMARY KEY,
  item_id     INT NOT NULL,
  catalog_id  INT NOT NULL,           -- 与 items 同义冗余，便于按数据集直接过滤明细
  language_id INT NOT NULL,
  platform_id INT NOT NULL,
  status      TINYINT,
  -- 本地化名称 / 图片等展示列略
  UNIQUE KEY uk_locales (item_id, language_id, platform_id),
  KEY idx_catalog (catalog_id, language_id, platform_id, status)  -- 按数据集取行的驱动索引
);

CREATE TABLE item_currencies (        -- 明细表二：一行 = 条目 × 币种，全表约 370 万行
  id          INT PRIMARY KEY,
  item_id     INT NOT NULL,
  currency_id INT NOT NULL,
  status      TINYINT NOT NULL,
  UNIQUE KEY uk_currencies (item_id, currency_id)   -- 后文 404 万次逐行探测走它
);

-- languages / platforms / currencies：id + code 的小字典表，行数从个位数到几百

items 与条目一一对应；两张明细表分别记录一个条目在哪些「语言 × 平台」上有本地化内容、支持哪些币种。留意两个唯一键，后面的主角就是它们。

查询长什么样：两张明细表在同一层#

去掉外围细节后，查询主体长这样（另有一个 NOT IN 黑名单子查询和一个取本地化名称的外层 LEFT JOIN，实测两者合计占比很小，本文略去）：

SELECT i.id, i.code, i.name,
       GROUP_CONCAT(DISTINCT l.code ORDER BY l.code) AS languages,
       GROUP_CONCAT(DISTINCT p.code ORDER BY p.code) AS platforms,
       GROUP_CONCAT(DISTINCT c.code ORDER BY c.code) AS currencies
FROM item_locales il                          -- 明细表一：条目 × 语言 × 平台
JOIN items i            ON i.id = il.item_id
JOIN languages l        ON l.id = il.language_id
JOIN platforms p        ON p.id = il.platform_id
JOIN item_currencies ic ON ic.item_id = i.id  -- 明细表二：条目 × 币种，与 il 同层
JOIN currencies c       ON c.id = ic.currency_id
WHERE il.catalog_id = ?
  AND il.status = 1 AND ic.status = 1
  AND ic.currency_id IN (/* 调用方配置 */)
  AND i.category_id   IN (/* 调用方分类配置 */)
GROUP BY i.id
ORDER BY i.code
-- Spring Data 在这段外面追加 LIMIT 500

三个 GROUP_CONCAT 想一次拿齐三个维度，于是把两张明细表拉进了同一层 JOIN。两个后果都写在这段里：聚合在全量数据上完成，LIMIT 只能在聚合之后截断，减不了中间集；而单独留下任何一张明细表都不慢，慢是两张表在同一层相遇之后才出现的。一行进去、多行出来，这个现象一般叫 fan-out（扇出）。

中间集有多大：约 396 万行#

这组参数下最后是 1,220 个条目。为了不从那个大 JOIN 的结果反推原因，我先按 item_id 分别统计两张明细表，再把每个条目两侧的行数相乘求和。行数和原查询用同一组 catalog_id、分类、币种过滤参数，黑名单等已省略的外围条件在统计里原样保留：

WITH locale_counts AS (
    SELECT il.item_id, COUNT(*) AS locale_rows
    FROM item_locales il
    JOIN items i ON i.id = il.item_id
    JOIN languages l ON l.id = il.language_id
    JOIN platforms p ON p.id = il.platform_id
    WHERE il.catalog_id = ? AND il.status = 1
      AND i.category_id IN (?)
    GROUP BY il.item_id
),
currency_counts AS (
    SELECT ic.item_id, COUNT(*) AS currency_rows
    FROM item_currencies ic
    JOIN currencies c ON c.id = ic.currency_id
    WHERE ic.status = 1 AND ic.currency_id IN (?)
    GROUP BY ic.item_id
)
SELECT COUNT(*) AS item_count,
       SUM(l.locale_rows) AS locale_rows,
       SUM(c.currency_rows) AS currency_rows,
       SUM(l.locale_rows * c.currency_rows) AS join_rows
FROM locale_counts l
JOIN currency_counts c ON c.item_id = l.item_id;

四个数都是这条统计直接给出的。其中 join_rows 约 396 万是原查询 GROUP BY 之前的行数本身，不是估算：两个 CTE 复刻了原查询的全部过滤条件，两侧任一为空的条目在原查询里同样不产生行，正好被 CTE 之间的内连接剔掉。后面反复用到的每条目 50 行、65 行，是拿两侧总量各除以 1,220 得到的平均值，这一页的 500 个条目未必正好落在平均值上。

拿这两个平均数就能把整条路走通：每个条目 50 × 65 = 3,250 行，1,220 个条目 396 万行，GROUP BY 压回 1,220 行，LIMIT 最后取 500 行。EXPLAIN ANALYZE 也交叉验证过：Sort 节点实际输出 rows=3.96e+6，和算出来的一致。

时间花在哪：贵的是聚合前那次排序#

MySQL 8.0.18 起可以用 EXPLAIN ANALYZE 拿到每个节点的实际执行信息。在只读副本上按线上参数跑一次（未预热），主查询 78.5 秒；这个数来自根节点的 actual time=74109..78456，取末值 78.456 秒后四舍五入。计划主干如下（标识符已脱敏；actual time 的两个数是返回首行和末行的毫秒时间）：

-> Group aggregate: group_concat(...)  (actual time=74109..78456 rows=1220)
  -> Sort: i.id  (actual time=74105..75852 rows=3.96e+6)
    -> Nested loop inner join  (actual time=2322..29094 rows=3.96e+6)
      -> Inner hash join (no condition)  (... rows=4.04e+6)
      -> Single-row index lookup on item_currencies  (loops=4.04e+6)

Inner hash join (no condition) 没有连接条件，它把 6.1 万行本地化明细和 IN 列表里的币种逐个配对，输出 404 万行，反推出那串 IN 里有 404 万 ÷ 6.1 万 ≈ 66 个币种（我没有另外记录它的实际长度）。紧接着按 (item_id, currency_id) 唯一键逐行探测 404 万次，落到 396 万行，命中率约 98%。66 和上一节那个 65 是同一个量的两次独立测量：每个条目的币种行数就是那串 IN 的长度。

按几个关键节点的时间窗口粗略分段，耗时大致是这样分布的。这些数字用于定位阶段，不是严格的 iterator self time；MySQL 的 iterator 时间包含子节点，多循环时还可能是每次循环的平均值，不能简单乘以 loops。

最贵的一段不是乘本身，是乘完之后那次排序：GROUP BY 这一轮走的是「先按分组键排完再聚合」，Sort 的代价直接跟着聚合前的行数走，而聚合自己只花了 6%。sort_buffer_size 用 SHOW VARIABLES 读到 256 KB，396 万行多半落到磁盘排序。乘出来的行数不只多读了些行，它还成倍放大了排序的输入。

countQuery：同样的乘法再付一次#

仓储方法返回 Spring Data 的 Page，于是还有一条 countQuery。它省掉了几个查名字的 JOIN，但乘法核心原样保留；在同一只读副本上单独运行得到 26.9 秒，同样物化近 400 万行，最后只为得出 1,220 这一个数字。

PageableExecutionUtils.getPage 里有个能省掉 count 的优化，但只有总数能从这一页本身推出来时才成立（unpaged、或返回不满一页）。本例第一页恰好取满 500 行，不在其中，于是每次请求两条查询都跑。线上 42.7 秒是这两条合起来的请求耗时，不能拿副本上的 78.5 + 26.9 秒去对。count 其实不必和内容查询同构，它只需要筛选语义一致，再按条目去重计数：COUNT(DISTINCT i.id)，或者数一个已经去重到条目粒度的集合。

问题分析：乘法造出来的行，答案只要加法#

上面几节量的是「有多大、多贵」。剩下的问题是这 396 万行为什么会被造出来，以及其中有多少是必要的。

乘法从哪来：两个唯一键只对上一半#

再看表结构里那两个唯一键：item_locales 是 (item_id, language_id, platform_id)，item_currencies 是 (item_id, currency_id)。它们只有 item_id 这一列对得上，剩下半截各说各话，一边是「语言 × 平台」，一边是「币种」。

对不上的部分只能相乘：JOIN 按 item_id 把两张表凑到一起，一个条目的 3 行本地化和 2 行币种两两配对，出来 6 行。乘法不是 JOIN 自带的能力，它只取决于共享键之外还剩多少列。如果 JOIN 的键在一侧唯一（1:1），行数就不会放大，这也是单独查任一张明细表都不慢的原因。

要的答案只需要加法#

三个 GROUP_CONCAT(DISTINCT) 各读各的：languages 和 platforms 只用到 item_locales，currencies 只用到 item_currencies，谁也不关心对方那一列取什么值。所以那 6 行携带的信息和 3 + 2 = 5 行一样多，乘出来的组合被 DISTINCT 原样压了回去。数据库付了一次乘法的钱，买到的是加法的结果；而且因为 DISTINCT 压得干净，结果看不出毛病，代价也就从结果上看不出来。

按全集摊开更直白：三个字段真正需要的输入是每个条目 50 + 65 = 115 行、1,220 个条目约 14 万行（同样是按平均值的粗算），而同一层 JOIN 实际物化了 396 万行。多出来的这 28 倍不产生任何一个新字段，它们先被造出来、再被 DISTINCT 消掉，中间还成倍放大了聚合前那次排序的输入。

什么时候拆不开#

「乘法换加法」只在聚合是各维度单独去重时成立。判断标准是聚合函数的输入：每个 GROUP_CONCAT 的参数都只来自一张明细表，两侧就可以先各自收敛到 item_id 粒度；哪天要的是「语言 × 币种」的组合（比如「这个条目在 zh 下支持哪些币种」），乘出来的行本身就是答案，拆不开。

下面这张图把两种顺序摆在一起，示意图里的行数为示意值。

396 万这个数于是有了两层来源：条目层上 1,220 个条目全都参与了聚合，LIMIT 只能在聚合之后截断；每个条目内部又各自乘了一遍。

解决方案：拆成三步#

把「哪些条目在这一页」和「这一页的条目各自聚合出什么」拆开：前者没有明细表参与，乘法不发生；后者两侧各自先聚合，乘法换成加法。

-- ① 只取当前页的 id：没有聚合、没有乘法，LIMIT 真正生效
SELECT i.id, i.code
FROM (SELECT DISTINCT il.item_id                    -- 明细去重到条目粒度，后面不再有明细行
      FROM item_locales il
      JOIN languages l ON l.id = il.language_id
      JOIN platforms p ON p.id = il.platform_id
      WHERE il.catalog_id = ? AND il.status = 1) t
JOIN items i ON i.id = t.item_id                    -- 1:1，t.item_id 已唯一
WHERE i.category_id IN (?)
  AND EXISTS (SELECT 1 FROM item_currencies ic      -- semijoin：每个条目最多留 1 行
              JOIN currencies c ON c.id = ic.currency_id
              WHERE ic.item_id = i.id AND ic.status = 1
                AND ic.currency_id IN (?))          -- 币种从 JOIN 降级为存在性判断，命中即停
ORDER BY i.code                                     -- 走 uk_items_code，翻页顺序稳定
LIMIT 500 OFFSET 0;                                 -- 剩 1,220 个候选条目，取其中 500 行

EXISTS 那一行不是写法偏好，它是第 ① 步不会被乘开的原因。换成 JOIN，一个条目命中多少条币种记录就产出多少行，还得再 DISTINCT 压回去，代价虽然比原查询小一两个数量级，结构却还是原查询那个结构。semijoin（半连接）的定义挡住了这一步：输出是外层行的子集，每个外层行最多留一行，且内层表的列不出现在结果里（MySQL 文档只说它 “returns only one instance of each row … that is matched by rows”）。前半句是它不放大行数的原因，后半句是它拿不到币种值、GROUP_CONCAT 只能留给第 ② 步的原因。配上 uk_currencies (item_id, currency_id)，它找到第一条命中就能停，MySQL 把这个策略叫 FirstMatch。

-- ② 只对页内 ≤500 个 id 做两路独立聚合：各自先收敛到 item_id 粒度，再 LEFT JOIN
SELECT i.id, i.code, i.name, ll.languages, ll.platforms, cc.currencies
FROM items i
-- ②a 读约 500 × 50 ≈ 2.5 万行本地化明细
LEFT JOIN (SELECT il.item_id,
                  GROUP_CONCAT(DISTINCT l.code ORDER BY l.code) AS languages,
                  GROUP_CONCAT(DISTINCT p.code ORDER BY p.code) AS platforms
           FROM item_locales il
           JOIN languages l ON l.id = il.language_id
           JOIN platforms p ON p.id = il.platform_id
           WHERE il.item_id IN (:ids) AND il.catalog_id = :catalogId AND il.status = 1
           GROUP BY il.item_id) ll ON ll.item_id = i.id   -- 出 ≤500 行，item_id 唯一
-- ②b 读约 500 × 65 ≈ 3.3 万行币种明细
LEFT JOIN (SELECT ic.item_id,
                  GROUP_CONCAT(DISTINCT c.code ORDER BY c.code) AS currencies
           FROM item_currencies ic
           JOIN currencies c ON c.id = ic.currency_id
           WHERE ic.item_id IN (:ids) AND ic.status = 1
             AND ic.currency_id IN (:currencyIds)
           GROUP BY ic.item_id) cc ON cc.item_id = i.id   -- 出 ≤500 行，item_id 唯一
WHERE i.id IN (:ids)
ORDER BY i.code;
-- 读入相加约 5.8 万行

-- ③ count：把 ① 的 SELECT 换成 COUNT(*)，去掉 ORDER BY / LIMIT，
--    其余 WHERE 和 EXISTS 原样不动 → 1,220
--    ① 的子查询已 DISTINCT 到条目粒度，所以这里 COUNT(*) 就是条目数

第 ② 步里两侧相遇时都已经在 item_id 上唯一，外层两次 LEFT JOIN 都是 1:1，谁也放不大谁，读入于是相加：约 2.5 万 + 3.3 万 ≈ 5.8 万行。逐段行数标在 SQL 注释里，是按全集平均的粗算，不是实测值。

做个对照：如果只改第 ① 步、把两张明细表仍然留在同一层聚合，页内 500 个条目也要物化 500 × 50 × 65 ≈ 163 万 行。所以这两步治的是两个不同的病：① 让 LIMIT 真正生效，把范围从 1,220 个条目收窄到 500 个；② 让这 500 个条目内部不再相乘，又降一个数量级。

GROUP_CONCAT 里的 DISTINCT 不能跟着去掉：②a 内部还有「语言 × 平台」这一层小 fan-out，同一个 language code 会在多个 platform 上重复出现。

两个前提：EXISTS 要到 MySQL 8.0.16 及以后才和等价的 IN 走同一套 semijoin 变换，更早的小版本可能退化成逐行相关子查询；「取满 500 行就停」还要求计划以 items 驱动、直接用 uk_items_code 提供顺序。我没给第 ① 步留 EXPLAIN ANALYZE，提前停止就不算已验证。

验证#

热路径 SQL 改写不能只看耗时，先看结果。我用同一组线上参数把新旧两版各跑一遍逐行 diff：500 行输出逐字段一致（含顺序），count 两边都是 1,220，两边都没有对方缺的行。

这里有个前提：原查询里的 languages / platforms / currencies 都是内连接，会滤掉对应字典表中查不到的行。第 ① 步现在也保留了这些连接，因此不会仅因为孤儿行改变条目集合；如果落地时为了省事去掉这些字典表连接，就需要另外确认三类外键都没有孤儿行。item_id 是否只属于一个 catalog 也要明确；如果可能跨 catalog 复用，第 ② 步必须保留 catalog_id 条件。逐行 diff 只能证明这组参数下没有踩到未覆盖的数据形态。

耗时上故意用了保守的测试顺序：新方案先跑，旧查询后跑、反而受益于预热。这里的「未预热」只是指它跑在这一轮最前面，我并没有主动清过 buffer pool；这对结论足够，因为它要说明的只是新方案没占到预热的便宜。

全文出现过六个秒数，条件各不相同，先摆在一起：

• 这六个数里只有同一轮的 95.1 s 和 14.4 s 可以直接相比，副本与线上的机器、并发、缓存状态都不同，1.7 s 也不能拿来推算线上收益比例。
• 副本的 innodb_buffer_pool_size 用 SHOW VARIABLES 读到 1.1 GB，但这次没采集 buffer pool read、临时表和排序 I/O 指标，冷热差距归不到某一种 I/O 行为上。

代价是 Java 侧不能再让 Spring Data 自动分页，得用 ② 的内容和 ③ 的计数自己组一个 PageImpl（对外的响应字段保持不变）。这一步我还没动手。

小结#

这组参数下，两张明细表在同一层 JOIN 把 1,220 个条目的明细乘到约 396 万行，而最贵的一段不是乘本身，是乘完之后那次排序（57%）；拆成三步后，同一轮里 95.1 秒变成 14.4 秒。