第3章 高频技术面试题：SQL与数据查询优化
本章学习目标：掌握窗口函数底层机制、慢SQL排查方法论、用户行为分析SQL建模能力。本章覆盖三类核心面试题——窗口函数对比、查询性能优化、留存分析SQL，并配套回答框架与追问路径图。
| 函数 | 编号特点 | 典型场景 | 关键差异 |
|---|---|---|---|
| ROW_NUMBER() | 连续编号，无重复 | 分页查询、去重标记 | 同值不同号，严格递增 |
| RANK() | 同值同排名，跳号 | 并列排名、竞赛榜单 | 产生序号断层 |
| DENSE_RANK() | 同值同排名，不跳号 | 密集排名、TopN截取 | 排名连续无间隙 |
| NTILE(n) | 平均分桶编号 | 数据分片、等频划分 | 指定桶数均匀分组 |
| LAG/LEAD | 前后行偏移访问 | 环比计算、差值分析 | offset控制偏移行数 |
📌 卡片1 · 窗口函数对比：ROW_NUMBER / RANK / DENSE_RANK
面试问题：请解释 ROW_NUMBER()、RANK() 和 DENSE_RANK() 的区别，并举例说明各自的使用场景。
🎯 面试官考察点：
- 是否理解窗口函数底层排序+编号机制
- 能否快速区分三者在“同值处理”上的核心差异
- 是否具备将函数匹配到真实业务场景的能力
📐 回答框架：
- 定义区别 —— 三者都是排序编号函数，但处理同值的方式不同
- 对比关键差异 —— ROW_NUMBER 连续不重复，RANK 同值同排名后跳跃，DENSE_RANK 同值同排名不跳跃
- 场景举例 —— TopN用 DENSE_RANK，分页用 ROW_NUMBER，并列排名用 RANK
💬 示例回答：
三者都用于排名编号。ROW_NUMBER() 顺序递增不重复；RANK() 同值同排名但跳号产生断层；DENSE_RANK() 同值同排名且不跳号。取 TopN 用 DENSE_RANK 保证排名连续，分页查询用 ROW_NUMBER 精确控制行数，竞赛榜单用 RANK 保留并列跳跃语义。
🔍 可能追问：
- 窗口函数与 GROUP BY 的执行顺序有什么区别？
- 如果同时使用 PARTITION BY 和 ORDER BY，窗口函数的计算逻辑如何？
- ROW_NUMBER 去重时要注意什么陷阱？
⚠️ 避坑提醒：不要混淆 RANK 与 DENSE_RANK 的跳跃逻辑——RANK 跳过后续序号（如 1,1,3），DENSE_RANK 不跳（如 1,1,2）。ROW_NUMBER 不可用于并列排名场景，否则会丢失同值语义。
📌 卡片2 · SQL查询优化：慢SQL排查与性能调优
面试问题：一条慢SQL你怎么排查和优化？请结合实际案例说明。
🎯 面试官考察点：
- 是否具备系统化的性能分析思维（定位→分析→优化→验证）
- 是否熟练使用 EXPLAIN、慢查询日志等工具
- 优化方案是否具体可落地，而非空谈“加索引”
📐 回答框架：
- 定位问题 —— 通过慢查询日志或监控工具找到目标SQL
- 分析瓶颈 —— 使用 EXPLAIN 查看扫描行数、type类型、索引使用情况
- 实施优化 —— 索引优化 / SQL改写 / 参数调优 / 数据倾斜处理
- 验证效果 —— 对比执行时间、扫描行数、资源消耗
💬 示例回答：
先用慢查询日志定位SQL，EXPLAIN 发现全表扫描且未命中索引。添加联合索引后查询从3.2s降至0.05s。同时使用覆盖索引避免回表，大表分页改用游标替代 OFFSET，减少扫描行数。最后通过监控确认优化效果并配置告警阈值。
🔍 可能追问：
- 什么情况下索引会失效？（隐式类型转换、不等值条件、Like前缀模糊等）
- 如何判断是否需要重建索引？查看索引碎片率与实际选择性
- EXPLAIN 中的 type 字段从好到差分别是哪些？
⚠️ 避坑提醒：不要只说“加索引”，要说明为什么加这个索引以及如何验证效果。一定要结合 EXPLAIN 输出分析，展示