新增INNER JOIN为何导致SQL执行时间与结果量剧增？

核心原因预判 #

新增的INNER JOIN [dbo].DEALER_INFORMATION di ON d.DEALER_ID = di.DEALER_ID引发问题，大概率是一对多关联导致结果集膨胀，或关联字段无索引引发全表扫描，以下是只读权限下可执行的排查步骤：

1. 验证经销商与信息表的关联关系是否为一对多 #

执行以下SQL，查看原查询涉及的经销商在信息表中是否对应多条记录：

SELECT d.DEALER_ID, COUNT(di.DEALER_ID) AS info_record_count
FROM [dbo].DEALER d
INNER JOIN [dbo].DEALER_INFORMATION di ON d.DEALER_ID = di.DEALER_ID
WHERE EXISTS (
    SELECT 1 FROM [dbo].DEALER_SALES ds 
    WHERE ds.DEALER_ID = d.DEALER_ID 
    AND ds.Sales IS NOT NULL
    AND EXISTS (
        SELECT 1 FROM [dbo].MODEL_OPTIONS mo
        INNER JOIN [dbo].MANUFACTURER m ON mo.MANUFACTURER_ID = m.MANUFACTURER_ID
        WHERE mo.DEALER_MARKET_ID = ds.DEALER_MARKET_ID
        AND m.countryID = 12
    )
)
GROUP BY d.DEALER_ID
HAVING COUNT(di.DEALER_ID) > 1
ORDER BY info_record_count DESC

• 若返回结果，说明单个经销商对应多条信息记录，原查询的每条结果会被重复关联，直接导致总结果量翻倍甚至数倍增长，同时增加数据库关联计算量，拖慢查询。

2. 检查DEALER_INFORMATION表的索引情况 #

即使无法创建索引，也可查询现有索引确认关联字段是否有优化：

SELECT 
    i.name AS index_name,
    c.name AS column_name
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE OBJECT_NAME(i.object_id) = 'DEALER_INFORMATION'
AND c.name = 'DEALER_ID'

• 若查询无结果，说明DEALER_ID字段无索引，数据库做关联时需全表扫描，当信息表数据量较大时，会大幅增加查询耗时。

3. 对比原查询与新查询的执行计划 #

在SQL Server中，可通过以下语句查看执行计划（只读权限通常允许此操作）：

-- 查看原查询执行计划
SET SHOWPLAN_XML ON;
GO
SELECT *  
FROM [dbo].[MODEL_OPTIONS] mo
INNER JOIN [dbo].MANUFACTURER m ON mo.MANUFACTURER_ID = m.MANUFACTURER_ID
INNER JOIN [dbo].DEALER_SALES ds ON mo.DEALER_MARKET_ID = ds.DEALER_MARKET_ID
INNER JOIN [dbo].DEALER d ON ds.DEALER_ID = d.DEALER_ID
WHERE m.countryID = 12
AND ds.Sales IS NOT NULL
GO
SET SHOWPLAN_XML OFF;
GO

-- 查看新查询执行计划
SET SHOWPLAN_XML ON;
GO
SELECT *  
FROM [dbo].[MODEL_OPTIONS] mo
INNER JOIN [dbo].MANUFACTURER m ON mo.MANUFACTURER_ID = m.MANUFACTURER_ID
INNER JOIN [dbo].DEALER_SALES ds ON mo.DEALER_MARKET_ID = ds.DEALER_MARKET_ID
INNER JOIN [dbo].DEALER d ON ds.DEALER_ID = d.DEALER_ID
INNER JOIN [dbo].DEALER_INFORMATION di ON d.DEALER_ID = di.DEALER_ID
WHERE m.countryID = 12
AND ds.Sales IS NOT NULL
GO
SET SHOWPLAN_XML OFF;
GO

对比两个执行计划时重点关注：

• 新增JOIN操作是否使用了索引扫描/查找，还是全表扫描
• 数据量较大的表是否存在大量排序、哈希匹配等耗时操作
• 执行计划中"估计行数"与"实际行数"的差异，差异过大可能意味着统计信息过时

4. 检查DEALER_INFORMATION表的重复数据 #

执行以下SQL，确认信息表中是否存在重复的DEALER_ID记录：

SELECT DEALER_ID, COUNT(*) AS duplicate_count
FROM [dbo].DEALER_INFORMATION
GROUP BY DEALER_ID
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC

• 若存在大量重复记录，说明表设计可能缺少主键或唯一约束，这是导致关联后结果量暴增的直接原因。

只读权限下的查询优化建议 #

1. 避免SELECT *：只选择需要的字段，减少数据传输量，示例：

SELECT mo.MODEL_OPTION_ID, mo.MANUFACTURER_ID, m.MANUFACTURER_NAME,
       ds.SALES_AMOUNT, d.DEALER_NAME, di.CONTACT_INFO
FROM [dbo].[MODEL_OPTIONS] mo
INNER JOIN [dbo].MANUFACTURER m ON mo.MANUFACTURER_ID = m.MANUFACTURER_ID
INNER JOIN [dbo].DEALER_SALES ds ON mo.DEALER_MARKET_ID = ds.DEALER_MARKET_ID
INNER JOIN [dbo].DEALER d ON ds.DEALER_ID = d.DEALER_ID
INNER JOIN [dbo].DEALER_INFORMATION di ON d.DEALER_ID = di.DEALER_ID
WHERE m.countryID = 12
AND ds.Sales IS NOT NULL

2. 过滤重复结果：若只需要每个经销商的一条信息，可使用ROW_NUMBER()去重（需替换成合适的排序字段）：

SELECT *
FROM (
    SELECT mo.*, m.*, ds.*, d.*, di.*,
           ROW_NUMBER() OVER (PARTITION BY d.DEALER_ID ORDER BY di.CREATE_DATE DESC) AS rn
    FROM [dbo].MODEL_OPTIONS mo
    INNER JOIN [dbo].MANUFACTURER m ON mo.MANUFACTURER_ID = m.MANUFACTURER_ID
    INNER JOIN [dbo].DEALER_SALES ds ON mo.DEALER_MARKET_ID = ds.DEALER_MARKET_ID
    INNER JOIN [dbo].DEALER d ON ds.DEALER_ID = d.DEALER_ID
    INNER JOIN [dbo].DEALER_INFORMATION di ON d.DEALER_ID = di.DEALER_ID
    WHERE m.countryID = 12
    AND ds.Sales IS NOT NULL
) t
WHERE rn = 1

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