SqlServer性能优化之Compression

执行SQL查询时，主要的几个瓶颈在于：CPU运算速度、内存缓存区大小、磁盘IO速度。而对于大数据量数据的查询，其瓶颈则一般集中于磁盘IO，以及内存缓存。那么为了提高SQL查询的效率，一方面我们需要考虑尽量减少查询设计的数据条目数——建立索引，设立分区；另一方面，我们也可以考虑切实减少数据表物理大小，从而减少IO大小。

在SQL Server 2008中，最新提供了一项功能“压缩（Compression）”，就是定位于减少数据表、索引物理大小。

 

设置压缩

在企业管理器中，在需要压缩的表或索引上右键选择Storage-》Manage Compression：

这里可以看到几点：

1. 下方列表里列出了该表所有的分区，也就是可以同一张表的不同分区应用不同的压缩策略。
2. 压缩方式（Compression Type）分为Row和Page两种。
   
   行级压缩（Row）：
   一方面减少了动态长度字段元数据的大小（varchar、varbinary等），比如之前存储字段实际长度需要2bytes，压缩后只需要3bits。
   另一方面也直接减少各字段存储内容的大小，比如存储数值1在一个int类型字段中，压缩后只占用了一个字节。
   
   页级压缩（Page）：能在各行间共享相同的数据，这里面包含两项技术：列前缀（Column Prefix）、页字典（Page Dictionary）。
   列前缀可以让拥有同样前缀的字段值拥有类似外键一样的结构来存储相同的前缀和各自的其余部分。比如一张存储了一个网站所有页面URL的表，URL字段存储的值分别是‘www.example.com/a.html’，‘www.example.com/b.html’，‘www.example.com/c.html’，‘www.example.com/d.html’。则压缩后，它们同样的前缀‘www.example.com/’会被提取出来，而其余部分会被类似如下的形式存储‘1a.html’，‘1b.html’，‘1c.html’，‘1d.html’。
   页字典则可以将在应用列前缀基础上的其余部分再次聚合存储，比如同样是一张存储了一个网站所有页面URL的表，假设有在表里里有多条URL字段的值相同，比如‘1a.html’，‘1b.html’，‘1c.html’，‘1b.html’，‘1a.html’，‘1a.html’，则通过页字典技术压缩后，实际存储在字段中的值会进一步减少为‘2’，‘3’，‘1c.html’（没有重复的字段值不会被压缩），‘3’，‘2’，‘2’。
3. 点击“Calculate”后，会计算出表当前占用的空间大小，以及压缩需要的空间大小。注意这里与一般预想的不同，如果要对一张预存有数据但尚未压缩的表进行压缩，首先需要的是额外的空间大小。

执行压缩

设置好之后，就可以选择是生成脚本还是立即执行，一般压缩的执行时间受表原有数据多少以及选择压缩方式的影响。笔者对一张有上千万条记录的表做页级压缩，耗时在10分钟左右。

压缩完成之后查看数据库大小，会发现数据库的大小变大了！这也和在设置阶段计算出来的额外空间相关。但实际上这里大部分空间是预占的空间，并没有实际数据。如果需要节省磁盘空间，需要进一步执行收缩（Shrink）操作。

与Compression不同，Shrink用来释放数据库占据的没有利用的空间，一般用来对无用的日志文件收缩（如果操作频繁，日志文件很有可能大于数据库实际数据的大小）。这里我们对数据库文件（mdf）做Shrink操作，完成之后再看数据库的大小，果然减少了很多。笔者做压缩、Shrink之后，一般都能将数据库的大小减为原来的1/3~1/2左右。当然，具体压缩比率取决于压缩方式、压缩表的字段特点、压缩表占整个数据库数据的比重等。

 

注意事项

 

既然对表行了压缩，那么在执行查询时必然会有解压缩的过程。而这一过程会占用CPU时间，也就是我们在通过压缩减少了磁盘占用空间以及IO时间的同时，增大了CPU的消耗。所以在压缩前需要考虑清楚查询的瓶颈到底是磁盘IO还是内存还是CPU。而且如果表应用了压缩，类似建立索引，对于增删改等操作也会有一定的影响。所以同样要考虑应用在表上的操作到底以哪种为主。

 

各页面的压缩是独立进行的，页字典和列前缀也分别存储于各页内。而且压缩仅在数据页快满的时候进行，因为一个页的大小是固定的，压缩半页不会有性能上的提升。

 

数据库备份中也有Compression的选项，但这利用的是系统的文件压缩技术，而且只能应用于整个数据库上。

 

容易被忽略的是，索引也能被压缩，而且和表压缩独立，同样也会提升所有应用到索引的查询的性能。

 

在Shrink阶段，可能会造成大量的索引碎片，所以可以在Shrink完成之后重建或者重组织索引，但同时，这些操作也会造成数据库的体积变大……也就是，最小的数据库体积和最小碎片比率的索引是鱼与熊掌，不可兼得