03.2 阶段 1:SQL 进阶与数据建模
所属:全栈开发面试题库 / 学习路径 / 03 MySQL 与 PostgreSQL
本页目录
一、阶段定位
阶段 0 解决了"能连、能建表、能写单表 SELECT"。本阶段解决两个更硬的问题:写出语义正确的多表查询,以及为业务设计一张结构合理的表。前者是每天 CRUD 的基本功,后者是面试里"给你一个业务场景设计表"的核心。
判断自己是否卡在本阶段:
- 写多表查询时搞不清 INNER JOIN 与 LEFT JOIN 的结果集差异,把"查每个用户的订单数"写成了 INNER JOIN 导致没下单的用户消失——这是 JOIN 语义没吃透。
GROUP BY之后 SELECT 里出现了非分组列,MySQL 5.7 之前能跑、5.7 之后报ONLY_FULL_GROUP_BY错误——这是聚合语义不清。- 遇到"按用户统计订单金额排行"直接想到程序里循环累加,不会用窗口函数——这是 SQL 表达能力不够。
- 设计表时把所有字段塞进一张大表,或者反过来把所有可能的列都拆成关联表,问"这样好不好"答不出取舍——这是范式与反范式的判断没建立。
衔接关系:本阶段产出的电商五表模型(用户、商品、订单、订单项、评论)会被 阶段 2 索引与执行计划 直接灌数据验证索引效果;阶段 3 的事务隔离实验也在这套表上做。不掌握 JOIN 语义与窗口函数,阶段 2 看执行计划时会连"这条 SQL 在干什么"都讲不清。
二、阶段目标与投入
| 能力目标(可验证的行为) | 验证方式 |
|---|---|
| 能区分 INNER/LEFT/RIGHT/FULL JOIN 的结果集,并说清 ON 与 WHERE 的区别 | 用 3 行用户、2 行订单的小数据手动推演结果 |
| 能写 GROUP BY + HAVING 对聚合结果过滤,不出现 SELECT 非分组列 | 在 MySQL 5.7+ 严格模式下查询不报错 |
| 能用 CTE(WITH)拆解复杂查询,能写递归 CTE 查层级(如菜单树) | 写出一个 3 层菜单的递归展开 |
| 能用 ROW_NUMBER/RANK/SUM OVER 写"每组 top N"与累计金额 | 每个用户按时间累计订单金额 |
| 能按 1NF/2NF/3NF 审查一张表并指出问题 | 对一张反范式大表列出冗余与更新异常 |
| 能在自增主键、UUID、雪花 ID 之间给出选型理由 | 对订单表、会话表、分布式订单 ID 分别选型 |
建议学时:
- 全职:1.5–2 周,约 50–60 小时。JOIN 与聚合 12 小时、子查询与 CTE 8 小时、窗口函数 10 小时、范式与建模 12 小时、主键与类型 8 小时、复盘 10 小时。
- 在职(每天 2 小时):4 周,约 60 小时。一个模块一周,不要一次读完概念再动手。
学时分配:JOIN 语义 20%、聚合与子查询 15%、CTE 与窗口函数 25%、范式与反范式 20%、主键与字段选择 15%、冷热分离与字符集 5%。窗口函数是近五年面试高频,时间要给够。
三、先修自检
| 先修项 | 合格标准 | 不合格时回到 |
|---|---|---|
| 单表 CRUD | 能写 SELECT/WHERE/ORDER BY/LIMIT | 03.1 阶段 0 |
| 建表与约束 | 能建带主键、外键、NOT NULL 的表 | 03.1 阶段 0 |
| 数据类型 | 能为金额、时间、枚举选对类型 | 03.1 阶段 0 |
| 示例库已建 | shop_user、product、orders、order_item 四张表已建并有测试数据 | 03.1 阶段 0 |
| 关系代数概念 | 知道"笛卡儿积""连接""投影"是什么意思 | 05.1 关系型数据库通用能力 |
经验判断:如果你之前用 ORM(MyBatis/JPA/GORM)写 SQL 但很少手写 JOIN,本阶段要刻意脱离 ORM,在命令行里把每条查询敲一遍——ORM 生成的 SQL 常常把简单问题复杂化。
四、知识地图
| 模块 | 知识点 | 掌握程度 | 对应题库章节 |
|---|---|---|---|
| JOIN 语义 | INNER/LEFT/RIGHT/FULL、ON vs WHERE、多表 JOIN 顺序 | 能讲清原理 | 05.1 关系型数据库通用能力 |
| 聚合 | GROUP BY、HAVING、聚合函数、ONLY_FULL_GROUP_BY | 会用 | 05.1 关系型数据库通用能力 |
| 子查询 | 相关子查询、派生表、IN/EXISTS 改写 | 会用 | 05.1 关系型数据库通用能力 |
| CTE | WITH 非递归、WITH RECURSIVE 层级查询 | 会用 | 05.1 关系型数据库通用能力 |
| 窗口函数 | ROW_NUMBER/RANK/DENSE_RANK、SUM/AVG OVER、PARTITION BY、累计 | 能讲清原理 | 05.1 关系型数据库通用能力 |
| 范式 | 1NF/2NF/3NF、BCNF、更新异常 | 能讲清原理 | 05.1 关系型数据库通用能力 |
| 反范式 | 冗余字段、冗余的一致性维护 | 会用 | 05.1 关系型数据库通用能力 |
| 主键选择 | 自增、UUID、雪花 ID、序列 | 能讲清原理 | 05.2 MySQL |
| 字段设计 | 枚举、状态、大字段、冷热分离 | 会用 | 05.1 关系型数据库通用能力 |
进度管理建议:窗口函数与范式是面试重灾区,没练熟不要进阶段 2。全量知识点见 03.11 知识点全清单,术语见 03.12 术语表。
五、分模块精讲
5.1 JOIN 语义与 ON/WHERE 区别
为什么需要它:JOIN 写错的代价不是"慢",而是"结果集错了还以为对"。没下单的用户在 LEFT JOIN 里应该出现(订单列为 NULL),在 INNER JOIN 里消失——这一个差异决定了报表的分母。
核心机制:
- INNER JOIN:只保留两表都匹配的行。
- LEFT JOIN:左表全保留,右表不匹配处补 NULL。
- RIGHT JOIN:右表全保留(实际很少用,调换表顺序即可)。
- FULL JOIN:两表全保留(MySQL 不直接支持,用 LEFT UNION RIGHT 模拟;PG 支持)。
- ON 是连接条件,WHERE 是连接后再过滤。LEFT JOIN 里把右表条件写在 WHERE 会把 LEFT 退化成 INNER——因为右表 NULL 行被过滤掉了。
最小可运行示例:
-- 数据:shop_user 有 3 人,orders 只有 2 人有订单
-- 查每个用户的订单数(没下单的也要出现,订单数为 0)
SELECT u.id, u.name, COUNT(o.id) AS order_cnt
FROM shop_user u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;
-- 结果:3 行,没下单的用户 order_cnt = 0
-- 错误写法:把右表条件放 WHERE
SELECT u.id, u.name, COUNT(o.id) AS order_cnt
FROM shop_user u
LEFT JOIN orders o ON o.user_id = u_id
WHERE o.status = 'PAID' -- 这里右表 NULL 行被过滤,退化成 INNER
GROUP BY u.id, u.name;
-- 结果:只剩有已支付订单的用户(2 行)
-- 正确:右表条件放 ON
SELECT u.id, u.name, COUNT(o.id) AS order_cnt
FROM shop_user u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'PAID'
GROUP BY u.id, u.name;
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 报表总数对不上,少了没下单的用户 | LEFT JOIN 条件误写在 WHERE | 右表过滤条件移到 ON |
| 多表 JOIN 结果行数暴涨 | 一对多关系没去重,产生笛卡儿积 | 先在子查询里聚合再 JOIN |
Unknown column 'o.user_id' in 'on clause' |
表别名写错 | 核对别名 |
| MySQL 不支持 FULL JOIN | 语法限制 | 用 LEFT JOIN ... UNION RIGHT JOIN |
面试考法:"INNER JOIN 与 LEFT JOIN 区别""为什么 LEFT JOIN 的右表条件要写在 ON 而不是 WHERE"。要点答到结果集差异与 NULL 行被 WHERE 过滤的机制。追问常给一段 SQL 让你预测行数。
5.2 聚合、GROUP BY 与 HAVING
为什么需要它:报表、统计、排行榜都靠聚合。MySQL 5.7 起默认开启 ONLY_FULL_GROUP_BY,SELECT 里出现非分组列直接报错,这是好事,逼你写对。
核心机制:
- 逻辑执行顺序:FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT。
- WHERE 在分组前行过滤,不能用聚合函数;HAVING 在分组后对组过滤,可以用
COUNT(*) > 5。 - SELECT 里的非聚合列必须出现在 GROUP BY 中。
COUNT(*)统计行数,COUNT(列)统计该列非 NULL 行数;SUM/AVG忽略 NULL。
最小可运行示例:
-- 统计每个用户的订单数与总金额,只看订单数 >=3 的用户
SELECT u.id, u.name,
COUNT(o.id) AS order_cnt,
COALESCE(SUM(o.amount), 0) AS total_amount
FROM shop_user u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name
HAVING COUNT(o.id) >= 3
ORDER BY total_amount DESC;
-- 错误:SELECT 了 u.age 但没 GROUP BY
SELECT u.id, u.name, u.age, COUNT(o.id)
FROM shop_user u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;
-- MySQL 报错:ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 1055 报错 | SELECT 列没在 GROUP BY | 把该列加进 GROUP BY,或用聚合函数 MAX() |
COUNT(o.id) 把没下单用户算成 1 |
用了 COUNT(*) | 用 COUNT(右表连接列) |
SUM(amount) 得到 NULL 而不是 0 |
该组无行 | 用 COALESCE(SUM(amount), 0) |
| 分页排序不稳定 | ORDER BY 有并列值 | 加第二排序键(如 id)打破并列 |
面试考法:"WHERE 与 HAVING 区别""COUNT(*) 与 COUNT(列) 区别"。要点:WHERE 行过滤、不能用聚合;HAVING 组过滤、能用聚合;COUNT(列) 忽略 NULL。
5.3 子查询、派生表与 EXISTS 改写
为什么需要它:复杂业务查询常嵌套 3 层以上。子查询可读性差、优化器有时选择不佳,理解"IN vs EXISTS vs JOIN"的改写关系是 SQL 优化的基础。
核心机制:
- 非相关子查询:子查询不依赖外层,执行一次。
- 相关子查询:子查询引用外层列,对外层每行执行一次。
IN适合外表小、内表大;EXISTS适合外表大、内表小(找到匹配即停)。- 派生表(FROM 子句里的子查询)在 MySQL 5.7 前可能物化,8.0 下推优化更好。
最小可运行示例:
-- 查有订单的用户(IN 写法)
SELECT * FROM shop_user
WHERE id IN (SELECT user_id FROM orders WHERE status = 'PAID');
-- EXISTS 写法(等价,且对 orders 有索引时更快)
SELECT * FROM shop_user u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.status = 'PAID'
);
-- 派生表:先聚合再 JOIN
SELECT u.name, s.order_cnt
FROM shop_user u
JOIN (
SELECT user_id, COUNT(*) AS order_cnt
FROM orders
GROUP BY user_id
) s ON s.user_id = u.id;
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 子查询慢 10 倍 | 相关子查询对外层每行执行 | 改 JOIN 或派生表 |
IN (SELECT ...) 含 NULL 导致查不出 |
IN 集合里有 NULL | 子查询里加 WHERE col IS NOT NULL |
| 派生表不能用外层列 | MySQL 限制 | 改用 LATERAL(PG)或 JOIN |
面试考法:"IN 与 EXISTS 怎么选"。要点:外表小用 IN,内表小有索引用 EXISTS;优化器在新版本会自动改写,但其语义与成本模型要懂。
5.4 CTE:WITH 与递归
为什么需要它:多层子查询嵌套的 SQL 难读难维护。CTE(Common Table Expression)把查询拆成命名的步骤,是 8.0/PG 都支持的现代写法。递归 CTE 还能查菜单树、组织架构这类层级数据。
核心机制:
- 非递归 CTE:
WITH x AS (...) SELECT ... FROM x,把复杂子查询命名。 - 递归 CTE:
WITH RECURSIVE(MySQL)或WITH RECURSIVE(PG),由"锚点成员 + 递归成员"两部分组成,直到不再产生新行。 - MySQL 8.0 与 PG 都支持;MySQL 5.7 不支持。
最小可运行示例:
-- 非递归:把"已支付订单聚合"命名,再 JOIN 用户
WITH paid_order AS (
SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders
WHERE status = 'PAID'
GROUP BY user_id
)
SELECT u.name, p.cnt, p.total
FROM shop_user u
JOIN paid_order p ON p.user_id = u.id
ORDER BY p.total DESC;
-- 递归 CTE:菜单树,menu 表有 id, parent_id, name
WITH RECURSIVE menu_tree AS (
SELECT id, parent_id, name, 1 AS level
FROM menu WHERE parent_id IS NULL -- 锚点:根节点
UNION ALL
SELECT m.id, m.parent_id, m.name, mt.level + 1
FROM menu m JOIN menu_tree mt ON m.parent_id = mt.id -- 递归成员
)
SELECT * FROM menu_tree ORDER BY level, id;
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| MySQL 5.7 报语法错误 | 5.7 不支持 WITH | 升级 8.0,或改写为派生表 |
| 递归无限循环 | 数据有环(parent_id 指向后代) | 加路径判断,或 LIMIT 防止 |
| CTE 被执行多次 | 优化器未物化 | PG 默认物化;MySQL 8.0 可能内联,加 MATERIALIZED |
面试考法:"CTE 与子查询区别""递归 CTE 怎么写层级"。要点:CTE 命名、可引用多次、可读性好;递归 = 锚点 + UNION ALL + 自连接。
5.5 窗口函数
为什么需要它:"每个部门工资第二高""每个用户最近三笔订单""累计销售额"这类需求,用 GROUP BY 要聚合后回连,用相关子查询性能差。窗口函数一套 OVER 搞定,是近五年 SQL 面试的绝对高频。
核心机制:
- 语法:
函数() OVER (PARTITION BY 分区列 ORDER BY 排序列 [窗口帧])。 - 排序类:
ROW_NUMBER()(行号,并列不重复)、RANK()(并列同名次,跳号)、DENSE_RANK()(并列同名次,不跳号)。 - 聚合类:
SUM() OVER、AVG() OVER,不折行,每行保留明细。 - 偏移类:
LAG(col, n)、LEAD(col, n)取上/下行。 - 窗口帧默认
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,即累计到当前行。
最小可运行示例:
-- 每个用户按时间累计订单金额
SELECT user_id, id, amount, created_at,
SUM(amount) OVER (
PARTITION BY user_id
ORDER BY created_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders
ORDER BY user_id, created_at;
-- 每个用户金额 top 3 订单
SELECT * FROM (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
FROM orders o
) t WHERE rn <= 3;
-- LAG:与上一笔订单的时间间隔
SELECT user_id, id, created_at,
LAG(created_at, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_time
FROM orders;
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
MySQL 5.7 报 You have an error in your SQL syntax near 'OVER' |
5.7 不支持窗口函数 | 升级 8.0 |
| ROW_NUMBER 与 RANK 结果混淆 | 并列处理不同 | 按业务选:要唯一行号用 ROW_NUMBER,要并列同名次用 RANK/DENSE_RANK |
| 累计金额不是预期的从 0 到当前 | 窗口帧用了 ROW 还是 RANGE | 明确写 ROWS BETWEEN ... AND CURRENT ROW |
| 窗口函数不能写在 WHERE 里 | 逻辑执行顺序 WHERE 在 OVER 前 | 包一层子查询/CTE 再过滤 |
面试考法:"分组取每组前 N 名怎么写""ROW_NUMBER/RANK/DENSE_RANK 区别""累计统计怎么做"。这是必考,要能当场手写。
5.6 范式与反范式
为什么需要它:表结构设计的核心矛盾是"数据冗余 vs 查询性能"。范式太高冗余少但 JOIN 多,范式太低查询快但更新异常。本模块建立判断能力。
核心机制:
- 1NF:列原子性,不可再分。地址拆成省/市/区,而不是一列存"xx省xx市xx区"。
- 2NF:在 1NF 基础上,非主键列完全依赖主键(消除部分依赖)。复合主键下,非主列不能只依赖主键的一部分。
- 3NF:在 2NF 基础上,非主列不传递依赖主键。"用户所属省份"依赖"城市",而"城市"依赖"用户",应拆出城市表。
- 反范式:为了查询性能,有意冗余字段。典型场景:订单表冗余 user_name(用户改名不影响历史订单)、商品表冗余类目名称。
- 反范式代价:冗余字段的一致性维护(更新用户名时要同步订单冗余字段),通常用"订单快照"模式——下单时把名称快照进订单,之后不再同步。
最小可运行示例:
-- 3NF 设计:用户、城市分开
CREATE TABLE city (
id INT PRIMARY KEY,
name VARCHAR(64) NOT NULL
);
CREATE TABLE shop_user (
id BIGINT PRIMARY KEY,
name VARCHAR(64) NOT NULL,
city_id INT NOT NULL,
FOREIGN KEY (city_id) REFERENCES city(id)
);
-- 反范式:订单冗余用户快照名(下单时写入,之后不改)
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
user_name VARCHAR(64) NOT NULL, -- 快照,下单时写入
amount DECIMAL(12,2) NOT NULL,
created_at DATETIME NOT NULL
);
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 用户改名后历史订单显示新名 | 没做快照,冗余字段实时同步 | 订单表存 user_name 快照,下单时写入后不再更新 |
| 一张表 50 列,改一个字段要锁全表 | 过度反范式 | 拆冷热表(见 5.8) |
| 改城市名要刷几十万行订单 | 订单冗余了 city_name | 只冗余必要字段,城市名通过 JOIN 取 |
| 严格按 3NF 写,报表要 JOIN 8 张表 | 过度范式 | 读多写少场景适度冗余 |
面试考法:"三范式是什么""反范式什么时候用""订单表要不要存用户名"。要点:范式防更新异常,反范式换查询性能;订单快照是经典反范式模式。
5.7 主键选择:自增、UUID、雪花 ID
为什么需要它:主键选错,后期迁移代价极大。自增主键简单但在分布式环境下会冲突;UUID 分布式友好但导致 InnoDB 页分裂与索引膨胀;雪花 ID 是折中。
核心机制:
- 自增(
AUTO_INCREMENT/BIGSERIAL):单调递增,InnoDB 聚簇索引插入顺序写,页分裂少;但分布式多主写入会冲突。 - UUID:36 字符字符串,无序,InnoDB 二级索引体积大,插入随机导致页分裂与缓冲池抖动。
- 雪花 ID(Snowflake):64 位整数(时间戳 + 机器 ID + 序列号),大致趋势递增,分布式不冲突;但依赖时钟,时钟回拨会重复。
- 业务订单号不要用自增暴露量级(被人看出来今天有多少单),要用雪花或号段。
最小可运行示例:
-- 自增(单机)
CREATE TABLE t_auto (id BIGINT AUTO_INCREMENT PRIMARY KEY, ...);
-- UUID(PG 用 uuid-ossp 扩展)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE t_uuid (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), ...);
-- 雪花 ID 由应用层生成,数据库只存 BIGINT
CREATE TABLE t_snow (id BIGINT PRIMARY KEY, ...);
-- 应用层用雪花算法生成 19 位 long,如 1890123456789012345
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| UUID 主键的表插入慢、索引大 | 无序,页分裂频繁 | 用 UUIDv7(时间有序),或改雪花 |
| 自增主键在主从切换后重复 | 多主写入 | 用雪花/号段,或设置 auto_increment 步长奇偶 |
| 雪花 ID 出现重复 | 时钟回拨 | 服务端检测回拨并拒绝,或用号段模式 |
| 订单号用自增被竞品估算单量 | 自增暴露业务量级 | 用雪花或中间加密 |
面试考法:"自增主键缺点""UUID 为什么不适合 InnoDB 主键""雪花 ID 结构"。要点:自增聚簇写入好但分布式冲突;UUID 无序致页分裂;雪花 = 时间戳+机器+序列,趋势递增。
5.8 大字段、冷热分离与枚举设计
为什么需要它:把商品详情(长文本)、评论、图片列表塞进主表,会让所有主表查询都背着这几 KB 无用数据。冷热分离把频繁访问的字段留在主表,大字段拆到扩展表。
核心机制:
- MySQL InnoDB 按 16KB 页存储,一行过大(如 TEXT/BLOB)会跨页,导致每行读取的 IO 放大。
- 冷字段拆到
product_ext(1:1),主表只存热点字段。 - 枚举:状态字段用
TINYINT(1 上架、0 下架)+ 注释,或 PG 的ENUM类型;不要用无注释的字符串。 - 逻辑删除:用
is_deleted TINYINT(1)而非物理 DELETE,配合唯一索引(如用户手机号)要加is_deleted联合唯一。
最小可运行示例:
-- 主表:热点字段
CREATE TABLE product (
id BIGINT PRIMARY KEY,
name VARCHAR(128) NOT NULL,
price DECIMAL(12,2) NOT NULL,
status TINYINT NOT NULL DEFAULT 1 -- 1 上架 0 下架
);
-- 扩展表:冷字段,1:1
CREATE TABLE product_ext (
product_id BIGINT PRIMARY KEY,
detail TEXT,
images_json JSON,
FOREIGN KEY (product_id) REFERENCES product(id)
);
-- 唯一索引带逻辑删除:已删除的手机号不参与唯一
CREATE UNIQUE INDEX uk_phone_active ON shop_user (phone, is_deleted);
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 列表查询慢,明明只查 name/price | TEXT 字段在同一行,IO 放大 | 拆 ext 表,列表 SELECT 不 JOIN ext |
| 逻辑删除后手机号重复注册 | 唯一索引没考虑 is_deleted | 联合唯一 (phone, is_deleted) |
| 状态字段加新值要改表 | 用了 ENUM 类型 | 用 TINYINT + 字典表 |
面试考法:"大字段为什么要拆""逻辑删除怎么做唯一约束"。要点:行外存储放大 IO;联合唯一把 is_deleted 纳入。
六、分层练习
6.1 复现型
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| JOIN 结果集推演 | 用 3 用户 2 订单数据,手动写 INNER/LEFT 结果并跑 SQL 对照 | 3 行 vs 2 行的差异能解释 | 1 小时 |
| 聚合严格模式 | 在 ONLY_FULL_GROUP_BY 下写出正确 GROUP BY |
故意制造一个 1055 报错并修掉 | 1 小时 |
| 递归菜单 | 建 3 层菜单表,写 WITH RECURSIVE 展开 | 输出带 level 的树 | 1.5 小时 |
| 窗口函数 top N | 每个用户金额 top 3 订单 | 用 ROW_NUMBER 实现,正确 | 1.5 小时 |
| 累计金额 | 按用户+时间累计订单金额 | 用 SUM OVER 窗口帧 | 1 小时 |
6.2 改造型
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 修 LEFT JOIN 退化 | 给定一条把右表条件写 WHERE 的 SQL,改为正确写法 | 没下单用户出现,count=0 | 0.5 小时 |
| 子查询改 JOIN | 把一个相关子查询改成派生表 JOIN | EXPLAIN 扫描行数下降 | 1.5 小时 |
| 冗余快照改造 | 给 orders 加 user_name 字段,写迁移脚本 | 历史数据回填,新订单自动写入 | 1.5 小时 |
| 大字段拆分 | 把 product 表的 detail/images 拆到 product_ext | 列表查询不再 SELECT TEXT | 2 小时 |
6.3 设计型
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 电商五表建模 | 用户、商品、订单、订单项、评论,写出完整 DDL | 主键选型有说明,外键关系画清 | 4 小时 |
| 报表 SQL | "每月 GMV 与环比""每个类目 top 10 商品"用窗口函数写 | 不依赖程序循环 | 3 小时 |
| 菜单树方案 | 对菜单/组织架构,对比邻接表与路径枚举两种建模 | 能说出递归 CTE 与路径查询的代价 | 2 小时 |
七、排障表
| 现象 | 可能原因 | 排查步骤 | 解决办法 |
|---|---|---|---|
| 报表少了没下单的用户 | LEFT JOIN 条件误写 WHERE | 把 SQL 贴出来看 ON/WHERE 位置 | 右表条件移到 ON |
ERROR 1055 (42000) |
SELECT 非分组列 | 看错误列号 | 加进 GROUP BY 或用聚合 |
| 多表 JOIN 结果行数翻倍 | 一对多未聚合,笛卡儿积 | 先单独跑各表 COUNT | 先聚合再 JOIN |
| 窗口函数报语法错 | MySQL < 8.0 | SELECT VERSION(); |
升级 8.0 |
| 递归 CTE 不返回 | 锚点条件没匹配 | 单独跑锚点 SELECT | 修正 WHERE |
| UUID 主键插入慢 | 页分裂 | SHOW ENGINE INNODB STATUS 看插入 |
改雪花或 UUIDv7 |
| 逻辑删除后唯一约束冲突 | 唯一索引没含 is_deleted | SHOW INDEX FROM t; |
重建联合唯一索引 |
SUM 返回 NULL 而非 0 |
无行时聚合为 NULL | 看是否用 LEFT JOIN | COALESCE(SUM(x),0) |
八、出口条件
全部勾选后再进入 阶段 2 索引与执行计划。
- [ ] 能在 3 用户 2 订单的小数据上手动推出 INNER 与 LEFT JOIN 的行数差异
- [ ] 能写出不触发 1055 错误的 GROUP BY,说清 WHERE 与 HAVING 区别
- [ ] 能写 WITH RECURSIVE 展开 3 层菜单
- [ ] 能用 ROW_NUMBER 实现"每组 top N",能区分 ROW_NUMBER/RANK/DENSE_RANK
- [ ] 能对一张表指出 1NF/2NF/3NF 的违反点
- [ ] 能说出自增、UUID、雪花 ID 的取舍,订单表选雪花并说明理由
- [ ] 能把大字段拆到 1:1 扩展表,并说清为什么
自测题:
- LEFT JOIN 里把右表条件写在 WHERE 会发生什么?为什么?
- 要点:右表不匹配行补 NULL,被 WHERE 过滤,退化成 INNER JOIN;右表条件要写 ON。
- WHERE 与 HAVING 都能过滤,区别是什么?
- 要点:WHERE 行过滤、分组前、不能用聚合;HAVING 组过滤、分组后、能用聚合。
- ROW_NUMBER、RANK、DENSE_RANK 在并列时分别怎么处理?
- 要点:ROW_NUMBER 不重复(1,2,3);RANK 并列跳号(1,1,3);DENSE_RANK 并列不跳(1,1,2)。
- 订单表要不要冗余 user_name?怎么维护?
- 要点:要,做快照;下单时写入,之后用户改名不影响历史订单。
- UUID 做主键为什么对 InnoDB 不友好?
- 要点:UUIDv4 无序,插入随机导致页分裂、二级索引大、缓冲池抖动;用 UUIDv7 或雪花。
- 逻辑删除的手机号唯一约束怎么设计?
- 要点:联合唯一索引 (phone, is_deleted);已删除的记录 phone+is_deleted 组合不冲突。
- 深翻页用
LIMIT 100000,20为什么慢?窗口函数能解决吗?- 要点:扫描并丢弃前 10 万行;窗口函数不能直接解决,改游标分页
WHERE id > ?。
- 要点:扫描并丢弃前 10 万行;窗口函数不能直接解决,改游标分页