SQL Server含关联表与多手机号列的查询优化咨询

背景信息 #

• PatientId和PracticeId为对应表的主键
• 6个手机号相关列（PhoneNumber和PhoneCountryCode）均未建立索引
• pp.PracticePatientStatus未建立索引
• 所有PhoneNumber列定义为NVARCHAR(10)

技术咨询问题 #

1. 如何为这6列创建索引？是创建包含6列的单索引（若可行，列顺序如何？）还是为每列单独创建索引？
2. 当列之间使用AND或OR逻辑时，索引的行为是否存在差异？
3. 对于pp.PracticePatientStatus，是否需要单独创建索引？

对应的查询代码 #

CREATE TYPE dbo.UDTTable_PhoneSearch AS TABLE 
(
    PhoneCountryCode SMALLINT     NOT NULL,
    PhoneNumber      NVARCHAR(10) NOT NULL
);
GO

CREATE PROCEDURE dbo.Search_Patients_ByPhone
    (@RowCount INT,
     @PracticeId INT,
     @Filter dbo.UDTTable_PhoneSearch READONLY,
     @IncludeInactivePatients BIT)
AS
BEGIN
    SELECT TOP (@RowCount) *
    FROM dbo.Patient AS p WITH (NOLOCK)
    INNER JOIN dbo.PracticePatient pp ON p.PatientId = pp.PatientId 
                                      AND pp.PracticeId = @PracticeId
    INNER JOIN @Filter f ON (p.PatientCellPhoneNumber = f.PhoneNumber AND COALESCE(p.PatientCellPhoneCountryCode, 1) = f.PhoneCountryCode)
                         OR (p.PatientHomePhoneNumber = f.PhoneNumber AND COALESCE(p.PatientHomePhoneCountryCode, 1) = f.PhoneCountryCode)
                         OR (p.PatientWorkPhoneNumber = f.PhoneNumber AND COALESCE(p.PatientWorkPhoneCountryCode, 1) = f.PhoneCountryCode)
    WHERE 
        @IncludeInactivePatients = 1 
        OR pp.PracticePatientStatus = 1
    ORDER BY 
        p.PatientId DESC 
END;

问题解答 #

1. 6个手机号相关列的索引创建方案 #

你的查询通过OR逻辑匹配三组（手机号+国家码）组合，单索引包含6列完全无效——SQL Server无法利用这类索引高效匹配OR条件。正确方案是为每组手机号+国家码创建单独的复合索引：

• 索引1：CREATE NONCLUSTERED INDEX IX_Patient_CellPhone ON dbo.Patient (PatientCellPhoneNumber, PatientCellPhoneCountryCode);
• 索引2：CREATE NONCLUSTERED INDEX IX_Patient_HomePhone ON dbo.Patient (PatientHomePhoneNumber, PatientHomePhoneCountryCode);
• 索引3：CREATE NONCLUSTERED INDEX IX_Patient_WorkPhone ON dbo.Patient (PatientWorkPhoneNumber, PatientWorkPhoneCountryCode);

列顺序上，将PhoneNumber前置，原因有两点：一是查询中先匹配手机号再匹配国家码，符合索引前缀匹配规则；二是手机号的选择性通常远高于国家码，前置高选择性列能让索引过滤更高效。

2. AND/OR逻辑下的索引行为差异 #

• AND逻辑：多列用AND连接时，复合索引可以被高效利用。例如A=X AND B=Y，创建(A,B)的复合索引后，SQL Server能直接定位到同时满足两个条件的行，索引列顺序会影响效率（高选择性列前置更优）。
• OR逻辑：多列用OR连接时，单个复合索引基本无法发挥作用。像你的查询里三组条件OR的场景，SQL Server会分别使用每组条件对应的索引，再合并结果集；如果没有对应索引，只能执行全表扫描。这也是必须为每组条件单独建索引的核心原因。

3. PracticePatientStatus的索引建议 #

是否需要单独建索引，取决于数据分布：

• 如果大部分患者为活跃状态（PracticePatientStatus=1），单独建索引意义不大。因为查询条件是@IncludeInactivePatients=1 OR pp.PracticePatientStatus=1，当@IncludeInactivePatients=0时才会过滤非活跃患者，若非活跃患者占比极低，全表扫描（或利用现有主键索引）的成本比使用索引更低。
• 如果非活跃患者占比不低（如超过10%-20%），应该创建包含PracticeId和PatientId的复合索引：CREATE NONCLUSTERED INDEX IX_PracticePatient_Status_PracticeId ON dbo.PracticePatient (PracticePatientStatus, PracticeId) INCLUDE (PatientId);。该索引能快速过滤出指定诊所下的活跃患者，同时包含PatientId可直接关联Patient表，避免回表操作。

另外，存储过程中使用的WITH (NOLOCK)需谨慎，该提示会允许脏读，仅在业务允许读取未提交数据的场景下使用。

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