DM系统管理员

DM数据库系统管理员操作

前言

• 前面从体系结构到数据库的安装都出了对应的文章，这次从系统管理员的角度对数据库的角色进行一个说明。同时也说一下作为比用户更高一级的系统管理员应该如何管理数据库。

DM系统管理员

DM 数据库采用“三权分立”或“四权分立”的安全机制。“三权分立”：数据库管理员、数据库安全员和数据库审计员；“四权分立”时新增数据库对象操作员。

• DM系统管理员的类型

  

  • 数据库管理员（DBA）

    • 每个 DM 数据库至少需要一个数据库管理员来管理
      • 负责：评估数据库运行所需的软、硬件环境、安装和升级 DM 数据库、配置 DM 数据库参数、创建主要的数据库存储结构（表空间）和对象（如表、视图、索引、角色、用户等）、监控和优化数据库性能、数据导入导出以及数据库的备份和恢复等
    • “三权分立”时可以进行的操作：
      • 既可进行系统管理和维护工作，也可对数据内容进行增删查改动作
    • “四权分立”时可以进行的操作：
      • 只具有“三权分立”中 DBA 角色预设的一部分与数据库管理相关的明确的数据库权限，如数据库创建、备份、还原和校验等

  • 数据库安全员（SSO）

    • 对于大型系统中，需要一名安全员来制定安全策略，强化系统安全机制，此时数据库安全员的主要任务就是制定安全策略，定义新的数据库安全员，设置系统的安全等级、范围和组，并为主、客体定义安全标记，从而全面提升系统安全性。

      以我的理解就是，需要一位专门制定安全措施和机制的人员来保障数据库的安全

  • 数据库审计员（AUDITOR）

    • 数据库审计员可以设置要审计的对象和操作、定义新的数据库审计员、查看和分析审计记录。通过设置审计，几乎可以跟踪任何人在系统内执行的任何操作，为事后追查提供便利。

      我的理解是AUDITOR是监视数据库的行为

  • 数据库对象操作员（DBO）——安全版才会有的

    • 可以创建数据库对象，并对自己拥有的数据库对象（表、视图、存储过程、序列、包、外部链接）具有所有的对象权限并可以授出与回收，但其无法管理与维护数据库对象。

• 数据库管理员的任务

  每个数据库至少需要一个 DBA 来管理。总体而言，数据库管理员的职责主要包括以下任务：

  • 评估数据库服务器所需的软、硬件运行环境
    • 通常情况下，DBA 不会直接配置除数据库之外的软、硬件环境，特殊情况下还是需要。但还是会负责为软、硬件的选型提供指南和规范
    • 作为 DBA，需要结合实际应用负载、总体费用、性能预期目标等来评估可供选择的软、硬件运行环境，需要考虑：
      • 操作系统/中间件等通用软件的稳定性、性能、安全性；
      • 处理器的个数和性能；
      • 内存容量和性能；
      • 网络带宽；
      • 存储容量和读写性能；
      • HBA 卡传输性能。
  • 安装和升级 DM 服务器
    • 数据库管理员，应负责安装 DM 数据库服务器软件。DBA 需要清楚地了解服务包和安全更新对现有系统的影响，然后考虑是否进行升级。是在生产系统上应用新的升级包之前，必须在测试环境下进行确认。
  • 数据库结构设计
    • 在安装 DM 数据库之后，DBA 就可以开始进行数据库结构设计。数据库的结构设计直接影响系统的综合性能，是 DBA 最为重要的工作任务之一。
    • 具体的配置情况和过程请看之前的数据库安装或者去官网查看相关的产品手册（当然，作为DBA这些必须是烂熟于心的）
  • 监控和优化数据库的性能
    • 监控和优化数据库的性能是数据库管理员的核心职责之一。对系统进行监控有助于在问题发生时识别它们，性能优化则有助于消除那些问题并防止它们发生。
    • DBA 需要提供对于各种问题的解决方案，努力优化 DM 数据库的各个方面
  • 计划和实施备份与故障恢复
    • DBA 必须对各种可能的故障和计划中的停机提供解决方案。DM 数据库提供了最基本的故障恢复、备份与还原功能，另外还提供了数据守护、数据复制、共享存储集群等高可靠、高可用解决方案，而DBA就需要学习对应的解决方案，能够在产品发生故障时及时提供方案。

• 数据库安全员的任务

  • 数据库安全员的主要职责就是制定并应用安全策略，强化系统安全机制，在安全策略中定义安全级别、范围和组，然后基于定义的安全级别、范围和组来创建安全标记，并将安全标记分别应用到主体（用户）和客体（表），以便启用强制访问控制功能。
  • 数据库安全员不能对用户数据进行增、删、改、查，也不能执行普通的 DDL 操作，他们只负责制定安全机制，将合适的安全标记应用到主体和客体，通过这种方式可以有效的对 DBA 的权限进行限制（防止黑客获取DBA身份后可以肆无忌惮地破坏数据库）

• 数据库审计员的任务

  • 数据库审计员设置审计策略（包括审计对象和操作），对数据库的活动行为进行监控，防止DBA等对数据库的某些重要数据进行修改。

讲真，我觉得DM数据库这个“三权分立”确实能很好地防护黑客，就算拿到了DBA权限，仍然只有一部分权利，不会说把数据库直接给翻天了，而且还有审计员可以时刻监视到数据库的行为，被入侵了会一时间就看出来。

基础数据库管理

• 管理模式对象的空间

  模式对象的空间管理关系到空间的有效使用和数据的合理分布

  • 设置存储参数

    • 普通表和索引

      • 针对普通表和索引，DM提供五个存储参数：

        • 初始簇数目 INITIAL：建立表时分配的簇个数。必须为整数，最小值为 1，最大值为 256，默认为 1
        • 下次分配簇数目 NEXT：当表空间不够时，从数据文件中分配的簇个数。必须为整数，最小值为 1，最大值为 256，默认为 1
        • 最小保留簇数目 MINEXTENTS：当删除表中的记录后，如果表使用的簇数目小于这个值，就不再释放表空间。必须为整数，最小值为 1，最大值为 256，默认为 1
        • 填充比例 FILLFACTOR：指定插入数据时数据页的充满程度，取值范围从 0 到 100。默认值为 0，等价于 100，表示全满填充，未充满的空间可供页内的数据更新时使用
        • 表空间名：在指定的表空间上建表或索引，表空间必须已存在，默认为用户缺省的表空间

      • 举例：

        • 在表空间TS_PERSON上建立表PERSON，初始簇为5，最小保留簇数目为5，下次分配簇数目为2，填充比例为85

        CREATE TABLE PERSON
        		(PERSONID INT IDENTITY(1,1) CLUSTER PRIMARY KEY,
        		SEX CHAR(1) NOT NULL,
        		NAME VARCHAR(50) NOT NULL,
        		EMAIL VARCHAR(50),
        		PHONE VARCHAR(25))
        		STORAGE
        			(INITIAL	5, MINEXTENTS 5,
        			NEXT	2, ON TS_PERSON, FILLFACTOR 85);
        

        

        • 也可以指定某个分区的存储参数，如下指定了PAR2分区存储在TS_PAR2表空间上

            CREATE TABLE PARTITION_TABLE
            	(C1 INT,
            	C2 INT) 
             	PARTITION BY RANGE(C1)
              	(PARTITION PAR1 VALUES LESS THAN(5), 
             	PARTITION PAR2 VALUES LESS THAN(100) STORAGE (ON TS_PAR2));
          

    • 堆表

      • 针对堆表，可以指定并发分支BRANCH非并发分支NOBRANCH的数目，范围是[1<=BRANCH<=64，1<=NOBRANCH<=64]，最多支持128个链表

      • 举例：创建LIST_TABLE表，2个并发分支，4个非并发分支

          CREATE TABLE LIST_TABLE(C1 INT) STORAGE(BRANCH (2,4));
        

        

    • HUGE表

      • 需要建立在混合表空间上，如果不使用默认的混合表空间 MAIN，则必须要先创建一个混合表空间。

        先创建一个混合表空间（名称为TS1，一样的位置）

          CREATE TABLESPACE TS1 DATAFILE 'C:\dmdbms\data\DAMENG\TS1\TS1.dbf' SIZE 128 WITH HUGE PATH 'C:\dmdbms\data\DAMENG\TS1\HUGE1';
        

        

        • 对于HUGE表，可以指定参数：
          • 区大小（一个区的数据行数）。 区大小可以通过设置表的存储属性来指定，区的大小必须是 2 的多少次方，如果不是则向上对齐。取值范围：1024 行~1024*1024 行。默认值为 65536 行。
          • 是否记录区统计信息，即在修改时是否做数据的统计。
          • 所属的表空间。创建 HUGE 表，需要通过存储属性指定其所在的表空间，不指定则存储于默认表空间 MAIN 中。HUGE 表指定的表空间只能是混合表空间。
          • 文件大小。创建 HUGE 表时还可以指定单个文件的大小，通过表的存储属性来指定，取值范围为 16M~1024*1024M。不指定则默认为 64M。文件大小必须是 2 的多少次方，如果不是则向上对齐。
          • 日志属性。1）LOG NONE：不做镜像；2）LOG LAST：做部分镜像；3）LOG ALL：全部做镜像。

      • 举例创建HUGE表：STUDENT 表的区大小为 65536 行，文件大小为 64M，指定所在的混合表空间为 TS1，做完整镜像，S_comment 列指定的区大小为不做统计信息，其它列默认设置

          CREATE HUGE TABLE STUDENT
          (
          	S_NO           	INT, 
            S_CLASS	        	VARCHAR, 
            S_COMMENT           	VARCHAR(79) STORAGE(STAT NONE)
          )STORAGE(SECTION(65536) , FILESIZE(64), ON TS1) LOG ALL;
        

  • 收回多余空间

    • 空闲的簇会被系统自动回收

  • 用户和表上的空间限制

    • 用户的空间限制

      • 用户占用的空间是他下边儿所有表占用的空间的总和，可以通过设置表空间限额限制用户使用的空间大小。

        • 例：创建用户TEST_USER时限制使用的TEST_TABLESPACE表空间大小为50M

          CREATE USER TEST_USER IDENTIFIED BY TEST_PASSWORD QUOTA 50M ON TEST_TABLESPACE;
        

        • 也可以修改限额，如修改TEST_USER可以用的表空间为100M

          ALTER USER TEST_USER QUOTA 100M ON TEST_TABLESPACE;
        

    • 表对象的空间限制

      • 创建表 TEST 时可规定该表对象可使用的最大磁盘空间为 500M

          CREATE TABLE TEST (SNO INT, MYINFO VARCHAR) DISKSPACE LIMIT 500;
        

      • 当然也可以修改（还是ALTER进行修改）

          ALTER TABLE TEST MODIFY DISKSPACE LIMIT 50;
        

  • 查看模式对象的空间使用

    • 查看用户占用空间

      • 使用系统函数USER_USED_SPACE 得到用户占用空间的大小，返回值是占用的页的数目（DBA用户下执行）

          SELECT USER_USED_SPACE('TEST');
        

        

    • 查看表占用的空间

      • 使用系统函数 TABLE_USED_SPACE 得到表对象占用空间的大小，函数参数为模式名和表名，返回值为占用的页的数目

          SELECT TABLE_USED_SPACE('DMHR', 'CITY');
        

        

    • 查看表使用的页数

      • 使用系统函数 TABLE_USED_PAGES 得到实际使用页的数目，函数参数为模式名和表名，返回值为实际使用页的数目

          SELECT TABLE_USED_PAGES('DMHR', 'CITY');
        

        

    • 查看索引占用的空间

      • 使用系统函数 INDEX_USED_SPACE 得到索引占用空间的大小，函数参数为索引 ID，返回值为占用的页的数目

          SELECT INDEX_USED_SPACE(33555604);
        

        

    • 查看索引使用的页数

      • 使用系统函数 INDEX_USED_PAGES 得到索引实际使用页的数目，函数参数为索引 ID，返回值为实际使用页的数目

          SELECT INDEX_USED_PAGES(33555604);
        

• 管理表

  • 管理表的准则：

    • 设计表
      • 数据库管理员认真规划每个表，具体需要做到以下几点：
        • 规范化表，估算并校正表结构，使数据冗余达到最小；
        • 为每个列选择合适的数据类型，是否允许为空等，并根据实际情况判断是否需要对列进行加密或压缩处理；
        • 建立合适的完整性约束；
        • 建立合适的聚集索引；
        • 根据实际需要，建立合适类型的表。
    • 指定表的存储空间上限
      • 在创建表时指定 SPACE LIMIT 子句，可以对表的存储空间指定上限。
      • DM 支持对表的存储空间指定大小，单位是 MB，由管理员指定，便于表的规模管理。
    • 指定表的存储位置
      • 创建表时，在 STORAGE 子句中，可对表指定存储的表空间

  • 创建表（这次就是对相关的每个操作进行讲解）

    • 创建普通表

      CREATE  TABLE  EMPLOYEE ( 
        EMPNO 	INT 				PRIMARY KEY, 
        ENAME 	VARCHAR(15) 	NOT NULL, 
        JOB 		VARCHAR(10), 
        MGR 		INT
        CONSTRAINT EMP_FKEY REFERENCES EMPLOYEE(EMPNO), 
        HIREDATE 	DATE 			DEFAULT (CURDATE), 
        SALARY 		FLOAT, 
        DEPTNO 	TINYINT 			NOT NULL 
        CONSTRAINT DEPT_FKEY REFERENCES DEPT(DEPTNO))
        STORAGE (
          INITIAL 			50, 
          NEXT 			50, 
          MINEXTENTS 		10,
          FILLFACTOR		80,
          ON 				USERS);
      

      • 这里是在user表空间创建一个employee表，并附上了几个完整性约束（后面会讲到，现在看不懂正常）

    • 指定表的聚集索引

      • DM数据库的表是基于B树索引结构进行管理，每个普通表都有一个聚集索引，数据通过聚集索引排序。当没有指定聚集索引键，会默认ROWID为聚集索引键，但是并不能提高查询速度，所以DM提供了三种指定聚集索引建的方式：

        • CLUSTER PRIMARY KEY：指定列为聚集索引键，并同时指定为主键，称为聚集主键；
        • CLUSTER KEY：指定列为聚集索引键，但是是非唯一的；
        • CLUSTER UNIQUE KEY：指定列为聚集索引键，并且是唯一的

      • 例：创建一个student表，指定stu_no为聚集主键

          CREATE  TABLE  STUDENT( 
            STUNO 		INT 				CLUSTER PRIMARY KEY, 
            STUNAME 	VARCHAR(15) 	NOT NULL, 
            TEANO 		INT, 
            CLASSID 	INT
          );
        

    • 指定表的填充因子

      • 每个普通表都含有一个聚集索引，指定表的填充因子，即指定聚集索引的填充因子，表示页面记录存储空间占页面总大小的百分比
        • 例如上面创建的employee表中的STORAGE，指定了FILLFACTOR为80，就是填充因子为80%

    • 查询建表

      • 查询建表的目的是为了创建一个只包含其他表中的几行（几列）数据，或者是想保存一个有名字的表的数据。

        • 我的理解就是将一次select查询的结果使用一个表来装起来

          CREATE TABLE NEW_EMP
          AS
          SELECT * FROM EMPLOYEE;
        

    • 创建临时表

      • 临时表作用：存储一些需要永久装存前暂时放存的数据或者一些SQL语句执行的中间结果。

      • 临时表在事务完成或会话断开后就会被清空。不同会话的临时表是独立的（不共享）

      • 临时表支持的功能：

        • 在临时表中，会话可以像普通永久表一样更新、插入和删除数据；
        • 临时表的 DML 操作产生较少的 REDO 日志；
        • 临时表支持建索引，以提高查询性能；
        • 在一个会话或事务结束后，数据将自动从临时表中删除；
        • 不同用户可以访问相同的临时表，每个用户只能看到自己的数据；
        • 临时表的数据量很少，意味着更高效的查询效率；
        • 临时表的表结构在数据删除后仍然存在，便于以后的使用；
        • 临时表的权限管理跟普通表一致

      • 临时表使用关键词 ON COMMIT 指定数据是事务级还是会话级，默认情况是事务级

        • ON COMMIT DELETE ROWS：指定临时表是事务级的，每次事务提交或回滚之后，表中所有数据都被删除；
        • ON COMMIT PRESERVE ROWS：指定临时表是会话级的，会话结束时才清空表，并释放临时 B 树

      • 例：创建一个事务级临时表

          CREATE GLOBAL TEMPORARY TABLE TMP_EMP(
            EMPNO 	INT 		PRIMARY KEY, 
            ENAME 	VARCHAR(15)  	NOT NULL, 
            JOB 	VARCHAR(10)) 
          ON COMMIT DELETE ROWS;
        

  • 更改表（ALTER）

    • 通过更改表，用户可以对数据库中的表作如下修改：
      • 添加或删除列，或修改现有的列的定义（列名、数据类型、长度、默认值）;
      • 添加、修改或删除与表相关的完整性约束；
      • 重命名一个表；
      • 启动或停用与表相关的完整性约束；
      • 启动或停用与表相关的触发器；
      • 修改表的 SPACE LIMIT；
      • 增删自增列。

  • 删除表（DROP）

    • 删除表时，将产生以下结果：

      • 表的结构信息从数据字典中删除，表中的数据不可访问；
      • 表上的所有索引和触发器被一起清除；
      • 所有建立在该表上的同义词、视图和存储过程变为无效；
      • 所有分配给表的簇标记为空闲，可被分配给其他的数据库对象。

    • 例：删除employee表

        DROP TABLE employee;
      

      • 删除不存在报错

      • 如果要删除的表被其他表引用，则要在 DROP TABLE 语句中包含 CASCADE 选项

          DROP TABLE employee CASCADE;
        

  • 清空表

    • 就是删除表中的所有行，三种方法：

      • 使用 DELETE 语句；
      • 使用 DROP 和 CREATE 语句；
      • 使用 TRUNCATE 语句。

    • 使用DELETE

        DELETE FROM employee;
      

      • 使用 DELETE 清空表，当表有很多行时，会消耗很多系统资源（针对小规模数据，慎用。虽然用的贼多）

    • 使用DROP和CREATE

        DROP TABLE employee;
        CREATE TABLE employee(…);
      

      • 就是删除表，然后建一个新表（这种我感觉容易暴毙）

    • 使用TRUNCATE

        TRUNCATE TABLE employee;
      

      • 这个更是重量级，没有回滚信息，不能回滚

  • 查看表信息

    • 查看表定义

      • 通过 SP_TABLEDEF 系统过程查看表的定义

          CALL SP_TABLEDEF('SYSDBA', 'employee');
        

    • 查看自增列信息

      • DM 支持 INT 和 BIGINT 两种数据类型的自增列，并提供以下函数查看表上自增列的当前值、种子和增量等信息：

        • IDENT_CURRENT：获得表上自增列的当前值；
        • IDENT_SEED：获得表上自增列的种子信息；
        • IDENT_INCR：获得表上自增列的增量信息。

          CREATE TABLE IDENT_TABLE (
                C1			INT		IDENTITY(100, 100),
                C2			INT
          );
          SELECT IDENT_CURRENT('SYSDBA.IDENT_TABLE');
          SELECT IDENT_SEED('SYSDBA.IDENT_TABLE');
          SELECT IDENT_INCR('SYSDBA.IDENT_TABLE');
        

    • 查看表空间的使用情况

        CREATE TABLE SPACE_TABLE (
              C1			INT,
              C2			INT
        );
        SELECT TABLE_USED_SPACE('SYSDBA','SPACE_TABLE');
        SELECT TABLE_USED_PAGES('SYSDBA','SPACE_TABLE');
      

• 管理索引

  索引是为了快速检索和定位数据行而创建的一种数据结构。索引是由表中索引列数据进行排序后的集合和指向这些值的物理标识（例如：ROWID 等聚集索引键）共同组成。

  • 索引分类

    • 聚集索引和非聚集索引

      • 聚集索引（又称为一级索引、主索引）：聚集索引就是按照聚集索引键构造一棵 B+ 树，表数据存储在 B+ 树叶子节点上，通过定位索引可直接在 B+ 树中找到数据。每一个表有且只有一个聚集索引。

      • 非聚集索引（又称为二级索引、辅助索引）：将二级索引列和聚集索引列共同存储在 B+ 树叶子节点上。

        //创建表格T1，并插入数据。
        CREATE TABLE T1 (ID int,NAME varchar(20),DEPARTMENT varchar(20));
        INSERT INTO T1 VALUES(1, 'Zhang San', 'A部');
        INSERT INTO T1 VALUES(2, 'Li Si', 'B部');
        INSERT INTO T1 VALUES(3, 'Wang Wu', 'C部');
        INSERT INTO T1 VALUES(4, 'Chen Liu', 'D部');
        //创建聚集索引，索引键为ID。
        CREATE CLUSTER INDEX C1 on T1(ID);
        //创建非聚集索引，索引键为NAME。
        CREATE INDEX S1 on T1(NAME);
        //使用聚集索引进行查找
        EXPLAIN SELECT * FROM  T1 WHERE ID=2;
        1   #NSET2: [1, 1, 112]
        2     #PRJT2: [1, 1, 112]; exp_num(4), is_atom(FALSE)
        3       #CSEK2: [1, 1, 112]; scan_type(ASC), C1(T1), scan_range[2,2]
        //使用非聚集索引进行查找
        EXPLAIN SELECT ID FROM  T1 WHERE NAME='Wang Wu';
        1   #NSET2: [1, 1, 64]
        2     #PRJT2: [1, 1, 64]; exp_num(2), is_atom(FALSE)
        3       #SSEK2: [1, 1, 64]; scan_type(ASC), S1(T1), scan_range['Wang Wu','Wang Wu']
        //先使用非聚集索引进行查找，再通过非聚集索引关联上聚集索引进行二次查找
        EXPLAIN SELECT DEPARTMENT FROM  T1 WHERE NAME='Chen Liu';
        1   #NSET2: [1, 1, 96]
        2     #PRJT2: [1, 1, 96]; exp_num(1), is_atom(FALSE)
        3       #BLKUP2: [1, 1, 96]; S1(T1)
        4         #SSEK2: [1, 1, 96]; scan_type(ASC), S1(T1), scan_range['Chen Liu','Chen Liu']
        

      

    • 功能索引

      • 从索引功能角度进行分类，可分为唯一索引、函数索引、位图索引、位图连接索引、全文索引、空间索引、数组索引、普通索引。
        • 唯一索引：索引数据根据索引键唯一；
        • 函数索引：包含函数/表达式的预先计算的值；
        • 位图索引：对低基数的列创建位图索引；
        • 位图连接索引：针对两个或者多个表连接的位图索引，主要用于数据仓库中；
        • 全文索引：在表的文本列上而建的索引。具体内容请参考第 18 章；
        • 空间索引：在空间数据上创建的索引，专用于 DMGEO 包中；
        • 数组索引：在一个只包含单个数组成员的对象列上创建的索引；
        • 普通索引：除了唯一索引、函数索引、位图索引、位图连接索引、全文索引、空间索引、数组索引以外的索引，均为普通索引。

    • 虚索引和实索引

      • 虚索引：创建 PRIMARY KEY 主键约束或 UNIQUE 唯一约束时，系统会自动创建一个相关的唯一索引。因为不需要用户创建，因此称为虚索引。
      • 实索引：虚索引以外的索引均为实索引。

    • 单列索引和复合索引

      • 单列索引：只有一个索引键的索引。
      • 复合索引：含有多个索引键的索引。

    • 全局索引和局部索引

      • 全局索引：全局索引是以整张表的数据为对象而建立的索引。指定 GLOBAL 关键字创建的索引即为全局索引。
        • 全局本地索引的命名规则为：INDEX+ 全局本地索引 ID_全局索引 ID
        • 全局索引具体分为两种：全局非分区索引和全局分区索引。只有 DMDPC 功能支持分区索引。
      • 局部索引：局部索引是在分区表的每个分区上创建的索引。未指定 GLOBAL 关键字创建的索引即为局部索引。
        • 子表局部索引的命名规则为：INDEX+ 子表局部索引 ID_局部索引 ID。

      

  • 管理索引的准则

    索引能使对应于表的 SQL 语句执行得更快。DM8 提供了几种最常见类型的索引，对不同场景有不同的功能。

    • 索引正确的列和表
      • 创建索引的情况：
        • 如果需要经常地检索大表中的少量的行，就为查询键创建索引；
        • 为了改善多个表的连接的性能，可为连接列创建索引；
        • 主键和唯一键自动具有索引，在外键上很多情况下也创建索引；
        • 小表不需要索引。
      • 选取索引列时考虑的情况：
        • 列中的值相对比较唯一 ；
        • 取值范围大，适合建立索引；
        • CLOB 和 TEXT 只能建立全文索引、BLOB 不能建立任何索引。
    • 性能
      • CREATE INDEX 语句中列的排序会影响查询的性能。通常，将最常用的列放在最前面。
    • 限制索引数量
      • 索引越多，修改表数据的开销就越大。需要针对表的使用情况进行索引限制，如果一个表主要仅用于读，则索引多就有好处；如果一个表经常被更新，则索引不宜多建。
    • 估计大小和存储参数
      • 创建索引之前先估计索引的大小能更好地促进规划和管理磁盘空间。可以用索引以及回滚段、重做日志文件的组合估计的大小来决定支持所期望的数据库所需的磁盘空间的大小。
      • 创建索引时，可以设置适当的存储参数，并改善使用该索引的应用的 I/O 性能。
    • 指定表空间
      • 将表及其索引放在不同的表空间（在不同磁盘上）产生的性能比放在相同的表空间更好，因为这样做减少了磁盘竞争。

  • 创建索引

    • 显示地创建（create index）

      • 例：在emp表的ename列上创建一个名为emp_ename的索引

          CREATE INDEX emp_ename ON emp(ename)
            STORAGE (
            INITIAL 	50,
            NEXT 	50,
            ON 		USERS);
        

        • 为该索引显式地指定了几个存储设置和一个表空间（如果没给指定选项，则 INITIAL 和 NEXT 等存储选项会自动使用表空间的默认存储选项）

      • 创建聚集索引

        • 每一个普通表都有且仅有一个聚集索引，数据都通过聚集索引键排序，根据聚集索引键可以快速查询任何记录

            CREATE CLUSTER INDEX clu_emp_name ON emp(ename);
          

        • 约束条件：

          • 每张表中只允许有一个聚集索引，如果之前已经指定过 CLUSTER INDEX 或者指定了 CLUSTER PK，则用户新建立 CLUSTER INDEX 时系统会自动删除原先的聚集索引。但如果新建聚集索引时指定的创建方式（列，顺序）和之前的聚集索引一样，则会报错；
          • 指定 CLUSTER INDEX 操作需要重建表上的所有索引，包括 PK 索引；
          • 删除聚集索引时，缺省以 ROWID 排序，自动重建所有索引；
          • 若聚集索引是默认的 ROWID 索引，不允许删除；
          • 聚集索引不能应用到函数索引中；
          • 不能在列存储表上新建/删除聚集索引；
          • 建聚集索引语句不能含有 partition_clause 子句；
          • 在临时表上增删索引会使当前会话上临时 b 树数据丢失;
          • 不支持在含有多媒体类型的表上新建聚集索引。

      • 创建唯一索引

        • 唯一索引可以保证表上不会有两行数据在键列上具有相同的值（UNIQUE）

             CREATE UNIQUE INDEX dept_unique_index ON dept (dname)
             STORAGE (ON users);
          

      • 创建基于函数的索引

        • 促进限定函数或表达式的返回值的查询，该函数或表达式的值被预先计算出来并存储在索引中。

             CREATE INDEX idx ON example_tab(column_a + column_b);
             SELECT * FROM example_tab WHERE column_a + column_b < 10;
          

          • 这个就是建立在example_tab表上的a列+b列小于10的索引，查询时使用范围扫描，就不会重复计算

        • 约束条件：

          • 表达式不允许为时间间隔类型；
          • 表达式中不允许出现半透明加密列；
          • 函数索引表达式的长度理论值不能超过 816 个字符（包括生成后的指令和字符串）；
          • 函数索引不能为 CLUSTER 或 PRIMARY KEY 类型；
          • 表达式不支持集函数和不确定函数，不确定函数为每次执行得到的结果不确定，系统中不确定函数包括：RAND、SOUNDEX、CURDATE、CURTIME、CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP、GETDATE、NOW、SYSDATE、CUR_DATABASE、DBID、EXTENT、PAGE、SESSID、UID、USER、VSIZE、SET_TABLE_OPTION、SET_INDEX_OPTION、UNLOCK_LOGIN、CHECK_LOGIN、GET_AUDIT、CFALGORITHMSENCRYPT、SF_MAC_LABEL_TO_CHAR、CFALGORITHMSDECRYPT、BFALGORITHMSENCRYPT、SF_MAC_LABEL_FROM_CHAR、BFALGORITHMSDECRYPT、SF_MAC_LABEL_CMP；
          • 快速装载不支持含有函数索引的表；
          • 若函数索引中要使用用户自定义的函数，则函数必须是指定了 DETERMINISTIC 属性的确定性函数；
          • 若函数索引中使用的确定性函数发生了变更或删除，用户需手动重建函数索引；
          • 若函数索引中使用的确定性函数内有不确定因素，会导致前后计算结果不同的情况。在查询使用函数索引时，使用数据插入函数索引时的计算结果为 KEY 值；修改时可能会导致在使用函数索引过程中出现根据聚集索引无法在函数索引中找到相应记录的情况，对此进行报错处理；
          • 临时表不支持函数索引。

      • 创建位图索引（bitmap index）

          CREATE BITMAP INDEX S1 ON PURCHASING.VENDOR (VENDORID);
        

        • 约束条件：
          • 支持普通表、堆表和水平分区表创建位图索引；
          • 不支持对大字段创建位图索引；
          • 不支持对计算表达式列创建位图索引；
          • 不支持在 UNIQUE 列和 PRIMARY KEY 上创建位图索引；
          • 不支持对存在 CLUSTER KEY 的表创建位图索引；
          • 仅支持单列或者不超过 63 个组合列上创建位图索引；
          • MPP 环境下不支持位图索引的创建；
          • 不支持快速装载建含有位图索引的表；
          • 不支持全局位图索引；
          • 包含位图索引的表不支持并发的插入、删除和更新操作。

      • 创建位图连接索引

        • 位图连接索引是一种提高通过连接实现海量数据查询效率的有效方式，主要用于数据仓库环境中。

            create bitmap index SALES_CUSTOMER_NAME_IDX   on SALES.SALESORDER_HEADER(SALES.CUSTOMER.PERSONID)
            from   SALES.CUSTOMER, SALES.SALESORDER_HEADER  where  SALES.CUSTOMER.CUSTOMERID = SALES.SALESORDER_HEADER.CUSTOMERID;
          

    • 隐式地创建索引

      • 隐式创建方式只支持创建唯一索引。DM8 通过在唯一键或主键上创建一个唯一索引来在表上实施 UNIQUE KEY 或 PRIMARY KEY 完整性约束。

        DROP TABLE T1;
        CREATE TABLE T1(C1 INT, C2 INT, C3 INT);
        //创建一个名为PK_EMP_NAME的PRIMARY KEY约束
        ALTER TABLE T1 ADD CONSTRAINT PK_EMP_NAME PRIMARY KEY (C1);
        //经查看，为约束创建了一个名为INDEX33555548的索引
        SELECT INDEX_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME='PK_EMP_NAME';  
        行号       INDEX_NAME
        ---------- -------------
        1          INDEX33555548
        //继续查看，INDEX33555548索引为唯一索引
        SELECT UNIQUENESS FROM ALL_INDEXES WHERE INDEX_NAME='INDEX33555548';
        行号       UNIQUENESS
        ---------- ---------- ----------
        1            UNIQUE
        

  • 使用索引

    • 使用操作explain进行查看

      • 数据准备：（一个表T2，聚集索引S21，普通索引S22、S23、S24、S25）

        CREATE TABLE T2 (ID int,NAME varchar(20),DEPARTMENT varchar(20),SALARY INT);
        CREATE CLUSTER INDEX S21 on T2(ID); //聚集索引
        CREATE INDEX S22 on T2(NAME,DEPARTMENT);
        CREATE INDEX S23 on T2(NAME);
        CREATE INDEX S24 on T2(DEPARTMENT);
        CREATE INDEX S25 on T2(SALARY);
        

      • 例1：使用聚集索引

        EXPLAIN SELECT SALARY FROM T2 WHERE ID=10;
        

      • 使用非聚集索引S22

        • SELECT 查询项和 WHERE 子句过滤项需包含 S22 的全部索引键 NAME 和 DEPARTMENT

        EXPLAIN SELECT DEPARTMENT FROM T2 WHERE NAME LIKE'%Zhang' AND DEPARTMENT='A部门';
        
        或
        
        EXPLAIN SELECT DEPARTMENT FROM T2 WHERE NAME='Zhang San';
        

  • 重建索引

    • DM8 提供的重建索引的系统函数为：

        SP_REBUILD_INDEX(SCHEMA_NAME varchar(256), INDEX_ID int);
      

    • 使用说明：

      • 水平分区子表，临时表和系统表上建的索引不支持重建
      • 虚索引和聚集索引不支持重建

  • 删除索引（DROP INDEX）

    • 必须包含在用户的模式中或用户必须具有 DROP ANY INDEX 数据库权限

        DROP INDEX emp_ename;
      

  • 查看索引信息（INDEXDEF）

    • 通过 INDEXDEF 系统函数查看索引的定义

        INDEXDEF(INDEX_ID int, PREFLAG int);
      

      • INDEX_ID 为索引 ID，PREFLAG 表示返回信息中是否增加模式名前缀

• 管理触发器

  • 概述

    • 触发器是一种特殊的存储过程，它在创建后就存储在数据库中，如果用户在这个表上执行了某个 DML 操作（INSERT、DELETE、UPDATE），触发器就被激发执行。（当然也要设置了才能触发）
    • 触发器功能：
      • 可以对表自动进行复杂的安全性、完整性检查；
      • 可以在对表进行 DML 操作之前或者之后进行其它处理；
      • 进行审计，可以对表上的操作进行跟踪；
      • 实现不同节点间数据库的同步更新。
    • DM提供三种类型的触发器：
      • 表级触发器：基于表中的数据进行触发；
      • 事件触发器：基于特定系统事件进行触发；
      • 时间触发器：基于时间而进行触发。

  • 触发器的使用

    • 首先需要确认触发器应该用于何种场景，考虑以下方面：

      • 触发器应该建立在哪个表/视图之上；
      • 触发器应该对什么样的 DML 操作进行响应；
      • 触发器在指定的 DML 操作之前激发还是在之后激发；
      • 对每次 DML 响应一次，还是对受 DML 操作影响的每一行数据都响应一次。

    • 创建语法

      	CREATE [OR REPLACE] TRIGGER 触发器名[WITH  ENCRYPTION]
      		BEFORE|AFTER|INSTEAD OF
      		DELETE|INSERT|UPDATE [OF 列名]
      		ON 表名
      		[FOR EACH ROW [WHEN 条件]]
      		BEGIN
      			DMSQL程序语句
      		END;
      

    • 删除触发器（使用DROP，最叼的操作之一）

      DROP TRIGGER [IF EXISTS] 触发器名;
      

    • 如果触发器失效，需要使用命令再次让他起作用

      ALTER TRIGGER 触发器 ENABLE;
      

  • 表级触发器

    • 基于表中数据的触发器，它通过针对相应表对象的插入/删除/修改等 DML 语句触发
      • 触发动作
        • 三种数据操作命令，即 INSERT、DELETE 和 UPDATE 操作。
      • 触发级别
        • 根据触发器的级别可分为元组级 (也称行级)和语句级
          • 元组级触发器：对触发命令所影响的每一条记录都激发一次，常用于数据审计、完整性检查等应用中。
          • 语句级触发器：对每个触发命令执行一次，一般用于对表上执行的操作类型引入附加的安全措施。
      • 触发时机
        • 触发时机通过两种方式指定
          • 一是通过指定 BEFORE 或 AFTER 关键字，选择在触发动作之前或之后运行触发器
          • 二是通过指定 INSTEAD OF 关键字，选择在动作触发的时候，替换原始操作

  • 事件触发器

    • 此触发器并不依赖于某个表，而是基于特定系统事件触发的，通过指定 DATABASE 或某个 SCHEMA 来表示事件触发器的作用区域
    • 可以触发的两类事件：
      • DDL 事件，包括 CREATE、ALTER、DROP、GRANT、REVOKE 以及 TRUNCATE；
      • 系统事件，包括 LOGIN/LOGON、LOGOUT/LOGOFF、AUDIT、NOAUDIT、BACKUP DATABASE、RESTORE DATABASE、TIMER、STARTUP、SHUTDOWN 以及 SERERR（即执行错误事件）。

  • 时间触发器

    • 一种特殊的事件触发器，时间触发器的特点是用户可以定义在任何时间点、时间区域、每隔多长时间等等的方式来激发触发器。

    • 创建语法

      	CREATE [OR REPLACE] TRIGGER 触发器名 WITH ENCRYPTION
      		AFTER TIMER ON DATABASE
      		{时间定义语句}
      		BEGIN
      			执行语句
      		END;
      

• 管理视图、序列和同义词

  • 管理视图

    • 视图是从一个或几个基表（或视图）导出的表，但它是一个虚表，即数据字典中只存放视图的定义（由视图名和查询语句组成），而不存放对应的数据，这些数据仍存放在原来的基表中

    • 从用户的角度来讲，视图就像一个窗口，透过它可以看到数据库中用户感兴趣的数据和变化。

    • 例：根据用户创建不同的视图

        CREATE VIEW normal_view AS SELECT name FROM person;
        CREATE VIEW special_view AS SELECT name, sex, email, phone FROM person;
      

    • 删除视图

      DROP VIEW [IF EXISTS] [<模式名>.]<视图名> [RESTRICT | CASCADE];
      

  • 管理序列

    • 通过使用序列，多个用户可以产生和使用一组不重复的有序整数值。
    • 当一个序列第一次被查询调用时，它将返回一个预定值，该预定值就是在创建序列时所指定的初始值；在随后的每一次查询中，序列将产生一个按其指定的增量增长的值（可以是正值，可以是负值，默认为1）。

  • 管理同义词

    • 相当于模式对象的别名，起着连接数据库模式对象和应用程序的作用。通过掩盖一个对象真实的名字和拥有者，并且对远程分布式的数据库对象给予了位置透明特性以此来提供了一定的安全性。

    • 创建同义词

      CREATE [OR REPLACE] [PUBLIC] SYNONYM [IF NOT EXISTS] [<模式名>.]<同义词名> FOR [<模式名>.]<对象名>
      

      • < 同义词名 > 指被定义的同义词的名字；
      • < 对象名 > 指示同义词替换的对象。

    • 删除同义词

        DROP [PUBLIC] SYNONYM [IF EXISTS] <同义词名>
      

      • < 同义词名 > 指被定义的同义词的名字

• 模式对象的常规管理

  • 概念

    • 用户的模式指的是用户账号拥有的对象集，在概念上可将其看作是包含表、视图、索引和权限定义的对象。一个用户可以创建多个模式，一个模式中的对象（表、视图等）可以被多个用户使用。
    • 采用模式的原因：
      • 允许多个用户使用一个数据库而不会干扰其它用户；
      • 把数据库对象组织成逻辑组，让它们更便于管理；
      • 第三方的应用可以放在不同的模式中，这样可以避免和其它对象的名字冲突。模式类似于操作系统层次的目录，只不过模式不能嵌套。

  • 单个操作中创建多个模式对象

    • 在 DM 数据库中，使用 CREATE SCHEMA 语句就可以创建一个空的模式对象，也可以在创建模式时，同时创建多个模式对象。

          CREATE SCHEMA TEST
              CREATE SEQUENCE ADDRESS_SEQ INCREMENT BY 1 
              CREATE TABLE ADDRESS(
                    ADDRESSID INT,
                      ADDRESS1 VARCHAR(60) NOT NULL,
                      ADDRESS2 VARCHAR(60),
                      CITY VARCHAR(30) NOT NULL,
                      POSTALCODE VARCHAR(15) NOT NULL)
              CREATE TABLE ADDRESS_TYPE(
                  ADDRESS_TYPEID INT PRIMARY KEY,
                      NAME VARCHAR(50) NOT NULL);
      

      • 上面在创建模式test时，创建了属于test模式的一个序列address_seq和两张表address、address_type

  • 重命名模式对象

    • 重命名一个模式对象，那么这个模式对象必须在指定的模式里面。可以采用以下方式：

      • 删除原有的模式对象，重新创建；

        • 如果采用的是删除并重建模式对象的方式来重命名模式对象，所有的基于这个模式对象的授权都将失效。（慎用）

      • 使用 ALTER … RENAME 语句（table）。

        • 如果使用的是 ALTER …RENAME 语句来重命名模式对象，所有基于原模式对象的权限将转移到重命名后的模式对象上，不需要重新授权。

  • 启用和禁用触发器

    • 设置触发器之前，用户需要满足的条件：

      • 拥有该触发器
      • 有使用ALTER ANY TRIGGER的权限

    • 使用ALTER TRIGGER设置触发器的状态

    • 启用触发器：

        ALTER TRIGGER addr_trig ENABLED;
      

    • 禁用触发器：

        ALTER TRIGGER addr_trig DISABLED;
      

  • 管理完整性约束

    完整性约束规则，限制表中一个或者多个列的值。

    • 完整性约束状态

      • 用户可以指定衣蛾约束条件是否启用。

        • 禁用约束：可以将违反约束规则的数据插入到表中。以下三种情况可以考虑禁用约束来提升性能：

          • 导入大量的数据到一张表中；
          • 做批处理操作并对一张表做大规模修改时；
          • 导入导出一张表。

        • 启用约束：在启用完整性约束的情况下，不满足约束规则的行是不能插入到表中的。

    • 定义完整性约束

      • 下面CREATE TABLE 和 ALTER TABLE 语句在定义的时候就启用完整性约束：

            CREATE TABLE t_con (
            id NUMBER(5) CONSTRAINT t_con_pk PRIMARY KEY);
            ALTER TABLE t_con
            ADD CONSTRAINT t_con_pk PRIMARY KEY (id);
        

        • 就是使用参数constraint来创建约束

    • 修改或删除现有的完整性约束

      • 用户可以使用 ALTER TABLE 语句来启用、禁止、删除一个约束。

        • 禁用已启用的约束

            ALTER TABLE t_con DISABLE CONSTRAINT t_con_pk;
          

          • 当有外键引用 UNIQUE 或者 PRIMARY KEY 列时，用户不能禁用 UNIQUE 或者 PRIMARY KEY 约束

        • 删除约束

            ALTER TABLE t_con DROP CONSTRAINT t_con_pk;
          

          • 如果有外键引用约束（UNIQUE 或者 PRIMARY KEY）时，在删除约束时必须加上 CASCADE 参数，否则不能删除。

    • 查看约束信息

      • 可以在系统表 SYSOBJECTS 和 SYSCONS 中查询约束的信息。

        • 在sysobjects系统表中查找约束名为t_con_pk的信息：

            SELECT * FROM SYSOBJECTS WHERE NAME='T_CON_PK';
          

        • 查找所有约束信息

            SELECT * FROM SYSOBJECTS WHERE TYPE$='CONS';
          

  • 管理对象名称解析

    • DM 数据库对象名字是由两部分组成，之间用点号隔开。对象分为两大类：一是 SQL 语句中的对象；二是 DMSQL 程序中的对象。

      • SQL语句对象

        • 如果只有一个名字，而没有点号。
          在当前模式下寻找是否存在相同名字的对象，如果找到，则返回；否则报错。
        • 如果有点号，首先检测对象名的第一部分，如在 TEST.ADDRESS 中，TEST 就是第一部分。步骤如下：
          1. 寻找哪一个模式的名字和第一部分相同，如果找到，以此模式进行步骤 2)；否则，以当前模式进行步骤 2）。
          2. 在模式中需找是否有与对象名第二部分同名的对象，如果找到且待解析对象名只有两个部分，则返回；如果找到但待解析对象名多于两个部分，则转步骤 3)；否则报错。
          3. 在模式中查找包含在前一个对象中并且和待解析对象名当前解析部分相同名字的对象，如果找到，循环步骤 3)，直至所有的部分检测结束后再返回；如果没有找到，则直接报错。如 TEST.SCHOOL.CLASS.STUDENT，在 TEST 模式下中的 SCHOOL 对象中寻找 CLASS 的对象，然后在 CLASS 对象中寻找 STUDENT 对象。

      • DMSQL程序对象

        • 如果只有一个名字，而没有点号。
          在当前模式下寻找是否存在相同名字的对象，如果找到，则返回；否则报错。这一点与 SQL 语句对象一致。
        • 如果有点号，首先检测对象名的第一部分，如在 TEST.ADDRESS 中，TEST 就是第一部分。步骤如下：
          1. 寻找当前模式下是否存在包名与第一部分相同，如果找到，进行步骤 2），否则进行步骤 4）。
          2. 在步骤 1 的包中查找是否有与对象名第二部分同名的对象，若找到且待解析对象名只有两个部分，则返回。若找到但待解析的对象名多于两个，则进行步骤 3）。若包中不存在与第二部分同名的对象则报错。
          3. 查找包含在前一个对象中并且和待解析对象名当前解析部分相同名字的对象，如果找到，循环步骤 3)，直至所有的部分检测结束后再返回；如果没有找到，则直接报错。
          4. 查找是否存在模式名与对象名的第一部分相同，如果找到，以此模式进行步骤 5），否则以当前模式进行步骤 5）。
          5. 在模式中需找是否有与对象名第二部分同名的对象，如果找到且待解析对象名只有两个部分，则返回；如果找到但待解析对象名多于两个部分，则转步骤 6)；否则报错。
          6. 在模式中查找包含在前一个对象中并且和待解析对象名当前解析部分相同名字的对象，如果找到，循环步骤 6)，直至所有的部分检测结束后再返回；如果没有找到，则直接报错。如 TEST.SCHOOL.CLASS.STUDENT，在 TEST 模式下中的 SCHOOL 对象中寻找 CLASS 的对象，然后在 CLASS 对象中寻找 STUDENT 对象。

  • 显示有关模式对象的信息

    • DM数据库中模式对象的信息主要记录在 SYSOBJECTS 系统表中。可通过以下语句进行查询

        SELECT * FROM SYSOBJECTS WHERE TYPE$ = 'SCHOBJ' OR TYPE$ = 'TABOBJ';
      

      • 但是，SYSOBJECTS 系统表并不能将模式对象的所有信息存储起来。

总结

• 以上是对系统管理员进行更细一步的划分以及管理员应该具备的基础管理信息。

参考

• DM 系统管理员 | 达梦技术文档 (dameng.com)