ROW_NUMBER() OVER PARTITION优化疑问：MSSQL为何执行全索引扫描？

这个问题其实挺常见的，我来帮你拆解下为什么会出现这种情况，以及对应的优化方向：

Core Reasons Behind the Full Index Scan #

你已经创建了以(Code, Price)为键列、包含所有其他字段的覆盖索引，理论上应该能高效定位每个Code的最低价格行，但优化器选择全扫描，主要有这几个关键原因：

1. Outdated or Inaccurate Statistics #

SQL Server's query optimizer relies entirely on statistics to estimate execution costs. If your statistics are not up-to-date, the optimizer might not know that Code only has 4000 unique values—for example, old stats might show a much higher cardinality (like hundreds of thousands) for Code. In that case, the optimizer would judge that the cost of grouping/sorting is far higher than a full scan, so it picks the latter.

In addition, if the row distribution data in statistics is inaccurate (e.g., one Code accounts for a huge proportion of the data), it will also skew the optimizer's decision.

2. Query Syntax Doesn't Leverage Index Order #

Your query写法 might not signal to the optimizer that it can use the ordered nature of the (Code, Price) index. For example, if you're using a subquery to get MIN(Price) then joining back to the original table:

SELECT o.*
FROM Offers o
JOIN (
    SELECT Code, MIN(Price) AS MinPrice
    FROM Offers
    GROUP BY Code
) m ON o.Code = m.Code AND o.Price = m.MinPrice

This syntax might lead the optimizer to do a full index scan for grouping aggregation, instead of using the index's ordered structure to skip subsequent rows of each Code (since Price is sorted, the first row is already the minimum).

3. Optimizer Judges Skip Scan as More Costly #

SQL Server supports Index Skip Scan for low-cardinality index columns (like your Code with only 4000 unique values), which can skip through different Code values and directly read the lowest Price row for each. But if the optimizer estimates that the total IO cost of skip scan (e.g., 4000 lookups + reading corresponding rows) is higher than a full index scan, it will choose the full scan. This usually happens when statistics are inaccurate, leading the optimizer to misjudge costs.

Optimization Suggestions #

You can troubleshoot and optimize by following these steps:

• Update Statistics: Force a full update of statistics to give the optimizer accurate data distribution:

  UPDATE STATISTICS Offers WITH FULLSCAN;
  

• Adjust Query Syntax: Use a window function approach to explicitly guide the optimizer to leverage the index's order:

  WITH RankedOffers AS (
      SELECT *,
             ROW_NUMBER() OVER(PARTITION BY Code ORDER BY Price ASC) AS rn
      FROM Offers
  )
  SELECT *
  FROM RankedOffers
  WHERE rn = 1;
  

  With this syntax, the optimizer can scan along the ordered (Code, Price) index, only picking the first row (rn=1) for each Code—no need to scan all 10 million rows.
• Verify Index Structure: Confirm your index is created with Code ASC, Price ASC and includes all required fields:

  sp_helpindex Offers;
  

• (Optional) Force Index Hint: If the above steps don't work, you can try forcing the optimizer to use your index (not recommended for long-term use, prefer letting the optimizer decide on its own):

  WITH RankedOffers AS (
      SELECT *,
             ROW_NUMBER() OVER(PARTITION BY Code ORDER BY Price ASC) AS rn
      FROM Offers WITH(INDEX(Your_Index_Name))
  )
  SELECT *
  FROM RankedOffers
  WHERE rn = 1;
  

内容的提问来源于stack exchange，提问作者Nick P.