Mysql中慢 SQL 优化实战：减少不必要的 JOIN 关联

Mysql中慢 SQL 优化实战减少不必要的 JOIN 关联一、问题背景线上接口查询收货单的出库物流信息时出现慢 SQL。该 SQL 通过三表 JOIN收货单主表 → 物流记录表 → 物流节点明细表查询物流轨迹数据其中收货单主表数据量超过 1200 万行。注博客https://blog.csdn.net/badao_liumang_qizhi二、优化流程1. 定位慢 SQL通过慢查询日志或 APM 监控系统获取到慢 SQL 的完整文本、执行时间和调用链路trace-id。2. 分析 SQL 结构原始 SQL 的执行结构receiving_goods_record (主表, 1200w) INNER JOIN store_receiving_logistics_record (物流记录表) LEFT JOIN store_receiving_logistics_record_item (物流节点明细表)3. 识别冗余 JOINsql 中直接可以从表中获取数据并且主表中的 record_code 字段是有索引的。分析发现 SELECT 中从主表取的字段record_code、order_code在物流记录表中同样存在且数据一致关联主表纯属冗余。4. 数据一致性验证通过 SQL 查询验证两表字段的数据一致性不一致记录数 0确认去掉 JOIN 不会导致数据差异。5. 修改并测试修改 Mapper XML去掉冗余 JOIN本地启动服务验证接口正常返回。三、核心技术点3.1 EXPLAIN 执行计划EXPLAINSELECT...FROMtable_a aINNERJOINtable_b bONa.idb.a_idWHEREa.codexxx;关键指标字段含义关注点type访问类型ALL(全表扫描) → ref(索引) → const(常量)key实际使用的索引NULL 表示未走索引rows预估扫描行数数值越小越好Extra额外信息Using filesort / Using temporary 需关注3.2 索引命中原则-- 单列索引WHERE 条件精确匹配时命中CREATEINDEXidx_record_codeONlogistics_record(record_code);SELECT*FROMlogistics_recordWHERErecord_codexxx;-- 命中-- 联合索引遵循最左前缀原则CREATEINDEXidx_member_codeONlogistics_record(member_id,order_code);SELECT*FROMlogistics_recordWHEREmember_id1;-- 命中SELECT*FROMlogistics_recordWHEREmember_id1ANDorder_codex;-- 命中SELECT*FROMlogistics_recordWHEREorder_codex;-- 不命中3.3 JOIN 的代价每增加一个 JOINMySQL 需要根据驱动表的结果集逐行去被驱动表查找匹配行如果被驱动表没有合适索引会产生全表扫描结果集膨胀 → 内存消耗增大 → 排序代价增大四、优化原理4.1 减少 JOIN 表数量这是本次优化的核心原理。当你发现SELECT 中从 A 表取的字段在 B 表中也有相同的数据冗余存储/数据同步字段就可以去掉对 A 表的关联直接从 B 表取字段。优化前三表SELECTa.name,b.detail,c.itemFROMbig_table a-- 1000w 行INNERJOINmedium_table bONa.idb.a_idLEFTJOINsmall_table cONb.idc.b_idWHEREa.codexxx;优化后两表SELECTb.name,b.detail,c.item-- name 字段从 b 表直接取FROMmedium_table bLEFTJOINsmall_table cONb.idc.b_idWHEREb.codexxx;-- b 表的 code 有索引4.2 驱动表选择MySQL 的 JOIN 执行顺序由优化器决定但通常小表驱动大表性能更优WHERE 条件能快速过滤的表适合作为驱动表五、常见的慢 SQL 优化方案方案一消除冗余 JOIN本次使用适用场景JOIN 的表只是为了取几个字段而这些字段在其他已关联的表中也有。-- 优化前三表 JOIN 只为取 order 表的 order_noSELECTo.order_no,d.product_name,d.qtyFROMorders oINNERJOINorder_details dONo.idd.order_idWHEREo.id12345;-- 优化后order_details 表本身冗余存储了 order_noSELECTd.order_no,d.product_name,d.qtyFROMorder_details dWHEREd.order_id12345;方案二添加合适索引适用场景WHERE 条件或 JOIN 条件的字段没有索引。-- 慢查询order_code 无索引全表扫描SELECT*FROMshipment_detailWHEREmember_id1179109ANDorder_codeER.240418.000673;-- 添加联合索引CREATEINDEXidx_member_orderONshipment_detail(member_id,order_code);方案三改写子查询为 JOIN适用场景IN 子查询在大数据量下性能差。-- 优化前子查询SELECT*FROMordersWHEREcustomer_idIN(SELECTidFROMcustomersWHERElevelVIP);-- 优化后改写为 JOINSELECTo.*FROMorders oINNERJOINcustomers cONo.customer_idc.idWHEREc.levelVIP;方案四分页优化延迟关联适用场景深分页 LIMIT offset, size 越往后越慢。-- 优化前LIMIT 100000, 10 需要扫描 100010 行SELECT*FROMordersORDERBYidDESCLIMIT100000,10;-- 优化后先定位 ID 区间再取数据SELECT*FROMordersWHEREid(SELECTidFROMordersORDERBYidDESCLIMIT100000,1)ORDERBYidDESCLIMIT10;方案五大表数据归档适用场景因为某表数据越来越多导致执行 sql 越来越慢因为 sql 暂时无法优化所以先按照备份数据的方式处理把指定之间之前的数据全部备份到备份表中一年一张表。-- 按年归档将历史数据迁移到归档表INSERTINTOxxx_2025SELECT*FROMxxxWHEREaccount_time2026-01;DELETEFROMxxxWHEREaccount_time2026-01;-- 或使用 pt-archiver 等工具无锁归档方案六为大表新增索引Online DDL适用场景因为只使用创建时间查询数据所以需要增加无锁变更把 create_time 增加索引表数据共 5000w。-- MySQL 5.6 支持 Online DDL不阻塞 DMLALTERTABLEstore_inbound_masterADDINDEXidx_create_time(create_time),ALGORITHMINPLACE,LOCKNONE;-- 或使用 gh-ost / pt-online-schema-change 进行无锁变更六、优化效果评估维度优化前优化后JOIN 表数量3 表2 表驱动表数据量1200w (receiving_goods_record)470w (store_receiving_logistics_record)索引使用orm.record_code 走唯一索引后再 JOINsdlr.record_code 直接走索引网络/内存开销需传输主表行数据无冗余数据传输七、总结检查清单在遇到慢 SQL 时按以下顺序排查是否有不必要的 JOIN— 分析 SELECT 字段来源确认是否可以从已有表获取WHERE/JOIN 条件是否走索引— 用 EXPLAIN 确认必要时添加索引是否有子查询可改写— IN 子查询改为 JOIN结果集是否过大— 增加过滤条件、分页优化表数据量是否可以缩减— 历史数据归档、分表