Node系列 · 数据库：联表查询

Node系列 · 数据库联表查询单表查询覆盖 80% 业务场景剩下 20% 是数据分散在多张表——必须靠 JOIN 合并。本章讲清楚三种 JOIN 的行为差异、何时选哪个、JOIN 性能优化。一、为什么需要 JOIN关系型数据库强调单表职责单一——用户表存用户、订单表存订单、文章表存文章。要查张三的所有订单就需要联表student (id, name, class_id) ← 学生 score (id, student_id, subject, score) ← 成绩查张三的所有数学成绩需要把两张表按student_id拼起来。二、三种 JOIN2.1 INNER JOIN内连接只保留两表都有匹配的行——交集。SELECT s.id, s.name, sc.subject, sc.score FROM student AS s INNER JOIN score AS sc ON sc.student_id s.id WHERE s.name 张三;维度INNER JOIN匹配规则两边都满足 ON 条件结果集交集A ∩ BNULL 行不保留性能通常最快只扫匹配的行2.2 LEFT JOIN左外连接保留左表全部行右表没匹配就填 NULL——左表全集 右表匹配。-- 查每个学生 他们的成绩没考的学生也列出成绩为 NULL SELECT s.id, s.name, sc.subject, sc.score FROM student AS s LEFT JOIN score AS sc ON sc.student_id s.id;维度LEFT JOIN匹配规则左表全部保留右表无匹配字段填 NULL结果集左表全集 右表匹配典型场景“找没下过单的用户”、“找没评论的文章”2.3 RIGHT JOIN右外连接与 LEFT JOIN 对称——保留右表全部行左表没匹配就填 NULL。SELECT s.id, s.name, sc.subject, sc.score FROM student AS s RIGHT JOIN score AS sc ON sc.student_id s.id;::: warningRIGHT JOIN 几乎不用。因为A RIGHT JOIN B等价于B LEFT JOIN A——直接交换表顺序用 LEFT JOIN 更易读。生产代码里看到 RIGHT JOIN 通常会被同事吐槽。:::2.4 FULL OUTER JOINMySQL 不直接支持MySQL 没有FULL OUTER JOIN关键字但可以用UNION模拟 LEFT RIGHTSELECT s.*, sc.* FROM student s LEFT JOIN score sc ON sc.student_id s.id UNION SELECT s.*, sc.* FROM student s RIGHT JOIN score sc ON sc.student_id s.id;三、JOIN 结果集可视化LEFT JOIN: 5 行a-NULLb-2c-3d-NULLe-NULLINNER JOIN: 2 行b-2c-3右表 score (3 行)123左表 student (5 行)abcde四、ON 与 WHERE 的关键差异ON和 WHERE 都能过滤行但作用时机不同-- ON 过滤保留左表全部行右表不满足 ON 的列填 NULLSELECTs.*,sc.scoreFROMstudent sLEFTJOINscore scONsc.student_ids.idANDsc.subject数学;-- 所有学生都在没考数学的学生 score 是 NULL-- WHERE 过滤先 LEFT JOIN结果再 WHERE 过滤SELECTs.*,sc.scoreFROMstudent sLEFTJOINscore scONsc.student_ids.idWHEREsc.subject数学;-- 只剩考了数学的学生LEFT JOIN 退化成 INNER JOIN::: tipLEFT JOIN WHERE INNER JOIN。这是一个常见的反直觉点。LEFT JOIN 的保留左表全部行承诺只在 ON 阶段生效进入 WHERE 阶段后所有行包括 NULL都参与过滤。:::五、多表 JOIN可以连续 JOIN 多张表SELECT s.id, s.name, c.name AS class_name, sc.subject, sc.score FROM student s JOIN class c ON c.id s.class_id LEFT JOIN score sc ON sc.student_id s.id WHERE s.sex b1;每加一个 JOIN 关联一张表。表越多性能越差——超过 5 张表要审视设计。六、自连接Self Join表自己连自己用于上下级关系、相邻行等场景employee ┌────┬───────────┬──────────┐ │ id │ name │ manager_id │ ├────┼───────────┼──────────┤ │ 1 │ 张总 │ NULL │ │ 2 │ 王经理 │ 1 │ │ 3 │ 李员工 │ 2 │ └────┴───────────┴──────────┘查每个员工的直属上级姓名SELECT e.name AS employee, m.name AS manager FROM employee AS e LEFT JOIN employee AS m ON m.id e.manager_id;employeemanager张总NULL王经理张总李员工王经理七、JOIN 性能优化7.1 驱动表选择MySQL JOIN 是嵌套循环每扫一行驱动表就在被驱动表上做一次查询。-- A 是驱动表外层循环-- B 是被驱动表内层循环靠索引定位SELECT*FROMAJOINBONA.idB.a_id;优化原则小表驱动大表被驱动表的连接字段必须有索引。判断小表的方法-- 看两表行数SELECTCOUNT(*)FROMA;SELECTCOUNT(*)FROMB;但更关键的是过滤后的行数-- 即使 A 行数多如果 WHERE 过滤后只剩 10 行A 仍是小驱动表EXPLAINSELECT*FROMAJOINBONA.idB.a_idWHEREA.status1;7.2 必须为连接字段建索引-- score.student_id 上必须有索引SELECTs.*,sc.scoreFROMstudent sJOINscore scONsc.student_ids.id;EXPLAINSELECT*FROMstudent sJOINscore scONsc.student_ids.id;::: warning看EXPLAIN输出里的type列system/const最优eq_ref被驱动表主键或唯一索引 JOIN最佳 JOIN 性能ref被驱动表普通索引 JOINOKindex全索引扫描慢ALL全表扫描必须优化:::7.3 小表驱动大表 索引 高效 JOIN-- score 表有 100 万行student 表有 1000 行-- student.id 是主键score.student_id 有索引SELECTs.*,sc.*FROMstudentASs-- 驱动表小JOINscoreASscONsc.student_ids.id;-- 被驱动表大靠索引定位执行计划扫 1000 行 student每行去 score 走索引查 → 1000 次索引查询。7.4 反例大表驱动 索引失效-- ❌ 大表驱动 被驱动表无索引SELECT*FROMscore scJOINstudent sONs.class_idsc.class_id;-- score 100 万行扫一遍student.class_id 无索引 → 全表扫描 100 万次-- 总操作100 万 × 100 万 1 万亿次比较八、Node 端 JOIN 查询const mysql require(mysql2/promise); const pool mysql.createPool({ /* config */ }); const [rows] await pool.execute( SELECT s.id, s.name, sc.subject, sc.score FROM student s LEFT JOIN score sc ON sc.student_id s.id WHERE s.class_id ? ORDER BY s.id LIMIT ?, [3, 20] ); console.log(rows); // [{ id: 1, name: 张三, subject: 数学, score: 95 }, ...]九、最佳实践场景推荐两表关联INNER JOIN要交集或LEFT JOIN要左表全集不用RIGHT JOIN改写为LEFT JOIN 表交换表超过 5 张审视设计或拆查询后应用层合并性能瓶颈EXPLAIN分析执行计划必做被驱动表连接字段必须有索引驱动表选择小表驱动大表过滤后行数小找不到记录LEFT JOIN 右表 IS NULL 找没匹配十、小结三种 JOININNER交集/ LEFT左表全集 右表匹配/ RIGHT右表全集 左表匹配LEFT JOIN WHERE INNER JOIN——LEFT JOIN 的保留全集只在 ON 阶段有效ON 过滤行匹配WHERE 过滤最终结果多表 JOIN 不超过 5 张自连接用表的别名实现自己连自己性能关键小表驱动大表 被驱动表连接字段有索引用EXPLAIN验证执行计划关注type列避免 ALL