sql - 为什么这会加速我的 SQL 查询？

Question

不久前，我从一位 DBA friend 那里学到了一个技巧来加速某些 SQL 查询。我记得他提到它与 SQL Server 如何编译查询有关，并且查询路径被迫使用索引值。

这是我的原始查询(需要 20 秒):

select Part.Id as PartId, Location.Id as LocationId
 FROM Part, PartEvent PartEventOuter, District, Location 
WHERE 
    PartEventOuter.EventType = '600'   AND PartEventOuter.AddressId = Location.AddressId  
    AND Part.DistrictId = District.Id   AND Part.PartTypeId = 15   
    AND District.SubRegionId = 11   AND PartEventOuter.PartId = Part.Id  
    AND PartEventOuter.EventDateTime <= '4/28/2009 4:30pm'   
    AND NOT EXISTS (
            SELECT PartEventInner.EventDateTime  
            FROM PartEvent PartEventInner
            WHERE PartEventInner.PartId = PartEventOuter.PartId
                AND PartEventInner.EventDateTime > PartEventOuter.EventDateTime 
                AND PartEventInner.EventDateTime  <= '4/30/2009 4:00pm')

这是“优化”查询(不到 1 秒):

select Part.Id as PartId, Location.Id as LocationId
 FROM Part, PartEvent PartEventOuter, District, Location 
WHERE 
    PartEventOuter.EventType = '600'   AND PartEventOuter.AddressId = Location.AddressId  
    AND Part.DistrictId = District.Id   AND Part.PartTypeId = 15   
    AND District.SubRegionId = 11   AND PartEventOuter.PartId = Part.Id  
    AND PartEventOuter.EventDateTime <= '4/28/2009 4:30pm'   
    AND NOT EXISTS (
            SELECT PartEventInner.EventDateTime  
            FROM PartEvent PartEventInner
            WHERE PartEventInner.PartId = PartEventOuter.PartId
                **AND EventType = EventType**
                AND PartEventInner.EventDateTime > PartEventOuter.EventDateTime 
                AND PartEventInner.EventDateTime  <= '4/30/2009 4:00pm')

任何人都可以详细解释为什么它运行得更快？我只是想更好地理解这一点。

Answer 1

可能是因为您在没有 EventType = EventType 的情况下获得笛卡尔积

来自维基百科:http://en.wikipedia.org/wiki/SQL

“[SQL] 使笛卡尔连接变得太容易(连接所有可能的组合)，当 WHERE 子句输入错误时，这会导致“失控”结果集。笛卡尔连接在实践中很少使用，需要明确的 CARTESIAN 关键字(SQL 1992 引入了 CROSS JOIN 关键字，它允许用户明确表示打算进行笛卡尔连接，但没有谓词的简写“逗号连接”仍然是可接受的语法，这仍然会引发同样的错误。) ”

实际上，您在第一次查询时要处理的行数超过了必要的行数。

http://www.fluffycat.com/SQL/Cartesian-Joins/