网站首页 收藏本站
广告位联系

APP正在开发中...
返回顶部
您的位置： 主页 > 数据库 > Mysql >

MySQL 8.4进阶之多表JOIN、索引与查询优化

Mysql 来源：互联网 作者：佚名 发布时间：2026-10-03 20:17:20 人浏览
摘要

MySQL 8.4 进阶：多表 JOIN + 索引与查询优化 这两件事其实是一条线：JOIN 写不对，结果错；JOIN 没索引，性能崩。下面用一套完整的示例库串起来讲。 第一部分：多表 JOIN 查询 一、先建三张表（

MySQL 8.4 进阶：多表 JOIN + 索引与查询优化

这两件事其实是一条线：JOIN 写不对，结果错；JOIN 没索引，性能崩。下面用一套完整的示例库串起来讲。

第一部分：多表 JOIN 查询

一、先建三张表（可直接跑）

沿用上一节的 school 库，再加两张表形成「班级 → 学生 → 成绩」的三级关系：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

USE school;

CREATE TABLE classes (

  id   INT PRIMARY KEY,

  name VARCHAR(20) NOT NULL

) ENGINE=InnoDB;

-- 给学生表补上 class_id（已有表可用 ALTER 追加）

ALTER TABLE students ADD COLUMN class_id INT, ADD INDEX idx_class (class_id);

CREATE TABLE scores (

  id         INT AUTO_INCREMENT PRIMARY KEY,

  student_id INT NOT NULL,

  course     VARCHAR(20) NOT NULL,

  score      DECIMAL(5,2),

  UNIQUE KEY uk_stu_course (student_id, course),   -- 一人一科一条，兼作索引

  INDEX idx_course (course)

) ENGINE=InnoDB;

INSERT INTO classes VALUES (1,'高一(1)班'), (2,'高一(2)班'), (3,'高一(3)班');

UPDATE students SET class_id = 1 WHERE id IN (1,2);

UPDATE students SET class_id = 2 WHERE id IN (3,4);

INSERT INTO scores (student_id, course, score) VALUES

  (1,'数学',92),(1,'英语',78),(2,'数学',55),(3,'数学',88),(3,'英语',91);

注意 scores.student_id 上有索引（uk_stu_course 的最左列）。关联列必须有索引——这是 JOIN 性能的第一定律，后面第二部分会解释为什么。

二、JOIN 的本质：先笛卡尔积，再按 ON 过滤

1

2

-- 不带 ON，得到 4 × 3 = 12 行笛卡尔积（几乎永远不是你要的）

SELECT * FROM students, classes;

JOIN 就是在笛卡尔积上做过滤，所以 ON 条件写漏 = 结果爆炸。这就是为什么多表查询宁可多写 ON，也不能省。

三、四种 JOIN 一张图记住

类型 含义 口诀
INNER JOIN 只保留两边都能匹配的行 取交集
LEFT JOIN 保留左表全部，右表没匹配补 NULL 左表为准
RIGHT JOIN 保留右表全部（实际几乎不用，改成换顺序的 LEFT JOIN 更易读） 右表为准
CROSS JOIN 纯笛卡尔积，无 ON 组合枚举

1. INNER JOIN（最常用）

1

2

3

SELECT s.name, c.name AS 班级, s.score

FROM students s

INNER JOIN classes c ON s.class_id = c.id;

INNER 可省略，直接写 JOIN 就是 INNER。s、c 是表别名，多表查询必须用别名。

2. LEFT JOIN（查"有没有"的神器）

1

2

3

4

5

-- 列出所有班级，以及各班的学生（没人就显示 NULL）

SELECT c.name AS 班级, s.name AS 学生

FROM classes c

LEFT JOIN students s ON s.class_id = c.id

ORDER BY c.id;

经典用法：查"不存在的"——找出一门成绩都没有的学生（反连接 / anti-join）：

1

2

3

4

SELECT s.name

FROM students s

LEFT JOIN scores sc ON sc.student_id = s.id

WHERE sc.id IS NULL;      -- 右表主键为 NULL ⇒ 没匹配上

同理可查「没有任何学生的空班级」。这比 NOT IN 更快也更安全（NOT IN 遇到子查询里有 NULL 会返回空结果，是个著名陷阱）。

3.  ON 和 WHERE 的区别（LEFT JOIN 最大坑）

1

2

3

4

5

6

7

8

9

-- A：条件写在 ON 里 —— 先过滤右表，再左连接。左表行全保留

SELECT s.name, sc.score

FROM students s

LEFT JOIN scores sc ON sc.student_id = s.id AND sc.course = '数学';

-- B：条件写在 WHERE 里 —— 连接完再过滤，NULL 行被干掉，LEFT JOIN 退化成 INNER JOIN

SELECT s.name, sc.score

FROM students s

LEFT JOIN scores sc ON sc.student_id = s.id

WHERE sc.course = '数学';

A 会列出所有学生（没数学成绩的显示 NULL），B 只列出有数学成绩的学生。想保留左表全部，过滤右表的条件必须放 ON。

4. 自连接（SELF JOIN）

表自己和自己连，用来做「同组内比较」：

1

2

3

4

5

-- 找出和"张三"同班的同学

SELECT s2.name

FROM students s1

JOIN students s2 ON s1.class_id = s2.class_id

WHERE s1.name = '张三' AND s2.name <> '张三';

自连接必须起不同的别名，否则 MySQL 报 Not unique table/alias。

四、JOIN + 聚合：真实业务的常见形态

1

2

3

4

5

6

7

8

9

-- 每个班的平均分、最高分、人数（没学生的班级也列出）

SELECT c.name AS 班级,

       COUNT(s.id)      AS 人数,

       ROUND(AVG(s.score),1) AS 平均分,

       MAX(s.score)     AS 最高分

FROM classes c

LEFT JOIN students s ON s.class_id = c.id

GROUP BY c.id, c.name

ORDER BY 平均分 DESC;

COUNT(s.id) 而不是 COUNT(*)：LEFT JOIN 产生的 NULL 行不会被 COUNT(列) 计入，正好得到 0 人。

高于本班平均分的学生（派生表 JOIN，子查询先聚合再关联）：

1

2

3

4

5

6

7

SELECT s.name, s.score, t.avg_score

FROM students s

JOIN (

  SELECT class_id, AVG(score) AS avg_score

  FROM students GROUP BY class_id

) t ON s.class_id = t.class_id

WHERE s.score > t.avg_score;

子查询放在 FROM 里叫派生表（derived table），MySQL 8.x 会尽量把它合并到外层查询，EXPLAIN 里看不到 <derived2> 就说明合并成功了。

五、IN / EXISTS / JOIN 怎么选

1

2

3

4

-- 查有数学成绩的学生

SELECT name FROM students WHERE id IN (SELECT student_id FROM scores WHERE course='数学');

SELECT name FROM students s WHERE EXISTS (SELECT 1 FROM scores sc WHERE sc.student_id=s.id AND sc.course='数学');

SELECT DISTINCT s.name FROM students s JOIN scores sc ON sc.student_id=s.id WHERE sc.course='数学';

MySQL 8.4 会把 IN 子查询自动转成 semi-join（table pullout / materialization / first match / loosescan / duplicate weedout 五种策略按代价选），三者性能往往趋同。

结论：别背"哪个一定快"的口诀，用 EXPLAIN 看实际计划。真正要避免的是相关子查询逐行执行（EXPLAIN 里出现 DEPENDENT SUBQUERY）。

第二部分：索引与查询优化

一、索引是什么

InnoDB 的索引是 B+Tree，类比新华字典：

  • 有索引 = 按拼音目录直接翻到那一页 → 查 1 次
  • 无索引 = 从第一页逐页翻 → 全表扫描（type: ALL）

1

2

3

4

5

CREATE INDEX idx_name ON students(name);                 -- 普通索引

CREATE UNIQUE INDEX uk_email ON students(email);         -- 唯一索引

CREATE INDEX idx_class_score ON students(class_id, score); -- 复合索引

ALTER TABLE students DROP INDEX idx_name;                -- 删除

SHOW INDEX FROM students;                                -- 查看

代价：索引不是免费的。每多一个索引，INSERT/UPDATE/DELETE 就要多维护一棵树，写性能线性下降、磁盘占用变大。索引是读性能和写性能的交易。

二、最左前缀原则（复合索引的灵魂）

索引 (class_id, score, name) 相当于同时建了：

(class_id)
(class_id, score)
(class_id, score, name)

1

2

3

4

5

WHERE class_id = 1                         -- ? 用索引

WHERE class_id = 1 AND score > 80          -- ? 用两列

WHERE class_id = 1 AND score > 80 AND name='张三' -- ? 用三列

WHERE score > 80                           -- ? 跳过最左列，索引失效

WHERE class_id = 1 AND name = '张三'        -- ?? 只能用上 class_id，name 被 score 挡住

复合索引列顺序的黄金法则

等值列在前 → 范围列在后 → 排序列最后

1

2

3

4

-- 查询模式

WHERE class_id = ? AND score > ? ORDER BY created_at LIMIT 20;

-- 最优索引

CREATE INDEX idx_cls_score_time ON students(class_id, score, created_at);

原因：范围查询（> < BETWEEN）之后的列，索引无法再用于精确定位，只能用于索引下推过滤。所以范围列要放最后（排序需求除外）。

三、覆盖索引：Extra 里出现 Using index 就是赢

1

2

CREATE INDEX idx_class_score ON students(class_id, score);

SELECT class_id, score FROM students WHERE class_id = 1;   -- ? 覆盖索引

查询的列全部在索引里，MySQL 根本不用回表读主键行数据，直接从索引树拿结果，速度能差一个数量级。这也是为什么严禁 SELECT *——一旦多查一个非索引列，覆盖索引立刻失效。

四、EXPLAIN  怎么看（调优的核心工具）

1

EXPLAIN SELECT s.name FROM students s JOIN scores sc ON sc.student_id = s.id;

重点看这几列：

列 看点 好坏排序
type 访问方式 system/const > eq_ref > ref > range > index > ALL
key 实际用到的索引 为 NULL 就是没用上
rows 预估扫描行数 越小越好，是调优第一指标
filtered 过滤后剩余百分比 太低说明索引过滤性不够
Extra 附加信息 见下

Extra 里的关键信号：

值 含义 态度
Using index 覆盖索引 ???? 很好
Using index condition 索引下推（ICP） ? 不错
Using where 在存储引擎之上再过滤 ? 正常
Using filesort 额外排序，没走索引顺序 ?? 需要 ORDER BY 建索引
Using temporary 建临时表（GROUP BY / DISTINCT） ?? 较重，需优化
Using join buffer 关联列没索引，走嵌套/哈希连接 ???? 优先加索引

type 速查：

  • eq_ref：用主键或唯一索引做关联，每次只匹配 1 行 —— JOIN 的理想状态
  • ref：用普通索引等值匹配
  • range：BETWEEN、IN、> 等范围扫描
  • ALL：全表扫描，大表上的红色警报
  • 更狠的：EXPLAIN ANALYZE

普通 EXPLAIN 是估算，8.0.18+ 的 EXPLAIN ANALYZE 会真的把查询跑一遍，给出实际耗时和行数：

1

2

EXPLAIN ANALYZE

SELECT s.name, sc.score FROM students s JOIN scores sc ON sc.student_id = s.id;

它真的会执行，别在生产库对着慢查询反复跑。

树形输出用 EXPLAIN FORMAT=TREE，看 JOIN 顺序和算法更直观。

五、索引失效的 8 个典型场景（背下来）

1

2

3

4

5

6

7

8

9

10

11

12

1. WHERE YEAR(created_at) = 2026              -- ? 列上套函数

   WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'  -- ? 改写成范围

2. WHERE phone = 13800138000                  -- ? 隐式类型转换：phone 是 VARCHAR 却传数字

   WHERE phone = '13800138000'                -- ? 类型一致（这坑极其隐蔽，且报错也不报）

3. WHERE name LIKE '%三'                      -- ? 前导通配符

   WHERE name LIKE '张%'                      -- ? 前缀匹配可用索引

4. WHERE age + 1 > 20                         -- ? 列参与运算

   WHERE age > 19                             -- ?

5. WHERE a = 1 OR b = 2                       -- ?? OR 条件容易全表扫，改 UNION ALL 或分别建索引

6. WHERE status != 1 / NOT IN / IS NULL       -- ?? 优化器可能放弃索引（取决于选择性）

7. 不满足最左前缀                              -- 见上文

8. JOIN 列字符集/排序规则不一致                -- utf8mb4_general_ci 连 utf8mb4_0900_ai_ci 会失效

选择性（Cardinality）原则：性别、是否删除这种只有 2~3 个取值的列，单独建索引几乎没用（优化器会直接放弃）。低选择性列要放在复合索引的后面。

六、JOIN 的性能优化

1. 关联列必须建索引（第一优先级）

1

2

3

-- 被驱动表（右表）的关联列没索引 → 外层每取一行，内层就全表扫一次

-- 1万 × 1万 = 1亿次比较，灾难

CREATE INDEX idx_student ON scores(student_id);

加了索引后 EXPLAIN 的 type 会从 ALL 变成 ref/eq_ref，复杂度从 O(N×M) 降到 O(N×logM)。

2. 8.4 默认开启 Hash Join

MySQL 8.0.18 起引入、8.4 中默认启用哈希连接：当关联列没有可用索引时，优化器不再傻傻做嵌套循环，而是把小表建哈希表、扫大表匹配，Extra 里显示 Using join buffer (hash join)。

1

2

3

-- 强制/禁用哈希连接（8.4 支持）

SELECT /*+ HASH_JOIN(t1, t2) */    * FROM t1 JOIN t2 ON t1.c1 = t2.c1;

SELECT /*+ NO_HASH_JOIN(t1, t2) */ * FROM t1 JOIN t2 ON t1.c1 = t2.c1;

但要清醒：Hash Join 是"没索引时的兜底方案"，不是"可以不建索引"的理由。OLTP 场景（高并发点查、小结果集）下，走索引的 eq_ref 仍然完胜 Hash Join。Hash Join 主要利好大表关联的 OLAP / 报表场景。

3. 小表驱动大表

优化器通常自己会选，但写 LEFT JOIN 时左表行数会强制保留，所以把行数少的表放左边。STRAIGHT_JOIN 可强制按书写顺序连接（慎用）。

4. 只 JOIN 需要的表、只 SELECT 需要的列

多 JOIN 一张表就多一层放大。中间结果越大，排序和临时表越容易落盘。

七、深分页优化（面试 & 实战高频）

1

SELECT * FROM students ORDER BY id LIMIT 100000, 20;   -- ? 要扫 100020 行再丢弃前 10 万

方案 A：延迟关联（用覆盖索引先定位 id，再回表）

1

2

SELECT s.* FROM students s

JOIN (SELECT id FROM students ORDER BY id LIMIT 100000, 20) t USING (id);

方案 B：游标分页（推荐，适合无限滚动）

1

SELECT * FROM students WHERE id > 100000 ORDER BY id LIMIT 20;  -- ? 直接定位

八、8.4 里几个好用的索引新特性

1. 不可见索引 —— 删索引前的"安全气囊"

1

2

3

ALTER TABLE students ALTER INDEX idx_name INVISIBLE;   -- 对优化器隐藏，但仍维护

-- 观察一段时间没问题后再 DROP

ALTER TABLE students ALTER INDEX idx_name VISIBLE;     -- 秒级回滚

删错一个大表索引，重建可能要几小时；设为 INVISIBLE 再改回 VISIBLE 是秒级的。用 optimizer_switch 的 use_invisible_indexes=on 还能只对当前会话测试：

1

EXPLAIN SELECT /*+ SET_VAR(optimizer_switch='use_invisible_indexes=on') */ * FROM students WHERE name='张三';

2. 函数索引 —— 解决"列上套函数就失效"

1

2

ALTER TABLE students ADD INDEX idx_year ((YEAR(created_at)));  -- 注意双括号！

SELECT * FROM students WHERE YEAR(created_at) = 2026;          -- 现在能走索引了

双括号 ((...)) 是语法强制要求，少了会报错。MySQL 内部其实是建了一个隐藏的虚拟生成列。

3. 降序索引 —— 消灭 Using filesort

1

2

CREATE INDEX idx_score_desc ON students(score DESC, id ASC);

SELECT * FROM students ORDER BY score DESC, id ASC LIMIT 10;  -- 直接走索引顺序

4. 直方图 —— 数据倾斜时救优化器

1

ANALYZE TABLE students UPDATE HISTOGRAM ON class_id WITH 10 BUCKETS;

某些值占比极高时（比如 90% 的学生都在 1 班），普通索引统计会误判选择性，直方图能显著提升执行计划的准确度。

5. 冗余/无用索引清理

1

2

SELECT * FROM sys.schema_unused_indexes     WHERE object_schema = 'school';  -- 从没被用过

SELECT * FROM sys.schema_redundant_indexes  WHERE table_schema = 'school';   -- 被其他索引覆盖的冗余索引

典型冗余：已有 (a,b,c) 又建了 (a) —— 后者完全被最左前缀覆盖，纯属拖慢写入。

另外：外键列一定要建索引，InnoDB 不会自动给外键的"子表侧"建索引（只有主键自动建）。

九、一套可复用的调优流程

  • 抓慢 SQL  → 开启 slow_query_log，或用 sys.statement_analysis / performance_schema
  • EXPLAIN   → 看 type / key / rows / Extra，找出全表扫描和 filesort
  • 补索引    → 按"等值→范围→排序"顺序建复合索引，优先覆盖索引
  • 改 SQL    → 去 SELECT *、函数改写、深分页改游标、相关子查询改 JOIN
  •  验证      → EXPLAIN ANALYZE 对比前后 rows 和实际耗时
  • 收尾      → 新索引先 INVISIBLE 上线观察，确认有效再 VISIBLE；清理冗余索引

下一步建议

把上面的 SQL 在 MySQL 8.4 里真跑一遍，重点对比加索引前后 EXPLAIN 的 rows 变化——看到数字从几万掉到几十的那一刻，索引才真正变成你自己的知识。

想继续深入的话，可以选一个方向：

  • 事务与锁（READ COMMITTED/REPEATABLE READ、行锁/间隙锁、死锁排查）
  • 执行计划深挖（EXPLAIN FORMAT=JSON 的 cost_info、optimizer_switch 调参）
版权声明 : 本文内容来源于互联网或用户自行发布贡献，该文观点仅代表原作者本人。本站仅提供信息存储空间服务和不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权， 违法违规的内容， 请发送邮件至2530232025#qq.cn(#换@)举报，一经查实，本站将立刻删除。

您可能感兴趣的文章 :

原文链接 :
相关文章
推荐阅读
最新更新
友情链接
  • 本站所有内容来源于互联网或用户自行发布，本站仅提供信息存储空间服务，不拥有版权，不承担法律责任。如有侵犯您的权益，请您联系站长处理！
  • Copyright © 2017-2022 F11.CN All Rights Reserved. F11站长开发者网 版权所有 | 苏ICP备2022031554号-1 | 51LA统计