主页 / dba / 问题 / 280157
Accepted
Avi
Asked: 2020-11-24 03:21:28 +0800 CST

小表上的聚集列存储索引

  • 772

聚集列存储索引表通常对大型表很有用。理想情况下有数百万行。对查询也很有用，它只选择此类表中可用列的子集。

如果我们打破这两个“规则”/最佳实践会发生什么？

  1. 就像拥有一个聚集列存储索引表，它最多只能存储几千或几十万行。
  2. 并针对需要所有列的那些聚集列存储表运行查询。

与行存储聚集索引表相比，我的测试没有显示任何性能下降。这对我们来说很棒。

是否存在违反这两条规则的“长期”影响？还是任何尚未出现的隐藏陷阱？

上下文为什么需要它：我设计了一个数据库模型，它将用于不同供应商数据库的许多实例。每个数据库中的模式都保持不变，但不同的供应商有不同的数据量。因此，很少有小型供应商最终可能会在其表中包含少量数据（<1 000 000）。我不能让自己为行存储和列存储模型保留两个不同的数据库。

columnstore sql-server-2017

2 个回答

  1. 列存储索引在压缩数据大小方面有很大的优势。Columnstore 索引的总体目标是由于其压缩而快速读取一堆数据。

    在此处输入图像描述

    CCI是Columnstore聚集索引，Clusted是聚集索引

    列存储索引将数据大小从 4MB 压缩到 2MB。

    我们可以分两表三部分来看表现。

    第一个是最小SELECT操作：

    SELECT * FROM Users_CCI WITH(INDEX=CCI_Users) WHERE Id=12333

    在此处输入图像描述

    在此处输入图像描述

    SELECT * FROM Users_Clustered WHERE Id=12333

    在此处输入图像描述

    在此处输入图像描述

    结果：这里有一个Columnstore Scan错误的估计。此外，逻辑读取差异。你可以说这对你没有关系，但是，如果你使用最小的 SELECT 查询，可能会使用数千次。它会影响整体性能。

    第二个是最小UPDATE操作：

    UPDATE Users_CCI SET Age=10 WHERE  Id=2

    在此处输入图像描述

    UPDATE Users_Clustered SET Age=10 WHERE  Id=2

    在此处输入图像描述

    结果：如我们所见，存在读取、CPU 和时间差异。

    第三个是REBUILD操作：

    USE [StackOverflow2013]
GO
ALTER INDEX [CCI_Users] ON [dbo].[Users_CCI] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE)
GO
USE [StackOverflow2013]
GO
ALTER INDEX [PK_Users_Clustered_Id] ON [dbo].[Users_Clustered] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

UPDATE Users_CCI SET Age=10 
UPDATE Users_Clustered SET Age=10

    如果我为索引碎片重建和更新所有行，我将看到比聚集索引更碎片化的聚集列存储索引。而且我没有显示，但是Clustered Columnstore Index重建过程产生的事务日志超过Clustered Index.

    在此处输入图像描述

    在此处输入图像描述

    就像文件说的

    • 表上超过 10% 的操作是更新和删除。大量更新和删除会导致碎片。碎片会影响压缩率和查询性能，直到您运行名为 reorganize 的操作，该操作强制所有数据进入列存储并删除碎片。有关详细信息，请参阅最小化列存储索引中的索引碎片。

    如果你有一个小表，你不需要列存储索引。

    • 4
  2. Best Answer

    @YunusUYANIK 指出设计模式仅满足一方面的潜在缺陷，为什么不在表上创建适合这两种情况的行存储和列存储索引呢？当然，您最终可能会以两种方式索引相同的字段，但主要缺点只是增加了存储空间的使用，这在规划性能时通常不太关心。

    这将取决于您的架构和每个供应商的表中的数据量，因此您必须进行测试以确保您的索引设计用于基于供应商谓词的不同数据量的适当查询. 在最坏的情况下，您有时也可能不得不使用索引提示，但我觉得如果您正确设计这两种类型的索引，那不太可能。

    • 3

相关问题