SQL Server高效地删除数百万行数据的方法

Question

SQL Server高效地删除数百万行数据的方法

sqlsql-server

10

我最近提出了这个问题：MS SQL如何在表之间共享标识种子（许多人想知道为什么）

我有一个如下所示的表布局：

Table: Stars
starId bigint
categoryId bigint
starname varchar(200)

但我的问题是，我有数百万行。因此，当我想从Stars表中删除星星时，它对SQL Server来说太过于强烈。

我不能使用2005+内置的分区，因为我没有企业许可证。

但是，当我删除时，我总是一次性删除整个类别ID。

我想做这样的设计：

Table: Star_1
starId bigint
CategoryId bigint constraint rock=1
starname varchar(200)

Table: Star_2
starId bigint
CategoryId bigint constraint rock=2
starname varchar(200)

通过这种方式，我可以通过简单的删除表来删除整个类别，从而以O（1）的速度处理数百万行。

我的问题是：在SQL Server中拥有数十万个表是否会产生问题？ O（1）的删除对我来说非常可取。也许有完全不同的解决方案我没有想到吗？

编辑：

插入后星星是否会被修改？否。

您是否必须跨星级别查询数据？我从未跨星级别查询过。

如果您正在查找特定星星的数据，您是否知道要查询哪个表？是的。

输入数据时，应用程序将如何决定将数据放入哪个表中？在创建categoryId时一次性插入星形数据。

会有多少个类别？您可以假定会有无限星级类别。每天最多可达100个星级类别，每天最多可达30个不需要的星级类别。

您真的需要删除整个类别还是仅更改了数据的星级？是整个星级类别。

您尝试过分批删除吗？是的，我们今天就这样做，但效果不够好。

另一种技术是标记要删除的记录？没有必要标记一个星级为已删除，因为我们知道整个星级类别都可以删除。

其中有多少比例从未使用过？通常情况下，我们保留每个星级类别的数据几周，但有时需要保留更久。

当您认为某个类别有用时，它永远有用吗，还是可能以后需要删除？

不是永久的，而是直到发出手动请求删除该类别为止。如果是这样，有多少时间需要删除？并不经常。

您正在使用何种磁盘排列？目前是单个文件组存储且没有分区。

您能使用 SQL 企业版吗？不行。许多人都在运行此软件，他们只有 SQL 标准版。购买 MS SQL 企业版超出了他们的预算。

- Net Citizen

7

让我猜猜，你有“亿万”条记录。 - D'Arcy Rittich

这些表格与我的不完全相同，但我认为它是一个很好的例子，可以清楚地表达我的观点 :) - Net Citizen

1

https://dev59.com/dXNA5IYBdhLWcg3wcdhk - womp

1

我看到了一些回答说"SQL Server删除数百万行应该足够快"。也许有使用SQL Server的人（可惜现在不是我，抱歉）应该真正设计一个原型，并分享性能结果以证明这一点。 - Oddthinking

1

@Oddthinking：完成。结果基本符合预期。 - Aaronaught

显示剩余6条评论

13个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user132371 · Answer 1

针对您提出的多表想法，如何实现呢？

考虑使用动态查询。

创建具有标识category_id列的类别表。
在此表上创建插入触发器 - 在其中创建星级表，其名称是根据category_id动态生成的。
创建删除触发器 - 在其中使用动态创建的SQL语句删除相应的星级表。
要选择特定类别的星级，可以使用返回表的函数。它将以category_id作为参数，并通过动态查询返回结果。
要插入新类别的星级，首先在类别表中插入新行，然后将星级插入到适当的表中。

我还会研究另一种方向，即使用XML类型列存储星级数据。这里的主要思想是，如果您只需要按类别操作星级，那么为什么不将具体类别的所有星级存储在表格的一个单元格中，以XML格式存储。不幸的是，我无法想象这种决策的性能如何。

这两种变体都只是头脑风暴中的想法。

- Marcus Adams · Answer 2

正如Cade所指出的那样，为每个类别添加一个表是手动分区数据，没有统一访问的好处。

对于以与删除表格一样快的速度删除数百万行数据而言，如果不使用分区，则永远不会发生。

因此，似乎为每个类别使用单独的表可能是一个有效的解决方案。然而，由于您已经说明了其中一些类别是保留的，而另一些则被删除，因此这里有一个解决方案：

为每个新类别创建一个新的星表。
等待时间到期，在此期间您可以决定是否保留该类别的星级。
如果您打算保留它们，请将记录滚动到主星表中。
删除表格。

这样，您将拥有有限数量的表格，具体取决于您添加类别的速率和您决定是否需要它们的时间段。

最终，对于您保留的类别，您将增加工作量，但额外的工作量会随着时间的推移而分布。用户可能会比从中间删除更少地体验到聚集索引末尾的插入。然而，对于那些您不保留的类别，您将节省大量时间。

即使你没有技术上保存工作，感知通常是更大的问题。

- HLGEM · Answer 3

你对星星数据要做什么？如果你每次只查看一个类别的数据，这可能有效，但很难维护。每当你有一个新类别时，你就必须建立一个新表。如果你想跨类别查询，它会变得更加复杂，也可能更耗费时间和金钱。如果你确实需要跨类别查询，那么视图可能是最好的选择（但不要在视图之上再添加视图）。如果你正在寻找特定星星的数据，你会知道查询哪个表吗？如果不知道，那么你将如何确定查询哪个表或者是否要查询所有表？在输入数据时，应用程序将如何决定将数据放入哪个表中？将会有多少类别？顺便提一下，关于每个类别都有一个单独的ID，使用bigint identities并将identity与类别类型组合成唯一标识符。

你真的需要删除整个类别还是只需要删除数据发生变化的星星？而且你真的需要删除吗？也许你只需要更新信息。

你尝试过分批删除（每次循环删除1000条记录左右）。这通常比一次性删除一百万条记录要快得多。它通常也可以避免在删除期间锁定表。

另一种技术是标记要删除的记录。然后，当使用率较低时，您可以运行批处理过程来删除这些记录，并且您的查询可以在排除了标记为删除的记录的视图上运行。

根据您的答案，我认为您的建议可能是合理的。