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

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

第一部分：多表 JOIN 查询

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

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

USEschool;CREATETABLEclasses(idINTPRIMARYKEY,nameVARCHAR(20)NOTNULL)ENGINE=InnoDB;-- 给学生表补上 class_id（已有表可用 ALTER 追加）ALTERTABLEstudentsADDCOLUMNclass_idINT,ADDINDEXidx_class(class_id);CREATETABLEscores(idINTAUTO_INCREMENTPRIMARYKEY,student_idINTNOTNULL,courseVARCHAR(20)NOTNULL,scoreDECIMAL(5,2),UNIQUEKEYuk_stu_course(student_id,course),-- 一人一科一条，兼作索引INDEXidx_course(course))ENGINE=InnoDB;INSERTINTOclassesVALUES(1,'高一(1)班'),(2,'高一(2)班'),(3,'高一(3)班');UPDATEstudentsSETclass_id=1WHEREidIN(1,2);UPDATEstudentsSETclass_id=2WHEREidIN(3,4);INSERTINTOscores(student_id,course,score)VALUES(1,'数学',92),(1,'英语',78),(2,'数学',55),(3,'数学',88),(3,'英语',91);

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

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

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

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

三、四种 JOIN 一张图记住

1. INNER JOIN（最常用）

SELECTs.name,c.nameAS班级,s.scoreFROMstudents sINNERJOINclasses cONs.class_id=c.id;

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

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

-- 列出所有班级，以及各班的学生（没人就显示 NULL）SELECTc.nameAS班级,s.nameAS学生FROMclasses cLEFTJOINstudents sONs.class_id=c.idORDERBYc.id;

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

SELECTs.nameFROMstudents sLEFTJOINscores scONsc.student_id=s.idWHEREsc.idISNULL;-- 右表主键为 NULL ⇒ 没匹配上

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

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

-- A：条件写在 ON 里 —— 先过滤右表，再左连接。左表行全保留SELECTs.name,sc.scoreFROMstudents sLEFTJOINscores scONsc.student_id=s.idANDsc.course='数学';-- B：条件写在 WHERE 里 —— 连接完再过滤，NULL 行被干掉，LEFT JOIN 退化成 INNER JOINSELECTs.name,sc.scoreFROMstudents sLEFTJOINscores scONsc.student_id=s.idWHEREsc.course='数学';

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

4. 自连接（SELF JOIN）

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

-- 找出和"张三"同班的同学SELECTs2.nameFROMstudents s1JOINstudents s2ONs1.class_id=s2.class_idWHEREs1.name='张三'ANDs2.name<>'张三';

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

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

-- 每个班的平均分、最高分、人数（没学生的班级也列出）SELECTc.nameAS班级,COUNT(s.id)AS人数,ROUND(AVG(s.score),1)AS平均分,MAX(s.score)AS最高分FROMclasses cLEFTJOINstudents sONs.class_id=c.idGROUPBYc.id,c.nameORDERBY平均分DESC;

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

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

SELECTs.name,s.score,t.avg_scoreFROMstudents sJOIN(SELECTclass_id,AVG(score)ASavg_scoreFROMstudentsGROUPBYclass_id)tONs.class_id=t.class_idWHEREs.score>t.avg_score;

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

五、IN/EXISTS/JOIN怎么选

-- 查有数学成绩的学生SELECTnameFROMstudentsWHEREidIN(SELECTstudent_idFROMscoresWHEREcourse='数学');SELECTnameFROMstudents sWHEREEXISTS(SELECT1FROMscores scWHEREsc.student_id=s.idANDsc.course='数学');SELECTDISTINCTs.nameFROMstudents sJOINscores scONsc.student_id=s.idWHEREsc.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）

CREATEINDEXidx_nameONstudents(name);-- 普通索引CREATEUNIQUEINDEXuk_emailONstudents(email);-- 唯一索引CREATEINDEXidx_class_scoreONstudents(class_id,score);-- 复合索引ALTERTABLEstudentsDROPINDEXidx_name;-- 删除SHOWINDEXFROMstudents;-- 查看

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

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

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

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

WHEREclass_id=1-- ✅ 用索引WHEREclass_id=1ANDscore>80-- ✅ 用两列WHEREclass_id=1ANDscore>80ANDname='张三'-- ✅ 用三列WHEREscore>80-- ❌ 跳过最左列，索引失效WHEREclass_id=1ANDname='张三'-- ⚠️ 只能用上 class_id，name 被 score 挡住

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

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

-- 查询模式WHEREclass_id=?ANDscore>?ORDERBYcreated_atLIMIT20;-- 最优索引CREATEINDEXidx_cls_score_timeONstudents(class_id,score,created_at);

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

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

CREATEINDEXidx_class_scoreONstudents(class_id,score);SELECTclass_id,scoreFROMstudentsWHEREclass_id=1;-- ✅ 覆盖索引

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

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

EXPLAINSELECTs.nameFROMstudents sJOINscores scONsc.student_id=s.id;

重点看这几列：

Extra里的关键信号：

type速查：

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

更狠的：EXPLAIN ANALYZE

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

EXPLAINANALYZESELECTs.name,sc.scoreFROMstudents sJOINscores scONsc.student_id=s.id;

⚠️ 它真的会执行，别在生产库对着慢查询反复跑。
树形输出用EXPLAIN FORMAT=TREE，看 JOIN 顺序和算法更直观。

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

1.WHEREYEAR(created_at)=2026-- ❌ 列上套函数WHEREcreated_at>='2026-01-01'ANDcreated_at<'2027-01-01'-- ✅ 改写成范围2.WHEREphone=13800138000-- ❌ 隐式类型转换：phone 是 VARCHAR 却传数字WHEREphone='13800138000'-- ✅ 类型一致（这坑极其隐蔽，且报错也不报）3.WHEREnameLIKE'%三'-- ❌ 前导通配符WHEREnameLIKE'张%'-- ✅ 前缀匹配可用索引4.WHEREage+1>20-- ❌ 列参与运算WHEREage>19-- ✅5.WHEREa=1ORb=2-- ⚠️ OR 条件容易全表扫，改 UNION ALL 或分别建索引6.WHEREstatus!=1/NOTIN/ISNULL-- ⚠️ 优化器可能放弃索引（取决于选择性）7.不满足最左前缀-- 见上文8.JOIN列字符集/排序规则不一致-- utf8mb4_general_ci 连 utf8mb4_0900_ai_ci 会失效

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

六、JOIN 的性能优化

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

-- 被驱动表（右表）的关联列没索引 → 外层每取一行，内层就全表扫一次-- 1万 × 1万 = 1亿次比较，灾难CREATEINDEXidx_studentONscores(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)。

-- 强制/禁用哈希连接（8.4 支持）SELECT/*+ HASH_JOIN(t1, t2) */*FROMt1JOINt2ONt1.c1=t2.c1;SELECT/*+ NO_HASH_JOIN(t1, t2) */*FROMt1JOINt2ONt1.c1=t2.c1;

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

3. 小表驱动大表

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

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

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

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

SELECT*FROMstudentsORDERBYidLIMIT100000,20;-- ❌ 要扫 100020 行再丢弃前 10 万

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

SELECTs.*FROMstudents sJOIN(SELECTidFROMstudentsORDERBYidLIMIT100000,20)tUSING(id);

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

SELECT*FROMstudentsWHEREid>100000ORDERBYidLIMIT20;-- ✅ 直接定位

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

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

ALTERTABLEstudentsALTERINDEXidx_name INVISIBLE;-- 对优化器隐藏，但仍维护-- 观察一段时间没问题后再 DROPALTERTABLEstudentsALTERINDEXidx_name VISIBLE;-- 秒级回滚

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

EXPLAINSELECT/*+ SET_VAR(optimizer_switch='use_invisible_indexes=on') */*FROMstudentsWHEREname='张三';

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

ALTERTABLEstudentsADDINDEXidx_year((YEAR(created_at)));-- 注意双括号！SELECT*FROMstudentsWHEREYEAR(created_at)=2026;-- 现在能走索引了

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

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

CREATEINDEXidx_score_descONstudents(scoreDESC,idASC);SELECT*FROMstudentsORDERBYscoreDESC,idASCLIMIT10;-- 直接走索引顺序

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

ANALYZETABLEstudentsUPDATEHISTOGRAMONclass_idWITH10BUCKETS;

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

5. 冗余/无用索引清理

SELECT*FROMsys.schema_unused_indexesWHEREobject_schema='school';-- 从没被用过SELECT*FROMsys.schema_redundant_indexesWHEREtable_schema='school';-- 被其他索引覆盖的冗余索引

典型冗余：已有(a,b,c)又建了(a)—— 后者完全被最左前缀覆盖，纯属拖慢写入。
另外：外键列一定要建索引，InnoDB 不会自动给外键的"子表侧"建索引（只有主键自动建）。

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

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

下一步建议

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

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

• 事务与锁（READ COMMITTED/REPEATABLE READ、行锁/间隙锁、死锁排查）
• 执行计划深挖（EXPLAIN FORMAT=JSON的cost_info、optimizer_switch 调参）
• SQL 实战题（用这套 school 库做 10 道从易到难的查询练习）