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

2021-12-02 00:00:00 indexing optimization sql sql-server

当您在 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.

详情见我博客文章:

  • 降序索引

更新:

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

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.

由于表是集群的，对行的引用实际上是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.

换句话说，构成任何表上CLUSTERED INDEX的列总是该表上任何其他索引的尾随列.

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.

相关文章