数据压缩 : 简要
1. 决定压缩哪些对象
通过sp_estimate_data_compression_savings 评估在ROW和PAGE压缩时分别节省的空间量。
表包含如下数据模式时，会有较好的压缩效果：
数字类型的列和固定长度的字符类型数据，但两者的大多数值都不会用到此类型的所有字节。如INT列的值大多数少于1000.
允许为NULL的列有很多NULL值
列值中有很多一样的值或者相同的前缀。
表包含如下数据模式时，压缩效果较差：
数字类型的列和固定长度的字符类型数据，但是两者的大多数值都会用尽此类型的所有字节。
非常少量的重复值
重复值不具有相同的前缀
数据存储在行外
FILESTREAM数据
2. 评估应用负载模式
被压缩的页在磁盘和内存都是压缩的。下面两种情况下会被解压缩(不是整页解压缩，只解压缩相关的数据)：
因为查询中的filtering, sorting, joining操作而被读取
被应用程序更新
解压缩会消耗CPU，但是数据压缩会减少物理IO和逻辑IO，同时会提高缓存效率。对于数据扫描操作，减少的IO量非常可观。对于单个的查找操作，减少的IO量较少。
行压缩导致的CPU开销通常不会超过10%。如果当前的系统资源充足，增加10%CPU毫无压力的话，建议所有的表都启用行压缩。
页压缩比行压缩的CPU开销高一些，所以确定是否使用页压缩会困难一些。可以通过一些简单的准则来帮助我们判断：
从那些不常用的表和索引开始
如果系统没有足够的CPU余量，不要使用页压缩
因为 filtering, joins, aggregates和sorting操作使用解压缩后的数据，所以数据压缩对这类查询没有太多帮助。如果工作负载主要由非常复杂的查询(多表JOIN，复杂聚合)组成，页压缩不会提高性能，最主要是节省存储空间。
大型数据仓库系统中，扫描性能是其重点，同时存储设备的成本较高，在CPU性能允许下，建议对所有表使用页压缩。
可以通过两个更细的度量值来帮我们评估使用何种数据压缩方式：
U:特定对象(表、索引或者分区)的更新操作占所有操作的百分比。越低越适合页压缩。
S:特定对象(表、索引或者分区)的扫描操作占所有操作的百分比。越高越适合页压缩。
通过如下脚本查询数据库所有对象的U:
SELECT o.name AS [Table_Name], x.name AS [Index_Name],
i.partition_number AS [Partition],
i.index_id AS [Index_ID], x.type_desc AS [Index_Type],
i.leaf_update_count * 100.0 /
(i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + i.leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count
) AS [Percent_Update]
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i
JOIN sys.objects o ON o.object_id = i.object_id
JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id
WHERE (i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count) != 0
AND objectproperty(i.object_id,'IsUserTable') = 1
ORDER BY [Percent_Update] ASC通过如下脚本查询数据库所有对象的S:
SELECT o.name AS [Table_Name], x.name AS [Index_Name],
i.partition_number AS [Partition],
i.index_id AS [Index_ID], x.type_desc AS [Index_Type],
i.range_scan_count * 100.0 /
(i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + i.leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count
) AS [Percent_Scan]
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i
JOIN sys.objects o ON o.object_id = i.object_id
JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id
WHERE (i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count) != 0
AND objectproperty(i.object_id,'IsUserTable') = 1
ORDER BY [Percent_Scan] DESC这两个查询用到了DMV sys.dm_db_index_operational_stats。DMV只是记录上次SQL Server实例启动以来的积累值，所以在实际应用中要选择一个合适的时间来查询。
通常U<20%和S>75%会是比较合理的考虑启用压缩的出发点，但是对于只插入有序数据的流水表，页压缩会比较合适(即使S值很低)。
3. 评估资源需求
使用ALTER TABLE… REBUILD和ALTER INDEX … REBUILD对表和索引启用压缩，其它原理和重建索引是一样的。通常需要的资源包括空间、CPU、IO、空间需求
在压缩过程中，已压缩的表和未压缩表是并存的，只有完成压缩后，未压缩的表才会被删除并释放空间。如果Rebuild是ONLINE的话，则还有Mapping Index需要额外的空间。
事务的空间需求由压缩方式是否是ONLINE(ON or OFF)和数据库的恢复模式决定。
当SORT_IN_TEMPDB=ON时(推荐为ON)，为了实现并发DML操作，会在tempdb中Mapping index的内部结构来映射旧书签和新书签的关系。对于版本化存储的，tempdb的使用量由并发DML操作所涉及的数据量和事务时间长度决定。
通常行压缩操作的CPU开销是重建一个索引的1.5倍左右，页压缩是它的2到5倍。ONLINE模式还需要额外的CPU资源。Rebuild和Compress可以被并行化的，所以还要结合MAXDOP一起考虑。
并行化的注意事项：
SQL Server在Create/Rebuild/Compress一个索引时，使用索引首列(最左列)的统计信息确定并行操作在多个CPU间的分布。所以当索引首列的筛选度不高，或者数据倾斜严重使得首列的值很少时，并行化对性能提升的帮助就很少。
使用ONLINE=ON方式Compress/Rebuild堆表是单线程操作。但是压缩和重建前的表扫描操作是并行多线程的。
下表总结对比了压缩和重建一个聚集索的资源开销：
X = 压缩或者重建前的页数量
P = 压缩后的页数量(P < X)
Y = 新增和被更新的页数据 (只适用于ONLINE=ON时并发应用所做修改)
M = Mapping index的大小 (基于<EMPDB Capacity Planning>白皮书的预估值)
C = 重建聚集索引所需CPU时间
在判断何时和怎么压缩数据时，下面是一些参考点：
Online vs. Offline:
Offline更快，需要的资源也更少，但是压缩操作过程中会锁表。Online自身也会有一些限制。
一次压缩一个table/index/partition vs. 多个操作并发:
这个由当前资源的余量决定，如果资源很充足，多个压缩操作并行也可以接受的，否则最好一次一个。
表压缩操作的顺序:
从小表开始，小表压缩需要的资源少，完成快。完成后释放的资源也利于后续表的压缩操作。
SORT_IN_TEMPDB= ON or OFF:
推荐ON。这样可以利用tempdb来存放和完成Mapping index操作，从而也减少用户数据的空间需求。
压缩操作副作用:
压缩操作包括重建操作，所以会移除表或索引上的碎片。
压缩堆表时，如果有非聚集索引存在，则：当ONLINE=OFF，索引重建是串行操作，ONLINE=ON，索引重建是并操作。
4. 维护压缩数据
新插入数据的压缩方式
*通过以页压缩方式重建堆表来将行级压缩页转换为页级压缩。
**页压缩中，并不是所有的页都是页压缩的，只有当页压缩节省的空间量超过一个内存阈值时才是。
更新和删除已压缩的行
所有对行压缩表/分区数据行的更新会保持行压缩格式。并不是每次对页压缩表/分区的数据行的更新都会导致列前缀和页字典被重新计算，只有当在上的更新数量超过某个内部阈值时，才会重新计算。
辅助数据结构的行为
Table compression | Transaction log | Mapping index for rebuilding the clustered index | Sort pages for queries | Version store (with SI or RCSI isolation level) |
ROW | ROW | NONE | NONE | ROW |
PAGE | ROW | NONE | NONE | ROW |
页压缩索引的非页级页是行压缩的
索引的非叶级相对较小，就算应用页压缩，节省的空间也不会很显著。对非叶级页的访问会很频繁，使用行级压缩减少每次访问时解压缩成本。
5. 回收数据压缩释放的空闲空间
不回收，留着给将在的数据增长使用。这个不适合分区表(每个分区对应一人不同的文件级)的只读分区，压缩旧的只读分区不会增长，压缩可以节省大量空间。
DBCC SHRINKFILE (或者DBCC SHRINKDATABASE) 。这个操作会带来大量碎片，同时它是一个单线程操作，可能会耗时较长。
如果压缩了一个文件组上的所有表，则新建一个文件组，然后在压缩时将表和索引移动到新的文件组。数据移动可以通过Create/Recreate聚集索引的方式实现(如，WITH (DATA_COMPRESSION=PAGE, DROP_EXISTING=ON, SORT_IN_TEMPDB=ON) ON [FG_NEW] )。移动完数据之后，删除原来的文件组即可。但是这种方式不能移动LOB_DATA数据到新文件组。
在新文件组上创建压缩的表，然后将数据导入到这些表。
6. BULK INSERT 和数据压缩
BULK INSERT WITH (TABLOCK)导入数据到已压缩的表，速度最快。很明显，这会锁表。
压缩数据时，BULK INSERT和创建聚集索引的顺序考虑：
序号 | 方式 | 比较 |
1 | BULK INSERT导入数据到未压缩的堆表，然后再 CREATE CLUSTERED INDEX WITH (DATA_COMPRESSION = PAGE). | 所需时间：1<2<3 |
2 | BULK INSERT导入数据到页压缩的堆表，然后再 CREATE CLUSTERED INDEX | 所需空间：1>2>3 |
3 | BULK INSERT导入数据到页压缩的聚集索引 |
7. 数据压缩和分区表维护
1. Switch操作要求目标分区(或目标表)与源分区的压缩方式相同。
2. Split后的分区继承原分区的压缩方式。
3. Merger操作，被删除的分区称为源分区,接收数据的分区称为目标分区：
目标分区的压缩方式 | 数据合并到目标分区的方式 |
NONE | 在Merger期间，数据会被解压缩到目标分区 |
ROW | 在Merger期间，数据会被转换成行压缩格式 |
PAGE | -堆表: 在Merger期间，数据会被转换成行压缩格式 - 聚集索引: 在Merger期间，数据会被转换成页压缩格式 |
PS：分区表Merger操作规则
1. LEFT RANGE时，删除边界值所在的分区，保留"左"侧的分区，并向其移动数据
2. RIGHT RANGE时，删除边界值所在的分区，保留"右"分区，并向其移动数据
8. 数据压缩和透明数据加密(TDE)
TDE是当数据页写入磁盘时加密，从磁盘中读出页放入到内存时解密。而数据压缩/解压缩操作是对内存中的页执行的，所以数据压缩/解压缩总是用到解密后的页。因此两者之前的相互影响很小。
总结
1. 本文来基于白皮书<Data Compression: Strategy, Capacity Planning and Best Practices>的简译和总结。此白皮书是基于SQL Server 2008的。
2. 数据压缩是一个被低估SQL Server技术，个人认为很有必要将之做为标准化最佳实践之一。
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/54.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
