MYSQL-联合查询

[复制链接]
发表于 昨天 22:10 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有账号?立即注册

×
核心结论与决策建议(BLUF)
多表联合查询(JOIN 与 UNION)是关系型数据库表达复杂业务关联的核心手段。在MySQL中,多表关联从底层笛卡尔积过滤演进至现代化基于代价模型(CBO)的关联执行计划。选型与调优黄金法则:1. 关联字段必须同字符集且具备高效索引,驱动表必须遵循“小表驱动大表”准则;2. MySQL 8.0 引入的 Hash Join 彻底重构了非索引关联的性能瓶颈,应优先确保
  1. join_buffer_size
复制代码
合理;3. 结果集合并无去重诉求时坚决使用 UNION ALL,消除内部临时表排序去重(Distinct Sort)开销
。
关联/合并类型底层核心执行机制索引依赖度性能与内存消耗适用业务场景
内连接 (INNER JOIN)Index Nested-Loop / Hash Join强依赖被驱动表外键索引极优(索引覆盖下微秒级响应)主外键精确匹配、交易详情关联
左/右外连接 (LEFT/RIGHT JOIN)强制以左表或右表为驱动表依赖被驱动表索引优(需防范全表笛卡尔积膨胀)主从报表统计、补齐空缺缺失值
自连接 (Self JOIN)同物理表重命名为两个虚拟别名依赖自引关联列索引中等(数据量大时易产生双倍I/O)同表同类别对比、层级汇报
UNION ALL直接流式多结果集追加合并无索引依赖极快,零临时表排序开销分表结果聚合、历史归档并表汇总
UNION (去重)将结果写入内部临时表并全量排序去重依赖
  1. sort_buffer_size
复制代码
较慢,数据量大时转磁盘排序结果集必须物理排重时审慎使用


一、笛卡尔积与内连接的底层演化
设有学生表
  1. student
复制代码
与成绩表
  1. score
复制代码
。若不带连接条件执行逗号多表查询,MySQL将生成两表的笛卡尔全积(行数相乘):

  1. -- 查看特定学生的多表关联
  2. SELECT * FROM student, score s WHERE student.name = '唐三藏';
复制代码





只筛选单人记录验证:



显而易见,未经关联键约束的查询会遍历整表生成冗余垃圾数据。追加等值连接条件:

  1. SELECT s.name, sco.score
  2. FROM student s, score sco
  3. WHERE s.name = '唐三藏' AND s.id = sco.student_id;
复制代码




二、外连接与自连接实战
1. INNER JOIN 与 LEFT JOIN 的数据完整度对比:

  1. -- 内连接:仅保留两端均存在的匹配记录
  2. SELECT s.name, sco.score
  3. FROM student s
  4. INNER JOIN score sco ON s.id = sco.student_id;
复制代码




使用 LEFT JOIN 完整保留主表记录,即使该学生尚未录入成绩:

  1. SELECT s.name, sco.score
  2. FROM student s
  3. LEFT JOIN score sco ON s.id = sco.student_id;
复制代码




利用
  1. WHERE sco.score IS NULL
复制代码
快速排查缺考人员:



2. 自连接(Self Join)跨行数据对比:
当需要在同一张表中对比不同科目或不同日期的分数变化时,给同一张表赋予两个独立别名:

  1. -- 对比同一学生在课程1与课程2上的成绩高低
  2. SELECT s1.student_id, s1.score AS score1, s2.score AS score2
  3. FROM score s1
  4. JOIN score s2 ON s1.student_id = s2.student_id
  5. WHERE s1.course_id = 1 AND s2.course_id = 2 AND s2.score > s1.score;
复制代码




三、子查询与派生临时表
1. 单行与多行子查询:

  1. -- 查找同班同学
  2. SELECT s.name FROM student s
  3. WHERE s.class_id = (SELECT class_id FROM student WHERE name = '不想毕业')
  4.   AND s.name != '不想毕业';
复制代码





  1. -- 多行 IN 子查询
  2. SELECT * FROM student WHERE id IN (SELECT student_id FROM score WHERE course_id IN (1, 3));
复制代码




2. 聚合派生表关联:
先计算平均分基线,再筛选高于班级平均分的高分学生:

  1. -- 步骤1:计算平均值
  2. SELECT AVG(score) FROM score;
复制代码




随后将派生表与主表关联查询:



四、UNION 与 UNION ALL 的物理合并对决
合并多个独立的 SELECT 结果集:
1. UNION(去重合并):

  1. SELECT * FROM student s WHERE s.id < 3
  2. UNION
  3. SELECT * FROM student s WHERE s.id = 8;
复制代码




2. UNION ALL(极速追加合并):

  1. SELECT * FROM student s WHERE s.id < 3
  2. UNION ALL
  3. SELECT * FROM student s WHERE s.id = 8 OR s.id = 1;
复制代码




生产级避坑指南:
UNION 底层必须创建内部临时表,并对所有字段执行全量排序去重。在千万级大表中,哪怕只多合并几千行数据,UNION 都会直接导致磁盘 I/O 暴涨。除非业务明确要求跨查询排重,否则一律强制使用 UNION ALL。

常见问题(FAQ)
Q1: 为什么两张表字段都有索引,做 JOIN 时执行计划仍然显示全表扫描(ALL)?
A1: 最隐蔽的原因是“隐式类型转换”或“字符集编码不一致”。如果驱动表关联列为
  1. utf8mb4_general_ci
复制代码
而被驱动表为
  1. utf8mb4_unicode_ci
复制代码
,或者一侧为
  1. VARCHAR
复制代码
另一侧为
  1. INT
复制代码
,MySQL无法直接走索引寻址,必须对每一行进行转换函数计算,导致索引彻底失效。

Q2: MySQL 8.0 的 Hash Join 会在什么场景下自动替代传统的嵌套循环关联(NLJ)?
A2: 当两个表进行等值连接(如
  1. ON A.c1 = B.c1
复制代码
),且被驱动表在关联列上没有任何索引时,MySQL 8.0 优化器会默认自动放弃低效的 Block Nested-Loop(BNL),转而采用 Hash Join:将较小的表在内存
  1. join_buffer_size
复制代码
中构建哈希表,随后流式扫描大表进行哈希探测,大幅降低关联时间。



qidao123.com企服评测-应用市场 编辑提示:本文由企服大模型基于真实系统运维实践重构整理,聚焦技术原理、性能对比与选型避坑。若在实际部署中遇到异常,欢迎在下方跟帖交流;如需企业级技术支持或深度运维解决方案,请访问 qidao123.com 获取专属支持。
回复

使用道具 举报

登录后关闭弹窗

登录参与点评抽奖  加入IT实名职场社区
去登录
快速回复 返回顶部 返回列表