群集列存储索引中加密数据的性能影响

Question

我使用的是SQL Server 2016，希望了解以下几点关于性能观点的详细信息：

1. 在已经创建聚集列存储索引的同一表上创建主键唯一约束，是否有任何性能增益或额外好处？
2. 如果我的表包含加密的列( 10列中的9列被加密)，那么创建集群列存储索引有什么好处吗？我正在使用SYMMETRIC KEY加密。

Answer 1

在可能的情况下，最好避免在一个问题中问多个无关的问题。你的第一个问题很宽泛。一般来说，创建索引总是一种权衡，有些查询将从该索引中获益。列存储表上的NCIs也是如此。我将把重点放在第二个问题上。

据我所知，SYMMETRIC KEY加密对于专栏商店来说可能是一个非常糟糕的匹配。将加密数据插入CCI意味着要将较长、相对独特的字符串插入到var二进制列中。CCIs在这些数据类型和数据分布方面做得不好。您可能会遇到字典压力，这将限制您的行组的大小，除了列删除之外，您还会忽略列存储的大部分功能。

我发现如何使用带有对称密钥的服务器加密的代码对于开发一个简单的示例很有帮助。我不知道您的数据是什么样子，所以我只需将唯一的整数插入到表中，然后用加密将相同的唯一整数插入到不同的表中。钥匙的定义：

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'you will never guess this';

CREATE CERTIFICATE MyFirstCertificate
WITH SUBJECT = 'no u';

CREATE SYMMETRIC KEY key_to_answering_217242 WITH
IDENTITY_VALUE = 'yeah nah',
ALGORITHM = AES_256,
KEY_SOURCE = 'closet full of jandals'
ENCRYPTION BY CERTIFICATE MyFirstCertificate;

创建两个表并将数据插入到两个表中的them：

DROP TABLE IF EXISTS dbo.CCI_NOT_ENCRYPTED;

CREATE TABLE dbo.CCI_NOT_ENCRYPTED (
ID BIGINT,
INDEX CCI CLUSTERED COLUMNSTORE
);

INSERT INTO dbo.CCI_NOT_ENCRYPTED WITH (TABLOCK)
SELECT TOP (1048576) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

GO

USE master;
GO

OPEN SYMMETRIC KEY key_to_answering_217242
DECRYPTION BY CERTIFICATE MyFirstCertificate


DROP TABLE IF EXISTS [TEST].dbo.CCI_ENCRYPTED;

CREATE TABLE [TEST].dbo.CCI_ENCRYPTED (
ID VARBINARY(256),
INDEX CCI CLUSTERED COLUMNSTORE
);

INSERT INTO [TEST].dbo.CCI_ENCRYPTED WITH (TABLOCK)
SELECT EncryptByKey(Key_GUID('key_to_answering_217242'), CAST(RN AS VARCHAR(7)))
FROM (
    SELECT TOP (1048576) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);

CLOSE SYMMETRIC KEY key_to_answering_217242;

GO

总桌子的大小有明显的差别。具有加密数据的表要大20倍以上：

查看sys.dm_db_column_store_row_group_physical_stats，我们可以看到带有加密数据的表的行组大小受到字典压力的限制：

当然，查询性能也会受到很大的影响。以下查询需要大约0毫秒的CPU时间：

SELECT COUNT(*)
FROM dbo.CCI_NOT_ENCRYPTED
WHERE ID = 1;

如果我在加密的表上运行相同的查询：

USE master;
GO

OPEN SYMMETRIC KEY key_to_answering_217242
DECRYPTION BY CERTIFICATE MyFirstCertificate

SELECT COUNT(*)
FROM [TEST].dbo.CCI_ENCRYPTED
WHERE CAST(CAST(DecryptByKey(ID) AS VARCHAR(7)) AS BIGINT) = 1;

CLOSE SYMMETRIC KEY key_to_answering_217242;

GO

它需要超过600毫秒的CPU时间。尽管如此，在处理加密数据时，总体上似乎存在很大的开销。我不能说列商店是否会为您的确切数据和场景提供任何好处，只是您肯定不会看到列存储的通常好处。

Answer 2

对于列存储索引来说，这听起来很奇怪。总之：

1:对于一个列存储索引，您可以得到数据的压缩，以及可能的段消除。但在一个专栏商店的索引中，根本就没有这样的东西。因此，如果在这些列上具有高选择性的查询和性能非常重要，那么拥有行索引(包括PK和UQ)可能是有益的。

他说:我有种感觉，你的意思是“加密”而不是“欺骗”？您没有说明您使用的是哪种技术，但是由于数据不是明文的，所以SQL Server几乎无法帮助您进行查询，所以我怀疑列存储在这里是否有益。但我们没有什么消息可查.