主页 / dba / 问题 / 239487
Accepted
Hannah Vernon
Asked: 2019-05-31 11:25:40 +0800 CST

这个索引扫描中这个 Uniq1002 列的目的是什么？

  • 772

进行以下复制：

USE tempdb;

IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t
GO
CREATE TABLE dbo.t
(
    id int NOT NULL 
        PRIMARY KEY 
        NONCLUSTERED 
        IDENTITY(1,1)
    , col1 datetime NOT NULL
    , col2 varchar(800) NOT NULL
    , col3 tinyint NULL
    , col4 sysname NULL
);

INSERT INTO dbo.t (
      col1
    , col2
    , col3
    , col4
    ) 
SELECT TOP(100000) 
      CONVERT(datetime, 
         DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2000-01-01 00:00:00'))
    , replicate('A', 800)
    , sc2.bitpos
    , CONVERT(sysname, CHAR(65 + CRYPT_GEN_RANDOM(1) % 26) 
        + CHAR(65 + CRYPT_GEN_RANDOM(1) % 26) 
        + CHAR(65 + CRYPT_GEN_RANDOM(1) % 26))
FROM sys.syscolumns sc
    CROSS JOIN sys.syscolumns sc2;

在这里，我将聚集索引添加到一组不唯一的列上，并且是典型的单列非聚集索引：

CREATE CLUSTERED INDEX t_cx 
ON dbo.t (col1, col2, col3);

CREATE INDEX t_c1 ON dbo.t(col4);

此查询强制 SQL Server 查找聚集索引。请原谅使用索引提示，这是获得重现的最快方法：

SELECT id
    , col1
    , col2
    , col3
FROM dbo.t aad WITH (INDEX = t_c1)
WHERE col4 = N'JSB'
    AND col1 > N'2019-05-30 00:00:00';

实际查询计划在非聚集索引扫描的输出列表中显示一个不存在的列：

在此处输入图像描述

从表面上看，这表示在非唯一聚集索引中使用的唯一标识符。是这样吗？像这样命名的列是否总是聚集索引 uniqifier？

sql-server sql-server-2012

2 个回答

  1. Best Answer

    从表面上看，这表示在非唯一聚集索引中使用的唯一标识符。是这样吗？

    是的。

    这个索引扫描中这个 Uniq1002 列的目的是什么？

    非聚集索引中的每一行都必须与基表中的一行恰好相关联，这样书签查找（RID 或键）才能正常工作。此映射由“行定位器”提供。

    对于堆表，行定位器是 RID。对于集群行存储表，它是集群键（包括必要的唯一性）。

    要使计划中的Key Lookup起作用，它必须有权访问行定位器。这包括uniquifier，因此它必须由非聚集索引扫描发出。

    uniquifier存储在行的可变长度部分，因此它只在需要时（即实际存在重复键时）占用空间。

    像这样命名的列是否总是聚集索引唯一标识符？

    是的。uniquifier 列始终命名为UniqXXXX。与堆表关联的行定位器名为BmkXXXX。列存储表的行定位器名为ColStoreLocXXXX。

    观察唯一性

    可以在包含功能query_trace_column_values扩展事件的 SQL Server 版本上直接观察 uniquifier 的值。

    此未记录且不受支持的事件位于调试通道中。它是在 SQL Server 2016 中引入的，并且在 SQL Server 2017 的 CU11 附近停止工作。

    例如：

    CREATE TABLE #T (c1 integer NULL INDEX ic1 CLUSTERED, c2 integer NULL INDEX ic2 UNIQUE, c3 integer NULL);
GO
INSERT #T
    (c1, c2, c3)
VALUES 
    (100, 101, 0),
    (100, 102, 1),
    (100, 103, 2);
GO
DBCC TRACEON (2486);
SET STATISTICS XML ON;
SELECT T.* FROM #T AS T WITH (INDEX(ic2));
SET STATISTICS XML OFF;
DBCC TRACEOFF (2486);
GO
DROP TABLE #T;

    有计划：

    计划

    它在 SQL Server 2016 上产生如下事件输出：

    事件输出

    • 9

  2. 为了让 SQL Server 创建非唯一聚集索引，在聚集索引的物理结构中添加了一个隐藏的“列”。该隐藏列称为 uniqifier，顾名思义，它提供了一种机制来确保聚集索引中的每一行都是唯一的。

    当您看到该列出现在查询计划中时，这是一个很好的指标，表明集群键列尚未定义为唯一的。这可能是因为已知列的组合不是唯一的。表的设计者也可能只是忘记将UNIQUE限定符添加到CREATE CLUSTERED INDEX语句中。

    事实上，如果我们使用唯一聚集索引重新创建上面的 repro，则该Uniq1002列不再出现在查询计划中：

    USE tempdb;

IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t
GO
CREATE TABLE dbo.t
(
    id int NOT NULL 
        PRIMARY KEY 
        NONCLUSTERED 
        IDENTITY(1,1)
    , col1 datetime NOT NULL
    , col2 varchar(800) NOT NULL
    , col3 int NULL
    , col4 sysname NULL
);

INSERT INTO dbo.t (
      col1
    , col2
    , col3
    , col4
    ) 
SELECT TOP(100000) 
      CONVERT(datetime, DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2000-01-01 00:00:00'))
    , replicate('A', 800)
    , CONVERT(int, CRYPT_GEN_RANDOM(4))
    , CONVERT(sysname, CHAR(65 + CRYPT_GEN_RANDOM(1) % 26) 
        + CHAR(65 + CRYPT_GEN_RANDOM(1) % 26) 
        + CHAR(65 + CRYPT_GEN_RANDOM(1) % 26))
FROM sys.syscolumns sc
    CROSS JOIN sys.syscolumns sc2;

    这是唯一的聚集索引：

    CREATE UNIQUE CLUSTERED INDEX t_cx 
ON dbo.t (col1, col2, col3);

CREATE INDEX t_c1 ON dbo.t(col4);

    和查询：

    SELECT id
    , col1
    , col2
    , col3
FROM dbo.t aad WITH (INDEX = t_c1)
WHERE col4 = N'JSB'
    AND col1 > N'2019-05-30 00:00:00';

    该计划现在为非聚集索引扫描输出列显示了这一点：

    在此处输入图像描述

    创建非唯一聚集索引时，会自动添加唯一标识符。uniqifier 也被添加到每个非聚集索引中，即使您无法通过查看索引的属性或通过“编写”索引来“看到”它。

    uniqifier 是一个四字节的列，其中包含一个整数，对于插入到表中的每一行，该整数在后台自动递增。插入的第一行不需要 uniqifier；只有在第一行之后添加的行才具有 uniqifier。

    • 2

相关问题