主页 / dba / 问题 / 68256
Accepted
Martin Smith
Asked: 2014-06-15 15:27:34 +0800 CST

在可为空的复合索引上重新加入范围搜索？

  • 772

对于以下架构和示例数据

CREATE TABLE T
  (
     A INT NULL,
     B INT NOT NULL IDENTITY,
     C CHAR(8000) NULL,
     UNIQUE CLUSTERED (A, B)
  )

INSERT INTO T
            (A)
SELECT NULLIF(( ( ROW_NUMBER() OVER (ORDER BY @@SPID) - 1 ) / 1003 ), 0)
FROM   master..spt_values

应用程序正在以 1,000 行块的聚集索引顺序处理此表中的行。

从以下查询中检索前 1,000 行。

SELECT TOP 1000 *
FROM   T
ORDER  BY A, B

该组的最后一行如下

+------+------+
|  A   |  B   |
+------+------+
| NULL | 1000 |
+------+------+

有没有办法编写一个查询，只寻找该复合索引键，然后跟随它来检索下一个 1000 行的块？

/*Pseudo Syntax*/
SELECT TOP 1000 *
FROM   T
WHERE (A, B) is_ordered_after (@A, @B)
ORDER  BY A, B

到目前为止，我设法获得的最低读取次数是 1020，但查询似乎太复杂了。有没有更简单的相同或更高效率的方法？也许一个设法在一个范围内完成所有工作的人寻求？

DECLARE @A INT = NULL, @B INT = 1000

;WITH UnProcessed
     AS (SELECT *
         FROM   T
         WHERE  ( EXISTS(SELECT A
                         INTERSECT
                         SELECT @A)
                  AND B > @B )
         UNION ALL
         SELECT *
         FROM   T
         WHERE @A IS NULL AND A IS NOT NULL
         UNION ALL
         SELECT *
         FROM   T
         WHERE A > @A        
         )
SELECT TOP 1000 *
FROM   UnProcessed
ORDER  BY A,
          B

在此处输入图像描述

FWIW：如果创建列A并NOT NULL使用标记值-1代替，等效的执行计划当然看起来更简单

在此处输入图像描述

但是计划中的单次搜索运算符仍然执行两次搜索，而不是将其折叠到一个连续的范围内，并且逻辑读取几乎相同，所以我怀疑这可能和它会得到的一样好？

sql-server index

1 个回答

  1. Best Answer

    有没有办法编写一个查询，只寻找该复合索引键，然后跟随它来检索下一个 1000 行的块？

    我最喜欢的解决方案是使用API游标：

    SET NOCOUNT ON;
SET STATISTICS IO ON;

DECLARE 
    @cur integer,
    -- FAST_FORWARD, AUTO_FETCH, AUTO_CLOSE, CHECK_ACCEPTED_TYPES, FAST_FORWARD_ACCEPTABLE
    @scrollopt integer = 16 | 8192 | 16384 | 32768 | 1048576,
    -- READ_ONLY, CHECK_ACCEPTED_OPTS, READ_ONLY_ACCEPTABLE
    @ccopt integer = 1 | 32768 | 65536, 
    @rowcount integer = 1000,
    @rc integer;

-- Open the cursor and return (up to) the first 1000 rows
EXECUTE @rc = sys.sp_cursoropen
    @cur OUTPUT,
    N'
    SELECT A, B, C
    FROM T
    ORDER BY A, B;
    ',
    @scrollopt OUTPUT,
    @ccopt OUTPUT,
    @rowcount OUTPUT;

IF @rc <> 16 -- FastForward cursor automatically closed
BEGIN
    -- Name the cursor so we can use CURSOR_STATUS
    EXECUTE sys.sp_cursoroption
        @cur, 
        2, 
        'MyCursorName';

    -- Until the cursor auto-closes
    WHILE CURSOR_STATUS('global', 'MyCursorName') = 1
    BEGIN
        EXECUTE sys.sp_cursorfetch
            @cur,
            2,
            0,
            1000;
    END;
END;

SET STATISTICS IO OFF;

    总体策略是一次扫描，它会记住它在调用之间的位置。使用API游标意味着我们可以返回一组行，而不是像T-SQL游标那样一次返回一个：

    执行计划

    STATISTICS IO输出是：

    Table 'T'. Scan count 1, logical reads 1011, physical reads 0, read-ahead reads 0
Table 'T'. Scan count 1, logical reads 1001, physical reads 0, read-ahead reads 0
Table 'T'. Scan count 1, logical reads 516, physical reads 0, read-ahead reads 0
    • 21

相关问题