LabelChange-server/.trae/documents/运营监控SQL性能优化方案.md

运营监控SQL性能优化方案

一、性能瓶颈分析

当前 最新运营监控.sql 查询耗时约2分钟，核心瓶颈如下：

1.1 CROSS JOIN 笛卡尔积（最大瓶颈）

DailyMetrics CTE 使用 CROSS JOIN OrderFullInfo ofi，对每个日期 × 每个订单进行全量计算。假设有90天 × 5万订单 = 450万行中间结果，导致：

• 巨大的内存消耗和临时表生成
• 每行都要做日期比较、CONVERT_TZ转换
• COUNT(DISTINCT ...) 去重开销巨大

1.2 无日期范围过滤

整条SQL对三张表做全量扫描，没有任何 WHERE 条件限制时间范围：

• label_replace_requests 全表扫描
• label_scan_history 全表扫描
• arrival_handover_forms 全表扫描

1.3 重复的 CONVERT_TZ 调用

CONVERT_TZ 在每行上执行，且出现在多个CTE中重复计算：

• LabelRetrievedAt 的时区转换在 LabelRequests、OrderFullInfo、DailyMetrics 中重复
• CreatedAt 的时区转换在 OrderAssessment、DailyScanCount 等多处重复
• CONVERT_TZ 结果无法使用索引（不可 sargable）

1.4 缺失关键索引

表	缺失索引	影响
arrival_handover_forms	ReceiptTime	到仓日期筛选全表扫描
label_replace_requests	BillOfLadingNumber	大箱号JOIN全表扫描
label_replace_requests	MasterPackageNumber	提单号JOIN全表扫描
label_replace_requests	LabelRetrievedAt	标签推送时间筛选全表扫描
label_scan_history	(Result, CreatedAt) 复合	扫描统计无法高效过滤

1.5 AllDates CTE 的9个UNION

AllDates CTE 对 OrderFullInfo 做了5次UNION + 对 label_scan_history 做3次UNION + 对 label_replace_requests 做1次UNION，每次都要重新扫描和转换时区。

1.6 窗口函数开销

FormLabelPushTimes 中的 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 对所有有标签的订单做排序分区，数据量大时开销显著。

---

二、优化方案（四级递进）

方案一：索引优化（预计提升 30-50%，无需改SQL）

-- 1. arrival_handover_forms 补充索引
ALTER TABLE arrival_handover_forms ADD INDEX idx_receipt_time (ReceiptTime);

-- 2. label_replace_requests 补充索引
ALTER TABLE label_replace_requests ADD INDEX idx_bill_of_lading (BillOfLadingNumber);
ALTER TABLE label_replace_requests ADD INDEX idx_master_package (MasterPackageNumber);
ALTER TABLE label_replace_requests ADD INDEX idx_label_retrieved_at (LabelRetrievedAt);
ALTER TABLE label_replace_requests ADD INDEX idx_label_status_created (LabelRetrievedAt, CreatedAt);

-- 3. label_scan_history 补充复合索引
ALTER TABLE label_scan_history ADD INDEX idx_result_created_at (Result, CreatedAt);
ALTER TABLE label_scan_history ADD INDEX idx_created_at_result (CreatedAt, Result);

方案二：SQL逻辑重写（预计提升 60-80%，与方案一叠加）

核心改动：

2.1 消除 CROSS JOIN —— 改为按日期分组聚合

-- 原来的写法（笛卡尔积）：
-- FROM DistinctDates dd CROSS JOIN OrderFullInfo ofi GROUP BY dd.日期

-- 优化后：直接从 OrderFullInfo 按日期维度聚合，不生成日期列表
-- 历史指标用 GROUP BY 日期，当天指标用子查询

2.2 限制日期范围，避免全量扫描

-- 只查最近90天数据（根据业务需求可调整）
WHERE r.CreatedAt >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 95 DAY)
  OR r.LabelRetrievedAt >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 95 DAY)

2.3 消除 AllDates CTE

用简单的日期范围表替代9个UNION：

-- 用递归CTE生成日期序列，替代 AllDates
WITH RECURSIVE DateRange AS (
    SELECT DATE(DATE_SUB(UTC_TIMESTAMP() - INTERVAL 5 HOUR, INTERVAL 89 DAY)) AS 日期
    UNION ALL
    SELECT DATE_ADD(日期, INTERVAL 1 DAY) FROM DateRange WHERE 日期 < DATE(UTC_TIMESTAMP() - INTERVAL 5 HOUR)
)

2.4 CONVERT_TZ 优化 —— 用 UTC 时间范围过滤

-- 原来的写法（不可利用索引）：
WHERE DATE(CONVERT_TZ(CreatedAt, '+00:00', '-05:00')) = '2026-05-20'

-- 优化后（先算出UTC范围，直接用索引）：
WHERE CreatedAt >= '2026-05-20 05:00:00'  -- UTC-5的00:00 = UTC的05:00
  AND CreatedAt < '2026-05-21 05:00:00'

2.5 合并独立的扫描统计CTE

将 DailyScanCount、DailySuccessCount、DailyFailCount、DailyStopCount 合并为一个CTE：

DailyScanAgg AS (
    SELECT
        DATE(CONVERT_TZ(CreatedAt, '+00:00', '-05:00')) AS 日期,
        COUNT(*) AS 当日扫描数,
        COUNT(DISTINCT CASE WHEN Result = 0 THEN NeutralWaybillNumber END) AS 当日换单完成数,
        COUNT(DISTINCT CASE WHEN Result = 0 AND Description LIKE '%成功返回STOP标签%' THEN NeutralWaybillNumber END) AS 当日STOP数
    FROM label_scan_history
    WHERE CreatedAt >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 95 DAY)
    GROUP BY DATE(CONVERT_TZ(CreatedAt, '+00:00', '-05:00'))
)

方案三：中间汇总表 + 定时刷新（预计查询时间 < 5秒，推荐方案）

创建 daily_metrics_summary 汇总表，存储预计算结果：

3.1 建表

CREATE TABLE IF NOT EXISTS daily_metrics_summary (
    日期 DATE NOT NULL PRIMARY KEY,
    当天新增换单数 INT DEFAULT 0,
    累计要换的总单数 INT DEFAULT 0,
    当天应该换单数 INT DEFAULT 0,
    当日换单完成数 INT DEFAULT 0,
    当日换单失败数 INT DEFAULT 0,
    当日STOP数 INT DEFAULT 0,
    24小时换单成功数 INT DEFAULT 0,
    当日标签推送数 INT DEFAULT 0,
    当日扫描数 INT DEFAULT 0,
    当天换单完成率 VARCHAR(20) DEFAULT '0.00%',
    24小时换单率 VARCHAR(20) DEFAULT '0.00%',
    数据拉取时间 DATETIME,
    计算耗时毫秒 INT DEFAULT 0,
    INDEX idx_日期 (日期)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3.2 刷新存储过程

-- 刷新历史日期（T-1及之前）的数据，一次计算永久缓存
-- 当天数据：可每5分钟刷新一次，或按需刷新
-- 查询时直接 SELECT * FROM daily_metrics_summary ORDER BY 日期 DESC

3.3 定时调度

• 历史日期：首次全量计算后，无需再刷新
• 当天日期：通过 MySQL Event 或应用层定时任务，每5分钟调用一次刷新
• 业务变更（如订单状态变化）：仅重算受影响的日期

方案四：混合方案（当天实时 + 历史缓存，查询时间 < 1秒）

查询逻辑：
1. 历史日期 → 直接查 daily_metrics_summary（预计算结果）
2. 当天日期 → 执行轻量级实时查询（仅当天数据）
3. 合并返回

这样可以做到：

• 历史数据毫秒级响应
• 当天数据5-10秒响应
• 整体响应时间 < 10秒

---

三、推荐实施路径

阶段	方案	预期效果	工作量
第一阶段	方案一（索引）+ 方案二（SQL重写）	2分钟 → 20-40秒	低
第二阶段	方案三（汇总表）	查询 < 5秒	中
第三阶段	方案四（混合方案）	查询 < 1秒	中高

---

四、实施方案细节

第一阶段实施步骤

1. 执行索引创建SQL（方案一）
2. 重写SQL逻辑（方案二）：
   • 新建 最新运营监控_优化版.sql 文件
   • 消除 CROSS JOIN，改为按日期分组聚合
   • 合并4个扫描统计CTE为1个
   • 用递归CTE替代 AllDates 的9个UNION
   • 所有时间过滤改为UTC范围条件
   • 添加90天日期限制
3. 验证结果一致性：对比原SQL和新SQL的输出

第二阶段实施步骤

1. 创建汇总表 daily_metrics_summary
2. 创建存储过程 sp_RefreshDailyMetrics：
   • 入参：p_date DATE（刷新指定日期）
   • 逻辑：将优化版SQL的结果INSERT/UPDATE到汇总表
   • 历史日期只算一次，当天日期可重复刷新
3. 创建定时任务：
   • MySQL Event 每天凌晨1点自动刷新昨天的数据
   • 应用层可按需调用刷新当天数据
4. 修改查询接口：
   • 查询改为 SELECT * FROM daily_metrics_summary WHERE 日期 BETWEEN ? AND ?
   • 响应时间降至毫秒级

第三阶段实施步骤

1. 修改C#服务层 MetricsCalculationService
2. 查询逻辑改为：
   • 历史日期查 daily_metrics_summary
   • 当天日期执行轻量级实时SQL
3. 合并返回给前端

---

五、优化版SQL核心改动说明

5.1 消除CROSS JOIN的核心思路

原SQL：

DistinctDates × OrderFullInfo → GROUP BY 日期 → 聚合

问题：笛卡尔积爆炸

优化后：

OrderFullInfo → GROUP BY 各日期维度 → 分别聚合

思路：每个指标直接按其对应的日期维度GROUP BY，不需要先生成日期列表再CROSS JOIN。

• 当天新增换单数 → 按到仓日期 GROUP BY
• 当天应该换单数 → 按考核基准日期或考核截止日期 GROUP BY
• 当日标签推送数 → 按标签推送日期 GROUP BY
• 累计要换的总单数 → 使用窗口函数或子查询
• 24H完成数 → 按首次成功日期 GROUP BY

5.2 日期范围限制

在最早的CTE（ArrivalForms、LabelRequests）中就加入日期过滤，后续CTE自动缩小范围：

-- 只查最近90天的数据
WHERE ReceiptTime >= DATE_SUB(CONVERT_TZ(UTC_TIMESTAMP(), '+00:00', '-05:00'), INTERVAL 90 DAY)

5.3 CONVERT_TZ → UTC范围过滤

将 DATE(CONVERT_TZ(col, '+00:00', '-05:00')) = target_date 改为 col >= UTC_START AND col < UTC_END，使索引可用。