自SqlServer使用数据压缩.docx

自SqlServer使用数据压缩.docx

《自SqlServer使用数据压缩.docx》由会员分享，可在线阅读，更多相关《自SqlServer使用数据压缩.docx（16页珍藏版）》请在冰豆网上搜索。

自SqlServer使用数据压缩

数据压缩

SQLServer2012

其他版本

此主题尚未评级 - 评价此主题

SQLServer2012支持表和索引的行压缩和页压缩。

 您可以使用数据压缩功能帮助压缩数据库中的数据并帮助减小数据库的大小。

 除了节省空间之外，数据压缩还可以帮助提高I/O密集型工作负荷的性能，因为数据存储在更少的页中，查询需要从磁盘读取的页更少。

 但是，在与应用程序交换数据时，在数据库服务器上需要额外的CPU资源来压缩和解压缩数据。

 可以为以下数据库对象配置数据压缩：

∙存储为堆的整个表。

∙存储为聚集索引的整个表。

∙整个非聚集索引。

∙整个索引视图。

∙对于已分区表和已分区索引，可为每个分区配置压缩选项，且对象的各个分区的压缩设置不必相同。

使用行压缩和页压缩时的注意事项

使用行压缩和页压缩时，应注意以下事项：

∙在ServicePack或后续版本中，有关数据压缩的详细信息如有更改，恕不另行通知。

∙不是SQLServer的每个版本都提供压缩功能。

 有关详细信息，请参阅 SQLServer2012各个版本支持的功能。

∙压缩功能不可用于系统表。

∙通过压缩可在一页上存储更多的行，但不会更改表或索引的最大行大小。

∙当最大行大小加上压缩开销超过最大行大小8060个字节时，不能对表启用压缩功能。

 例如，不能压缩具有c1 char（8000） 和c2 char（53） 列的表，因为存在额外的压缩开销。

 当使用vardecimal存储格式时，会在启用此格式时执行行大小检查。

 对于行压缩和页压缩，在最初压缩对象时会执行行大小检查，以后在每插入或修改一行时也都会执行这一检查。

 压缩功能要求遵循下面两条规则：

o固定长度类型的更新必须总是成功。

o禁用数据压缩必须总是成功。

 即使已压缩的行可以容纳在页面中（意味着它小于8060个字节），SQLServer也不允许对哪些在未压缩时无法容纳在行中的更新。

∙当指定分区列表时，可以将各个分区的压缩类型设置为ROW、PAGE或NONE。

 如果未指定分区列表，将使用语句中指定的数据压缩属性来设置所有分区。

 创建表或索引时，除非指定了其他压缩设置，否则数据压缩将设置为NONE。

 修改表时，除非指定了其他压缩设置，否则将保留现有压缩设置。

∙如果指定的分区列表或分区超出范围，将生成错误。

∙非聚集索引不继承表的压缩属性。

 若要压缩索引，必须显式设置索引的压缩属性。

 默认情况下，在创建索引时，索引的压缩设置将设置为NONE。

∙对堆创建聚集索引时，除非指定了另一压缩状态，否则聚集索引会继承该堆的压缩状态。

∙如果堆配置为页级压缩，则只有在以下情况下，页才会进行页级压缩：

o在启用大容量优化的情况下大容量导入数据。

o数据是使用INSERTINTO... WITH（TABLOCK）语法插入的。

o表是通过执行带PAGE压缩选项的ALTERTABLE... REBUILD语句重新生成的。

∙通过DML操作被分配到堆中的新页面将不会使用PAGE压缩，除非重新生成该堆。

 重新生成堆的方法有：

删除压缩然后重新应用压缩，或者创建聚集索引然后再删除聚集索引。

∙若要更改堆的压缩设置，要求对表重新生成所有非聚集索引，以便它们具有指向堆中的新行位置的指针。

∙可以联机或脱机启用或禁用ROW或PAGE压缩功能。

 当执行联机操作时，对堆启用压缩功能是单线程的。

∙启用或禁用行压缩或页压缩的磁盘空间要求与创建或重新生成索引时的磁盘空间要求相同。

 对于已分区数据，可以通过每次对一个分区启用或禁用压缩功能来减少所需的空间。

∙若要确定已分区表中分区的压缩状态，请查询sys.partitions目录视图的data_compression列。

∙压缩索引时，可以使用行压缩和页压缩来压缩叶级页。

 非叶级页不接收页压缩。

∙由于大小的关系，大值数据类型有时不与普通行数据存储在一起，而是存储在特殊用途的页上。

 对于单独存储的数据，数据压缩不可用。

∙SQLServer2005中实现vardecimal存储格式的表在升级后会保留该设置。

 可以向具有vardecimal存储格式的表应用行压缩。

但是，因为行压缩是vardecimal存储格式的超集，所以不必保留vardecimal存储格式。

 将vardecimal存储格式与行压缩一起使用时，十进制值不会进一步压缩。

 可以向具有vardecimal存储格式的表应用页压缩；但是，vardecimal存储格式列可能不会实现进一步的压缩。

 注意

SQLServer2012支持vardecimal存储格式；但是，由于行级压缩可实现同样的目标，因此不推荐使用vardecimal存储格式。

 后续版本的MicrosoftSQLServer将删除该功能。

请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。

压缩对已分区表和已分区索引的影响

如果对已分区表和已分区索引使用数据压缩，则应注意以下事项：

∙如果使用ALTERPARTITION语句拆分分区，则两个分区均继承原始分区的数据压缩属性。

∙合并两个分区时，生成的分区将继承目标分区的数据压缩属性。

∙若要切换分区，该分区的数据压缩属性必须与表的压缩属性匹配。

∙可以使用两种语法变体来修改已分区表或已分区索引的压缩：

o下面的语法仅重新生成被引用分区：

oALTERTABLE

oREBUILDPARTITION=1WITH（DATA_COMPRESSION=）

o下面的语法通过对未引用的任何分区使用现有压缩设置来重新生成整个表：

oALTERTABLE

oREBUILDPARTITION=ALL

oWITH（DATA_COMPRESSION=PAGEONPARTITIONS（）,

o...）

已分区索引使用ALTERINDEX遵循同样的原则。

∙删除聚集索引时，除非修改了分区方案，否则相应的堆分区将保留其数据压缩设置。

 如果分区方案已更改，则所有分区都将重新生成为未压缩状态。

 若要删除聚集索引并更改分区方案，需要执行下列步骤：

1. 删除聚集索引。

2. 使用指定压缩选项的ALTERTABLE... REBUILD... 选项来修改表。

若要删除聚集索引，脱机操作的执行速度很快，因为只删除较高级别的聚集索引。

 如果联机删除聚集索引，则SQLServer必须重新生成堆两次，一次针对步骤1，一次针对步骤2。

压缩对复制的影响

如果将数据压缩与复制一起使用，则应注意以下事项：

∙当快照代理生成初始架构脚本时，新架构将对表及其索引使用相同的压缩设置。

 不能仅对表启用压缩，而不对索引启用压缩。

∙对于事务复制，项目架构选项决定了必须对哪些依赖对象和属性编写脚本。

 有关详细信息，请参阅 sp_addarticle。

分发代理在应用脚本时，不对下级订阅服务器进行检查。

 如果选择了压缩的复制，则在下级订阅服务器上创建表将会失败。

 在混合拓扑中，不启用压缩的复制。

∙对于合并复制，发布兼容级别优先于架构选项，并决定了将编写脚本的架构对象。

在混合拓扑中，如果不是必须支持新的压缩选项，则发布兼容级别应设置为下级订阅服务器版本。

 否则，应在创建表后在订阅服务器上压缩表。

下表列出了在复制期间控制压缩的复制设置。

用户意图

为表或索引复制分区方案

复制压缩设置

脚本编写行为

复制分区方案并在该分区上的订阅服务器上启用压缩。

正确

正确

对分区方案和压缩设置均编写脚本。

复制分区方案，但不压缩订阅服务器上的数据。

正确

错误

对分区方案编写脚本，但不对分区的压缩设置编写脚本。

不复制分区方案，也不压缩订阅服务器上的数据。

错误

错误

不对分区和压缩设置编写脚本。

如果发布服务器上的所有分区均压缩，则压缩订阅服务器上的表，但不复制分区方案。

错误

正确

检查是否对所有分区均启用了压缩。

在表级别对压缩编写脚本。

压缩对其他SQLServer组件的影响

压缩发生在存储引擎中，数据以未压缩状态呈现给SQLServer的其他大部分组件。

 这决定了其他组件上的压缩效果仅限于以下方面：

∙大容量导入和导出操作

导出数据时，即使采用本机格式，数据也以未压缩的行格式输出。

 这会导致导出的数据文件的大小比源数据要大得多。

导入数据时，如果已对目标表启用压缩，则存储引擎会将数据转换为压缩的行格式。

 这样所使用的CPU资源会比将数据导入未压缩表时使用的CPU资源多。

如果以大容量方式将数据导入具有页压缩设置的堆，则在插入数据时，大容量导入操作会尝试使用页压缩来压缩数据。

∙压缩对备份和还原没有影响。

∙压缩对日志传送没有影响。

∙数据压缩与稀疏列不兼容。

 因此，无法压缩包含稀疏列的表，也不能将稀疏列添加到压缩表。

∙启用压缩可以导致查询计划更改，因为数据是用不同的页数和每页不同的行数存储的。

请参阅

参考

CREATEPARTITIONSCHEME（Transact-SQL）

CREATEPARTITIONFUNCTION（Transact-SQL）

CREATETABLE（Transact-SQL）

ALTERTABLE（Transact-SQL）

CREATEINDEX（Transact-SQL）

ALTERINDEX（Transact-SQL）

概念

行压缩的实现

页压缩的实现

Unicode压缩的实现

行压缩的实现

SQLServer2012

其他版本

本主题概述了数据库引擎是如何实现行压缩的。

 此摘要提供了有助于您规划数据所需存储空间的基本信息。

启用压缩只会更改与数据类型相关联的数据的物理存储格式，而不会更改其语法或语义。

 当对一个或多个表启用压缩时，不需要更改应用程序。

 新的记录存储格式主要有以下更改：

∙减少了与记录相关联的元数据开销。

 此元数据为有关列、列长度和偏移量的信息。

 在某些情况下，元数据开销可能大于旧的存储格式。

∙它对于数值类型（例如，integer、decimal 和 float）和基于数值的类型（例如，datetime 和 money）使用可变长度存储格式。

∙它通过使用不存储空字符的可变长度格式来存储定长字符串。

 注意

将对所有数据类型的NULL和0值进行优化，从而使它们不占用任何字节。

行压缩影响存储的方式

下表介绍了行压缩是如何影响SQLServer中的现有类型的。

 此表不包括通过使用页压缩可以节省的空间。

数据类型

是否影响存储？

说明

tinyint

否

1个字节是所需的最小存储单位。

smallint

是

如果值使用1个字节即可存储，则将只使用1个字节。

int

是

只使用所需字节数。

 例如，如果值可以用1个字节存储，则将只占用1个字节的存储空间。

bigint

是

只使用所需字节数。

 例如，如果值可以用1个字节存储，则将只占用1个字节的存储空间。

decimal

是

此存储与vardecimal存储格式完全相同。

numeric

是

此存储与vardecimal存储格式完全相同。

bit

是

元数据开销使此类型达到4个位。

smallmoney

是

使用4字节整数表示整数数据。

 货币值乘以10000，并在存储生成的整数值时删除小数点之后的所有位数。

 此类型具有类似于整数类型存储优化的存储优化。

money

是

使用8字节整数表示整数数据。

 货币值乘以10000，并在存储生成的整数值时删除小数点之后的所有位数。

 此类型的范围比smallmoney 更大。

 此类型具有类似于整数类型存储优化的存储优化。

float

是

不存储带零的最低有效字节。

 float 压缩主要适用于尾数中的非小数值。

real

是

不存储带零的最低有效字节。

 real 压缩主要适用于尾数中的非小数值。

smalldatetime

否

使用两个2字节整数表示整数数据。

 日期占用2个字节。

 它是自1901年1月1日以来经过的天数。

 从1902年起便需要2个字节。

 因此，自该时间之后的日期不会节省任何空间。

时间是自午夜以来经过的分钟数。

 超过4AM后，时间值就开始使用第二个字节。

如果 smalldatetime 只用于表示日期（常见情况），则时间为0.0。

 通过为行压缩以最高有效字节格式存储时间，压缩操作可节省2个字节。

datetime

是

使用两个4字节整数表示整数数据。

 此整数值表示自1900年1月1日以来经过的天数。

 前2个字节最高可以表示2079年。

在2079年之前，此类压缩始终可以节省2个字节。

 每个整数值表示3.33毫秒。

 压缩在前五分钟只占用前2个字节，在4PM之后将使用第四个字节。

 因此，压缩在4PM之后只能节省1个字节。

 当 datetime 像任何其他整数一样进行压缩时，压缩可在日期方面节省2个字节。

date

否

使用3字节整数表示整数数据。

 这表示自0001年1月1日起的日期。

 对于当代日期，行压缩使用所有3个字节。

 这不会节省任何空间。

time

否

使用3到6个字节表示整数数据。

 有从0到9的各种精度，会占用3到6个字节。

 压缩后的空间按如下方式使用：

∙精度=0。

 字节数=3。

 每个整数值表示一秒。

 使用2个字节，压缩最长可表示到6PM，这样可以节省1个字节。

∙精度=1。

 字节数=3。

 每个整数值表示1/10秒。

 在2AM之前，压缩使用第三个字节。

 结果是只能节省很少的空间。

∙精度=2。

 字节数=3。

 与前一情况类似，节省空间的可能性很小。

∙精度=3。

 字节数=4。

 由于直到5AM都将使用前3个字节，因而节省的空间很小。

∙精度=4。

 字节数=4。

 前27秒会使用前3个字节。

 不会节省任何空间。

∙精度=5，字节数=5。

 在中午12点之后，将使用第五个字节。

∙精度=6和7，字节数=5。

 不能实现任何节省。

∙精度=8，字节数=6。

 在3AM之后将使用第六个字节。

进行行压缩时存储不做任何更改。

 总体上而言，压缩 time 数据类型不会获得很大的空间节省。

datetime2

是

使用6到9个字节表示整数数据。

 前4个字节表示日期。

 时间占用的字节数将取决于指定的时间精度。

此整数值表示自0001年1月1日以来经过的天数，上限为9999年12月31日。

 若要表示2005年的日期，压缩操作将使用3个字节。

对于时间则不会节省任何空间，这是因为压缩允许对于各种时间精度使用2到4个字节。

 因此，当时间精度为一秒时，压缩使用2个字节来存储时间，在255秒之后将使用第二个字节。

datetimeoffset

是

类似于 datetime2，所不同的是格式为HH:

MM的时区占用2个字节。

就像 datetime2，压缩可以节省2个字节。

对于时区值，在大多数情况下MM值都可能是0。

 因此，压缩可能会节省1个字节。

进行行压缩时存储不做任何更改。

char

是

将删除尾随填充字符。

 请注意，不管使用何种排序规则，数据库引擎均将插入相同的填充字符。

varchar

否

无效。

text

否

无效。

nchar

是

将删除尾随填充字符。

 请注意，不管使用何种排序规则，数据库引擎均将插入相同的填充字符。

nvarchar

否

无效。

ntext

否

无效。

binary

是

将删除尾随的零。

varbinary

否

无效。

image

否

无效。

cursor

否

无效。

timestamp/rowversion

是

使用8个字节表示整数数据。

 每个数据库均维护有一个时间戳计数器，并且它的值从0开始。

 会像压缩任何其他整数值一样压缩此值。

sql_variant

否

无效。

uniqueidentifier

否

无效。

table

否

无效。

xml

否

无效。

用户定义类型

否

这在内部表示为 varbinary。

否

这在内部表示为 varbinary。

页压缩的实现

SQLServer2012

其他版本

1（共1）对本文的评价是有帮助 - 评价此主题

本主题概述了数据库引擎是如何实现页压缩的。

 此摘要提供了有助于您规划数据所需存储空间的基本信息。

表、表分区、索引和索引分区的页压缩都是类似的。

 以下针对表的页压缩的说明同样适用于所有对象类型的页压缩。

 以下示例压缩的是字符串，但对于其他数据类型而言，前缀压缩和字典压缩的原理都是相同的。

使用页压缩压缩表和索引的叶级别的过程由按以下顺序进行的三个操作组成：

1.行压缩

2.前缀压缩

3.字典压缩

当使用页压缩时，将仅使用行压缩来压缩索引的非叶级别页。

 有关行压缩的详细信息，请参阅行压缩的实现。

前缀压缩

对于要压缩的每一页，前缀压缩采用以下步骤：

1.对于每一列，将确定一个值，此值可用于减少每一列中的值的存储空间。

2.将创建表示每一列的前缀值的行，并将其存储在紧随页头之后的压缩信息（CI）结构中。

3.列中重复的前缀值将由指向对应前缀的引用进行替换。

 如果行中的值与所选前缀值并不完全匹配，则仍会指示存在部分匹配。

下图显示了前缀压缩之前表的一个示例页。

下图显示的是同一页在前缀压缩之后的样子。

 前缀移至页头，列值更改为指向前缀的引用。

在第一行的第一列，值4b指示为该行显示前缀的前四个字符（aaab）和字符b。

 这样的话，结果值就是aaabb，这是原始值。

字典压缩

前缀压缩完成后，将应用字典压缩。

 字典压缩搜索页面上任意位置的重复值，然后将它们存储在CI区域中。

 与前缀压缩不同，字典压缩不局限于一列。

 字典压缩可以替换页面上任意位置出现的重复值。

 下图显示的是同一页在字典压缩之后的样子。

请注意，值4b已由页的其他列引用。

进行页压缩时

当创建具有页压缩的新表时，不会进行压缩。

 但是，表的元数据会指示应使用页压缩。

 当将数据添加到第一个数据页时，会对数据进行行压缩。

 因为此页未满，所以无法通过页压缩获得任何益处。

 如果页已满，则添加下一行将引导页压缩操作。

 将查看整个页；计算每一列以进行前缀压缩，然后计算所有列以进行字典压缩。

 如果页压缩已在页上为要添加的行创建了足够的空间，则添加该行，并对数据进行行压缩和页压缩。

 如果通过页压缩获得的空间减去CI结构所需空间之后剩余的空间并不充足，则不会对此页使用页压缩。

 以后，行将添加到新页上，如果新页中也无法再容纳更多的行，则将再向表中添加一个新页。

 与第一页类似，新页最初也不进行页压缩。

当包含数据的现有表转换为页压缩时，将重新生成和计算每一页。

 重新生成所有页会导致重新生成表、索引或分区。