达梦8增加字段性能优化案例 b5ee9b2fbb4641ce8b307b53fd9b4c76

达梦8增加字段性能优化案例

情况	多张宽表包含上百个字段，需要在线增加字段（ALTER TABLE ADD COLUMN）
问题	测试环境增加字段非常慢，数据量2000万条
难点	生产环境的数据量达到七千万条，且业务为7*24小时高并发

优化方法

达梦数据库提供了一个动态会话级参数ALTER_TABLE_OPT，该参数默认值为0。具体参数说明如下：

参数名	缺省值	属性	说明
ALTER_TABLE_OPT	0	动态，会话级	是否对加列、修改列、删除列操作进行优化。 0：全部不优化； 1：全部优化； 2：快速加列，删除列和修改列与1等效； 3：快速加列，允许指定快速列默认值，其他功能与2相同

操作步骤

1. 查询当前参数值

sql复制代码
SELECT PARA_NAME, PARA_VALUE, FILE_VALUE
FROM V$DM_INI
WHERE PARA_NAME='ALTER_TABLE_OPT';

2. 修改参数为3

sql复制代码
SP_SET_PARA_VALUE(1, 'ALTER_TABLE_OPT', 3);
-- 或
ALTER SYSTEM SET 'ALTER_TABLE_OPT' = 3 BOTH;

说明：参数修改后，只对新建立连接的会话生效，原本存在的会话无效。

参数详解

1. ALTER_TABLE_OPT = 1：
   • 添加列时采用查询插入实现，可能导致ROWID改变。
2. ALTER_TABLE_OPT = 2：
   • 开启快速加列功能，对于没有默认值或默认值为NULL的新列，系统标记为附加列，实现瞬间加列效果，不改变ROWID。
3. ALTER_TABLE_OPT = 3：
   • 开启快速加列功能，允许指定新增列默认值，系统标记为附加列，查询表中已存在的数据时自动追加默认值，不改变ROWID。

测试和结果

1. 创建基础表并插入数据

sql复制代码
CREATE TABLE t_objects AS
SELECT level logid, * FROM sysobjects
CONNECT BY rownum <= 180000;

BEGIN
    FOR i IN 1..5
    LOOP
        INSERT INTO t_objects
        SELECT * FROM t_objects;
        COMMIT;
    END LOOP;
END;

2. 测试不同参数值对加列无默认值操作的效率

sql复制代码
-- 设置参数值并测试
ALTER SESSION SET 'ALTER_TABLE_OPT' = 0;
ALTER TABLE t_objects ADD COLUMN logtime DATETIME;
ALTER TABLE t_objects DROP COLUMN logtime;

ALTER SESSION SET 'ALTER_TABLE_OPT' = 1;
ALTER TABLE t_objects ADD COLUMN logtime DATETIME;
ALTER TABLE t_objects DROP COLUMN logtime;

ALTER SESSION SET 'ALTER_TABLE_OPT' = 2;
ALTER TABLE t_objects ADD COLUMN logtime DATETIME;
ALTER TABLE t_objects DROP COLUMN logtime;

ALTER SESSION SET 'ALTER_TABLE_OPT' = 3;
ALTER TABLE t_objects ADD COLUMN logtime DATETIME;
ALTER TABLE t_objects DROP COLUMN logtime;

3. 测试不同参数值对加列有默认值操作的效率

sql复制代码
-- 设置参数值并测试
ALTER SESSION SET 'ALTER_TABLE_OPT' = 0;
ALTER TABLE t_objects ADD COLUMN logtime DATETIME DEFAULT SYSDATE;
ALTER TABLE t_objects DROP COLUMN logtime;

ALTER SESSION SET 'ALTER_TABLE_OPT' = 1;
ALTER TABLE t_objects ADD COLUMN logtime DATETIME DEFAULT SYSDATE;
ALTER TABLE t_objects DROP COLUMN logtime;

ALTER SESSION SET 'ALTER_TABLE_OPT' = 2;
ALTER TABLE t_objects ADD COLUMN logtime DATETIME DEFAULT SYSDATE;
ALTER TABLE t_objects DROP COLUMN logtime;

ALTER SESSION SET 'ALTER_TABLE_OPT' = 3;
ALTER TABLE t_objects ADD COLUMN logtime DATETIME DEFAULT SYSDATE;
ALTER TABLE t_objects DROP COLUMN logtime;

测试结果

1. 加列无默认值：
   • ALTER_TABLE_OPT = 2和3时效率最快（毫秒级）。
   • ALTER_TABLE_OPT = 1有较小效率提升（约25%）。
   • 删除列也有小幅效率提升（约25%）。
2. 加列有默认值：
   • ALTER_TABLE_OPT = 3时效率最快（毫秒级）。
   • ALTER_TABLE_OPT = 1和2也有提升（20%-30%）。

总结

1. ALTER_TABLE_OPT = 3时，不管新增列有没有默认值，效率最快（毫秒级）。
2. ALTER_TABLE_OPT = 0时，大表新增和删除列最慢。
3. ALTER_TABLE_OPT = 1, 2, 3时，新增列效率均有提升，删除列有小幅提升。
4. 对于生产系统，在大表上使用以上方法添加列时，请联系达梦数据库技术服务团队获取建议。

https://eco.dameng.com 达梦社区