达梦(DM)数据库管理表

简介: 达梦(DM)数据库表管理

表是数据库中数据存储的基本单元,是对用户数据进行读和操纵的逻辑实体,表由列和行组成,每一行代表一个单独的记录。

管理表

管理表的准则

表的存储空间上限

DM数据库支持 在创建表时指定 SPACE LIMIT 子句,可以对表的存储空间指定上限。DM 支持对表的存储空间指定大小,单位是 MB,即表的大小可由管理员指定,便于表的规模管理。

表的存储位置

创建表时,在 STORAGE 子句中,可对表指定存储的表空间。如果没有指定,则该表将创建在用户的默认表空间中。

创建表

创建普通表

在所属模式中创建新表,需要有 CREATE TABLE 数据库权限;而要在其他用户的模式中创建新表,则需要有 CREATE ANY TABLE 数据库权限。创建表时,应当为表指定一个表空间,否则,表将在 MAIN 创建。

指定表的聚集索引

DM 提供三种方式供用户指定聚集索引键:

  1. CLUSTER PRIMARY KEY:指定列为聚集索引键,并同时指定为主键,称为聚集主键;
  2. CLUSTER KEY:指定列为聚集索引键,但是是非唯一的;
  3. CLUSTER UNIQUE KEY:指定列为聚集索引键,并且是唯一的。
CREATETABLE  STUDENT(    STUNO     INT         CLUSTER PRIMARY KEY,    STUNAME   VARCHAR(15)NOTNULL,    TEANO     INT,    CLASSID   INT);

查询建表

创建一个与已有表相同的新表,可以使用 CREATE TABLE AS SELECT(CTAS)命令

CREATETABLE NEW_EMP
ASSELECT*FROM EMPLOYEE;

更改表

想更改的表如果在所属的模式中,用户必须具有 ALTER TABLE 数据库权限;若在其他模式中,用户必须有 ALTER ANY TABLE 的数据库权限。

用户可以对数据库中的表作如下修改:

  1. 添加或删除列,或修改现有的列的定义(列名、数据类型、长度、默认值)。
  2. 添加、修改或删除与表相关的完整性约束;
  3. 重命名一个表;
  4. 启动或停用与表相关的完整性约束;
  5. 启动或停用与表相关的触发器;
  6. 修改表的 SPACE LIMIT;
  7. 增删自增列。

删除表

当一个表不再使用时,可以将其删除.一般情况下,普通用户只能删除自己模式下的表。若要删除其他模式下的表,则必须具有 DROP ANY TABLE 数据库权限。

删除表

DROPTABLE employee;

删除不存在的表会报错,若指定 IF EXISTS 关键字,删除不存在的表,不会报错

DROPTABLE IF EXISTS employee;

如果要删除的表被其他表引用,即其他表的外键引用了表的任何主键或唯一键,则需要在 DROP TABLE 语句中包含 CASCADE 选项

DROPTABLE employee CASCADE;

清空表

当表的数据不再使用时,需要删除表的所有行,即清空该表

使用 DELETE 语句能删除表中的行

DELETEFROM employee;

使用 DELETE 清空表,当表有很多行时,会消耗很多系统资源。因为,DELETE 操作需要 CPU 时间,并且会产生大量的 REDO 日志和 UNDO 记录。另外,如果表上关联了元组级触发器,每删除一行,就会启动一次触发器。这都需要大量的系统资源。

使用 DROP 和 CREATE

使用 DROP 删除一个表,然后创建一个同名的表,也可以达到清空表的效果

DROPTABLE employee;CREATETABLE employee();

使用 TRUNCATE 语句能删除表中的所有行

TRUNCATE TABLE employee;

TRUNCATE 是一个 DDL 语句,不会产生任何回滚信息。执行 TRUNCATE 会立即提交,而且不能回滚。

查看表定义

创建表后,可以通过 SP_TABLEDEF 系统过程查看表的定义

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

查看表空间使用

DM 支持查看表的空间使用情况

  1. TABLE_USED_SPACE:已分配给表的页面数;
  2. TABLE_USED_PAGES:表已使用的页面数。
SELECT TABLE_USED_SPACE('SYSDBA','EMPLOYEE');SELECT TABLE_USED_PAGES('SYSDBA','EMPLOYEE');

表索引

这里主要说一下常用的索引,从物理存储角度进行分类,可分为聚集索引和非聚集索引

● 聚集索引(又称为一级索引、主索引):聚集索引就是按照聚集索引键构造一棵 B+ 树,表数据存储在 B+ 树叶子节点上,通过定位索引可直接在 B+ 树中找到数据。每一个表有且只有一个聚集索引。
● 非聚集索引(又称为二级索引、辅助索引):将二级索引列和聚集索引列共同存储在 B+ 树叶子节点上。如果查找非聚集索引键值或聚集索引键值可直接在 B+ 树中找到;如果查找索引键值以外的数据,则需要回到一级索引中进行查找。每一个表可以有多个非聚集索引。

下面举个例子说明一下,创建表并插入数据

--创建表格T1,并插入数据。CREATETABLE T1 (ID int,NAME varchar(20),DEPARTMENT varchar(20));INSERTINTO T1 VALUES(1'Zhang San','A部');INSERTINTO T1 VALUES(2'Li Si','B部');INSERTINTO T1 VALUES(3'Wang Wu','C部');INSERTINTO T1 VALUES(4'Chen Liu','D部');

查看表结构

SP_TABLEDEF('SYSDBA','T1');

image.png

创建索引后查看表结构

--创建聚集索引,索引键为ID。CREATE CLUSTER INDEX C1 on T1(ID);--创建非聚集索引,索引键为NAME。CREATE INDEX S1 on T1(NAME);SP_TABLEDEF('SYSDBA','T1');

image.png

使用聚集索引查找

--使用聚集索引进行查找EXPLAIN SELECT*FROM  T1 WHERE ID=2;

image.png

使用非聚集索引查找

EXPLAIN SELECT ID FROM  T1 WHERE NAME='Wang Wu';

image.png

先使用非聚集索引进行查找,再通过非聚集索引关联上聚集索引进行二次查找

EXPLAIN SELECT*FROM  T1 WHERE NAME='Chen Liu';

image.png

一般情况下,在插入或装载了数据后,为表创建索引会更加有效率。如果在装载数据之前创建了一个或多个索引,那么在插入每行时 DM8 都必须更改和维护每个索引,使得插入效率降低

索引准则

包括以下准则:

  1. 如果需要经常地检索大表中的少量的行,就为查询键创建索引;
  2. 为了改善多个表的连接的性能,可为连接列创建索引;
  3. 主键和唯一键自动具有索引,在外键上很多情况下也创建索引;
  4. 小表不需要索引。

索引列选取规则:

  1. 列中的值相对比较唯一 ;
  2. 取值范围大,适合建立索引;
  3. CLOB 和 TEXT 只能建立全文索引、BLOB 不能建立任何索引。

其他准则

如果查询中有多个字段组合定位,则不应为每个字段单独创建索引,而应该创建一个组合索引。当两个或多个字段都是等值查询时,组合索引中各个列的前后关系是无关紧要的。但是如果是非等值查询时,要想有效利用组合索引,则应该按等值字段在前,非等值字段在后的原则创建组合索引,查询时只能利用一个非等值的字段。

一个表可以有任意数量的索引。但是,索引越多,修改表数据的开销就越大,因此索引不是越多越好,而应该适当。

创建索引

显式地创建索引

可以用 CREATE INDEX 语句显式地创建索引

CREATE INDEX idx_deptid ON test1(dept_id);

image.png

创建索引时未指定表空间的话,默认与表在相同的表空间下

image.png

指定存储设置及表空间创建索引

--指定表空间CREATE INDEX idx_user ON test1(user_name) STORAGE ( INITIAL 50, NEXT 50,ON TEST2023);

image.png

查看指定的表空间索引

image.png

说明:

如果表及其索引使用相同的表空间 能更方便地对数据库进行管理(如表空间或文件备份)或保证应用的可用性,因为所有有关的数据总是在一起联机。然而,将表及其索引放在不同的表空间(在不同磁盘上)产生的性能比放在相同的表空间更好,因为这样做减少了磁盘竞争。但是将表及其索引放在不同的表空间时,如果一个表上某索引所在的表空间脱机了,则涉及这张表的 SQL 语句可能由于执行计划仍旧需要使用被脱机的索引而不能成功执行。


其他创建索引语句

--创建聚集索引CREATE CLUSTER INDEX clu_emp_name ON emp(ename);--创建唯一索引 STORAGE (ON users) 指定表空间,可以不加,默认与表在相同表空间CREATE UNIQUE INDEX dept_unique_index ON dept (dname) STORAGE (ON users);

聚集索引的约束条件:

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

该索引是建立在 column_a + column_b 之上的,所以优化器可以为该查询使用范围扫描。优化器根据该索引计算查询代价,如果代价最少,优化器就会选择该函数索引,column_a + column_b 就不会重复计算

函数索引约束条件:参考文档

表达式不允许为时间间隔类型;
表达式中不允许出现半透明加密列;
函数索引表达式的长度理论值不能超过 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 值;修改时可能会导致在使用函数索引过程中出现根据聚集索引无法在函数索引中找到相应记录的情况,对此进行报错处理;
临时表不支持函数索引。

创建位图索引以及创建位图连接索引也可参考上述文档,这里不怎么用到,不再详解。

使用索引

创建测试表

CREATETABLE 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);

image.png

使用聚集索引S21查询

EXPLAIN SELECT SALARY FROM T2 WHERE ID=10;

image.png

使用非聚集索引S22查询

EXPLAIN SELECT DEPARTMENT FROM T2 WHERE NAME='Zhang San';

image.png

先使用非聚集索引S23再使用聚集索引

EXPLAIN SELECT*FROM T2 WHERE NAME='Zhang San';

image.png

首先,在 SSEK 中使用 S23 非聚集索引进行扫描定位,得到 NAME;

其次,因为 SELECT * 中查询项太多,需要在 BLKUP2 中通过 S23 回到聚集索引上进行二次查找,得到*中的其它数据。

重建索引

当一个表经过大量的增删改操作后,表的数据在物理文件中可能存在大量碎片,从而影响访问速度。另外,当删除表的大量数据后,若不再对表执行插入操作,索引所处的段可能占用了大量并不使用的簇,从而浪费了存储空间。

可以使用重建索引来对索引的数据进行重组,使数据更加紧凑,并释放不需要的空间,从而提高访问效率和空间效率

--SCHEAM_NAME 为索引所在的模式名,INDEX_ID 为索引 IDSP_REBUILD_INDEX(SCHEMA_NAME varchar(256), INDEX_ID int);--  SP_REBUILD_INDEX('SYSDBA', 1547892);

删除索引

删除索引操作

DROP INDEX emp_ename;--删除不存在的索引会报错。若指定 IF EXISTS 关键字,删除不存在的索引,不会报错DROP INDEX IF EXISTS emp_ename;

不能直接删除与已启用的 UNIQUE KEY 键或 PRIMARY KEY 键约束相关的索引。要删除一个与约束相关的索引,必须停用或删除该约束本身。

相关文章
|
17天前
|
数据库
【赵渝强老师】达梦数据库实例的状态
达梦数据库实例包含NORMAL、PRIMARY和STANDBY三种模式,以及MOUNT、OPEN和SUSPEND三种状态。模式之间可在MOUNT状态下相互转换,不同状态与模式适用于数据库的启动、配置及运行需求。
|
6月前
|
SQL 数据库 Docker
【赵渝强老师】在Docker中运行达梦数据库
本文介绍了在Docker容器中部署达梦数据库(DM 8)的具体步骤,包括创建文件夹、下载安装包、导入镜像、启动容器、登录数据库及查看状态等操作。同时,通过视频讲解辅助理解。文中还分析了将数据库服务容器化的潜在问题,如数据安全性、硬件资源争用、网络带宽占用和额外隔离带来的挑战,指出数据库服务在生产环境中可能不适合容器化的原因。
362 4
【赵渝强老师】在Docker中运行达梦数据库
|
6月前
|
SQL 缓存 分布式数据库
【赵渝强老师】达梦数据库的线程结构
达梦数据库采用单进程、多线程结构,利用对称服务器架构实现高效资源利用与可扩展性。其核心线程类型包括监听线程(管理客户端连接)、I/O线程(处理数据页读写)、工作线程(执行数据操作)、调度线程(定时任务管理)和日志刷新线程(确保REDO日志刷盘)。通过合理分工与同步机制,达梦数据库实现了高性能与稳定性。视频讲解进一步详细介绍了各线程的功能与协作方式。
163 1
【赵渝强老师】达梦数据库的线程结构
|
6月前
|
存储 SQL NoSQL
【赵渝强老师】达梦数据库的逻辑存储结构
本文介绍了达梦数据库的存储结构,包括逻辑和物理存储两部分。逻辑存储结构由数据库(Database)、表空间(Tablespaces)、段(Segments)、簇(Cluster)和页(Page)组成。数据库是最大逻辑单元,包含所有表、索引等;表空间由数据文件组成,用于存储对象;段由簇构成,簇包含连续的数据页;页是最小存储单元。文中还提供了查询表空间、段和页大小的SQL语句,并附有视频讲解和示意图。
254 7
|
6月前
|
存储 SQL 数据库
【赵渝强老师】达梦数据库的数据库对象
达梦数据库包含基本与复杂两大类数据库对象。基本对象如表、索引、视图、序列和同义词,通过单一DDL语句创建和管理。表是数据存储核心,支持多种数据类型;索引提升查询速度,常见类型包括聚集、唯一、函数等索引;视图提供虚表功能;序列生成有序整数;同义词简化对象访问。复杂对象包括存储过程、函数和触发器,需用DMSQL语言开发,适用于更复杂的业务逻辑处理。文中通过实例详细介绍了各类对象的创建与使用方法。
319 3
|
6月前
|
SQL 存储 运维
【赵渝强老师】达梦数据库的客户端工具
本文介绍了达梦数据库的多种工具及其使用方法,包括disql交互式查询工具、数据库配置助手DBCA、DM管理工具、DM服务查看器、DM控制台工具、DM数据迁移工具、DM性能监视工具以及DM审计分析工具。每种工具的功能和操作步骤均有详细说明,并配有相关图片和代码示例,帮助用户更好地理解和使用这些工具,提升数据库管理和维护效率。
653 1
|
6月前
|
SQL 存储 数据库
【赵渝强老师】达梦数据库的归档模式
本文介绍了达梦数据库备份与恢复中重做日志文件的作用,重点讲解了归档模式的必要性及其配置方法。文章分析了非归档模式可能导致的数据丢失问题,并推荐使用归档模式以保障数据一致性和完整性。归档模式分为本地归档和远程归档:本地归档将重做日志存储在本地,而远程归档适用于集群环境,确保所有节点拥有完整日志。文中还详细展示了如何通过SQL命令开启归档模式,包括切换状态、设置路径及验证配置等步骤,并附有视频教程辅助理解。
351 1
|
6月前
|
SQL 存储 缓存
【赵渝强老师】达梦数据库的内存结构
本文介绍了达梦数据库管理系统的内存结构,包括内存池、缓冲区、排序区和哈希区。内存池分为共享内存池和运行时内存池,能够提高内存申请与释放效率,并便于监控内存使用情况。缓冲区涵盖数据缓冲区、日志缓冲区、字典缓冲区和SQL缓冲区,用于优化数据读写和查询性能。排序区和哈希区分别提供排序和哈希连接所需的内存空间,通过合理配置参数可提升系统效率。文内附有具体配置示例及视频讲解,帮助用户深入理解达梦数据库的内存管理机制。
168 0
|
6月前
|
存储 SQL 安全
【赵渝强老师】达梦数据库的物理存储结构
本文介绍了达梦数据库的存储结构及各类物理文件的作用。达梦数据库通过逻辑和物理存储结构管理数据,包含配置文件(如dm.ini、sqllog.ini)、控制文件(dm.ctl)、数据文件(*.dbf)、重做日志文件(*.log)、归档日志文件、备份文件(*.bak)等。配置文件用于功能设置,控制文件记录数据库初始信息,数据文件存储实际数据,重做日志用于故障恢复,归档日志增强数据安全性,备份文件保障数据完整性,跟踪与事件日志辅助问题分析。这些文件共同确保数据库高效、稳定运行。
281 0
|
6月前
|
存储 SQL 并行计算
【赵渝强老师】达梦数据库MPP集群的架构
达梦数据库提供大规模并行处理(MPP)架构,以低成本实现高性能并行计算,满足海量数据存储和复杂查询需求。DM MPP采用完全对等无共享体系,消除主节点瓶颈,通过多节点并行执行提升性能。其执行流程包括主EP生成计划、分发任务、各EP并行处理及结果汇总返回。为确保高可用性,建议结合数据守护部署。
160 0