SQL Server双外键关联同主键的跨表查询性能优化

问题背景 #

现有两张表：

• Users（原表A）：包含Id（主键）、UserId等字段，数据量超50万行
• Transactions（原表B）：包含Id（主键）、SenderId（外键关联Users.Id）、ReceiverId（外键关联Users.Id）等字段

需要查询UserId为6760254D-846D-4747-B177-BBB1869275C2的用户在Transactions中的所有记录，当前使用的查询语句仅返回3条记录却耗时超40秒，要求将查询耗时优化至5秒以内。

原查询语句 #

SELECT 
    T.Id, T.ExternalId, T.RecordedAt, T.Amount, 
    T.Status, T.StatusDescription, T.AdminNotes, T.Remarks, T.Tenant,
    T.TotalFee, T.DefaultFeeValue, T.PartnerFeeValue, 
    T.CurrencyId, T.ServiceId, T.ReferenceNumber, T.Location,
    U.Fullname AS SenderName, U.Email AS SenderEmail, U.ContactNumber AS SenderPhoneNumber
FROM 
    Transactions T
INNER JOIN 
    Users U ON T.ReceiverId = U.Id
INNER JOIN 
    Users W ON T.Senderid = W.Id
WHERE  
    (U.UserId = '6760254D-846D-4747-B177-BBB1869275C2' OR 
     W.UserId='6760254D-846D-4747-B177-BBB1869275C2')
ORDER BY 
    T.Created DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

优化方案 #

1. 重构查询逻辑，避免低效OR关联 #

原查询通过两次关联Users表并使用OR条件，容易导致优化器无法有效利用索引，引发全表扫描。先定位目标用户的Id，再拆分查询逻辑：

-- 先获取目标用户的Id
DECLARE @TargetUserId UNIQUEIDENTIFIER = '6760254D-846D-4747-B177-BBB1869275C2'
DECLARE @UserID INT -- 若Users.Id为其他类型，需对应调整
SELECT @UserID = Id FROM Users WHERE UserId = @TargetUserId

-- 拆分查询：分别获取用户作为发送方、接收方的交易记录，用UNION ALL合并
SELECT 
    T.Id, T.ExternalId, T.RecordedAt, T.Amount, 
    T.Status, T.StatusDescription, T.AdminNotes, T.Remarks, T.Tenant,
    T.TotalFee, T.DefaultFeeValue, T.PartnerFeeValue, 
    T.CurrencyId, T.ServiceId, T.ReferenceNumber, T.Location,
    ContactUser.Fullname AS ContactName, ContactUser.Email AS ContactEmail, ContactUser.ContactNumber AS ContactPhone
FROM Transactions T
INNER JOIN Users ContactUser ON T.SenderId = ContactUser.Id
WHERE T.ReceiverId = @UserID

UNION ALL

SELECT 
    T.Id, T.ExternalId, T.RecordedAt, T.Amount, 
    T.Status, T.StatusDescription, T.AdminNotes, T.Remarks, T.Tenant,
    T.TotalFee, T.DefaultFeeValue, T.PartnerFeeValue, 
    T.CurrencyId, T.ServiceId, T.ReferenceNumber, T.Location,
    ContactUser.Fullname AS ContactName, ContactUser.Email AS ContactEmail, ContactUser.ContactNumber AS ContactPhone
FROM Transactions T
INNER JOIN Users ContactUser ON T.ReceiverId = ContactUser.Id
WHERE T.SenderId = @UserID

ORDER BY Created DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

注：原查询中U.Fullname AS SenderName的逻辑可能存在混淆（U关联的是ReceiverId），这里调整为显示交易对方的信息，若需保留原逻辑可自行修改关联关系。

2. 创建针对性索引 #

索引是优化大表查询的核心，针对本次查询创建以下索引：

• Users表索引：覆盖UserId过滤条件及查询所需的用户信息，避免键查找

CREATE NONCLUSTERED INDEX IX_Users_UserId_Include 
ON Users(UserId) 
INCLUDE(Id, Fullname, Email, ContactNumber);

• Transactions表索引：覆盖过滤条件（SenderId/ReceiverId）、排序字段（Created DESC）及所有查询需要的交易字段，实现索引覆盖扫描，无需回表

CREATE NONCLUSTERED INDEX IX_Transactions_SenderReceiver_Created 
ON Transactions(SenderId, ReceiverId, Created DESC) 
INCLUDE(Id, ExternalId, RecordedAt, Amount, Status, StatusDescription, AdminNotes, Remarks, Tenant, TotalFee, DefaultFeeValue, PartnerFeeValue, CurrencyId, ServiceId, ReferenceNumber, Location);

3. 更新统计信息 #

若数据库统计信息过时，优化器可能生成低效执行计划，执行以下语句更新统计信息：

UPDATE STATISTICS Users;
UPDATE STATISTICS Transactions;

4. 验证执行计划 #

执行优化后的查询，查看执行计划，确认是否使用了创建的索引，避免出现全表扫描、键查找等低效操作。

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