SQL Server索引 - 升序或降序，它有什么区别？ [英] SQL Server indexes - ascending or descending, what difference does it make?

问题描述

当您在MS SQL Server中创建列或列数时（我使用的是版本2005），您可以指定每列的索引是升序还是降序。我很难理解为什么这个选择就在这里。使用二进制排序技术，任何一种查找都不会那么快吗？它选择哪个订单有什么区别？

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?

推荐答案

这在与复合索引一起使用时非常重要：

This primarily matters when used with composite indexes:

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

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

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

查看文章在我的博客中了解详情：

See the article in my blog for details:

• 降序索引

更新：

事实上，即使对于单列索引也是如此，尽管它并不那么明显。

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)

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

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

由于表格是在clustered中，对行的引用实际上是 pk 的值。它们也在 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.

这意味着索引的叶子实际上是在（col1，pk），此查询：

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

SELECT  col1, pk
FROM    mytable
ORDER BY
        col1, pk

需求没有排序。

如果我们创建索引如下：

If we create the index as following:

CREATE INDEX ix_mytable_col1_desc ON mytable (col1 DESC)

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

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

这意味着以下查询：

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

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

can be served by ix_mytable_col1_desc but not by ix_mytable_col1.

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

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.

这篇关于SQL Server索引 - 升序或降序，它有什么区别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！