优化含多OR条件的SQL查询性能问题求助

我需要编写一个SQL查询，通过单个关键词匹配人员姓名、母亲姓名或任一ID编号。原查询语句如下：

select pf.id, 
       (i.FirstName + ' ' + i.SecondName +' ' + i.ThirdName + ' '+i.FourthName) as Name ,
       (i.MotherFirstName + ' ' + i.MotherSecondName +' ' + i.MotherThirdName) as MotherName ,
       (select Alias From FormStatuses where id = (select top(1) StatusId from BeneficiaryStatus where BeneficiaryId = pf.Id order by CreatedDate desc)) ALias ,
       (select NameEn From FormStatuses where id = (select top(1) StatusId from BeneficiaryStatus where BeneficiaryId = pf.Id order by CreatedDate desc)) Status ,
       (Case When exists (select 1 from CaseManagementForms where PeopleId = pf.Id) then 1 Else 0 End) HasCase 
from PeopleForms pf 
inner join FormPersonalInfos i on pf.Id = i.PeopleFormId 
Left outer join CivilStatusIds c on pf.CivilStatusId = c.Id 
left outer join NationalCards n on pf.NationalCardId = n.Id 
left outer join PdsCards p on pf.PdsCardId = p.Id 
where i.FullName like 'Azad100087%' 
   or i.MotherFullName like 'Azad100087%' 
   or c.Number = 'Azad100087' 
   or n.Number = 'Azad100087' 
   or p.Number = 'Azad100087' 
   or pf.Code = 'Azad100087' 
   or pf.OldCode = 'Azad100087'

背景情况 #

• 各表数据量均达40万+条，原查询耗时2秒；
• 减少OR条件（仅保留姓名和母亲姓名）时耗时仅117ms；
• 已为查询字段创建合适索引，尝试过多表连接、WHERE子查询、CROSS/OUTER APPLY等方法但效果不佳；
• 更新：改用UNION后，耗时降至700+ms；
• 更新2：调整数据库架构并使用UNION ALL后，耗时降至60-90ms；

进一步优化建议 #

• 确认索引的覆盖性：针对每个匹配条件的索引，确保包含关联查询所需的字段，避免回表操作。例如：

  • 对于FormPersonalInfos.FullName和MotherFullName，创建索引IX_FormPersonalInfos_FullName (FullName) INCLUDE (PeopleFormId, FirstName, SecondName, ThirdName, FourthName, MotherFirstName, MotherSecondName, MotherThirdName)，这样查询姓名时直接从索引获取所有需要的字段，无需访问表数据；
  • 对于CivilStatusIds.Number，创建索引IX_CivilStatusIds_Number (Number) INCLUDE (Id)，关联时直接拿到Id匹配pf.CivilStatusId；
  • 同理，NationalCards.Number、PdsCards.Number、PeopleForms.Code/OldCode的索引都要包含关联和返回所需的字段。

• 细化UNION ALL子查询：继续保持每个UNION ALL分支只处理一个匹配条件，确保每个分支都能精准命中对应的索引。比如每个子查询只查询当前条件匹配的行，并且只返回最终需要的字段，避免冗余数据处理。示例结构：

  -- 匹配姓名的分支
  SELECT pf.id, 
         (i.FirstName + ' ' + i.SecondName +' ' + i.ThirdName + ' '+i.FourthName) as Name,
         (i.MotherFirstName + ' ' + i.MotherSecondName +' ' + i.MotherThirdName) as MotherName,
         fs.Alias,
         fs.NameEn as Status,
         CASE WHEN EXISTS (SELECT 1 FROM CaseManagementForms WHERE PeopleId = pf.Id) THEN 1 ELSE 0 END as HasCase
  FROM PeopleForms pf 
  JOIN FormPersonalInfos i ON pf.Id = i.PeopleFormId
  OUTER APPLY (
      SELECT TOP 1 fs.Alias, fs.NameEn
      FROM BeneficiaryStatus bs
      JOIN FormStatuses fs ON bs.StatusId = fs.Id
      WHERE bs.BeneficiaryId = pf.Id
      ORDER BY bs.CreatedDate DESC
  ) fs
  WHERE i.FullName LIKE @SearchKeyword + '%'
  
  UNION ALL
  
  -- 匹配母亲姓名的分支
  SELECT pf.id, 
         (i.FirstName + ' ' + i.SecondName +' ' + i.ThirdName + ' '+i.FourthName) as Name,
         (i.MotherFirstName + ' ' + i.MotherSecondName +' ' + i.MotherThirdName) as MotherName,
         fs.Alias,
         fs.NameEn as Status,
         CASE WHEN EXISTS (SELECT 1 FROM CaseManagementForms WHERE PeopleId = pf.Id) THEN 1 ELSE 0 END as HasCase
  FROM PeopleForms pf 
  JOIN FormPersonalInfos i ON pf.Id = i.PeopleFormId
  OUTER APPLY (
      SELECT TOP 1 fs.Alias, fs.NameEn
      FROM BeneficiaryStatus bs
      JOIN FormStatuses fs ON bs.StatusId = fs.Id
      WHERE bs.BeneficiaryId = pf.Id
      ORDER BY bs.CreatedDate DESC
  ) fs
  WHERE i.MotherFullName LIKE @SearchKeyword + '%'
  
  -- 其他匹配分支以此类推
  

• 参数化查询与执行计划缓存：如果查询的关键词是动态输入的，使用参数化查询（比如@SearchKeyword）替代硬编码字符串，这样SQL Server可以复用执行计划，避免每次不同关键词都重新编译，提升重复查询的效率。

• 更新统计信息：大表的数据分布可能随着时间变化，过时的统计信息会导致查询优化器选择低效的执行计划。执行UPDATE STATISTICS [TableName] WITH FULLSCAN;（替换为对应表名），确保优化器能基于准确的数据分布生成最优计划。

• 优化状态查询逻辑：原查询中获取最新状态的子查询会对每个匹配行执行两次，建议预先计算并存储最新状态：

  • 创建一个索引视图或物化视图，包含BeneficiaryId和最新的StatusId（按CreatedDate降序取TOP 1）；
  • 或者建立一个LatestBeneficiaryStatus表，通过触发器或定时任务（比如SQL Agent Job）定期更新，查询时直接关联该表并JOIN FormStatuses获取Alias和NameEn，避免重复子查询。

• 优化HasCase判断：对于HasCase的exists子查询，确保CaseManagementForms.PeopleId有索引，让exists查询能快速定位匹配项。如果业务允许，也可以在PeopleForms表中添加一个HasCase字段，通过触发器或定时任务更新该字段的值，查询时直接读取字段而非执行子查询。

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