159 lines
7.4 KiB
Markdown
159 lines
7.4 KiB
Markdown
# SQL 运营监控查询性能优化方案
|
||
|
||
## 问题分析
|
||
|
||
当前查询耗时约 2 分钟，主要性能瓶颈来自以下几个方面：
|
||
|
||
### 瓶颈一：CROSS JOIN 笛卡尔积（最严重）
|
||
|
||
```sql
|
||
-- DailyMetrics 中：
|
||
FROM DistinctDates dd
|
||
CROSS JOIN OrderFullInfo ofi
|
||
```
|
||
|
||
`DistinctDates` 有 N 行（假设 60 天就是 60 行），`OrderFullInfo` 有 M 行（假设 7000 条订单），则这个 CROSS JOIN 产生 **60 × 7000 = 420,000 行**的笛卡尔积，然后再对每一行做 5 个 `COUNT(DISTINCT CASE ...)` 聚合，计算量极大。
|
||
|
||
### 瓶颈二：OrderFullInfo 被多次物化
|
||
|
||
`OrderFullInfo` 这个 CTE 在 `AllDates`（步骤 10）中被引用了 **5 次**，然后在 `DailyMetrics` 里又被 CROSS JOIN 引用一次。MySQL 对 CTE 不做缓存（非 Materialized Hint），每次引用都会重新执行整个计算链路（从 `FormRequestRelation → FormTotalOrderCount → FormLabelPushTimes → FormFirstQualifiedDate → FormLabelRateAndQualifiedDate → OrderAssessment → OrderScanStatus → OrderFullInfo`），造成严重的重复计算。
|
||
|
||
### 瓶颈三：label_scan_history 被扫描多次
|
||
|
||
`label_scan_history` 表在以下 CTE 中被反复全表扫描：
|
||
- `FormFirstScan`：JOIN scan history
|
||
- `OrderScanStatus`：GROUP BY NeutralWaybillNumber
|
||
- `DailyScanCount`：COUNT 全表
|
||
- `DailySuccessCount`：WHERE Result=0
|
||
- `DailyFailCount`：子查询 + GROUP BY
|
||
- `DailyStopCount`：WHERE Description LIKE '%STOP%'
|
||
- `AllDates`：UNION 中三次引用
|
||
|
||
共计 **7+ 次**扫描，而 `Description LIKE '%成功返回STOP标签%'` 是前缀通配符，无法使用索引。
|
||
|
||
### 瓶颈四：GREATEST() 中重复计算 AssessmentBaseTime
|
||
|
||
`OrderAssessment` 中，`GREATEST(CASE...END, flr.FirstQualifiedTime_UTC5)` 这个表达式在同一行被计算了 **3 次**（用于 AssessmentBaseTime、HOUR() 判断、DATE() 计算），每次都重新计算。
|
||
|
||
### 瓶颈五：FormRequestRelation 双 LEFT JOIN + COALESCE
|
||
|
||
```sql
|
||
LEFT JOIN ArrivalForms f_m ON r.MasterPackageNumber = f_m.HandoverNumber
|
||
LEFT JOIN ArrivalForms f_b ON r.BillOfLadingNumber = f_b.HandoverNumber
|
||
WHERE COALESCE(f_m.FormId, f_b.FormId) IS NOT NULL
|
||
```
|
||
|
||
`arrival_handover_forms` 被扫描两次，虽然 `HandoverNumber` 有唯一索引，但 `MasterPackageNumber` 和 `BillOfLadingNumber` 在 `label_replace_requests` 上**没有索引**，导致这两个 JOIN 是全表扫描。
|
||
|
||
### 瓶颈六：AllDates 中 UNION 包含冗余子查询
|
||
|
||
`AllDates` 的 UNION 中有 8 个子查询，其中多个查 `label_scan_history` 的日期，结果存在大量重复，但又必须 UNION 去重，造成额外的排序和去重开销。
|
||
|
||
---
|
||
|
||
## 优化方案：使用物化临时表（存储过程）
|
||
|
||
### 选择方案：存储过程 + 临时表
|
||
|
||
**理由**：
|
||
- 普通视图（VIEW）无法缓存中间结果，MySQL 会每次重新执行，对复杂多步 CTE 无帮助
|
||
- 物化视图（MySQL 不原生支持）需要额外维护
|
||
- **存储过程 + 临时表** 是 MySQL 中最有效的方式：将每个 CTE 的结果显式写入临时表，并在关键列上建索引，彻底消除重复计算和 CROSS JOIN 笛卡尔积问题
|
||
|
||
### 优化要点
|
||
|
||
#### 1. 消除 CROSS JOIN 笛卡尔积
|
||
将 `DailyMetrics` 的计算方式从"日期 × 订单 CROSS JOIN"改为"按订单数据聚合"：
|
||
- 每个订单的 `ReceiptDate`、`LabelRetrievedDate_UTC5`、`AssessmentBaseTime`、`AssessmentTime`、`FirstSuccessDate_UTC5` 都是已知的固定值
|
||
- 改用 **预先按订单计算每个指标所属的日期范围**，再按日期 GROUP BY 汇总，避免笛卡尔积
|
||
|
||
#### 2. 将 OrderFullInfo 写入临时表并建索引
|
||
```sql
|
||
CREATE TEMPORARY TABLE tmp_order_full_info (...);
|
||
-- 建索引：
|
||
ALTER TABLE tmp_order_full_info ADD INDEX idx_receipt_date (ReceiptDate);
|
||
ALTER TABLE tmp_order_full_info ADD INDEX idx_label_date (LabelRetrievedDate_UTC5);
|
||
ALTER TABLE tmp_order_full_info ADD INDEX idx_success_date (FirstSuccessDate_UTC5);
|
||
ALTER TABLE tmp_order_full_info ADD INDEX idx_assessment_base_date (AssessmentBaseDate);
|
||
ALTER TABLE tmp_order_full_info ADD INDEX idx_assessment_date (AssessmentDate);
|
||
```
|
||
|
||
#### 3. 将 label_scan_history 的聚合结果写入临时表
|
||
将 `OrderScanStatus`、`DailyScanCount`、`DailySuccessCount`、`DailyFailCount`、`DailyStopCount` 提前计算并缓存到临时表，避免多次扫描 `label_scan_history`。
|
||
|
||
#### 4. 为 label_replace_requests 补充关联字段索引
|
||
在 `label_replace_requests` 上为 `BillOfLadingNumber` 和 `MasterPackageNumber` 添加索引，加速 `FormRequestRelation` 的 JOIN。
|
||
|
||
#### 5. 提前计算 AssessmentBaseTime，避免重复计算
|
||
在 `OrderAssessment` 阶段先计算出 `AssessmentBaseTime`，后续直接引用，不再重复展开 CASE WHEN。
|
||
|
||
---
|
||
|
||
## 实施步骤
|
||
|
||
### 步骤 1：添加缺失的索引（针对 BillOfLadingNumber 和 MasterPackageNumber）
|
||
|
||
新建文件：`database/migrations/003_add_join_indexes.sql`
|
||
|
||
```sql
|
||
-- 为 label_replace_requests 添加 JOIN 关联字段索引
|
||
ALTER TABLE label_replace_requests
|
||
ADD INDEX IF NOT EXISTS idx_bill_of_lading_number (BillOfLadingNumber),
|
||
ADD INDEX IF NOT EXISTS idx_master_package_number (MasterPackageNumber);
|
||
```
|
||
|
||
### 步骤 2：将 最新运营监控.sql 重写为存储过程
|
||
|
||
新建文件：`database/migrations/003_create_sp_operations_monitor.sql`
|
||
|
||
存储过程逻辑：
|
||
1. 建临时表 `tmp_scan_agg`（label_scan_history 聚合，按 NeutralWaybillNumber）
|
||
2. 建临时表 `tmp_daily_scan`（每日扫描统计，包含 扫描数/成功数/失败数/STOP数）
|
||
3. 建临时表 `tmp_form_order`（FormRequestRelation 的结果，含交接单信息）
|
||
4. 建临时表 `tmp_form_stats`（每个 FormId 的 TotalOrderCount、QualifyNeedCount、FirstQualifiedTime）
|
||
5. 建临时表 `tmp_order_full`（OrderFullInfo 结果，含 AssessmentBaseTime、AssessmentTime、IsSuccess 等所有字段）
|
||
6. 在 `tmp_order_full` 上建必要的日期索引
|
||
7. 直接按日期聚合计算各项指标，输出最终结果（无 CROSS JOIN）
|
||
|
||
调用方式：`CALL sp_GetOperationsMonitor();`
|
||
|
||
### 步骤 3：新建调用文件（不修改原文件）
|
||
|
||
新建文件：`运营监控_优化版.sql`
|
||
|
||
内容为：`CALL sp_GetOperationsMonitor();`，作为新的日常使用入口，原 `最新运营监控.sql` 保持不变。
|
||
|
||
---
|
||
|
||
## 预期优化效果
|
||
|
||
| 优化点 | 优化前 | 优化后 |
|
||
|---|---|---|
|
||
| CROSS JOIN 笛卡尔积 | N日期 × M订单行 | 消除，直接按订单聚合 |
|
||
| OrderFullInfo 重复计算 | 6+ 次 | 1 次，写入临时表 |
|
||
| label_scan_history 扫描次数 | 7+ 次 | 2 次（一次聚合，一次日统计） |
|
||
| BillOfLadingNumber JOIN | 全表扫描 | 索引扫描 |
|
||
| MasterPackageNumber JOIN | 全表扫描 | 索引扫描 |
|
||
| AssessmentBaseTime 重复计算 | 3 次 | 1 次 |
|
||
|
||
**预期查询时间：从 120s 降至 5~20s**（取决于数据量）。
|
||
|
||
---
|
||
|
||
## 文件变更清单
|
||
|
||
| 操作 | 文件路径 | 说明 |
|
||
|---|---|---|
|
||
| 新建 | `database/migrations/003_add_join_indexes.sql` | 添加 BillOfLadingNumber、MasterPackageNumber 索引 |
|
||
| 新建 | `database/migrations/004_create_sp_operations_monitor.sql` | 创建存储过程 `sp_GetOperationsMonitor` |
|
||
| 新建 | `运营监控_优化版.sql` | 调用存储过程的入口文件（原文件保持不变） |
|
||
|
||
---
|
||
|
||
## 注意事项
|
||
|
||
- 存储过程使用 `DROP TEMPORARY TABLE IF EXISTS` 开头清理，保证每次调用都是全量最新数据
|
||
- 临时表生命周期仅限本次连接，不占用持久存储
|
||
- 索引使用 `ADD INDEX IF NOT EXISTS`（MySQL 8.0 支持）
|
||
- 原 `最新运营监控.sql` 文件不做任何修改，保留作为参考
|