SQL Server索引——升序或降序，有什么区别?

When you create an index on a column or number of columns in MS SQL Server (I'm using version 2005), you can specify that the index on each column be either ascending or descending. I'm having a hard time understanding why this choice is even here. Using binary sort techniques, wouldn't a lookup be just as fast either way? What difference does it make which order I choose?

当您在一个列上创建索引或在MS SQL Server中创建多个列(我使用的是2005版)时，您可以指定每个列上的索引是升序还是降序的。我很难理解为什么会有这样的选择。使用二进制排序技术，查找是否和其他方法一样快?我选择的顺序有什么不同?

3 个解决方案

#1

This primarily matters when used with composite indexes:

当使用复合索引时，这主要是重要的:

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

can be used for either:

可用于以下任何一种:

SELECT  *
FROM    mytable
ORDER BY
        col1, col2 DESC

or:

或者:

SELECT  *
FROM    mytable
ORDER BY
        col1 DESC, col2

, but not for:

,但不是为:

SELECT  *
FROM    mytable
ORDER BY
        col1, col2

An index on a single column can be efficiently used for sorting in both ways.

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

See the article in my blog for details:

详情请参阅我的博客文章:

• Descending indexes
• 降序索引

Update:

更新:

In fact, this can matter even for a single column index, though it's not so obvious.

事实上，即使对于单个列索引，这也很重要，尽管不是很明显。

Imagine an index on a column of a clustered table:

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

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

The index on col1 keeps ordered values of col1 along with the references to rows.

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

Since the table is clustered, the references to rows are actually the values of the pk. They are also ordered within each value of col1.

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

This means that that leaves of the index are actually ordered on (col1, pk), and this query:

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

SELECT  col1, pk
FROM    mytable
ORDER BY
        col1, pk

needs no sorting.

不需要排序。

If we create the index as following:

如果我们创建索引如下:

CREATE INDEX ix_mytable_col1_desc ON mytable (col1 DESC)

, then the values of col1 will be sorted descending, but the values of pk within each value of col1 will be sorted ascending.

，则col1的值按降序排序，而col1的每个值中的pk值按升序排序。

This means that the following query:

这意味着以下查询:

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

can be served by ix_mytable_col1_desc but not by ix_mytable_col1.

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

In other words, the columns that constitute a CLUSTERED INDEX on any table are always the trailing columns of any other index on that table.

换句话说，组成任何表上聚集索引的列总是该表上任何其他索引的尾列。

#2

For a true single column index it makes little difference from the Query Optimiser's point of view.

对于一个真正的单列索引，它与查询Optimiser的观点没什么区别。

For the table definition

为表定义

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

The Query

查询

SELECT TOP 10 *
FROM T1
ORDER BY ID DESC

Uses an ordered scan with scan direction BACKWARD as can be seen in the Execution Plan. There is a slight difference however in that currently only FORWARD scans can be parallelised.

使用顺序扫描，扫描方向向后，可以在执行计划中看到。不过，目前只有向前扫描才能并行执行，这一点略有不同。

However it can make a big difference in terms of logical fragmentation. If the index is created with keys descending but new rows are appended with ascending key values then you can end up with every page out of logical order. This can severely impact the size of the IO reads when scanning the table and it is not in cache.

然而，它可以在逻辑碎片方面产生很大的差异。如果索引是用降序的键创建的，但是新行附加升序的键值，那么您就可以得到没有逻辑顺序的每个页面。当扫描表时，这会严重影响IO读取的大小，而且它不在缓存中。

See the fragmentation results

看到分裂的结果

                    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

for the script below

下面的脚本

/*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 

It's possible to use the spatial results tab to verify the supposition that this is because the later pages have ascending key values in both cases.

可以使用spatial results选项卡来验证这一假设，因为后面的页面在这两种情况下都具有升序键值。

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

#3

The sort order matters when you want to retrieve lots of sorted data, not individual records.

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

Note that (as you are suggesting with your question) the sort order is typically far less significant than what columns you are indexing (the system can read the index in reverse if the order is opposite what it wants). I rarely give index sort order any thought, whereas I agonize over the columns covered by the index.

请注意(正如您所提出的问题)排序顺序通常远不如您所在的列重要(如果排序顺序与它想要的顺序相反，索引系统可以反向读取索引)。我很少考虑索引排序顺序，但是我对索引所包含的列感到痛苦。

@Quassnoi provides a great example of when it does matter.

@Quassnoi提供了一个很好的例子来说明它什么时候起作用。