SQLServer 索引-升序或降序，有什么区别吗？

当您想要检索大量已排序的数据(而不是单个记录)时，排序顺序很重要。

请注意(正如您在问题中所建议的那样)排序顺序通常远没有您所索引的列重要(如果顺序与它想要的相反，系统可以反过来读取索引)。我很少考虑索引的排序顺序，而是考虑索引所覆盖的列。

@ Quassnoi 提供了关于 是的物质的 很好的例子。

这主要是在使用复合索引时的问题:

CREATE INDEX ix_index ON mytable (col1, col2 DESC);

可用于以下任何一种情况:

SELECT  *
FROM    mytable
ORDER BY
col1, col2 DESC

或:

SELECT  *
FROM    mytable
ORDER BY
col1 DESC, col2

但不是为了:

SELECT  *
FROM    mytable
ORDER BY
col1, col2

可以通过这两种方式有效地使用单个列的索引进行排序。

详情请看我博客上的文章:

• 下降索引

更新:

事实上，即使对于单个列索引来说，这也是很重要的，尽管它并不那么明显。

假设一个聚集表的一列上有一个索引:

CREATE TABLE mytable (
pk INT NOT NULL PRIMARY KEY,
col1 INT NOT NULL
)
CREATE INDEX ix_mytable_col1 ON mytable (col1)

col1上的索引保持 col1的有序值以及对行的引用。

由于表是聚集的，所以对行的引用实际上是 pk的值。它们也在 col1的每个值内排序。

这意味着索引的叶子实际上是在 (col1, pk)上排序的，而这个查询:

SELECT  col1, pk
FROM    mytable
ORDER BY
col1, pk

不需要分类。

如果我们按以下方式创建索引:

CREATE INDEX ix_mytable_col1_desc ON mytable (col1 DESC)

然后，col1的值将按降序排序，但是 col1的每个值中的 pk值将按升序排序。

这意味着以下查询:

SELECT  col1, pk
FROM    mytable
ORDER BY
col1, pk DESC

可以由 ix_mytable_col1_desc提供，但不能由 ix_mytable_col1提供。

换句话说，在任何表上构成 CLUSTERED INDEX的列始终是该表上任何其他索引的尾列。

对于一个真正的单列索引来说，它与查询优化器的视角几乎没有什么区别。

用于表定义

CREATE TABLE T1( [ID] [int] IDENTITY NOT NULL,
[Filler] [char](8000) NULL,
PRIMARY KEY CLUSTERED ([ID] ASC))

质询

SELECT TOP 10 *
FROM T1
ORDER BY ID DESC

使用扫描方向为 BACKWARD的有序扫描，如执行计划中所示。但是有一个细微的差别，目前只有 FORWARD扫描可以并行化。

但是 它可以在逻辑碎片方面产生很大的不同。如果创建索引时键降序，但是新行附加了升序键值，那么最终每个页面都会出现逻辑顺序错误。这会严重影响扫描表时 IO 读取的大小，因为表不在缓存中。

查看碎片结果

                    avg_fragmentation                    avg_fragment
name   page_count   _in_percent         fragment_count   _size_in_pages
------ ------------ ------------------- ---------------- ---------------
T1     1000         0.4                 5                200
T2     1000         99.9                1000             1

下面的脚本

/*Uses T1 definition from above*/
SET NOCOUNT ON;


CREATE TABLE T2( [ID] [int] IDENTITY NOT NULL,
[Filler] [char](8000) NULL,
PRIMARY KEY CLUSTERED ([ID] DESC))


BEGIN TRAN


GO
INSERT INTO T1 DEFAULT VALUES
GO 1000
INSERT INTO T2 DEFAULT VALUES
GO 1000


COMMIT


SELECT object_name(object_id) AS name,
page_count,
avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages
FROM
sys.dm_db_index_physical_stats(db_id(), object_id('T1'), 1, NULL, 'DETAILED')
WHERE  index_level = 0
UNION ALL
SELECT object_name(object_id) AS name,
page_count,
avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages
FROM
sys.dm_db_index_physical_stats(db_id(), object_id('T2'), 1, NULL, 'DETAILED')
WHERE  index_level = 0

可以使用空间结果选项卡来验证这种假设，即这是因为后面的页面在两种情况下都具有升键值。

SELECT page_id,
[ID],
geometry::Point(page_id, [ID], 0).STBuffer(4)
FROM   T1
CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
UNION ALL
SELECT page_id,
[ID],
geometry::Point(page_id, [ID], 0).STBuffer(4)
FROM   T2
CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )