分页接口 pagesize 翻 5 倍、延迟线性飙升——用 MySQL 8 窗口函数一刀切掉 N+1

某后台管理系统有一个数据统计列表接口，pagesize=20 时响应正常，切到 pagesize=100 之后 p99 直接 5 倍增长。APM 一看：一次请求发了 101 条 SQL。典型的 N+1。

奇怪的是，代码里明明有 GROUP BY，怎么还会 N+1？

现象

打开 slow query log，过滤本接口的 SQL，看到如下模式：

一条聚合查询拿到 N 行「事件名 + 次数」列表。
紧接着 N 条形如 SELECT page_name … WHERE event_name = ? GROUP BY page_name ORDER BY COUNT(*) DESC LIMIT 1 的补查，每行一条。

pagesize 从 20 改成 100，补查从 20 条变成 100 条，延迟精准线性增长。

根因

代码逻辑是：先 GROUP BY 拿到事件排行榜，再给每个事件找「访问量最高的页面」。问题在于「TOP-1 per group」这件事无法直接用 GROUP BY 一次完成——GROUP BY event_name 只保留一行，page_name 被随机截断；要想拿到按计数排序的第一名，必须另想办法。

开发同学的本能解法是：循环里再查一遍。

foreach ($events as &$event) {
    // 每个事件多发一条 SQL
    $event['top_page'] = Db::name('tracking_events')
        ->where('event_name', $event['event_name'])
        ->group('page_name')
        ->order('cnt DESC')
        ->limit(1)
        ->value('page_name');
}

坑：GROUP BY 之后想补「组内 Top-1」，很容易滑进 PHP 循环 N+1

GROUP BY 把每组折叠成一行，额外维度（如「这组里哪个 page_name 次数最多」）丢失了。要在同一次查询里恢复这个维度，不能靠子循环——那是把数据库当循环体在用，N 越大越慢。

正解

MySQL 8.0 引入的窗口函数 ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) 可以在不折叠行的前提下给组内每行编号，再用派生表过滤 rn = 1 拿到组内第一。整个过程一条 SQL，没有 PHP 循环，没有额外 round-trip。

// Step 1: 拿事件列表（原有查询，不变）
$events = Db::name('tracking_events')
    ->field('event_name, COUNT(*) as cnt')
    ->group('event_name')
    ->order('cnt DESC')
    ->limit($pageSize)
    ->select()->toArray();
 
// Step 2: 用窗口函数一条 SQL 查全部事件的 Top 页面
$eventNames = array_column($events, 'event_name');
$placeholders = implode(',', array_fill(0, count($eventNames), '?'));
 
$sql = "
    SELECT event_name, page_name, cnt
    FROM (
        SELECT event_name,
               page_name,
               COUNT(*) AS cnt,
               ROW_NUMBER() OVER (
                   PARTITION BY event_name
                   ORDER BY COUNT(*) DESC
               ) AS rn
        FROM tracking_events
        WHERE event_name IN ({$placeholders})
        GROUP BY event_name, page_name
    ) ranked
    WHERE rn = 1
";
$topPages = Db::connect()->query($sql, $eventNames);
 
// Step 3: 内存 map 合并，无额外 SQL
$topPageMap = array_column($topPages, 'page_name', 'event_name');
foreach ($events as &$event) {
    $event['top_page'] = $topPageMap[$event['event_name']] ?? '';
}

SQL 总数：从 1 + N 降到 2（列表 + 窗口函数补查），不随 pagesize 增长。

正解：ROW_NUMBER() OVER (PARTITION BY) 派生表，两条 SQL 定乾坤

把「组内编号」交给数据库引擎完成，PHP 只做内存 map 合并。无论 pagesize 多大，额外 SQL 始终是 1 条。

RANK() vs ROW_NUMBER()：有并列时 RANK 两行都是 1，ROW_NUMBER 随机取一行——只取唯一第一名用 ROW_NUMBER。
想取「每组 Top-N」：把 WHERE rn = 1 改成 WHERE rn <= N 即可。
MySQL 5.7 不支持窗口函数，退路是 GROUP_CONCAT 两段查询或相关子查询。

排查指引

遇到接口延迟随 pagesize 线性增长，先做三步快速确认：

APM / slow log 数 SQL 条数：接口 SQL 数 ≈ pagesize + 1 → 八九不离十是 N+1。
搜 foreach + Db::（或 ORM 方法）：循环体里有数据库调用就是元凶。
看 GROUP BY 后有没有跟一次补查：这是 N+1 最高频的变体之一。

一句话外卖

"每组 Top-1" 是数据库该做的事，把它扔回 PHP 循环就是在用应用层假装数据库——ROW_NUMBER() OVER (PARTITION BY) 是正确归宿。