LabelChange-server/.trae/documents/SQL查询性能优化方案.md

SQL查询性能优化方案

优化目标

将最新运营监控.sql和客户维度运营监控.sql的查询耗时从当前的2分钟降低到30秒以内

性能瓶颈分析

当前SQL执行慢的主要原因：

1. 多表关联复杂度高：存在多次LEFT JOIN、CROSS JOIN，关联数据量较大
2. 重复扫描同一张表：多个CTE独立扫描label_scan_history、label_replace_requests表，重复IO开销大
3. 子查询效率低：部分指标使用EXISTS子查询，逐行判断效率低
4. 索引缺失：常用关联字段、过滤字段缺少有效索引，查询时全表扫描
5. 计算逻辑重复：日期转换、维度判断等逻辑在多处重复计算

优化方案（按优先级排序）

方案一：索引优化（实施成本最低，效果最明显）

需创建的索引：

表名	索引字段	用途
label_scan_history	NeutralWaybillNumber, CreatedAt, Result, Description	覆盖扫描记录的关联、过滤、统计需求，避免回表
label_scan_history	CreatedAt, Result, NeutralWaybillNumber	覆盖独立统计CTE的统计需求，直接从索引获取统计数据
label_replace_requests	MasterPackageNumber, BillOfLadingNumber, customerid, LabelRetrievedAt	覆盖订单表的关联、过滤需求
arrival_handover_forms	HandoverNumber	覆盖交接单匹配关联需求
customers	Id, CustomerCode	覆盖客户维度关联需求

所有索引均为组合索引，实现查询全覆盖，避免回表查询

方案二：SQL逻辑优化（无额外开发成本，仅修改SQL结构）

1. 合并重复CTE：将多个独立扫描label_scan_history的CTE合并为一个，一次性计算所有扫描相关指标（成功数、失败数、STOP数、扫描数），减少表扫描次数
2. 替换CROSS JOIN：将DistinctDates CROSS JOIN OrderFullInfo改为更高效的关联方式，减少笛卡尔积计算量
3. 移除不必要的逻辑：删除冗余的判断条件和重复计算逻辑
4. 替换EXISTS子查询：将指标统计中的EXISTS子查询改为预计算的关联方式

方案三：中间汇总表方案（适合准实时场景，性能提升最大）

创建定时任务（每15分钟/每小时执行一次），预计算以下中间结果：

1. daily_scan_stats：每日扫描统计结果（日期、扫描数、成功数、失败数、STOP数）
2. daily_order_stats：每日订单统计结果（日期、新增换单数、应该换单数、标签推送数等）
3. customer_daily_stats：客户维度每日统计结果 查询时直接读取预计算的汇总表，查询耗时可降低到秒级

方案四：物化视图方案（适合MySQL 8.0+版本）

创建物化视图预计算常用的统计维度，自动刷新数据，查询时直接读取物化视图

实施步骤

第一步：先实施索引优化（1小时内完成）

1. 创建上述所有建议的组合索引
2. 重新执行SQL测试性能，预计可降低50%以上的耗时

第二步：实施SQL逻辑优化（2小时内完成）

1. 重构SQL结构，合并重复CTE
2. 优化关联逻辑和子查询
3. 测试验证数据准确性和性能提升

第三步：（可选）实施中间汇总表方案（半天内完成）

1. 设计汇总表结构
2. 开发定时汇总脚本
3. 修改查询SQL读取汇总表

预期效果

• 仅实施索引+SQL逻辑优化：查询耗时可降低到30-60秒
• 实施中间汇总表方案：查询耗时可降低到1-5秒

验证标准

1. 查询耗时≤30秒
2. 统计结果和原SQL完全一致
3. 无业务逻辑偏差