第5讲我们正式开始写复杂查询。我给团队做 MySQL 内训的时候每次讲到这一讲都会先泼一盆冷水如果你觉得复杂查询就是把几张表 join 在一起那后面的内容大概率会刷新你的认知。数据操纵语句是日常开发里使用频率最高的一类 SQLSELECT、INSERT、UPDATE、DELETE 几乎绕不开而复杂查询则是把多表连接、子查询、分组聚合、窗口函数这些能力叠加到基础语句上让一句 SQL 能解决原来要写一大段业务代码才能搞定的事。这一讲的定位是给“会写基础增删改查但没系统梳理过复杂查询”的开发者和学生也适合正在准备 MySQL 面试、想补齐底层执行逻辑的朋友。学完之后你至少能回答三个问题多表 join 到底怎么选内连接和外连接子查询什么时候该用 EXISTS 而不是 IN窗口函数为什么能替代大量手动拼写的排名和累计代码1. 复杂查询的整体思路先记住 MySQL 的逻辑执行顺序1.1 执行顺序决定了你能怎么写 SQL很多人写复杂查询最大的问题不是语法不会而是脑子里的执行模型是错的。MySQL 拿到一条 SELECT 语句并不是按照你书写的顺序去执行的它有一套固定的逻辑处理顺序。这里我直接给出顺序建议你拿个小本子抄下来FROM 确定数据来源处理多表连接 WHERE 对 FROM 阶段的结果做行级过滤 GROUP BY 按指定列分组 HAVING 对分组后的结果做过滤 SELECT 投影需要的列计算表达式 DISTINCT 对结果去重 ORDER BY 排序 LIMIT 分页截断这个顺序是理解复杂查询的钥匙。为什么这么说因为它直接解释了两个高频报错为什么 WHERE 条件里不能用聚合函数因为执行到 WHERE 时GROUP BY 还没发生聚合结果还不存在。为什么 SELECT 里取的别名不能在 WHERE 里用因为 SELECT 的投影在 WHERE 之后才执行。这两个坑我几乎每次培训都能看到有人踩。1.2 复杂查询的本质是“分阶段筛选”把执行顺序刻在脑子里之后复杂查询就没那么神秘了。你可以把它想象成一条流水线先从 FROM 把原料搬上工作台WHERE 是第一次粗筛GROUP BY 是把原料分筐HAVING 是对每个筐做二次检查SELECT 是把你真正要的东西捡出来ORDER BY 和 LIMIT 是最后整理货架。很多新手在复杂查询里迷路的根本原因是试图用“一条 SQL 一次性解决所有逻辑”一旦业务条件复杂就开始堆条件、堆子查询最后自己都看不懂。正确的做法是先拆需求哪部分是行过滤哪部分是分组统计哪部分是连接补充字段。拆清楚了SQL 自然就顺了。2. 多表连接查询JOIN 的底层逻辑与实战取舍2.1 先建一个学生-课程-成绩三表模型复杂查询不能空谈我这一讲全部用同一个业务模型来演示学生、课程、成绩。这也是面试里最常见的表结构设计。先看建表语句CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, class_id INT NOT NULL ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE course ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, credit INT NOT NULL ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE score ( student_id INT NOT NULL, course_id INT NOT NULL, score DECIMAL(5,2) NOT NULL, PRIMARY KEY (student_id, course_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;score 表用联合主键 (student_id, course_id)保证一个学生对一门课只能有一条成绩记录。这里我特意把 score 设计成窄表因为它未来会被高频关联查询字段越精简索引效率越高。2.2 INNER JOIN 与 LEFT JOIN 的选择逻辑先看最常见的内连接查询每个学生的姓名和课程成绩。SELECT s.name, c.name AS course_name, sc.score FROM score sc JOIN student s ON sc.student_id s.id JOIN course c ON sc.course_id c.id;这里我用的是 INNER JOIN 的简写 JOIN。它的语义是“两表都匹配才返回”。也就是说如果一个学生没有成绩记录或者一门课没有学生选它就不会出现在结果里。对于“我要看真正产生了成绩的数据”这种需求内连接就是正确答案。再看一个典型需求查询所有学生包括那些还没选任何课的学生。SELECT s.name, c.name AS course_name, sc.score FROM student s LEFT JOIN score sc ON s.id sc.student_id LEFT JOIN course c ON sc.course_id c.id;注意我把驱动表换成了 student用 LEFT JOIN 保证 student 表的所有行都保留匹配不到成绩的地方自动补 NULL。这是内外连接最核心的差异左边是驱动表右边是被驱动表LEFT JOIN 以左表为准右表没有匹配就填空。日常开发里查询“主表数据 可选附加信息”的场景几乎都是 LEFT JOIN比如订单列表带用户昵称、文章列表带作者信息这些都属于主数据必须全量展示的情况。2.3 ON 和 WHERE 的配合LEFT JOIN 里的大坑LEFT JOIN 有一个特别容易翻车的细节条件放 ON 后面还是 WHERE 后面结果可能完全不同。看这两个写法-- 写法 A附加条件放在 ON 里 SELECT s.name, sc.score FROM student s LEFT JOIN score sc ON s.id sc.student_id AND sc.score 60; -- 写法 B附加条件放在 WHERE 里 SELECT s.name, sc.score FROM student s LEFT JOIN score sc ON s.id sc.student_id WHERE sc.score 60;写法 A 的意图是“左表全保留右表只匹配及格的成绩”没及格的学生照样出现成绩显示为 NULL。写法 B 则先做 LEFT JOIN再用 WHERE 过滤掉成绩为 NULL 和不及格的行效果等同于把 LEFT JOIN 变成了 INNER JOIN。这个区别在报表统计里非常致命很多人写完 SQL 发现数据少了十有八九就是掉进这个坑。2.4 自连接同一个表和自己 Join还有一个容易懵的场景是自连接。典型需求是查询每个学生的班主任是谁而班主任也在 student 表里通过一个 teacher_id 字段指向自己的 id。SELECT stu.name AS student_name, tea.name AS teacher_name FROM student stu LEFT JOIN student tea ON stu.teacher_id tea.id;自连接的关键是必须给表起不同的别名否则 MySQL 根本分不清你引用的是哪一份。别觉得自连接冷门员工-经理、分类-父分类、关注-粉丝这类层级关系表全靠它。2.5 JOIN 时因重复数据导致爆炸JOIN 最常见的一个隐藏风险是一对多关联造成结果行数翻倍。比如 score 表里一个学生有多条课程记录你再 join 一张“学生扩展信息表”如果扩展信息表里也有多条同名记录就会出现笛卡尔式的行数膨胀。遇到这种情况先查一下每张表的粒度再决定 join 的目标表是不是需要在子查询里先做聚合去重。做报表的人对这个问题应该深有体会一个 JOIN 把数据放大了一倍汇总数字全错了最后只能逐层排查。3. 子查询IN、EXISTS 与派生表的正确姿势3.1 子查询的三种位置子查询就是嵌套在另一条 SQL 里的 SELECT按出现位置分成三种WHERE 子查询、FROM 子查询也叫派生表、SELECT 子查询。先看一个 WHERE 子查询的例子SELECT name FROM student WHERE id IN ( SELECT DISTINCT student_id FROM score WHERE score 60 );这个查询找出“有不及格成绩”的学生名单。子查询先执行算出一批不合格的 student_id然后外层 student 表根据这些 id 过滤。逻辑直观是新手最容易上手的写法。3.2 EXISTS 与 IN 怎么选再看 EXISTS 版本SELECT name FROM student s WHERE EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id s.id AND sc.score 60 );EXISTS 是关联子查询它对外层 student 的每一行去 score 表里做一次存在性检查只要找到一条满足条件的记录就返回真。注意里面写的是 SELECT 1不是 SELECT *因为存在性检查根本不关心查出了什么列写成 1 既省内存又明确表达意图。什么时候用 EXISTS 而不是 IN主要有两个判断点。第一IN 子查询的结果集如果很大整个结果集都要在内存里暂存而 EXISTS 是边遍历外层边探测通常 EXISTS 在大数据量下表现更稳。第二IN 遇到 NULL 值会出逻辑问题。如果子查询结果里包含 NULLIN 判断会变成“既不是真也不是假”结果集可能意外为空。EXISTS 没有这个问题。当然现代 MySQL 优化器对 IN 也会做半连接优化性能差距并没有想象中夸张但 EXISTS 的语义更安全我在生产环境里更推荐它。3.3 FROM 子查询先查一张临时表再继续查FROM 子查询的典型场景是“先按明细聚合再和主表关联”。比如想统计每个学生的平均分然后找出平均分低于 70 的学生姓名SELECT s.name, t.avg_score FROM student s JOIN ( SELECT student_id, AVG(score) AS avg_score FROM score GROUP BY student_id ) t ON s.id t.student_id WHERE t.avg_score 70;派生表 t 就相当于一张只存在于这条 SQL 执行过程中的临时表。这里有个非常实用的经验派生表一定要起别名否则 MySQL 会直接报错。另外派生表的字段尽量在子查询里就把类型和名称定好外层引用时才不会糊涂。3.4 关联子查询的逐行执行逻辑关联子查询是很多人理解上的分水岭。像 3.2 里的 EXISTS 写法外层每一行都要去执行一次子查询这叫“相关子查询”。它的威力在于可以引用外层查询的列但代价是如果优化器没有把它改写为高效的半连接性能会随外层行数线性下降。面试里经常问“怎么用一条 SQL 找出每个学生的最高分课程”这个需求用关联子查询可以写SELECT sc1.student_id, sc1.course_id, sc1.score FROM score sc1 WHERE sc1.score ( SELECT MAX(sc2.score) FROM score sc2 WHERE sc2.student_id sc1.student_id );内层子查询对每个学生都求一次最高分外层再找等于这个最高分的记录。理解了这个逐行执行的逻辑后面学窗口函数就会轻松很多。4. 分组聚合GROUP BY 与 HAVING 的分工4.1 GROUP BY 的分组本质GROUP BY 看起来简单但很多人不知道它的执行逻辑其实是“先分组再逐组聚合”。在 MySQL 中一旦使用了 GROUP BYSELECT 的非聚合列必须出现在 GROUP BY 列表里否则结果不可控。比如SELECT student_id, AVG(score) AS avg_score FROM score GROUP BY student_id;这条没问题。但如果你写成SELECT student_id, course_id, AVG(score) AS avg_score FROM score GROUP BY student_id;在 MySQL 的 ONLY_FULL_GROUP_BY 模式下会直接报错因为 course_id 没有被分组也没有被聚合。就算你侥幸关掉了严格模式查出来的 course_id 也是“这一组里的任意一条”毫无业务含义。这不仅是语法问题更是数据正确性问题。4.2 WHERE 和 HAVING 是怎么分工的WHERE 在分组之前过滤行HAVING 在分组之后过滤组。这个区别直接决定你能不能把条件写对。举一个经典需求统计每个学生的选课数量只显示选课超过 3 门的学生。SELECT student_id, COUNT(*) AS course_cnt FROM score GROUP BY student_id HAVING COUNT(*) 3;如果你试图在 WHERE 里写 COUNT(*) 3MySQL 会直接告诉你“无效使用组函数”因为执行到 WHERE 的时候还没有分组。反过来如果条件是“只统计 2024 年秋季的选课记录”那就必须在 WHERE 里先过滤因为这是行级条件提前过滤掉不需要的数据也能减少后续分组的开销。4.3 聚合函数里的坑COUNT 和 SUM 别再踩了聚合函数看着简单坑也不少。COUNT(*) 统计行数COUNT(column) 统计该列非 NULL 值的个数。如果某列大量为 NULL两个 COUNT 结果可能差很远。SUM 也一样SUM(column) 会忽略 NULL但如果整组都是 NULLSUM 返回 NULL 而不是 0报表里经常因此出现空白。处理办法是用 IFNULL 或 COALESCE 包一层比如 SUM(IFNULL(score, 0))。另一个高频坑是 COUNT(DISTINCT ...) 的性能。去重计数在大表上非常昂贵如果只是为了看“是否有重复”不如先用 GROUP BY 加 HAVING COUNT(*) 1 去定位具体重复行再做后续处理。5. 窗口函数排名与累计计算的高效利器5.1 窗口函数和 GROUP BY 的本质区别MySQL 从 8.0 开始支持窗口函数这绝对是复杂查询里性价比最高的一块内容。窗口函数和 GROUP BY 最大的不同是GROUP BY 会把多行合并成一行窗口函数则保留每一行同时在每一行上额外算出一个“窗口范围内的聚合值”。打个比方GROUP BY 是把同一个班级的学生成绩汇总成一条班级平均分窗口函数是每个学生名字旁边都写上“你所在班级的平均分”。一个最直观的例子给每个学生的每门成绩加一列“该学生自己的平均分”SELECT student_id, course_id, score, AVG(score) OVER (PARTITION BY student_id) AS student_avg FROM score;PARTITION BY 是“按学生分区”MySQL 在每个分区内独立计算平均分但结果不折叠行每条原始成绩都保留。这就是窗口函数的核心用法。5.2 ROW_NUMBER、RANK、DENSE_RANK 怎么选排名是窗口函数最出圈的场景。面试必考三道函数SELECT student_id, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_no, RANK() OVER (ORDER BY score DESC) AS rk, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rk FROM score;三者的区别非常经典ROW_NUMBER 不管分数是否相同强行给一个连续排名 1、2、3、4RANK 相同分数排名相同但下一个排名会跳号比如 1、1、3DENSE_RANK 相同分数排名相同且下一个排名不跳号比如 1、1、2。业务上“取前三名”如果用 RANK 可能会取出 4 条因为并列第三占了两个位置如果业务要求“必须最多三个人”就要用 ROW_NUMBER并列时再按学号或时间做次级排序。5.3 累计求和与移动平均窗口函数另一个频繁的应用是累计计算。比如计算截止到每门课的累计成绩SELECT student_id, course_id, score, SUM(score) OVER (PARTITION BY student_id ORDER BY course_id) AS running_total FROM score;ORDER BY 出现在 OVER 子句里时窗口会被定义为“从分区第一行到当前行”于是 running_total 每一行都是截至当前课程的累计值和。这种逻辑以前要用变量或者多层子查询硬写现在一句 SQL 就解决。同理移动平均只要把窗口范围改成 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 就能算出“近 3 条数据的平均值”。6. 用 EXPLAIN 给复杂查询做体检6.1 看懂执行计划的核心字段写复杂查询不会用 EXPLAIN 等于开车不看仪表盘。你只需要在 SQL 前面加一个 EXPLAIN 关键词MySQL 就会告诉你这条查询准备怎么执行EXPLAIN SELECT s.name, c.name, sc.score FROM score sc JOIN student s ON sc.student_id s.id JOIN course c ON sc.course_id c.id WHERE sc.score 60;重点关注几个字段type 表示访问类型从好到差大致是 system const eq_ref ref range index ALL看到 ALL 就要警惕全表扫描key 表示实际使用的索引rows 是预估扫描行数数字越大说明成本越高Extra 里如果出现 Using filesort 或 Using temporary说明排序或分组没法用索引完成数据量一大性能就会崩。6.2 联合索引的顺序不能乱性能优化里索引设计和 SQL 写法的配合特别重要。比如这张成绩表经常按“课程 分数”过滤建一个联合索引 (course_id, score) 就很合理。但要注意联合索引有“最左前缀”原则查询条件里如果只写 score 而不带 course_id这个索引就用不上。所以建索引之前先梳理你的 WHERE、JOIN、ORDER BY 到底涉及哪些列把最常用于等值筛选的列放最前面。6.3 三个常见的性能杀手我总结过复杂查询最常见的三个性能杀手。第一SELECT *。复杂查询结果集往往很大只取需要的列既能减少网络传输也能让优化器更容易覆盖索引。第二在索引列上做函数运算比如 WHERE DATE(create_time) 2024-01-01索引会失效正确写法是 WHERE create_time 2024-01-01 AND create_time 2024-01-02。第三深分页LIMIT 100000, 20 这种写法要扫描前面十万行才能丢掉建议改成基于主键或唯一键的游标分页。7. 综合实战学生成绩排行与统计一次搞定7.1 需求拆解这一节的实战需求很典型按班级统计每位学生的总成绩排名同时在结果里展示学生的选课门数、平均分以及该学生每门课是否高于课程平均分。这个需求如果不拆解很容易写得一团糟。我把它拆成三层第一层要按学生汇总总成绩和选课数第二层要按总成绩在班级内排名第三层要关联每门课的平均分做对比。7.2 逐步实现先做第一层和第二层用窗口函数直接一步到位SELECT s.id AS student_id, s.name, s.class_id, COUNT(sc.course_id) AS course_cnt, SUM(sc.score) AS total_score, RANK() OVER (PARTITION BY s.class_id ORDER BY SUM(sc.score) DESC) AS class_rank FROM student s LEFT JOIN score sc ON s.id sc.student_id GROUP BY s.id, s.name, s.class_id;注意一个细节窗口函数 OVER 里的 ORDER BY SUM(sc.score) 这种写法是允许的因为它是在分组聚合之后才计算的窗口。先把学生维度的数据算出来再用窗口函数在同一批结果上打排名不需要额外子查询。第三层更复杂一点先用派生表算出每门课的平均分再关联到成绩明细最后用 CASE WHEN 判断学生成绩是否高于平均分SELECT sc.student_id, sc.course_id, sc.score, CASE WHEN sc.score c.avg_score THEN 高于平均 WHEN sc.score c.avg_score THEN 等于平均 ELSE 低于平均 END AS compare_result FROM score sc JOIN ( SELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id ) c ON sc.course_id c.course_id;7.3 把多层逻辑串起来如果业务上要求把上面的学生排名和课程对比合成一张宽表我的建议是不要强行用一个超大 SQL 一次写完。更稳妥的做法是分别查出学生维度和成绩明细维度在应用层按 student_id 关联。这样每条 SQL 的职责清晰执行计划容易把控排查问题也方便。很多复杂查询翻车不是 SQL 语法不会而是过度追求“一条 SQL 天下无敌”最后谁都维护不了。8. 复杂查询常见问题速查表最后整理一份我平时答疑时最常遇到的排查清单建议截图收藏现象可能原因排查方向LEFT JOIN 后数据变少附加条件误放 WHERE移到 ON 后面分组查询报 ONLY_FULL_GROUP_BY 错误SELECT 含未分组的非聚合列补全 GROUP BY 列或改聚合逻辑同一条 SQL 结果突然很慢驱动表顺序变化或索引未命中用 EXPLAIN 看 type 和 rowsIN 子查询结果为空但直觉不该为空子查询里混入 NULL改用 EXISTS 或过滤 NULL排名结果出现跳号或行数不符RANK 和 ROW_NUMBER 选错确认业务是否允许并列汇总数字翻倍JOIN 导致行数膨胀检查每张表的粒度先聚合再关联排序结果不稳定排序字段有重复值加第二排序字段保证稳定性做复杂查询这么多年我个人最大的体会是SQL 能力的瓶颈不在语法记忆而在“能不能在大脑里把执行顺序跑一遍”。每一条复杂查询你都要能回答——哪些条件在 WHERE 阶段生效哪些在 HAVING 阶段生效哪个子查询先执行哪个窗口后计算。把这一讲里的执行顺序、JOIN 语义、子查询逻辑、窗口函数边界都吃透再去面对面试题或者生产报表你会发现大多数“难题”其实只是几个基础能力的叠加。最后再分享一个建议写完每条复杂查询都掏出一个最小数据集亲手执行一遍别只盯着结果对不对还要看 EXPLAIN 的扫描行数变化。这个习惯比看十篇教程都管用。