为什么这会加快我的SQL查询速度？

Question

我曾从一个DBA朋友那里学到了一个技巧，可以加速某些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')

有人能详细解释一下为什么这运行得更快吗？我只是想更好地理解它。

- Keith

---

你尝试过查看查询计划吗？ - Alex Peck

因为事件类型上有索引？ - OMG Ponies

根据您的数据库引擎而定，我假设您正在使用 SQL 2005/2008。 - hova

请注意，如果EventType可以为NULL，那么这两个查询不相同（因为如果EventType为NULL，则EventType = EventType不成立）。 - Mark Brackett

检查 EventType 不为空并没有加快查询速度。 - Keith

Answer 1

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

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

“[SQL]使得进行笛卡尔积（连接所有可能的组合）变得太容易，当WHERE子句被打错时，结果集会出现“失控”情况。实际上很少使用笛卡尔积，因此需要要求显式的CARTESIAN关键字。（SQL 1992引入了CROSS JOIN关键字，允许用户明确表示预期的笛卡尔积，但仍然可以使用缩写的“逗号连接”语法而不需要谓词，这仍然会引起同样的错误。）”

你的第一个查询实际上比必要的行数多。

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

- Robert Greiner

---

不能这样...EventType 没有表限定符，所以它正在将其与自身进行比较。本质上是一个无操作（除了 NULLS 之外）。 - Mark Brackett

在我第一次尝试优化这个查询时，我确实将所有内容转换为相关字段上的（左）内连接。然而，这并没有对执行时间产生任何影响。 - Keith

Answer 2

EventType = Null 的记录数量很多吗？
在添加额外限制之前，您的子查询将返回所有这些 Null 记录，然后 Not Exists 谓词将扫描外部查询中的每一行... 因此，您限制子查询返回的内容越多，需要扫描以验证 Not Exists 的行数就越少...

如果这是问题，那么在子查询中也将记录限制为 EventType = '600' 可能会更快...

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 = '600'                        
                       AND PartEventInner.EventDateTime > PartEventOuter.EventDateTime
                       AND PartEventInner.EventDateTime  <= '4/30/2009 4:00pm')

- Charles Bretana

---

那样虽然快，但对于我们的结果集是错误的... 子查询是正确的，因为它包含了任何“EventType”。 - Keith

Answer 3

奇怪，你是否定义了一个同时包含EventType和EventDateTime的索引？

编辑：
等等，EventType是可空列吗？ 如果它的值为NULL，Column = Column将评估为FALSE*。至少使用默认的SQL Server设置。

更安全的等价物是EventType IS NOT NULL。看看这是否在速度上给出相同的结果？

---

*：我的 T-SQL 参考资料说，当 ANSI_NULLS 设置为 OFF 时，它应该评估为 TRUE，但我的查询窗口显示不同。现在有点困惑了。
有什么规定吗？TRUE、FALSE、NULL 或 UNKNOWN？ :) 在 SQL 中，你得喜欢“二进制”逻辑啊 :(

- Thorarin

---

PartEvent 视图的列上没有索引（视图所拉取的表中的列）。 - Keith

下周一回去上班时，我得尝试一下"EventType IS NOT NULL"。 - Keith

检查 EventType 不为空并没有加快查询速度 - 仍然需要20秒。检查 EventType = EventType 仍然只需要不到1秒钟。 - Keith

你有比对执行计划来揭示其中的问题吗？我很想知道到底发生了什么。 - Thorarin

Answer 4

只有当查询中包含此索引的所有列时，SQL Server 才会使用索引查找。

- Johan Buret

Answer 5

你添加的每个非索引列都会执行一次表扫描。如果您在 WHERE 子句中更早地缩小查询范围，则后续的扫描速度更快。因此，通过添加索引扫描，您的表扫描将针对更少的数据运行。

- tsilb

Answer 6

这种情况以前比现在常见得多。例如，Oracle 6曾经对WHERE子句中限制的顺序非常敏感。你之所以感到惊讶，实际上是因为我们已经变得非常擅长期望数据库引擎始终能够找到最佳的访问路径，无论你如何构造SQL语句。此外，Oracle 6和7（之后我转向MSSQL）还具有提示扩展功能，可以用来告诉数据库如何构建查询计划。

在这种特定情况下，如果没有看到实际的查询计划，很难给出确定性的答案，但我怀疑差异在于你有一个复合索引，其中使用了EventType，但第一个查询没有使用，而第二个查询使用了。这是不寻常的，因为我本来希望你的第一个查询也会使用它，所以我怀疑数据库统计信息可能已经过时，

请执行REGENERATE STATISTICS，然后再试一次，并在此处发布结果。

- Cruachan