你提供的 SQL 是在 **达梦数据库（DM Database）** 上运行的，用于从多个订单表中联合查询并计算“未交付重量”(`confmWt`)，最终实现分页展示。当前 SQL 存在一些性能瓶颈点，尤其是在大数据量下容易变慢。
---
## ✅ 目标：**提升该 SQL 在达梦数据库中的查询速度**
我们将从以下几个方面进行优化：
1. **SQL 逻辑结构分析**
2. **索引优化建议**
3. **执行计划调优**
4. **减少重复子查询与 UNION 开销**
5. **达梦特有优化技巧**
---
### 🔍 一、原始 SQL 分析
```sql
SELECT ROWNUM AS ID, A.*
FROM (
SELECT
PROD_CLASS_CODE, SG_SIGN, PSR_CODE, PROD_CODE, PROD_CNAME,
ORDER_THICK, ORDER_WIDTH, ORDER_LEN,
<if test='sosoa4.lenDivCodeFlag!="N"'> LEN_DIV_CODE, SPEC_DESC, </if>
EXP_FLAG,
SUM(CONFM_WT - GREATEST(MOVE_WT, DELIVY_WT)) AS "confmWt"
FROM (
-- 主体数据：QZA/QSZ 类型订单
SELECT ... FROM TSOSOA5 A5, TSOSOA4 A4, TSOSOA1 A1 WHERE ...
UNION ALL
-- 补充数据：CZA 关联的 QYA 类型订单
SELECT ... FROM TSOSOA5 A5, TSOSOA4 A4, TSOSOA1 A1 WHERE ...
) SUB
GROUP BY 各字段
HAVING SUM(...) != 0
) A;
```
#### ⚠️ 性能问题点：
| 问题 | 描述 |
|------|------|
| `UNION ALL` + 多表连接 | 数据膨胀严重，尤其无索引时扫描巨大 |
| 缺少明确 `JOIN` 写法 | 使用隐式连接（逗号），可读性和优化器识别差 |
| `ROWNUM` 模拟分页 | 达梦中 `ROWNUM` 先生成再过滤，无法下推 |
| `GROUP BY` 字段过多 | 容易导致临时排序/磁盘溢出 |
| `GREATEST(MOVE_WT, DELIVY_WT)` | 函数表达式阻碍索引使用 |
| `IFNULL(...)` 嵌套多层 | 增加 CPU 计算负担 |
---
### ✅ 二、优化后的 SQL（适配达梦数据库）
```sql
SELECT
ROWNUM AS ID,
T.PROD_CLASS_CODE,
T.SG_SIGN,
T.PSR_CODE,
T.PROD_CODE,
T.PROD_CNAME,
T.ORDER_THICK,
T.ORDER_WIDTH,
T.ORDER_LEN,
<if test='sosoa4.lenDivCodeFlag != "N"'>
T.LEN_DIV_CODE,
T.SPEC_DESC,
</if>
T.EXP_FLAG,
T.confmWt
FROM (
SELECT
a4.PROD_CLASS_CODE,
a4.SG_SIGN,
a1.PSR_CODE,
a4.PROD_CODE,
a4.PROD_CNAME,
a4.ORDER_THICK,
a4.ORDER_WIDTH,
a4.ORDER_LEN,
<if test='sosoa4.lenDivCodeFlag != "N"'>
a4.LEN_DIV_CODE,
a4.SPEC_DESC,
</if>
a1.EXP_FLAG,
SUM(
COALESCE(a5.CONFM_WT, 0) -
GREATEST(COALESCE(a5.MOVE_WT, 0),
COALESCE(
CASE
WHEN a5.TRANS_MODE_CODE = '23'
AND a1.EXP_FLAG = '0'
AND a1.SALE_DEPT_CODE = '00105'
AND a4.USE_STATUS = '1'
AND a1.ORDER_STATUS = '67'
THEN a5.MOVE_WT
ELSE a5.DELIVY_WT
END, 0)
)
) AS confmWt
FROM SO.TSOSOA1 a1
INNER JOIN SO.TSOSOA4 a4 ON a1.CONTRACT_NO = a4.CONTRACT_NO
INNER JOIN SO.TSOSOA5 a5 ON a4.ORDER_NO = a5.ORDER_NO
WHERE
a1.ORDER_TYPE_CODE IN ('QZA', 'QSZ')
<!-- 动态条件 -->
<include refid="querya1a4a5ByWhere"/>
GROUP BY
a4.PROD_CLASS_CODE, a4.SG_SIGN, a1.PSR_CODE, a4.PROD_CODE,
a4.PROD_CNAME, a4.ORDER_THICK, a4.ORDER_WIDTH, a4.ORDER_LEN,
<if test='sosoa4.lenDivCodeFlag != "N"'>
a4.LEN_DIV_CODE, a4.SPEC_DESC,
</if>
a1.EXP_FLAG
UNION ALL
SELECT
a4.PROD_CLASS_CODE,
a4.SG_SIGN,
a1.PSR_CODE,
a4.PROD_CODE,
a4.PROD_CNAME,
a4.ORDER_THICK,
a4.ORDER_WIDTH,
a4.ORDER_LEN,
<if test='sosoa4.lenDivCodeFlag != "N"'>
a4.LEN_DIV_CODE,
a4.SPEC_DESC,
</if>
a1.EXP_FLAG,
SUM(
COALESCE(a5.CONFM_WT, 0) -
COALESCE((
SELECT SUM(COALESCE(sub5.DELIVY_WT, 0))
FROM SO.TSOSOA4 sub4
INNER JOIN SO.TSOSOA5 sub5 ON sub4.ORDER_NO = sub5.ORDER_NO
WHERE sub4.PRE_ORDER_NO = a4.ORDER_NO
AND sub4.ORDER_TYPE_CODE = 'CZA'
), 0)
) AS confmWt
FROM SO.TSOSOA1 a1
INNER JOIN SO.TSOSOA4 a4 ON a1.CONTRACT_NO = a4.CONTRACT_NO
INNER JOIN SO.TSOSOA5 a5 ON a4.ORDER_NO = a5.ORDER_NO
WHERE
a1.ORDER_TYPE_CODE = 'QYA'
<!-- 动态条件 -->
<include refid="querya1a4a5ByWhere"/>
GROUP BY
a4.PROD_CLASS_CODE, a4.SG_SIGN, a1.PSR_CODE, a4.PROD_CODE,
a4.PROD_CNAME, a4.ORDER_THICK, a4.ORDER_WIDTH, a4.ORDER_LEN,
<if test='sosoa4.lenDivCodeFlag != "N"'>
a4.LEN_DIV_CODE, a4.SPEC_DESC,
</if>
a1.EXP_FLAG
) T
WHERE T.confmWt != 0;
```
> 📌 注意：达梦不支持 `LIMIT/OFFSET`，但支持 `ROWNUM` 实现分页。
---
### ✅ 三、添加分页支持（推荐方式）
由于你在 Java 层传入了 `Page<Soso00Query>` 对象，建议将分页逻辑也集成进 SQL，避免全量加载。
```sql
SELECT * FROM (
SELECT
ROWNUM AS RN,
V.*
FROM (
-- 上面的 T 查询结果（不含 ROWNUM）
) V
WHERE ROWNUM <= #{page.current} * #{page.size}
)
WHERE RN > (#{page.current} - 1) * #{page.size};
```
这可以显著减少返回数据量。
---
### ✅ 四、关键优化措施总结
| 优化项 | 措施 | 效果 |
|--------|------|------|
| **改用显式 JOIN** | `INNER JOIN` 替代 `,` 隐式连接 | 提高可读性 & 优化器选择效率 |
| **使用 COALESCE 替代 IFNULL** | 达梦兼容标准函数 | 更通用且性能略好 |
| **提前 WHERE 过滤** | 将 `ORDER_TYPE_CODE IN (...)` 放入主查询 | 减少参与 JOIN 的数据量 |
| **拆分 UNION ALL 查询** | 不同业务类型分开处理 | 易于分别建立索引 |
| **合理创建复合索引** | 下方重点说明 |
---
### ✅ 五、达梦数据库索引优化建议
为提升性能，请在以下字段上创建复合索引：
#### 1. `TSOSOA1` 表
```sql
CREATE INDEX IDX_A1_CONTRACT_OTYPE_EXP ON SO.TSOSOA1 (CONTRACT_NO, ORDER_TYPE_CODE, EXP_FLAG);
-- 或更细粒度
CREATE INDEX IDX_A1_OTYPE_STATUS ON SO.TSOSOA1 (ORDER_TYPE_CODE, ORDER_STATUS, SALE_DEPT_CODE);
```
#### 2. `TSOSOA4` 表
```sql
CREATE INDEX IDX_A4_ORDER_PRE ON SO.TSOSOA4 (ORDER_NO, PRE_ORDER_NO, CONTRACT_NO, ORDER_TYPE_CODE);
CREATE INDEX IDX_A4_PRODCLS ON SO.TSOSOA4 (PROD_CLASS_CODE, PROD_CODE, USE_STATUS);
```
#### 3. `TSOSOA5` 表
```sql
CREATE INDEX IDX_A5_ORDER_MOVE_DELIVY ON SO.TSOSOA5 (ORDER_NO, CONFM_WT, MOVE_WT, DELIVY_WT, TRANS_MODE_CODE);
```
> 💡 原理：让每个 `JOIN` 和 `WHERE` 条件都能命中索引，避免全表扫描。
---
### ✅ 六、其他达梦优化技巧
| 技巧 | 说明 |
|------|------|
| **启用执行计划查看** | 使用 `EXPLAIN` 查看是否走索引：<br>`EXPLAIN SELECT ...` |
| **统计信息更新** | 定期执行：<br>`CALL SP_UPDATE_STATISTICS('SO', 'TSOSOA1');` |
| **增大 SORT_BUF_SIZE / WORKER_THREADS** | 提升排序和并行能力（需 DBA 配合） |
| **避免 SELECT \*** | 只查需要字段，减少 IO |
| **使用物化视图（高级）** | 若此查询频繁，可建 MV 预计算结果 |
---
### ✅ 七、Java 层配合优化建议
回到最初的方法，如果你能在 SQL 层就把 `dynamicStock` 算出来，就可以彻底删除循环中调用 `lmService.querySumMatWt()` 的部分！
例如，在 SQL 中直接关联动态库存表：
```sql
LEFT JOIN DYNAMIC_STOCK_VIEW ds
ON a4.PLANT = ds.PLANT
AND a4.MATERIAL = ds.MATERIAL
AND a4.BATCH = ds.BATCH
```
这样就能一步到位，无需 N+1 查询。
---
###
理解Shell脚本中的$?
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
