如何优化统计两张关联表数据的TSQL语句

SQL查询优化方案 #

原有写法性能痛点 #

现有实现对ISPRange根表执行了2次独立的关联扫描，重复执行CIDR范围匹配逻辑，存在冗余计算开销。

优化后查询语句 #

通过合并关联逻辑+条件聚合的方式，将2次根表扫描合并为1次，大幅降低计算量：

SELECT 
    R.ID,
    ISNULL(I.IpWithIncidents, 0) AS IpWithIncidents,
    ISNULL(I.IpWithOutIncident, 0) AS IpWithOutIncident
FROM [dbo].[ISPRange] AS R
OUTER APPLY (
    SELECT
        COUNT(DISTINCT CASE WHEN src = 'incident' THEN CIDR END) AS IpWithIncidents,
        COUNT(DISTINCT CASE WHEN src = 'visit' THEN CIDR END) AS IpWithOutIncident
    FROM (
        -- 合并两张关联表的有效数据
        SELECT [CIDR], 'incident' AS src FROM [dbo].[Incidents]
        UNION ALL
        SELECT [CIDR], 'visit' AS src FROM [dbo].[VisitStats] WHERE [Incident] = 0
    ) AS t
    WHERE t.CIDR BETWEEN R.CIDR_FROM AND R.CIDR_TILL
) AS I
WHERE (R.ID = @RangeId OR @RangeId IS NULL)

额外索引优化建议 #

可配合索引进一步提升查询性能：

• 为Incidents表创建CIDR单列索引，覆盖查询所需字段，避免全表扫描
• 为VisitStats表创建(Incident, CIDR)联合索引，可直接命中过滤条件同时获取CIDR数据
• 为ISPRange表创建(CIDR_FROM, CIDR_TILL, ID)联合索引，加快范围匹配效率

如果业务数据量较大，还可以预计算CIDR所属的ISP Range ID，写入Incidents和VisitStats表中固化关联关系，避免每次查询都执行范围匹配操作，性能可提升数倍。

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