达梦数据库——压缩

DM8压缩表

0、结论

行表（普通表）不支持压缩。但是语法支持。建表之后，查询到的占用空间会比普通表小一半。
- 经过测试，装10万数据（两个字段），压缩的、未压缩，占用空间一样大。
列表（huge表）支持压缩。可以压缩表（就是压缩所有列），也可以选择压缩列。但是建表的时候就要设置，否则建好表之后修改不成压缩表或压缩列。
- 经过测试，装10万数据（两个字段），压缩级别9（最高），压缩所有字段。压缩的、未压缩的相差600MB左右。

-- 查询dm数据库信息（包含版本）
select * from v$instance;
-- 查询dm数据库版本
select * from v$version;
-- 查询版本时间
select id_code;

1、需求：

数据压缩
- 只有列式存储的表（huge表）可以压缩
- huge表新建好后（开始未设置压缩的）是不能修改为压缩表
其他对象压缩（索引、视图、函数……）
- 不支持其他对象压缩
可参考问答：https://eco.dameng.com/community/question/5d6b19e9d7a929644ab49de0fd64ba39

2、压缩表、压缩列

COMPRESS关键字

建表：普通表

（见官方文档：https://eco.dameng.com/document/dm/zh-cn/pm/definition-statement.html#3.5 管理表）

建表：huge表

（见官方文档：https://eco.dameng.com/document/dm/zh-cn/pm/definition-statement.html#3.5.1.3 定义 HUGE 表）

2.1、普通表（行式存储表）

语法支持，功能已经取消。
但是设置了压缩的表，初始占用空间会变小。但是放同样的数据，大小没有区别。

image"

建表语句

-- 压缩表
create table "CS_YT1"."TABLE_3"
(
	"COLUMN_1" CHAR(10) not null ,
	"COLUMN_2" CHAR(10),
	"COLUMN_3" CHAR(10),
	primary key("COLUMN_1")
)
storage(initial 1, next 1, minextents 1, fillfactor 0)
compress;

-- 压缩列
create table "CS_YT1"."TABLE_3"
(
	"COLUMN_1" CHAR(10) not null ,
	"COLUMN_2" CHAR(10),
	"COLUMN_3" CHAR(10),
	primary key("COLUMN_1")
)
storage(initial 1, next 1, minextents 1, fillfactor 0)
compress ("COLUMN_1","COLUMN_2","COLUMN_3");

测试建表，查看大小

2.2、HUGE表——列式存储表（大表）

huge表可见官方文档：https://eco.dameng.com/document/dm/zh-cn/pm/manage-column-tables#16.1 什么是列存储

压缩级别、压缩类型

1. <压缩级别> 指定列的压缩级别，有效值范围为：0~10，分别代表不同的压缩算法和压缩级别。有两种压缩算法：SNAPPY 和 ZIP。10 采用 SNAPPY 算法轻量级方式压缩。2~9 采用 ZIP 算法压缩，2~9 代表压缩级别，值越小表示压缩比越低、压缩速率越快；值越大表示压缩比越高、压缩速度越慢。0 和 1 为快捷使用，默认值为 0。0 等价于 LEVEL 2；1 等价于 LEVEL 9；

2. < 压缩类型 > 指定列压缩类型。FOR 'QUERY [LOW]'表示进行规则压缩；FOR 'QUERY HIGH'表示结合进行规则压缩与通用压缩结合，前者的压缩比一般在 1:1 至 1:3 之间，后者一般为 1:3 至 1:5 之间。规则压缩方式一般适用于具有一定的数据规则的数据的压缩，例如重复值较多等。若某列的类型为字符串类型且定义长度超过 48，则即使指定规则压缩也无效，实际只进行通用压缩；

huge表
- 不能：新增字段、修改字段（类型、长度、精度）
- 可以：修改字段名字、给字段加注释、给表加注释

建表语句

-- 1压缩列
CREATE HUGE TABLE "CS_YT1"."T_HUGE_COMPRESS_FIELD"
(
  "ID" VARCHAR2(50) not null ,
	"NAME" VARCHAR2(50),
	primary key("ID")
) 
STORAGE(STAT ASYNCHRONOUS EXCEPT(ID), WITH DELTA, SECTION(65536), FILESIZE(64), ON "HMAIN") 
  COMPRESS ("ID" LEVEL 3 FOR 'QUERY',"NAME" LEVEL 4 FOR 'QUERY LOW') LOG LAST ;

comment on table "CS_YT1"."T_HUGE_COMPRESS_FIELD" is '压缩列';

-- 2压缩表
CREATE HUGE TABLE "CS_YT1"."T_HUGE_COMPRESS"
(
  "ID" VARCHAR2(50) not null ,
	"NAME" VARCHAR2(50),
	primary key("ID")
) 
STORAGE(STAT ASYNCHRONOUS EXCEPT(ID), WITH DELTA, SECTION(65536), FILESIZE(64), ON "HMAIN") 
COMPRESS LEVEL 3 FOR 'QUERY' LOG LAST ;

comment on table "CS_YT1"."T_HUGE_COMPRESS" is '压缩表';

-- 3不压缩的huge表
CREATE HUGE TABLE "CS_YT1"."T_HUGE_NOCOMPRESS"
(
  "ID" VARCHAR2(50) not null ,
	"NAME" VARCHAR2(50),
	primary key("ID")
) 
STORAGE(STAT ASYNCHRONOUS EXCEPT(ID), WITH DELTA, SECTION(65536), FILESIZE(64), ON "HMAIN") ;

comment on table "CS_YT1"."T_HUGE_NOCOMPRESS" is '未压缩的表';

修改列为压缩列
- DM不支持
新增列为压缩列
- 原来未压缩的表、已压缩的表，都可以新增压缩列。
- 达梦版本低了不支持：DM Database Server 64 V8（1-1-172-21.03.05-135967-ENT Pack1）（我们现在用的，就不行）
- 可以去下个最新的dm试一下。

ALTER TABLE T1 ADD COLUMN COL_TEST VARCHAR(10) COMPRESS LEVEL 9;

修改表为压缩表（oracle支持，dm不支持）

ALTER TABLE SALES_HISTORY_COMP COMPRESS;

3、查询

查询表信息-用USER_TABLES

-- 查询表信息：(COMPRESSION 字段，是否被压缩):ENABLED被压缩；DISABLED未被压缩。
SELECT *
FROM DBA_TABLES
WHERE OWNER = 'CS_YT1'
--AND TABLE_NAME = 'TABLE_333333' OR TABLE_NAME = 'TABLE_1';

-- 查询表信息2 (快，推荐)
SELECT TABLE_NAME,TABLESPACE_NAME,COMPRESSION
FROM USER_TABLES

☆☆查询所有类型的表的空间占用☆☆

-- 查询表的实际占用大小、占用表空间大小（huge表都为0）
SELECT 
TABLE_USED_PAGES('CS_YT1','T_PT') * PAGE / 1024  AS "实际大小(KB)" ,
TABLE_USED_SPACE('CS_YT1','T_PT') * PAGE / 1024  AS "占用表空间大小(KB)"

3.1、普通表

3.1.1、查询表占用大小

可以使用通用查询

-- 查看表的占用大小(单表)1（模式，表名）
SELECT TABLE_USED_SPACE('CS_YT1', 'T_HUGE_COMPRESS') * PAGE() / 1024  "占用大小(KB)";
-- 查询表的占用大小(单表)2（模式，表名）
SELECT TABLE_USED_SPACE('CS_YT1', 'T_HUGE_COMPRESS') * PARA_VALUE / 1024 / 1024 "表占用(MB)"
  FROM V$DM_INI
 WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';
 
 -- 查询所有表所占的大小及所在空间（所有表）
SELECT 
A.OWNER AS "模式",
A.SEGMENT_NAME AS "表名",
A.BLOCKS*2 AS "对象大小(KB)",
A.BYTES/1024 AS "占用空间(KB)",
A.TABLESPACE_NAME AS "所属表空间",
B.COMMENTS AS "表注释" 
FROM DBA_SEGMENTS A,DBA_TAB_COMMENTS B 
WHERE 
A.OWNER=B.OWNER 
AND A.SEGMENT_NAME = B.TABLE_NAME 
AND A.OWNER='CS_YT1' 
ORDER BY SEGMENT_NAME ASC;

3.1.2、查询表空间占用大小

新建表后，已使用空间会变大、剩余空间会变小

https://blog.csdn.net/u011595939/article/details/131168337

-- 查看数据库文件总大小
select (select TOTAL_SIZE from V$DATABASE) *(select page())/1024/1024 as 占用大小单位MB;

-- 查看表空间使用情况
SELECT
    t.tablespace_name AS "表空间名称",
    t.total_space AS "总空间(MB)",
    t.total_space - f.free_space AS "已使用空间(MB)",
    f.free_space AS "剩余空间(MB)",
    ((t.total_space - f.free_space) / t.total_space) * 100 AS "已使用百分比"
FROM
    (SELECT
        tablespace_name,
        SUM(bytes) / 1024 / 1024 AS total_space
    FROM
        dba_data_files
    GROUP BY
        tablespace_name) t
JOIN
    (SELECT
        tablespace_name,
        SUM(bytes) / 1024 / 1024 AS free_space
    FROM
        dba_free_space
    GROUP BY
        tablespace_name) f ON t.tablespace_name = f.tablespace_name;

-- 查询所有表空间的占用大小2
SELECT 

F.TABLESPACE_NAME AS 表空间名称,
(T.TOTAL_SPACE - F.FREE_SPACE) "使用 (MB)",
F.FREE_SPACE "剩余 (MB)",
T.TOTAL_SPACE "总大小 (MB)",
(ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) || '%' "使用率(使用/总)"

FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BLOCKS *
(SELECT PARA_VALUE / 1024
FROM V$DM_INI
WHERE PARA_NAME =
'GLOBAL_PAGE_SIZE') / 1024)) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES /
1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;

3.2、HUGE表

3.2.1、查询表空间

-- huge表（列存储表）
-- HUGE表存储在HTS(HUGE TABLESPACE)表空间上,最多可以创建32767个HUGE表空间。默认的HUGE表空间是HMAIN。查看HUGE表空间的SQL语句如下 
select * from v$HUGE_TABLESPACE;

3.2.2、查询表空间大小

可以参考问答：https://eco.dameng.com/community/question/224bbb1e97def662c0b9a7701162cef6

-- 查询HUGE列存储空间
SELECT ID,NAME,PATHNAME
      ,ROUND(GET_DISK_SIZE(PATHNAME) / 1024/1024/1024, 2) AS "总空间(GB)"
      ,ROUND(GET_DISK_SIZE(PATHNAME) * GET_DISK_RATIO(PATHNAME) / 1024/1024/1024, 2) AS "使用(GB)"
      ,ROUND(GET_DISK_SIZE(PATHNAME) * (1 - GET_DISK_RATIO(PATHNAME)) / 1024/1024/1024, 2) AS "剩余(GB)"
FROM V$HUGE_TABLESPACE

4、测试

4.1、准备表

所有设置了压缩的表，压缩率都设置为9（最大压缩率）

①T_PT：普通表/行式表，未设置压缩

②T_PT_COMPRESS ：普通表/行式表，设置了压缩

③T_HUGE_NOCOMPRESS： huge表/列式表，未压缩

④T_HUGE_COMPRESS_FIELD：huge表/列式表，压缩一个字段

⑤T_HUGE_COMPRESS： huge表/列式表，压缩整个表（所有字段）

以上表，都是两个字段：

	"ID"   VARCHAR2(50),
	"NAME" VARCHAR2(8188)

查看表是否是压缩表

-- 查询表信息：(COMPRESSION 字段，是否被压缩):ENABLED被压缩；DISABLED未被压缩。
SELECT TABLE_NAME,TABLESPACE_NAME,COMPRESSION
FROM DBA_TABLES
WHERE OWNER = 'CS_YT1'
AND TABLE_NAME = 'T_PT' 
OR TABLE_NAME = 'T_PT_COMPRESS'
OR TABLE_NAME = 'T_HUGE_NOCOMPRESS'
OR TABLE_NAME = 'T_HUGE_COMPRESS_FIELD'
OR TABLE_NAME = 'T_HUGE_COMPRESS'
;

4.2、测试条件

每张表循环插入10万条数据

-- 循环执行一条sql
DECLARE
    i NUMBER := 1;
BEGIN
    WHILE i <= 100000 LOOP
        -- 在这里编写你要执行的SQL语句
        -- 例如：EXECUTE IMMEDIATE 'INSERT INTO table_name VALUES (' || i || ')';

insert into "CS_YT1"."T_PT"("ID", "NAME") 
VALUES(i, 'dddd……');
insert into "CS_YT1"."T_PT_COMPRESS"("ID", "NAME") 
VALUES(i, 'dddd……');
insert into "CS_YT1"."T_HUGE_NOCOMPRESS"("ID", "NAME") 
VALUES(i, 'dddd……');
insert into "CS_YT1"."T_HUGE_COMPRESS_FIELD"("ID", "NAME") 
VALUES(i, 'dddd……');
insert into "CS_YT1"."T_HUGE_COMPRESS"("ID", "NAME") 
VALUES(i, 'dddd……');

        i := i + 1;
    END LOOP;
END;

查看每张表数据量

select '普通表' as table_name,count(1) as 数量 from T_PT
union all
select '普通压缩表' as table_name,count(1) as 数量 from T_PT_COMPRESS
union all
select 'huge未压缩表' as table_name,count(1) as 数量 from T_HUGE_NOCOMPRESS
union all
select 'huge压缩字段表' as table_name,count(1) as 数量 from T_HUGE_COMPRESS_FIELD
union all
select 'huge压缩表' as table_name,count(1) as 数量 from T_HUGE_COMPRESS

4.3、测试结果

查询每张表的占用空间大小

-- 查询表的实际占用大小、占用表空间大小
SELECT 
ROUND(TABLE_USED_PAGES('CS_YT1','T_PT') * PAGE / 1024 / 1024, 2)  AS "普通表-实际大小(MB)" ,
ROUND(TABLE_USED_SPACE('CS_YT1','T_PT') * PAGE / 1024 / 1024, 2)  AS "普通表-占用表空间大小(MB)",

ROUND(TABLE_USED_PAGES('CS_YT1','T_PT_COMPRESS') * PAGE / 1024 / 1024, 2) AS "普通压缩表-实际大小(MB)" ,
ROUND(TABLE_USED_SPACE('CS_YT1','T_PT_COMPRESS') * PAGE / 1024 / 1024, 2) AS "普通压缩表-占用表空间大小(MB)",

ROUND(TABLE_USED_PAGES('CS_YT1','T_HUGE_NOCOMPRESS') * PAGE / 1024/ 1024, 2) AS "huge未压缩表-实际大小(MB)" ,
ROUND(TABLE_USED_SPACE('CS_YT1','T_HUGE_NOCOMPRESS') * PAGE / 1024/ 1024, 2) AS "huge未压缩表-占用表空间大小(MB)",

ROUND(TABLE_USED_PAGES('CS_YT1','T_HUGE_COMPRESS_FIELD') * PAGE / 1024/ 1024, 2) AS "huge压缩字段表-实际大小(MB)" ,
ROUND(TABLE_USED_SPACE('CS_YT1','T_HUGE_COMPRESS_FIELD') * PAGE / 1024/ 1024, 2) AS "huge压缩字段表-占用表空间大小(MB)",

ROUND(TABLE_USED_PAGES('CS_YT1','T_HUGE_COMPRESS') * PAGE / 1024/ 1024, 2) AS "huge压缩表-实际大小(MB)" ,
ROUND(TABLE_USED_SPACE('CS_YT1','T_HUGE_COMPRESS') * PAGE / 1024/ 1024, 2) AS "huge压缩表-占用表空间大小(MB)"
;

结果

4.4、结论

普通表，也不压缩占用空间都一样
huge表，占用空间从小到大为：

压缩huge表（压缩所有字段） < 压缩字段huge表 < 未压缩的huge表

ps：压缩字段表、压缩表差别不大是因为未设置压缩的字段值很小，但是结果已经能说明，压缩了的表占用空间小。

5、总结

①、普通表：不支持压缩，就算建表时增加压缩关键字也没有意义。

②、huge表（列式存储表）：修改huge列式存储表为压缩表的方案：

先修改原来表的名字（原表名A，改为B），新建压缩表A（很慢，3-8秒）
将B表中的数据重新插入到压缩表A中
最后删除B表

③、导入数据方式

一、未建表，直接插入数据的过程中建表

-- 100万数据，建表+抽数据：1未压缩表：19秒 660毫秒；2压缩表：16秒 281毫秒；3压缩表往普通表添加数据：10分 39秒 536毫秒
CREATE HUGE TABLE "T_HUGE_COMPRESS_05"
COMPRESS LEVEL 7 FOR 'QUERY LOW' LOG LAST
AS
SELECT * FROM T_HUGE_COMPRESS_BACK;

二、insert into select

-- 2、普遍表
-- 建表：6秒 500毫秒；抽数据100万：10秒 640毫秒、11秒 189毫秒
insert into T_HUGE_COMPRESS
(ID, NAME)
(
SELECT * FROM T_HUGE_COMPRESS_BACK
);

posted on 2023-11-20 09:08 C_C_菜园阅读(488) 评论(0) 编辑收藏举报

刷新页面返回顶部

kakarotto-chen