拓冰网站定制 
文章目录MySQL 8.4 进阶多表 JOIN 索引与查询优化第一部分多表 JOIN 查询一、先建三张表可直接跑二、JOIN 的本质先笛卡尔积再按 ON 过滤三、四种 JOIN 一张图记住1. INNER JOIN最常用2. LEFT JOIN查有没有的神器3. ⚠️ ON 和 WHERE 的区别LEFT JOIN 最大坑4. 自连接SELF JOIN四、JOIN 聚合真实业务的常见形态五、IN / EXISTS / JOIN 怎么选第二部分索引与查询优化一、索引是什么二、最左前缀原则复合索引的灵魂复合索引列顺序的黄金法则三、覆盖索引Extra 里出现 Using index 就是赢四、EXPLAIN 怎么看调优的核心工具更狠的EXPLAIN ANALYZE五、索引失效的 8 个典型场景背下来六、JOIN 的性能优化1. 关联列必须建索引第一优先级2. 8.4 默认开启 Hash Join3. 小表驱动大表4. 只 JOIN 需要的表、只 SELECT 需要的列七、深分页优化面试 实战高频八、8.4 里几个好用的索引新特性1. 不可见索引 —— 删索引前的安全气囊2. 函数索引 —— 解决列上套函数就失效3. 降序索引 —— 消灭 Using filesort4. 直方图 —— 数据倾斜时救优化器5. 冗余/无用索引清理九、一套可复用的调优流程下一步建议MySQL 8.4 进阶多表 JOIN 索引与查询优化这两件事其实是一条线JOIN 写不对结果错JOIN 没索引性能崩。下面用一套完整的示例库串起来讲。第一部分多表 JOIN 查询一、先建三张表可直接跑沿用上一节的school库再加两张表形成「班级 → 学生 → 成绩」的三级关系USEschool;CREATETABLEclasses(idINTPRIMARYKEY,nameVARCHAR(20)NOTNULL)ENGINEInnoDB;-- 给学生表补上 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))ENGINEInnoDB;INSERTINTOclassesVALUES(1,高一(1)班),(2,高一(2)班),(3,高一(3)班);UPDATEstudentsSETclass_id1WHEREidIN(1,2);UPDATEstudentsSETclass_id2WHEREidIN(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 一张图记住类型含义口诀INNER JOIN只保留两边都能匹配的行取交集LEFT JOIN保留左表全部右表没匹配补NULL左表为准RIGHT JOIN保留右表全部实际几乎不用改成换顺序的 LEFT JOIN 更易读右表为准CROSS JOIN纯笛卡尔积无 ON组合枚举1. INNER JOIN最常用SELECTs.name,c.nameAS班级,s.scoreFROMstudents sINNERJOINclasses cONs.class_idc.id;INNER可省略直接写JOIN就是 INNER。s、c是表别名多表查询必须用别名。2. LEFT JOIN查有没有的神器-- 列出所有班级以及各班的学生没人就显示 NULLSELECTc.nameAS班级,s.nameAS学生FROMclasses cLEFTJOINstudents sONs.class_idc.idORDERBYc.id;经典用法查不存在的——找出一门成绩都没有的学生反连接 / anti-joinSELECTs.nameFROMstudents sLEFTJOINscores scONsc.student_ids.idWHEREsc.idISNULL;-- 右表主键为 NULL ⇒ 没匹配上同理可查「没有任何学生的空班级」。这比NOT IN更快也更安全NOT IN遇到子查询里有NULL会返回空结果是个著名陷阱。3. ⚠️ ON 和 WHERE 的区别LEFT JOIN 最大坑-- A条件写在 ON 里 —— 先过滤右表再左连接。左表行全保留SELECTs.name,sc.scoreFROMstudents sLEFTJOINscores scONsc.student_ids.idANDsc.course数学;-- B条件写在 WHERE 里 —— 连接完再过滤NULL 行被干掉LEFT JOIN 退化成 INNER JOINSELECTs.name,sc.scoreFROMstudents sLEFTJOINscores scONsc.student_ids.idWHEREsc.course数学;A 会列出所有学生没数学成绩的显示 NULLB 只列出有数学成绩的学生。想保留左表全部过滤右表的条件必须放ON。4. 自连接SELF JOIN表自己和自己连用来做「同组内比较」-- 找出和张三同班的同学SELECTs2.nameFROMstudents s1JOINstudents s2ONs1.class_ids2.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_idc.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_idt.class_idWHEREs.scoret.avg_score;子查询放在FROM里叫派生表derived tableMySQL 8.x 会尽量把它合并到外层查询EXPLAIN 里看不到derived2就说明合并成功了。五、IN/EXISTS/JOIN怎么选-- 查有数学成绩的学生SELECTnameFROMstudentsWHEREidIN(SELECTstudent_idFROMscoresWHEREcourse数学);SELECTnameFROMstudents sWHEREEXISTS(SELECT1FROMscores scWHEREsc.student_ids.idANDsc.course数学);SELECTDISTINCTs.nameFROMstudents sJOINscores scONsc.student_ids.idWHEREsc.course数学;MySQL 8.4 会把IN子查询自动转成semi-jointable pullout / materialization / first match / loosescan / duplicate weedout 五种策略按代价选三者性能往往趋同。结论别背哪个一定快的口诀用EXPLAIN看实际计划。真正要避免的是相关子查询逐行执行EXPLAIN 里出现DEPENDENT SUBQUERY。第二部分索引与查询优化一、索引是什么InnoDB 的索引是BTree类比新华字典有索引 按拼音目录直接翻到那一页 → 查 1 次无索引 从第一页逐页翻 → 全表扫描type: ALLCREATEINDEXidx_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_id1-- ✅ 用索引WHEREclass_id1ANDscore80-- ✅ 用两列WHEREclass_id1ANDscore80ANDname张三-- ✅ 用三列WHEREscore80-- ❌ 跳过最左列索引失效WHEREclass_id1ANDname张三-- ⚠️ 只能用上 class_idname 被 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_id1;-- ✅ 覆盖索引查询的列全部在索引里MySQL 根本不用回表读主键行数据直接从索引树拿结果速度能差一个数量级。这也是为什么严禁SELECT *——一旦多查一个非索引列覆盖索引立刻失效。四、EXPLAIN 怎么看调优的核心工具EXPLAINSELECTs.nameFROMstudents sJOINscores scONsc.student_ids.id;重点看这几列列看点好坏排序type访问方式system/consteq_refrefrangeindexALLkey实际用到的索引为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用普通索引等值匹配rangeBETWEEN、IN、等范围扫描ALL全表扫描大表上的红色警报更狠的EXPLAIN ANALYZE普通 EXPLAIN 是估算8.0.18 的EXPLAIN ANALYZE会真的把查询跑一遍给出实际耗时和行数EXPLAINANALYZESELECTs.name,sc.scoreFROMstudents sJOINscores scONsc.student_ids.id;⚠️ 它真的会执行别在生产库对着慢查询反复跑。树形输出用EXPLAIN FORMATTREE看 JOIN 顺序和算法更直观。五、索引失效的 8 个典型场景背下来1.WHEREYEAR(created_at)2026-- ❌ 列上套函数WHEREcreated_at2026-01-01ANDcreated_at2027-01-01-- ✅ 改写成范围2.WHEREphone13800138000-- ❌ 隐式类型转换phone 是 VARCHAR 却传数字WHEREphone13800138000-- ✅ 类型一致这坑极其隐蔽且报错也不报3.WHEREnameLIKE%三-- ❌ 前导通配符WHEREnameLIKE张%-- ✅ 前缀匹配可用索引4.WHEREage120-- ❌ 列参与运算WHEREage19-- ✅5.WHEREa1ORb2-- ⚠️ 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 JoinMySQL 8.0.18 起引入、8.4 中默认启用哈希连接当关联列没有可用索引时优化器不再傻傻做嵌套循环而是把小表建哈希表、扫大表匹配Extra 里显示Using join buffer (hash join)。-- 强制/禁用哈希连接8.4 支持SELECT/* HASH_JOIN(t1, t2) */*FROMt1JOINt2ONt1.c1t2.c1;SELECT/* NO_HASH_JOIN(t1, t2) */*FROMt1JOINt2ONt1.c1t2.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*FROMstudentsWHEREid100000ORDERBYidLIMIT20;-- ✅ 直接定位八、8.4 里几个好用的索引新特性1. 不可见索引 —— 删索引前的安全气囊ALTERTABLEstudentsALTERINDEXidx_name INVISIBLE;-- 对优化器隐藏但仍维护-- 观察一段时间没问题后再 DROPALTERTABLEstudentsALTERINDEXidx_name VISIBLE;-- 秒级回滚删错一个大表索引重建可能要几小时设为 INVISIBLE 再改回 VISIBLE 是秒级的。用optimizer_switch的use_invisible_indexeson还能只对当前会话测试EXPLAINSELECT/* SET_VAR(optimizer_switchuse_invisible_indexeson) */*FROMstudentsWHEREname张三;2. 函数索引 —— 解决列上套函数就失效ALTERTABLEstudentsADDINDEXidx_year((YEAR(created_at)));-- 注意双括号SELECT*FROMstudentsWHEREYEAR(created_at)2026;-- 现在能走索引了双括号((...))是语法强制要求少了会报错。MySQL 内部其实是建了一个隐藏的虚拟生成列。3. 降序索引 —— 消灭Using filesortCREATEINDEXidx_score_descONstudents(scoreDESC,idASC);SELECT*FROMstudentsORDERBYscoreDESC,idASCLIMIT10;-- 直接走索引顺序4. 直方图 —— 数据倾斜时救优化器ANALYZETABLEstudentsUPDATEHISTOGRAMONclass_idWITH10BUCKETS;某些值占比极高时比如 90% 的学生都在 1 班普通索引统计会误判选择性直方图能显著提升执行计划的准确度。5. 冗余/无用索引清理SELECT*FROMsys.schema_unused_indexesWHEREobject_schemaschool;-- 从没被用过SELECT*FROMsys.schema_redundant_indexesWHEREtable_schemaschool;-- 被其他索引覆盖的冗余索引典型冗余已有(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 FORMATJSON的cost_info、optimizer_switch 调参SQL 实战题用这套 school 库做 10 道从易到难的查询练习 MySQL 8.4 进阶：多表 JOIN + 索引与查询优化
文章目录MySQL 8.4 进阶多表 JOIN 索引与查询优化第一部分多表 JOIN 查询一、先建三张表可直接跑二、JOIN 的本质先笛卡尔积再按 ON 过滤三、四种 JOIN 一张图记住1. INNER JOIN最常用2. LEFT JOIN查有没有的神器3. ⚠️ ON 和 WHERE 的区别LEFT JOIN 最大坑4. 自连接SELF JOIN四、JOIN 聚合真实业务的常见形态五、IN / EXISTS / JOIN 怎么选第二部分索引与查询优化一、索引是什么二、最左前缀原则复合索引的灵魂复合索引列顺序的黄金法则三、覆盖索引Extra 里出现 Using index 就是赢四、EXPLAIN 怎么看调优的核心工具更狠的EXPLAIN ANALYZE五、索引失效的 8 个典型场景背下来六、JOIN 的性能优化1. 关联列必须建索引第一优先级2. 8.4 默认开启 Hash Join3. 小表驱动大表4. 只 JOIN 需要的表、只 SELECT 需要的列七、深分页优化面试 实战高频八、8.4 里几个好用的索引新特性1. 不可见索引 —— 删索引前的安全气囊2. 函数索引 —— 解决列上套函数就失效3. 降序索引 —— 消灭 Using filesort4. 直方图 —— 数据倾斜时救优化器5. 冗余/无用索引清理九、一套可复用的调优流程下一步建议MySQL 8.4 进阶多表 JOIN 索引与查询优化这两件事其实是一条线JOIN 写不对结果错JOIN 没索引性能崩。下面用一套完整的示例库串起来讲。第一部分多表 JOIN 查询一、先建三张表可直接跑沿用上一节的school库再加两张表形成「班级 → 学生 → 成绩」的三级关系USEschool;CREATETABLEclasses(idINTPRIMARYKEY,nameVARCHAR(20)NOTNULL)ENGINEInnoDB;-- 给学生表补上 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))ENGINEInnoDB;INSERTINTOclassesVALUES(1,高一(1)班),(2,高一(2)班),(3,高一(3)班);UPDATEstudentsSETclass_id1WHEREidIN(1,2);UPDATEstudentsSETclass_id2WHEREidIN(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 一张图记住类型含义口诀INNER JOIN只保留两边都能匹配的行取交集LEFT JOIN保留左表全部右表没匹配补NULL左表为准RIGHT JOIN保留右表全部实际几乎不用改成换顺序的 LEFT JOIN 更易读右表为准CROSS JOIN纯笛卡尔积无 ON组合枚举1. INNER JOIN最常用SELECTs.name,c.nameAS班级,s.scoreFROMstudents sINNERJOINclasses cONs.class_idc.id;INNER可省略直接写JOIN就是 INNER。s、c是表别名多表查询必须用别名。2. LEFT JOIN查有没有的神器-- 列出所有班级以及各班的学生没人就显示 NULLSELECTc.nameAS班级,s.nameAS学生FROMclasses cLEFTJOINstudents sONs.class_idc.idORDERBYc.id;经典用法查不存在的——找出一门成绩都没有的学生反连接 / anti-joinSELECTs.nameFROMstudents sLEFTJOINscores scONsc.student_ids.idWHEREsc.idISNULL;-- 右表主键为 NULL ⇒ 没匹配上同理可查「没有任何学生的空班级」。这比NOT IN更快也更安全NOT IN遇到子查询里有NULL会返回空结果是个著名陷阱。3. ⚠️ ON 和 WHERE 的区别LEFT JOIN 最大坑-- A条件写在 ON 里 —— 先过滤右表再左连接。左表行全保留SELECTs.name,sc.scoreFROMstudents sLEFTJOINscores scONsc.student_ids.idANDsc.course数学;-- B条件写在 WHERE 里 —— 连接完再过滤NULL 行被干掉LEFT JOIN 退化成 INNER JOINSELECTs.name,sc.scoreFROMstudents sLEFTJOINscores scONsc.student_ids.idWHEREsc.course数学;A 会列出所有学生没数学成绩的显示 NULLB 只列出有数学成绩的学生。想保留左表全部过滤右表的条件必须放ON。4. 自连接SELF JOIN表自己和自己连用来做「同组内比较」-- 找出和张三同班的同学SELECTs2.nameFROMstudents s1JOINstudents s2ONs1.class_ids2.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_idc.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_idt.class_idWHEREs.scoret.avg_score;子查询放在FROM里叫派生表derived tableMySQL 8.x 会尽量把它合并到外层查询EXPLAIN 里看不到derived2就说明合并成功了。五、IN/EXISTS/JOIN怎么选-- 查有数学成绩的学生SELECTnameFROMstudentsWHEREidIN(SELECTstudent_idFROMscoresWHEREcourse数学);SELECTnameFROMstudents sWHEREEXISTS(SELECT1FROMscores scWHEREsc.student_ids.idANDsc.course数学);SELECTDISTINCTs.nameFROMstudents sJOINscores scONsc.student_ids.idWHEREsc.course数学;MySQL 8.4 会把IN子查询自动转成semi-jointable pullout / materialization / first match / loosescan / duplicate weedout 五种策略按代价选三者性能往往趋同。结论别背哪个一定快的口诀用EXPLAIN看实际计划。真正要避免的是相关子查询逐行执行EXPLAIN 里出现DEPENDENT SUBQUERY。第二部分索引与查询优化一、索引是什么InnoDB 的索引是BTree类比新华字典有索引 按拼音目录直接翻到那一页 → 查 1 次无索引 从第一页逐页翻 → 全表扫描type: ALLCREATEINDEXidx_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_id1-- ✅ 用索引WHEREclass_id1ANDscore80-- ✅ 用两列WHEREclass_id1ANDscore80ANDname张三-- ✅ 用三列WHEREscore80-- ❌ 跳过最左列索引失效WHEREclass_id1ANDname张三-- ⚠️ 只能用上 class_idname 被 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_id1;-- ✅ 覆盖索引查询的列全部在索引里MySQL 根本不用回表读主键行数据直接从索引树拿结果速度能差一个数量级。这也是为什么严禁SELECT *——一旦多查一个非索引列覆盖索引立刻失效。四、EXPLAIN 怎么看调优的核心工具EXPLAINSELECTs.nameFROMstudents sJOINscores scONsc.student_ids.id;重点看这几列列看点好坏排序type访问方式system/consteq_refrefrangeindexALLkey实际用到的索引为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用普通索引等值匹配rangeBETWEEN、IN、等范围扫描ALL全表扫描大表上的红色警报更狠的EXPLAIN ANALYZE普通 EXPLAIN 是估算8.0.18 的EXPLAIN ANALYZE会真的把查询跑一遍给出实际耗时和行数EXPLAINANALYZESELECTs.name,sc.scoreFROMstudents sJOINscores scONsc.student_ids.id;⚠️ 它真的会执行别在生产库对着慢查询反复跑。树形输出用EXPLAIN FORMATTREE看 JOIN 顺序和算法更直观。五、索引失效的 8 个典型场景背下来1.WHEREYEAR(created_at)2026-- ❌ 列上套函数WHEREcreated_at2026-01-01ANDcreated_at2027-01-01-- ✅ 改写成范围2.WHEREphone13800138000-- ❌ 隐式类型转换phone 是 VARCHAR 却传数字WHEREphone13800138000-- ✅ 类型一致这坑极其隐蔽且报错也不报3.WHEREnameLIKE%三-- ❌ 前导通配符WHEREnameLIKE张%-- ✅ 前缀匹配可用索引4.WHEREage120-- ❌ 列参与运算WHEREage19-- ✅5.WHEREa1ORb2-- ⚠️ 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 JoinMySQL 8.0.18 起引入、8.4 中默认启用哈希连接当关联列没有可用索引时优化器不再傻傻做嵌套循环而是把小表建哈希表、扫大表匹配Extra 里显示Using join buffer (hash join)。-- 强制/禁用哈希连接8.4 支持SELECT/* HASH_JOIN(t1, t2) */*FROMt1JOINt2ONt1.c1t2.c1;SELECT/* NO_HASH_JOIN(t1, t2) */*FROMt1JOINt2ONt1.c1t2.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*FROMstudentsWHEREid100000ORDERBYidLIMIT20;-- ✅ 直接定位八、8.4 里几个好用的索引新特性1. 不可见索引 —— 删索引前的安全气囊ALTERTABLEstudentsALTERINDEXidx_name INVISIBLE;-- 对优化器隐藏但仍维护-- 观察一段时间没问题后再 DROPALTERTABLEstudentsALTERINDEXidx_name VISIBLE;-- 秒级回滚删错一个大表索引重建可能要几小时设为 INVISIBLE 再改回 VISIBLE 是秒级的。用optimizer_switch的use_invisible_indexeson还能只对当前会话测试EXPLAINSELECT/* SET_VAR(optimizer_switchuse_invisible_indexeson) */*FROMstudentsWHEREname张三;2. 函数索引 —— 解决列上套函数就失效ALTERTABLEstudentsADDINDEXidx_year((YEAR(created_at)));-- 注意双括号SELECT*FROMstudentsWHEREYEAR(created_at)2026;-- 现在能走索引了双括号((...))是语法强制要求少了会报错。MySQL 内部其实是建了一个隐藏的虚拟生成列。3. 降序索引 —— 消灭Using filesortCREATEINDEXidx_score_descONstudents(scoreDESC,idASC);SELECT*FROMstudentsORDERBYscoreDESC,idASCLIMIT10;-- 直接走索引顺序4. 直方图 —— 数据倾斜时救优化器ANALYZETABLEstudentsUPDATEHISTOGRAMONclass_idWITH10BUCKETS;某些值占比极高时比如 90% 的学生都在 1 班普通索引统计会误判选择性直方图能显著提升执行计划的准确度。5. 冗余/无用索引清理SELECT*FROMsys.schema_unused_indexesWHEREobject_schemaschool;-- 从没被用过SELECT*FROMsys.schema_redundant_indexesWHEREtable_schemaschool;-- 被其他索引覆盖的冗余索引典型冗余已有(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 FORMATJSON的cost_info、optimizer_switch 调参SQL 实战题用这套 school 库做 10 道从易到难的查询练习