sql-server - 针对 400 万条记录的两个截然不同的查询同时执行 - 其中一个使用暴力破解

Question

我使用的是 SQL Server 2008。我有一个包含超过 300 万条记录的表，该表与另一个包含 100 万条记录的表相关。

我花了几天时间尝试不同的方式来查询这些表。我将其归结为两个完全不同的查询，这两个查询都需要 6 秒才能在我的笔记本电脑上执行。

第一个查询使用强力方法来评估可能匹配，并通过聚合求和计算删除不正确的匹配。

第二个获取所有可能的匹配项，然后通过 EXCEPT 查询删除不正确的匹配项，该查询使用两个专用索引来查找低不匹配和高不匹配。

从逻辑上讲，人们会期望暴力破解会很慢，而索引会很快。并非如此。我对索引进行了大量实验，直到获得最佳速度。

此外，强力查询不需要那么多索引，这意味着从技术上讲它会产生更好的整体系统性能。

下面是两个执行计划。如果您看不到它们，请告诉我，我会横向重新发布/邮寄给您。

暴力查询:

SELECT      ProductID, [Rank]
FROM        (
            SELECT      p.ProductID, ptr.[Rank], SUM(CASE
                            WHEN p.ParamLo < si.LowMin OR
                            p.ParamHi > si.HiMax THEN 1
                            ELSE 0
                            END) AS Fail
            FROM        dbo.SearchItemsGet(@SearchID, NULL) AS si
                        JOIN dbo.ProductDefs AS pd
            ON          pd.ParamTypeID = si.ParamTypeID
                        JOIN dbo.Params AS p
            ON          p.ProductDefID = pd.ProductDefID
                        JOIN dbo.ProductTypesResultsGet(@SearchID) AS ptr
            ON          ptr.ProductTypeID = pd.ProductTypeID
            WHERE       si.Mode IN (1, 2)
            GROUP BY    p.ProductID, ptr.[Rank]
            ) AS t
WHERE       t.Fail = 0

基于索引的异常查询:

with si AS (
    SELECT      DISTINCT pd.ProductDefID, si.LowMin, si.HiMax
    FROM        dbo.SearchItemsGet(@SearchID, NULL) AS si
                JOIN dbo.ProductDefs AS pd
    ON          pd.ParamTypeID = si.ParamTypeID
                JOIN dbo.ProductTypesResultsGet(@SearchID) AS ptr
    ON          ptr.ProductTypeID = pd.ProductTypeID
    WHERE       si.Mode IN (1, 2)
)
SELECT      p.ProductID
FROM        dbo.Params AS p
            JOIN si
ON          si.ProductDefID = p.ProductDefID
EXCEPT
SELECT      p.ProductID
FROM        dbo.Params AS p
            JOIN si
ON          si.ProductDefID = p.ProductDefID    
WHERE       p.ParamLo < si.LowMin OR p.ParamHi > si.HiMax

我的问题是，根据执行计划，哪一个看起来更有效率？我意识到随着我的数据增长，情况可能会发生变化。

编辑:

我已经更新了索引，现在第二个查询的执行计划如下:

Answer 1

相信优化器。

编写最简单地表达您想要实现的目标的查询。 如果您在该查询中遇到性能问题，那么您应该查看是否缺少任何索引。但您仍然不必明确使用这些索引。

不要担心你如何实现这样的搜索。

在非常极少数情况下，您可能需要进一步强制查询使用特定索引(通过提示)，但这可能只占查询的 < 0.1%。

<小时/>

在您发布的计划中，您的“优化”版本导致对您的(我认为)Params 表(PK_Params_1、IX_Params_1)的 2 个索引进行扫描。如果不看到查询，就很难知道为什么会发生这种情况，但是如果您将对表进行一次扫描(“强力”)和两次扫描进行比较，很容易看出为什么第二次扫描效率不高。

<小时/>

我想我会尝试:

        SELECT      p.ProductID, ptr.[Rank]
        FROM        dbo.SearchItemsGet(@SearchID, NULL) AS si
                    JOIN dbo.ProductDefs AS pd
        ON          pd.ParamTypeID = si.ParamTypeID
                    JOIN dbo.Params AS p
        ON          p.ProductDefID = pd.ProductDefID
                    JOIN dbo.ProductTypesResultsGet(@SearchID) AS ptr
        ON          ptr.ProductTypeID = pd.ProductTypeID

LEFT JOIN Params p_anti
    on p_anti.ProductDefId = pd.ProductDefID and
         (p_anti.ParamLo < si.LowMin or p_anti.ParamHi > si.HiMax)


        WHERE       si.Mode IN (1, 2)

AND p_anti.ProductID is null

        GROUP BY    p.ProductID, ptr.[Rank]

即引入反连接来消除您不想要的结果。