SQL Server查询优化建议

发布于：2025-05-24 ⋅ 阅读:(16) ⋅ 点赞:(0)

对以下sql查询语句进行优化：

        select
        qgm_ID as no, qgm_CreateDate as createDatetime, qgm_HandlerID as handlerId, qgm_ApplyDept as deptId,
        qgm_KS as deptSubmitPerson, qgm_KSIdea as deptSubmitOpinion, qgm_KSDate as deptSubmitDatetime,
        qgm_Total as total, qgm_State as state, qgm_Remark as remark
        from QGMaster

        inner join (
        select distinct
        qgd_MasterID
        from QGDetail
        where qgd_Bought = 1 and qgd_IsRk = 0) as t on qgm_ID = qgd_MasterID

        where qgm_ID not in(
        select distinct
        rkd_QGDMASTERID from RKDetail
        inner join RKMaster on rkm_ID = rkd_MasterID
        where rkm_State = 0)

        order by qgm_ID

以下是优化后的SQL语句及说明：

        SELECT
        qgm.qgm_ID AS no,
        qgm.qgm_CreateDate AS createDatetime,
        qgm.qgm_HandlerID AS handlerId,
        qgm.qgm_ApplyDept AS deptId,
        qgm.qgm_KS AS deptSubmitPerson,
        qgm.qgm_KSIdea AS deptSubmitOpinion,
        qgm.qgm_KSDate AS deptSubmitDatetime,
        qgm.qgm_Total AS total,
        qgm.qgm_State AS state,
        qgm.qgm_Remark AS remark
        FROM QGMaster qgm
        -- 改用 EXISTS 替代 DISTINCT 子查询（更高效）
        WHERE EXISTS (
        SELECT 1
        FROM QGDetail qgd
        WHERE
        qgd.qgd_MasterID = qgm.qgm_ID
        AND qgd.qgd_Bought = 1
        AND qgd_IsRk = 0
        )
        -- 改用 NOT EXISTS 替代 NOT IN（更安全高效）
        AND NOT EXISTS (
        SELECT 1
        FROM RKDetail rkd
        INNER JOIN RKMaster rkm ON rkd.rkd_MasterID = rkm.rkm_ID
        WHERE
        rkd.rkd_QGDMASTERID = qgm.qgm_ID
        AND rkm.rkm_State = 0
        )
        ORDER BY qgm.qgm_ID

优化说明：

  1. 消除DISTINCT操作

    • 原INNER JOIN (SELECT DISTINCT...)改为EXISTS，当只需要判断存在性时，EXISTS在找到第一条匹配记录后就会停止扫描，效率高于DISTINCT

  2. 转换NOT IN为NOT EXISTS

    • 当子查询结果可能包含NULL值时，NOT IN会有逻辑问题，NOT EXISTS更安全

    • 关联条件写在子查询内部，便于利用索引

  3. 建议添加索引（根据实际数据量选择）：

    sql

    复制

    下载

    -- QGDetail表
CREATE INDEX IX_QGDetail_Bought_IsRk ON QGDetail(qgd_Bought, qgd_IsRk) INCLUDE (qgd_MasterID)

-- RKMaster表
CREATE INDEX IX_RKMaster_State ON RKMaster(rkm_State) INCLUDE (rkm_ID)

-- RKDetail表
CREATE INDEX IX_RKDetail_QGDMasterID ON RKDetail(rkd_QGDMASTERID) INCLUDE (rkd_MasterID)

-- QGMaster表
CREATE INDEX IX_QGMaster_ID ON QGMaster(qgm_ID)

  4. 其他优化点：

    • 显式指定表别名，提高可读性和维护性

    • 去除不必要的子查询嵌套

    • 确保所有关联字段（如qgm_ID、rkm_ID等）有适当索引

  5. 执行计划优化：

    • 建议在实际环境中查看执行计划，重点关注：

    • 是否出现Table Scan（全表扫描）

    • 索引的实际使用情况

    • 各个操作的预估行数与实际行数差异

优化后优势：

  1. 减少不必要的DISTINCT排序开销

  2. 使用更高效的EXISTS/NOT EXISTS逻辑

  3. 更清晰的查询逻辑结构

  4. 更好的索引利用潜力

注意事项：

  1. 优化前后请验证结果集一致性

  2. 索引创建需考虑写操作频率，避免过度索引

  3. 如果数据量极大，可考虑分区表等进阶优化手段

  4. 定期更新统计信息保证执行计划准确性

可以通过以下语句验证是否仍有全表扫描：

sql

复制

下载

SET STATISTICS PROFILE ON
-- 执行优化后的查询
SET STATISTICS PROFILE OFF

这是 SQL Server 生成的详细查询执行计划（通过 SET STATISTICS PROFILE ON 获取）。我们可以通过以下方式分层解读：

核心结构解读

  1. 执行顺序（从内向外）：

    sql

    复制

    下载

    主查询
|-- Merge Join (Left Anti Semi Join)       -- 主连接逻辑
    |-- Merge Join (Inner Join)           -- EXISTS 子查询部分
    |    |-- Clustered Index Scan(QGMaster)
    |    |-- Sort(DISTINCT)               -- 处理 QGDetail 的条件
    |         |-- Table Scan(QGDetail)    -- 扫描 QGDetail 表
    |
    |-- Sort(DISTINCT)                    -- 处理 NOT EXISTS 子查询
         |-- Hash Match(Inner Join)       -- 连接 RKMaster 和 RKDetail
              |-- Clustered Index Scan(RKMaster)
              |-- Clustered Index Scan(RKDetail)

关键节点分析

1. 主查询 Merge Join (Left Anti Semi Join)

  • 作用：将 QGMaster 与 NOT EXISTS 子查询结果进行反连接（排除匹配记录）

  • 关键指标：

    • Estimated Rows = 432.6392：优化器预估返回约 433 行

    • Actual Rows = 225：实际返回 225 行（说明统计信息可能有偏差）

2. EXISTS 子查询部分

  • Merge Join (Inner Join)：

    • 将 QGMaster 与 QGDetail 通过 qgm_ID = qgd_MasterID 合并

    • Clustered Index Scan(QGMaster)：

      • 扫描主键索引 PK__QGMaster__403A8C7D

      • 未使用 WHERE 过滤，全表扫描 1139 行

    • Sort(DISTINCT)：

      • 对 QGDetail 结果去重排序（qgd_Bought=1 AND qgd_IsRk=0）

      • Table Scan(QGDetail)：

        • 全表扫描 761 行，过滤后返回 21 行

        • 问题：缺少索引导致全表扫描

3. NOT EXISTS 子查询部分

  • Sort(DISTINCT)：

    • 对 RKDetail 结果去重排序

    • Hash Match(Inner Join)：

      • 连接 RKMaster 和 RKDetail（rkm_ID = rkd_MasterID）

      • Clustered Index Scan(RKMaster)：

        • 使用主键索引，通过 rkm_State=0 过滤，返回 26 行

      • Clustered Index Scan(RKDetail)：

        • 全表扫描 968 行

性能瓶颈分析

  1. 全表扫描问题：

    • QGDetail 表（WHERE qgd_Bought=1 AND qgd_IsRk=0）：

      • 缺少覆盖索引，导致全表扫描（Table Scan）

    • RKDetail 表：

      • 全表扫描 968 行，没有利用 rkd_QGDMASTERID 过滤

  2. 排序开销：

    • 两个 Sort(DISTINCT) 操作（预估成本占比高）：

      • 原因为子查询中需要去重

  3. 统计信息偏差：

    • 主查询预估返回 433 行，实际返回 225 行，说明统计信息需要更新

优化建议

1. 添加缺失索引

sql

复制

下载

-- QGDetail 表（解决 WHERE 条件和避免排序）
CREATE NONCLUSTERED INDEX IX_QGDetail_Filter
ON QGDetail (qgd_Bought, qgd_IsRk)
INCLUDE (qgd_MasterID);

-- RKDetail 表（加速 NOT EXISTS 子查询）
CREATE NONCLUSTERED INDEX IX_RKDetail_QGDMasterID
ON RKDetail (rkd_QGDMASTERID)
INCLUDE (rkd_MasterID);
2. 更新统计信息

sql

复制

下载

UPDATE STATISTICS QGMaster;
UPDATE STATISTICS QGDetail;
UPDATE STATISTICS RKMaster;
UPDATE STATISTICS RKDetail;
3. 验证索引效果

优化后重点关注：

  • QGDetail 是否使用 Index Seek 替代 Table Scan

  • Sort 操作是否消失（通过索引排序避免额外排序）

执行计划关键术语

术语 说明
Merge Join 通过排序后的数据集进行高效合并
Left Anti Semi Join 排除主表中与子查询匹配的记录（NOT EXISTS 逻辑）
Clustered Index Scan 扫描聚簇索引（通常是全表扫描）
Hash Match 通过哈希表实现连接（适合大数据量）
Estimated Rows 优化器预估的行数（与实际行数差异可能影响执行计划选择）

最终结论

当前执行计划的主要问题集中在 QGDetail 和 RKDetail 的全表扫描 和 冗余排序 上。通过添加建议的索引，可以显著减少 I/O 和排序开销。优化后需重新查看执行计划，确认是否出现 Index Seek 和 Merge Join 的改进效果。

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布