MySQL基于Sequence自连接查询相邻行前后SKU的性能优化
现有查询慢的核心原因
- 三层表关联+逐行触发的相关子查询结构:整个查询逐行匹配B1的每一条数据,都要单独执行一次B3的MIN子查询找最小的相邻Sequence,找到之后还要再连一次B2取SKU,相当于5万行数据要触发5万次子查询、完成三层嵌套循环关联,关联判断量达到千万级,出现数分钟耗时属于正常情况。
- 之前建的联合索引完全无效:把
Sequence放在最左列的(Sequence, Shipment Number, Pallet Number)索引,不符合查询的过滤逻辑——查询的时候是先固定Shipment Number和Pallet Number的值,再在同组内匹配Sequence范围,最左匹配原则下这个索引根本没法快速缩小筛选范围,相当于白建。 - 原SQL存在书写笔误:字段名写成了带尾点的
Sequence.,就算MySQL能容错解析,也会额外增加语法解析、字段隐式匹配的开销。
可落地的优化方案
1. 重建匹配逻辑的覆盖索引
原来的(Shipment Number, Pallet Number, Sequence)索引虽然顺序对,但因为没有包含查询需要的SKU字段,查到匹配行之后还要回表取SKU值,有额外的IO开销。直接把SKU加进索引,做成不需要回表的覆盖索引:
-- 删除之前建错的无效索引 ALTER TABLE Builder DROP INDEX IF EXISTS `idx_sequence_shipment_pallet`; -- 创建覆盖索引,包含所有查询需要的字段 ALTER TABLE Builder ADD INDEX `idx_shipment_pallet_seq_sku` (`Shipment Number`, `Pallet Number`, `Sequence`, `SKU`);
2. 用MySQL 8.0原生窗口函数重写查询
MySQL 8.0自带的LEAD/LAG窗口函数,就是专门用来做分组内相邻行取值计算的,单次扫描就能完成所有分组、排序、相邻值计算,完全不需要自连接和逐行触发子查询。
优化后的SQL写法:
SELECT B.`SKU` AS current_sku, B.`Shipment Number`, B.`Pallet Number`, B.`Sequence`, -- 取同组下Sequence排序后的下一行SKU,无匹配行返回NULL LEAD(B.`SKU`, 1) OVER w AS next_row_sku, -- 取同组下的前序相邻行SKU LAG(B.`SKU`, 1) OVER w AS prev_row_sku FROM Builder B -- 定义公共窗口规则,减少重复代码 WINDOW w AS ( PARTITION BY B.`Shipment Number`, B.`Pallet Number` ORDER BY B.`Sequence` );
优化效果说明
优化完成后,整个查询全程走覆盖索引扫描,没有回表IO、没有嵌套循环关联、没有逐行触发的子查询,5万行规模的数据集基本可以做到百毫秒级返回,性能比原写法提升几个数量级。
注意:如果同一个
Shipment Number+Pallet Number分组下存在重复的Sequence值,需要先做去重处理,否则会导致相邻行取值计算偏差。
内容的提问来源于stack exchange,提问作者fanderbiles
相关产品推荐
相关产品推荐

