选择索引列返回顺序与表设计不一致的问题排查与解决

问题场景 #

执行select top 1 * from numbers能得到预期的最新id（50），但执行select top 1 id from numbers却返回了最小的id（1）。明明给id列定义了unique clustered (id desc)的聚集索引，期望新记录按类FIFO存储，方便扫描最新数据，但仅查询索引列时结果顺序不符合预期。

对应的SQL代码：

drop table if exists numbers
create table numbers (
    h char(1),
    id int identity(-2147483648,1) not null
    primary key nonclustered (id),
    index ix unique clustered (id desc)
)
insert into numbers (h) values
    ('h'), ('h'), ('h'), ('h'), ('h'),
    ('h'), ('h'), ('h'), ('h'), ('h'),
    ('h'), ('h'), ('h'), ('h'), ('h'),
    ('h'), ('h'), ('h'), ('h'), ('h'),
    ('h'), ('h'), ('h'), ('h'), ('h'),
    ('h'), ('h'), ('h'), ('h'), ('h'),
    ('h'), ('h'), ('h'), ('h'), ('h'),
    ('h'), ('h'), ('h'), ('h'), ('h'), 
    ('h'), ('h'), ('h'), ('h'), ('h'),
    ('h'), ('h'), ('h'), ('h'), ('h')
; select top 1 id from numbers -- 返回1
select top 1* from numbers     -- 返回50

问题解答 #

1. 索引是否被错误实现？ #

没有错误实现。你的聚集索引ix确实是按id desc顺序组织数据的，问题出在SQL Server查询优化器的索引选择逻辑，而非索引定义本身。

当执行select top 1 id from numbers时，优化器发现非聚集主键索引（primary key nonclustered (id)）是一个覆盖索引——它只包含id列，比聚集索引（包含所有列）更小、更高效，所以会优先选择这个索引。而这个非聚集主键索引是按id asc排序的，自然返回最小的id值。

2. 如何达成预期功能？ #

有两种可靠的方式：

• 显式指定排序规则：在查询中加上ORDER BY id desc，这是最稳妥的做法，因为SQL标准中，TOP子句如果没有搭配ORDER BY，返回的结果顺序是未定义的，数据库可以任意返回符合条件的行。

  select top 1 id from numbers order by id desc -- 返回50
  

• 强制使用聚集索引：通过查询提示让优化器使用定义好的聚集索引ix，确保按聚集索引的顺序返回结果。

  select top 1 id from numbers with (index(ix)) -- 返回50
  

3. 为何索引的实际顺序与定义不符？ #

不是索引顺序不符，是你误解了查询结果顺序的逻辑：

• 聚集索引ix确实是按id desc存储数据的，但没有ORDER BY的TOP查询不保证结果顺序。优化器会选择它认为最高效的索引来执行查询，而非必须使用聚集索引。
• 当只查询id列时，非聚集主键索引的开销更低（体积小，扫描更快），所以优化器选择了这个按id asc排序的索引，导致返回的是最小的id，让你误以为聚集索引顺序不对。

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