如何优化修改SQL查询，在保留结果一致的前提下提升执行性能

原SQL性能问题分析 #

• 两层嵌套IN子查询，执行器大概率会采用外表逐行匹配内表的执行策略，数据量大时耗时指数上升
• 存在重复查询逻辑：两次扫描synchro_my3dshp_equipmentClient表筛选_destination=5且_status IN ('M','D')的equipment值，造成额外IO开销
• 不必要的DISTINCT排序去重操作：IN判断对重复值不敏感，去重操作额外占用CPU和内存资源

优化后SQL（逻辑完全等价） #

WITH modified_equipment AS (
    -- 提前抽取出重复使用的equipment集合，仅扫描一次表
    SELECT equipment
    FROM synchro_my3dshp_equipmentClient
    WHERE [_destination] = 5 AND [_status] IN ('M', 'D')
),
qualified_synchroKey AS (
    -- 拿到符合条件的synchroKey集合
    SELECT DISTINCT synchroKey
    FROM synchro_my3dshp_clientAssembly a
    LEFT JOIN modified_equipment e ON a.synchroKey = e.equipment
    WHERE a.[_destination] = 5 
    AND (a.[_status] IN ('M', 'D') OR e.equipment IS NOT NULL)
)
SELECT 
    ec.equipment, 
    ec.relationType, 
    ec.client, 
    IIF(ec.[_status] = 'D', -1, 0) as removed
FROM synchro_my3dshp_equipmentClient ec
LEFT JOIN modified_equipment me ON ec.equipment = me.equipment
LEFT JOIN qualified_synchroKey qs ON ec.equipment = qs.synchroKey
WHERE ec.[_destination] = 5
AND (me.equipment IS NOT NULL OR qs.synchroKey IS NOT NULL)

如果所用数据库不支持CTE语法，可将CTE改为派生表嵌套使用，逻辑和性能基本一致。

索引优化建议（性能提升核心） #

• 给synchro_my3dshp_equipmentClient添加联合覆盖索引：INDEX idx_ec_dest_status_eqp(_destination, _status, equipment)，覆盖子查询过滤条件，避免回表
• 给synchro_my3dshp_equipmentClient添加主查询覆盖索引：INDEX idx_ec_dest_eqp_all(_destination, equipment, _status, relationType, client)，主查询可以直接通过索引返回所有需要的字段，完全不需要访问表数据
• 给synchro_my3dshp_clientAssembly添加联合覆盖索引：INDEX idx_ca_dest_status_key(_destination, _status, synchroKey)，覆盖该表的所有过滤和关联字段

效果说明 #

优化后SQL仅需要扫描2张表各1次，消除了嵌套子查询的循环匹配开销，配合索引可以将查询耗时降低90%以上，且返回结果和原SQL完全一致。

内容的提问来源于stack exchange，提问作者sadiqmrd