主页 / dba / 问题 / 54636
Accepted
user31402
Asked: 2013-12-08 11:28:57 +0800 CST

具有很少不同条目的字符串列是否会自动压缩？

  • 772

我们有一个表，其中包含一列，其中只有十几个不同的字符串。为了让数据库压缩这些信息，我需要做些什么吗？我们没有太多可用的磁盘空间，一遍又一遍地存储所有这些长字符串似乎是一种浪费。

我希望数据库以压缩形式在内部存储此信息。例如，当前数据如下所示：

Column "TypeInfo"
---------------------------------------------------------
very_long_descriptor_someone_came_up_with_a_long_time_ago
this_desciptor_is_also_very_long
nobody_knows_why_this_descriptor_is_so_long
very_long_descriptor_someone_came_up_with_a_long_time_ago
very_long_descriptor_someone_came_up_with_a_long_time_ago
nobody_knows_why_this_descriptor_is_so_long

我希望看到 SQL Server 存储某种小键，而不是代表那些重复的较长字符串：

Column "TypeInfo"
-----------------
1
2
3
1
1
3
(+ mapping information)
sql-server compression

1 个回答

  1. Best Answer

    不，SQL Server 不会自动执行此类操作。您可以通过数据压缩实现您所追求的某些目标，但其最高粒度是在页面级别。因此，如果您仅在该列上有索引（或至少将此列作为前导键列），那么您将在索引页中获得一些良好的压缩，但假设聚集索引不在此列上，您将获得当数据和其他索引页包含字符串的许多变体时，它的好处就少得多。

    数据压缩使用多种算法，包括字典，如果页面上的所有值都相同，这将产生最佳效果。不过，即使在最好的情况下，这在大型表上也不会非常有价值 - 假设您有一百万页，并且每页都有其中一个值的两个副本。当然，压缩通过在每个页面上只存储一次而不是两次存储值（加上一些可以忽略不计的指针开销）来为您节省一些费用，但是 SQL Server 仍然存储一百万个副本- 每页一个！

    我的建议是，不要一遍又一遍地存储相同的十几个字符串，而是创建一个带有TINYINT键的查找表，让您只存储每个描述字符串一次，无论它被使用了多少次。您始终可以在查询时检索描述，而无需将其与数据一起存储，您甚至可以创建视图以使其对查询、应用程序和用户几乎透明。如果您的工作负载受 CPU 限制，这也是一个更有吸引力的选择；虽然数据压缩可以节省存储和内存，但压缩和解压缩每个页面都会涉及一些 CPU 开销。

    例如：

    CREATE TABLE dbo.TypeInfo
(
  TypeInfoID TINYINT PRIMARY KEY,
  Description VARCHAR(64) NOT NULL UNIQUE
);

INSERT dbo.TypeInfo(TypeInfoID, Description) VALUES
  (1,'very_long_descriptor_someone_came_up_with_a_long_time_ago'),
  (2,'this_desciptor_is_also_very_long'),
  (3,'nobody_knows_why_this_descriptor_is_so_long');
-- ... 9 other values ...

    现在您需要将TypeInfoID列添加到原始表中：

    ALTER TABLE dbo.OtherTable 
  ADD TypeInfoID TINYINT;

    然后您可以按如下方式更新现有数据：

    UPDATE o
  SET TypeInfoID = t.TypeInfoID
  FROM dbo.OtherTable AS o
  INNER JOIN dbo.TypeInfo AS t
  ON o.TypeInfo = t.Description;

    （如果表很大，并且您希望将对日志和阻塞操作的影响降到最低，则分批处理。）

    然后你可以添加一个外键约束：

    ALTER TABLE dbo.OtherTable 
  ADD CONSTRAINT fk_TypeInfo 
  FOREIGN KEY (TypeInfoID) REFERENCES dbo.TypeInfo(TypeInfoID);

    验证所有数据正确后，您可以删除该列（首先删除引用它的所有约束和索引）：

    ALTER TABLE dbo.OtherTable 
  DROP COLUMN TypeInfo;

    然后您可以创建一个视图，您的查询可以使用该视图来保持语义相同：

    CREATE VIEW dbo.vOtherTable
AS
  SELECT /* o.columns */, TypeInfo = t.Description
    FROM dbo.OtherTable AS o
    INNER JOIN dbo.TypeInfo AS t
    ON o.TypeInfoID = t.TypeInfoID;

    （或者在某些情况下，您甚至可以重命名表，并为视图提供表曾经拥有的名称，使其完全透明。）

    最后，如果您不想NULL在此列中使用 s，并且您使用的是 SQL Server 2012 或更高版本，则可以将该列更改为，NOT NULL而不会产生更新和日志记录：

    ALTER TABLE dbo.OtherTable 
  ALTER COLUMN TypeInfo TINYINT NOT NULL;

    如果您使用的是早期版本，您可以考虑添加一个CHECK CONSTRAINT：

    ALTER TABLE dbo.OtherTable 
  WITH NOCHECK ADD CONSTRAINT ck_TypeInfoNotNULL
  CHECK (TypeInfoID IS NOT NULL);

    但这可能并不理想，因为不受信任的约束会影响优化器使用它的能力……在很多情况下，最好先考虑一次性性能损失。

    我认为这在很大程度上超出了您最初问题的范围，您甚至可能认为这个解决方案中的任何一个都没有吸引力，但如果您认为有吸引力，您可能需要进行自己的研究和测试以确保采用最少侵入性的制作方法这个变化。您可以从此处和 Stack Overflow 上的以下问题开始：

    • 3

相关问题