达梦数据库（DM8）深入学习笔记

一、DM8 运维规范

作为达梦管理员，日常的运维工作至关重要。为了确保系统的稳定性和高效运行，我们需要遵循一系列严格的运维流程和规范。

达梦管理员的任务：
- 安装与配置：负责数据库的初始安装和配置，包括硬件环境准备、软件部署、网络设置等。
- 备份与恢复：制定并执行定期备份策略，确保数据的安全性和可恢复性。熟悉各种恢复场景下的操作步骤。
- 性能优化：通过监控工具分析系统性能瓶颈，调整参数以提高查询效率和响应速度。
- 安全管理：实施安全策略，如用户权限管理、访问控制列表（ACL）、防火墙规则等。
- 故障处理：及时响应并解决生产环境中出现的问题，保障业务连续性。
运维流程规范：
- 巡检制度：每周进行一次全面巡检，检查服务器状态、磁盘空间、日志文件等关键指标。
- 变更管理：任何配置或代码上的改动都必须经过严格的测试，并记录详细的变更日志。
- 补丁更新：根据官方发布的安全公告和技术文档，定期评估和应用必要的补丁程序。
- 应急演练：每年至少组织两次应急演练，模拟真实场景下的故障恢复过程，提升团队应对突发事件的能力。

二、DM8 分区表管理

分区表是优化大型数据集查询性能的有效手段之一。合理设计分区结构可以显著减少I/O开销，简化数据管理和维护工作。

分区类型：
- 范围分区（Range Partitioning）：适用于时间序列或其他有序数据。例如，按日期或编号范围进行划分。
- 哈希分区（Hash Partitioning）：用于随机分布的数据。通过散列函数将数据均匀分配到各个分区内。
- 列表分区（List Partitioning）：针对离散值的数据集。每个分区包含一组预定义的值。

创建与管理：

创建分区表：使用 CREATE TABLE ... PARTITION BY 语句指定分区方式和条件。

CREATE TABLE t_partrangetab (
    logtime datetime,
    userid int,
    username varchar(20),
    areaid int,
    optype int
) PARTITION BY RANGE(logtime) (
    PARTITION p2022 VALUES LESS THAN('2023-01-01') TABLESPACE tbs01,
    PARTITION p202301 VALUES LESS THAN('2023-02-01') TABLESPACE tbs02
);

添加分区：随着新数据的到来，可以通过 ALTER TABLE ... ADD PARTITION 动态增加新的分区。
```
ALTER TABLE t_partrangetab ADD PARTITION p202304 VALUES LESS THAN ('2023-05-01') TABLESPACE tbs;
```
合并分区：当某些分区的数据量过小或不再需要时，可以使用 ALTER TABLE ... MERGE PARTITIONS 将其合并为一个更大的分区。
```
ALTER TABLE T_PARTRANGETAB MERGE PARTITIONS p202301, p202302 INTO PARTITION p202302;
```

拆分分区：对于过于庞大的分区，可以利用 ALTER TABLE ... SPLIT PARTITION 将其细分为多个更小的部分。

ALTER TABLE T_PARTRANGETAB SPLIT PARTITION p202302 AT ('2023-02-15') INTO (PARTITION p20230115, PARTITION p202302);

三、临时表与外部表管理

临时表和外部表在特定场景下非常有用，能够有效提高开发效率和灵活性。

临时表：
- 会话级临时表：数据在整个会话期间有效，直到会话结束才会被清除。
```
CREATE TEMPORARY TABLE t_tem02 (id int, name VARCHAR(20)) ON COMMIT PRESERVE ROWS;
```
- 事务级临时表：仅对当前事务有效，提交后自动清空。
```
CREATE TEMPORARY TABLE t_tem01 (id int, name VARCHAR(20)) ON COMMIT DELETE ROWS;
```
- 优势：不记录 Redo 日志，插入性能更快；只对创建它的会话可见，避免了跨会话干扰。

外部表：

用途：主要用于从外部文件导入数据，特别是大规模批量导入任务。

创建方法：

直接指定数据文件：

CREATE EXTERNAL TABLE t_extemp (
    "EMPLOYEE_ID" INT,
    "EMPLOYEE_NAME" VARCHAR(20),
    ...
) FROM DATAFILE DEFAULT DIRECTORY DIR_DATA LOCATION ('DMHR_EMPLOYEE.txt')
PARAMS (fields DELIMITED by '|', records 0x0a, skip 1, character_code 'UTF-8');

基于控制文件：

编写控制文件 dept.ctl：

options
(skip=1
errors=10
log='t_extdept.log'
character_code='UTF-8')
load data
infile 'DMHR_DEPARTMENT.txt' str X '0d0a'
badfile 't_extdept.bad'
into table t_extdept
fields '|'

创建外部表：

CREATE EXTERNAL TABLE t_extdept (
    "DEPARTMENT_ID" INT,
    "DEPARTMENT_NAME" VARCHAR(30),
    ...
) FROM DEFAULT DIRECTORY DIR_DATA LOCATION ('dept.ctl');

四、索引管理

索引是优化查询性能的关键技术之一。正确地创建和维护索引可以极大程度上改善系统的整体表现。

统计信息收集：
- 静态收集：使用 dbms_stats 包手动收集统计信息。可以通过 gather_table_stats 等函数指定采样率和目标对象。
```
DBMS_STATS.GATHER_TABLE_STATS('DMTEST','T_TESTTAB', ESTIMATE_PERCENT=>100);
```
- 动态收集：通过设置 OPTIMIZER_DYNAMIC_SAMPLING 参数实现动态采样。取值范围为0~12，其中11表示由优化器自动确定采样率。
创建索引：
- 普通索引（二级索引）：默认情况下创建的是非聚集索引。它不会改变表中数据的实际存储顺序。
```
CREATE INDEX ix_testtab_userid ON T_TESTTAB(userid) TABLESPACE TBSIDX ONLINE;
```
- 聚簇索引（一级索引）：将数据按照索引键排序存储，适合频繁读取但较少更新的场景。
```
CREATE CLUSTER INDEX ix_test_cluster_id ON t_test(id);
```
- 唯一索引：保证某一列或多列组合的值唯一。
```
CREATE UNIQUE INDEX ix_TESTTAB_username ON T_TESTTAB (username);
```
- 位图索引：适用于列值分布较少且主要用于查询而非更新的情况。
```
CREATE BITMAP INDEX ix_testtab_optype ON T_TESTTAB(optype) TABLESPACE TBSIDX;
```
- 函数索引：基于表达式建立的索引，能提升特定查询的效率。
```
CREATE INDEX ix_TESTTAB_funusername ON T_TESTTAB (upper(username));
```
- 组合索引：将多个字段组合成一个索引，有助于复杂查询条件的匹配。
```
CREATE INDEX ix_COMBINED ON T_TESTTAB (userid, username);
```
维护索引：
- 有效性/无效性：可以通过 ALTER INDEX ... UNUSABLE 和 ALTER INDEX ... REBUILD 控制索引的状态。
- 可见性/不可见性：即使索引存在，也可以通过 ALTER INDEX ... INVISIBLE 让优化器忽略它。
- 监控索引使用情况：开启索引监控后，可以通过 V$OBJECT_USAGE 视图查看索引的使用频率。
```
ALTER INDEX ix_TESTTAB_username MONITORING USAGE;
SELECT * FROM SYS."V$OBJECT_USAGE";
```

五、安全管理

保障数据库的安全性是至关重要的。除了设置复杂的密码策略外，还需要启用强制访问控制机制，并定期审查审计日志以发现潜在风险。

用户资源限制：

创建资源限制 Profile：可以为不同用户组或角色定制化的资源配额。
```
CREATE PROFILE pro1 LIMIT PASSWORD_LIFE_TIME 180;
```

查看用户资源限制信息：

SELECT a.name username, c.name profilename
FROM sysobjects a, SYSUSERPROFILES b, sysobjects c
 WHERE a.id = b.uid AND b.pid = c.id;

操作系统认证：
- 基于 OS 的身份验证：首先将操作系统用户加入到相应的用户组（如 dmdba），然后在数据库中创建同名用户。
```
[root@localhost ~]# groupadd dmdba
[root@localhost ~]# usermod -G dmdba dmdba
```
- 普通用户登录：需要在数据库中创建同名用户，并将其加入到 dmusers 用户组。
```
[root@localhost ~]# useradd dmtest
[root@localhost ~]# usermod -G dmusers dmtest
[dmtest@localhost bin]$ ./disql / as dmusers
```
数据库审计：
- 系统审计：记录系统的启动与关闭事件。
- 语句审计：审计特定类型的 SQL 或语句组。例如，AUDIT TABLE 将审计所有 DDL 操作。
```
SP_AUDIT_STMT('TABLE','NULL','ALL');
```
- 对象审计：审计作用于某个特定对象上的语句。例如，审计 EMP 表上的 INSERT 语句。
```
SP_AUDIT_OBJECT('INSERT','DMTEST','DMTEST','T_PARTRANGETAB','SUCCESSFUL');
```

六、数据迁移

随着业务的发展，有时需要将现有数据迁移到新的平台。DTS 工具提供了图形化的界面，使得整个过程变得简单直观。

迁移准备：
- 初始化参数环境：确保目标环境的各项配置与源库一致，特别是字符集、页大小等方面。
- 系统包环境：如果业务用到了空间地理数据、DBMS_SCHEDULER、DBMS_JOB 等功能，需要提前创建相关系统包。
注意事项：
- 关闭不必要的监控功能：如 enable_monitor 和 pk_with_cluster 参数，以减少额外开销。
- 禁用归档模式：归档文件会占用大量磁盘空间，并影响迁移速度。
- 分批次逐步完成迁移任务：先迁移序列、基础表、视图、存储过程、函数等，再逐步处理大表和其他复杂对象。

七、快速数据加载

当面对大量数据时，传统的插入方法显然不够高效。这时可以借助 dmfldr 工具实现批量装载。

控制文件编写：

定义输入输出格式：通过控制文件指导 dmfldr 如何处理数据。

示例控制文件：

[OPTIONS(
skip=1
errors=10
log='t_extdept.log'
character_code='UTF-8'
)]
LOAD DATA
INFILE 'DMHR_DEPARTMENT.txt' str X '0d0a'
BADFILE 't_extdept.bad'
INTO TABLE t_dept
FIELDS '|'

实际应用：

从文本文件中读取数据：

[dmdba@localhost dmfldr]$ dmfldr userid=dmtest/Dameng123 control=\'dept.ctl\' mode=\'IN\'

向文本文件中写入数据：

[dmdba@localhost dmfldr]$ dmfldr userid=dmtest/Dameng123 control=\'emp.ctl\' mode=\'OUT\'

感谢老师的耐心讲解！