第3章 · SQL与数据查询优化：常用函数、窗口函数、查询性能调优、高频SQL题
本章聚焦BI建模面试中出现频率最高的SQL考察板块。核心能力包括：窗口函数灵活运用、慢查询排查方法论、排名/聚合类SQL手写能力。面试官通过本章题目判断候选人是否具备生产环境查询优化与复杂分析SQL编写的实战经验。
📌 本章核心知识结构
| 知识模块 | 核心技能 | 面试频次 | 典型考题 |
|---|
| 窗口函数 | OVER / PARTITION BY / 排名函数 | ⭐⭐⭐⭐⭐ | 部门工资排名、移动平均 |
| 查询性能调优 | EXPLAIN / 索引优化 / SQL改写 | ⭐⭐⭐⭐⭐ | 慢SQL排查流程 |
| 常用聚合函数 | GROUP BY / HAVING / 聚合嵌套 | ⭐⭐⭐⭐ | 窗口函数 vs GROUP BY |
| 复杂查询编写 | CTE / 子查询 / JOIN优化 | ⭐⭐⭐⭐ | Top N / 连续登录问题 |
📇 问答卡片 1 · 窗口函数 vs GROUP BY
| 面试问题 | 请说明窗口函数与 GROUP BY 的核心区别，并举例各自适用场景。 |
| 面试官考察点 |
- 是否理解窗口函数「保留行数」的本质特性
- 能否清晰对比两种语法的行数变化与语义差异
- 实际业务中能否正确选型
|
| 回答框架 |
| 维度 | GROUP BY | 窗口函数 |
|---|
| 输出行数 | 每组仅一行，行数减少 | 每行保留，行数不变 |
| 典型场景 | 维度汇总报表 | 排名、同比环比、移动平均 |
| 性能特点 | 通常更快 | 大数据集需注意内存占用 |
|
| 简短示例回答 |
窗口函数与GROUP BY核心区别在于行数变化：GROUP BY按分组聚合，每组输出一行；窗口函数在每行保留原数据同时附加聚合结果。GROUP BY适合维度汇总报表，窗口函数适合排名、同比环比、移动平均等分析场景。 |
| 可能追问 |
- ROW_NUMBER()、RANK()、DENSE_RANK() 三者的区别？
- 窗口函数性能问题如何定位和优化？
- 窗口函数是否可以嵌套？
|
| 避坑提醒 |
不要混淆 PARTITION BY 与 GROUP BY 的分组逻辑；窗口函数在分组后对每行计算，不会减少行数。回答时强调「行数不变」是区分关键。 |
📇 问答卡片 2 · SQL查询性能排查与优化
| 面试问题 | 一条SQL查询很慢，你会如何排查和优化？请描述完整流程。 |
| 面试官考察点 |
- 是否有系统化排查方法论
- 是否熟悉 EXPLAIN、慢查询日志等工具
- 能否区分索引优化、SQL改写、架构优化等不同层次
|
| 回答框架 |
- 定位问题：开启慢查询日志，获取具体慢SQL及执行时间
- 分析计划：使用 EXPLAIN 分析 type、key、rows、Extra
- 识别瓶颈：全表扫描（ALL）、文件排序（filesort）、索引失效
- 制定方案：联合索引覆盖、子查询→JOIN、避免函数索引列
- 验证效果：对比优化前后的执行时间与扫描行数
|
| 简短示例回答 |
首先开启慢查询日志定位慢SQL，用EXPLAIN分析执行计划，关注type是否全表扫描、索引使用情况、rows扫描行数。常见优化手段：建立联合索引覆盖查询字段、将子查询改写为JOIN、避免在索引列使用函数。最后对比执行时间验证优化效果。 |
| 可能追问 |
- 联合索引的最左前缀原则是什么？
- 什么是索引下推（Index Condition Pushdown）？
- 大表 LIMIT 分页优化如何做？
|
| 避坑提醒 |
不要盲目加索引，需评估写性能影响；EXPLAIN 的 rows 是估算值，不精准；避免在索引列上使用函数或隐式类型转换，会导致索引失效。 |
📇 问答卡片 3 · 手写SQL：部门工资排名前3
| 面试问题 | 已知 employee(id, name, salary, dept_id)，写出SQL：查询每个部门工资排名前3的员工信息。 |
| 面试官考察点 |
- 窗口函数熟练度（语法准确）
- 排名函数选择是否合理（ROW_NUMBER vs RANK vs DENSE_RANK）
- 是否考虑并列排名边界情况
|
| 回答框架 |
- 使用窗口函数 DENSE_RANK() 处理并列排名
- PARTITION BY dept_id ORDER BY salary DESC 分组排序
- 外层查询过滤排名 ≤ 3，得到每个部门前三员工
|
| 简短示例回答 |
WITH emp_rank AS (
SELECT *, DENSE_RANK() OVER (
PARTITION BY dept_id ORDER BY salary DESC
) AS r FROM employee
)
SELECT * FROM emp_rank WHERE r <= 3;
使用DENSE_RANK避免并列时排名不连续，PARTITION BY按部门分组排序。 |
| 可能追问 |
- 如果要求并列时取前3条记录（不是排名）怎么做？
- 如何用 变量 实现窗口函数效果？
- 排名第 N 的工资怎么查？
|
| 避坑提醒 |
区分 ROW_NUMBER、RANK、DENSE_RANK：并列取前3人用 RANK 或 DENSE_RANK；只要3条记录用 ROW_NUMBER。答前确认面试官需求「前3人」还是「前3条」。 |
📁 项目案例 · 电商运营数据平台 SQL 查询优化专项
| 项目名称 | 电商运营数据平台 · SQL查询优化专项 |
| 业务背景 | 运营报表系统查询缓慢，部分核心报表耗时 >30s，影响运营团队每日数据复盘效率。 |
| 规模指标 | 数据量级 10亿+ 行；日均查询 5万+；涉及订单、商品、用户等 8 张核心表。 |
| 技术栈 | MySQL 8.0 · ClickHouse · Redis 缓存 · DataX · Kettle |
| 核心难点 |
- 大表 JOIN 性能差（订单表×商品表，关联字段无索引）
- 窗口函数在 MySQL 8.0 大数据集下执行效率不稳定
- 复杂报表 SQL 执行计划难以手工分析
|
← 上一章
📖 返回目录
下一章 →