面试聊到SQL问到后面基本都会落到同一个地方怎么取组内前几名。这个问题看起来简单但它的答案分水岭非常明显——只会 GROUP BY 的人会卡住会用窗口函数的人三行写完。窗口函数Window Function是 MySQL 8.0 引入的重头戏也是这几年数据库岗、后端岗、数据分析岗面试里出现频率最高的考点之一问法从ROW_NUMBER 和 RANK 有什么区别到连续登录7天的用户怎么找再到窗口函数的性能你怎么评估一层比一层深。这篇内容我按面试官的真实提问顺序来组织先把 OVER() 的语法骨架拆干净再用五类高频真题从建表造数一路写到出结果然后往下钻到窗口帧、执行顺序、索引与执行计划这些加分项最后把我自己踩过的坑和常见追问整理成速查表。文中所有 SQL 都可以直接复制到本地 8.0 环境跑涉及参数和版本差异的地方我会明确标出来。不管你是在准备面试还是要在生产里把一段跑不动的自连接改成窗口函数应该都能直接抄作业。1. 窗口函数为什么会成为面试分水岭1.1 一道真题怎么把组内前N写对我拿这道题做过很多次对照实验一张员工薪资表取每个部门薪资最高的前 2 名。用传统写法标准答案一般是自连接或者相关子查询代码长、可读性差而且数据量一上来就崩。用窗口函数主体就一段子查询加一个rn 2。为什么面试官偏爱这种题因为它同时考察三件事你知不知道窗口函数存在、你分不分得清三种排名函数的差异、你懂不懂窗口函数不能写在 WHERE 里这个硬性限制。第三点特别关键很多人第一次写就会写成下面这样-- 错误写法窗口函数不能出现在 WHERE 中 SELECT dept_id, emp_name, salary FROM emp_salary WHERE ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) 2;MySQL 会直接报语法错误。原因稍后在第 4 章讲执行顺序时会说透这里先记住结论窗口函数的计算发生在 WHERE 之后所以它没法反过来给 WHERE 当过滤条件必须套一层子查询或者 CTE把排名结果物化成普通列外层再过滤。1.2 窗口函数与GROUP BY的本质差异很多人把窗口函数理解成加强版 GROUP BY这个类比方向对但会误导。两者最本质的区别是GROUP BY 会把多行压成一行窗口函数不会改变行数。窗口函数是在每一行上侧着看一眼它所属的那一组把聚合结果贴回原来的行上。举个具体例子。同样是算部门总薪资GROUP BY dept_id之后一个部门只剩一行你拿不到每个员工的明细。SUM(salary) OVER (PARTITION BY dept_id)之后每个员工那一行都多了一列本部门总薪资明细一行没少。这个差异直接决定了适用场景。要做报表汇总、按维度降维用 GROUP BY要做明细汇总同屏、组内排名、组内占比、环比同比窗口函数才是正解。面试里如果被问什么时候不该用窗口函数一个稳妥的回答是结果集本身就需要降维的时候硬上窗口函数等于先展开再聚合纯属浪费。还有个容易被忽略的点窗口函数和 GROUP BY 是可以同时出现在一条 SQL 里的。看下面这段SELECT dept_id, SUM(salary) AS dept_total, ROUND(SUM(salary) / SUM(SUM(salary)) OVER () * 100, 2) AS pct_of_all FROM emp_salary GROUP BY dept_id;SUM(SUM(salary)) OVER ()这层嵌套看着别扭但逻辑很干净内层SUM(salary)是分组聚合的结果外层再把它当成一个普通表达式做整窗求和。这条正是热搜里sql group 窗口函数想问的东西——窗口函数作用在聚合之后的结果集上而不是原始表上。理解这一层很多聚合函数套窗口函数的写法就不神秘了。1.3 先把环境对齐确认你的版本支持窗口函数动手之前先确认版本这是最容易被跳过、也最容易白折腾的一步。窗口函数是 MySQL 8.0.2 引入的5.7 及以前完全没有。不同版本的行为差异也实打实存在比如EXPLAIN ANALYZE要 8.0.18 才有GROUPS帧类型虽然 8.0 一开始就支持但早期小版本 bug 相对多。生产上建议至少 8.0.30新项目直接上 8.4 LTS。一句命令确认SELECT VERSION();想搭个干净的练习环境用容器是最省事的不会污染本机已经装好的实例docker run -d --name mysql8-lab \ -e MYSQL_ROOT_PASSWORDRoot_1234 \ -p 13306:3306 \ -v ~/mysql-lab/data:/var/lib/mysql \ mysql:8.0 \ --character-set-serverutf8mb4 \ --collation-serverutf8mb4_0900_ai_ci这里有两个我踩过的坑。第一端口别直接用 3306本机如果已经有 MySQL 在跑会冲突映射成 13306 更稳。第二-v挂载目录一定要先在宿主机建好权限给足否则容器起来后初始化会失败docker logs里一堆权限报错。连上去之后客户端用命令行、MySQL Workbench、DBeaver 都行我个人练手习惯用命令行因为能清楚看到每一列的对齐关系跑窗口函数时尤其有用。提示如果你手上只有 5.7 环境窗口函数的题目照样能练但要换写法用户变量或自连接具体在第 5 章给对照方案。不过要注意 8.0 里用户变量赋值在 SELECT 中的求值顺序不再保证5.7 时代那种一行搞定的排名写法在 8.0 上不可靠。2. 语法骨架拆解OVER()里到底放了什么2.1 三个子句PARTITION BY、ORDER BY、帧窗口函数的完整语法长这样函数名(...) OVER ( PARTITION BY 分区列 ORDER BY 排序列 帧定义 )三个部分各管一件事缺省行为差别很大。PARTITION BY决定分组边界等价于 GROUP BY 的分组但它不合并行。不写 PARTITION BY 时整张结果集就是一个大窗口这点常被用来算全局占比、全局排名。ORDER BY决定窗口内的顺序。这是最容易被低估的一处只要你在 OVER() 里写了 ORDER BY默认帧就从整个分区变成从分区第一行到当前行。这意味着SUM(x) OVER (ORDER BY d)算出来的不是总和而是累计和。很多人第一次写累计求和成功了第二次只想算总和却忘了去掉 ORDER BY结果拿到一列递增的数字排查半天。帧Frame决定当前行往前看多少、往后看多少。它只在有 ORDER BY 的时候才真正有意义。完整的帧语法ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW三种帧类型的细节在第 4 章展开这里先建立一个直觉ROWS数的是物理行数RANGE数的是排序值GROUPS数的是并列值的组数。另外提一个让代码变干净的写法——命名窗口SELECT dept_id, emp_name, salary, ROW_NUMBER() OVER w AS rn, RANK() OVER w AS rk, SUM(salary) OVER w AS dept_salary_sum FROM emp_salary WINDOW w AS (PARTITION BY dept_id ORDER BY salary DESC);一条 SQL 里反复写同样的 OVER() 内容既啰嗦又容易改漏WINDOW 名字 AS (...)提出来之后复用可作为面试时的加分细节。2.2 排名类函数ROW_NUMBER、RANK、DENSE_RANK、NTILE这四个是面试出现率最高的尤其是前三个的区别几乎每场必问。用一句话概括ROW_NUMBER 不管并列、RANK 跳跃、DENSE_RANK 不跳跃。拿前面部门1的数据看张伟 28000、李娜 32000、王强 32000、赵敏 21000按薪资降序姓名薪资ROW_NUMBERRANKDENSE_RANK李娜32000111王强32000211张伟28000332赵敏21000443关键点在于两行并列第一之后RANK 直接跳到 3DENSE_RANK 还是 2。这个差异在实际业务里是有语义的取薪资排名前三的员工如果并列第二名也算那用 DENSE_RANK 会多出来人如果要严格只取 3 个人必须用 ROW_NUMBER。ROW_NUMBER 有个隐藏陷阱——它是不确定的。上面李娜和王强薪资一样谁拿 1 谁拿 2 完全取决于存储引擎返回的顺序同一台机器上多跑几次、加了索引之后重建结果可能就变了。生产上如果要把这个结果落库或者做对账务必在 OVER() 的 ORDER BY 里加一个唯一列做兜底比如ORDER BY salary DESC, id ASC。这个细节我在一次数据对账里吃过亏两边系统用的都是 ROW_NUMBER数据一样跑出来的第一名却不是同一个人查了大半天。NTILE(n)是把分区均分成 n 桶用于分位数分析。它也有个坑如果行数除不尽前面的桶会比后面的多一行比如 7 行分 3 桶是 3、2、2 而不是 3、3、1。做等频分箱的时候要知道这一点否则分布会略微倾斜。还有两个偏统计的PERCENT_RANK()返回(rank - 1) / (总行数 - 1)CUME_DIST()返回小于等于当前值的行数占比。做用户分层、绩点百分位的时候有用面试问到的频率不高但能说出来会显得体系比较完整。2.3 取值类函数LAG、LEAD、FIRST_VALUE、LAST_VALUE、NTH_VALUE这一族是解决跨行取值的环比同比、和上一次比较、取首尾值全靠它们。LAG(expr, offset, default)取当前行往前第 offset 行的值LEAD是往后。三个参数里第三个默认值参数千万别省。不写的话第一行取上月数据会返回 NULL然后你拿 NULL 去做除法或者加减整列结果全变 NULL。写LAG(amount, 1, 0)至少保证算出来是个数。SELECT month_str, amount, LAG(amount, 1, NULL) OVER (ORDER BY month_str) AS prev_amount, LEAD(amount, 1) OVER (ORDER BY month_str) AS next_amount FROM monthly_sales;FIRST_VALUE/LAST_VALUE/NTH_VALUE取窗口帧内的第一个、最后一个、第 n 个值。这里面LAST_VALUE 有一个经典陷阱值得单独说。-- 陷阱写法得到的是当前行的值不是分区最后一行的值 SELECT user_id, order_date, amount, LAST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS last_amt FROM order_detail;因为默认帧是从分区首行到当前行当前行恰好就是这个帧的最后一行所以 LAST_VALUE 返回的永远是它自己。要取真正的末值必须把帧显式撑到分区末尾SELECT user_id, order_date, amount, FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_amt, LAST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amt FROM order_detail;同一个原因FIRST_VALUE一般「碰巧」是对的第一行永远是首行而LAST_VALUE十有八九是错的。这个不对称性非常坑我在面试里问过几次能主动提出来的人不到三成。注意窗口函数不能嵌套。SUM(ROW_NUMBER() OVER (...)) OVER (...)这种写法直接报错。要嵌套就先落成子查询或 CTE外层再开一个窗口。2.4 聚合函数的窗口化与GROUP BY配合使用的正确姿势SUM、AVG、COUNT、MAX、MIN这五个聚合函数加上 OVER() 就变成了窗口聚合这是最实用的一类也是面试里最容易出彩的。按用途分窗口聚合常见三种形态整窗聚合——不带 ORDER BY返回整个分区的值用于算占比、和组内均值比较SELECT order_id, user_id, amount, ROUND(amount / SUM(amount) OVER (PARTITION BY user_id) * 100, 2) AS pct FROM order_detail;累计聚合——带 ORDER BY 不写帧默认就是累计SELECT user_id, order_date, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS running_total FROM order_detail;滑动聚合——显式指定帧用于移动平均SELECT order_date, ROUND(AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS ma7 FROM daily_amount;这里有个反直觉的地方值得强调累计聚合和 GROUP BY 的聚合结果看起来一样但语义完全不同。SUM(x) OVER (PARTITION BY u ORDER BY d)算的是截止当前行的累计它的值随行变化SUM(x) ... GROUP BY u算的是该组的总额组内所有行是同一个值。面试里被要求算每个用户截止每天的累计消费如果答成 GROUP BY 就彻底跑偏了。另外一个细节窗口 COUNT 里的COUNT(*)和COUNT(列名)行为差异和普通聚合一致前者数行数后者不数 NULL。做每个用户第几笔订单的时候我更倾向用 ROW_NUMBER 而不是 COUNT因为 COUNT 遇到重复时间戳会有并列歧义ROW_NUMBER 加上 ID 兜底后结果唯一。3. 高频真题实战从建表造数到拿到结果3.1 造两张能覆盖80%题型的表先把练习环境准备好两张表基本能覆盖面试里绝大多数窗口函数题一张员工薪资表考组内排名一张订单/登录表考时间序列类的题。DROP TABLE IF EXISTS emp_salary; CREATE TABLE emp_salary ( id BIGINT PRIMARY KEY AUTO_INCREMENT, dept_id INT NOT NULL, emp_name VARCHAR(32) NOT NULL, salary DECIMAL(10,2) NOT NULL, hire_date DATE NOT NULL, KEY idx_dept_salary (dept_id, salary) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO emp_salary (dept_id, emp_name, salary, hire_date) VALUES (1,张伟,28000,2019-03-11), (1,李娜,32000,2018-07-02), (1,王强,32000,2020-01-15), (1,赵敏,21000,2021-05-20), (2,陈磊,45000,2017-09-01), (2,刘洋,26000,2020-11-03), (2,孙宇,19000,2022-02-18), (3,周涛,15000,2021-08-09), (3,吴迪,15000,2020-06-25), (3,郑凯, 9000,2022-04-01);DROP TABLE IF EXISTS order_detail; CREATE TABLE order_detail ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_date DATE NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 1, KEY idx_user_date (user_id, order_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;索引不是随便加的。idx_dept_salary (dept_id, salary)的列顺序刻意和PARTITION BY dept_id ORDER BY salary对齐idx_user_date (user_id, order_date)对齐PARTITION BY user_id ORDER BY order_date。这不是装饰——在第 4 章的性能对比里你会看到索引顺序匹配时排序开销能明显下降不匹配时会多出一次 filesort。再看登录表连续N天这类题的标配DROP TABLE IF EXISTS user_login; CREATE TABLE user_login ( user_id INT NOT NULL, login_date DATE NOT NULL, PRIMARY KEY (user_id, login_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO user_login (user_id, login_date) VALUES (1001,2024-03-01),(1001,2024-03-02),(1001,2024-03-03), (1001,2024-03-05),(1001,2024-03-06), (1002,2024-03-01),(1002,2024-03-03),(1002,2024-03-04),(1002,2024-03-05), (1003,2024-03-02),(1003,2024-03-03);刻意把用户1001的登录日期做成断开的3月3日之后跳到3月5日这样能立刻验证你的连续判断逻辑是不是真的对——很多人写的SQL在连续数据上跑得通一遇到断点就露馅。3.2 组内TopN三种排名函数的取舍先看最标准的解法取每个部门薪资前 2 名SELECT dept_id, emp_name, salary, rn FROM ( SELECT dept_id, emp_name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, id ASC) AS rn FROM emp_salary ) t WHERE rn 2 ORDER BY dept_id, rn;跑出来的结果部门1是李娜(32000)和王强(32000)部门2是陈磊和刘洋部门3是周涛和吴迪。注意部门1两个人薪资相同谁排第一取决于id ASC这个兜底条件——这就是我前面强调的确定性。现在换个问法如果并列都算比如取薪资排名前三含并列那就要用 DENSE_RANKSELECT dept_id, emp_name, salary, dr FROM ( SELECT dept_id, emp_name, salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dr FROM emp_salary ) t WHERE dr 2 ORDER BY dept_id, dr, salary DESC;这里结果会多出来人部门1的 dr2 会返回三个员工李娜、王强、张伟因为 DENSE_RANK 把两个并列第一压成一个名次28000 的张伟就成了第二名。那 RANK 用在哪它的语义是竞赛排名——两个第一下一个就是第三。业务上对应的是跳过名次的场景比如绩效强制分布、竞赛榜单。三者怎么选我一般按这个判断需求描述该用哪个理由严格取 N 条记录ROW_NUMBER保证行数不多不少取前 N 个名次并列全要DENSE_RANK名次连续不错过并列竞赛式排名跳过占位RANK符合名次跳号语义按比例切分桶NTILE等频分箱实操心得写完 TopN 之后一定用SELECT COUNT(*)对一下总数。我遇到过一次 DT 引擎切换导致 ROW_NUMBER 的行数莫名多出几十条最后定位是上游数据有重复主键。TopN 类需求对重复数据极其敏感加一步总数校验能省掉后面几个小时的排查。3.3 连续登录N天差值分组法这道题是窗口函数的进阶门槛面试里出现率极高核心思路是date - row_number 得到分组标记gap and island。原理不难。如果日期是连续的那么每个日期减去它在该用户内的行号得到的结果是同一个常量login_daterow_numberdate - rn2024-03-0112024-02-292024-03-0222024-02-292024-03-0332024-02-292024-03-0542024-03-01连续段内这个差值恒定一旦日期断开差值就变了。于是连续就转化成了按差值分组标准聚合就能解决。SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp FROM user_login ) t GROUP BY user_id, grp HAVING COUNT(*) 3 ORDER BY user_id, start_date;跑完你会看到只有用户1001的 03-01 到 03-03 被返回用户1002那几天是断开的用户1003只有两天。这个结果正好验证了逻辑的正确性如果你写的SQL把1002也返回了多半是漏了 PARTITION BY user_id 或者忘了去重。注意如果上游表可能有同一天重复登录的记录比如加了秒级时间戳必须先DISTINCT或者先按天聚合再排序否则 ROW_NUMBER 会把同一天编成两个号连续段被硬生生拆开结果全错。这个坑我在线上见过两次第二次是因为上游改了埋点逻辑同一天产生了多条记录。如果问的是连续N天的起止日期或者最大连续天数在上面基础上再包一层就行SELECT user_id, MAX(continuous_days) AS max_continuous FROM ( SELECT user_id, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date) DAY) AS grp FROM user_login ) t GROUP BY user_id, grp ) s GROUP BY user_id;注意这里出现了窗口函数 → 子查询 → GROUP BY → 外层 GROUP BY的三层结构这是窗口函数题的典型形态。写的时候建议一层一层跑每层都看一眼中间结果比一次性写完再调试快得多。3.4 环比同比与占比LAG和整窗SUM时间序列类的题基本绕不开 LAG。先按月聚合再算环比SELECT month_str, amount, LAG(amount, 1) OVER w AS prev_amount, ROUND((amount - LAG(amount, 1) OVER w) / LAG(amount, 1) OVER w * 100, 2) AS mom_pct, LAG(amount, 12) OVER w AS last_year_amount, ROUND((amount - LAG(amount, 12) OVER w) / LAG(amount, 12) OVER w * 100, 2) AS yoy_pct FROM ( SELECT DATE_FORMAT(order_date, %Y-%m) AS month_str, SUM(amount) AS amount FROM order_detail WHERE status 1 GROUP BY month_str ) t WINDOW w AS (ORDER BY month_str) ORDER BY month_str;三个细节值得说。第一月份必须先在子查询里聚合。窗口函数作用在聚合结果上所以SUM(amount)的 GROUP BY 要放在内层。如果直接在外层对明细行算环比结果就是每一行相对上一行的变化和业务要的月度环比完全不是一回事。第二用DATE_FORMAT生成月份字符串时要确认排序正确。2024-03这种格式字典序和时间序一致可以直接 ORDER BY如果写成2024-3字典序就乱了2024-10会排在2024-2前面。要么改成补零格式要么用DATE_FORMAT(order_date,%Y-%m-01)再转成日期类型。这个坑很隐蔽数据只有两三个月的时候根本看不出来。第三除零风险。prev_amount为 0 或者 NULL 时mom_pct会变成 NULL 或者报错。稳妥写法是套NULLIF或者CASE WHENROUND((amount - prev_amount) / NULLIF(prev_amount, 0) * 100, 2) AS mom_pct占比类的问题更简单整窗聚合一行搞定SELECT dept_id, emp_name, salary, ROUND(salary / SUM(salary) OVER (PARTITION BY dept_id) * 100, 2) AS pct_in_dept, ROUND(salary / SUM(salary) OVER () * 100, 2) AS pct_in_all FROM emp_salary ORDER BY dept_id, salary DESC;这里OVER ()不带任何参数表示整个结果集是一个窗口这就是全体占比。很多人误以为必须要写 PARTITION BY其实不写就是全局窗口。3.5 累计求和与移动平均帧的真实作用累计求和可能是最早让人尝到窗口函数甜头的一类需求SELECT user_id, order_date, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM order_detail ORDER BY user_id, order_date;这里我把帧显式写全了。虽然不写帧默认就是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW但RANGE和ROWS在同一天有多笔订单时行为不同RANGE 会把排序值相同的行一起算进来也就是同一天的所有订单会被视为一行累计值会跳ROWS才是严格按行累加。做对账、算余额的场景必须用 ROWS用默认的 RANGE 会得出错误结果。移动平均是帧最典型的应用算7日移动平均SELECT stat_date, amount, ROUND(AVG(amount) OVER (ORDER BY stat_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS ma7, ROUND(AVG(amount) OVER (ORDER BY stat_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) * 1.0 AS ma7_float FROM daily_amount ORDER BY stat_date;前 6 天因为窗口不满 7 行算出来的平均值是用不足 7 天的数据算的。有些业务要求不满7天不出值这时候用COUNT(*) OVER (...)判断一下行数不够就返回 NULL。这个细节在监控告警类需求里很关键否则刚开始那几天会给出剧烈波动的均值触发误告警。3.6 去重取最新ROW_NUMBER的另一个主战场除了排名ROW_NUMBER 还有一个使用率极高的场景分组取最新一条记录。比如订单表里一个订单号有多条状态变更记录要取每个订单最新那条SELECT order_no, status, update_time FROM ( SELECT order_no, status, update_time, ROW_NUMBER() OVER (PARTITION BY order_no ORDER BY update_time DESC, id DESC) AS rn FROM order_status_log ) t WHERE rn 1;这个写法的价值在于它在语义上比GROUP BY order_no MAX(update_time)加自连接要清楚得多而且不怕时间戳重复。如果时间戳可能重复用 MIN/MAX 方案会一次返回多条记录这个 bug 在并发写入的场景里出现频率不低。加上id DESC兜底永远只返回一条。如果是要物理删掉重复数据MySQL 有个额外的限制需要知道窗口函数不能直接写在 UPDATE 或 DELETE 的 SET/WHERE 里。可行方案是先建临时表存好待删主键再按主键删除CREATE TEMPORARY TABLE tmp_dup_ids AS SELECT id FROM ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY order_no ORDER BY update_time DESC, id DESC) AS rn FROM order_status_log ) t WHERE rn 1; DELETE FROM order_status_log WHERE id IN (SELECT id FROM tmp_dup_ids);提示这个写法在千万级表上要分批执行一次删几十万行的做法会把 undo log 撑爆还可能拖慢主从同步。我一般按 5000 一批循环删批间隙加个几百毫秒对线上影响几乎察觉不到。4. 执行与性能面试官追问的深水区4.1 窗口帧的三种类型ROWS、RANGE、GROUPS把三种帧类型摊开讲这是区分会用和懂原理的分界线。ROWS以物理行为单位。ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING就是字面意思前一行、当前行、后一行一共三行。RANGE以排序值为单位。RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING表示排序值在当前值减 1 到加 1 之间的所有行。注意这里的 1 是数值偏移量所以ORDER BY 的表达式必须是单个数值或日期类型多列排序时不能用带偏移量的 RANGE。GROUPS以并列组为单位。GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW表示当前并列组加上前一个并列组。当排序值有大量重复时GROUPS 比 ROWS 更符合按值看的直觉。三者放在一起跑一遍最直观SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS rows_sum, SUM(amount) OVER (ORDER BY order_date RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS range_sum, SUM(amount) OVER (ORDER BY order_date GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS groups_sum FROM order_detail;如果某天有三笔订单ROWS 只取上下各一行RANGE 会把日期在这个区间内的订单全算进来GROUPS 则会包含并列组。同一份数据三种结果选哪个完全取决于业务语义。还有两个限制值得背下来面试问到了能加分带偏移量的 RANGE 帧只能有一个 ORDER BY 表达式而且要能转换成数值。写RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW这种日期偏移也是支持的但同样只能有一个排序表达式。帧里不能出现窗口函数或聚合函数ROWS BETWEEN SUM(x) PRECEDING AND CURRENT ROW直接报错。4.2 窗口函数在SQL执行顺序中的位置这个知识点解释了第 1 章那个为什么不能写在 WHERE 里的问题。MySQL 8.0 的逻辑执行顺序大致是FROM → JOIN → WHERE → GROUP BY → HAVING → WINDOW → SELECT → DISTINCT → ORDER BY → LIMIT关键点是WINDOW 阶段排在 WHERE、GROUP BY、HAVING 之后SELECT 之前。由此可以推出几条硬性限制第一窗口函数不能出现在 WHERE、GROUP BY、HAVING、JOIN ON 里。这些阶段执行时窗口函数的结果还不存在引擎根本没有东西可以比较。所以才必须套一层子查询把窗口结果落成普通列外层 WHERE 才能过滤。第二窗口函数作用在 GROUP BY 之后的结果集上这就是为什么SUM(SUM(salary)) OVER ()是合法的——内层的聚合已经在上一个阶段算完了。第三外层 ORDER BY 和内层 OVER() 里的 ORDER BY 是两码事。前者决定最终结果的展示顺序后者决定窗口内的计算顺序。两者可以完全不同也可以一致。只写 OVER() 里的 ORDER BY、不写外层 ORDER BY最终结果的行顺序是不保证的测试时必须加外层排序否则两次跑出来的结果顺序可能不一样。实操心得调试窗口函数时我习惯先单独跑内层子查询看每一行的 rn、累计值对不对确认无误再套外层过滤。一步到位写完直接跑大查询一旦结果不对就得从头二分排查效率差好几倍。4.3 索引能不能帮上忙EXPLAIN看什么窗口函数的性能瓶颈通常有两个排序和结果集物化。索引能不能帮上忙取决于你的索引列顺序和 PARTITION BY ORDER BY 的列顺序是否匹配。还是拿emp_salary举例索引是(dept_id, salary)EXPLAIN FORMATTREE SELECT dept_id, emp_name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM emp_salary;EXPLAIN FORMATTREE是看窗口函数执行计划最直观的方式8.0 支持输出里会看到排序和窗口处理相关的节点。对比一下把 ORDER BY 改成ORDER BY hire_date DESChire_date 不在索引里你会看到多出一个显式的排序步骤成本估算明显上升。几个判断依据如果PARTITION BY的列和ORDER BY的列按顺序构成了某个索引的前缀扫描时天然有序排序可以省掉。如果 ORDER BY 用了索引里没有的列必然多一次 filesort数据量大的时候这一层就是主要耗时。EXPLAIN ANALYZE8.0.18能看到实际执行时间和实际行数比估算的 rows 可靠得多。EXPLAIN ANALYZE会真正执行 SQL别在写库上跑没有 LIMIT 的大查询。另外窗口函数本质上是需要把整个结果集物化后再处理的MySQL 在这块会用到临时表如果结果集特别大可能会落到磁盘临时表。可以通过SHOW STATUS LIKE Created_tmp_disk_tables观察落盘了就要考虑先减少参与窗口计算的数据量——在子查询里先把无关列和无关行裁掉比任何参数调优都有效。4.4 一份可复现的性能对比我做过一次对比实验100 万行订单数据取每个用户金额最高的前 3 笔订单。硬件是 4 核 8G 的云主机MySQL 8.0.34InnoDBidx_user_amount (user_id, amount)。三种写法各跑三次取中位数写法耗时主要开销相关子查询约 12.6s每行都要回表统计比自己大的行数自连接约 3.8s一次大 join中间结果膨胀窗口函数约 1.2s一次索引有序扫描 窗口计算数量级差异很明显。相关子查询是 O(n²) 的复杂度100 万行基本不可用自连接虽然优化器能做不少事但中间结果会膨胀窗口函数只需要一次有序扫描。不过这里有个前提要讲清楚这个结论成立的条件是索引列顺序匹配。我把索引换成(user_id, order_date)再跑同样的 SQL窗口函数那版涨到了约 4.5s因为 order_date 不是金额ORDER BY amount 没法用索引序多了一次全量排序。也就是说窗口函数不是银弹索引设计跟不上的时候它一样慢。反过来还有一种情况值得注意如果分区数很少、每个分区行数极多窗口函数要一次性物化整个分区内存压力会比较大。我遇到过一个 casePARTITION BY tenant_id只分了 3 个租户每个租户几百万行结果临时文件写到磁盘跑了两分多钟。解决办法是在子查询里先按时间范围过滤把单次参与计算的行数压下来。5. 踩坑记录与高频追问速查5.1 报错与结果不对的速查表下面这些是我和身边同事真实遇到过的问题按现象 → 原因 → 处理整理现象大概率原因处理方式语法错误指向 OVER窗口函数写在了 WHERE/GROUP BY/HAVING套子查询外层过滤LAST_VALUE 返回的是当前行默认帧到当前行为止显式写ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGSUM 算出来是累计不是总和OVER() 里带了 ORDER BY去掉 ORDER BY或显式指定整窗帧同一份数据两次跑结果顺序不同ROW_NUMBER 没有唯一兜底列ORDER BY 里补主键或唯一列环比第一行全是 NULLLAG 没给默认值LAG(x, 1, 0)或外层用 COALESCE连续登录判断把断开的数据也算上日期有重复未去重先 DISTINCT 或先按天聚合UPDATE 里用窗口函数报错窗口函数不允许出现在 UPDATE 中先物化临时表再按主键更新窗口函数结果列在 WHERE 里查不到列别名不能用于同层 WHERE用子查询或 CTE 包一层查询内存暴涨、临时表落盘分区过大结果集物化子查询里先过滤数据缩小参与集合ORDER BY位置报错想把窗口函数排序和结果排序混用分清 OVER() 内和外层两个 ORDER BY关于最后一条再补一句SELECT ROW_NUMBER() OVER (ORDER BY d) FROM t ORDER BY d里的两个 ORDER BY 各管各的前者管编号顺序后者管展示顺序。它们可以不一致比如按金额编号但按日期展示这时候是合法的。注意MySQL 8.0 里sql_mode开了ONLY_FULL_GROUP_BY之后GROUP BY 的列限制比 5.7 严得多混写 GROUP BY 和窗口函数时更容易报错。遇到column not in GROUP BY的提示先把非聚合列补进 GROUP BY或者用ANY_VALUE()包一下。5.2 窗口函数、自连接、子查询怎么选面试里经常被追问不用窗口函数能不能做这是在考察你对替代方案的掌握。以组内 TopN 为例5.7 时代的自连接写法SELECT a.dept_id, a.emp_name, a.salary FROM emp_salary a LEFT JOIN emp_salary b ON a.dept_id b.dept_id AND b.salary a.salary GROUP BY a.dept_id, a.emp_name, a.salary HAVING COUNT(b.id) 2;逻辑是对每一行数一数同部门里比它薪资高的行有几条小于2就说明它在前两名。这个写法的可读性和性能都不如窗口函数但在老版本上是标准解法面试提到能显示你了解历史演进。三种方案的选择我的经验是这样组内排名、累计、环比、跨度取值优先窗口函数一次扫描可读性最好。结果需要降维聚合用 GROUP BY别硬套窗口。老版本环境5.7 及以下小数据量用相关子查询大数据量用自连接但要严格控制中间结果规模。需要去重取最新8.0 用 ROW_NUMBER8.0 以下用ORDER BY update_time DESC LIMIT 1配合临时表或者用主键反查。还有一个常被忽略的对比窗口函数和 DISTINCT 一起用。SELECT DISTINCT和窗口函数混在一层里结果往往不是你想的那样因为去重可能发生在窗口计算之后也可能因为窗口列的差异导致本来该合并的行没合并。稳妥做法是先在子查询里 DISTINCT外层再算窗口。5.3 面试官顺带追问的那些点窗口函数问到后面面试官常常会顺着往旁边的知识点延伸这几个出现频率最高。第一个是执行计划。会问你怎么确认这条窗口函数 SQL 走得好不好。答法就是前面说的EXPLAIN FORMATTREE看有没有额外排序EXPLAIN ANALYZE看实际耗时和行数配合Created_tmp_disk_tables判断有没有落盘临时表。能说出我主要看排序步骤和临时表就够了比背术语管用。第二个是索引设计。常见问法是这条 SQL 你会怎么建索引。思路是把 PARTITION BY 的列放前面、ORDER BY 的列放后面匹配窗口的有序需求。如果过滤条件里还有等值列比如 status考虑放最前面做覆盖。不要忘了评估维护成本——每多一个索引写入就多一份开销。第三个是 MVCC。窗口函数和 MVCC 没有直接关系但面试官经常顺手问一句隔离级别。简单说InnoDB 靠 undo log 和读视图实现一致性读普通 SELECT 走快照读不加锁而窗口函数只是执行阶段的一环不改变读的性质。要注意的是如果你在窗口函数子查询里加上FOR UPDATE那就是当前读了会加锁这在统计类场景里应该极力避免。第四个是版本兼容。如果面试官说我们线上是 5.7别慌把自连接方案和用户变量方案讲清楚就行。同时可以提一句用户变量在 8.0 里不再是可靠方案因为 SELECT 中赋值表达式的求值顺序没有保证——这一句话往往能把话题引到你熟悉的深水区。第五个是结果一致性。面试官可能会问这个统计结果下班跑和凌晨跑一样吗。这时候要主动提两点一是窗口函数本身是确定性的前提是没有用 ROW_NUMBER 且没有加唯一兜底二是数据本身在变如果统计口径涉及 T1 快照要确保数据源已经定格。这个角度很少有人主动展开提出来会显得有真实的生产经验。最后分享一个我自己养成的习惯。每次写完一段复杂的窗口函数 SQL我都会在下面附一行注释写清楚这段解决什么业务问题、依赖哪个索引、预期行数量级。三个月后回头看那句注释能省掉重新读一遍 SQL 的时间。窗口函数写得越熟越容易把一段逻辑写得很紧凑而紧凑的代码恰恰是最需要注释的——毕竟ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date, id)这一行半年后连自己都要想两秒才知道当初为什么补了个 id 上去。