按名、中间名、姓氏任意组合搜索全名耗时过长的优化咨询

当前执行包含姓名多组合模糊搜索的SQL查询时耗时过长，原查询语句如下：

WHERE p.DeleteInd = 0
    AND (
        nullif(@v_PhoneNo, '') IS NULL
        OR cast(p.MobileNo AS NVARCHAR(20)) LIKE '%' + @v_PhoneNo + '%'
        OR cast(p.ContactNo AS NVARCHAR(20)) LIKE '%' + @v_PhoneNo + '%'
        )
    AND (
        isnull(@v_PatientDisplayId, '') = ''
        OR p.PatientDisplayId = @v_PatientDisplayId
        )
    AND (
        nullif(@v_OldPatientMRNO, '') IS NULL
        OR p.OldMrno = @v_OldPatientMRNO
        )
    AND (
        nullif(@v_SearchText, '') IS NULL
        OR p.FirstName LIKE @v_SearchText + '%'
        OR p.LastName LIKE @v_SearchText + '%'
        OR p.MiddleName LIKE @v_SearchText + '%'
        OR cast(p.MobileNo AS NVARCHAR(20)) LIKE '%' + @v_SearchText + '%'
        OR CONCAT (
            FirstName
            ,' '
            ,MiddleName
            ,' '
            ,LastName
            ) LIKE @v_SearchText + '%'
        OR CONCAT (
            p.FirstName
            ,' '
            ,p.LastName
            ) LIKE '%' + @v_SearchText + '%'
        OR CONCAT (
            p.FirstName
            ,' '
            ,MiddleName
            ,' '
            ,LastName
            ,' '
            ,p.SecondLastName
            ) LIKE '%' + @v_SearchText + '%'
        OR CONCAT (
            p.FirstName
            ,' '
            ,p.SecondLastName
            ) LIKE '%' + @v_SearchText + '%'
        OR CONCAT (
            p.LastName
            ,' '
            ,p.SecondLastName
            ) LIKE '%' + @v_SearchText + '%'
        )
    AND (
        nullif(@Mobile, 0) IS NULL
        OR p.MobileNo LIKE '%' + @Mobile + '%'
        )

优化方案 #

1. 消除隐式转换与函数包装字段 #

• 将MobileNo、ContactNo字段类型直接改为NVARCHAR(20)，避免查询时的cast转换——这种转换会导致索引失效，强制数据库执行全表扫描。
• 替换参数判断的函数：把nullif(@v_PhoneNo, '') IS NULL改为@v_PhoneNo = '' OR @v_PhoneNo IS NULL，isnull(@v_PatientDisplayId, '') = ''改为@v_PatientDisplayId = '' OR @v_PatientDisplayId IS NULL，减少函数调用的额外开销。

2. 预计算全名，减少动态拼接 #

• 新增持久化计算列存储完整姓名组合：

  ALTER TABLE Patients ADD FullName AS CONCAT(FirstName, ' ', ISNULL(MiddleName, ''), ' ', LastName, ' ', ISNULL(SecondLastName, '')) PERSISTED;
  

• 为FullName创建带过滤条件的非聚集索引：

  CREATE NONCLUSTERED INDEX IX_Patients_FullName ON Patients(FullName) 
  INCLUDE(DeleteInd, PatientDisplayId, OldMrno, MobileNo, ContactNo) 
  WHERE DeleteInd = 0;
  

  后续查询姓名时，直接匹配FullName LIKE '%' + @v_SearchText + '%'即可，无需多次执行CONCAT操作。

3. 用全文索引替代模糊匹配 #

• 为姓名相关字段创建全文索引，适合任意位置的关键词搜索，效率远高于LIKE：

  -- 先启用全文目录（如果未创建）
  CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
  -- 创建全文索引，替换PK_Patients为表的主键索引名
  CREATE FULLTEXT INDEX ON Patients(FirstName, MiddleName, LastName, SecondLastName) KEY INDEX PK_Patients;
  

• 查询时用CONTAINS替代LIKE：

  CONTAINS((FirstName, MiddleName, LastName, SecondLastName), @v_SearchText)
  

  支持多关键词匹配，性能比模糊匹配提升显著。

4. 优化索引策略 #

• 为精确匹配字段创建独立过滤索引：

  CREATE NONCLUSTERED INDEX IX_Patients_PatientDisplayId ON Patients(PatientDisplayId) WHERE DeleteInd = 0;
  CREATE NONCLUSTERED INDEX IX_Patients_OldMrno ON Patients(OldMrno) WHERE DeleteInd = 0;
  

• 创建覆盖索引减少回表：

  CREATE NONCLUSTERED INDEX IX_Patients_DeleteInd_Covering ON Patients(DeleteInd) 
  INCLUDE(PatientDisplayId, OldMrno, MobileNo, ContactNo, FirstName, MiddleName, LastName, SecondLastName) 
  WHERE DeleteInd = 0;
  

  这个索引包含查询需要的所有字段，避免数据库回表查询原数据。

5. 简化冗余查询逻辑 #

• 合并重复的手机号匹配条件：原查询中@v_PhoneNo和@Mobile都在匹配MobileNo，可合并为一个条件减少重复判断：

  AND (
      (@v_PhoneNo = '' OR @v_PhoneNo IS NULL) AND (@Mobile = 0 OR @Mobile IS NULL)
      OR MobileNo LIKE '%' + COALESCE(@v_PhoneNo, @Mobile) + '%'
      OR (@v_PhoneNo <> '' AND @v_PhoneNo IS NOT NULL AND ContactNo LIKE '%' + @v_PhoneNo + '%')
  )
  

• 移除重复的姓名组合判断：比如CONCAT(FirstName, ' ', LastName)已包含在FullName中，无需单独写条件。

6. 执行计划与参数优化 #

• 如果使用SQL Server，在查询末尾添加OPTION(RECOMPILE)生成最优执行计划，避免参数嗅探导致的低效执行计划：

  -- 在查询最后一行添加
  OPTION(RECOMPILE)
  

• 查看执行计划，确认是否存在全表扫描，针对性调整索引或查询逻辑。

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