多列索引适配异常：特定查询性能未提升及复现方法

一、复现性能差异的具体操作步骤 #

• 搭建测试环境

  1. 创建测试表，包含name、age、address及额外字段（模拟真实业务表结构）：

     CREATE TABLE dbo.employee (
         id INT IDENTITY(1,1) PRIMARY KEY,
         name VARCHAR(50),
         age INT,
         address VARCHAR(100),
         email VARCHAR(100),
         hire_date DATETIME
     )
     

  2. 插入大规模测试数据，比如生成100万条以上记录，其中至少包含1万条name='lucas'的记录，且这些记录中address='street 6'的条目覆盖多个不同age值，确保数据量足够触发性能差异。
  3. 创建指定的非聚集索引：

     CREATE NONCLUSTERED INDEX [IX_EMPLOYEE] 
     ON dbo.employee (name, age, address)
     

• 执行查询并对比性能

  1. 执行第一个查询，查看执行计划并记录耗时：

     select * 
     from dbo.employee 
     where employee.name = 'lucas' 
       and employee.age = 36 
       and employee.address = 'street 6'
     

     此时执行计划会显示使用IX_EMPLOYEE索引的索引查找，耗时极低。
  2. 执行第二个查询，同样查看执行计划并记录耗时：

     select * 
     from dbo.employee 
     where employee.name = 'lucas' 
       and employee.address = 'street 6'
     

     执行计划会显示表扫描或索引扫描，耗时远高于第一个查询。

• 验证无效操作
  创建与原索引列完全相同的重复索引：

  CREATE NONCLUSTERED INDEX [IX_EMPLOYEE_DUP] 
  ON dbo.employee (name, age, address)
  

  再次执行第二个查询，性能无任何改善，执行计划仍未切换为高效的索引查找。

二、问题核心原因 #

• 多列非聚集索引遵循最左匹配原则：索引的列顺序决定了查询条件能否触发高效的索引查找。当前索引首列是name，第二列是age，第三列是address，只有当查询条件按索引顺序匹配（先name，再age，最后address）时，才能利用索引的有序结构快速定位数据。
• 第二个查询跳过了age列，直接用name+address作为条件，此时索引无法通过有序定位筛选数据，只能扫描整个索引或表来匹配记录，导致性能骤降。
• 重复创建相同列的索引无意义，因为索引结构完全一致，SQL Server优化器不会因此改变执行计划。

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