SQL Server 索引 - 升序或降序，有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/743858/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server indexes - ascending or descending, what difference does it make?
提问by Joshua Carmody
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 版）的一列或多列上创建索引时，您可以指定每列上的索引是升序还是降序。我很难理解为什么这个选择甚至在这里。使用二元排序技术，查找不会同样快吗？它对我选择哪个订单有什么区别？
采纳答案by Quassnoi
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:
详情见我博客中的文章：
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 col1keeps ordered values of col1along 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 col1will be sorted descending, but the values of pkwithin each value of col1will be sorted ascending.
，则 的值col1将按降序排序，但pk每个值中的 值col1将按升序排序。
This means that the following query:
这意味着以下查询：
SELECT col1, pk
FROM mytable
ORDER BY
col1, pk DESC
can be served by ix_mytable_col1_descbut not by ix_mytable_col1.
可以由 提供，ix_mytable_col1_desc但不能由 提供ix_mytable_col1。
In other words, the columns that constitute a CLUSTERED INDEXon any table are always the trailing columns of any other index on that table.
换句话说，CLUSTERED INDEX在任何表上构成 a 的列始终是该表上任何其他索引的尾随列。
回答by Martin Smith
For a true single column index it makes little difference from the Query Optimiser's point of view.
对于真正的单列索引，它与查询优化器的观点几乎没有区别。
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 BACKWARDas can be seen in the Execution Plan. There is a slight difference however in that currently only FORWARDscans can be parallelised.
使用具有扫描方向的有序扫描，BACKWARD如执行计划中所示。然而，有一点不同，目前只有FORWARD扫描可以并行化。
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.
可以使用空间结果选项卡来验证这是因为后面的页面在两种情况下都具有升序键值的假设。
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 %% )
回答by Michael Haren
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 exampleof when it doesmatter.
@Quassnoi提供了一个很好的例子，当它没有关系。
