主页 / dba / 问题 / 317999
Accepted
Geezer
Asked: 2022-10-10 05:53:11 +0800 CST

为什么当我有索引时会得到排序？

  • 772

Azure SQL 数据库。

我有一个表，我需要从中获取第一行和最近的行，Col1并Col2基于CreateDate.

CREATE TABLE dbo.table1 (
    Id            INT    IDENTITY(1,1) PRIMARY KEY ,
    Col1        VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    Col2        VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    CreateDate    DATETIME NOT NULL
) ;

我有一个像这样的索引：

CREATE INDEX IX__table1_ASC
ON dbo.table1 (Col1, Col2, CreateDate );

我获取第一行的查询是（在此处计划）：

--Get the first row
SELECT    TOP (1) WITH TIES
        *
FROM    table1
ORDER BY ROW_NUMBER()
        OVER (PARTITION BY Col1, Col2
              ORDER BY CreateDate );

索引扫描使用的是IX__table1_ASC我创建的索引 ()，但为什么我得到一个排序？

在此处输入图像描述

我获取最新行的查询（在此处计划）：

--get latest row
SELECT    TOP (1) WITH TIES
        *
FROM    table1
ORDER BY ROW_NUMBER()
        OVER (PARTITION BY Col1, Col2
              ORDER BY CreateDate DESC); --desc here

同样，索引扫描使用的是索引 ( IX__table1_ASC)，但这次我得到了两种。索引扫描后的第一个。优化器还不够聪明，无法以相反的顺序读取索引吗？再说一次，第二类是干什么用的？

实际的表非常大，因此您可以想象排序的成本很高。我怎样才能在这里最好地优化？

在此处输入图像描述

sql-server query-performance

1 个回答

  1. Best Answer

    索引扫描使用的是我创建的索引 (IX__table1_ASC)，但为什么我得到一个排序？

    因为您使用了一种低效的方式来选择每组的顶行。

    只需使用

    WITH T
     AS (SELECT *,
                ROW_NUMBER()
                  OVER (
                    PARTITION BY Col1, Col2
                    ORDER BY CreateDate ) AS RN
         FROM   table1)
SELECT *
FROM   T
WHERE  RN = 1

    在此处输入图像描述

    这里TOP (1) WITH TIES只是选择行号等于 1 的所有行的一种更模糊且效率更低的方法。不幸的是，StackOverflow 上的一些回答者没有充分的理由使用这种方法，除了喜欢新奇之外，我无法辨别。

    在您的第一个执行计划中，排序不是为了计算行号，而是在没有排序的情况下根据该行编号的结果对行进行排序。

    关于您的第二个查询，这是一个长期存在的优化器限制 - 您可以获得向后排序的索引扫描，并且没有使用下面的排序。

    WITH T
     AS (SELECT *,
                ROW_NUMBER()
                  OVER (
                    PARTITION BY Col1, Col2
                    ORDER BY CreateDate DESC ) AS RN
         FROM   table1
         ORDER BY Col1 DESC, Col2 DESC, CreateDate DESC
         OFFSET 0 ROWS
         )
SELECT *
FROM   T
WHERE  RN = 1

    这OFFSET 0 ROWS是一种允许ORDER BY在派生表中使用的 hack，这在 SQL Server 中通常是不允许的。重要的是给优化器一个单独的理由来考虑最优排序。

    演示级别ORDER BY可以达到相同的目的，但我更喜欢将 hack 放在更靠近需要它的东西的地方。此方法还允许您指定不同的呈现顺序。请记住，OFFSET 0有一天可能会被优化，就像TOP (100) PERCENT现在一样。

    在 SQL Server 2000 中，有些人过去常常通过添加TOP 100 PERCENT ... ORDER BY. 至少在大多数情况下，这样做的效果是，仅从SELECT视图中执行一个简单的操作，而外部查询上没有任何内容，就会ORDER BY以所需的顺序返回行。这从未得到保证，在 SQL Server 2005 中，逻辑被添加到优化器中，TOP 100 PERCENT在这种情况下刚刚优化为逻辑冗余。未来可能会发生同样的情况，OFFSET 0 ROWS因为它同样是多余的。

    就我个人而言，我希望任何多余的工程努力都可以用于改进优化，所以这种黑客攻击并不是必需的！

    • 16

相关问题