SQL SERVER 2008 笔记之行压缩和页压缩 收藏
/*
2008 压缩
行压缩
SQL SERVER 2008 引进了行压缩这个新特性,只有企业版具有该新特性.行压缩只需要最低的空间对行数据进行压缩.行压缩可以在CREATE一个表,索引或者ALTER一个表,索引的时候进行创建.压缩可以在行级别,也可以在页级别.同样备份的时候也可以进行压缩.以下先举例说明如何创建一个表行压缩和ALTER表的时候添加行压缩.
建立测试数据
*/
CREATE DATABASE COMPRESSION_TEST
GO
USE COMPRESSION_TEST
GO
/* 建立一个具有行压缩的表*/
CREATE TABLE Compression_tb1
(id int not null identity(1,1),
departid int not null,
JOBdesc CHAR(2000) not null --为了效果明显一点,可以设置长点
)
with( DATA_COMPRESSION=ROW) --建立到有行压缩选项的表
/* 可以用如下语句从新配置压缩选项*/
ALTER TABLE Compression_tb1 REBUILD
WITH (DATA_COMPRESSION=NONE) --去除压缩选项
/*
以上个语句可以颠倒过来譬如先建立没有压缩选项的表,然后通过ALTER表配置获得压缩选项
其中DATA_COMPRESSION有个参数NONE,ROW,PAGE
下面对测试表填充垃圾数据来演示压缩的效果
*/
INSERT Compression_tb1(departid ,JOBdesc)
VALUES(CAST(RAND()*20 as int)
, REPLICATE('a',50))
GO 100000
/*
数据添加好,我可以用系统SP sp_estimate_data_compression_savings 来估计使用行压缩或者页压缩可以节省多少磁盘空间.sp_estimate_data_compression_savings有个参数:需要压缩的表的架构名称、 对象名称、索引ID、分区号和压缩方式。如下的示例先检查行压缩可以节省多少空间。
*/
EXEC SYS.sp_estimate_data_compression_savings
@SCHEMA_NAME='DBO',
@OBJECT_NAME='Compression_tb1',
@INDEX_ID=NULL,
@PARTITION_NUMBER=NULL,
@DATA_COMPRESSION='ROW'
/*
结果(行转列好阅读)
object_name Compression_tb1
schema_name DBO
index_id 1
-- 索引的索引ID：= 堆,1 = 聚集索引,>1 = 非聚集索引
partition_number 1
-- 分区号。对于未分区的表或索引，返回1
size_with_current_compression_setting(KB) 266720
--当前存在的所请求的表、索引或分区的大小。
size_with_requested_compression_setting(KB) 8808
--使用请求的压缩设置及现有填充因子（如果适用）且假定不存在碎片
--时的表、索引或分区的估计大小。
sample_size_with_current_compression_setting(KB) 29536
sample_size_with_requested_compression_setting(KB) 976
--使用请求的压缩设置及现有填充因子（如果适用）且假定不存在碎片
--时的表、索引或分区的估计大小。
上面的结果看可以看出对当前表使用行压缩可以节省-6712=194040 KB.样本大小数据是基于存储过程将样本数据载入到TEMPDB的克隆表中,并验证相应的压缩率.
但是有的情况可能无法节省空间，因为这取决于填充因子和行大小。例如，如果某行长度为8000 字节并且您将该行的大小减少40%，则数据页上仍只能容纳一行。因此不会节省空间。
下面给表添加行压缩选项
*/
ALTER TABLE Compression_tb1 REBUILD
WITH (DATA_COMPRESSION=ROW)
EXEC sp_spaceused Compression_tb1
/*
name rows reserved data index_size unused
--------------- ----------- ----------- ---------- ----------- -------
Compression_tb1 100000 6544 KB 6440 KB 8 KB 96 KB
结果看以看出压缩后的表使用看6544 KB的空间.
对于那些已经存在数据的表经行行压缩我们可以使用ALTER TABLE... WITH(DATA_COMPRESSION=ROW)
下面演示索引的行压缩
*/
CREATE TABLE Compression_tb2
(id int not null identity(1,1),
departid int not null,
JOBdesc CHAR(2000) not null --为了效果明显一点,可以设置长点
)
INSERT Compression_tb2(departid ,JOBdesc)
VALUES(CAST(RAND()*20 as int)
, REPLICATE('a',50))
GO 100000
/*
建立聚集索引后再查看占用空间
*/
create clustered index id_Compression_tb2_1 ON Compression_tb2(ID)
EXEC sp_spaceused Compression_tb2
/*
name rows reserved data index_size unused
--------------- ------ ---------- --------- ----------- ---------
Compression_tb2 100000 200472 KB 200000 KB 376 KB 96 KB
聚集索引的基础上建立非聚集索引再查看占用空间
*/
create nonclustered index departid_Compression_tb2_1 ON Compression_tb2(departid)
EXEC sp_spaceused Compression_tb2
/*
name rows reserved data index_size unused
--------------- ------ ---------- ---------- ----------- ---------
Compression_tb2 100000 201712 KB 200000 KB 1520 KB 192 KB
*/
/*
添加压缩选项.
*/
ALTER INDEX id_Compression_tb2_1 ON Compression_tb2
REBUILD WITH (DATA_COMPRESSION=ROW)
EXEC sp_spaceused Compression_tb2
/*
name rows reserved data index_size unused
--------------- ------- -------- -------- ----------- ----------
Compression_tb2 100000 7864 KB 6432 KB 1192 KB 240 KB
*/
ALTER INDEX departid_Compression_tb2_1 ON Compression_tb2
REBUILD WITH (DATA_COMPRESSION=ROW)
EXEC sp_spaceused Compression_tb2
/*
name rows reserved data index_size unused
--------------- ------- --------- -------- ----------- ---------
Compression_tb2 100000 7656 KB 6432 KB 1016 KB 208 KB
*/
/*
以上的结果对比可以看到在聚集索引和非聚集索引的基础上建立行压缩可以节省很多的空间.
当然对于索引行压缩的建立同样可以在索引建立的时候指定压缩选项.
CREATE INDEX ...
WITH (DATA_COMPRESSION=ROW)
*/
/*
页压缩的实现
页压缩包含行压缩,以及前缀压缩和字典压缩,当使用页压缩时，将仅使用行压缩来压缩索引的非叶级别页,行压缩上面已经介绍.
页压缩对表、表分区、索引和索引分区都是类似的。以下针对表的页压缩的说明同样适用于所有对象类型的页压缩。以下示例压缩的是字符串，但对于其他数据类型而言，前缀压缩和字典压缩的原理都是相同的。
前缀压缩
对于要压缩的每一页，前缀压缩采用以下步骤：
1 对于每一列，将确定一个值，此值可用于减少每一列中的值的存储空间。
2 将创建表示每一列的前缀值的行，并将其存储在紧随页头之后的压缩信息(CI) 结构中。
3 列中重复的前缀值将由指向对应前缀的引用进行替换。如果行中的值与所选前缀值并不完全匹配，则
仍会指示存在部分匹配。
下图显示了前缀压缩之前表的一个示例页。
下图显示的是同一页在前缀压缩之后的样子。前缀移至页头，列值更改为指向前缀的引用
在第一行的第一列，值4b 指示为该行显示前缀的前四个字符(aaab) 和字符b。这样的话，结果值就是aaabb，这是原始值。
字典压缩
前缀压缩完成后，将应用字典压缩。字典压缩搜索页面上任意位置的重复值，然后将它们存储在CI 区域中。与前缀压缩不同，字典压缩不局限于一列。字典压缩可以替换页面上任意位置出现的重复值。
下图显示的是同一页在字典压缩之后的样子。
请注意，值4b 已由页的其他列引用。
进行页压缩时,当 创建具有页压缩的新表时，不会进行压缩。但是，表的元数据会指示应使用页压缩。当将数据添加到第一个数据页时，会对数据进行行压缩。因为此页未满，所以无 法通过页压缩获得任何益处。如果页已满，则添加下一行将引导页压缩操作。将查看整个页；计算每一列以进行前缀压缩，然后计算所有列以进行字典压缩。如果页 压缩已在页上为要添加的行创建了足够的空间，则添加该行，并对数据进行行压缩和页压缩。如果通过页压缩获得的空间减去CI 结构所需空间之后剩余的空间并不充足，则不会对此页使用页压缩。以后，行将添加到新页上，如果新页中也无法再容纳更多的行，则将再向表中添加一个新页。与第一页类似，新页最初也不进行页压缩。
页压缩的建立
*/
CREATE TABLE Compression_tb3
(id int not null identity(1,1),
departid int not null,
JOBdesc CHAR(2000) not null
)
with( DATA_COMPRESSION=PAGE) --页压缩选项
--去掉页压缩选项
ALTER TABLE Compression_tb3 REBUILD
WITH (DATA_COMPRESSION=NONE)
--建立聚集索引页压缩
CREATE CLUSTERED INDEX id_Compression_tb3_1 ON Compression_tb3(ID)
WITH (DATA_COMPRESSION=PAGE)
--建立非聚集索引页压缩
CREATE NONCLUSTERED INDEX departid_Compression_tb3_1 ON Compression_tb3(ID)
WITH (DATA_COMPRESSION=PAGE)
/*
建立压缩之后查询占用空间可以非常肯定,页级压缩肯定比行级压缩带来更大的好处.
同样作为代价,压缩通常会增加CPU的使用,所以在你决定使用压缩之前要在C盘空间
和CPU的开销之间做出权衡.
不想写了,分区级别的压缩配置下次写.
*/