数据库空间不够？试试 SQL 压缩！介绍与教程 – wiki基地

作者： /

数据库空间不够？试试 SQL 压缩！介绍与教程

随着业务的不断发展，数据库中的数据量也呈爆炸式增长。很快，DBA 们就会面临一个令人头疼的问题：数据库存储空间不足。传统的解决方案包括购买更昂贵的存储设备、清理不必要的历史数据或者对数据进行归档。然而，这些方法往往需要投入额外的硬件成本、耗费大量的人力物力，或者可能影响对历史数据的快速访问。

有没有一种方法，可以在不改变硬件基础设施或不丢失数据的情况下，有效地减少数据库占用的空间呢？答案是肯定的：数据库空间压缩（Data Compression）。尤其是对于主流的关系型数据库系统，如 Microsoft SQL Server、Oracle、MySQL (InnoDB) 和 PostgreSQL 等，都提供了不同程度的数据压缩功能。本文将以 SQL Server 为例，详细介绍数据库空间压缩的概念、优势、注意事项以及如何应用它来解决空间不足的问题。

什么是数据库空间压缩？

数据库空间压缩是一种通过特定的算法，减少数据库中存储的数据的物理大小的技术。其核心思想是识别并消除数据中的冗余和重复模式，从而用更紧凑的方式存储信息。当应用程序需要访问压缩的数据时，数据库系统会在后台透明地对数据进行解压缩，供应用程序使用。

数据库压缩通常作用于表或索引的物理存储级别。在 SQL Server 中，主要有两种类型的压缩：

  1. 行压缩 (ROW Compression):

    • 行压缩是在行级别应用的。它主要通过更有效地存储固定长度数据类型、消除某些列值的重复前缀以及使用变长存储来减少每行所需的存储空间。
    • 例如，对于 CHAR 类型的数据，如果实际存储的字符串比定义的长度短，行压缩可以只存储实际的字符串长度加上数据本身，而不是填充到固定长度。
    • 对于数值类型，行压缩可以使用更小的存储空间表示较小的数值。
    • 它对 CPU 的开销相对较低，但压缩率可能不如页压缩高。

  2. 页压缩 (PAGE Compression):

    • 页压缩是在数据库页面级别应用的，通常比行压缩提供更高的压缩率。
    • 页压缩在行压缩的基础上，增加了两种主要的压缩技术：
      • 前缀压缩 (Prefix Compression): 在一个数据页内，如果多行数据在某些列的前缀部分是相同的，页压缩会将这些共同的前缀只存储一次，并在每行中引用这个共享前缀。
      • 字典压缩 (Dictionary Compression): 在一个数据页内，如果某个列的某个值（或者多个值）在页内重复出现多次，页压缩会创建一个字典条目来表示这个值，并在每行中使用一个短引用来指向字典中的这个值。这类似于创建一个小型查找表。
    • 页压缩通常能显著减少大型表和索引的存储空间，但由于需要额外的字典查找和前缀处理，对 CPU 的开销相对更高。

理解这两种压缩级别非常重要，因为它们影响着压缩率和性能权衡。通常，会先尝试行压缩，如果需要更高的压缩率且 CPU 开销可接受，再尝试页压缩。

为什么要使用 SQL 压缩？（优势）

应用数据库空间压缩可以带来多方面的好处：

  1. 显著节省存储空间: 这是最直接和主要的好处。通过压缩，可以减少表和索引占用的物理空间，从而延缓或避免购买新存储设备的需要，降低硬件成本。对于拥有海量数据的企业来说，这一点尤为重要。
  2. 减少 I/O 操作: 当数据被压缩存储时，从磁盘读取相同逻辑数量的数据所需的物理页面会更少。这意味着数据库系统执行的磁盘读写操作次数减少。减少 I/O 是提升数据库性能的关键因素之一，尤其是在 I/O 成为瓶颈的系统中。
  3. 潜在的查询性能提升: 由于需要从磁盘读取的数据量减少，相同时间内可以从磁盘读取更多的数据。这使得更多的数据页可以被缓存到内存中（缓冲区缓存），提高了缓存命中率。从内存中读取数据远比从磁盘中读取数据快得多，因此可以显著提升某些查询的执行速度，特别是那些需要扫描大量数据的查询。尽管解压缩会增加 CPU 负担，但在 I/O 密集型场景下，减少的 I/O 时间往往能弥补甚至超过 CPU 增加的时间，从而带来整体性能提升。
  4. 缩短备份和恢复时间: 备份和恢复操作的时间很大程度上取决于需要读写的数据总量。压缩后的数据库或数据文件体积更小，备份过程需要读取的数据量减少，备份文件也更小，从而缩短备份时间。同样，恢复过程需要写入的数据量减少，也能缩短恢复时间。
  5. 降低成本: 无论是通过延迟硬件采购、减少数据中心空间占用，还是通过提升现有硬件的效率，数据库压缩最终都能帮助企业降低IT基础设施成本。

使用 SQL 压缩的注意事项与权衡

虽然数据库压缩有诸多优点，但也并非万灵药。在使用之前，需要充分了解其潜在的缺点和影响：

  1. 增加 CPU 开销: 压缩和解压缩数据都需要消耗 CPU 资源。当应用程序读写压缩数据时，数据库系统需要在后台执行这些计算任务。如果系统已经处于 CPU 瓶颈状态，应用大量压缩可能会加剧 CPU 负载，反而影响整体性能。因此，在应用压缩之前，评估系统的 CPU 利用率非常重要。
  2. 对写入性能的影响: 插入 (INSERT) 和更新 (UPDATE) 操作在压缩表或索引上可能比在未压缩时更慢。这是因为插入新行或更新现有行时，可能需要重新压缩所在的页面，或者如果插入导致页面分裂，新页面也需要进行压缩。频繁进行小批量写入的场景可能需要仔细评估。
  3. 不适用于所有数据类型: 某些数据类型本身就已经是压缩格式，例如图像 (IMAGE)、音频 (VARBINARY(MAX) 存储的压缩文件) 或视频。对这些数据再次进行数据库压缩通常效果甚微，反而浪费 CPU 资源。此外，非常小的表或索引通常也无法获得显著的压缩效果，因为压缩算法有其开销，而且数据量不足以体现重复模式。
  4. 版本和版本要求: 在 SQL Server 中，数据压缩功能并非在所有版本中都可用。通常它需要 Enterprise Edition、Developer Edition 或 Evaluation Edition。在 Standard Edition 或 Express Edition 中，该功能是不可用的（或者功能受限）。在计划使用压缩之前，务必确认你的 SQL Server 版本支持此功能。
  5. 对索引维护的影响: 对压缩的表或索引进行索引维护操作（如重建或重新组织）时，会涉及数据的解压和重新压缩过程。这可能会使得索引维护操作耗时更长，消耗更多资源。
  6. 规划和测试的重要性: 在生产环境中大规模应用压缩之前，强烈建议在测试环境中进行充分的规划和性能测试。使用 sp_estimate_data_compression_savings 存储过程预估压缩效果，并在典型负载下测试读写性能，确保压缩带来的好处大于其开销。

SQL Server 压缩的工作原理简述

为了更好地理解教程部分，这里对 SQL Server 压缩的工作原理做更详细一点的简述：

  • 行压缩:

    • 存储格式优化: 对于固定长度类型（如 CHAR, NCHAR, BINARY, VARBINARY 的固定部分），如果实际存储的数据小于最大长度，它会只存储实际数据和长度信息，而不是用填充字符填满。
    • 数字类型优化: 对于某些数字类型，如 INT 或 BIGINT，如果存储的值较小，可以使用更少的字节表示。
    • Null 和 0 优化: Null 值和 0 值会用更紧凑的方式表示。
    • Metadata 压缩: 减少存储每行数据的元数据开销。

  • 页压缩:

    • 在行压缩的基础上进行。
    • 前缀压缩: 扫描页面内所有行的指定列，找出共同的前缀，将前缀存储在页眉中，并在每行中用一个短引用代替重复的前缀。例如，如果一个页面内很多地址都以 “浙江省杭州市西湖区” 开头，这个前缀会被提取出来。
    • 字典压缩: 扫描页面内所有列的所有值，找出重复出现的值。将这些重复值存储在一个字典中（位于页眉），并在每行中用一个短引用代替实际的值。例如，如果一个页面内很多行的 “城市” 列都是 “北京”，那么 “北京” 会被放入字典，行中只存储指向字典条目的索引。
    • 这些额外的处理步骤使得页压缩能达到更高的压缩比，但也增加了 CPU 的负担。

SQL Server 压缩教程：如何应用与管理

接下来，我们将通过实际的 SQL 语句来学习如何在 SQL Server 中应用和管理数据压缩。

前提条件:

确保你的 SQL Server 版本支持数据压缩（Enterprise, Developer, Evaluation Edition）。

1. 查看当前对象是否已压缩

可以使用 sys.partitions 视图来查看表或索引的分区是否使用了数据压缩。

“`sql
— 查看特定表的所有分区及其压缩状态
SELECT
o.name AS TableName,
i.name AS IndexName,
p.partition_number,
p.data_compression_desc — NONE, ROW, PAGE
FROM
sys.partitions AS p
JOIN
sys.objects AS o ON p.object_id = o.object_id
JOIN
sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE
o.name = ‘YourTableName’ — 替换为你的表名
ORDER BY
o.name, i.index_id, p.partition_number;

— 查看数据库中所有使用压缩的对象
SELECT
o.name AS TableOrIndexName,
i.name AS IndexName,
p.partition_number,
p.data_compression_desc
FROM
sys.partitions AS p
JOIN
sys.objects AS o ON p.object_id = o.object_id
JOIN
sys.indexes AS i ON p.object_id = i.index_id AND p.index_id = i.index_id
WHERE
p.data_compression > 0 — 0代表NONE, 1代表ROW, 2代表PAGE
ORDER BY
o.name, i.index_id, p.partition_number;
“`

你也可以使用 sp_spaceused 存储过程来获取表的空间使用信息，但它不会直接显示压缩类型，不过空间减少本身就是压缩的体现。更详细的空间使用信息，包括压缩后的空间，可以通过 sys.dm_db_partition_stats DMV (动态管理视图) 获取。

sql
-- 查看特定表或索引的空间使用情况，包括压缩后的信息
SELECT
o.name AS TableName,
i.name AS IndexName,
ps.partition_number,
ps.in_row_data_page_count, -- 行内数据页数量
ps.row_overflow_data_page_count, -- 行溢出数据页数量
ps.lob_data_page_count, -- LOB数据页数量
ps.used_page_count, -- 使用的总页数 (压缩后)
ps.reserved_page_count, -- 保留的总页数
ps.in_row_data_page_count * 8 / 1024.0 AS InRowDataMB, -- 行内数据MB
ps.used_page_count * 8 / 1024.0 AS UsedSpaceMB -- 使用的总空间MB (压缩后)
FROM
sys.dm_db_partition_stats AS ps
JOIN
sys.objects AS o ON ps.object_id = o.object_id
JOIN
sys.indexes AS i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE
o.name = 'YourTableName' -- 替换为你的表名
ORDER BY
o.name, i.index_id, ps.partition_number;

2. 评估压缩的潜在节省空间

在实际应用压缩之前，强烈建议使用 sp_estimate_data_compression_savings 系统存储过程来预测压缩能节省多少空间。这个过程不会真正应用压缩，只是评估。

“`sql
— 评估对整个表应用 ROW 压缩的节省空间
EXEC sp_estimate_data_compression_savings
@schema_name = N’dbo’, — 替换为你的 Schema 名称
@object_name = N’YourTableName’, — 替换为你的表名
@index_id = NULL, — NULL 表示评估表 (堆或聚集索引)
@partition_number = NULL, — NULL 表示评估所有分区
@data_compression = N’ROW’; — 评估 ROW 压缩

— 评估对特定索引应用 PAGE 压缩的节省空间
EXEC sp_estimate_data_compression_savings
@schema_name = N’dbo’, — 替换为你的 Schema 名称
@object_name = N’YourTableName’, — 替换为你的表名
@index_id = 1, — 替换为你的索引 ID (聚集索引通常是1，非聚集索引 > 1)
@partition_number = NULL, — NULL 表示评估所有分区
@data_compression = N’PAGE’; — 评估 PAGE 压缩
“`

sp_estimate_data_compression_savings 的输出会提供原始大小和估计的压缩后大小，让你了解大致的压缩率和空间节省。需要注意的是，这只是一个估算，实际效果可能略有差异。

3. 应用数据压缩

应用数据压缩是通过 ALTER TABLE 或 ALTER INDEX 语句配合 REBUILD 选项来实现的。REBUILD 操作会重建表或索引，在此过程中应用指定的压缩设置。这是一个离线操作，需要对目标对象进行锁定，可能会影响并发访问（取决于版本和选项，但通常需要较强的锁）。对于大型对象，这个过程可能耗时较长且消耗资源。

重要提示: REBUILD 操作会使索引失效并重建，这会消耗 CPU、内存和 I/O 资源。在业务低峰期执行这些操作通常是最佳实践。

a) 对表应用压缩 (如果是堆表或聚集索引表):

“`sql
— 对表应用 ROW 压缩 (如果表没有聚集索引，它是堆；如果有聚集索引，这是对聚集索引应用压缩)
ALTER TABLE YourTableName
REBUILD WITH (DATA_COMPRESSION = ROW);

— 对表应用 PAGE 压缩
ALTER TABLE YourTableName
REBUILD WITH (DATA_COMPRESSION = PAGE);
“`

b) 对特定索引应用压缩 (包括聚集索引和非聚集索引):

虽然对表应用压缩通常就是对聚集索引应用压缩，但明确指定 ALTER INDEX 也是可以的，并且是应用非聚集索引压缩的标准方法。index_id = 1 通常代表聚集索引，index_id > 1 代表非聚集索引。index_id = 0 代表堆表。

“`sql
— 对表的聚集索引应用 ROW 压缩 (假设聚集索引名称为 PK_YourTableName)
ALTER INDEX PK_YourTableName ON YourTableName
REBUILD WITH (DATA_COMPRESSION = ROW);

— 对表的某个非聚集索引应用 PAGE 压缩 (假设非聚集索引名称为 IX_YourTableName_ColumnA)
ALTER INDEX IX_YourTableName_ColumnA ON YourTableName
REBUILD WITH (DATA_COMPRESSION = PAGE);

— 对表的聚集索引应用 PAGE 压缩 (如果你的表有聚集索引)
ALTER INDEX YourClusteredIndexName ON YourTableName
REBUILD WITH (DATA_COMPRESSION = PAGE);
“`

如果你想对表的所有索引（包括聚集索引和所有非聚集索引）应用相同的压缩设置，可以使用以下简化语法：

“`sql
— 对表 YourTableName 的所有索引应用 ROW 压缩
ALTER INDEX ALL ON YourTableName
REBUILD WITH (DATA_COMPRESSION = ROW);

— 对表 YourTableName 的所有索引应用 PAGE 压缩
ALTER INDEX ALL ON YourTableName
REBUILD WITH (DATA_COMPRESSION = PAGE);
“`

c) 对分区表或分区索引应用压缩:

如果你的表或索引是分区的，你可以对特定的分区应用压缩。这在管理大型分区数据集时非常有用，例如只压缩旧的历史数据分区。

“`sql
— 对表 YourPartitionedTable 的特定分区 (分区号为 2) 应用 ROW 压缩
ALTER TABLE YourPartitionedTable
REBUILD PARTITION = 2 WITH (DATA_COMPRESSION = ROW);

— 对分区索引 YourPartitionedIndex ON YourPartitionedTable 的特定分区 (分区号为 3) 应用 PAGE 压缩
ALTER INDEX YourPartitionedIndex ON YourPartitionedTable
REBUILD PARTITION = 3 WITH (DATA_COMPRESSION = PAGE);

— 对分区索引 YourPartitionedIndex ON YourPartitionedTable 的所有分区应用 PAGE 压缩
ALTER INDEX YourPartitionedIndex ON YourPartitionedTable
REBUILD PARTITIONS = ALL WITH (DATA_COMPRESSION = PAGE);

— 或者指定分区范围
ALTER INDEX YourPartitionedIndex ON YourPartitionedTable
REBUILD PARTITIONS = (1 TO 5) WITH (DATA_COMPRESSION = PAGE);
“`

4. 关闭（移除）数据压缩

如果你发现应用压缩后性能下降或者有其他问题，可以随时移除压缩，恢复到未压缩状态。这同样是通过 ALTER TABLE 或 ALTER INDEX 的 REBUILD 操作，将 DATA_COMPRESSION 设置为 NONE 来实现。

“`sql
— 移除表的压缩 (恢复到 NONE)
ALTER TABLE YourTableName
REBUILD WITH (DATA_COMPRESSION = NONE);

— 移除特定索引的压缩 (恢复到 NONE)
ALTER INDEX YourIndexName ON YourTableName
REBUILD WITH (DATA_COMPRESSION = NONE);

— 移除表的所有索引的压缩 (恢复到 NONE)
ALTER INDEX ALL ON YourTableName
REBUILD WITH (DATA_COMPRESSION = NONE);

— 移除分区索引特定分区的压缩
ALTER INDEX YourPartitionedIndex ON YourPartitionedTable
REBUILD PARTITION = 2 WITH (DATA_COMPRESSION = NONE);
“`

移除压缩同样是一个资源消耗型的离线操作，需要规划执行时间。

5. 监控压缩效果和性能

应用压缩后，需要持续监控其效果和对系统性能的影响：

  • 监控空间使用: 再次运行 sp_spaceused 或查询 sys.dm_db_partition_stats 来确认空间是否如预期般减少。
  • 监控 CPU 使用率: 观察系统的 CPU 负载是否显著增加。如果 CPU 成为瓶颈，可能需要权衡压缩的使用范围或级别。
  • 监控查询性能: 运行关键业务查询，比较压缩前后的执行计划和执行时间，确认性能是否有提升或下降。注意是整体性能，包括 I/O 等待时间、CPU 时间等。
  • 监控写入性能: 对于频繁插入或更新的表，测试这些操作的性能。
  • 监控索引维护时间: 观察索引重建或重新组织操作是否耗时过长。

可以使用 SQL Server 自带的性能监视器 (Performance Monitor, PerfMon) 或动态管理视图 (DMV) 来收集这些性能指标。例如，可以关注 SQLServer:Buffer Manager 中的 Page life expectancy (PFE) 和 SQLServer:Databases 中的 Page reads/sec 来评估 I/O 效果和缓存命中率，以及 SQLServer:SQL Statistics 中的 Batch Requests/sec 等来评估整体吞吐量。同时，关注操作系统的 CPU 使用率。

其他数据库系统的压缩概览

虽然本文主要以 SQL Server 为例，但其他主流数据库系统也提供类似的数据压缩功能：

  • Oracle: 提供了多种压缩选项，包括 Basic Compression (用于批量加载)、OLTP Compression (适用于 OLTP 工作负载，支持 DML 操作)、Archive Compression 等。这些压缩可以在表或分区级别应用。
  • MySQL (InnoDB): InnoDB 存储引擎支持表和页面的压缩。可以在创建表时指定 ROW_FORMAT=COMPRESSED 并配置 KEY_BLOCK_SIZE。需要文件系统支持稀疏文件或打孔 (punching holes) 功能。
  • PostgreSQL: PostgreSQL 本身的核心存储没有内置的块级压缩，但其 TOAST (The Oversized-Attribute Storage Technique) 机制会对超出页面大小的长字段（如 TEXT, BYTEA）进行自动压缩和外部存储。此外，一些扩展或第三方工具可能提供更全面的表级或块级压缩功能。

这些数据库系统的压缩机制和语法各不相同，具体使用时需要查阅对应数据库版本的官方文档。但核心目标和原理（减少物理存储、权衡 CPU 开销）是相似的。

总结

数据库空间不足是一个普遍存在的挑战。SQL Server 提供的数据压缩功能（行压缩和页压缩）为解决这一问题提供了一个有效的途径。通过合理地应用压缩，可以在不增加硬件成本的情况下，显著减少数据库占用的存储空间，同时可能通过减少 I/O 操作来提升查询性能。

然而，数据压缩并非没有代价。它会增加 CPU 开销，可能影响写入性能，并且不适用于所有数据类型和所有场景。在决定应用压缩之前，务必充分了解其原理、优势和局限性。

关键步骤回顾:

  1. 评估: 使用 sp_estimate_data_compression_savings 预估压缩效果。
  2. 规划: 选择合适的压缩级别 (ROW 或 PAGE)，规划在业务低峰期执行 REBUILD 操作。
  3. 实施: 使用 ALTER TABLE ... REBUILD 或 ALTER INDEX ... REBUILD 命令应用压缩。
  4. 监控: 持续监控空间节省、CPU 使用率、读写性能以及索引维护时间。
  5. 调整: 根据监控结果决定是否调整压缩级别、范围或移除压缩。

数据库压缩是一个强大的工具，但需要谨慎使用。通过仔细的规划、充分的测试和持续的监控，你可以有效地利用这一功能来优化数据库存储，缓解空间压力，并可能在某些场景下提升性能。

希望这篇文章能帮助你理解 SQL 压缩并成功将其应用于你的数据库环境中！

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部