外观
05 - JOIN 为什么会把数据查重:从关系代数理解多表查询
摘要与阅读定位
订单金额在多表查询后翻倍,常见原因是同一条明细参与了多个匹配组合,随后被 SUM 重复累计。排查时先看每条明细在连接结果中出现几次,再决定如何汇总。本文从订单关联支付记录开始,解释一对多关联、多个子表造成的行数放大,以及只判断有无记录的存在性查询;过滤位置由 06展开,分组与窗口由 07展开。
- 前置:10 NULL 与三值逻辑,用于解释未匹配行和普通等号。
- 验证约定:A 数据库实验与证据验证方法。基线为 MySQL 8.0.16+ / 8.4、InnoDB;EXPLAIN ANALYZE 另需 8.0.18+。
- 本文结果表是语义推导的预期,不是性能证据。
初次先理解下面的两表关联,再执行第 2 节的初始化与原始行查询。第 3 节是金额修复主线,第 4—5 节补边界;第 6 节算法与实际执行计划是可选进阶,不需要先掌握算法名才能写对 JOIN。
从两张表开始:JOIN 是匹配记录,不是按位置拼接
JOIN 按条件把不同表的记录组合成结果行,ON 指定匹配条件。订单表每行是一笔订单,支付表每行是一笔支付流水。一笔订单可以对应多笔支付,这就是一对多关系。例如,订单 1 有支付 21、22、23,订单 2 有支付 24,订单 3 没有支付。
ON payment.order_id = orders.id 表示“支付的订单号等于订单主键才算匹配”,不是两张表第 1 行与第 1 行拼在一起。支付流水数不必等于订单数。
| 订单 | INNER JOIN 输出 | LEFT JOIN 输出 |
|---|---|---|
| 1 | 三行,分别关联支付 21、22、23 | 同样三行 |
| 2 | 一行,关联支付 24 | 同样一行;支付金额可以未知 |
| 3 | 没有匹配,不输出 | 一行,支付侧列为 NULL |
INNER JOIN(内连接)只保留匹配组合;LEFT JOIN(左外连接)还保留没有匹配的左侧记录,此时用 NULL 填充右侧列。NULL 表示缺失或未知,不能直接当成数值 0。有多个匹配时,两种连接都输出多个组合。因此“以订单表为左表”不等于“每笔订单必定只有一行”。第 2 节会用这个模型直接查询,再增加商品明细。
粒度指一行代表什么业务单位:连接前是“一笔订单”或“一笔支付”,连接后则是“一笔订单与一笔支付的匹配组合”。本文用连接基数描述一条输入记录能匹配多少条另一侧记录。求和前要先确认粒度,因为 SUM 累计的是结果行中的值,不会自动识别哪些值来自同一笔业务。
1. 现象:六行连接结果,不等于两个金额都放大六倍
一笔订单有两条商品明细和三条支付流水。两条明细各值 100,支付为 80、70、50:真实商品总额和支付总额都为 200。如果两个子表都只按订单号关联,每条明细就能匹配该订单的全部三条支付,直接连接产生 2 × 3 = 6 行。每条商品明细出现三次,每条支付出现两次,直接求和的错误结果分别是 600 和 400。六行组合满足连接条件,错误在于把组合中的重复金额当成独立金额累计。
关系代数用集合运算描述查询,例如按条件筛选记录、选取列和连接记录。但普通 SQL 查询允许重复行,这种允许重复元素的集合称为多重集;只选择几列也不会自动去重。因此,分析 SQL 结果时还要考虑重复行和 NULL。对左侧某一行,右侧匹配 N 行,本级 INNER JOIN 输出 N 行,LEFT JOIN 输出 max(1,N) 行;N 为 0 时,后者的一行由右侧补 NULL,不代表新增了一笔支付。
对于这里的等值关联,要靠右侧连接键的唯一约束保证每个左行至多有一个匹配,不能仅凭字段名或当前样本判断。唯一约束限制键值重复;外键约束子表引用的父记录是否合法,并不限制同一父记录被多少条子记录引用,所以不能保证一对一。一个 NULL 连接键用普通 = 不会匹配另一个 NULL。MySQL 的 <=> 是 NULL 安全等号,两个 NULL 用它比较会得到真;是否应这样匹配,必须由业务对关联身份的定义决定。
2. 实现:独立初始化和逆向样本
以下初始化建立独立的教学数据,并加入能暴露错误判断的边界样本。先提交或回滚所有实验事务,再执行初始化。DROP 仅作用于隔离库的 join_* 教学表;没有权限时由管理员预建隔离库,不要改为生产库。
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit=1;
DROP TABLE IF EXISTS join_payment;
DROP TABLE IF EXISTS join_item;
DROP TABLE IF EXISTS join_order;
CREATE TABLE join_order (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL
) ENGINE=InnoDB;
CREATE TABLE join_item (
id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
KEY idx_join_item_order (order_id)
) ENGINE=InnoDB;
CREATE TABLE join_payment (
id BIGINT PRIMARY KEY,
order_id BIGINT NULL,
amount DECIMAL(10,2) NULL,
KEY idx_join_payment_order (order_id)
) ENGINE=InnoDB;
INSERT INTO join_order VALUES (1,1001),(2,1002),(3,1003);
INSERT INTO join_item VALUES (11,1,1,100.00),(12,1,2,50.00);
INSERT INTO join_payment VALUES
(21,1,80.00),(22,1,70.00),(23,1,50.00),
(24,2,NULL),(25,999,10.00),(26,NULL,20.00);特意不建外键:999 是用于审查数据质量的孤儿记录,NULL 是未知关联键;并不是推荐生产取消外键。订单 2 有支付记录但金额未知,订单 3 完全没有支付记录,两者不能混为一谈。
先只关联订单和支付,核对上面的匹配模型:
sql
SELECT orders.id AS order_id, payment.id AS payment_id, payment.amount
FROM join_order AS orders
LEFT JOIN join_payment AS payment ON payment.order_id = orders.id
ORDER BY orders.id, payment.id;推导预期是五行:(1,21,80)、(1,22,70)、(1,23,50)、(2,24,NULL)、(3,NULL,NULL)。支付 25、26 没有匹配到这三笔订单,不出现在结果中。订单 2 与 3 都显示 NULL 金额,却只有订单 2 有支付记录。
再增加商品明细,先看原始组合,不急着求和:
sql
SELECT item.id AS item_id, payment.id AS payment_id,
item.quantity*item.price AS item_amount, payment.amount
FROM join_order AS orders
JOIN join_item AS item ON item.order_id=orders.id
JOIN join_payment AS payment ON payment.order_id=orders.id
ORDER BY orders.id,item.id,payment.id;预期配对:(11,21),(11,22),(11,23),(12,21),(12,22),(12,23),只有订单 1 留下。
sql
SELECT orders.id, COUNT(*) AS joined_rows,
SUM(item.quantity*item.price) AS item_amount,
SUM(payment.amount) AS paid_amount,
SUM(DISTINCT item.quantity*item.price) AS distinct_item_amount
FROM join_order AS orders
JOIN join_item AS item ON item.order_id=orders.id
JOIN join_payment AS payment ON payment.order_id=orders.id
GROUP BY orders.id;预期订单 1:joined_rows=6,item_amount=600,paid_amount=400,distinct_item_amount=100。SUM(DISTINCT ...) 先按金额值去重,再求和,把两条不同明细的合法金额 100 合并了,不能作为修复。SELECT DISTINCT 也只按选出的列去重;选取输出列称为投影。两条业务记录可能投影出相同金额,所以按值去重不能代替按业务身份确定统计粒度。
3. 正例:先把两个子表收敛到订单粒度
先分别按 order_id 汇总商品明细和支付流水,让每个子表的汇总结果都变成“每笔订单一行”,再关联订单表。这叫预聚合。两个汇总结果中,同一订单号各只有一行,关联时就不会再形成明细与支付之间的乘法组合。
下面 FROM 中的子查询结果称为派生表,可以作为新的表参与关联。COUNT(*) 统计记录数,COUNT(amount) 只统计金额非 NULL 的记录;SUM(amount) 忽略 NULL,但一组金额全为 NULL 时结果仍为 NULL。这些计数可以区分“没有支付”与“有支付但金额未知”。
sql
SELECT orders.id,
COALESCE(item.item_amount,0) AS item_amount,
COALESCE(payment.payment_rows,0) AS payment_rows,
COALESCE(payment.known_amount_rows,0) AS known_amount_rows,
payment.paid_amount
FROM join_order AS orders
LEFT JOIN (
SELECT order_id,SUM(quantity*price) AS item_amount
FROM join_item GROUP BY order_id
) AS item ON item.order_id=orders.id
LEFT JOIN (
SELECT order_id,COUNT(*) AS payment_rows,
COUNT(amount) AS known_amount_rows,SUM(amount) AS paid_amount
FROM join_payment GROUP BY order_id
) AS payment ON payment.order_id=orders.id
ORDER BY orders.id;预期输出:
| 订单 | 商品金额 | 支付记录数 | 已知金额记录数 | 支付金额 |
|---|---|---|---|---|
| 1 | 200 | 3 | 3 | 200 |
| 2 | 0 | 1 | 0 | NULL |
| 3 | 0 | 0 | 0 | NULL |
COALESCE 返回参数中第一个非 NULL 值,COALESCE(x,0) 因而把 x 的 NULL 显示为 0。这里将“没有商品明细”显示为 0 是展示约定;支付金额仍保留 NULL。订单 2 的支付记录数为 1、已知金额记录数为 0,订单 3 则都为 0。若把支付金额一律转换为 0,就会把未知金额和没有支付混为一谈,隐藏订单 2 的数据问题。
预聚合解决的是粒度正确性,不是万能性能优化。大子表可能先聚合很多无关订单;若查询仅选少量订单,需评估先限定目标订单再聚合的等价改写,见 14 执行计划。
4. 存在、未匹配与数据质量的区别
如果需求只是“找出有支付记录的订单”,不需要输出每笔支付。EXISTS 判断子查询是否返回至少一行;NOT EXISTS 判断是否一行也没有。下面前两条分别回答有无支付,第三条则检查金额是否为 NULL,它回答的是另一个问题。
sql
SELECT orders.id FROM join_order AS orders
WHERE EXISTS (
SELECT 1 FROM join_payment AS payment WHERE payment.order_id=orders.id
) ORDER BY orders.id;
SELECT orders.id FROM join_order AS orders
WHERE NOT EXISTS (
SELECT 1 FROM join_payment AS payment WHERE payment.order_id=orders.id
) ORDER BY orders.id;
SELECT orders.id,payment.id AS payment_id,payment.amount
FROM join_order AS orders
LEFT JOIN join_payment AS payment ON payment.order_id=orders.id
WHERE payment.amount IS NULL
ORDER BY orders.id,payment.id;预期:EXISTS 返回 1、2;NOT EXISTS 返回 3;最后查询返回订单 2 和 3。判断“没有匹配”应测试右侧非 NULL 主键 payment.id IS NULL,或直接使用 NOT EXISTS,而不是可空业务列。
EXISTS 表达半连接:保留符合存在条件的左行,不按右侧匹配数复制。它不会去重左输入中本来就有的重复行。NOT EXISTS 表达反连接:没有满足相关条件的右行。它与 NOT IN 的 NULL 边界由 10逐项对照;优化器是否转换为物理半连接/反连接还取决于具体查询与版本。
sql
SELECT payment.id,payment.order_id
FROM join_payment AS payment
WHERE NOT EXISTS (
SELECT 1 FROM join_order AS orders WHERE orders.id=payment.order_id
) ORDER BY payment.id;预期返回 25、26;若业务仅定义“非 NULL 引用却不存在父行”为孤儿,应再加 payment.order_id IS NOT NULL,只返回 25。
5. 过滤位置:相同条件,不同保留对象
sql
SELECT orders.id,payment.id AS payment_id
FROM join_order AS orders
LEFT JOIN join_payment AS payment ON payment.order_id=orders.id
WHERE payment.amount>0
ORDER BY orders.id,payment.id;
SELECT orders.id,payment.id AS payment_id
FROM join_order AS orders
LEFT JOIN join_payment AS payment
ON payment.order_id=orders.id AND payment.amount>0
ORDER BY orders.id,payment.id;预期第一条只有订单 1 的三行;第二条还有订单 2、3 各一条补 NULL 行。条件放在 ON 中时,订单 2 的支付金额未知,不满足匹配条件,但 LEFT JOIN 仍保留该订单并补 NULL。条件放在 WHERE 中时,它筛选的是连接结果:订单 2 的未知金额与订单 3 的补空金额都使 amount > 0 得到 UNKNOWN(未知)。SQL 条件有真、假、未知三种结果,WHERE 只保留真,因此两笔订单都会消失。多层外连接还有“行保留正确、聚合值错误”的陷阱,下一篇 06用月外明细复现。
6. 物理算法与执行证据
这一节是可选进阶:算法决定怎样找到匹配,不改变应该输出哪些匹配组合。先用上面的主键配对验证语义,再研究下面的执行成本。
| 算法 | 工作方式 | 需要观察的成本 |
|---|---|---|
| Nested Loop(嵌套循环连接) | 对外侧输入的每一行访问内侧输入;内侧可能索引查找或扫描 | 外侧实际行数、内侧 loops(执行次数)、每次返回行数与访问路径 |
| Hash Join(哈希连接) | 用散列表组织构建侧数据,再用另一侧数据探测匹配;重复键仍需输出全部匹配 | 构建侧规模、内存、溢写(内存不足时写入磁盘)和输出基数(结果行数) |
| Merge Join(归并连接) | 两侧按连接键有序推进,处理同键行组 | 获得有序输入的排序成本、重复键组与输出量 |
MySQL 8.4 手册明确包含内连接、非等值条件、外连接、半连接与反连接的 Hash Join 场景,不能说它“只支持等值内连接”。8.0 各小版本能力不同,具体范围应查目标版本;本实验不强制某算法。Merge Join 是跨产品比较模型,不假定 MySQL 8.4 会选它。
sql
EXPLAIN FORMAT=TREE
SELECT orders.id,payment.id
FROM join_order AS orders
JOIN join_payment AS payment ON payment.order_id=orders.id
WHERE orders.customer_id=1001;
EXPLAIN ANALYZE
SELECT orders.id,payment.id
FROM join_order AS orders
JOIN join_payment AS payment ON payment.order_id=orders.id
WHERE orders.customer_id=1001;ANALYZE 会实际运行查询。记录版本、行数、统计信息、估算/实际行数和 loops;此处小数据只能看机制,不能推出索引、EXISTS 或 Hash Join 普遍更快。13解释成本估算,14提供计划判读。
分布式连接还可能把一侧数据发送到多个节点,称为广播,或按连接键重新分配数据,称为按键重分布。这会增加网络传输成本;若大量记录集中在少数键上,还可能造成数据倾斜,让少数节点承担大部分工作。只有访问路径确实跨节点才讨论这种成本。29 分库分表是跨分片关联的应用,而不是所有产品的 JOIN 算法说明。
7. 排障与工程取舍
- 定义输入与输出一行代表什么,查唯一约束而非相信字段名。
- 暂去聚合,输出左右主键,按业务 ID 检查匹配数与乘法基数。
- 对照独立子表总额,测试相等合法金额、无匹配、可空金额与孤儿数据。
- 先修语义,再用计划和代表性分布评估预聚合、相关查询或索引。
- 表间读取需要一致时,再明确事务快照;JOIN 本身不是跨资源一致性协议。
清理前结束所有会话事务,在 kb_database_lab 中依次删除 join_payment、join_item、join_order,不要删除公共实验库。自然后续:06 过滤位置 → 07 聚合与窗口。
8. 名词解释
| 名词 | 含义 |
|---|---|
| JOIN / ON | 组合满足条件的记录 / 定义本级匹配条件 |
| 粒度 / 连接基数 | 一行代表什么业务对象 / 每行可以匹配多少行 |
| 预聚合 | 先按目标单位汇总,再关联,避免同一金额被重复累计 |
| 半连接 / 反连接 | 只判断存在匹配并保留左行 / 只保留没有匹配的左行 |
| 投影 / 多重集 | 选取输出列 / 允许重复元素的集合,普通 SQL 不自动去重 |
| 孤儿记录 | 引用的父记录不存在的子记录;未知引用需按业务另行区分 |
| Nested Loop / Hash Join | 外侧逐行驱动内侧查找 / 用散列表组织匹配;都是物理做法 |
参考资料
- MySQL 8.4 Reference Manual:§10.2.1.4 Hash Join Optimization、§10.2.2.1 Optimizing IN and EXISTS Subquery Predicates with Semijoin and Antijoin Transformations。
- 适用条件:SQL 输出、执行计划和性能应在目标版本按 A留证。