DQL 窗口函数使用示例 - 问题详情 - 创脉思

解读

国内一线/二线互联网公司的 PHP 后端面试，数据库能力往往占 30% 以上权重。
“窗口函数”是 MySQL 8.0 之后正式引入的高阶特性，面试官通过它同时考察三点：

你是否还停留在“GROUP BY 只能聚合”的老版本思维；
能否把业务需求翻译成窗口函数，而不是拉回 PHP 内存里二次计算；
对索引、执行计划、大数据量分页的优化意识。
因此，回答时必须给出“业务背景 + 完整 SQL + 关键子句解释 + PHP 侧调用方式”，并主动提到性能与回退方案，才能拿到高分。

知识点

窗口函数语法结构
函数名([DISTINCT] expr) OVER (
[PARTITION BY ...]
[ORDER BY ...]
[{ROWS|RANGE} BETWEEN ...]
)
常用函数：ROW_NUMBER、RANK、DENSE_RANK、LAG、LEAD、SUM/AVG/COUNT 的窗口版本。
与 GROUP BY 区别：窗口函数不减少行数，可直接与原始列同行返回，避免 PHP 二次循环。
执行计划：使用窗口函数时 Extra 列会出现 “Using window; Using temporary”，需关注内存临时表是否落盘。
索引优化：PARTITION BY + ORDER BY 的列顺序应匹配联合索引，否则可能触发 filesort。
回退方案：MySQL 5.7 及以下可用会话变量模拟 ROW_NUMBER，但并发安全性差，需加悲观锁或迁移到 8.0。
PHP 调用：PDO::prepare() 后用 fetchAll(PDO::FETCH_ASSOC) 拿到带排名列的数组，直接 json_encode 给前端，无需再排序。

答案

业务场景
电商秒杀系统，商品表 goods（id, name, stock），订单表 orders（id, goods_id, user_id, amount, created_at）。
需求：实时输出每个商品的最新 3 笔订单，并在 PHP 端直接分页返回，不准把全表拉回内存。

建表与索引（面试时口述即可）

CREATE TABLE goods (
  id bigint PRIMARY KEY,
  name varchar(100),
  stock int,
  KEY idx_stock (stock)
) ENGINE=InnoDB;

CREATE TABLE orders (
  id bigint PRIMARY KEY,
  goods_id bigint,
  user_id bigint,
  amount int,
  created_at datetime,
  KEY idx_gid_ct (goods_id, created_at DESC)
) ENGINE=InnoDB;

窗口函数 SQL

SELECT o.*
FROM (
  SELECT id, goods_id, user_id, amount, created_at,
         ROW_NUMBER() OVER (PARTITION BY goods_id ORDER BY created_at DESC) AS rn
  FROM orders
) AS o
WHERE o.rn <= 3
ORDER BY goods_id, rn;

PHP 侧调用（Laravel 风格，原生 PDO 同理）

$sql = "
  SELECT o.id, o.goods_id, o.user_id, o.amount, o.created_at, o.rn
  FROM (
    SELECT id, goods_id, user_id, amount, created_at,
           ROW_NUMBER() OVER (PARTITION BY goods_id ORDER BY created_at DESC) AS rn
    FROM orders
    USE INDEX (idx_gid_ct)
  ) AS o
  WHERE o.rn <= 3
  ORDER BY goods_id, rn
  LIMIT :limit OFFSET :offset
";

$stmt = DB::connection()->getPdo()->prepare($sql);
$stmt->bindValue(':limit', 20, PDO::PARAM_INT);
$stmt->bindValue(':offset', 0, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

return response()->json($result);

关键解释

ROW_NUMBER() 为每个 goods_id 分区内部按 created_at 倒序编号，得到 rn；
外层 WHERE rn<=3 直接截出“最新 3 笔”，MySQL 8.0 优化器会先下推过滤，减少回表；
idx_gid_ct 联合索引让 PARTITION BY + ORDER BY 走索引，避免 filesort；
PHP 仅接收 20 条记录，内存占用 O(1)，支持大页码深度分页；
若线上仍为 5.7，可升级主从到 8.0，或在从库做只读查询，保证兼容性。

拓展思考

深度分页优化：当 goods 数量巨大，可用“上一页最大 (goods_id, rn)” 做 Seek Method，把 OFFSET 改成 WHERE (goods_id, rn) > (?, ?)，避免扫描前 N 行。
实时排名缓存：把窗口函数结果写入 Redis ZSet，key 为 “goods:{id}:top3”，过期 5 秒，读场景 99% 走缓存，写场景异步刷新。
多指标复合排名：业务要求“按成交金额倒序再按时间倒序”，可在 ORDER BY 里加两列，窗口函数依旧单遍扫描，复杂度 O(n)。
安全陷阱：ROW_NUMBER 生成的序号在分布式主键下可能重复，若需要对外暴露，应再拼接全局唯一标识，防止竞态条件。