带WHERE子句的SELECT查询性能下降问题咨询

• 无WHERE子句查询返回快的本质
  不带筛选条件的全表查询不需要做行级判断，数据库可以直接按存储顺序逐页读取数据，你在SSMS中看到的「立刻返回行」只是流式输出的第一页结果，并不代表全量查询执行完成，后续数据会边扫描边返回，因此不会有明显的等待感知。

• 带WHERE日期筛选查询耗时高的核心原因

  1. 缺少匹配的索引：绝大多数情况是myDate列没有创建非聚集索引，也没有包含column1、column2的覆盖索引。此时数据库需要遍历500万行全表数据，逐行判断日期是否符合区间要求，全部筛选完成后才能返回结果集，自然需要数十秒的执行时间。
  2. 回表开销过大：如果myDate列有单独的索引，但索引没有包含你要查询的column1、column2字段，数据库需要先从日期索引中筛选出符合条件的行标识，再回到主键索引或堆表中读取对应行的指定字段值，符合条件的行数越多，随机IO的开销就越高，执行效率甚至会低于直接全表扫描。
  3. 统计信息过期：如果表的统计信息长期未更新，数据库优化器会错误预估符合条件的行数，选择错误的执行计划（比如内存分配不足导致排序/筛选操作溢出到磁盘），也会大幅拉长执行耗时。

• 应用抛出超时但SSMS可执行完成的原因
  应用默认的CommandTimeout配置为30秒，超过这个时间未收到数据库返回的完整结果集就会触发超时异常，而SSMS默认没有查询超时限制，因此可以等待40秒拿到最终结果。

• 优先为myDate列创建包含查询字段的覆盖索引，避免全表扫描和回表开销，示例语句：

CREATE NONCLUSTERED INDEX IX_table_myDate ON [table] (myDate) INCLUDE (column1, column2);

• 定期更新表统计信息，避免优化器生成错误执行计划：

UPDATE STATISTICS [table];

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