“关系代数”这四个字大概是数据库原理课睡眠率最高的部分。希腊字母、集合符号、抽象运算定义当年背完就忘工作后更觉得“直接写SQL就好了”。但我在线上排查过一个慢查询优化器把三层子查询拆成了笛卡尔积连接那一刻才彻底明白数据库引擎内部跑的就是关系代数。这篇文章就聚焦关系代数里的两个关键运算——投影运算和外连接运算。投影解决“取哪些列、怎么去掉重复”的问题外连接解决“连接时是否保留未匹配行”的问题。我会用自然连接做参照配合一个完整的订单业务案例把两者的语义、用途、关键特性和SQL落地方式挨个讲透。适合谁看写过SQL但没系统学过关系代数的开发者经常和报表、数据看板打交道、对JOIN结果心存疑虑的分析师准备技术面试、想弄清“关系代数到底怎么映射SQL”的候选人。1. 关系代数为什么值得重新学一遍1.1 关系代数是SQL的“中间表示”一条SQL语句并不是数据库直接照着执行的。优化的第一步是把SQL解析成语法树再转成关系代数表达式接着在表达式上做等价改写把WHERE条件下推到表扫描之前把SELECT的投影列裁剪掉不需要的字段把多表连接重排顺序以减少中间结果集。这些动作全部发生在关系代数层。等操作顺序确定之后才决定用哪种物理算法去执行进入执行计划阶段。你可以把关系代数想象成“查询配方”SQL是配方的口语表达执行计划是厨房里的实际操作。配方里写着先投影还是先选择、左连接还是内连接、连接条件落在哪个属性上这些都直接决定了最终端上桌的菜长什么样。所以我遇到复杂SQL时习惯先在草稿纸上写出等价的关系代数表达式再判断问题出在哪一步而不是盯着几十行的SQL干瞪眼。1.2 投影和外连接为什么最值得单独研究关系代数基本运算符里选择和投影最常用连接最昂贵但为什么单独把投影和外连接拎出来讲因为这两个运算在集合语义上都容易让人摔跤。投影会改变关系的列结构和去重行为外连接则改变了连接结果对未匹配行的“包含关系”两者叠加后结果集的行数和NULL分布都会出现反直觉变化。举一个我见过的真实场景用户表有100万行订单表有50万行。用户表左外连接订单表结果行数绝对不是100万而是100万加上“一对多”扩展出来的所有订单行。如果这时候再做投影去重、聚合统计很容易出现用户数重复统计、金额翻倍之类的事故。理解这两个运算等于给这类报表问题上了保险。2. 投影运算的语义与SQL落地2.1 投影的本质是垂直切表投影运算是从关系里选取若干列生成一个新关系记作π_列名列表(表)。比如有一张员工表EmployeeIDNameDeptIDSalary1张伟D1100002李娜D212000执行π_Name, DeptID(员工表)会得到NameDeptID张伟D1李娜D2它在逻辑上相当于“垂直切表”只保留下指定列其他列全部丢掉。投影不改变行的总数它改变的是关系的“宽度”。从数据库执行来看投影直接影响扫描阶段要读取的字段。如果你只需要两列而这两列恰好都在某个二级索引里数据库可以只扫索引不回表这就是后面会提到的“覆盖索引”。2.2 集合语义为什么投影自动去重而SELECT不去重关系代数研究的是“关系”关系在集合论中是一个集合集合不允许重复元素。所以π_A(B)的结果里相同行只出现一次。这个性质在理论上很干净但在SQL里却出现了偏差SQL的SELECT默认返回的是多重集合允许重复行。也就是说SELECT Name, DeptID FROM 员工表并不保证去重只有加DISTINCT才和关系代数投影完全等价。这是初学者最容易踩的坑以为SELECT就是投影实际它更像是“扩展投影加重复保留”。举一个实际例子。订单表里想查“有哪些用户下过单”关系代数写π_UserID(Orders)每个用户只出现一次。翻译成SQL如果直接写SELECT UserID FROM Orders下过10单的用户会出现10次后续聚合全不对。正确对应是SELECT DISTINCT UserID FROM Orders。反过来如果你要的是“每个订单和它的用户ID”明细那绝不能加DISTINCT否则多个相同用户ID的订单会被合并订单维度就丢了。2.3 三种投影写法的对应关系在实际SQL里投影有三种常见映射形态弄清楚就不会乱了。关系代数写法SQL写法是否去重π_A,B(R)SELECT DISTINCT A, B FROM R是扩展投影 π_{F1,F2}(R)SELECT expr1 AS F1, expr2 AS F2 FROM R否π_A,B(σ_条件(R))SELECT DISTINCT A, B FROM R WHERE 条件是为什么SQL要引入“扩展投影”因为业务中经常需要计算列比如年薪等于月薪乘12、从时间字段里提取年份、拼接姓名等这些都不是单纯的列裁剪而是“生成新列”。关系代数的原始投影只有列名不支持表达式后来实际应用中才扩展出了表达式投影能力。理解这个演变你就能明白为什么优化器有时无法把SELECT *优化成只取需要的列表达式、函数和不确定行数都会增加改写的难度。2.4 投影在实践中要避开的三个坑第一个坑DISTINCT用错对象。SELECT DISTINCT UserID, OrderID是对“UserID和OrderID的整体组合”去重不是对 UserID 单独去重。如果你只想看 UserID 的所有可能取值要写SELECT DISTINCT UserID FROM Orders而不是把 OrderID 也带上去重。第二个坑忽略去重代价。做DISTINCT需要排序或哈希去重对百万级、亿级表来说成本很高。很多场景业务上根本不需要去重加上DISTINCT只是心里舒服查询却慢了几十倍。我见过有人对“两表连接后天然不会重复”的结果强行加DISTINCT纯属浪费。先想清楚业务语义再决定要不要去重。第三个坑投影列过宽。SELECT *会把所有列都捞出来哪怕外层只用一个字段。这既浪费I/O和网络带宽又压缩了覆盖索引的发挥空间。在复杂查询里尽量在源头子查询就把列裁剪好给优化器“投影下推”留空间。3. 外连接究竟在干什么3.1 连接运算的基本模型笛卡尔积加选择连接可以这样理解先把左边每行和右边每行做笛卡尔积然后用连接条件判断哪些组合成立即R ⋈_C S σ_C(R × S)。如果两张表分别有m行和n行笛卡尔积会产生m×n行连接条件筛掉不满足的剩下的就是结果。数据库真实执行时当然不会物化完整笛卡尔积它会在扫描过程中利用索引、哈希结构直接跳过大量不可能匹配的组合。但逻辑模型仍然很有价值连接条件决定哪些行配对成功从而决定结果行数。如果你写ON a.user_id b.user_id配对粒度就是用户维度如果漏写条件就是全笛卡尔积结果行数爆炸。很多慢SQL的根源其实就是这种非预期笛卡尔积。3.2 自然连接简洁背后的“隐式条件”自然连接是等值连接的一种简化写法自动把两个关系中同名的所有列做等值比较结果中同名列只保留一份。比如Users(CityID)和Cities(CityID)自然连接就等于按CityID等值连接输出列里只出现一个CityID再附带其他列。自然连接看起来很简洁前提是两张表设计规范、命名统一、重名字段的语义确实是该连接的键。问题在于现实世界里的表是多个版本叠加出来的用户表有status订单表也有status如果都用NATURAL JOIN数据库会把status也拿去自动匹配等于额外加了一条user.status order.status条件结果行数会少很多甚至为空。这种错误出现概率不低而且特别难排查因为SQL看起来“很干净”。所以我在生产环境几乎不用NATURAL JOIN看到同名列就手动写在ON后面。连接是业务意图应该显式表达而不是让数据库猜。3.3 三种外连接保留未匹配行的语义扩展标准内连接只保留匹配成功的行两边没配上的行会被丢弃。但很多业务需求是“没配上也要保留”订单和退款有的订单没有退款记录用户和订单有的用户从没下过单城市和订单有的城市没有任何交易。这时候就需要外连接。左外连接保留左侧所有行右侧没有匹配就补NULL。右外连接保留右侧所有行左侧没有匹配就补NULL。全外连接保留两侧所有行各自没有匹配的就补NULL。最典型的例子订单表Orders和退款表Refunds。要查“所有订单是否有退款”用Orders LEFT JOIN Refunds没有退款的订单退款字段就是NULL。再进一步如果想筛出“没有退款记录的订单”直接在外连接结果上加WHERE Refunds.OrderID IS NULL即可。这个写法初看有点反直觉但非常常用属于外连接的招牌用法。4. 自然连接与外连接的核心差异4.1 一张表看清差异把自然连接和代表外连接的左外连接放在同一张表里对比语义差异一望便知对比维度自然连接左外连接外连接代表匹配依据自动匹配所有同名同值列必须在 ON/USING 中显式指定结果行数仅保留匹配成功的行保留左表所有行未匹配补 NULL同名列处理自动合并为一列通过表名或别名区分显示语义定位内连接语义保留未匹配行的扩展语义业务风险同名不同义时静默出错条件可控问题容易定位SQL写法NATURAL JOIN / USINGLEFT/RIGHT/FULL OUTER JOIN从行数角度理解自然连接的结果行数不会超过左表和右表中“能匹配上”的行的组合数左外连接的结果行数至少包含左表的全部行再加上一对多匹配产生的扩展行。外连接通常比内连接多出那些“未匹配行”这一点直接决定了报表聚合时要不要用DISTINCT。4.2 生产库我为什么不推荐自然连接自然连接并非一无是处。在列名体系极其规范、同名列语义完全一致的数据仓库中它可以少写不少连接条件。但业务系统数据库往往做不到。原因是业务表结构天然会演进用户表加了source订单表也加了source一个表示注册渠道一个表示下单渠道。如果哪天有人图省事把JOIN改成NATURAL JOIN优化器就会自动把source也变成等值条件用户来自微信、订单来自抖音的行全部配不上结果表行数骤减。线上排查这类问题非常痛苦因为语句不长、不报错只有最后数据对不上时才暴露。我自己的原则是“三个显式”连接条件显式写、连接类型显式写、过滤字段显式写。宁可多敲几个字符也不要让系统去猜连接意图。4.3 外连接报表与对账场景中的刚需外连接在两种业务场景中不可替代。第一类是“补全维度”。要统计每个城市的用户数和订单金额城市是维度表用户和订单是事实表。内连接会把没有用户的空城市直接丢掉而报表要求所有城市都出现哪怕数字是0。这时只能以城市为左表逐级左外连接用户、订单再聚合。这就是典型的“外连接做维度补全”。第二类是“寻找缺失”。比如找出从未下过单的用户、找出没有退款的订单、找出有入账但没有出账的对账单。推荐写法有两种一是外连接加IS NULL过滤二是NOT EXISTS子查询。在关系代数层面前者正是外连接引入NULL后产生的新能力语义非常清晰。5. 实战订单场景中投影与外连接的组合运用5.1 表结构与业务前提造三张能说明问题又不啰嗦的表Users(UserID, UserName, CityID)用户表一个用户属于一个城市。Cities(CityID, CityName)城市表城市维度。Orders(OrderID, UserID, Amount, Status)订单表Status只有SUCCESS和CANCELED。业务特点一个城市可以有很多用户一个用户可以下很多订单所以从城市到订单是典型的一对多关系。这个结构覆盖了投影、内连接、外连接、聚合所有关键点做演示很合适。5.2 需求1查询每个用户的用户名和所在城市名称关系代数写法很简洁π_UserName, CityName(Users ⋈ Cities)。这里用自然连接因为Users和Cities共有CityID列语义就是要按城市ID匹配。翻译成SQLSELECT u.UserName, c.CityName FROM Users u JOIN Cities c ON u.CityID c.CityID;注意我没有写成NATURAL JOIN。虽然当前表结构只有CityID一个同名列用NATURAL JOIN没问题但换个系统就不一定。用ON显式声明后读代码的人一眼知道是按城市ID连接不会被未来的同名字段坑到。5.3 需求2查询所有用户以及他们的订单信息如果只写内连接Users JOIN Orders ON ...没下过单的用户会整个消失。业务要求“所有用户”哪怕没有订单也要出现所以用左外连接SELECT u.UserID, u.UserName, o.OrderID, o.Amount FROM Users u LEFT JOIN Orders o ON u.UserID o.UserID;关系代数表达式可以写作π_UserID, UserName, OrderID, Amount(Users ⟕ Orders)其中⟕表示左外连接。特别注意结果集中的OrderID和Amount允许为NULL。展示时可以用COALESCE(Amount, 0)做页面展示但生产统计里不要随便把 NULL 变成 0因为 NULL 和 0 的业务含义不同NULL 表示“没有订单”0 可能被误读为“金额为零的订单”。结果行数规则也值得记一下有多少个用户至少就有多少行每个用户有n张订单就会额外贡献n-1行。5.4 需求3统计每个城市的用户数和成功订单金额这是把外连接和聚合结合起来的经典报表需求SQL如下SELECT c.CityName, COUNT(DISTINCT u.UserID) AS user_cnt, COALESCE(SUM(o.Amount), 0) AS success_amount FROM Cities c LEFT JOIN Users u ON u.CityID c.CityID LEFT JOIN Orders o ON u.UserID o.UserID AND o.Status SUCCESS GROUP BY c.CityName;这里有三个关键点。第一城市表必须是左表否则没有用户的空城市不会出现在结果里。第二第一个LEFT JOIN之后一个城市会展开成多行用户第二个LEFT JOIN之后一个用户又会展开成多行订单。如果直接COUNT(u.UserID)同一个用户有多张订单时会被重复计数所以必须用COUNT(DISTINCT u.UserID)或者先做用户维度预聚合再连接。这是我反复见过的报表错误十次里有八次数据对不上都是这个原因。第三o.Status SUCCESS放在ON条件里而不是WHERE里。如果放进WHERE那么没有成功订单的用户其o.Status是 NULLNULL SUCCESS为 UNKNOWN整行会被过滤左外连接“保留左侧全量”的语义就被破坏了。5.5 关系代数表达式的完整推导为了更贴近关系代数我把上面的SQL一步步拆开。第一步城市和用户左外连接得到“城市-用户”明细T1 Cities ⟕ Users ON Cities.CityID Users.CityID。第二步先对订单做选择只留成功订单再左外连接到T1T2 T1 ⟕ (σ_StatusSUCCESS(Orders)) ON T1.UserID Orders.UserID。先选择再连接可以保证被过滤的订单不会把左侧的行整个滤掉。第三步分组聚合。关系代数中常用G表示分组聚合把分组列和聚合函数写在下标里T3 G_CityName, COUNT(DISTINCT UserID), SUM(Amount)(T2)。最后投影出展示列Result π_CityName, user_cnt, success_amount(T3)。可以看到SQL里每个FROM顺序、每个JOIN条件、每个聚合列都能对应到关系代数的一步。遇到复杂SQL先画这样的推导再回去看执行计划很多“为什么多一行、少一行”的问题就清楚了。6. 执行计划视角数据库怎么执行投影和外连接6.1 投影下推与索引覆盖查询优化器有个经典优化叫“投影下推”把SELECT需要的列信息尽可能下推到扫描阶段让表扫描只读取必要字段减少行宽和中间结果集。如果你在子查询里写SELECT *外层再裁列优化器往往没法跨层裁剪数据被迫先全量带出来再丢弃。充分利用这一点的实践是设计“覆盖索引”。比如经常查UserID和Amount可以建(UserID, Amount)索引当查询SELECT UserID, Amount FROM Orders WHERE UserID ?时数据库直接在索引里拿到两列不需要回表。理解投影你就理解“查询需要哪些列”怎样直接影响索引设计。6.2 三种连接算法怎么选数据库执行连接常见有三种算法。嵌套循环连接适合小表驱动大表、且连接列有索引的情况每次拿驱动表一行去被驱动表里找匹配。哈希连接适合两个大表做等值连接先在内存里建哈希表再探测。合并连接适合两边数据已按连接列排好序的情况比如连接列上有索引扫描时像拉链一样顺序匹配。这些算法复杂度各不相同但共同点是连接列上有索引或者两边数据量小才能跑得快。所以连接条件越明确、可用索引越好数据库越容易选出合适的算法。外连接通常会限定驱动表顺序如果被驱动表上缺索引容易变成逐行扫描性能会明显劣化。6.3 外连接在优化器眼中的“限制”外连接和普通连接在优化上有一个重要区别外连接左右顺序的语义是固定的优化器不能随意交换左右表来降低中间结果集大小。比如A LEFT JOIN BA必须保留全部行不能临时改成B RIGHT JOIN A去优化扫描顺序这会让执行计划的可选空间变小。所以遇到大表外连接性能差时我会先检查两件事一是被驱动表的连接列有没有索引二是能不能通过预聚合缩小左侧表的数据量。很多情况下把左侧的过滤条件先执行、再外连接执行计划会好看很多。7. 常见问题与排查实录7.1 LEFT JOIN 被 WHERE 悄悄变成 INNER JOIN这是最常被问到的外连接问题。假设SQL写成了SELECT u.UserName, o.Amount FROM Users u LEFT JOIN Orders o ON u.UserID o.UserID WHERE o.Amount 100;表面看是左外连接但WHERE o.Amount 100会把o.Amount为NULL的行过滤掉而没有订单的用户正好o.Amount是NULL于是他们消失了左外连接实际变成了内连接。如果确实只想过滤订单金额大于100的订单同时保留所有用户请把金额条件放到ON里LEFT JOIN Orders o ON u.UserID o.UserID AND o.Amount 100;排查这类问题时先看 WHERE 里有没有引用右表字段有就得警惕。7.2 自然连接匹配到“同名不同义”的字段我遇到过一次事故用户表和订单表都新增了一个region字段一个是用户注册地区一个是订单归属仓。同事为了省事用了NATURAL JOIN结果region被自动拿来等值连接原本能配上的订单大量被过滤掉。排查了两天最后看执行计划时才发现连接条件比预期多了一条。教训已经写在前面连接条件必须显式生产库禁用NATURAL JOIN。这个例子不算极端任何两张业务表在演进中都可能撞出同名但不同义的字段。7.3 SELECT DISTINCT 让结果神秘变少某次报表需求是查“每天每个商品成交了多少订单”同事写的是SELECT DISTINCT order_date, product_id, COUNT(*) AS cnt FROM orders GROUP BY order_date, product_id;这里的DISTINCT是多余的它作用在整个分组结果上不会让计数变大或变小但会让数据库多一次去重。真正常见的“变少”是另一个版本把DISTINCT加在明细结果上但由于分组列和明细列混用合并掉了本不该合并的行。统计行数时一定要先确认去重对象是什么DISTINCT作用于整个行组合不是某一列。7.4 多表外连接导致 COUNT 翻倍订单明细和退款明细是多对多关联的典型。如果你把订单表 LEFT JOIN 退款表再按用户做 COUNT退款多几条订单行就被复制几遍用户订单数就虚高了。解决办法有两个先在退款表里按订单聚合再连接或者计算时使用COUNT(DISTINCT OrderID)去重统计。我更推荐前者因为明细聚合后再连接不会污染订单层其他指标比如金额求和。我在实际踩坑后形成的习惯是写任何 JOIN先问自己三个问题——连接条件的业务含义是什么这个连接会保留哪些行聚合时会不会被明细行放大如果答不上来就先写关系代数表达式再转成 SQL。这套习惯帮我在无数报表和查询里避免了很多莫名其妙的数据错误。关系代数不是考场里的符号游戏它是理解数据库行为、写出可控 SQL 的最底层语言。投影和外连接尤其如此搞清楚了这两个运算你再看 JOIN、看 DISTINCT、看聚合都会有庖丁解牛的感觉。