MySQL SQL进阶：CTE、窗口函数与索引优化实战

Day5-MySQL-SQL-4看到这个编号你就知道我现在是把自己按课程节奏摁着学MySQL。前面几天把 SELECT、WHERE、JOIN、GROUP BY 这些基础啃完写单表查询已经不怎么卡壳了但一到真实业务需求里照样犯怵要取分组内前几名要算同环比要把多行明细拼成一行要在同一张表里搞出行转列……我估计很多自学者都卡在这个阶段语法都认识题不会做或者做出来一团乱麻。这个模块就是专门解决这种基础到实战之间断层的。第4个SQL模块重心不再是你认识多少函数而是你能不能把一个业务问题拆成几个清晰的步骤再用子查询、CTE、窗口函数、条件聚合把它们组合起来。适合刚学完SQL基础、准备刷题或者做实际报表的人也适合那些写了几年SQL但全靠临时表和复制粘贴的人——看完你会有一种原来还能这么写的感觉。1. 为什么说SQL-4才是MySQL实战的分水岭1.1 Day5这个节点的真实关卡我见过太多人学到SQL第5天时状态是这样的单表查询没问题多表JOIN也能凑出来但一面对每个渠道销售额前3名的订单明细这类需求第一反应是先把数据导到Excel里或者建一堆临时表慢慢凑。这不是笨是缺少一个关键转换能力把业务语言翻译成集合操作语言。所谓集合操作就是说SQL处理的是行集,你脑子里想的应该是我有哪几张表需要参与先算谁后算谁最后怎么合并,而不是我要循环遍历每一行。SQL-4讲的就是这个思维转变。子查询是让你学会分步思考CTE是让你学会把思考过程写清楚窗口函数是让你在保留明细的前提下做聚合CASE WHEN和GROUP_CONCAT又是对付报表需求的组合拳。这些东西凑在一起你会发现此前很多必须靠程序处理的逻辑其实一条SQL就能做完。1.2 从能跑到跑得对、读得懂很多人在这个阶段还有另一个误区只要结果对就行SQL写得丑没关系。但我自己的体会是SQL-4开始代码的可读性会直接影响你排查问题的速度。你今天写一个嵌套三层的子查询当时看得懂两周后你自己都看不明白当初为什么这么写别人接手更是一头雾水。所以这个模块里我会特别强调能用CTE拆开就别硬套子查询窗口函数比临时表加自连接更清晰这些写法上的取舍。SQL-4不是教你更多语法是教你怎么把复杂需求写成人话。1.3 本文统一使用的环境与示例表后面的示例我统一用 MySQL 8.0因为窗口函数、CTE这些能力在8.0里才完整支持。如果你还在用5.7建议至少知道这些写法是存在的然后找机会升级环境。先建一张销售订单表后面所有的案例都拿它说事CREATE TABLE sales_order ( order_id BIGINT PRIMARY KEY, channel VARCHAR(20) COMMENT 渠道APP/门店/小程序, region VARCHAR(20) COMMENT 区域, amount DECIMAL(12, 2) COMMENT 订单金额, pay_time DATETIME COMMENT 支付时间 ); INSERT INTO sales_order VALUES (1001, APP, 华东, 1200.00, 2025-01-02 10:20:00), (1002, 门店, 华南, 4500.00, 2025-01-03 11:15:00), (1003, 小程序, 华东, 800.00, 2025-01-05 09:30:00), (1004, APP, 华南, 3200.00, 2025-01-08 14:40:00), (1005, 门店, 华东, 2800.00, 2025-01-10 16:00:00), (1006, 小程序, 华南, 1500.00, 2025-01-12 20:10:00), (1007, APP, 华东, 3800.00, 2025-02-01 12:00:00), (1008, 门店, 华南, 2100.00, 2025-02-03 18:30:00);数据量不大但足够讲清楚每一种写法的结构。实际业务里你只需要把表名、字段名替换成自己的就行。2. 子查询和CTE先拆开再拼接2.1 子查询SQL的四种套娃位置子查询说白了就是查询里套查询。它在SQL里的位置有四种很多人只熟悉一种其实每一种对应一种需求类型SELECT子句里的子查询通常用来补一列计算结果FROM子句里的子查询相当于先构造一张临时表再和别的表做关联WHERE子句里的子查询配合IN、EXISTS、比较运算符做过滤HAVING子句里的子查询用得少但偶尔能解决分组之后还要再筛选的问题。举一个最常见的例子找出所有高于本区域平均订单金额的订单。这个需求拆开是两步先算每个区域的平均金额再对比每一张订单。如果不用子查询你得先手动算区域均值再写第二个查询用了子查询一条SQL搞定SELECT s.order_id, s.region, s.amount FROM sales_order s WHERE s.amount ( SELECT AVG(amount) FROM sales_order WHERE region s.region ) ORDER BY s.region, s.amount DESC;这里括号里的子查询用了关联条件region s.region它会对每一行订单都执行一次。数据量小的时候无所谓数据量大就要留意性能这也是后面要讲为什么要学CTE的原因之一。2.2 CTE让人一眼看懂你的思路CTE全称是公用表表达式在MySQL里用 WITH 开头。它解决的最大问题就是子查询一多层嵌套读代码的人会疯掉。举个例子还是上面那个需求用CTE写是这样WITH regional_avg AS ( SELECT region, AVG(amount) AS avg_amount FROM sales_order GROUP BY region ) SELECT s.order_id, s.region, s.amount, ra.avg_amount FROM sales_order s JOIN regional_avg ra ON s.region ra.region WHERE s.amount ra.avg_amount;注意感受一下WITH 部分把计算区域均值这个步骤单独拎出来了后面主查询读起来就像在读一段话先有区域均值表然后拿订单去关联过滤掉不满足条件的。逻辑顺序和书写顺序一模一样这就是CTE最大的价值——可读性。你自己排查问题方便别人看你的SQL也轻松。CTE还可以多个串起来前一个CTE的结果给后一个用相当于把临时表的思路直接写进SQL里又不需要真的去创建表WITH order_level AS ( SELECT order_id, region, channel, amount FROM sales_order WHERE amount 0 ), region_channel AS ( SELECT region, channel, COUNT(*) AS order_cnt FROM order_level GROUP BY region, channel ) SELECT * FROM region_channel ORDER BY region, channel;这比嵌套子查询友好太多了。我自己的建议是只要逻辑超过两步能用CTE就尽量用CTE别为了炫技去硬套多层子查询。2.3 递归CTE5行代码生成连续日期SQL-4里另一个值得花10分钟掌握的是递归CTE。最常见的场景是补全日期报表里经常只记录有业务的日期没有订单的日期就是空白行你需要在结果里补出完整的日期序列。这里用递归CTE生成最近7天的日期WITH RECURSIVE seq AS ( SELECT CURDATE() - INTERVAL 6 DAY AS dt UNION ALL SELECT dt INTERVAL 1 DAY FROM seq WHERE dt CURDATE() ) SELECT dt FROM seq;递归部分就两步初始查询给起点递归查询不断加一天直到条件不满足。运行起来就是连续7天。这种写法在做按天补零自然周补全的时候特别好用算是SQL-4里一个小彩蛋我经常把它用在看板数据补全上。3. 窗口函数把排名、累计、环比一次算完3.1 窗口函数和GROUP BY的本质区别很多人学窗口函数之前先被一个问题困住它和GROUP BY到底哪不一样一句话解释GROUP BY会把多行压成一行丢失明细窗口函数不丢明细它是在保留每一行的情况下在旁边附加一个聚合结果。比如SELECT region, SUM(amount) FROM sales_order GROUP BY region;出来一行一个区域但窗口函数SELECT order_id, region, amount, SUM(amount) OVER (PARTITION BY region) FROM sales_order;出来的还是每行一条订单只是旁边多了一列这个区域的总金额。一个是压缩一个是加列这就是核心区别。加列这个特性决定了窗口函数特别适合做报表场景你既想看明细又想同时看到汇总还要做排名、累计、环比那窗口函数就是首选。3.2 ROW_NUMBER、RANK、DENSE_RANK到底该选谁窗口函数入门的第一个门槛就是这三个排名函数的区别。很多面试题和生活里的例子都喜欢拿成绩排名考这个我直接把区别摊开说。函数相同分数怎么排下一个名次适合场景ROW_NUMBER()随机或按副序字段分先后连续取前N条每人只保留一条记录RANK()并列同一名次跳过名次比赛排名允许并列且保留空位DENSE_RANK()并列同一名次不跳名次按等级划分名次紧凑举个例子就明白了。建一张成绩表语文成绩分别是95、88、88、76三个函数的输出分别是SELECT student_id, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, RANK() OVER (ORDER BY score DESC) AS rk, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rk FROM t_score;结果会是第一个学生三列都是1并列的第二个学生 row_num2、rk2、dense_rk2下一个学生是 row_num3、rk4、dense_rk3。看到没有ROW_NUMBER永远不并列RANK会跳号DENSE_RANK不跳号。业务里取每个渠道订单金额前3名的写法是这样的SELECT order_id, channel, amount, rn FROM ( SELECT order_id, channel, amount, ROW_NUMBER() OVER (PARTITION BY channel ORDER BY amount DESC) AS rn FROM sales_order ) t WHERE rn 3 ORDER BY channel, rn;注意我外面包了一层子查询因为窗口函数的结果不能直接在WHERE里用——这个执行顺序问题下面会专门说。这套写法几乎是所有报表项目的标配学会了就再也不用靠自连接GROUP BY去模拟排名了。3.3 累计求和与同环比SUM OVER和LAG LEAD窗口函数里除了排名最高频的就是累计求和和同环比。累计求和是SUM(...) OVER (PARTITION BY ... ORDER BY ...)它按顺序逐行累加特别适合做截至当前的累计销售额SELECT order_id, channel, amount, SUM(amount) OVER (PARTITION BY channel ORDER BY pay_time, order_id) AS running_total FROM sales_order ORDER BY channel, pay_time;同环比则需要LAG和LEAD函数它们能取到上一行或下一行的值。比如我想看每个区域逐月的销售额并且跟上个月对比算出增长率写法是WITH monthly AS ( SELECT region, DATE_FORMAT(pay_time, %Y-%m) AS ym, SUM(amount) AS total FROM sales_order GROUP BY region, DATE_FORMAT(pay_time, %Y-%m) ) SELECT region, ym, total, LAG(total, 1) OVER (PARTITION BY region ORDER BY ym) AS last_month_total, ROUND((total - LAG(total, 1) OVER (PARTITION BY region ORDER BY ym)) / LAG(total, 1) OVER (PARTITION BY region ORDER BY ym) * 100, 2) AS mom_pct FROM monthly ORDER BY region, ym;先按月汇总再用LAG取上个月的值。第一个月因为没有上一行LAG会返回NULL那么环比也是NULL正好符合业务认知。如果你的数据库是5.7这个逻辑就得用两次自连接配合子查询来模拟代码很长8.0直接一行LAG就完了。3.4 窗口函数的使用边界和常见小坑窗口函数虽好但有几个坑值得记一下。第一个是执行顺序。SQL的逻辑执行顺序是FROM → WHERE → GROUP BY → HAVING → 窗口函数 → SELECT → ORDER BY → LIMIT。这意味着你在WHERE里不能用窗口函数的结果必须像我上面那样先在外面包一层子查询才能过滤。同理ORDER BY里倒是能用窗口函数的别名。第二个是PARTITION BY的分区不要太多。分区多意味着窗口的滑动机变小如果分区数和行数一样多窗口函数就退化成逐行计算性能会非常难看。第三个是MySQL 8.0对窗口函数有严格语法要求ORDER BY后面要跟字段的排序方向不写方向默认ASC这一点在累计求和的场景里格外重要——排序顺序不一样累计结果就完全错了。4. CASE WHEN 和 GROUP_CONCAT一张表写出报表的宽表4.1 用SUM(CASE WHEN ...)代替多个子查询报表需求里最常见的痛点之一是要把一张明细表变成统计宽表。比如我想统计每个渠道的订单总数、高金额订单数、高金额订单的销售额很多人会写三个查询再拼起来其实用CASE WHEN配合SUM就够了SELECT channel, COUNT(*) AS total_orders, SUM(CASE WHEN amount 3000 THEN 1 ELSE 0 END) AS high_cnt, SUM(CASE WHEN amount 3000 THEN amount ELSE 0 END) AS high_amount FROM sales_order GROUP BY channel;这里CASE WHEN充当了条件计数的作用条件成立输出1不成立输出0SUM统计1的个数就是满足条件的行数。想要满足条件的金额合计把THEN后面的1改成金额字段即可。你如果观察得仔细会发现这其实就是在聚合函数内部嵌套逻辑判断。这套组合可以玩出非常多的花样多条件交叉统计、占比计算、同比口径判断都能在一个SELECT里完成。相比写多个子查询再UNION性能和可读性都赢麻了。4.2 行转列用条件聚合做透视表行转列是另一个高频需求。原始数据是每个区域一行的明细报表却要求区域下每个渠道各成一列。这时候CASE WHEN的价值就体现出来了SELECT region, SUM(CASE WHEN channel APP THEN amount ELSE 0 END) AS app_amount, SUM(CASE WHEN channel 门店 THEN amount ELSE 0 END) AS shop_amount, SUM(CASE WHEN channel 小程序 THEN amount ELSE 0 END) AS mini_amount, SUM(amount) AS total_amount FROM sales_order GROUP BY region;MEMO一行区域三列渠道清清楚楚。如果渠道种类是动态的这个写法就没法硬编码得用动态SQL拼字段名那是进阶玩法SQL-4阶段先把固定列搞明白就够了。4.3 GROUP_CONCAT把多行明细拼进一个单元格GROUP_CONCAT也是报表场景里的宝贝它能把同一组里的某个字段拼接成一行字符串。比如我想看每个区域用过哪些渠道用普通SQL得返回多行用GROUP_CONCAT一行就搞定了SELECT region, GROUP_CONCAT(DISTINCT channel ORDER BY channel SEPARATOR , ) AS channels FROM sales_order GROUP BY region;这里可以加 DISTINCT 去重可以加 ORDER BY 控制拼接顺序默认分隔符是逗号你也可以改成SEPARATOR | 。实际做运营报告的时候我经常用它把某个用户的所有标签拼到一行或者把一段时间内的订单号拼起来供人工核对。有几个小坑要提第一GROUP_CONCAT默认有长度限制一般是1024字节拼接结果太长会被截断需要执行SET SESSION group_concat_max_len 102400;调整第二拼接前一定要想清楚排序否则拼出来的顺序不可控第三如果组里数据量巨大拼接出来的字符串可能会很大注意配合场景使用。5. 日期与字符串报表里最常见的隐形坑5.1 先搞清楚字段到底是日期还是文本我接到过很多求助说为啥我同一条SQL在测试环境跑是好的一到正式环境就报错或者结果不对一查全是字段类型的锅。MySQL里日期有关的类型有DATE、DATETIME、TIMESTAMP有的业务表为了省事还会把日期存成VARCHAR——这就麻烦了因为字符串比较和日期比较的规则完全不一样。所以SQL-4阶段一定要养成一个习惯拿到一个字段先看表结构确认到底是日期类型还是字符串。你可以在MySQL里执行SHOW CREATE TABLE或者DESC sales_order;确认。自己新建表的时候能用DATE、DATETIME就用千万别图省事存字符串后面做日期计算你会哭的。5.2 日期格式化和日期计算函数速查MySQL的日期函数不算多但每次都要去查文档我把最常用的一份整理在下面SQL-4阶段背熟这几个就基本够用了函数作用示例DATE_FORMAT(datetime, fmt)按指定格式输出日期字符串DATE_FORMAT(pay_time, %Y-%m-%d)STR_TO_DATE(str, fmt)把字符串转成日期STR_TO_DATE(2025/01/05, %Y/%m/%d)DATE_ADD / DATE_SUB加/减时间间隔DATE_ADD(2025-01-01, INTERVAL 1 MONTH)DATEDIFF两个日期相差的天数DATEDIFF(2025-01-10, 2025-01-01)LAST_DAY返回当月最后一天LAST_DAY(2025-01-15)用的时候注意一个最经典的坑DATE_FORMAT返回的是字符串不是日期。比如你写WHERE DATE_FORMAT(pay_time, %Y-%m-%d) 2025-01-05能跑但有没有索引都跟你没关系了因为函数包住了字段MySQL大概率做不了索引查找。更专业的做法是用日期范围SELECT order_id, amount FROM sales_order WHERE pay_time 2025-01-05 00:00:00 AND pay_time 2025-01-06 00:00:00;5.3 字符串清洗TRIM、REPLACE、SUBSTRING_INDEX报表里的脏数据永远是躲不掉的。导出来的手机号可能带空格金额字段里可能混了逗号ID字段可能混了换行符。这时候就需要字符串函数来洗数据。最常见的三件套TRIM()去掉两端空格REPLACE(str, 旧, 新)替换指定字符比如REPLACE(phone, -, )SUBSTRING_INDEX(str, delimiter, count)按分隔符截取比如从 华东-大区-销售组 里取第一段SUBSTRING_INDEX(region_code, -, 1)。实际使用中字符串清洗和日期转换经常是连着的。例如某系统导出的时间是2025/01/05 10:30:00你需要先确认格式再转成DATETIMESELECT STR_TO_DATE(2025/01/05 10:30:00, %Y/%m/%d %H:%i:%s);这类操作在SQL-4里属于基本功里的基本功。别小看它们很多看起来莫名其妙的报表错误根源都是某一行数据里多了个空格。5.4 日期函数和索引能查对不代表能查快说到日期函数我必须单独拎出来再强调一遍在WHERE条件里对日期列做函数处理是性能杀手。上面WHERE DATE_FORMAT(pay_time, %Y-%m-%d) 2025-01-05就是典型反面案例。MySQL为了判断这一条件必须对整个表的每一行执行一次DATE_FORMAT索引完全失效。如果表有几百万行一次全表扫描就出来了慢是自然的。正确做法是借助索引的范围扫描特性把函数处理放在等号的右侧。如果你只知道用户输进来的是2025-01-05这种字符串可以先转成日期再比较或者像我上面那样用和划出当天区间。这个思路适用于所有你对日期列的过滤场景值得写进你的SQL习惯里。6. 把SQL写对还不够几类慢查询与索引思维6.1 EXPLAIN到底应该看哪几列SQL-4里会开始接触SQL优化的概念但我不建议一上来就看一堆复杂的执行计划参数。先盯住EXPLAIN输出里的三列足够你排出大多数慢查询type表访问方式。从好到差大致是 system const eq_ref ref range index ALL。看到ALL就说明是全表扫描这是你重点优化的对象。key实际用到的索引名。如果这一列是 NULL说明没用上索引。rows预估扫描行数。这个数字越接近查出来的结果行数越好差得太多就要考虑是不是过滤条件写漏了。实操方法写好的SQL直接在前面加EXPLAIN三个字不用真的跑整个查询就能看到这些信息非常快。对比一下你改条件前后的 type、key、rows 变化优化方向马上清楚。6.2 OR、前导%和函数包裹三个典型的索引失效场景索引失效有很多种原因但SQL-4阶段你应该记住最常见的三种第一种是OR连接条件。比如WHERE channel APP OR amount 3000如果两个字段上没有合适的组合索引MySQL可能直接放弃索引。改成UNION或者用组合查询有时会更好。第二种是LIKE的左匹配。WHERE channel LIKE %APP%因为通配符在开头索引用不上。如果必须这么做考虑全文索引如果只是前缀查询APP%则没问题。第三种就是我前面强调的字段被函数包裹。WHERE DATE_FORMAT(pay_time, %Y-%m-%d) 2025-01-05会让索引失效改成范围查询就好。这个坑最常见也最容易被忽略因为查错和查慢它都不报错你得自己排查。6.3 LIMIT大偏移量翻页越翻越慢的原因还有一个报表里很常见的现象数据量一大翻页到100页以后查询越来越慢。经典的问题是LIMIT 100000, 20。MySQL要先把前面10万行都扫描出来再扔掉然后才取你要的20行效率自然低下。一个绕开的思路是延迟关联或起始点定位-- 用主键或唯一键定位起点再去取页大小 SELECT s.order_id, s.channel, s.amount FROM sales_order s JOIN ( SELECT order_id FROM sales_order ORDER BY order_id LIMIT 100000, 20 ) t ON s.order_id t.order_id;先让索引列快速定位需要的主键再回表取完整行比直接大偏移量高效得多。当然这只是其中一种思路真正业务里还要结合排序字段的类型来定但思路本身值得记下来。6.4 我的SQL-4调试节奏先小样本再套模板最后看执行计划最后分享一下我个人在这个阶段的调试方法这比记住任何单个函数都管用。拿到一个复杂需求我从来不会直接写一条巨长的SQL。我的习惯是先建一个小的临时结果集比如只取10条数据在数据量小的前提下反复试子查询、试窗口函数直到每一步的输出都符合预期。确认逻辑无误后再替换成真实表和全量数据。第二步是学会套模板。分组TopN、同环比、行转列、条件聚合这些场景的写法基本是固定的你不需要每次从头发明直接把本文里的示例换成你的表名和字段名就行。熟练以后看到需求就能在脑子里匹配模板写SQL的速度会快很多。第三步也是容易被忽略的就是写完以后花10秒跑一下EXPLAIN。不要等到数据量大了才发现全表扫描到时候改起来成本高得多。把这个动作变成肌肉记忆你会少踩很多坑。MySQL里SQL这个东西学到最后就是熟练度。Day5的SQL-4是我目前收获最大的一个模块因为它在基础语法和真实业务之间架了一座桥——CTE让逻辑变清晰窗口函数让统计变简单条件聚合让报表不用来回拼表。你照着上面的案例敲一遍再拿自己的业务表试一试应该很快就能感觉到这种原来复杂需求也就这么回事的顺畅感。