SQL Server 查询优化方案_sqlserver 优化查询语句

一、问题分析

当前查询涉及多表关联（`AA_Product_data_Details`、`U8CUSTDEF_0005_E001`、`KCSaleOutList`、`KCOtherOutList`等）、子查询嵌套、聚合计算（`SUM`）及日期筛选，性能瓶颈可能源于：

1. 缺少索引导致关联和过滤操作全表扫描；

2. 子查询重复执行（如多次对`KCSaleOutList`等表聚合）；

3. 多表关联逻辑复杂，JOIN顺序不合理；

4. 日期转换函数（如`CONVERT(CHAR(7), ddate, 120)`）导致索引失效。

二、优化步骤

1. 索引优化（核心）

为频繁过滤、关联、排序的字段创建索引，避免全表扫描。

2. 子查询优化：合并重复计算

原查询中多个子查询（如出库、入库数据）重复扫描相同表，建议通过CTE（公用表表达式） 或临时表预计算聚合结果，减少重复IO。

优化示例（以出库数据为例）：

-- 提前计算所有出库单的聚合结果，避免多次UNION ALL和SUM

WITH CTE_Out AS (

SELECT

CINVCODE,

DDATE,

SUM(iQuantity) AS iQuantity

FROM (

SELECT CINVCODE, DDATE, iQuantity

FROM KCSaleOutList WITH (NOLOCK)

WHERE cVouchType = N'32'

AND ISNULL(bIsSTQc, 0) <> 1

AND ISNULL(bPuFirst, 0) <> 1

AND ISNULL(bIAFirst, 0) <> 1

AND ISNULL(bOMFirst, 0) <> 1

AND cwhcode = '001'

UNION ALL

SELECT CINVCODE, DDATE, iQuantity FROM KCOtherOutList WITH (NOLOCK) WHERE cwhcode = '001'

UNION ALL

SELECT CINVCODE, DDATE, iQuantity FROM RecordOutList WITH (NOLOCK) WHERE cwhcode = '001'

) t

GROUP BY CINVCODE, DDATE

),

-- 同理优化入库单聚合（CTE_In）、期初结存（CTE_Dis）等

CTE_In AS (...),

CTE_Dis AS (

SELECT CINVCODE, qimojiechunshuliang

FROM AA_receive_dispatch WITH (NOLOCK)

WHERE CONVERT(CHAR(7), ddate, 120) = '2025-01' -- 提前过滤日期

)

-- 主查询直接引用CTE，减少重复计算

SELECT ...

FROM AA_Product_data_Details d

LEFT JOIN CTE_Out b ON a.CINVCODE = b.CINVCODE AND a.ddate = b.DDATE

3. 简化日期筛选，避免函数失效索引

原查询中`CONVERT(CHAR(7), ddate, 120) = '2025-01'`会导致`ddate`索引失效，改为范围筛选：

-- 原写法：函数转换导致索引失效

WHERE CONVERT(CHAR(7), ddate, 120) = '2025-01'

-- 修改为：直接比较日期范围（利用ddate索引）

WHERE ddate >= '2025-01-01' AND ddate < '2025-02-01'

4. 减少JOIN数量及关联复杂度.

- 移除无用关联：若`U8CUSTDEF_0005_E001`表仅用于获取`remark`且非必需，可考虑按需查询或延迟加载;

- 明确JOIN类型：使用`INNER JOIN`代替`LEFT JOIN`（若逻辑允许)，减少无效行参与后续计算;

-例如：若`AA_Product_data_Details`与`CINVCODE_ddate`为一对一关系，可改为`INNER JOIN`.

####. 聚合后关联，减少数据量原查询先关联后聚合（`GROUP BY`在主查询），可改为子查询内先聚合，再关联主表，减少参与JOIN的数据量：```sql-- 原逻辑: 关联大量明细后聚合

SELECT ... SUM(chukushu) ...

FROM (大量明细关联) a

GROUP BY ...

-- 修改为:子查询内聚合，再关联

SELECT a.CINVCODE, ... SUM(b.iQuantity) AS chukushu ...

FROM (基础信息表) a

LEFT JOIN (子查询聚合结果) b ON a.CINVCODE = b.CINVCODE

GROUP BY ... -- 此时聚合的数据量已大幅减少

6. 其他细节优化.-. 避免`SELECT *`:仅查询必要字段（当前已符合，无需修改）;

- 使用`NOLOCK`谨慎 :虽可减少锁等待，但可能读取未提交数据，根据业务场景选择;

- 更新统计信息 :执行`UPDATE STATISTICS 表名`，确保SQL Server生成最优执行计划.

三、优化后完整SQL示例（精简版）```sql

WITH CTE_Out AS (-- 预计算出库聚合

SELECT CINVCODE, DDATE, SUM(iQuantity) AS iQuantity

FROM (

SELECT CINVCODE, DDATE, iQuantity

FROM KCSaleOutList WITH (NOLOCK)

WHERE cVouchType = N'32' AND ISNULL(bIsSTQc, 0) <> 1

AND ISNULL(bPuFirst, 0) <> 1 AND ISNULL(bIAFirst, 0) <> 1

AND ISNULL(bOMFirst, 0) <> 1 AND cwhcode = '001'

UNION ALL

SELECT CINVCODE, DDATE, iQuantity FROM KCOtherOutList WITH (NOLOCK) WHERE cwhcode = '001'

UNION ALL

SELECT CINVCODE, DDATE, iQuantity FROM RecordOutList WITH (NOLOCK) WHERE cwhcode = '001'

) t

GROUP BY CINVCODE, DDATE

),

CTE_In AS (-- 预计算入库聚合

SELECT CINVCODE, DDATE, SUM(iQuantity) AS iQuantity

FROM (...) t GROUP BY CINVCODE, DDATE

),

CTE_Dis AS (-- 预计算期初结存

SELECT CINVCODE, qimojiechunshuliang

FROM AA_receive_dispatch WITH (NOLOCK)

WHERE ddate >= '2025-01-01' AND ddate < '2025-02-01'

)

SELECT

a.CINVCODE, a.cinvname, a.cInvStd, a.storage_space,

CONVERT(CHAR(7), a.ddate, 120) AS 年月,

SUM(b.iQuantity) AS chukushu,

SUM(c.iQuantity) AS rukushu,

a.xiancunliang, a.customer, a.cinvmnemcode,

a.qimojiechunshuliang, a.remark, c.在制, b_wj.未结数

FROM (

SELECT

d.CINVCODE, ff.cinvname, ff.cInvStd, d.storage_space,

a.ddate, ddd.iQuantity AS xiancunliang, d.customer,

ff.cinvmnemcode, dis.qimojiechunshuliang, ee.remark

FROM AA_Product_data_Details d

INNER JOIN CINVCODE_ddate a ON d.CINVCODE = a.CINVCODE

LEFT JOIN U8CUSTDEF_0005_E001 ee ON d.CINVCODE = ee.cun_huo_bian_ma

LEFT JOIN CTE_Dis dis ON a.CINVCODE = dis.CINVCODE

LEFT JOIN (-- 现存量子查询（已优化聚合）

SELECT CINVCODE, SUM(iQuantity) AS iQuantity

FROM v_ST_currentstockForReport WITH (NOLOCK)

WHERE cWhCode = '001' GROUP BY CINVCODE

) ddd ON a.CINVCODE = ddd.CINVCODE

LEFT JOIN inventory ff ON a.CINVCODE = ff.cinvcode

WHERE a.ddate >= '2025-01-01' AND a.ddate < '2025-02-01'

) a

LEFT JOIN CTE_Out b ON a.CINVCODE = b.CINVCODE AND a.ddate = b.DDATE

LEFT JOIN CTE_In c ON a.CINVCODE = c.CINVCODE AND a.ddate = c.DDATE

LEFT JOIN Inventory_View_Short b_wj ON a.CINVCODE = b_wj.存货编码

LEFT JOIN v_shichangyuji_stock sc ON a.CINVCODE = sc.cInvCode

GROUP BY a.CINVCODE, ...-- GROUP BY字段（与原查询一致

四、效果预期

- 索引优化可将关联/过滤操作从全表扫描（O(n)）降为索引查找（O(log n)）；

- CTE预聚合减少重复计算，降低IO开销；

- 日期范围筛选使索引有效利用，过滤速度提升5-10倍；

- 整体性能预计提升 50%-200%,具体取决于数据量和服务器配置 .

五、后续建议

1. 执行计划分析 :通过SSMS的“显示估计的执行计划”（Ctrl+L），定位剩余瓶颈；

2. 分表分库 :若数据量极大（千万级以上），考虑按`CINVCODE`或日期分表；

3. 监控性能 :使用SQL Server Profiler或Extended Events跟踪慢查询，持续优化.

通过以上步骤，可显著提升查询效率，避免因数据量增长导致的性能恶化.