2022年SqlServer使用数据压缩 .pdf

数据压缩SQL Server 2012其他版本此主题尚未评级 - 评价此主题SQL Server 2012 支持表和索引的行压缩和页压缩。您可以使用数据压缩功能帮助压缩数据库中的数据并帮助减小数据库的大小。除了节省空间之外，数据压缩还可以帮助提高 I/O 密集型工作负荷的性能，因为数据存储在更少的页中，查询需要从磁盘读取的页更少。但是，在与应用程序交换数据时，在数据库服务器上需要额外的 CPU 资源来压缩和解压缩数据。可以为以下数据库对象配置数据压缩：存储为堆的整个表。存储为聚集索引的整个表。整个非聚集索引。整个索引视图。对于已分区表和已分区索引，可为每个分区配置压缩选项，且对象的各个分区的压缩设置不必相同。使用行压缩和页压缩时的注意事项使用行压缩和页压缩时，应注意以下事项：在 Service Pack 或后续版本中，有关数据压缩的详细信息如有更改，恕不另行通知。不是 SQL Server 的每个版本都提供压缩功能。有关详细信息， 请参阅SQL Server 2012 各个版本支持的功能。压缩功能不可用于系统表。通过压缩可在一页上存储更多的行，但不会更改表或索引的最大行大小。当最大行大小加上压缩开销超过最大行大小 8060 个字节时，不能对表启用压缩功能。例如，不能压缩具有 c1 char(8000)和 c2 char(53)列的表，因为存在额外的压缩开销。当使用 vardecimal 存储格式时，会在启用此格式时执行行大小检查。对于行压缩和页压缩，在最初压缩对象时会执行行大小检查，以后在每插入或修改一行时也都会执行这一检查。压缩功能要求遵循下面两条规则：o固定长度类型的更新必须总是成功。o禁用数据压缩必须总是成功。即使已压缩的行可以容纳在页面中（意味着它小于 8060 个字节）， SQL Server 也不允许对哪些在未压缩时无法容纳在行中的更新。当指定分区列表时，可以将各个分区的压缩类型设置为 ROW 、PAGE 或 NONE 。 如果未指定分区列表，将使用语句中指定的数据压缩属性来设置所有分区。创建表或索引时，除非指定了其他压缩设置，否则数据压缩将设置为 NONE 。 修改表时，除非指定了其他压缩设置，否则将保留现有压缩设置。如果指定的分区列表或分区超出范围，将生成错误。非聚集索引不继承表的压缩属性。若要压缩索引，必须显式设置索引的压缩属性。默认情况下，在创建索引时，索引的压缩设置将设置为 NONE 。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 10 页 - - - - - - - - - 对堆创建聚集索引时，除非指定了另一压缩状态，否则聚集索引会继承该堆的压缩状态。如果堆配置为页级压缩，则只有在以下情况下，页才会进行页级压缩：o在启用大容量优化的情况下大容量导入数据。o数据是使用 INSERT INTO . WITH (TABLOCK) 语法插入的。o表是通过执行带 PAGE 压缩选项的 ALTER TABLE . REBUILD 语句重新生成的。通过 DML 操作被分配到堆中的新页面将不会使用 PAGE 压缩，除非重新生成该堆。重新生成堆的方法有：删除压缩然后重新应用压缩，或者创建聚集索引然后再删除聚集索引。若要更改堆的压缩设置，要求对表重新生成所有非聚集索引，以便它们具有指向堆中的新行位置的指针。可以联机或脱机启用或禁用 ROW 或 PAGE 压缩功能。当执行联机操作时，对堆启用压缩功能是单线程的。启用或禁用行压缩或页压缩的磁盘空间要求与创建或重新生成索引时的磁盘空间要求相同。对于已分区数据，可以通过每次对一个分区启用或禁用压缩功能来减少所需的空间。若要确定已分区表中分区的压缩状态，请查询 sys.partitions 目录视图的data_compression 列。压缩索引时，可以使用行压缩和页压缩来压缩叶级页。非叶级页不接收页压缩。由于大小的关系，大值数据类型有时不与普通行数据存储在一起，而是存储在特殊用途的页上。对于单独存储的数据，数据压缩不可用。SQL Server 2005 中实现 vardecimal 存储格式的表在升级后会保留该设置。可以向具有 vardecimal 存储格式的表应用行压缩。但是，因为行压缩是 vardecimal 存储格式的超集，所以不必保留 vardecimal 存储格式。将 vardecimal 存储格式与行压缩一起使用时，十进制值不会进一步压缩。可以向具有 vardecimal 存储格式的表应用页压缩；但是，vardecimal 存储格式列可能不会实现进一步的压缩。注意SQL Server 2012 支持 vardecimal 存储格式；但是，由于行级压缩可实现同样的目标，储格式。后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使在使用该功能的应用程序。压缩对已分区表和已分区索引的影响如果对已分区表和已分区索引使用数据压缩，则应注意以下事项：如果使用 ALTER PARTITION 语句拆分分区，则两个分区均继承原始分区的数据压缩属性。合并两个分区时，生成的分区将继承目标分区的数据压缩属性。若要切换分区，该分区的数据压缩属性必须与表的压缩属性匹配。可以使用两种语法变体来修改已分区表或已分区索引的压缩：o下面的语法仅重新生成被引用分区：oALTER TABLE 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 10 页 - - - - - - - - - oREBUILD PARTITION = 1 WITH (DATA_COMPRESSION = ) o下面的语法通过对未引用的任何分区使用现有压缩设置来重新生成整个表：oALTER TABLE oREBUILD PARTITION = ALL oWITH (DATA_COMPRESSION = PAGE ON PARTITIONS(), o. ) 已分区索引使用 ALTER INDEX 遵循同样的原则。删除聚集索引时，除非修改了分区方案，否则相应的堆分区将保留其数据压缩设置。如果分区方案已更改，则所有分区都将重新生成为未压缩状态。若要删除聚集索引并更改分区方案，需要执行下列步骤：1. 删除聚集索引。2. 使用指定压缩选项的 ALTER TABLE . REBUILD . 选项来修改表。若要删除聚集索引，脱机操作的执行速度很快，因为只删除较高级别的聚集索引。如果联机删除聚集索引，则 SQL Server 必须重新生成堆两次，一次针对步骤 1 ，一次针对步骤 2 。压缩对复制的影响如果将数据压缩与复制一起使用，则应注意以下事项：当快照代理生成初始架构脚本时，新架构将对表及其索引使用相同的压缩设置。不能仅对表启用压缩，而不对索引启用压缩。对于事务复制，项目架构选项决定了必须对哪些依赖对象和属性编写脚本。有关详细信息，请参阅sp_addarticle。分发代理在应用脚本时，不对下级订阅服务器进行检查。如果选择了压缩的复制，则在下级订阅服务器上创建表将会失败。在混合拓扑中，不启用压缩的复制。对于合并复制，发布兼容级别优先于架构选项，并决定了将编写脚本的架构对象。在混合拓扑中，如果不是必须支持新的压缩选项，则发布兼容级别应设置为下级订阅服务器版本。否则，应在创建表后在订阅服务器上压缩表。下表列出了在复制期间控制压缩的复制设置。用户意图为表或索引复制分区方案复制压缩设置脚本复制分区方案并在该分区上的订阅服务器上启用压缩。正确正确对分本。复制分区方案，但不压缩订阅服务器上的数据。正确错误对分的压不复制分区方案，也不压缩订阅服务器上的数据。错误错误不对如果发布服务器上的所有分区均压缩，则压缩订阅服错误正确检查名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 10 页 - - - - - - - - - 务器上的表，但不复制分区方案。缩。在表压缩对其他SQL Server 组件的影响压缩发生在存储引擎中，数据以未压缩状态呈现给 SQL Server 的其他大部分组件。这决定了其他组件上的压缩效果仅限于以下方面：大容量导入和导出操作导出数据时，即使采用本机格式，数据也以未压缩的行格式输出。这会导致导出的数据文件的大小比源数据要大得多。导入数据时， 如果已对目标表启用压缩，则存储引擎会将数据转换为压缩的行格式。这样所使用的 CPU 资源会比将数据导入未压缩表时使用的 CPU 资源多。如果以大容量方式将数据导入具有页压缩设置的堆，则在插入数据时，大容量导入操作会尝试使用页压缩来压缩数据。压缩对备份和还原没有影响。压缩对日志传送没有影响。数据压缩与稀疏列不兼容。因此，无法压缩包含稀疏列的表，也不能将稀疏列添加到压缩表。启用压缩可以导致查询计划更改，因为数据是用不同的页数和每页不同的行数存储的。请参阅参考CREATE PARTITION SCHEME (Transact-SQL)CREATE PARTITION FUNCTION (Transact-SQL)CREATE TABLE (Transact-SQL)ALTER TABLE (Transact-SQL)CREATE INDEX (Transact-SQL)ALTER INDEX (Transact-SQL)概念行压缩的实现页压缩的实现Unicode 压缩的实现名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 10 页 - - - - - - - - - 行压缩的实现SQL Server 2012其他版本本主题概述了数据库引擎是如何实现行压缩的。此摘要提供了有助于您规划数据所需存储空间的基本信息。启用压缩只会更改与数据类型相关联的数据的物理存储格式，而不会更改其语法或语义。当对一个或多个表启用压缩时，不需要更改应用程序。新的记录存储格式主要有以下更改：减少了与记录相关联的元数据开销。此元数据为有关列、列长度和偏移量的信息。在某些情况下，元数据开销可能大于旧的存储格式。它对于数值类型（例如，integer、decimal和 float）和基于数值的类型（例如，datetime和 money）使用可变长度存储格式。它通过使用不存储空字符的可变长度格式来存储定长字符串。注意将对所有数据类型的 NULL 和 0 值进行优化，从而使它们不占用任何字节。行压缩影响存储的方式下表介绍了行压缩是如何影响 SQL Server 中的现有类型的。此表不包括通过使用页压缩可以节省的空间。数据类型是否影响存储？说明tinyint否1 个字节是所需的最小存储单位。smallint是如果值使用 1 个字节即可存储，则将只使用 1 个字节int是只使用所需字节数。例如，如果值可以用 1 个字节存储空间。bigint是只使用所需字节数。例如，如果值可以用 1 个字节存储空间。decimal是此存储与 vardecimal 存储格式完全相同。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 10 页 - - - - - - - - - numeric是此存储与 vardecimal 存储格式完全相同。bit是元数据开销使此类型达到 4 个位。smallmoney是使用 4 字节整数表示整数数据。货币值乘以 10000，并数点之后的所有位数。此类型具有类似于整数类型存储money是使用 8 字节整数表示整数数据。货币值乘以 10000，并数点之后的所有位数。此类型的范围比smallmoney 更型存储优化的存储优化。float是不存储带零的最低有效字节。float压缩主要适用于尾real是不存储带零的最低有效字节。real压缩主要适用于尾smalldatetime否使用两个 2 字节整数表示整数数据。日期占用 2 个字日以来经过的天数。从 1902 年起便需要 2 个字节。会节省任何空间。时间是自午夜以来经过的分钟数。超过 4AM 后，时间如果 smalldatetime只用于表示日期（常见情况），则最高有效字节格式存储时间，压缩操作可节省 2 个字节datetime是使用两个 4 字节整数表示整数数据。此整数值表示自天数。 前 2 个字节最高可以表示 2079 年。在 2079 2 个字节。 每个整数值表示 3.33 毫秒。 压缩在前五分之后将使用第四个字节。因此， 压缩在 4PM 之后只能节任何其他整数一样进行压缩时，压缩可在日期方面节省date否使用 3 字节整数表示整数数据。这表示自 0001 年 1日期，行压缩使用所有 3 个字节。这不会节省任何空time否使用 3 到 6 个字节表示整数数据。有从 0 到 9 的各节。 压缩后的空间按如下方式使用：精度 = 0 。 字节数 = 3 。 每个整数值表示一秒。表示到 6PM，这样可以节省 1 个字节。精度 = 1 。 字节数 = 3 。 每个整数值表示 1/10第三个字节。结果是只能节省很少的空间。精度 = 2 。 字节数 = 3 。 与前一情况类似，节省精度 = 3 。 字节数 = 4 。 由于直到 5AM 都将使间很小。精度 = 4 。 字节数 = 4 。 前 27 秒会使用前 3 精度 = 5 ，字节数 = 5 。 在中午 12 点之后，将精度 = 6 和 7 ，字节数 = 5 。 不能实现任何节省名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 10 页 - - - - - - - - - 精度 = 8 ，字节数 = 6 。 在 3AM 之后将使用第六进行行压缩时存储不做任何更改。总体上而言，压缩空间节省。datetime2是使用 6 到 9 个字节表示整数数据。前 4 个字节表示决于指定的时间精度。此整数值表示自 0001 年 1 月 1 日以来经过的天数，日。 若要表示 2005 年的日期，压缩操作将使用 3 个对于时间则不会节省任何空间， 这是因为压缩允许对于各节。 因此，当时间精度为一秒时，压缩使用 2 个字节使用第二个字节。datetimeoffset是类似于 datetime2 ，所不同的是格式为 HH:MM 的时区占就像 datetime2 ，压缩可以节省 2 个字节。对于时区值，在大多数情况下 MM 值都可能是 0 。 因此进行行压缩时存储不做任何更改。char是将删除尾随填充字符。请注意，不管使用何种排序规则填充字符。varchar否无效。text否无效。nchar是将删除尾随填充字符。请注意，不管使用何种排序规则填充字符。nvarchar否无效。ntext否无效。binary是将删除尾随的零。varbinary否无效。image否无效。cursor否无效。timestamp / rowversion是使用 8 个字节表示整数数据。每个数据库均维护有一0 开始。 会像压缩任何其他整数值一样压缩此值。sql_variant否无效。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 10 页 - - - - - - - - - uniqueidentifier否无效。table否无效。xml否无效。用户定义类型否这在内部表示为varbinary 。FILESTREAM 否这在内部表示为varbinary 。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 10 页 - - - - - - - - - 页压缩的实现SQL Server 2012其他版本1（共1）对本文的评价是有帮助 - 评价此主题本主题概述了数据库引擎是如何实现页压缩的。此摘要提供了有助于您规划数据所需存储空间的基本信息。表、表分区、索引和索引分区的页压缩都是类似的。以下针对表的页压缩的说明同样适用于所有对象类型的页压缩。以下示例压缩的是字符串，但对于其他数据类型而言，前缀压缩和字典压缩的原理都是相同的。使用页压缩压缩表和索引的叶级别的过程由按以下顺序进行的三个操作组成：1.行压缩2.前缀压缩3.字典压缩当使用页压缩时，将仅使用行压缩来压缩索引的非叶级别页。有关行压缩的详细信息，请参阅行压缩的实现。前缀压缩对于要压缩的每一页，前缀压缩采用以下步骤：1.对于每一列，将确定一个值，此值可用于减少每一列中的值的存储空间。2.将创建表示每一列的前缀值的行，并将其存储在紧随页头之后的压缩信息 (CI) 结构中。3.列中重复的前缀值将由指向对应前缀的引用进行替换。如果行中的值与所选前缀值并不完全匹配，则仍会指示存在部分匹配。下图显示了前缀压缩之前表的一个示例页。下图显示的是同一页在前缀压缩之后的样子。前缀移至页头，列值更改为指向前缀的引用。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 10 页 - - - - - - - - - 在第一行的第一列，值 4b 指示为该行显示前缀的前四个字符 (aaab) 和字符 b 。 这样的话，结果值就是 aaabb ，这是原始值。字典压缩前缀压缩完成后，将应用字典压缩。字典压缩搜索页面上任意位置的重复值，然后将它们存储在 CI 区域中。与前缀压缩不同，字典压缩不局限于一列。字典压缩可以替换页面上任意位置出现的重复值。下图显示的是同一页在字典压缩之后的样子。请注意，值 4b 已由页的其他列引用。进行页压缩时当创建具有页压缩的新表时，不会进行压缩。但是， 表的元数据会指示应使用页压缩。当将数据添加到第一个数据页时，会对数据进行行压缩。因为此页未满，所以无法通过页压缩获得任何益处。如果页已满，则添加下一行将引导页压缩操作。将查看整个页；计算每一列以进行前缀压缩，然后计算所有列以进行字典压缩。如果页压缩已在页上为要添加的行创建了足够的空间，则添加该行，并对数据进行行压缩和页压缩。如果通过页压缩获得的空间减去 CI 结构所需空间之后剩余的空间并不充足，则不会对此页使用页压缩。以后，行将添加到新页上，如果新页中也无法再容纳更多的行，则将再向表中添加一个新页。与第一页类似，新页最初也不进行页压缩。当包含数据的现有表转换为页压缩时，将重新生成和计算每一页。重新生成所有页会导致重新生成表、索引或分区。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 10 页 - - - - - - - - -