SQL Server可空列场景下LINQ分页排序的效率与最优实现

我的SQL Server表中有一个可空列SomeDate，需求是获取数据并分页，要求非空的SomeDate按日期升序排在前面，空值排在后面。

我尝试了两种LINQ查询：

1. 结果正确但担心大数据集性能的写法：

query.OrderByDescending(x => x.SomeDate.HasValue)
      .ThenBy(x => x.SomeDate)
      .Skip(skip) // skip=0
      .Take(take) // take=10
      .AsAsyncEnumerable();

2. 效率高但结果不符合预期的写法：

query.Skip(skip) // skip=0
     .Take(take) // take=10
     .OrderByDescending(x => x.SomeDate.HasValue)
     .ThenBy(x => x.SomeDate)
     .AsAsyncEnumerable();

问题1：正确写法在≥10万行大数据集上的效率如何？ #

这种写法的效率核心取决于是否有匹配的索引：

• 无索引时：SQL Server需要对全表执行排序操作，10万行数据大概率会触发磁盘排序（Sort运算符），开销较高；尤其是当skip值较大时（比如翻页到较靠后的页码），每次都要重新排序全表再跳过大量数据，性能下降会更明显。
• 有匹配索引时：如果创建了覆盖排序逻辑的复合索引，SQL Server可以直接利用索引的有序性获取数据，无需全表排序，此时分页操作的开销会大幅降低，10万行数据也能高效处理。

问题2：若原查询开销过大，最优实现方案是什么？ #

最优方案是索引优化+正确的LINQ写法，具体如下：

1. 创建针对性复合索引
   直接创建匹配排序规则的索引，让SQL Server无需额外排序就能获取有序数据：

   CREATE INDEX IX_YourTable_SomeDate_Ordered ON YourTable
   (
       CASE WHEN SomeDate IS NOT NULL THEN 1 ELSE 0 END DESC,
       SomeDate ASC
   )
   -- 如果查询需要返回其他列，添加INCLUDE子句覆盖这些列，避免键查找
   INCLUDE (Column1, Column2, ...);
   

   该索引完全匹配“非空值在前、空值在后，非空值按日期升序”的排序逻辑，SQL Server可以通过索引扫描/查找直接拿到有序数据，Skip和Take操作会非常高效。

2. 保持正确的LINQ写法
   第一种LINQ写法本身是正确的，ORM（比如EF Core）会自动将x.SomeDate.HasValue转换为SomeDate IS NOT NULL的SQL表达式，能完美利用上面创建的索引，无需额外修改。

3. 排除错误写法
   第二种写法先分页再排序，只会对取到的少量数据排序，完全不符合“全表范围内按规则排序后分页”的需求，绝对不能使用。

另外，如果你的需求是只获取SomeDate非空的行，可以先过滤再排序分页，进一步减少数据处理量：

query.Where(x => x.SomeDate.HasValue)
     .OrderBy(x => x.SomeDate)
     .Skip(skip)
     .Take(take)
     .AsAsyncEnumerable();

此时只需创建CREATE INDEX IX_YourTable_SomeDate ON YourTable(SomeDate)即可，性能会更优。

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