EF Core中OrderBy结合Skip/Take分页超时问题求助

问题背景 #

在.NET Core 2.1项目中使用EF Core与Azure SQL Server交互，多表关联查询约15万条数据时原查询超时，采用Skip+Take分页后可正常运行，但添加OrderBy子句后查询耗时超过30秒，触发Azure SQL超时限制，抛出异常：

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

正常运行的分页代码 #

query.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();

添加OrderBy后的代码 #

query.OrderBy(sortField)
     .Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList()

生成的SQL语句 #

SELECT [p].[Id], [p].[CreatedById], [p].[CreatedOn], [p].[CurrencyId], [p].[CustomerId], [p].[DataMigrationLogId], [p].[FollowUp], [p].[IsActive], [p].[ProjectName], [p].[PlantCode], [p].[ShipToDistanceFromPlant], [p].[StatusId], 
[p].[UpdatedById], [p].[UpdatedOn], [p.DataMigrationLog].[Id], [p.DataMigrationLog].[CreatedOn], [p.DataMigrationLog].[GeneratedOn], [p.DataMigrationLog].[HasEdsBid], [p.DataMigrationLog].[HasMbiBid], [p.DataMigrationLog].[Log], 
[p.DataMigrationLog].[RequestXml], [p.DataMigrationLog].[Status], [p.DataMigrationLog].[Xq1ProjectId], [p.UpdatedBy].[Id], [p.UpdatedBy].[CellPhone], [p.UpdatedBy].[CreatedOn], [p.UpdatedBy].[Discriminator], [p.UpdatedBy].[Email], 
[p.UpdatedBy].[FirstName], [p.UpdatedBy].[HasAccessToDoors], [p.UpdatedBy].[HasAccessToWindows], [p.UpdatedBy].[IsActive], [p.UpdatedBy].[LastName], [p.UpdatedBy].[Prefix], [p.UpdatedBy].[RecentProjectId], [p.UpdatedBy].[UpdatedOn], 
[p.UpdatedBy].[WorkPhone], [p.UpdatedBy].[XQ1LoginName], [p.UpdatedBy].[IsSuperAdmin], [p.UpdatedBy].[HasAccessToRestrictedReports], [p.UpdatedBy].[HasEdsProgramAndDealerPriceAccess], [p.UpdatedBy].[IsIss], 
[p.UpdatedBy].[ProjectVisibilityId], [p.UpdatedBy].[CustomerId], [p.UpdatedBy].[IsApiUser], [p.CreatedBy].[Id], [p.CreatedBy].[CellPhone], [p.CreatedBy].[CreatedOn], [p.CreatedBy].[Discriminator], [p.CreatedBy].[Email],
[p.CreatedBy].[FirstName], [p.CreatedBy].[HasAccessToDoors], [p.CreatedBy].[HasAccessToWindows], [p.CreatedBy].[IsActive], [p.CreatedBy].[LastName], [p.CreatedBy].[Prefix], [p.CreatedBy].[RecentProjectId], [p.CreatedBy].[UpdatedOn],
[p.CreatedBy].[WorkPhone], [p.CreatedBy].[XQ1LoginName], [p.CreatedBy].[IsSuperAdmin], [p.CreatedBy].[HasAccessToRestrictedReports], [p.CreatedBy].[HasEdsProgramAndDealerPriceAccess], [p.CreatedBy].[IsIss],
[p.CreatedBy].[ProjectVisibilityId], [p.CreatedBy].[CustomerId], [p.CreatedBy].[IsApiUser], [p.Status].[Id], [p.Status].[Code], [p.Status].[Name], [p.Customer].[Id], [p.Customer].[AxCustomerNumber], [p.Customer].[CreatedById],
[p.Customer].[CreatedOn], [p.Customer].[CreditTermId], [p.Customer].[CurrencyId], [p.Customer].[CustomerTypeId], [p.Customer].[DefaultPricing], [p.Customer].[FabricSystemFreight], [p.Customer].[IsActive], [p.Customer].[IsSpecialFreight],
[p.Customer].[LocalityRepId], [p.Customer].[MinFreightCharge], [p.Customer].[CompanyName], [p.Customer].[Notes], [p.Customer].[PrimaryBusinessId], [p.Customer].[ProgramAccountId], [p.Customer].[Prospect], 
[p.Customer].[ShippingDollarsThreshold], [p.Customer].[ShippingMilesThreshold], [p.Customer].[SpecialNote], [p.Customer].[SpecialPlantInstruction], [p.Customer].[UpdatedById], [p.Customer].[UpdatedOn], 
[p.Customer].[Xq1ProgGroupName], [p.Customer].[Xq1SrNo], [p.Customer].[XqCustomerNumber], [p.Customer.ProgramAccount].[Id], [p.Customer.ProgramAccount].[AxCustomerNumber], [p.Customer.ProgramAccount].[CreatedById], 
[p.Customer.ProgramAccount].[CreatedOn], [p.Customer.ProgramAccount].[CreditTermId], [p.Customer.ProgramAccount].[CurrencyId], [p.Customer.ProgramAccount].[CustomerTypeId], [p.Customer.ProgramAccount].[DefaultPricing],
[p.Customer.ProgramAccount].[FabricSystemFreight], [p.Customer.ProgramAccount].[IsActive], [p.Customer.ProgramAccount].[IsSpecialFreight], [p.Customer.ProgramAccount].[LocalityRepId], [p.Customer.ProgramAccount].[MinFreightCharge],
[p.Customer.ProgramAccount].[CompanyName], [p.Customer.ProgramAccount].[Notes], [p.Customer.ProgramAccount].[PrimaryBusinessId], [p.Customer.ProgramAccount].[ProgramAccountId], [p.Customer.ProgramAccount].[Prospect], 
[p.Customer.ProgramAccount].[ShippingDollarsThreshold], [p.Customer.ProgramAccount].[ShippingMilesThreshold], [p.Customer.ProgramAccount].[SpecialNote], [p.Customer.ProgramAccount].[SpecialPlantInstruction],
[p.Customer.ProgramAccount].[UpdatedById], [p.Customer.ProgramAccount].[UpdatedOn], [p.Customer.ProgramAccount].[Xq1ProgGroupName], [p.Customer.ProgramAccount].[Xq1SrNo], [p.Customer.ProgramAccount].[XqCustomerNumber],
[p.Customer.CustomerType].[Id], [p.Customer.CustomerType].[Code], [p.Customer.CustomerType].[Name]
FROM [Projects] AS [p]
LEFT JOIN [DataMigrationLogs] AS [p.DataMigrationLog] ON [p].[DataMigrationLogId] = [p.DataMigrationLog].[Id]
INNER JOIN [Security].[XQUsers] AS [p.UpdatedBy] ON [p].[UpdatedById] = [p.UpdatedBy].[Id]
INNER JOIN [Security].[XQUsers] AS [p.CreatedBy] ON [p].[CreatedById] = [p.CreatedBy].[Id]
INNER JOIN [ProjectStatuses] AS [p.Status] ON [p].[StatusId] = [p.Status].[Id]
LEFT JOIN [Customers] AS [p.Customer] ON [p].[CustomerId] = [p.Customer].[Id]
LEFT JOIN (
    SELECT [p.Customer.LocalityRep].*
    FROM [Security].[XQUsers] AS [p.Customer.LocalityRep]
    WHERE [p.Customer.LocalityRep].[Discriminator] = N'SALES_SP'
) AS [t] ON [p.Customer].[LocalityRepId] = [t].[Id]
LEFT JOIN [Customers] AS [p.Customer.ProgramAccount] ON [p.Customer].[ProgramAccountId] = [p.Customer.ProgramAccount].[Id]
LEFT JOIN (
    SELECT [p.Customer.ProgramAccount.LocalityRep].*
    FROM [Security].[XQUsers] AS [p.Customer.ProgramAccount.LocalityRep]
    WHERE [p.Customer.ProgramAccount.LocalityRep].[Discriminator] = N'SALES_SP'
) AS [t0] ON [p.Customer.ProgramAccount].[LocalityRepId] = [t0].[Id]
LEFT JOIN [CustomerTypes] AS [p.Customer.CustomerType] ON [p.Customer].[CustomerTypeId] = [p.Customer.CustomerType].[Id]
WHERE ([p.UpdatedBy].[Discriminator] IN (N'INT_SYSADMMIN', N'XqInternalUser', N'EXT', N'SALES_SP', N'XqUser') AND [p.CreatedBy].[Discriminator] IN (N'INT_SYSADMMIN', N'XqInternalUser', N'EXT', N'SALES_SP', N'XqUser')) AND (EXISTS (
    SELECT 1
    FROM [Security].[BusinessUserRegions] AS [r]
    WHERE [r].[RegionId] IN (CAST(6 AS bigint), CAST(8 AS bigint), CAST(9 AS bigint)) AND ([t].[Id] = [r].[BusinessUserId])) OR ([p.Customer].[ProgramAccountId] IS NOT NULL AND EXISTS (
    SELECT 1
    FROM [Security].[BusinessUserRegions] AS [r0]
    WHERE [r0].[RegionId] IN (CAST(6 AS bigint), CAST(8 AS bigint), CAST(9 AS bigint)) AND ([t0].[Id] = [r0].[BusinessUserId]))))
ORDER BY [p].[CreatedOn]

问题分析 #

你的理解是正确的：不加OrderBy时，数据库可能利用现有索引直接提取分页数据；但添加OrderBy后，数据库需要先筛选出所有符合条件的15万条数据，完成全量排序后再执行分页，这个过程在多表关联、数据量较大时会产生极高的性能开销，最终导致超时。

解决办法 #

1. 创建针对性的覆盖索引 #

针对排序字段（如示例中的[p].[CreatedOn]）和查询过滤条件创建覆盖索引，包含查询用到的过滤字段、排序字段及关联外键，让数据库无需回表即可完成筛选和排序。示例：

-- 针对Projects表的CreatedOn字段创建覆盖索引，包含必要的关联和过滤字段
CREATE NONCLUSTERED INDEX IX_Projects_CreatedOn_Filtered 
ON [Projects] ([CreatedOn] DESC) -- 根据排序方向调整
INCLUDE ([Id], [CreatedById], [UpdatedById], [StatusId], [CustomerId], [DataMigrationLogId], [IsActive])
WHERE [IsActive] = 1; -- 若有固定过滤条件可加入，缩小索引范围

-- 优化关联表的索引
CREATE NONCLUSTERED INDEX IX_XQUsers_Discriminator ON [Security].[XQUsers] ([Discriminator]);
CREATE NONCLUSTERED INDEX IX_BusinessUserRegions_RegionId_BusinessUserId ON [Security].[BusinessUserRegions] ([RegionId], [BusinessUserId]);

2. 先分页主表再关联其他表 #

先对主表（Projects）执行排序分页，仅获取分页后的主键ID，再通过这些ID关联其他表获取详细数据，减少数据库需要处理的关联数据量。EF Core代码示例：

// 第一步：获取分页后的主表ID
var projectIds = query.Select(p => p.Id)
                      .OrderBy(sortField)
                      .Skip((pageNumber - 1) * pageSize)
                      .Take(pageSize)
                      .ToList();

// 第二步：通过ID关联其他表获取完整数据
var result = query.Where(p => projectIds.Contains(p.Id))
                  .Include(p => p.DataMigrationLog)
                  .Include(p => p.UpdatedBy)
                  .Include(p => p.CreatedBy)
                  .Include(p => p.Status)
                  .Include(p => p.Customer)
                  .ThenInclude(c => c.ProgramAccount)
                  .Include(p => p.Customer)
                  .ThenInclude(c => c.CustomerType)
                  .ToList();

3. 精简查询字段 #

避免一次性加载所有关联表的全部字段，使用Select明确只返回业务需要的列，减少数据传输和数据库处理负担：

var result = query.OrderBy(sortField)
                  .Skip((pageNumber - 1) * pageSize)
                  .Take(pageSize)
                  .Select(p => new 
                  {
                      p.Id,
                      p.ProjectName,
                      p.CreatedOn,
                      UpdatedByFullName = $"{p.UpdatedBy.FirstName} {p.UpdatedBy.LastName}",
                      CustomerName = p.Customer?.CompanyName
                      // 仅保留实际需要的字段
                  })
                  .ToList();

4. 临时调整超时时间（不推荐作为长期方案） #

若需要临时缓解问题，可调整EF Core的CommandTimeout延长查询超时时间，但这无法解决根本性能问题：

using (var context = new YourDbContext())
{
    context.Database.SetCommandTimeout(60); // 设置为60秒
    // 执行查询逻辑
}

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