MSSQL数据库的优化：学习如何压缩

1. MSSQL数据库压缩的概念

MSSQL数据库压缩是一种将数据库表、索引和视图尺寸缩小的方法。通过压缩数据库，可以减少磁盘空间的使用和I/O访问时间，从而提高数据库的性能和可用性。压缩后的数据库可以使数据读取速度更快，从而带来更快的查询响应时间和更好的用户体验。

1.1 压缩类型

在MSSQL数据库中，有两种压缩类型：行级压缩和页级压缩。

行级压缩

行级压缩是一种将数据行中的重复项删除的方法。使用行级压缩，可以降低存储和查询此表时所需的内存和I/O。行级压缩通常适用于具有大量重复项的表。MSSQL数据库支持RLE、PAGE和封口算法来实现行级压缩。

页级压缩

页级压缩可以压缩整个数据页，包括表，索引和视图数据。该方法可减少存储空间，从而提高读取和写入性能。可用的页级数据压缩算法包括ROW级压缩（即压缩单独的行）和PAGE级压缩（即压缩整个数据页）。

2. 如何压缩MSSQL数据库

下面介绍如何在MSSQL中压缩数据库。

2.1 行级压缩

要使用行级压缩，请使用MSSQL的以下步骤：

通过以下代码动态创建表。

CREATE TABLE test_table(
    id int  primary key,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    email varchar(50) not null,
    age int,
    CONSTRAINT check_age CHECK (age >= 18 AND age <= 120)
    ) WITH (DATA_COMPRESSION = PAGE);

此代码创建一个名为test_table的表。此表包括id、first_name、last_name、email和age列。约束check_age检查年龄是否介于18到120之间。

将压缩级别设置为表。

ALTER TABLE test_table REBUILD WITH (DATA_COMPRESSION = PAGE);

此代码将数据页设置为行级PAGE压缩。

删除现有表并重新创建它并超过压缩级别。

为了演示页面压缩的效果，我们需要插入大量数据。

USE master;
GO
IF EXISTS (SELECT * FROM sysdatabases WHERE name='demo_db')
DROP DATABASE demo_db;
GO
CREATE DATABASE demo_db;
GO
USE demo_db;
GO
CREATE TABLE test_table (
    id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    age INT,
    CONSTRAINT check_age CHECK (age >= 18 AND age <= 120)
) WITH (DATA_COMPRESSION = PAGE);
INSERT INTO test_table VALUES 
    (1,N'david',N'lee',N'dlee1@yahoo.com', 28),
    (2,N'oliver', N'miller',N'omiller2@hotmail.com',42),
    ..............................
    (5000,N'james', N'smith',N'jsmith1@verizon.net',55);
GO

此代码创建一个名为“ test_table”的表，然后向其中插入5000条记录。

使用数据压缩完整性级别，查看表大小的变化。

USE demo_db;
GO
EXEC sp_table_info 'test_table'
GO

此代码显示了两个结果集，第一个结果集显示了未应用数据页压缩前的空间状态和第二个结果集显示了应用数据页压缩后的空间状态。可以看到由于应用页级压缩，压缩后的表大小明显减少了。

2.2 页级压缩

要使用页级压缩，请按以下步骤操作：

为表启用页压缩。

ALTER TABLE [schema_name].[table_name] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);

此代码将分区表的分区设置为页级压缩。

在现有表上定义列存储。

列存储是一种使用列的组合来存储和压缩数据的方法。

ALTER TABLE [schema_name].[table_name] ADD COLUMN_STORE COMPRESSED 
WITH (columnstore_compression_delay = 0);

此代码将列存储设置为压缩并启用列存储压缩。

使用数据压缩完整性级别

USE demo_db;
GO
EXEC sp_table_info '[schema_name].[table_name]'

此代码显示了未应用数据页压缩前的空间状态和应用数据页压缩后的空间状态。通过列存储，压缩后的表大小明显减少了。

3. 总结

通过压缩MSSQL数据库，可以大大减少磁盘空间的使用以及I/O访问时间，从而提升性能。行级压缩和页级压缩是两种常用的压缩方式，用户可以根据需求选择不同的压缩方式。在实际工作中，需要根据实际情况对表进行压缩，并根据压缩效果进行性能测试和调优。