为何基于索引列的查询速度未快于字符串前缀查询？

问题背景 #

创建了包含非聚集索引Ix_Brands（基于Brand列）的Products表，通过四种方式测试按品牌查询的性能：

• ADO + 产品名前缀匹配（WHERE ProductName LIKE 'Brand_%'）
• ADO + 索引列精确匹配（WHERE Brand='Brand'）
• EF + 产品名前缀匹配（StartsWith(brand + "_")）
• EF + 索引列精确匹配（Equals(brand, StringComparison.OrdinalIgnoreCase)）

测试结果显示：EF的索引查询符合预期（最快），但ADO的索引查询反而比前缀查询慢，且两个ADO查询均执行聚集索引扫描，执行计划完全一致。

核心原因分析 #

1. 非聚集索引的书签查找成本过高 #

你创建的Ix_Brands是仅包含Brand和主键ID的非聚集索引，当执行SELECT *时，SQL Server需要先通过非聚集索引找到匹配的ID，再通过主键去聚集索引（PK_Products）中查找其他列的数据（即键查找/书签查找）。

由于每个品牌约占总数据量的1/6（10万行/6个品牌），返回行数占比约16%，SQL Server优化器认为直接扫描整个聚集索引的成本，低于先查非聚集索引再做大量键查找的总成本，因此选择了聚集索引扫描。

2. ADO查询的执行计划复用问题 #

第一个ADO前缀查询（ProductName LIKE）已经触发了聚集索引扫描，SQL Server会缓存这个执行计划。后续的ADO索引列查询虽然逻辑不同，但由于查询模式相似（硬编码字符串、返回全列），优化器可能复用了之前的扫描计划，而非重新评估使用非聚集索引的成本。

3. EF查询的差异 #

EF的索引查询表现更优，大概率是因为：

• EF默认生成参数化查询，优化器会针对参数化语句生成更合理的执行计划（而非复用之前的扫描计划）；
• EF上下文的一级缓存或查询编译缓存，减少了重复查询的额外开销。

验证与解决方案 #

方案1：创建覆盖索引 #

将非聚集索引改为覆盖索引，包含所有需要查询的列，避免书签查找：

DROP INDEX Ix_Brands ON Products;
CREATE NONCLUSTERED INDEX Ix_Brands ON Products(Brand)
INCLUDE (ProductName, Field1, Field2, Field3, Field4, Field5);

此时执行SELECT * FROM Products WHERE Brand='xxx'，SQL Server可直接从非聚集索引获取所有数据，无需访问聚集索引，执行计划会切换为索引查找。

方案2：强制使用索引 #

在ADO查询中显式指定使用Ix_Brands，验证优化器的成本判断是否正确：

cmd.CommandText = $"select * from products with(index(Ix_Brands)) where brand='{brand}'";

若执行时间明显降低，说明优化器的成本估算存在偏差，可考虑更新统计信息。

方案3：参数化ADO查询 #

避免硬编码字符串，使用参数化查询，让优化器生成更合理的执行计划，同时避免SQL注入：

foreach (var brand in Brands)
{
    cmd.CommandText = "select * from products where brand=@brand";
    cmd.Parameters.Clear();
    cmd.Parameters.Add("@brand", SqlDbType.NVarChar, 100).Value = brand;
    var da = new SqlDataAdapter(cmd);
    var dt = new DataTable();
    da.Fill(dt);
}

方案4：更新统计信息 #

在插入10万条数据后，手动更新表统计信息，确保优化器拥有最新的数据分布情况：

UPDATE STATISTICS Products;

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