热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
登录
注册
技术探索：数据库数据空洞问题初步探索
培训园地/
文章详情
/
技术探索：数据库数据空洞问题初步探索
L.Ink
2026/03/18
241
0
0
最近在项目中发现了一个现象，原本1张大表占用的空间达到了上百GB的大小，但是将这张表重建后，将原本的数据导入，表只占用了几十GB的空间，瞬间缩减了几十GB。这个现象立马引起了专家的重视，长时间运行的系统中，数据空洞带来的表膨胀对查询性能、运维工作一定会带来一些隐患和风险，今天初步探索一下数据空洞能有多严重，怎么评估表的空洞情况。
一、什么是数据空洞？
通常在数据库的底层存储中，当执行 DELETE 操作删除行数据时，数据库并不会将物理存储空间删除，它只是将那些数据页内的空间清除，或在内部标记为“可复用”。当新的数据插入时，可能会优先使用这些被标记的空闲位置，而不是去申请新的磁盘空间。
这些被标记为可复用、但尚未被新数据填满的闲置空间，就被称为数据空洞。如果1张表在经过反复的DML操作后，数据页里存储数据的空间并不能百分百复用，一定会存在一些空洞，就像往一个瓶子里装石子，会存在不少的空间无法完全利用，而一旦数据空洞过多，就会造成占用数据页的数量膨胀。
二、DM8表数据存储试验
首先，在DM8中数据最小存储单元是数据页，而且手册中提到，DM 规定每条记录的总长度不能超过页面大小的一半。
在测试实例上，先确认页大小，这是一个页大小为16K的实例。
SELECT SF_GET_PAGE_SIZE();
16384
在创建表的时候，一次性会申请1个簇的大小，默认1个簇是16页，16K页大小最多存放的记录大约是8000字节，因此创建一张测试表：
-- drop table TEST_PAGE_FULL_TAB1;
CREATE TABLE TEST_PAGE_FULL_TAB1 (
c1 VARCHAR(2000),
c2 VARCHAR(2000),
c3 VARCHAR(2000),
c4 VARCHAR(2000)
);
尝试插入最长的记录，经过试验，最长可插入7973字节长度的数据。
-- 插入成功，如果最后一个字段插入1974，则会提示超长。
INSERT INTO TEST_PAGE_FULL_TAB1 VALUES(
RPAD('A', 2000, 'A'),
RPAD('B', 2000, 'B'),
RPAD('C', 2000, 'C'),
RPAD('D', 1973, 'D')
);
完成初步准备后，可以通过存储过程，反复写入约8000长度的记录，同时每写入一行数据，就记录一次测试表数据页使用情况的信息。
-- 为了记录数据页信息情况，创建一个记录表。
CREATE TABLE TEST_PAGE_FULL_STATS (
id INT IDENTITY(1,1) PRIMARY KEY, -- 自增ID
insert_number INT, -- 插入次数（第几次）
total_pages INT, -- 表占用的总页数（HWM以下）
total_mb DECIMAL(10,4), -- 占用大小（MB）
used_pages INT, -- 表实际使用的数据页数
used_mb DECIMAL(10,4), -- 实际使用大小（MB）
record_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP() -- 记录时间
);
-- 创建1个存储过程，每写入一行数据，就记录一下页信息，直到写入1000行数据。
CREATE OR REPLACE PROCEDURE SP_INSERT_LOOP_STATS()
AS
v_count INT := 0; -- 循环计数器
v_total_pages INT;
v_total_mb DECIMAL(10,4);
v_used_pages INT;
v_used_mb DECIMAL(10,4);
BEGIN
WHILE v_count < 1000 LOOP
-- 插入一行数据（每个字段长度固定，总字符数约7973字节）
INSERT INTO TEST_PAGE_FULL_TAB1 VALUES(
RPAD('A', 2000, 'A'),
RPAD('B', 2000, 'B'),
RPAD('C', 2000, 'C'),
RPAD('D', 1973, 'D')
);
COMMIT; -- 提交事务，确保空间统计信息反映最新状态
-- 获取当前表的空间占用
SELECT TABLE_USED_SPACE('SYSDBA', 'TEST_PAGE_FULL_TAB1') INTO v_total_pages FROM DUAL;
SELECT (v_total_pages * PAGE()) / 1024 / 1024 INTO v_total_mb FROM DUAL;
SELECT TABLE_USED_PAGES('SYSDBA', 'TEST_PAGE_FULL_TAB1') INTO v_used_pages FROM DUAL;
SELECT (v_used_pages * PAGE()) / 1024 / 1024 INTO v_used_mb FROM DUAL;
-- 将本次结果插入记录表
INSERT INTO TEST_PAGE_FULL_STATS(
insert_number, total_pages, total_mb, used_pages, used_mb
) VALUES (
v_count + 1, v_total_pages, v_total_mb, v_used_pages, v_used_mb
);
v_count := v_count + 1;
END LOOP;
COMMIT;
END;
/
-- 调用存储过程，并查看记录表结果。
CALL SP_INSERT_LOOP_STATS();
SELECT * FROM TEST_PAGE_FULL_STATS ORDER BY insert_number;
通过查看测试的结果，发现一个比较奇怪的问题。前100行结果如下：
在上面的结果中，存在有几个疑点：
1.1个数据页中，肯定不止存放一条记录，从第48行以后，基本上记录数都是大于表占用的数据页数量的。
2.每次扩展簇的时候，新占用的数据页都是16个，且其中15个都是被使用的(TABLE_USED_PAGES)，还没写数据为什么就被使用了？
目前，根据测试的结果，只能推测：
1.手册中说的每条记录的总长度不能超过页面大小的一半，并不只是指16K的页只能存得下8K长度的数据，实际上1个页是可以存放2条接近8K长度数据的，应该与表的FILLFACTOR设置有关，默认是0即可全部空间可使用，只是规定不能超过页大小的一半。最终1000条记录占用了528个页，平均1个页存放了1.89条记录。
2.TABLE_USED_PAGES 统计的可能是已格式化的页数（包括空闲页），所以它大于实际数据页数，随着插入继续，页内空闲空间会被逐步填满。每次扩展的16个页中，会直接格式化15个页，还有1个页可能作为管理页或者其他用途没被直接格式化存放记录。
三、数据空洞测试
经过了上述试验，我们对于DM库里的IOT表记录存储有了一个初步的理解，那么可以开始设计数据空洞的测试场景了。
首先，同样创建一个测试表和记录表。
-- truncate table TEST_PAGE_FULL_TAB2;
-- truncate table TAB2_SIZE_STATS;
CREATE TABLE TEST_PAGE_FULL_TAB2 (
id VARCHAR(10),
col2 VARCHAR(200),
col3 VARCHAR(200),
col4 VARCHAR(200),
col5 VARCHAR(200),
col6 VARCHAR(200),
col7 VARCHAR(200),
col8 VARCHAR(200),
col9 VARCHAR(200),
col10 VARCHAR(200)
);
-- 记录表，用于存储表大小信息（无自增ID和时间戳）
CREATE TABLE TAB2_SIZE_STATS (
row_count INT, -- 数据行数
total_pages INT, -- 占用总页数
total_mb DECIMAL(10,4), -- 占用大小（MB）
used_pages INT, -- 使用页数
used_mb DECIMAL(10,4) -- 使用大小（MB）
);
创建2个存储过程，过程1 用来初始化数据，通过随机写入100w行数据，并记录表大小。过程2通过反复的更新、删除、插入，创造数据空洞的条件。
CREATE OR REPLACE PROCEDURE SP_INIT_TAB2_DATA
AS
v_rows INT := 1000000; -- 总插入行数
v_batch INT := 10000; -- 每批插入行数
v_loop INT := v_rows / v_batch; -- 循环次数
v_i INT;
v_row_count INT;
v_total_pages INT;
v_used_pages INT;
v_total_mb DECIMAL(10,4);
v_used_mb DECIMAL(10,4);
v_page_size INT;
BEGIN
-- 清空表（保证从零开始）
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEST_PAGE_FULL_TAB2';
-- 获取页大小（字节）
SELECT PAGE() INTO v_page_size FROM DUAL;
-- 分批插入数据（每批插入 v_batch 行）
FOR v_i IN 1..v_loop LOOP
INSERT INTO TEST_PAGE_FULL_TAB2 (id, col2, col3, col4, col5, col6, col7, col8, col9, col10)
SELECT
DBMS_RANDOM.STRING('A', 10), -- 随机10位大写字母
RPAD('A', 200, 'A'), -- 固定200个'A'
RPAD('B', 200, 'B'), -- 固定200个'B'
RPAD('C', 200, 'C'), -- 固定200个'C'
RPAD('D', 200, 'D'), -- 固定200个'D'
RPAD('E', 200, 'E'), -- 固定200个'E'
RPAD('F', 200, 'F'), -- 固定200个'F'
RPAD('G', 200, 'G'), -- 固定200个'G'
RPAD('H', 200, 'H'), -- 固定200个'H'
RPAD('I', 200, 'I') -- 固定200个'I'
FROM DUAL
CONNECT BY LEVEL <= v_batch;
COMMIT;
END LOOP;
-- 获取当前表统计信息
SELECT COUNT(*) INTO v_row_count FROM TEST_PAGE_FULL_TAB2;
SELECT TABLE_USED_SPACE('SYSDBA', 'TEST_PAGE_FULL_TAB2') INTO v_total_pages FROM DUAL;
SELECT TABLE_USED_PAGES('SYSDBA', 'TEST_PAGE_FULL_TAB2') INTO v_used_pages FROM DUAL;
-- 计算MB大小
v_total_mb := v_total_pages * v_page_size / 1024 / 1024;
v_used_mb := v_used_pages * v_page_size / 1024 / 1024;
-- 插入记录表
INSERT INTO TAB2_SIZE_STATS (row_count, total_pages, total_mb, used_pages, used_mb)
VALUES (v_row_count, v_total_pages, v_total_mb, v_used_pages, v_used_mb);
COMMIT;
DBMS_OUTPUT.PUT_LINE('初始数据插入完成，行数：' || v_row_count || '，统计信息已记录。');
END;
/
CREATE OR REPLACE PROCEDURE SP_MIX_TAB2
AS
v_update_rows INT := 100000; -- 目标更新行数
v_delete_rows INT := 300000; -- 目标删除行数
v_insert_rows INT := 300000; -- 插入行数
v_batch INT := 10000; -- 每批插入行数（分30批）
v_i INT;
v_curr_rows INT;
v_actual_del INT;
v_actual_upd INT;
v_row_count INT;
v_total_pages INT;
v_used_pages INT;
v_total_mb DECIMAL(18,4);
v_used_mb DECIMAL(18,4);
v_page_size INT;
BEGIN
SELECT PAGE() INTO v_page_size FROM DUAL;
-- 获取当前行数
SELECT COUNT(*) INTO v_curr_rows FROM TEST_PAGE_FULL_TAB2;
DBMS_OUTPUT.PUT_LINE('当前行数：' || v_curr_rows);
-- 1. 随机删除（不超过当前行数）
IF v_curr_rows < v_delete_rows THEN
v_actual_del := v_curr_rows;
ELSE
v_actual_del := v_delete_rows;
END IF;
DELETE FROM TEST_PAGE_FULL_TAB2
WHERE ROWID IN (
SELECT RID FROM (
SELECT ROWID AS RID
FROM TEST_PAGE_FULL_TAB2
ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM <= v_actual_del
);
v_actual_del := SQL%ROWCOUNT;
COMMIT;
DBMS_OUTPUT.PUT_LINE('实际删除行数：' || v_actual_del);
-- 2. 插入30万行（固定长度200，分30批）
FOR v_i IN 1..30 LOOP
INSERT INTO TEST_PAGE_FULL_TAB2 (id, col2, col3, col4, col5, col6, col7, col8, col9, col10)
SELECT
DBMS_RANDOM.STRING('A', 10),
RPAD('A', 200, 'A'),
RPAD('B', 200, 'B'),
RPAD('C', 200, 'C'),
RPAD('D', 200, 'D'),
RPAD('E', 200, 'E'),
RPAD('F', 200, 'F'),
RPAD('G', 200, 'G'),
RPAD('H', 200, 'H'),
RPAD('I', 200, 'I')
FROM DUAL
CONNECT BY LEVEL <= v_batch;
COMMIT;
END LOOP;
DBMS_OUTPUT.PUT_LINE('插入30万行完成');
-- 3. 随机更新10万行（固定长度200）
SELECT COUNT(*) INTO v_curr_rows FROM TEST_PAGE_FULL_TAB2;
IF v_curr_rows < v_update_rows THEN
v_actual_upd := v_curr_rows;
ELSE
v_actual_upd := v_update_rows;
END IF;
UPDATE TEST_PAGE_FULL_TAB2
SET
col2 = RPAD('A', 200, 'A'),
col3 = RPAD('B', 200, 'B'),
col4 = RPAD('C', 200, 'C'),
col5 = RPAD('D', 200, 'D'),
col6 = RPAD('E', 200, 'E'),
col7 = RPAD('F', 200, 'F'),
col8 = RPAD('G', 200, 'G'),
col9 = RPAD('H', 200, 'H'),
col10 = RPAD('I', 200, 'I')
WHERE ROWID IN (
SELECT RID FROM (
SELECT ROWID AS RID
FROM TEST_PAGE_FULL_TAB2
ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM <= v_actual_upd
);
v_actual_upd := SQL%ROWCOUNT;
COMMIT;
DBMS_OUTPUT.PUT_LINE('实际更新行数：' || v_actual_upd);
-- 4. 记录统计信息
SELECT COUNT(*) INTO v_row_count FROM TEST_PAGE_FULL_TAB2;
SELECT TABLE_USED_SPACE('SYSDBA', 'TEST_PAGE_FULL_TAB2') INTO v_total_pages FROM DUAL;
SELECT TABLE_USED_PAGES('SYSDBA', 'TEST_PAGE_FULL_TAB2') INTO v_used_pages FROM DUAL;
v_total_mb := ROUND(to_number(v_total_pages) * v_page_size / 1024 / 1024,2);
v_used_mb := ROUND(to_number(v_used_pages) * v_page_size / 1024 / 1024,2);
INSERT INTO TAB2_SIZE_STATS (row_count, total_pages, total_mb, used_pages, used_mb)
VALUES (v_row_count, v_total_pages, v_total_mb, v_used_pages, v_used_mb);
COMMIT;
DBMS_OUTPUT.PUT_LINE('混合操作完成，当前行数：' || v_row_count);
END;
/
通过调用存储过程，记录初始大小和大量DML操作后的表大小。
-- 初始化100w行数据；
CALL SP_INIT_TAB2_DATA();
-- 套一层过程，DML 20次
CREATE OR REPLACE PROCEDURE SP_RUN_MIX_20
AS
v_i INT;
BEGIN
FOR v_i IN 1..20 LOOP
SP_MIX_TAB2(); -- 调用混合操作过程
END LOOP;
DBMS_OUTPUT.PUT_LINE('已完成 20 次混合操作，请查看 TAB2_SIZE_STATS 表。');
END;
/
-- 执行
call SP_RUN_MIX_20();
最后，检查统计的信息结果。
发现，刚初始化100w行数据的时候，表占用了12.5万个数据页，表大小约2GB。经过反复的DML操作以后，虽然数据量还是维持在100w行，但是表大小已经逐渐扩展到了接近8GB并稳定下来。
这个时候，再创建一张测试表3，把当前经过反复DML的100w行数据写入表3，对比空间占用情况，确实可以缩小回2GB左右，也证明经过大量DML，TEST_PAGE_FULL_TAB2表就会产生大量的数据空洞。
-- 导入测试表3；
CREATE TABLE TEST_PAGE_FULL_TAB3 (
id VARCHAR(10),
col2 VARCHAR(200),
col3 VARCHAR(200),
col4 VARCHAR(200),
col5 VARCHAR(200),
col6 VARCHAR(200),
col7 VARCHAR(200),
col8 VARCHAR(200),
col9 VARCHAR(200),
col10 VARCHAR(200)
);
insert into TEST_PAGE_FULL_TAB3 select * from TEST_PAGE_FULL_TAB2;
commit;
四、初步分析
那么，怎么辨别数据库的表存在比较严重的数据空洞问题呢？
从表结构来算，可以通过评估各个字段的数据类型，比如测试表一行记录最长大约是1810字节，16K的页最终平均的1个页存放9条记录左右，通过计算100w行数据最紧密的情况下可以占用11w个数据页。
通过更新统计信息，可以直观获取2个表的差异。
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA', 'TEST_PAGE_FULL_TAB2', null, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA', 'TEST_PAGE_FULL_TAB3', null, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
SELECT
B.NAME AS 表名,
S.N_LEAF_PAGES,
S.N_LEAF_USED_PAGES
FROM SYS.SYSSTATS S, SYS.SYSOBJECTS B
WHERE S.ID = B.ID
AND B.NAME IN ('TEST_PAGE_FULL_TAB2','TEST_PAGE_FULL_TAB3')
AND B.TYPE$ = 'SCHOBJ'
AND S.COLID = -1;
结果如下：
2个表中，TEST_PAGE_FULL_TAB2表占用的数据页比理论值高了接近5倍，可以判断存在比较明显的数据空洞。
对于理论值的计算，可以结合项目中业务表用到的数据类型，定义好最长的长度，然后进行粗略评估，再用上述方式，应该可以初步找出数据空洞比较严重的表，再考虑通过重建表的方式消灭空洞。
评论
后发表回复
作者
文章
阅读量
获赞
扫一扫
联系客服
联系客服
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1