两种自连接T-SQL查询性能疑问：SQL Server带索引为何Query2更快？

针对你提到的两个自连接查询性能差异问题，核心原因和索引相关的疑问可以拆解为以下几点：

1. Query2更快的核心原因：提前缩小数据集 #

Query2通过子查询select * from attr where attr_type=N先将原表按attr_type拆分成三个独立的小数据集，每个数据集只保留对应类型的行，之后再基于ticket_id做连接。这种方式的优势在于：

• 参与连接的数据量大幅减少：如果attr_type分布均匀，每个子查询返回的行数仅为原表的1/3左右（假设只有3种类型），后续连接操作的处理量远小于Query1。
• 过滤逻辑前置：避免了连接过程中逐行检查attr_type的开销，Query1中a2.attr_type=2写在JOIN条件里，SQL Server可能先通过ticket_id匹配行，再过滤attr_type，若同一个ticket_id下有大量不同attr_type的行，这一步过滤会产生额外开销。

2. 为何ticket_id单键索引未发挥预期作用 #

你认为Query1能利用ticket_id索引更快，其实忽略了单键索引的局限性：

• 需要回表操作：如果只有ticket_id的单键索引，SQL Server通过索引找到匹配的ticket_id后，必须回表（执行Key Lookup或Bookmark Lookup）读取attr_type和attr_val的值，才能判断是否符合attr_type=2/3的条件。回表会带来大量随机IO，数据量较大时开销极高。
• 索引覆盖度不足：如果没有包含attr_type和attr_val的覆盖索引，Query1的JOIN过程无法仅通过索引完成过滤和数据获取，必须依赖回表，这会抵消索引带来的优势。
• 查询优化器的成本估算：SQL Server优化器会根据表的统计信息判断执行计划成本。当attr_type过滤能大幅降低数据量时，优化器会认为先过滤再连接的成本更低，因此即使有ticket_id索引，也会选择Query2的执行路径（或对Query1做等价重写，实际执行逻辑和Query2一致）。

3. 优化建议 #

如果想让两个查询的性能都达到最优，建议创建联合覆盖索引：

CREATE INDEX IX_attr_attrtype_ticketid ON attr(attr_type, ticket_id) INCLUDE(attr_val);

这个索引的优势在于：

• 可以快速过滤出指定attr_type的行，同时直接通过索引获取ticket_id和attr_val，不需要回表。
• 不管是Query1的JOIN条件过滤，还是Query2的子查询过滤，都能高效利用该索引，大幅减少IO开销。

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