sql-server - 添加 ORDER BY 语句时 SQL 查询很慢

Question

我有一个表 [Documents] 包含以下列:

Name (string)
Status (string)
DateCreated [datetime]

这个表有大约 100 万条记录。所有这三列都有一个索引(每一列都有一个索引)。

当我运行这个查询时:

select top 50 *  
from [Documents] 
where (Name = 'None' OR Name is null OR Name = '') 
  and Status = 'New';

执行速度非常快(300 毫秒)

如果我使用 ORDER BY 子句运行相同的查询，它真的很慢(3000 毫秒)

select top 50 *  
from [Documents] 
where (Name = 'None' OR Name is null OR Name = '') 
  and Status = 'New'
order by DateCreated;

我知道它在另一个索引中搜索(DateCreated)，但它真的应该慢得多吗？如果是这样，为什么？我可以做些什么来加快这个查询(复合索引)？

谢谢

顺便说一句:包括 DateCreated 在内的所有索引都具有非常低的碎片，实际上我进行了重组，它并没有改变任何事情。

Answer 1

至于查询速度较慢的原因，查询需要“按顺序”返回行，因此它要么需要进行排序，要么需要使用索引。

使用具有 CreatedDate 前导列的索引，SQL Server 可以避免排序。但是 SQL Server 还必须访问基础表中的页面来评估是否要返回该行，查看 Status 和 Name 列中的值。

如果优化器选择不使用以 CreatedDate 作为前导列的索引，那么它需要首先定位所有满足谓词的行，然后执行排序操作以将这些行按顺序排列。然后它可以返回排序集中的前 50 行。 (SQL Server 不一定需要对整个集合进行排序，但它需要遍历整个集合，并进行足够的排序以确保获得需要返回的“前 50 个”。

注意:我怀疑您已经知道这一点，但要澄清一下:SQL Server 在 TOP 50 之前尊重 ORDER BY。如果您想要满足谓词的任何 50 行，但不一定是 DateCreated 值最低的 50 行，您可以重组/重写您的查询，以获取(最多)50 行，然后执行这些操作。

---

提高性能的几个想法

添加复合索引(正如其他答案所建议的那样)可能会带来一些改进，例如:

 ON Documents (Status, DateCreated, Name)

SQL Server 可能能够使用该索引来满足 Status 上的相等谓词，并且还可以在不进行排序操作的情况下以 DateCreated 顺序返回行。 SQL 服务器还可以满足索引中关于 Name 的谓词，限制对基础表中页面的查找次数，它需要为要返回的行执行此操作，以获取该行的“所有”列.

---

对于 SQL Server 2008 或更高版本，我会考虑过滤索引...取决于 Status='New' 的基数(即，如果行满足谓词 Status='New' 是表的一个相对较小的子集。

  CREATE NONCLUSTERED INDEX Documents_FIX 
  ON Documents (Status, DateCreated, Name)
  WHERE Status = 'New'

我还将修改查询以指定 ORDER BY Status, DateCreated, Name

因此 order by 子句与索引匹配，它并没有真正改变返回行的顺序。

---

作为更复杂的替代方案，我会考虑添加一个持久计算列并在其上添加一个过滤索引

  ALTER TABLE Documents
    ADD new_none_date_created AS
      CASE 
        WHEN Status = 'New' AND COALESCE(Name,'') IN ('','None') THEN DateCreated 
        ELSE NULL
      END
    PERSISTED
  ;  

  CREATE NONCLUSTERED INDEX Documents_FIXP 
  ON Documents (new_none_date_created)
  WHERE new_none_date_created IS NOT NULL
  ;

然后可以重写查询:

  SELECT TOP 50 *
    FROM Documents
   WHERE new_none_date_created IS NOT NULL
   ORDER BY new_none_date_created 
  ;

---