主页 / dba / 问题 / 61894
Accepted
Paul
Asked: 2014-03-28 07:06:11 +0800 CST

用于 INSERT 时的 GETDATE() 唯一性

  • 772

最近在看这篇博文： 链接

其中包含本节：

选择聚集索引

有几种选择聚集索引的策略；最简单和最好的方法之一是添加另一列数据类型 datetime 并将该列用于聚集索引。这是您需要做的：

  1. 将列添加为数据类型 datetime

  2. 我通常称它为日期

  3. 将默认值设置为 GetDate()。

  4. 使其非空。

  5. 在向您插入数据之前，在其上创建聚集索引。

我的问题是这会为日期创建两个相同的值吗？如果使用并行性，这个答案会改变吗？（假设值从未指定，总是来自 GetDate()）

我相信由于添加了幕后唯一性，我认为这无关紧要是正确的，对吧？但无论如何我都很感兴趣。

我是从 SQL2008R2 的角度询问的，但是如果从 7.0 开始的任何版本的 SQL Server 的答案都不同，我会感兴趣。

sql-server index

1 个回答

  1. Best Answer

    GETDATE()不保证是唯一的，不。特别是如果它是一个日期时间，毫秒是向上或向下舍入的，当然当并非所有数据都来自同一个用户时，几乎肯定会发生冲突。

    当然，聚集索引不需要是唯一的，因为如果不是，SQL Server 会这样做（但仅在需要时）。如果您需要自己识别特定行（而不仅仅是 SQL Server 内部使用的唯一性），并且没有其他候选键列（这可能与事件日志表之类的东西），您可以添加一个非集群主键，即 IDENTITY 列。或者，如果您真的想要 Web 规模 - 并且更关心插入性能而不是存储或任何后续数据使用 - 您可以使用填充 NEWID() 的 uniqueidentifier 列。

    例子

    让我们看一个例子，看看有什么不同。

    USE tempdb;
GO

-- rely on uniqifier

CREATE TABLE dbo.Test1
(
  g DATETIME
);
CREATE CLUSTERED INDEX x ON dbo.Test1(g);


-- use an IDENTITY column

CREATE TABLE dbo.Test2
(
  i INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
  g DATETIME
);
CREATE CLUSTERED INDEX x ON dbo.Test2(g);


-- use a GUID

CREATE TABLE dbo.Test3
(
  n UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()
    PRIMARY KEY NONCLUSTERED,
  g DATETIME
);
CREATE CLUSTERED INDEX x ON dbo.Test3(g);
GO

    插入速度

    我使用以下脚本填充了大约 500,000 行的所有三个表：

    SET NOCOUNT ON;
GO
SELECT SYSDATETIME();
GO
INSERT dbo.Test1(g) 
  SELECT DATEADD(SECOND, ABS([object_id])/1000, GETDATE()) 
  FROM sys.all_columns;
GO 100

SELECT SYSDATETIME();
GO
INSERT dbo.Test2(g) 
  SELECT DATEADD(SECOND, ABS([object_id])/1000, 
  GETDATE()) FROM sys.all_columns;
GO 100

SELECT SYSDATETIME();
GO
INSERT dbo.Test3(g) 
  SELECT DATEADD(SECOND, ABS([object_id])/1000, 
  GETDATE()) FROM sys.all_columns;
GO 100

SELECT SYSDATETIME();

    结果：

    Uniquifier:    2.26 seconds
IDENTITY:      3.89 seconds
GUID:          5.06 seconds

    扫描速度

    SELECT SYSDATETIME();
DBCC DROPCLEANBUFFERS;
SELECT * FROM dbo.Test1;
SELECT SYSDATETIME();
DBCC DROPCLEANBUFFERS;
SELECT * FROM dbo.Test2;
SELECT SYSDATETIME();
DBCC DROPCLEANBUFFERS;
SELECT * FROM dbo.Test3;
SELECT SYSDATETIME();

    结果：

    Uniquifier:  3.89 seconds
IDENTITY:    4.21 seconds
GUID:        4.85 seconds

    使用空间

    查看来自以下的简单结果sp_spaceused：

    EXEC sp_spaceused N'dbo.Test1';
EXEC sp_spaceused N'dbo.Test2';
EXEC sp_spaceused N'dbo.Test3';

    结果：

                          Rows      Reserved    Data      Index size
Test1 - Uniquifier    513300    14024 KB    13880 KB       96 KB
Test2 - IDENTITY      513300    29072 KB    15792 KB    13200 KB
Test3 - GUID          513300    49936 KB    21960 KB    27928 KB

    uniquifier 占用的空间比 IDENTITY 列少（显然两者都比 GUID 少），因为它仅用于碰撞（可能还有我不知道的其他优化，例如压缩）。

    我们还可以查看日期时间列（索引 id = 1）和非聚集主键（索引 id = 2）上的聚集索引的索引页：

    DBCC TRACEON(3604,-1);

-- Uniquifier
DBCC IND('tempdb', 'dbo.Test1', 1); -- 1,747 index pages
-- no second index for this table

-- IDENTITY
DBCC IND('tempdb', 'dbo.Test2', 1); -- 1,987 index pages
DBCC IND('tempdb', 'dbo.Test2', 2); -- 1,637 index pages

-- GUID
DBCC IND('tempdb', 'dbo.Test3', 1); -- 2,764 index pages
DBCC IND('tempdb', 'dbo.Test3', 2); -- 3,472 index pages

    页/行内容

    最后，我们可以查看特定页面以查看一行中存储的内容。我只是从上面的每个聚集索引 DBCC IND 结果中取出第一行（您的页面 id 值几乎肯定会不同）：

    DBCC PAGE('tempdb',1, 153, 1);
DBCC PAGE('tempdb',1, 199, 1);
DBCC PAGE('tempdb',1, 217, 1);

    Uniquifier - 特别注意长度/记录大小：

    Slot 0, Offset 0x60, Length 15, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Record Size = 15
--------------^^

Memory Dump @0x000000000F7EA060

0000000000000000:   10000c00 0333ba00 fba20000 020000††††.....3º.û¢.....  

Slot 1, Offset 0x6f, Length 23, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 23              
--------------^^       
Memory Dump @0x000000000F7EA06F

0000000000000000:   30000c00 0333ba00 fba20000 02000001 †0....3º.û¢...... 
0000000000000010:   00170001 000000††††††††††††††††††††††.......

    IDENTITY 似乎在聚集索引中有 4 个额外的字节：

    Slot 0, Offset 0x60, Length 19, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Record Size = 19
--------------^^

Memory Dump @0x0000000011DAA060

0000000000000000:   10001000 a735ba00 fba20000 03020000 †....§5º.û¢...... 
0000000000000010:   030000†††††††††††††††††††††††††††††††...              

Slot 1, Offset 0x73, Length 27, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 27              
--------------^^       
Memory Dump @0x0000000011DAA073

0000000000000000:   30001000 a735ba00 fba20000 04020000 †0...§5º.û¢...... 
0000000000000010:   03000001 001b0001 000000†††††††††††††...........

    GUID 在聚集索引中有额外的 16 个字节：

    Slot 0, Offset 0x60, Length 31, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Record Size = 31
--------------^^

Memory Dump @0x0000000011DAA060

0000000000000000:   10001c00 393aba00 fba20000 f3233e73 †....9:º.û¢..ó#>s 
0000000000000010:   a36e114b b1229a80 a5cb090a 030000††††£n.K±".¥Ë  ....  

Slot 1, Offset 0x7f, Length 39, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 39
--------------^^                     
Memory Dump @0x0000000011DAA07F

0000000000000000:   30001c00 393aba00 fba20000 c7bb2544 †0...9:º.û¢..Ç»%D 
0000000000000010:   4ad3574f a2c2029f e4abc9d7 03000001 †JÓWO¢Â.ä«É×.... 
0000000000000020:   00270001 000000††††††††††††††††††††††.'.....

    结论

    在我看来，从各方面来说，你最好让 uniquifier 做自己的事情（假设你不希望能够区分具有完全相同日期/时间值的两行）。唯一一次您会遇到麻烦，如果任何单个值重复 2,147,483,648 次，此时您将溢出整数的唯一性范围。

    • 5

相关问题