SQL这门语言可能是被误解最深的技术。一提它很多人脑子里浮现的还是二十多年前的增删改查、多表JOIN觉得它是数据领域的“老古董”。但如果你真的翻一遍最近十年的SQL标准更新或者在新一代数据库里敲几行SQL你会发现在这个所有人都在追新语言、新框架的时代SQL反而是迭代得最勤奋、也最实用的那一个。所谓现代SQL不是给这门语言涂一层新漆而是关系模型在数据形态爆炸之后的一次系统性进化JSON进来了、数组进来了、窗口函数和递归CTE成了标配、执行引擎也从逐行解释变成了向量化计算。这篇文章就聊聊我理解的现代SQL到底是什么、它解决了哪些实际问题以及这几年用下来我认为最值得掌握的东西。1. 从SQL:2016到SQL:2023标准这十年走完了过去三十年没走完的路1.1 我最初对SQL的误判我以前也觉得SQL就是老样子查数据、改数据、建表语法二十年不动。真正让我改观的是几年前维护一个报表系统发现旧写法里那些密密麻麻的自连接和子查询在很多新数据库里完全可以用一个窗口函数三板斧改干净。后来我去翻了标准文档才发现SQL其实一直在更新只是很多开发者没意识到自己每天用的语法已经是“现代SQL”的一部分。早期SQL标准确实慢SQL-92之后隔了很久才有SQL:1999再往后SQL:2003、SQL:2008、SQL:2011、SQL:2016、SQL:2019一直到SQL:2023。有人说标准更新快有什么用数据库厂商又不跟。这话对一半厂商确实不会全盘照抄标准但头部数据库产品的大版本迭代节奏基本是在向标准靠拢的。窗口函数、MERGE、LATERAL派生表、JSON处理、行模式匹配这些当年新标准里写的特性今天你在主流数据库里都能用区别只是语法细节上的方言差异。1.2 真正改变日常开发的三项里程碑如果只让我挑三个最影响日常开发的里程碑我会选窗口函数、JSON支持和递归CTE。窗口函数在SQL:2003就被写进标准了但真正普及是过去十年的事。它解决的痛点是“分组不折叠”传统GROUP BY把一组行压成一行你拿不到组内的第几名、累计求和、移动平均只能写子查询或者应用层二次处理。窗口函数让SQL具备了在组内逐行计算的能力这直接改变了复杂报表的写法。JSON支持尤其值得一提。SQL:2016引入了JSON数据类型和相关操作SQL:2019又补了SQL/JSON路径语言。它意味着主流关系数据库不再排斥半结构化数据你可以在同一张表里既存规整的字段又存一段灵活的业务JSON。我在实际项目里的感受是不是所有数据都适合建模成两列四行的关系表但也不是所有数据都值得单独上文档数据库。能用一张带JSON列的表解决的问题没必要多维护一套存储。递归CTE则是把“遍历树”这种以前必须写存储过程、写递归程序才能做的事情硬生生压进了一条SELECT语句里。SQL标准对它的定义其实很朴素一个基础结果集一个迭代部分一个跳出条件。但就这么点东西让SQL从单纯的集合操作变成了具备有限表达能力的图遍历工具。标准版本核心增量对开发者的直观影响SQL:2003窗口函数、MERGE报表查询可以写组内排名、累计值SQL:2011时间周期表时态数据建模有了标准参考SQL:2016JSON、行模式匹配关系库开始处理半结构化数据SQL:2019SQL/JSON路径语言JSON查询有了统一表达式SQL:2023更多属性图与面向对象特征图数据与关系数据的融合试探标准层面的演进给我的一个重要启发是SQL不是停滞的它只是用一种“慢慢来”的姿态在演进。你要做的不是等所有特性都普及了再学而是先把你所在的数据库版本已经支持的特性用起来。2. 窗口函数和通用表表达式把SQL从“查询”变成“可读的程序”2.1 窗口函数分组排序、移动累计这类逻辑别再交子查询了窗口函数的价值一句话就能讲清楚它让你可以在不合并行的前提下基于一个分组窗口去计算。包括ROW_NUMBER()、RANK()、DENSE_RANK()、SUM() OVER()、LAG()/LEAD()、AVG() OVER(ROWS BETWEEN ... AND ...)。这些函数组合起来能解决什么问题呢举个例子。有一张销售明细表字段是销售员、月份、金额。你想算每个销售员在自己组内的按金额排名还要算他过去三个月的移动平均。传统写法要写两个相关子查询一个查排名一个查窗口均值SQL会变得又长又难调试。窗口函数版本就干净很多SELECT salesperson, month, amount, ROW_NUMBER() OVER (PARTITION BY salesperson ORDER BY amount DESC) AS rn, AVG(amount) OVER ( PARTITION BY salesperson ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg FROM monthly_sales;第一列排名第二列移动平均一次扫描解决。我最早看这段代码时最大的感慨是窗口函数的“PARTITION BY ORDER BY”骨架和GROUP BY查询长得太像了但它不折叠行所以保留了每行细节。这种“不合并也能聚合”的思路是理解整个现代SQL的关键。使用窗口函数时有一个很容易踩的细节ROWS BETWEEN 2 PRECEDING AND CURRENT ROW定义的是帧frame。如果不写这个子句不同数据库对默认帧的定义不一样结果就可能不同。我见过有人生产环境线上计算结果对不上排查半天发现是窗口帧语义差异。提示默认的窗口帧在某些数据库里是“到当前行为止的所有行”在另一些数据库里是整个分区。如果你要对移动平均这类有明确范围的计算不要依赖默认明确写ROWS BETWEEN。2.2 CTE与递归CTE把复杂逻辑拆成步骤CTECommon Table Expression是现代SQL里另一个“用过就回不去”的功能。它本质上就是一个命名的临时结果集用WITH开头一次定义多次引用。它的最大贡献是让复杂SQL从“一行长龙”变成“分步骤的流水线”。比如你想找出所有在A类产品上花了超过一万块、又在B类产品上月均消费超过五百的用户这种多层过滤条件落地到SQL里如果不用CTE最直接的写法就是嵌套子查询叠三层可读性很差。用CTE拆开WITH a_users AS ( SELECT user_id, SUM(amount) AS total_spent FROM orders WHERE product_category A GROUP BY user_id ), b_monthly AS ( SELECT user_id, AVG(monthly_amount) AS avg_monthly FROM ( SELECT user_id, month, SUM(amount) AS monthly_amount FROM orders WHERE product_category B GROUP BY user_id, month ) t GROUP BY user_id ) SELECT a.user_id FROM a_users a JOIN b_monthly b ON a.user_id b.user_id WHERE a.total_spent 10000 AND b.avg_monthly 500;我平时习惯把CTE当SQL里的“函数提取”一段查询先叫什么名字、后面怎么引用思路和写程序时拆函数完全一样。递归CTE则是把能力更推了一层比如组织架构表一张表存着员工ID和上级ID要找出某个部门下所有人的所有层级递归CTE是这样写的WITH RECURSIVE org_tree AS ( SELECT emp_id, emp_name, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id, t.depth 1 FROM employees e JOIN org_tree t ON e.manager_id t.emp_id ) SELECT * FROM org_tree;关键理解递归CTE分两部分前半是种子查询后半是递归查询UNION ALL把它们连起来数据库会反复执行后半部分直到返回空。这个模型和写递归函数很像但它在SQL引擎里是由集合操作驱动的不需要你手动管理栈也不需要担心深度爆栈。实际用的时候唯一需要留神的是递归终止条件写不好会让一次查询扫全表直到超时。2.3 我自己的习惯把SQL改写成“函数式管道”用现代SQL这几年我养成一个习惯一段业务逻辑超过三个嵌套查询我必然先用CTE把它拆成步骤。为什么因为可读性和可调试性。真出问题时把WITH里每个CTE单独跑一遍数据在哪一步不对一目了然比在一行两千字符的SQL里抠括号要高效得多。而且CTE还有一个隐藏优势优化器通常能把它当成独立子查询做物化或内联展开不需要你手动关心中间结果要不要落临时表。你在逻辑上写步骤物理执行计划交给优化器。这和过去写临时表的思路完全不同是现代SQL“面向集合、面向逻辑”的工作方式。3. JSON、数组与嵌套结构半结构化时代的关系数据库求生记3.1 JSON列不是只能存关键在查询路径以前很多老系统处理JSON的方法是数据库里存一个TEXT字段取出来丢给应用层用某个JSON库解析。这种方案的痛点是“存进去容易查出来难”你想在数据库层面过滤某个JSON字段里的值根本做不到。现代SQL的标准做法是把JSON变成一等公民。大多数主流数据库都支持JSON类型很多还区分了纯JSON和二进制JSON名字可能不同本质是解析后的二进制格式并提供对应的提取运算符、路径查询函数。你可以直接在SQL里过滤JSON字段的某个层级SELECT user_id, profile FROM users WHERE profile-city 上海 AND CAST(profile-age AS INTEGER) BETWEEN 18 AND 30;这里-取文本、-取对象或数组算是这类数据库的常见方言。路径查询再复杂一点还能用JSON_PATH之类的表达式定位到嵌套数组里的元素然后做条件过滤、聚合、展开。它的意义是业务上那些“变化特别快的字段”终于不用频繁ALTER TABLE加列了你把它塞进一个JSON字段查询层一样能做到筛选和统计。我在实际项目里的感受是JSON列最忌讳的是“无脑堆一切”。适合放JSON的是那些结构不稳定、大概率未来三个月还要加字段的东西比如第三方回调原始报文、用户自定义偏好设置、运营配置快照。不适合放JSON的是那些查询条件稳定、需要频繁关联和索引的字段那种该建列就建列该建外键就建外键。3.2 数组与UNNEST一列拆多行一行拼多列数组在很多数据库里是原生类型但很多人没意识到它配合UNNEST之后有多好用。UNNEST的含义是把一个数组展开成多行相当于把“列表”拆成一张临时表。场景很常见一张表里存着用户打的标签字段是字符串数组你想统计每个标签的分布一行SQL就做完了SELECT tag, COUNT(*) FROM users CROSS JOIN LATERAL UNNEST(tags) AS t(tag) GROUP BY tag ORDER BY COUNT(*) DESC;这张查询本质上是在做“行变列”的展开。反过来“列变行”或者多行聚合成数组也成了现代SQL的常规操作。比如把每个用户在多个订单里的金额汇总成一个数组用ARRAY_AGG就能搞定SELECT user_id, ARRAY_AGG(amount ORDER BY order_date) AS amounts FROM orders GROUP BY user_id;数组和UNNEST组合起来让SQL在处理列表型数据时有了很强的表达能力。以前这种逻辑要拉到应用层做循环现在一条查询搞定。3.3 什么时候该用结构什么时候该老实建表这是最常见的争论JSON和数组这么方便关系模型是不是该淘汰了我的观点正好相反——恰恰是因为关系模型还在JSON和数组才有地方安放。我的判断标准很简单如果字段是业务实体的“稳定属性”就建列如果字段是“可变扩展信息”就放JSON如果字段本身描述的是一个集合就用数组。比如订单状态建列因为业务逻辑要频繁where它、分组它、索引它订单的来源渠道带一堆渠道商自定义参数放JSON因为这些参数是渠道商说了算你不可能每个渠道建一列订单里的赠品列表用数组因为它天然就是一个列表。注意JSON字段不是不能建索引而是索引方式与传统列索引完全不同通常用路径表达式建表达式索引或GIN索引成本也不低。你需要确认你的查询模式真正需要它再来建这类索引。4. 藏在SQL背后的“现代引擎”向量化、列式存储与统计信息4.1 为什么现代SQL跑得快从逐行到批量同一个SQL十年前和现在跑起来可能差一百倍但SQL语法看起来一模一样。差别在引擎内部。老一代执行引擎是典型的“火山模型”每个算子一行一行地往外吐数据上层算子再逐行处理。好处是实现简单坏处是函数调用开销巨大CPU缓存命中率差。现代分析型数据库普遍转向向量化执行一次从内存里取一批数据比如1024行批量交给算子处理中间还配合SIMD指令做并行计算。这个设计思路和批量处理工厂很像单件流水的工人手速再快也不如流水线一次送一整筐零件统一开机器处理。对于聚合、过滤、排序这类CPU密集操作向量化带来的收益是数量级的。更极端的例子是列式存储。传统行式存储把一个表的整行数据挨着放查询只需要两列时也会把整行读进内存浪费IO带宽。列式存储把每一列单独存放查询只要读涉及的列而且同一列的数据类型一致压缩率通常远高于行式存储。对分析型查询来说这种布局几乎是压倒性的优势。4.2 优化器不是玄学统计信息直接决定SQL命运写现代SQL不能只想着“这语法对不对”还得想“这SQL跑起来快不快”。而快不快很大程度由优化器决定。优化器会基于表的统计信息做基数估算然后生成执行计划选择哪个索引、什么顺序JOIN、是否做哈希连接还是嵌套循环这些都是统计信息驱动决策的结果。统计信息包括行数、每列的NULL比例、高频值、直方图分布等。如果统计信息过期或者缺失优化器很容易做出错误判断。我遇到过最典型的问题两张大表JOIN某张表刚经历了一次几千万行的批量删除统计信息没刷新优化器根据旧的行数选择了错误的JOIN顺序结果一条本来一秒完成的查询跑了三分钟。解决思路不复杂日常注意维护统计信息排查慢SQL的第一步永远不是改SQL而是EXPLAIN看执行计划看它估的行数和实际行数差了多远。很多所谓SQL“玄学调优”本质都是优化器在错误信息基础上做的合理决策只是客观结果不合理而已。4.3 并行、物化视图与增量改写工程侧的三件套除了向量化和列式存储现代SQL“看起来快”还因为工程侧做了三件事。第一是并行查询。一个查询可以切分成多个分片分给多个CPU核同时跑最后合并结果。这个机制对大数据量扫描特别有效。你看一条SQL没变但底层线程数变了吞吐自然上来了。第二是物化视图。普通视图只是一层壳查询时还是要现算物化视图把查询结果实际存起来查询时直接读结果。它的价值在于把昂贵的聚合计算提前到写入或调度阶段完成。代价是牺牲实时性、增加存储。我在报表场景里用得很多但会特意关注它的刷新策略。第三是增量改写。这是最精巧的一环数据库能在你查询一个物化视图时自动判断是否能通过“上次结果新增数据增量计算”来更新视图而不必全量重算。增量改写依赖数据库自己的改写器能力有时候逻辑等价性判断非常复杂但它确实让“大查询”变成了“小更新”。理解引擎这些机制对SQL使用者有什么实际帮助帮助是当你写一条现代SQL时你写的是一份逻辑意图而数据库有一套复杂的“编译调度存储”体系在背后把它变成物理执行过程。你不需要完全理解向量化细节但你需要尊重统计信息、懂得看执行计划避免写出让优化器无能为力的SQL。5. 现代数据栈里的SQL从报表查询到特征工程与向量检索5.1 ELT与湖仓SQL成为数据加工的统一入口以前的数据链路里SQL只出现在BI报表阶段数据加工大多靠写脚本、跑定时任务去完成。现在的趋势完全变了。数据先落到对象存储或数据湖然后通过SQL直接把原始数据加工成宽表、汇总表、指标表这就是ELT模式——先把数据原封不动载入转换全部由SQL在仓库内完成。这套模式能跑通依赖现代SQL的几个能力。一个是复杂的表达式和UDF机制你可以在SQL里定义自定义函数处理文本、解析JSON、做正则匹配。另一个是标准化的窗口函数和CTE让数据清洗的每一步逻辑可读、可复现。还有一个是灵活的存储格式这个时代的数据仓库往往直接定位在对象存储上SQL扫描的是Parquet这类列式文件元数据由湖引擎管理。在这个结构下SQL的定位已经从“查询语言”变成了“数据加工语言”。数据工程师每天写的SQL本质上是在构建一条数据管道管道每个节点都是一段CTE或者一个INSERT INTO SELECT输入是一批原始数据输出是业务直接可用的表。这比旧时代把加工逻辑散落在脚本语言里要统一得多。5.2 特征工程里的SQL窗口函数在机器学习场景的价值还有个容易被忽视的场景是机器学习特征工程。做模型训练时你需要把原始行为数据加工成每个样本的特征最常见的特征类型恰恰是时序相关的过去七天累计消费、过去三十天购买次数、最近一次行为的间隔天数。这些特征如果用Python逐行处理可能要面对几千万行数据又慢又占内存。窗口函数和CTE天然适合这种“按主体分组、按时间排序、计算窗口聚合”的加工逻辑。WITH base AS ( SELECT user_id, event_time, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC) AS seq FROM user_events ) SELECT user_id, SUM(amount) FILTER (WHERE seq 10) AS last_10_amount, COUNT(*) FILTER (WHERE seq 7) AS last_7_cnt, MAX(amount) FILTER (WHERE seq 30) AS last_30_max FROM base GROUP BY user_id;这里还用到了FILTER子句它可以在聚合函数内部附加一个条件过滤相当于把一个条件聚合写进一个函数调用里比CASE WHEN包一层更清晰。现代SQL的特性就是这样一环扣一环窗口函数负责组内排序FILTER负责条件聚合CTE负责封装中间结果最后GROUP BY收敛成特征宽表。用熟了之后你会觉得写特征工程SQL和写Python处理逻辑在思路上是等价的只是前者不需要你考虑内存和数据搬运。5.3 AI时代的新SQL的一角向量距离计算这几年AI应用带火了一个概念向量检索。很多人的第一反应是这得用专门的向量数据库但其实主流关系数据库已经在SQL里增加向量类型的支持。你可以建一个向量列存文本或图像的Embedding然后直接在SQL里算距离、做KNN检索SELECT id, content, embedding - $query_embedding AS distance FROM documents ORDER BY distance LIMIT 5;这里的-运算符在不同数据库里可能是欧氏距离、余弦距离或者内积具体要看实现。向量索引也有很多种结构大的方向是近似最近邻搜索而不是精确搜索因为这才能在百万级向量上做到毫秒级返回。这件事给现代SQL带来的新含义是SQL的用户边界正在扩大数据科学的很多工作开始回到SQL里完成。你不需要用一套完全独立的工具来管理向量你的通用数据表里直接加一列就可以。当然这里也要提醒向量检索真正要处理的数据量往往巨大通用数据库的向量能力在超大规模场景下未必是最优解先看规模再选型。6. 这些年我踩过的现代SQL的坑6.1 窗口函数性能陷阱看着简洁背后可能全表扫描窗口函数虽好但性能特征和GROUP BY完全不同。GROUP BY一般可以把分组算子下推到存储层做部分预聚合而很多窗口函数必须先按PARTITION BY字段做全局排序ORDER BY PARTITION BY处理不好就是一次大排序。我遇到过一个真实案例一张上亿行的明细表加了个ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY time DESC)用来取每个用户最新一条记录。逻辑没问题但分区数量几十万个每个分区内排序整体就是一次巨大的磁盘排序查询跑了四十多秒。后来尝试的优化思路是把窗口逻辑改写成JOIN子查询或者利用索引把预排序提前让执行计划直接用索引顺序替代排序操作耗时才降下来。这给我的教训是窗口函数写起来要检查执行计划尤其注意有没有Sort算子以及Sort的数据量有多大。不是不能用而是要带着对成本的意识去用。6.2 JSON字段的索引“看起来很美”JSON字段建索引的坑和我之前提的路径表达式索引强相关。很多人以为给JSON列建个普通索引就能加速一切JSON查询其实完全不是。普通索引只能加速直接对整列的等值查询而JSON查询里你过滤的是某个路径值必须为该路径建单独索引。有一次我在一个业务表里给JSON字段加了索引以为查询会变快结果EXPLAIN一看走的仍然是全表扫描因为条件写在路径表达式上压根没匹配到那个索引。后来改成基于路径的表达式索引才算真正生效。所以写JSON查询时心里要有个清单你过滤的路径有哪些、这些路径是否都建了对应索引、索引是否会被表达式或类型转换干扰。JSON方便归方便优化器不会因为你用的是JSON就开绿灯。6.3 方言差异比想象中更凶现代SQL的痛点之一是方言差异。不同的数据库对JSON操作符定义不同有的用-有的用$.路径表达式有的支持标准SQL/JSON函数有的只有传统函数。窗口函数的名字大体统一但窗口帧的默认规则、FILTER的细节支持程度、递归CTE的某些边界行为也可能不同。项目迁移时这个坑尤其明显。我之前参与过一个跨数据库迁移项目第一批就遇到了字符串截断、时间函数参数顺序、NULL排序规则这些完全不一致的细节。SQL标准在努力统一语言但具体数据库的实现始终有各自的历史包袱和扩展策略。应对办法只有一个尽量把公共查询逻辑收敛在少数几个核心SQL模式里迁移时集中改这几处而不是让全项目散落各种花式写法。6.4 一个建议换个思维方式而不是背语法最后说一点个人体会。现代SQL的语法表面上是新增了一批函数但底层思维方式变了你要从“一行一行地思考”切换到“一批一批地思考”从“先取出来再处理”切换到“在集合内部直接计算”。窗口函数、CTE、JSON路径、数组展开全都是这种集合思维的产物。在我带过的团队里很多人写不好现代SQL问题往往不在语法而在习惯。他们拿到一个需求第一反应是写个程序循环正确做法是先问自己这个问题能否用集合操作表达如果能在SQL里一次性算完就完全不需要引入应用层循环。提示我日常训练新人的一个小方法是让他们把一段Python里用循环实现的逻辑尽量改写成一条SQL不允许用临时表也不允许在循环里逐行处理。做过几轮之后他们对现代SQL的理解会有一个明显变化。SQL这条路走了半个世纪非但没被取代反而在数据形态更复杂的今天变成了通用语言。我觉得它的厉害之处不在于某一个新特性而在于整个体系一直在不动声息地进化——你不需要推翻原来的知识框架只需要不断往里面加新的思维模型。写SQL越久越觉得这门语言真正值钱的不是记住所有语法而是知道什么场景下有一种更优雅、更高效的集合式解法。