数据平台开发工程师面试宝典 - 第4节
第4章：SQL优化与数据建模
面试中 SQL 优化和数据建模是高频考点，考察候选人是否具备从执行计划、索引设计到模型选择的系统性思维。以下先通过一张慢查询分析路径图梳理核心链路，再以问答卡片和项目模板帮助巩固回答结构。
面试问题1：一个SQL语句执行很慢，你如何排查和优化？
面试官考察点：是否具备系统化的慢查询分析思路；是否理解EXPLAIN输出字段含义；能否结合索引和业务给出具体方案。
回答框架
- 定位：通过慢查询日志找到问题SQL，并记录频率、耗时。
- 分析：使用EXPLAIN查看type、possible_keys、key、rows、Extra，判断全表扫描、文件排序等。
- 优化：创建覆盖索引、改写条件顺序、避免函数操作、拆分大查询。
- 验证：执行后再次 EXPLAIN 并对比耗时。
简短示例回答
“先开启慢日志捕获具体SQL，用EXPLAIN发现type=ALL且rows=100万，没有使用索引。分析WHERE条件字段，考虑建一个联合索引（user_id, status）并减少回表。优化后type变成ref，耗时从2s降到20ms。”
可能追问
- 如果加了索引但MySQL没用上，可能是什么原因？
- 组合索引最左前缀原则的具体表现？
- 如何用pt-query-digest分析慢日志？
避坑提醒：不要一上来就说“加索引”，先分析执行计划；不要忽略覆盖索引和索引下推；别把优化责任推给DBA。
面试问题2：星型模型和雪花模型有什么区别？如何选择？
面试官考察点：数据建模基础；维度建模理论；理解查询效率与存储冗余的权衡。
回答框架
- 定义：星型模型是事实表直接连接各维度表（不继续拆分）；雪花模型将维度表进一步规范化。
- 对比：星型查询路径短、易理解、冗余多；雪花减少冗余但增加JOIN层数，查询性能可能下降。
- 选择：OLAP查询场景优先用星型；数据一致性和存储成本敏感时考虑雪花。
简短示例回答
“星型模型维度表冗余，但查询只需一次JOIN，适合大宽表和高并发；雪花模型将‘地区’拆为省-市-区，节省存储但需三次JOIN。我一般用星型，除非源数据粒度过细才用雪花。”
可能追问
- 事实表有哪些类型？事务、周期快照、累积快照。
- 缓慢变化维（SCD）如何处理？
- 在ClickHouse中更适合哪种模型？
避坑提醒：不要只说理论无实际场景；记住星型并非总是最佳，大数据量下雪花也有优势；注意区分OLTP与OLAP建模差异。
面试问题3：联合索引（a,b,c）在哪些查询条件下能完全命中？
面试官考察点：对最左前缀原则的理解深度；能否识别范围查询导致的索引失效。
回答框架
- 原则：必须从左边连续匹配，缺失某一列则后面列失效。
- 命中情况：WHERE a=1 and b=2 and c=3（全命中）；a=1 and b>2 and c=3（b范围后c失效）。
- 失效情况：跳过b列（如a=1 and c=3）只能用到a列；b='2'（无a）完全不走索引。
简短示例回答
“联合索引(a,b,c)在全等匹配三个字段时全命中。若b使用范围查询（b>1），则c的索引失效。若只用到a和c但没用到b，则只使用a列。所以一般把等值条件放前面，范围条件放最后。”
可能追问
- 数据库如何选择索引？ICP（索引下推）的作用？
- 如果查询条件是a in (1,2) and b=3 能走索引吗？
- 如何通过调整字段顺序优化现有索引？
避坑提醒：别搞混联合索引顺序与WHERE条件写法的关系；注意IN条件也属于范围，可能引起部分失效；面试时举例具体字段值会更有说服力。
知识结构：星型模型 vs 雪花模型
| 对比维度 | 星型模型 | 雪花模型 |
|---|---|---|
| 存储冗余 | 高（维度表未拆分） | 低（规范化拆分） |
| 查询JOIN次数 | 1次 | 多次（层级JOIN） |
| 查询性能 | 更快（减少JOIN） | 相对较慢 |
| ETL复杂度 | 低 | 较高（需维护多张表） |
| 典型场景 | 大宽表、报表、Ad-hoc | 数据一致性要求高、存储受限 |
项目案例：实时订单分析平台 SQL 优化实战
项目名称
实时订单核心指标看板
业务背景
电商大促期间，运营需实时查看各品类订单金额及转化率，原离线报表延迟30分钟，急需升级为近实时（延迟<5秒）系统。
规模指标
| 日增订单数 | 500万 |
| 事实表行数 | 20亿+（历史） |
| 维度表数量 | 5张（商品、用户、时间、区域、活动） |
| QPS峰值 | 3000 |
技术栈
Kafka → Flink → ClickHouse（MergeTree引擎），前端 Grafana + 自研API。
核心难点
- 大表JOIN性能差，原MySQL查询超10秒。
- 维度数据缓慢变化（活动名称可能修改）。
- 实时写入与查询读写冲突。
面试高频追问
- 如何优化ClickHouse中多个维度JOIN？
- 为什么选择星型模型而不是雪花？
- 慢查询出现时你的排查步骤？
可迁移回答话术
“在这个项目中，我将事实表与维度表设计为星型模型，利用ClickHouse的Global Join和物化视图减少实时计算开销。针对慢查询，我首先用system.query_log定位，发现ORDER BY字段缺少排序键，于是调整MergeTree的order by (event_time, region_id)，查询时间从800ms降到15ms。”
💡 复习建议：将EXPLAIN输出中的type（system>const>eq_ref>ref>range>index>ALL）背熟；星型与雪花的选择结合具体工具（如Doris、ClickHouse）特点；联合索引练习题推荐“SQL优化50题”。