分页查询慢了 40 秒：两张明细表 JOIN 在同一层

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

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

现象#

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

后面会看到，慢的原因不在索引也不在锁，而在两张明细表相遇时的粒度：聚合前中间集被乘到近 400 万行。把这一步拆开之后，同一副本上从 95.1 秒降到 14.4 秒。

排查#

涉及的表#

先交代表结构，后面所有 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 只能在全量聚合之后再截断，所以没有提前减少中间集。

粒度写在唯一约束里#

回头看表结构里那两个唯一键：(item_id, language_id, platform_id) 和 (item_id, currency_id)。两表共享 item_id，另外半个键互相正交：一边是「语言 × 平台」，一边是「币种」。JOIN 只能按 item_id 对齐，对不上的那半边就只能相乘——一行输入被匹配成多行输出，这个现象一般叫 fan-out（扇出）。拿一个条目 item_id = X 举例，两侧行数取示意值：

这 6 行里没有 3 + 2 = 5 行之外的信息：三个维度最后各自去重，乘出来的组合又被 GROUP_CONCAT(DISTINCT) 压了回去。数据库付了乘法的钱，买到的是加法的结果。

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

这组参数下最后是 1,220 个条目、6.1 万行本地化明细，乘出约 396 万行中间集；下面是这几个数怎么统计出来的。

下面的行数都对应上面同一组 catalog_id、分类和币种过滤参数。为了不从大 JOIN 的结果反推原因，我先分别按 item_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(l.locale_rows * c.currency_rows) AS expected_join_rows,
       SUM(l.locale_rows * c.currency_rows) / SUM(l.locale_rows) AS avg_currencies_per_locale_row
FROM locale_counts l
JOIN currency_counts c ON c.item_id = l.item_id;

这组统计先得到条目数和本地化侧的基础行数，再把每个条目两侧的行数相乘求和，得到中间集：

因为两个 CTE 复刻了原查询的全部过滤条件，SUM(locale_rows × currency_rows) 不是估算，而是原查询聚合前的行数本身：两侧任一为空的条目在原查询里同样不产生行，正好被 CTE 之间的内连接剔掉。

关于 65 这个数的口径：这条统计没有单独输出币种侧的总行数，所以 65 是从乘积反算出来的比值，分母是每条本地化明细行、不是每个条目；两个口径只有在两侧行数互不相关时才相等。后文再出现 65，都按这个口径读。

随后再用 EXPLAIN ANALYZE 交叉验证：Sort 节点实际输出 rows=3.96e+6，与上面算出的约 396 万行相符。GROUP BY i.id 把这些行收敛为 1,220 个条目，LIMIT 最后取其中 500 个。

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

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) 输出 404 万行，约等于 6.1 万本地化明细行乘以调用方传入的币种个数（按比值反推约 66 个，我没有另外记录 IN 列表的实际长度）；随后按 (item_id, currency_id) 唯一键逐行回表，落到 396 万行，命中率约 98%，也就是这个数据集里的条目基本都覆盖了列表中的全部币种。上一节是按 item_id 分别统计，这里读的是计划节点的实际行数，两条独立路径结果相符。

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

最贵的一段不是回表，是聚合之前那次排序。sort_buffer_size 通过 SHOW VARIABLES 读到 256 KB，396 万行很可能需要磁盘排序；这次记录的计划里，真正做 GROUP_CONCAT 的阶段反而最便宜。

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

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

PageableExecutionUtils.getPage 里有个省掉 count 的优化，但触发条件很窄，只有总数能从这一页直接推出来时才成立：

本例落在最后一行，所以每次请求都会执行内容查询和 countQuery。线上 42.7 秒是两条查询共同贡献的请求总耗时，不能直接用只读副本上的 78.5 + 26.9 秒相加得到。

解决方案：拆成三步#

先把「哪些条目在这一页」和「这一页的条目各自聚合出什么」分开，让乘法变成加法。

-- ① 只取当前页的 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 会 fan-out：一个条目命中多少条币种记录就产出多少行，输出基数可以远大于输入，想拿回条目列表还得再 DISTINCT 压回去。这一步的行数比原查询小一到两个数量级，但去重和排序都可能变成 LIMIT 之前必须走完的全量工作（排序是典型的 blocking operator，读完全部输入才能吐出第一行），等于把原查询的结构缩小几十倍重犯一遍。

-- ② 只对页内 ≤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(*) 就是条目数

第 ② 步的工作量因此从相乘变成相加。相乘的前提是两侧相遇时都还是明细粒度：一个条目的 50 行本地化和 65 行币种在同一层按 item_id 对齐，就是 3,250 行。② 把顺序倒过来，两个子查询各自先 GROUP BY item_id，输出在 item_id 上唯一，外层两次 LEFT JOIN 于是都是 1:1，谁也放不大谁；两路读入的行数因此是相加而不是相乘，合计约 5.8 万，逐段行数标在上面的 SQL 注释和示意图里。这些行数是按全集平均值的粗算，不是新的实测值，这一页的 500 个条目也未必正好落在平均值上。

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

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

最后说回 ① 里的 EXISTS。它走的是另一类关系代数算子：semijoin（半连接）。按关系代数里的定义，它的输出是外层关系的一个子集，每个外层行最多留一行，而且内层表的列不出现在结果里（MySQL 文档没有这样下定义，只说它「returns only one instance of each row … that is matched by rows」）。前半句是它不放大行数的原因，后半句是它拿不到币种值、只能把 GROUP_CONCAT 留给第 ② 步的原因。加上 item_currencies 上的 uk_currencies (item_id, currency_id)，它找到第一条命中就可以停，不必读完这个条目的全部币种记录：MySQL 把这个策略叫 FirstMatch，文档的说法是「choose one rather than returning them all」。

这里有两个前提。一是版本：EXISTS 要到 MySQL 8.0.16 之后才和等价的 IN 走同一套 semijoin 变换（同一页文档：「In MySQL 8.0.16 and later, any statement with an EXISTS subquery predicate is subject to the same semijoin transforms as a statement with an equivalent IN subquery predicate」），更早的小版本可能退化成逐行相关子查询。二是「取满 500 行就停」还要求计划以 items 驱动、直接用 i.code 的唯一索引提供顺序；我这次没留下第 ① 步的 EXPLAIN ANALYZE，所以本文不把提前停止当成已验证的部分。

验证#

改写热路径 SQL，光看耗时不够，先要检查结果是否一致。我用同一组线上参数把新旧两版各跑一遍逐行 diff，两边都没有对方缺的行。

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

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

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

副本与线上的机器、并发和缓存状态都不同，这六个数里只有同一轮的 95.1 s 和 14.4 s 可以直接相比。

副本的 innodb_buffer_pool_size 通过 SHOW VARIABLES 读到 1.1 GB，但这次没有采集 buffer pool read、临时表或排序 I/O 指标，因此本文不把冷热差距归因到某一种 I/O 行为。上表里的 1.7 秒与线上 42.7 秒条件不同，也不能直接当作线上收益比例。

代价是 Java 侧不能再让 Spring Data 自动分页，要用 ③ 的计数和 ② 的内容手工组一个 PageImpl，对外的响应字段保持不变。这段组装还没写，加上把其他参数形态也过一遍同样的逐行比对，是上线前剩下的两件事。

教训#

粒度写在唯一约束里。两张明细表如果只共享半个唯一键、另外半边互相正交，我现在会尽量避免在同一层 JOIN 之后再聚合：先各自 GROUP BY 到共享键的粒度，或者像这次一样先把页内 id 定下来再分路聚合。另一条相关的经验：只参与筛选、不需要取值的表，用 EXISTS 而不是 JOIN。

返回 Page 就要记得你可能买了两条查询：在本例这种第一页恰好填满的情形，countQuery 会执行。count 不需要和内容查询同构，但必须保留相同的筛选语义，并按条目去重计数，例如 COUNT(DISTINCT i.id)，或直接 count 第 ① 步得到的条目集合。