数据库SQL系列写到第七篇之前几篇我们聊了各种查询语法、函数、性能优化但有一个绕不开的理论基石我一直没展开讲就是 Relational Algebra关系代数。很多人上学时把它当作数据库原理课里的劝退章节一看到σ、π、⨝、÷这些符号就头大觉得和日常工作毫无关系。但做了几年数据库相关的活之后我的感受刚好相反关系代数不是纯数学它是SQL的执行图纸也是理解查询优化器、分析慢SQL、以及设计复杂查询的最底层思维工具。这篇文章我会用一套非常常见的选课系统数据模型把关系代数的核心运算掰开揉碎讲一遍每个运算都配上对应的SQL写法和实际例子。你不需要有数据库理论的底子只要会写基本的SELECT语句就能跟着思路走完。读完你至少能收获三样东西一是真正看懂执行计划里那些JOIN、FILTER步骤从哪来的二是遇到查询选修了所有课程的学生这类变态需求时脑子里能直接浮现解法骨架三是以后面试被问关系代数有什么用的时候你能说出个一二三四而不是背教材。1. 为什么我建议每个写SQL的人都系统学一遍关系代数1.1 关系代数不是数学课它是SQL的工程图纸很多人有个误区觉得关系代数是数据库理论里最没用的部分因为它充满了抽象的运算符看起来跟写SQL完全是两码事。但实际恰恰相反。SQL本质上是一种描述你要什么的语言它告诉数据库你期望的结果集合却并不规定数据库具体怎么把结果算出来。而关系代数就是一种过程化的运算语言它用有限的几种运算组合明确描述从输入表到输出表的每一步操作路径。打个比方SQL就像是告诉装修师傅我要这样一个客厅关系代数则是那张标明了水电怎么走、墙体哪里拆、家具怎么摆的施工图。数据库的查询优化器拿到你的SQL之后第一件事就是把它解析成一颗关系代数的运算树然后在这棵树上做各种等价变换最终生成真正去执行的计划。也就是说你在EXPLAIN里看到的每一个执行步骤底层都能对应到某一个或者某几个关系代数运算符。如果你完全不理解关系代数那你对执行计划的理解就会停留在这个是索引扫描那个是嵌套循环的表面很难想明白优化器为什么调整JOIN顺序、为什么把过滤条件下推、为什么有的查询可以被自动改写成更轻量的形式。而一旦你掌握了关系代数看执行计划就不再是雾里看花而是能顺着运算树的脉络准确判断一个SQL到底慢在哪里。1.2 八个核心运算符先来个全景图关系代数的核心运算符其实就八个很多人被吓退是因为记不住希腊字母和符号而不是运算符本身复杂。我把它们整理成一张和SQL对应的表格你先把这张表记住后面看细节会轻松很多。运算符名称作用SQL对应σSigma选择从表中筛选满足条件的行WHEREπPi投影从表中挑出指定列SELECT 列名∪Union并合并两个表的所有行UNION−Minus差取一个表中有而另一个表中没有的行EXCEPT / NOT IN×Times笛卡尔积两个表所有行两两组合CROSS JOIN⨝Join连接按条件组合两个表的行JOIN ... ON÷Divide除找出包含另一个关系全部值的集合嵌套 NOT EXISTSρRho更名给表或列起别名AS / 表别名先说清楚一个概念这些运算符里σ、π、∪、−、×、ρ 这六个是最基本的因为其余的连接、除、交等运算都可以由它们推导出来。这个最小运算集的思想本身就是数据库理论基础里很重要的一部分。接下来的内容我会先用一套固定的示例数据把这六种基本运算挨个讲透再把连接和除法单独拿出来重点讲因为它们在实际查询中最常见也最容易出错。1.3 本文统一使用的示例数据为了让后面的例题有连贯性这里先定义三张表都是我实际学习时用的经典选课模型Student学生表SID学号SName姓名SAge年龄SDept院系Course课程表CID课程号CName课程名CCredit学分SC选课表SID学号CID课程号Score成绩比如 Student 表里存着 (1, 张三, 20, 计算机) 这样的行SC 表里存着 (1, C01, 88) 这样的行。下面所有运算我都会用这几张表来演示结果你可以在纸上手动画一画比直接看公式爽得多。2. 六种基本运算所有复杂查询的原语2.1 选择运算SelectionWHERE子句的灵魂选择运算用希腊字母 σ 表示它的作用是从一个关系表中筛选出满足给定条件的元组行。形式化写出来是σ_条件(表名)。比如我想找出 Student 表中计算机系、年龄大于20岁的学生关系代数写出来就是σ_SDept计算机 AND SAge20(Student)在SQL里对应的是SELECT * FROM Student WHERE SDept 计算机 AND SAge 20;这个运算是不是看起来平平无奇但它有几个细节值得说一下。第一选择运算是行级过滤它只删行不删列。所以它的结果表结构和原表一模一样属性个数不变。理解这一点对你后续理解投影、连接很重要因为每类运算对表结构的影响都不一样。第二选择条件里可以用 AND、OR、NOT 组合多个判断在关系代数里就写成逻辑表达式的形式。这里有个容易犯糊涂的地方σ 下标里的等号是判断相等还是赋值关系代数里它只有判断相等的含义不会改变原表的任何数据。第三从集合论的角度看选择就是一个取子集的动作从全集里筛出满足条件的子集。它在整个关系代数体系里充当的是过滤闸门的角色。实际写SQL时WHERE子句之所以能执行得很快很大程度上依赖索引而在关系代数层面上它就是一次 σ 操作。所以当你在EXPLAIN里看到Filter或Index Range Scan这样的词本质就是优化器把 σ 下推到离数据源更近的位置去执行了。2.2 投影运算ProjectionSELECT列裁剪以及一个经典陷阱投影运算用希腊字母 π 表示它的作用是筛选出指定的列并且自动去除重复行。比如我要查所有学生的姓名和院系关系代数写为π_SName, SDept(Student)在SQL里对应SELECT DISTINCT SName, SDept FROM Student;注意这里用了 DISTINCT因为关系代数里投影的结果是一个集合集合天然不允许重复元素存在。这一点和SQL默认行为非常不一样SQL里 SELECT SName, SDept FROM Student 时如果两个学生同名且同院系结果会出现两行一模一样的记录因为SQL处理的是多重集合bag默认不去重。但如果用关系代数视角π 的结果集必须去重。这个差异在面试里经常被拿出来考也容易在实际优化中引起困惑。比如有同学分析一条SQL时觉得明明该去重却忘了加 DISTINCT结果就拿到重复数据。理解多维建模的人都知道关系代数去重的语义更接近集合而SQL更接近袋子。实际开发中你要清楚地记得关系代数帮你理解原理但写SQL时去不去重得自己拿主意。投影还有一个容易忽略的点它在裁剪列的同时如果结果出现了重复行它会顺手把重复行消掉。所以从执行成本上看一个 π 操作并不像看起来那么便宜它背后隐含了对整个结果集排序或哈希去重的开销。因此当执行计划中出现 Unique 或 HashAggregate 时往往意味着优化器在处理投影或集合运算的去重逻辑。2.3 集合运算并、差、笛卡尔积的底层逻辑把并∪、差−、笛卡尔积×放在一起讲是因为它们都来自集合论的基本概念而且都遵循一个共同的前提两个参与运算的关系必须是并相容的union-compatible也就是两个表的列数相同并且对应列的类型域一致。拿并运算举例Student 表和一个临时的新生表NewStudent 结构完全一样我想把新生合并进老生名单在关系代数里就是Student ∪ NewStudent对应SQLSELECT * FROM Student UNION SELECT * FROM NewStudent;这里的核心是 UNION 自带去重。如果两个表里有重复的行UNION 只保留一份。如果你希望保留全部重复行SQL里得改用 UNION ALL这一点在关系代数里没有对应物因为集合语意天然不允许重复。差运算用 − 表示。比如我想找计算机系学生中有哪些是年龄大于20的本质是先取计算机系全体再减去年龄不大于20的那部分。关系代数这样写σ_SDept计算机(Student) − σ_SAge20(Student)对应SQLSELECT * FROM Student WHERE SDept 计算机 EXCEPT SELECT * FROM Student WHERE SAge 20;注意MySQL 8.0.31 之前没有 EXCEPT 关键字你得用 NOT EXISTS 或 LEFT JOIN 来模拟。这也是为什么很多从MySQL入门的朋友看到这里会觉得很陌生。没关系你只要理解差运算的集合语义是A中有、B中无用任何工具都能改写出来。笛卡尔积 × 则是所有集合运算里最容易引发事故的一个。两个表做笛卡尔积意味着A表的每一行都会和B表的每一行组合一次。假设 Student 有1000行Course 有50行那么 Student × Course 就会产生 50000 行。写SQL时如果你忘了写JOIN条件实际执行的往往就是一次笛卡尔积这也是很多慢SQL查询的直接原因。我见过一次线上事故一条本来该关联两张表的查询因为关联条件被误删结果一张 10 万行的表和一张 8 万行的表做了一次跨连接中间结果瞬间膨胀到 80 亿行直接把临时表空间写满了。所以在实际工程里笛卡尔积这个操作的基本盘要非常警觉。关系代数里它是个基础运算符但工程上它是危险的猛兽。2.4 更名运算Rename自连接与别名的底层依据更名运算用 ρ 表示功能是给关系或者属性重新起名字。比如我把 Student 表重命名为 S并给几个属性起新的名字可以写成ρ_S(SID1, SName1)(Student)对应SQL就是给表起别名SELECT s.SID AS SID1, s.SName AS SName1 FROM Student AS s;很多人写自连接时隐约知道要给表起别名但不知道为什么。实际上关系代数里的更名运算就是为了解决同一个关系需要以不同身份参与运算的问题。比如我想查年龄比张三大的学生在SQL里最常见的写法是自连接SELECT a.SName FROM Student a JOIN Student b ON b.SName 张三 WHERE a.SAge b.SAge;这里 a、b 就是 Student 关系经过 ρ 运算产生的两个拷贝。数据库底层并不会真的复制两份表但逻辑语义上这个查询就是两个 Student 关系的笛卡尔积再做选择。如果你不理解这一点自连接写起来就会总觉得别扭偶尔还会忘了搞清楚哪个实例是基准表、哪个实例是对照表。3. 连接运算JOIN家族的前世今生3.1 θ连接、等值连接、自然连接的区别连接运算是关系代数里使用频率最高的操作也是很多开发者最熟悉的 SQL JOIN 的理论来源。它的定义很简洁两个关系做笛卡尔积再根据条件做选择。也就是R ⨝_θ S σ_θ(R × S)这看起来就是先跨接再过滤。但你别小看这个定义θ连接、等值连接、自然连接三个概念全是从这里衍生出来的。θ连接就是连接条件是一个任意比较表达式比如 R.A S.B、R.A S.B 都行。等值连接就是 θ 取等号的情况比如 SC.SID Student.SID。这是内连接最常见的形式SQL里的 JOIN ... ON 加上等号条件完全对应。自然连接则更进一步它要求两个关系在同名的属性上做等值匹配并且合并掉重复的列。表达式直接写成 R ⨝ S不需要写条件。自然连接隐含了两个动作先按所有同名属性做等值连接然后把重复的那个属性列去掉。举个例子Student 和 SC 都有 SID 这一列那么自然连接Student ⨝ SC得到的结果列是SID、SName、SAge、SDept、CID、Score。这里SID只出现一次因为自然连接自动合并了同名列。对应的SQL写法是SELECT Student.SID, Student.SName, Student.SAge, Student.SDept, SC.CID, SC.Score FROM Student JOIN SC ON Student.SID SC.SID;3.2 为什么实际写SQL时很少用自然连接我上课时老师特别强调考试时自然连接写得爽但工程上几乎不用自然连接。原因很简单自然连接自动匹配所有同名属性这个隐式行为非常危险。举个例子如果两张表里除了业务关联字段外恰巧都有一个备注字段叫 Remark那么自然连接会额外要求 Remark 也相等导致你本想让 SID 相等的行关联起来结果因为备注不一样大量行被过滤掉。最坑的是这个问题不会报错你只会拿到一份看起来没什么问题但数据少了很多的结果。另一个常见的坑是自然连接的结果列名是合并后的如果你在查询中使用 SELECT *很容易分不清某个同名字段到底来自哪张表导致后续程序取数据时发生错乱。所以真实项目里写SQL我强烈建议使用显式的 JOIN ... ON把连接条件明确写出来。宁可多敲几个字符也不要让隐式逻辑替你猜。顺带一提数据库理论里的连接家族还有外连接左外、右外、全外。纯粹的关系代数八大运算符里其实没有外连接它是SQL为了实用性扩展出来的运算。如果你非要用关系代数来表达左外连接可以写成R ⨝_L S (R ⨝ S) ∪ ((R − π_R(R ⨝ S)) × {(NULL, NULL, ...)})这个公式看着吓人本质意思就一句话把内连接的结果并上左边表那些没匹配上的行与右边全NULL的一行的组合。理解这个思想就够了实际写SQL时你直接用 LEFT JOIN数据库内部会自动处理这些逻辑。3.3 从连接推导看JOIN性能先过滤再连接永远没错连接运算的定义是 R × S 再 σ这会给很多初学者一个错误暗示做连接就要先做一次巨大的笛卡尔积。但真实数据库绝对不会这么干优化器会把过滤条件下推到连接之前执行也就是先把 R 和 S 各自用 σ 缩小再做连接。这在关系代数里是有严格依据的因为选择运算在连接运算上满足分配律。形式化地说σ_条件(R ⨝ S) σ_条件(R) ⨝ S条件只涉及R中的属性这个等价关系意味着只要连接条件只涉及R的属性你就可以提前把R过滤掉连接成本会大幅下降。放在SQL里就是先加WHERE把能过滤的数据尽量收窄再JOIN。这个道理很多人都听过但不知道原理。现在你知道了它本质上是关系代数的代数变换规则。我曾经接手过一个非常慢的报表查询代码里先JOIN了四张表然后在最外层WHERE里做过滤结果每次跑都要二十分钟。我根据这个下推原则把过滤条件分别挪到每张子查询内部改造之后跑起来只要不到三秒。优化的原理说穿了就是让每个连接操作的对象变小。你在写SQL时如果始终保持先缩小表再JOIN的习惯很多性能问题根本不会出现。4. 除法运算解决全部/至少类查询的唯一数学工具4.1 除法的定义和本质关系除法Division是八大运算符里最抽象、最劝退的一个因为它的定义很绕。设关系 R 的属性和关系 S 的属性有交集那么 R ÷ S 的结果是一个新的关系它包含的元组要满足与 S 中的每一个元组组合后都出现在 R 中。说人话就是A ÷ B 的结果是那些对应了B里所有东西的A的部分。举例是最快的理解方式。假设我们要查哪些学生选修了全部课程。这里 R 可以看作 SC 选课关系包含SID和CIDS 看作 Course 里的全部课程CID。那么π_SID, CID(SC) ÷ π_CID(Course)这个除法的结果就是在SC表里每一个SID对应的CID集合都覆盖了Course表里所有CID。翻译成人话就是这些学生的选课记录包含了所有课程。用集合的观点想除法结果的每一个元组在R里对应的所有组合必须覆盖S的全部元组。比如学生1选了C01、C02、C03而课程表里恰好只有这三门课那学生1就在除法结果里。4.2 完整SQL实现双重 NOT EXISTS关系代数里的除法在SQL里最标准的翻译是双重否定写法。逻辑是找出那些不存在任何一门课程是他没选过的学生。SELECT SID FROM SC s1 WHERE NOT EXISTS ( SELECT 1 FROM Course c WHERE NOT EXISTS ( SELECT 1 FROM SC s2 WHERE s2.SID s1.SID AND s2.CID c.CID ) );我第一次看懂这个SQL的时候愣了好久。这里的关键在于两层 NOT EXISTS 的嵌套逻辑最内层判断某门课程是否存在该学生的选课记录。外层遍历所有课程只要有一门课程该学生没选过NOT EXISTS 就会把它筛掉。最外层只要存在没选的课程这个学生就不出现在结果里。这样绕了两层不存在最后剩下的就是所有课程都不存在没选的学生也就是选了全部课程的学生。如果你觉得双重 NOT EXISTS 不好记也可以用 GROUP BY 加 HAVING 实现前提是你知道课程总数SELECT SID FROM SC GROUP BY SID HAVING COUNT(DISTINCT CID) (SELECT COUNT(*) FROM Course);这种写法在课程总数固定时很好用但如果课程表会动态变化双重 NOT EXISTS 更稳。面试时建议两种写法都掌握原因各不相同HAVING 写法理解简单、执行也可能更快双层 NOT EXISTS 是除法语义的标准翻译能在没有聚合函数只有集合运算的理论环境下通吃。5. 关系代数与SQL的映射以及优化启示5.1 把SQL翻译成关系代数的三步法很多人在学习关系代数时最容易忽略的一点是它不只是理论还能反过来帮我们写SQL。当你面对一个复杂的业务查询脑袋里一团乱麻时可以先用关系代数搭骨架再翻译成SQL。我自己的习惯是三步走。第一步把业务需求转成关系表达式的骨架。比如需求是查计算机系学生的姓名及其所选课程的课程名那骨架就是先选、再连、再投影π_SName, CName( (σ_SDept计算机(Student)) ⨝ SC ⨝ Course )第二步根据骨架确定SQL的主干结构。先做FROM和JOIN再做WHERE过滤最后SELECT列。上面的表达式落到SQL就是SELECT s.SName, c.CName FROM Student s JOIN SC sc ON s.SID sc.SID JOIN Course c ON sc.CID c.CID WHERE s.SDept 计算机;第三步检查返回结果确认列裁剪和去重语义。如果关系代数里用了 πSQL里别忘了考虑是否需要 DISTINCT尤其是两表连接可能产生重复行时。这个三步法看起来很朴素但它特别能治硬憋SQL的毛病。遇到需求的脑子蒙蒙的时候先在纸上画关系表达式画完再转SQL出错率会大幅下降。我带过不少新人发现他们写复杂查询时最大的问题不是不会语法而是没有一个从需求到查询的推导路径经常凭感觉写写完再对着数据一个劲地试错。关系代数恰好提供了这条推导路径。5.2 优化器在做什么代数等价变换数据库的查询优化器之所以能优化SQL底层靠的就是关系代数的等价变换规则。优化器拿到SQL解析成代数树之后会在上面做一系列保结果的变形最终选择它认为成本最低的那一棵树去执行。最常见的等价变换有这么几个选择下推把 σ 尽可能往树下层移动让数据更早被过滤。投影下推把 π 尽可能下移让中间结果只保留必要的列。连接顺序交换利用连接的结合律和交换律重排表的连接顺序让小表先参与连接减少中间结果大小。举个例子我优化过一个查询原始SQL是这样的SELECT s.SName, c.CName FROM Student s JOIN SC sc ON s.SID sc.SID JOIN Course c ON c.CID sc.CID WHERE s.SDept 计算机;表面看没什么问题但如果 Student 表特别大数据库先做了连接再过滤性能就很差。用选择下推的思路等价改写成SELECT s.SName, c.CName FROM (SELECT * FROM Student WHERE SDept 计算机) s JOIN SC sc ON s.SID sc.SID JOIN Course c ON c.CID sc.CID;这个改写让第一步连接只处理计算机系学生而不是全部学生中间结果会小很多。在关系代数层面这个改写就是利用了 σ 下推的等价规则。实际生产里优化器很多时候会自己做到这一步但遇到特别复杂的查询或者提示器统计信息不准的时候手动的等价改写仍然是DBA调优的重要手段。5.3 EXPLAIN里的关系代数影子你把一段SQL丢到数据库里执行加一个 EXPLAIN 关键字就能看到执行计划。那些执行计划里的操作名比如 Seq Scan、Index Scan、Nested Loop、Hash Join、Filter看起来五花八门但本质上底层都是把关系代数树翻译成物理操作符后的产物。比如 Nested Loop Join对应的关系代数逻辑就是笛卡尔积加选择只是数据库在物理层用一个循环嵌套的方式实现罢了。Hash Join 则是用哈希表加速了连接匹配过程但它的逻辑语义仍然对应 θ 连接。Filter 操作对应的就是 σ。ProjectSet、Unique 等操作对应的则是 π 和集合运算的去重。我见过太多开发同学把 EXPLAIN 当慢SQL检查工具来用只会看有没有 Index Scan一旦看到树形结构就不知道怎么分析了。其实你只要抓住一点执行计划里每个操作的输出列和过滤条件都对应了某棵关系代数子树。顺着运算树走你就能在头脑中重建出这条SQL是从哪一步开始变慢的。6. 常见问题与实操心得从理论到落地的几个坑6.1 关系代数的集合和SQL的多重集合并不是一回事前面提到过关系代数里所有运算都基于集合语义结果不允许有重复行但SQL是多重集合语义默认保留重复行。这种理论语义和实际语义的偏差是很多实际问题的根源。举个例子你想用 UNION 合并两张表但忘了它其实对应的是 ∪ 运算结果因为SQL默认的行为是 DISTINCT 去重导致最终行数比自己预想的少。反过来如果你想让两个查询的结果合并并且保留重复行却写了 UNION ALL这时你其实是在绕过集合语义回到了多重集合语义。我自己的经验是心里始终要有一根弦关系代数和SQL在去重这件事上是有差别的。你不需要强制自己站在哪一边但要能在两者之间自由切换。分析应该是什么结果时用关系代数的集合视角分析SQL实际跑了多少行时用多重集合视角。6.2 自然连接和自连接的两大经典事故自然连接的坑前面说过了这里再说一个真实场景我和同事联调一个报表两边各写了一段代码中间通过一张中间表交互。我这边JOIN中间表时用了 USING(sid)同事那边也用了 USING(sid)结果两边以为同步的是业务主键实际上中间表还有个 hidden 列也叫 sid导致数据匹配完全错乱。查了两天才发现。这个锅虽然不能说全是自然连接的但 USING 和 NATURAL JOIN 这类隐式匹配同名列的语法遇到表结构变更时非常容易产生意外行为。从那以后我给自己定了条规矩所有JOIN必须显式写 ON 条件所有连接键必须指明来源表。自连接的事故则容易发生在忘了区分实例的时候。比如想查同一门课上成绩比自己高的人结果写JOIN条件时写反了a和b查出的人关系正好反了。这种错误SQL不会报错但结果错得离谱很考验数据敏感性。解决办法是写自连接时先明确基准行和比较行最好在草稿上画一下两个实例再写ON条件。6.3 关于除法、慢查询与关系代数的一点个人体会除法运算是我当年学关系代数时最受挫的部分因为教材例题少老师也是照本宣科。但后来我在实际业务里遇到找出完成了某项目所有任务的人找出了解某产品线全部特性的文档这类需求时才发现除法模型太有用了。只要两个表之间存在一个实体对应另一组实体的包含关系你就可以尝试用除法来建模。这种反向思维也正是关系代数给我最大的启发。多数人写SQL是正向的先JOIN再过滤但像选修了全部课程这种问题正向想根本无从下手只有反过来想不存在没选的课才能解决。关系代数的运算体系迫使你从集合变换的角度审题这比死记SQL语法有用得多。最后再分享一个小技巧分析复杂SQL时我习惯先在纸上把涉及的表、主外键关系、过滤条件画成一张小图然后用关系代数运算符把逻辑串起来。很多看起来很头痛的需求走一遍这套流程之后会变得异常清晰。关系代数这个工具初学觉得多余工作几年回头看它真的是数据库这条路上最值得花时间的基础功。