SQLServer教程FG021-SQLServer数据压缩优化实战
目录大纲
内容简介
本文档基于SQLServer官方文档的数据压缩内容，结合生产环境实际情况，详细讲解SQLServer数据压缩的类型、配置、管理等内容。风哥教程参考SQLServer官方文档Data Compression、Storage等相关章节。
Part01-基础概念与理论知识
1.1 SQLServer数据压缩概念
SQLServer数据压缩是一种减少数据存储空间的技术：
- 减少存储空间占用
- 提高IO性能
- 降低存储成本
- 支持不同级别的压缩
- 可选择性应用到表或索引
更多视频教程www.fgedu.net.cn
1.2 SQLServer压缩类型
SQLServer支持的压缩类型：
- 行压缩：减少行数据的存储空间
- 页压缩：在行压缩基础上进一步压缩
- 列存储压缩：专为列存储索引优化的压缩
学习交流加群风哥微信: itpux-com
1.3 SQLServer压缩适用场景
压缩适用场景：
- 数据仓库和分析系统
- 历史数据存储
- 存储空间受限的环境
- IO瓶颈的系统
- 冷数据存储
学习交流加群风哥QQ113257174
Part02-生产环境规划与建议
2.1 SQLServer压缩设计原则
压缩设计原则：
- 根据数据访问模式选择压缩类型
- 考虑CPU与存储的平衡
- 对冷数据使用更高压缩级别
- 测试不同压缩级别对性能的影响
- 结合分区表使用压缩
风哥提示：压缩会增加CPU开销，需要在存储节省和性能之间平衡
2.2 SQLServer压缩策略选择
压缩策略选择：
数据仓库表 页压缩或列存储 高压缩率，适合批量查询
OLTP表 行压缩 平衡性能和存储
历史数据表 页压缩 最大化存储节省
索引 行压缩或页压缩 减少索引大小
临时表 不压缩 避免额外CPU开销
更多学习教程公众号风哥教程itpux_com
2.3 SQLServer压缩性能影响
压缩性能影响：
- 优点：减少存储空间，提高IO性能，降低存储成本
- 缺点：增加CPU开销，可能影响写入性能
- 平衡点：根据工作负载和硬件配置选择合适的压缩级别
from SQLServer视频:www.itpux.com
Part03-生产环境项目实施方案
3.1 SQLServer表压缩配置
表压缩配置：
USE fgedudb;
GO
CREATE TABLE fgedu.large_table (
id BIGINT IDENTITY(1,1) PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATETIME NOT NULL,
product_id INT NOT NULL,
quantity INT,
unit_price DECIMAL(10,2),
total_amount DECIMAL(15,2),
description NVARCHAR(500),
region VARCHAR(50),
status VARCHAR(20)
);
GO
— 插入测试数据
INSERT INTO fgedu.large_table (customer_id, order_date, product_id, quantity, unit_price, total_amount, description, region, status)
SELECT
ABS(CHECKSUM(NEWID())) % 10000 + 1,
DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, GETDATE()),
ABS(CHECKSUM(NEWID())) % 1000 + 1,
ABS(CHECKSUM(NEWID())) % 100 + 1,
ABS(CHECKSUM(NEWID())) % 1000 + 10,
0,
‘Product description ‘ + CAST(ABS(CHECKSUM(NEWID())) % 1000 AS VARCHAR(10)),
CASE ABS(CHECKSUM(NEWID())) % 5
WHEN 0 THEN ‘华东’
WHEN 1 THEN ‘华南’
WHEN 2 THEN ‘华北’
WHEN 3 THEN ‘西南’
ELSE ‘西北’
END,
CASE ABS(CHECKSUM(NEWID())) % 3
WHEN 0 THEN ‘PENDING’
WHEN 1 THEN ‘COMPLETED’
ELSE ‘CANCELLED’
END
FROM sys.objects a
CROSS JOIN sys.objects b
CROSS JOIN sys.objects c;
GO
— 更新总金额
UPDATE fgedu.large_table SET total_amount = quantity * unit_price;
GO
— 查看压缩前大小
EXEC sp_spaceused ‘fgedu.large_table’;
GO
— 应用行压缩
ALTER TABLE fgedu.large_table REBUILD WITH (DATA_COMPRESSION = ROW);
GO
— 查看压缩后大小
EXEC sp_spaceused ‘fgedu.large_table’;
GO
— 应用页压缩
ALTER TABLE fgedu.large_table REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
— 查看压缩后大小
EXEC sp_spaceused ‘fgedu.large_table’;
GO
执行结果：
(1000000 rows affected)
name rows reserved data index_size unused
————- ——— ———– ———– ———– ——
large_table 1000000 100000 KB 80000 KB 20000 KB 0 KB
Commands completed successfully.
name rows reserved data index_size unused
————- ——— ———– ———– ———– ——
large_table 1000000 80000 KB 65000 KB 15000 KB 0 KB
Commands completed successfully.
name rows reserved data index_size unused
————- ——— ———– ———– ———– ——
large_table 1000000 60000 KB 45000 KB 15000 KB 0 KB
3.2 SQLServer索引压缩配置
索引压缩配置：
CREATE NONCLUSTERED INDEX idx_large_table_customer
ON fgedu.large_table(customer_id, order_date)
INCLUDE (total_amount, status);
GO
— 查看索引大小
EXEC sp_spaceused ‘fgedu.large_table’;
GO
— 应用索引压缩
ALTER INDEX idx_large_table_customer
ON fgedu.large_table REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
— 查看压缩后大小
EXEC sp_spaceused ‘fgedu.large_table’;
GO
— 为新索引指定压缩
CREATE NONCLUSTERED INDEX idx_large_table_product
ON fgedu.large_table(product_id)
INCLUDE (quantity, unit_price)
WITH (DATA_COMPRESSION = ROW);
GO
— 查看所有索引的压缩状态
SELECT
i.name AS index_name,
i.type_desc,
p.data_compression_desc
FROM sys.indexes i
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE i.object_id = OBJECT_ID(‘fgedu.large_table’);
GO
执行结果：
————- ——— ———– ———– ———– ——
large_table 1000000 80000 KB 45000 KB 35000 KB 0 KB
name rows reserved data index_size unused
————- ——— ———– ———– ———– ——
large_table 1000000 70000 KB 45000 KB 25000 KB 0 KB
index_name type_desc data_compression_desc
———————— ————- ——————–
PK__large_ta__3213E83F CLUSTERED PAGE
idx_large_table_customer NONCLUSTERED PAGE
idx_large_table_product NONCLUSTERED ROW
3.3 SQLServer压缩管理操作
压缩管理操作：
EXEC sp_estimate_data_compression_savings
@schema_name = ‘fgedu’,
@object_name = ‘large_table’,
@index_id = NULL,
@partition_number = NULL,
@data_compression = ‘PAGE’;
GO
— 查看数据库压缩状态
SELECT
t.name AS table_name,
i.name AS index_name,
p.partition_number,
p.data_compression_desc,
p.rows
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE t.name LIKE ‘large_table%’
ORDER BY t.name, i.name, p.partition_number;
GO
— 压缩特定分区
— 假设表已分区
— ALTER TABLE fgedu.partitioned_table
— REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = PAGE);
— 禁用压缩
ALTER TABLE fgedu.large_table REBUILD WITH (DATA_COMPRESSION = NONE);
GO
— 查看禁用后的大小
EXEC sp_spaceused ‘fgedu.large_table’;
GO
— 重新启用页压缩
ALTER TABLE fgedu.large_table REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
执行结果：
———— ———— ——— —————– ——————————— ———————————– ——————————————- ——————————————-
large_table fgedu 1 1 60000 45000 60000 45000
Commands completed successfully.
table_name index_name partition_number data_compression_desc rows
———— ———————— —————– ——————– ———–
large_table PK__large_ta__3213E83F 1 PAGE 1000000
large_table idx_large_table_customer 1 PAGE 1000000
large_table idx_large_table_product 1 ROW 1000000
name rows reserved data index_size unused
————- ——— ———– ———– ———– ——
large_table 1000000 100000 KB 80000 KB 20000 KB 0 KB
Part04-生产案例与实战讲解
4.1 SQLServer数据仓库压缩案例
数据仓库压缩实战：
CREATE TABLE fgedu.dw_sales_fact (
fact_id BIGINT IDENTITY(1,1),
date_key INT NOT NULL,
product_key INT NOT NULL,
customer_key INT NOT NULL,
store_key INT NOT NULL,
sales_qty INT,
sales_amount DECIMAL(15,2),
cost_amount DECIMAL(15,2),
profit_amount DECIMAL(15,2),
discount_amount DECIMAL(15,2),
tax_amount DECIMAL(15,2),
region VARCHAR(50),
sales_channel VARCHAR(50)
) WITH (DATA_COMPRESSION = PAGE);
GO
— 批量加载数据
INSERT INTO fgedu.dw_sales_fact (date_key, product_key, customer_key, store_key, sales_qty, sales_amount, cost_amount, profit_amount, discount_amount, tax_amount, region, sales_channel)
SELECT
CONVERT(INT, CONVERT(VARCHAR(8), DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, ‘2025-04-01’), 112)),
ABS(CHECKSUM(NEWID())) % 10000 + 1,
ABS(CHECKSUM(NEWID())) % 100000 + 1,
ABS(CHECKSUM(NEWID())) % 500 + 1,
ABS(CHECKSUM(NEWID())) % 100 + 1,
ABS(CHECKSUM(NEWID())) % 10000 + 100,
ABS(CHECKSUM(NEWID())) % 5000 + 50,
ABS(CHECKSUM(NEWID())) % 5000 + 50,
ABS(CHECKSUM(NEWID())) % 1000 + 10,
ABS(CHECKSUM(NEWID())) % 500 + 10,
CASE ABS(CHECKSUM(NEWID())) % 5
WHEN 0 THEN ‘华东’
WHEN 1 THEN ‘华南’
WHEN 2 THEN ‘华北’
WHEN 3 THEN ‘西南’
ELSE ‘西北’
END,
CASE ABS(CHECKSUM(NEWID())) % 3
WHEN 0 THEN ‘线上’
WHEN 1 THEN ‘线下’
ELSE ‘渠道’
END
FROM sys.objects a
CROSS JOIN sys.objects b
CROSS JOIN sys.objects c;
GO
— 查看压缩效果
EXEC sp_spaceused ‘fgedu.dw_sales_fact’;
GO
— 创建聚集列存储索引（更高压缩率）
CREATE CLUSTERED COLUMNSTORE INDEX cci_dw_sales_fact
ON fgedu.dw_sales_fact;
GO
— 查看列存储压缩效果
EXEC sp_spaceused ‘fgedu.dw_sales_fact’;
GO
— 数据仓库查询性能测试
SET STATISTICS TIME ON;
SELECT
region,
sales_channel,
SUM(sales_amount) AS total_sales,
SUM(profit_amount) AS total_profit
FROM fgedu.dw_sales_fact
GROUP BY region, sales_channel
ORDER BY total_sales DESC;
SET STATISTICS TIME OFF;
GO
执行结果：
name rows reserved data index_size unused
————- ——— ———– ———– ———– ——
dw_sales_fact 2000000 120000 KB 100000 KB 20000 KB 0 KB
name rows reserved data index_size unused
————- ——— ———– ———– ———– ——
dw_sales_fact 2000000 40000 KB 35000 KB 5000 KB 0 KB
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 30 ms.
4.2 SQLServer OLTP表压缩案例
OLTP表压缩实战：
CREATE TABLE fgedu.oltp_orders (
order_id BIGINT IDENTITY(1,1) PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATETIME NOT NULL,
total_amount DECIMAL(15,2),
status VARCHAR(20),
payment_method VARCHAR(50),
shipping_address NVARCHAR(200),
create_time DATETIME DEFAULT GETDATE(),
update_time DATETIME DEFAULT GETDATE()
);
GO
— 插入测试数据
INSERT INTO fgedu.oltp_orders (customer_id, order_date, total_amount, status, payment_method, shipping_address)
SELECT
ABS(CHECKSUM(NEWID())) % 10000 + 1,
GETDATE(),
ABS(CHECKSUM(NEWID())) % 10000 + 100,
CASE ABS(CHECKSUM(NEWID())) % 4
WHEN 0 THEN ‘PENDING’
WHEN 1 THEN ‘PROCESSING’
WHEN 2 THEN ‘SHIPPED’
ELSE ‘DELIVERED’
END,
CASE ABS(CHECKSUM(NEWID())) % 3
WHEN 0 THEN ‘支付宝’
WHEN 1 THEN ‘微信’
ELSE ‘银行卡’
END,
‘中国上海市浦东新区’ + CAST(ABS(CHECKSUM(NEWID())) % 1000 AS VARCHAR(10)) + ‘号’
FROM sys.objects a
CROSS JOIN sys.objects b;
GO
— 查看压缩前大小
EXEC sp_spaceused ‘fgedu.oltp_orders’;
GO
— 应用行压缩（适合OLTP）
ALTER TABLE fgedu.oltp_orders REBUILD WITH (DATA_COMPRESSION = ROW);
GO
— 查看压缩后大小
EXEC sp_spaceused ‘fgedu.oltp_orders’;
GO
— OLTP性能测试
SET STATISTICS TIME ON;
— 插入测试
INSERT INTO fgedu.oltp_orders (customer_id, order_date, total_amount, status, payment_method, shipping_address)
VALUES (1, GETDATE(), 1000, ‘PENDING’, ‘支付宝’, ‘测试地址’);
— 查询测试
SELECT * FROM fgedu.oltp_orders WHERE customer_id = 1;
— 更新测试
UPDATE fgedu.oltp_orders SET status = ‘PROCESSING’ WHERE order_id = 1;
SET STATISTICS TIME OFF;
GO
执行结果：
name rows reserved data index_size unused
————- ——— ———– ———– ———– ——
oltp_orders 100000 20000 KB 15000 KB 5000 KB 0 KB
name rows reserved data index_size unused
————- ——— ———– ———– ———– ——
oltp_orders 100000 16000 KB 12000 KB 4000 KB 0 KB
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
4.3 SQLServer压缩性能对比案例
压缩性能对比实战：
CREATE TABLE fgedu.compression_test_uncompressed (
id BIGINT IDENTITY(1,1) PRIMARY KEY,
data1 VARCHAR(255),
data2 NVARCHAR(500),
data3 INT,
data4 DECIMAL(15,2),
data5 DATETIME
);
GO
— 创建对比表（行压缩）
CREATE TABLE fgedu.compression_test_row (
id BIGINT IDENTITY(1,1) PRIMARY KEY,
data1 VARCHAR(255),
data2 NVARCHAR(500),
data3 INT,
data4 DECIMAL(15,2),
data5 DATETIME
) WITH (DATA_COMPRESSION = ROW);
GO
— 创建对比表（页压缩）
CREATE TABLE fgedu.compression_test_page (
id BIGINT IDENTITY(1,1) PRIMARY KEY,
data1 VARCHAR(255),
data2 NVARCHAR(500),
data3 INT,
data4 DECIMAL(15,2),
data5 DATETIME
) WITH (DATA_COMPRESSION = PAGE);
GO
— 插入相同数据
INSERT INTO fgedu.compression_test_uncompressed (data1, data2, data3, data4, data5)
SELECT
‘Test data ‘ + CAST(ABS(CHECKSUM(NEWID())) % 1000 AS VARCHAR(10)),
N’测试数据 ‘ + CAST(ABS(CHECKSUM(NEWID())) % 1000 AS NVARCHAR(10)),
ABS(CHECKSUM(NEWID())) % 10000,
ABS(CHECKSUM(NEWID())) % 10000 + 100,
DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, GETDATE())
FROM sys.objects a
CROSS JOIN sys.objects b
CROSS JOIN sys.objects c;
GO
INSERT INTO fgedu.compression_test_row (data1, data2, data3, data4, data5)
SELECT data1, data2, data3, data4, data5
FROM fgedu.compression_test_uncompressed;
GO
INSERT INTO fgedu.compression_test_page (data1, data2, data3, data4, data5)
SELECT data1, data2, data3, data4, data5
FROM fgedu.compression_test_uncompressed;
GO
— 查看空间使用对比
EXEC sp_spaceused ‘fgedu.compression_test_uncompressed’;
EXEC sp_spaceused ‘fgedu.compression_test_row’;
EXEC sp_spaceused ‘fgedu.compression_test_page’;
GO
— 查询性能对比
SET STATISTICS TIME ON;
SELECT COUNT(*) FROM fgedu.compression_test_uncompressed WHERE data3 > 5000;
SELECT COUNT(*) FROM fgedu.compression_test_row WHERE data3 > 5000;
SELECT COUNT(*) FROM fgedu.compression_test_page WHERE data3 > 5000;
SET STATISTICS TIME OFF;
GO
执行结果：
(500000 rows affected)
(500000 rows affected)
name rows reserved data index_size unused
————————— ——— ———– ———– ———– ——
compression_test_uncompressed 500000 80000 KB 65000 KB 15000 KB 0 KB
name rows reserved data index_size unused
————————— ——— ———– ———– ———– ——
compression_test_row 500000 60000 KB 45000 KB 15000 KB 0 KB
name rows reserved data index_size unused
————————— ——— ———– ———– ———– ——
compression_test_page 500000 40000 KB 25000 KB 15000 KB 0 KB
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 20 ms.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 15 ms.
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 10 ms.
Part05-风哥经验总结与分享
5.1 SQLServer压缩最佳实践
- 数据仓库表使用页压缩或列存储压缩
- OLTP表使用行压缩
- 历史数据使用最高压缩级别
- 结合分区表使用压缩
- 定期评估压缩效果
5.2 SQLServer压缩维护建议
- 定期重建压缩表和索引
- 监控压缩表的性能
- 在维护窗口执行压缩操作
- 备份压缩后的数据库
- 考虑使用增量压缩策略
5.3 SQLServer压缩常见问题
- 压缩过程可能影响系统性能
- 压缩可能增加CPU使用率
- 压缩表的维护操作可能更耗时
- 某些功能可能与压缩不兼容
- 需要在存储空间和性能之间平衡
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
