SQL Server索引-升序或降序,有什么区别?

当与复合索引一起使用时,这一点很重要:

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

单列上的索引可以以两种方式高效地用于排序。

有关详细信息,请参阅我的博客中的文章:

• Descending indexes

更新:

事实上,即使对于单列索引,这也很重要,尽管它并不那么明显。

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

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 将按降序排序,但 PK 在 COL1 将按升序排序。

这意味着以下查询:

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 %% )

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

请注意(正如您在提出问题时所建议的那样),排序顺序通常远不如正在索引的列重要(如果顺序与所需顺序相反,则系统可以反向读取索引)。我很少给出索引排序顺序的任何想法,而我却为索引所覆盖的列而苦恼。

@Quassnoi提供了 great example 当它 做 物质。