我在 MS SQL Server 中有一张表。
- 表大小:806 GB
- 行数:12 亿
- 索引空间:1.2 GB
表用法:来自 Web 服务调用的日志记录 99.9% 是来自日志记录的用法,开发人员很少在 Prod 中查看此表(仅在报告或研究问题时)。
主键:基于“INT”数据类型的“ID”。有一个基于该“ID”列的聚集索引。
我对此更改的意图:想要管理此表(因为它有 10 年的数据)并继续前进(由于新要求),开发人员/分析师有可能进一步深入研究此表(仅几个月)而且我不想为相同的目的创建一个新表。
我的问题:
[主要问题]我可以根据“DateCreated”(DATETIME,NOT NULL 列)对该表进行分区,而不会导致问题(逻辑/性能方面)。
[很高兴知道]需要多少时间(我知道这取决于数据库空间/服务器内存和其他详细信息,但大致 # 会很好)对这个巨大的表进行分区(如果可以根据日期进行分区) . 问这个问题,因为这是一个生产表,并且经常插入行(现在 ~ 350 条记录/分钟)。
【不完全是个问题,求推荐】有没有更好的方案来管理这张表(不想在Production中保留超过3年的数据,方案在下面提到)?
当前计划(我是 MS SQL 的新手,所以这是我想出的):
- 每个分区保留 3 个月的数据。
- 系统在每个季度之前自动创建分区。
- 在活动表中只保留 3 年的分区。
- 将其他分区移动到 OLD/ARCHIEVE 表(需要创建这个)。真正要清除的旧数据。
简短的回答:不,你不能这样做。根据文档...
这意味着为了在 上进行分区
[DateCreated]
,您还必须在 上进行集群[DateCreated]
。当您考虑一下分区和集群实际上是什么时,这会更有意义。
聚集索引是数据的逻辑顺序
分区是一种基于函数管理数据物理存储的技术
只要不冲突,这两个东西可以一起使用。如果你试图让它们发生冲突,你会过得很糟糕。要逐点回答您的项目:
[DateCreated]
根本无法分区。这是可能的,但它可能是一个比您提出问题时计划的更大的项目。如果您决定一次性执行此操作,则可能会受到磁盘速度的限制。
类似的操作将进行备份。备份从这里读取一大堆字节并在那里写入一大堆字节。就像你的表重建一样。因此,如果您的数据库是 1.6TB 并且备份需要 2 小时,那么重建 800GB 表可能需要大约 1 小时(大小的一半,时间的一半)。
当然,这两个任务之间存在很多差异。备份驱动器可能与数据驱动器不同,并发工作负载会影响它们,到 SAN 的网络路径,二级索引,yadda yadda。所以时间会有所不同,但它们不必相差几个数量级。
与大爆炸相比,您最好按时间倒序一次处理一个用例。
删除您想要删除的最旧数据。因为有 10 年,而您只想要 3 年,所以问题立即变得容易 70%!这最好通过一次删除块来完成,而不是在单个查询中全部删除。如果不能完全删除它,如何将其移动到 OLAP 服务器或转储到压缩文件格式(镶木地板、ORC 及其同类)?
使用您想要的分区方案和为所有 3 年历史和未来定义的分区创建一个新的空表。首先以块的形式移动最旧的数据,插入新表并从当前表中删除。我猜 ID 列是一个 IDENTITY,所以它应该与日期分区很好地相关。虽然如果有人想研究这些旧数据,他们可以从这个新的分区表中进行研究。
最终,您将进入当前未分区表中的唯一数据是当前 3 个月范围内的数据,或者将数据拆分到两个表中会给用户带来实际痛苦的阶段。这是停电的时候。将最后的数据移动到新的分区表,删除当前表并重命名分区表。
DateCreated
在将其设为聚集索引后,您可以对列进行分区。即使您设法使其成为聚集索引键,也会存在性能问题 - 您的索引将比以前(当 ID INT 聚集时)消耗更多空间(存储/内存)。因为 INT 是 4 个字节并且是唯一值,但是您的
DateCreated
列是 8 个字节并且很可能不是唯一的,所以会在顶部添加额外的 4 字节唯一性,使聚集索引在一定程度上变得更重。由于聚集索引键被添加到所有非聚集索引,它们也会变得更重这完全取决于您的服务器的马力
不要使用分区。使用具有相同结构的单独历史表。创建一个每日作业,该作业将根据列移动(插入历史表/删除父表中)所有超过 3 年的
DateCreated
行。不是每天一次移动所有行(超过 3 年),而是以 N 行的块的形式移动,块之间的间隔为 X 秒(或毫秒)。N 是每 1 个块删除的行数。确保 N 不会导致父表上的独占表锁升级(通过实验找出数字)
不容忽视的一点是,有了 12 亿行,您比您想象的更接近 int 数据类型的上限。因此,我认为您的更改也应该计划解决此限制,可能通过更改为 Bigint 来代替。
正如之前指出的,70% 的数据太旧了,您不再需要保留它,而是批量删除 70%,我会考虑在要保留的 30% 上插入/选择,然后截断原始表。然后,您可以将行复制回来或重命名表。
最后一点是,您的 Id 列可能是按升序添加的,这样行 < id x 较早,> id x 较晚,因此您可以构建一个映射表,将 ids 映射到日期然后直接在 id 上分区?