一、CASE_SENSITIVE参数1.1 参数概述CASE_SENSITIVE是达梦数据库创建时指定的核心参数用于控制标识符如表名、列名等数据库对象名称大小写是否敏感。该参数在建库时通过dminit脚本指定数据库创建成功后无法修改。取值说明Y、y、1表示大小写敏感N、n、0表示大小写不敏感默认值为Y。可通过系统函数CASE_SENSITIVE()或SF_GET_CASE_SENSITIVE_FLAG()查询当前设置。1.2 大小写敏感时的表现当大小写敏感时系统会自动将不带双引号的标识符转换为大写1) 对对象名的影响创建表时不加双引号CREATE TABLE test(id INT);–系统实际存储为TESTSELECT TABLE_NAME FROM USER_TABLES;--返回 TEST–均被转换为大写匹配SELECT * FROM TEST;SELECT * FROMtest;SELECT * FROM Test;创建小写对象名必须使用双引号括起CREATE TABLEtest_lower(id INT);–只有加双引号且大小写完全匹配才能查询SELECT * FROMtest_lower;--成功 SELECT * FROM test_lower;--失败被转为大写TEST_LOWER开启大小写敏感时不加双引号时字符串拼写相同但大小写不同的对象被认定为等同加上双引号后则被识别为不同对象。2对数据比较的影响大小写敏感时字符串比较区分大小写CREATE TABLE testdata(c1 VARCHAR);INSERT INTO testdata VALUES(a),(A);SELECT COUNT(*)FROM testdata WHERE c1a;--返回1 SELECT COUNT(*)FROM testdata WHERE c1A;--返回11.3 大小写不敏感时的表现当大小写不敏感时系统不会自动转换标识符大小写但比较函数会将字母统一转换为小写后再进行比较CREATE TABLE TEST(id INT);–以下查询方式均能成功比较时忽略大小写SELECT * FROM TEST;SELECT * FROMtest;SELECT * FROM TEst;SELECT * FROMTEST;SELECT * FROMtest;数据比较时同样不区分大小写SELECT COUNT(*)FROM testdata WHERE c1a;-- 返回2 SELECT COUNT(*)FROM testdata WHERE c1A;-- 返回21.4 总结下表对比了“大小写敏感”与“大小写不敏感”两种模式下不同对象的处理规则差异规则项大小写敏感大小写不敏感无引号对象名自动转为大写保持原样有引号对象名保持引号内大小写比较时忽略大小写字符串比较区分大小写不区分大小写跨大小写查询需精确匹配任意大小写均可二、LENGTH_IN_CHAR和CHARSET参数2.1 参数概述CHARSET控制数据库的字符编码方式。0代表GB180301代表UTF-82代表韩文字符集EUC-KR缺省值为0建库后不可修改。LENGTH_IN_CHAR控制VARCHAR类型对象的长度是否以字符为单位。1Y表示以字符为单位0N表示以字节为单位默认值为0建库后不可修改。2.2 不同字符集下的存储差异不同字符集下每个字符占用的字节数不同字符集英文字母中文字符GB180301 字节2 字节UTF-81 字节3 字节2.3 LENGTH_IN_CHAR的影响当LENGTH_IN_CHAR0以字节为单位时--字符集为GB18030varchar(3)以字节为单位 CREATE TABLE t(name VARCHAR(3));INSERT INTO t VALUES(测);--成功1个中文占2字节 INSERT INTO t VALUES(测试);--失败2个中文占4字节超限当LENGTH_IN_CHAR1以字符为单位时精度会在原有基础上扩大。在GB18030字符集下实际存储长度变为定义长度的2倍在UTF-8下变为4倍。--LENGTH_IN_CHAR1GB18030字符集varchar(5)以字符为单位 CREATE TABLE t(name VARCHAR(5));INSERT INTO t VALUES(三一三一三);--成功5个字符 --实际存储长度为10字节5×22.4 总结CHARSET决定单个字符的字节数GB18030对中文更友好为2字节/字UTF-8为3字节/字。LENGTH_IN_CHAR决定VARCHAR长度定义的单位是字符还是字节。两个参数均在建库时确定后续不可修改。存储中文较多的场景应开启LENGTH_IN_CHAR。三、DISQL3.1 DISQL简介DIsql是达梦数据库的命令行客户端工具用于与DM数据库服务器进行交互它识别用户输入将SQL语句打包发送给数据库服务器执行并展示执行结果。工具存放在安装目录的bin目录下。3.2 连接数据库的方法1基本连接语法./disql username/passwordip:port其中IP和端口不写时默认localhost和5236。2常见连接方式#本地默认连接./disql SYSDBA/SYSDBA#指定IP和端口./disql SYSDBA/SYSDBA127.0.0.1:5236#密码含特殊字符时加引号./disql SYSDBA/password127.0.0.1:52363常用选项-L只尝试登录一次-S隐藏模式隐藏SQL标识符/NOLOG不登录数据库直接启动disql3.3 执行SQL的方法1进入disql交互界面后执行./disql SYSDBA/SYSDBA SELECT * FROM TEST1;CREATE TABLE t(id INT);2在命令行直接执行SQL语句./disql SYSDBA/SYSDBA127.0.0.1:5236-eSELECT * FROM TEST1; SELECT * FROM V$INSTANCE;3.4 执行SQL脚本的方法1使用反引号方式./disql SYSDBA/SYSDBA127.0.0.1:5236 /home/dmdba/test.sql2使用start命令START /home/dmdba/test.sql3.5 总结操作类型方法适用场景连接数据库./disql user/pwdip:port日常交互操作执行单条 SQL-e SQL语句快速查询或简单操作执行 SQL 脚本脚本路径 或 START 脚本路径批量操作、初始化、迁移四、模式4.1 模式的定义模式SCHEMA是用户账号拥有的对象集可看作是包含表、视图、索引、存储过程等数据库对象的容器。在达梦数据库中一个用户可以拥有多个模式一个模式只能归属于一个用户一个模式中的对象可被多个用户共享使用需授权系统为每个用户自动创建一个与用户名同名的默认模式模式类似于操作系统中的目录但不能嵌套。4.2 模式的作用组织隔离允许多个用户使用同一数据库而不互相干扰逻辑分组将数据库对象组织成逻辑组便于管理避免命名冲突不同模式中可存在同名对象权限管理通过模式粒度控制用户对数据库对象的访问权限4.3 模式的基本用法1引用模式对象格式为[模式名].对象名当前模式与对象所属模式相同时可省略模式名--完整引用 SELECT * FROM HR.EMPLOYEES;--当前模式为HR时可省略 SELECT * FROM EMPLOYEES;2创建模式及对象使用CREATE SCHEMA语句可在创建模式的同时创建多个对象CREATE SCHEMA TEST;CREATE SEQUENCE ADDRESS_SEQ INCREMENT BY1;CREATE TABLE ADDRESS(ADDRESSID INT, ADDRESS1 VARCHAR(60)NOT NULL, CITY VARCHAR(30)NOT NULL);3模式对象与非模式对象模式对象表、视图、索引、触发器、存储过程、序列、同义词等。非模式对象用户、角色、权限、表空间等。4.4 总结模式是达梦数据库组织对象的逻辑容器每个用户自动拥有同名默认模式。一个用户可创建多个模式模式间相互独立可避免命名冲突。引用对象时需注意模式前缀默认使用当前用户的同名模式。理解模式与用户的区别与联系是掌握达梦数据库对象管理的基础。五、数据类型5.1 数据类型分类达梦数据库支持SQL-92的绝大部分数据类型主要分为数值数据类型、字符数据类型、日期时间数据类型、位串数据类型、多媒体数据类型和其他特殊数据类型。5.2 数值数据类型1整数类型类型范围字节典型用途TINYINT-128 ~ 1271状态码、性别标记SMALLINT-32768 ~ 327672年龄、数量INT/INTEGER-2³¹ ~ 2³¹-14用户 ID、计数器BIGINT-2⁶³ ~ 2⁶³-18交易流水号、大数据量统计2定点数类型NUMERIC/DECIMAL/NUMBER用于存储精确的定点数适用于财务计算等需精确的场景CREATE TABLE products(price NUMERIC(10,2), -- 总位数10小数2位 tax_rate DECIMAL(5,4)-- 总位数5小数4位);精度范围1~38标度定义了小数点右边的位数。3浮点数类型FLOAT单精度4字节、DOUBLE/DOUBLE PRECISION双精度8字节适用于科学计算等近似值场景。5.3 字符数据类型类型特点说明CHAR(n)定长固定长度不足时填充空格VARCHAR(n)变长动态调整长度节省空间补充说明最大长度由数据库页面大小决定。VARCHAR类型在表达式计算中的长度上限为32767。5.4 日期时间数据类型包括DATE日期、TIME时间、DATETIME/TIMESTAMP日期时间等类型。5.5 总结数值类型需根据数据范围选择合适类型以节省存储空间。财务计算须使用定点数NUMERIC/DECIMAL保证精度。字符类型长度受页面大小影响VARCHAR以字节或字符为单位由LENGTH_IN_CHAR参数决定。六、表类型6.1 表类型达梦数据库支持多种表类型普通表索引组织表、堆表、列存储表HUGE分区表、临时表、外部表。6.2 普通表索引组织表普通表是达梦默认创建的表类型。其有且仅有一个聚簇索引键表数据按聚簇索引键排序存储使用逻辑ROWID占用存储空间创建时若指定主键则主键为聚簇索引键若未指定则ROWID为聚簇索引键。CREATE TABLE employee(empno INT PRIMARY KEY, ename VARCHAR(15)NOT NULL, salary FLOAT)STORAGE(INITIAL50, NEXT50, ON USERS);6.3 堆表堆表的数据无序存储与索引组织表相比占用空间更少。通过DDL中包含BRANCH或NO BRANCH关键字来区分CREATE TABLE heap_table(col1 CHAR(10), col2 CHAR(10))STORAGE(BRANCH(6,2));--6个并发分支2个非并发分支6.4 其他表类型列存储表HUGE 面向列存储适用于OLAP分析场景分区表将数据按行拆分为多个分区每个分区独立管理临时表存储临时数据会话结束自动清理外部表访问数据库外部数据源6.5 总结表类型数据组织适用场景普通表索引组织表按聚簇索引键有序存储默认推荐OLTP 场景堆表无序存储对空间敏感、插入频繁的场景列存储表列式存储OLAP、数据仓库分区表按分区键拆分大数据量管理七、索引7.1 索引的基本原理索引是数据库中用于提升数据检索效率的关键数据结构。其作用类似于书籍的目录通过索引可快速定位数据行大幅缩短查询时间。全表扫描如同逐页翻阅书籍效率极低索引扫描则先通过目录找到数据所在位置直接定位读取。7.2 索引的分类1按物理存储分类类型说明特点聚集索引表数据存储在 B 树叶子节点每表有且仅有一个非聚集索引存储索引列和聚集索引列每表可有多个2按功能分类索引类型特点适用场景B-Tree 索引最常用、默认类型等值查询、范围查询唯一索引确保列值唯一保证数据完整性复合索引基于多列创建多列组合查询函数索引基于函数表达式对列使用函数的查询位图索引适合低基数列数据仓库 OLAP 场景7.3 索引的应用原则1创建索引的考虑因素选择唯一性高的列创建索引可更快定位数据分析WHERE子句、JOIN条件中频繁使用的列考虑ORDER BY和GROUP BY中的列索引本身有序2最左前缀原则复合索引遵循最左前缀匹配原则查询条件必须包含索引的最左列才能有效利用索引。3索引的维护索引需要存储空间创建或删除索引不影响基表。插入、更新、删除数据时DM会自动维护索引。需注意索引并非越多越好过多索引会影响DML操作性能。7.4 执行计划分析使用EXPLAIN可查看SQL执行计划判断是否使用了索引EXPLAIN SELECT * FROM students WHERE student_id1001;--关注执行计划中是否出现 TABLE SCAN全表扫描7.5 总结索引是提升查询性能的核心手段但需权衡查询加速与维护成本不同类型的索引适用于不同场景OLTP多用B-TreeOLAP优先位图索引合理设计索引是数据库优化的关键环节需结合业务查询模式持续调整。八、模式对象8.1 触发器Trigger触发器是一种自动执行的数据库对象当指定事件发生时被触发执行。触发器类型表级触发器基于表中数据的INSERT/UPDATE/DELETE操作触发事件触发器基于特定系统事件触发时间触发器基于时间触发CREATE TRIGGER trigger_name BEFORE|AFTER|INSTEAD OF DELETE|INSERT|UPDATE ON table_name[FOR EACH ROW]BEGIN --程序语句 END;管理操作ALTER TRIGGER trigger_name DISABLE;-- 禁用 ALTER TRIGGER trigger_name ENABLE;-- 启用 DROP TRIGGER trigger_name;-- 删除8.2 视图View视图是从一个或多个基表导出的虚表数据字典中只存放视图定义查询语句不存储实际数据。作用提供多角度观察数据的方式、简化用户数据模型、提供逻辑数据独立性、实现数据共享和安全保密CREATE[OR REPLACE]VIEW view_name[(column_list)]AS SELECT... FROM...[WITH READ ONLY];8.3 物化视图Materialized View物化视图与普通视图的核心区别在于存储了导出表的真实数据。特点占用存储空间、基表数据变化时物化视图数据会变陈旧、需通过刷新机制保持数据同步刷新选项FAST增量刷新 COMPLETE完全刷新 FORCE尽量快速刷新否则完全刷新 CREATE MATERIALIZED VIEW mv_name REFRESH FAST ON COMMITAS SELECT... FROM...;8.4 序列Sequence序列用于生成唯一数字序列常用于自增主键值。CREATE SEQUENCE seq_name INCREMENT BY1START WITH1MAXVALUE999999;--使用序列 SELECT seq_name.NEXTVAL FROM DUAL;SELECT seq_name.CURRVAL FROM DUAL;8.5 同义词Synonym同义词为数据库对象提供别名可简化对象引用CREATE SYNONYM emp FOR hr.employees;--之后可直接使用emp替代hr.employees SELECT * FROM emp;8.6 自增列Identity/Auto-increment自增列在插入数据时自动生成递增数值CREATE TABLE t(idINT IDENTITY(1,1)PRIMARY KEY, name VARCHAR(50));8.7 外部链接External Link/Database Link外部链接用于透明访问远程数据库中的对象CREATE EXTERNAL LINK link_name CONNECT TO remote_user IDENTIFIED BY passwordUSINGremote_host:port;--通过外部链接访问远程表 SELECT * FROM remote_tablelink_name;8.8 总结对象类型本质主要用途触发器自动执行的程序数据审计、业务逻辑自动处理视图查询定义虚表简化查询、安全控制物化视图存储查询结果提升查询性能、数据汇总序列数字生成器生成唯一主键值同义词对象别名简化引用、隐藏对象细节自增列自动编号列主键自动生成外部链接远程连接通道跨库数据访问https://eco.dameng.com DM技术深入理解
一、CASE_SENSITIVE参数1.1 参数概述CASE_SENSITIVE是达梦数据库创建时指定的核心参数用于控制标识符如表名、列名等数据库对象名称大小写是否敏感。该参数在建库时通过dminit脚本指定数据库创建成功后无法修改。取值说明Y、y、1表示大小写敏感N、n、0表示大小写不敏感默认值为Y。可通过系统函数CASE_SENSITIVE()或SF_GET_CASE_SENSITIVE_FLAG()查询当前设置。1.2 大小写敏感时的表现当大小写敏感时系统会自动将不带双引号的标识符转换为大写1) 对对象名的影响创建表时不加双引号CREATE TABLE test(id INT);–系统实际存储为TESTSELECT TABLE_NAME FROM USER_TABLES;--返回 TEST–均被转换为大写匹配SELECT * FROM TEST;SELECT * FROMtest;SELECT * FROM Test;创建小写对象名必须使用双引号括起CREATE TABLEtest_lower(id INT);–只有加双引号且大小写完全匹配才能查询SELECT * FROMtest_lower;--成功 SELECT * FROM test_lower;--失败被转为大写TEST_LOWER开启大小写敏感时不加双引号时字符串拼写相同但大小写不同的对象被认定为等同加上双引号后则被识别为不同对象。2对数据比较的影响大小写敏感时字符串比较区分大小写CREATE TABLE testdata(c1 VARCHAR);INSERT INTO testdata VALUES(a),(A);SELECT COUNT(*)FROM testdata WHERE c1a;--返回1 SELECT COUNT(*)FROM testdata WHERE c1A;--返回11.3 大小写不敏感时的表现当大小写不敏感时系统不会自动转换标识符大小写但比较函数会将字母统一转换为小写后再进行比较CREATE TABLE TEST(id INT);–以下查询方式均能成功比较时忽略大小写SELECT * FROM TEST;SELECT * FROMtest;SELECT * FROM TEst;SELECT * FROMTEST;SELECT * FROMtest;数据比较时同样不区分大小写SELECT COUNT(*)FROM testdata WHERE c1a;-- 返回2 SELECT COUNT(*)FROM testdata WHERE c1A;-- 返回21.4 总结下表对比了“大小写敏感”与“大小写不敏感”两种模式下不同对象的处理规则差异规则项大小写敏感大小写不敏感无引号对象名自动转为大写保持原样有引号对象名保持引号内大小写比较时忽略大小写字符串比较区分大小写不区分大小写跨大小写查询需精确匹配任意大小写均可二、LENGTH_IN_CHAR和CHARSET参数2.1 参数概述CHARSET控制数据库的字符编码方式。0代表GB180301代表UTF-82代表韩文字符集EUC-KR缺省值为0建库后不可修改。LENGTH_IN_CHAR控制VARCHAR类型对象的长度是否以字符为单位。1Y表示以字符为单位0N表示以字节为单位默认值为0建库后不可修改。2.2 不同字符集下的存储差异不同字符集下每个字符占用的字节数不同字符集英文字母中文字符GB180301 字节2 字节UTF-81 字节3 字节2.3 LENGTH_IN_CHAR的影响当LENGTH_IN_CHAR0以字节为单位时--字符集为GB18030varchar(3)以字节为单位 CREATE TABLE t(name VARCHAR(3));INSERT INTO t VALUES(测);--成功1个中文占2字节 INSERT INTO t VALUES(测试);--失败2个中文占4字节超限当LENGTH_IN_CHAR1以字符为单位时精度会在原有基础上扩大。在GB18030字符集下实际存储长度变为定义长度的2倍在UTF-8下变为4倍。--LENGTH_IN_CHAR1GB18030字符集varchar(5)以字符为单位 CREATE TABLE t(name VARCHAR(5));INSERT INTO t VALUES(三一三一三);--成功5个字符 --实际存储长度为10字节5×22.4 总结CHARSET决定单个字符的字节数GB18030对中文更友好为2字节/字UTF-8为3字节/字。LENGTH_IN_CHAR决定VARCHAR长度定义的单位是字符还是字节。两个参数均在建库时确定后续不可修改。存储中文较多的场景应开启LENGTH_IN_CHAR。三、DISQL3.1 DISQL简介DIsql是达梦数据库的命令行客户端工具用于与DM数据库服务器进行交互它识别用户输入将SQL语句打包发送给数据库服务器执行并展示执行结果。工具存放在安装目录的bin目录下。3.2 连接数据库的方法1基本连接语法./disql username/passwordip:port其中IP和端口不写时默认localhost和5236。2常见连接方式#本地默认连接./disql SYSDBA/SYSDBA#指定IP和端口./disql SYSDBA/SYSDBA127.0.0.1:5236#密码含特殊字符时加引号./disql SYSDBA/password127.0.0.1:52363常用选项-L只尝试登录一次-S隐藏模式隐藏SQL标识符/NOLOG不登录数据库直接启动disql3.3 执行SQL的方法1进入disql交互界面后执行./disql SYSDBA/SYSDBA SELECT * FROM TEST1;CREATE TABLE t(id INT);2在命令行直接执行SQL语句./disql SYSDBA/SYSDBA127.0.0.1:5236-eSELECT * FROM TEST1; SELECT * FROM V$INSTANCE;3.4 执行SQL脚本的方法1使用反引号方式./disql SYSDBA/SYSDBA127.0.0.1:5236 /home/dmdba/test.sql2使用start命令START /home/dmdba/test.sql3.5 总结操作类型方法适用场景连接数据库./disql user/pwdip:port日常交互操作执行单条 SQL-e SQL语句快速查询或简单操作执行 SQL 脚本脚本路径 或 START 脚本路径批量操作、初始化、迁移四、模式4.1 模式的定义模式SCHEMA是用户账号拥有的对象集可看作是包含表、视图、索引、存储过程等数据库对象的容器。在达梦数据库中一个用户可以拥有多个模式一个模式只能归属于一个用户一个模式中的对象可被多个用户共享使用需授权系统为每个用户自动创建一个与用户名同名的默认模式模式类似于操作系统中的目录但不能嵌套。4.2 模式的作用组织隔离允许多个用户使用同一数据库而不互相干扰逻辑分组将数据库对象组织成逻辑组便于管理避免命名冲突不同模式中可存在同名对象权限管理通过模式粒度控制用户对数据库对象的访问权限4.3 模式的基本用法1引用模式对象格式为[模式名].对象名当前模式与对象所属模式相同时可省略模式名--完整引用 SELECT * FROM HR.EMPLOYEES;--当前模式为HR时可省略 SELECT * FROM EMPLOYEES;2创建模式及对象使用CREATE SCHEMA语句可在创建模式的同时创建多个对象CREATE SCHEMA TEST;CREATE SEQUENCE ADDRESS_SEQ INCREMENT BY1;CREATE TABLE ADDRESS(ADDRESSID INT, ADDRESS1 VARCHAR(60)NOT NULL, CITY VARCHAR(30)NOT NULL);3模式对象与非模式对象模式对象表、视图、索引、触发器、存储过程、序列、同义词等。非模式对象用户、角色、权限、表空间等。4.4 总结模式是达梦数据库组织对象的逻辑容器每个用户自动拥有同名默认模式。一个用户可创建多个模式模式间相互独立可避免命名冲突。引用对象时需注意模式前缀默认使用当前用户的同名模式。理解模式与用户的区别与联系是掌握达梦数据库对象管理的基础。五、数据类型5.1 数据类型分类达梦数据库支持SQL-92的绝大部分数据类型主要分为数值数据类型、字符数据类型、日期时间数据类型、位串数据类型、多媒体数据类型和其他特殊数据类型。5.2 数值数据类型1整数类型类型范围字节典型用途TINYINT-128 ~ 1271状态码、性别标记SMALLINT-32768 ~ 327672年龄、数量INT/INTEGER-2³¹ ~ 2³¹-14用户 ID、计数器BIGINT-2⁶³ ~ 2⁶³-18交易流水号、大数据量统计2定点数类型NUMERIC/DECIMAL/NUMBER用于存储精确的定点数适用于财务计算等需精确的场景CREATE TABLE products(price NUMERIC(10,2), -- 总位数10小数2位 tax_rate DECIMAL(5,4)-- 总位数5小数4位);精度范围1~38标度定义了小数点右边的位数。3浮点数类型FLOAT单精度4字节、DOUBLE/DOUBLE PRECISION双精度8字节适用于科学计算等近似值场景。5.3 字符数据类型类型特点说明CHAR(n)定长固定长度不足时填充空格VARCHAR(n)变长动态调整长度节省空间补充说明最大长度由数据库页面大小决定。VARCHAR类型在表达式计算中的长度上限为32767。5.4 日期时间数据类型包括DATE日期、TIME时间、DATETIME/TIMESTAMP日期时间等类型。5.5 总结数值类型需根据数据范围选择合适类型以节省存储空间。财务计算须使用定点数NUMERIC/DECIMAL保证精度。字符类型长度受页面大小影响VARCHAR以字节或字符为单位由LENGTH_IN_CHAR参数决定。六、表类型6.1 表类型达梦数据库支持多种表类型普通表索引组织表、堆表、列存储表HUGE分区表、临时表、外部表。6.2 普通表索引组织表普通表是达梦默认创建的表类型。其有且仅有一个聚簇索引键表数据按聚簇索引键排序存储使用逻辑ROWID占用存储空间创建时若指定主键则主键为聚簇索引键若未指定则ROWID为聚簇索引键。CREATE TABLE employee(empno INT PRIMARY KEY, ename VARCHAR(15)NOT NULL, salary FLOAT)STORAGE(INITIAL50, NEXT50, ON USERS);6.3 堆表堆表的数据无序存储与索引组织表相比占用空间更少。通过DDL中包含BRANCH或NO BRANCH关键字来区分CREATE TABLE heap_table(col1 CHAR(10), col2 CHAR(10))STORAGE(BRANCH(6,2));--6个并发分支2个非并发分支6.4 其他表类型列存储表HUGE 面向列存储适用于OLAP分析场景分区表将数据按行拆分为多个分区每个分区独立管理临时表存储临时数据会话结束自动清理外部表访问数据库外部数据源6.5 总结表类型数据组织适用场景普通表索引组织表按聚簇索引键有序存储默认推荐OLTP 场景堆表无序存储对空间敏感、插入频繁的场景列存储表列式存储OLAP、数据仓库分区表按分区键拆分大数据量管理七、索引7.1 索引的基本原理索引是数据库中用于提升数据检索效率的关键数据结构。其作用类似于书籍的目录通过索引可快速定位数据行大幅缩短查询时间。全表扫描如同逐页翻阅书籍效率极低索引扫描则先通过目录找到数据所在位置直接定位读取。7.2 索引的分类1按物理存储分类类型说明特点聚集索引表数据存储在 B 树叶子节点每表有且仅有一个非聚集索引存储索引列和聚集索引列每表可有多个2按功能分类索引类型特点适用场景B-Tree 索引最常用、默认类型等值查询、范围查询唯一索引确保列值唯一保证数据完整性复合索引基于多列创建多列组合查询函数索引基于函数表达式对列使用函数的查询位图索引适合低基数列数据仓库 OLAP 场景7.3 索引的应用原则1创建索引的考虑因素选择唯一性高的列创建索引可更快定位数据分析WHERE子句、JOIN条件中频繁使用的列考虑ORDER BY和GROUP BY中的列索引本身有序2最左前缀原则复合索引遵循最左前缀匹配原则查询条件必须包含索引的最左列才能有效利用索引。3索引的维护索引需要存储空间创建或删除索引不影响基表。插入、更新、删除数据时DM会自动维护索引。需注意索引并非越多越好过多索引会影响DML操作性能。7.4 执行计划分析使用EXPLAIN可查看SQL执行计划判断是否使用了索引EXPLAIN SELECT * FROM students WHERE student_id1001;--关注执行计划中是否出现 TABLE SCAN全表扫描7.5 总结索引是提升查询性能的核心手段但需权衡查询加速与维护成本不同类型的索引适用于不同场景OLTP多用B-TreeOLAP优先位图索引合理设计索引是数据库优化的关键环节需结合业务查询模式持续调整。八、模式对象8.1 触发器Trigger触发器是一种自动执行的数据库对象当指定事件发生时被触发执行。触发器类型表级触发器基于表中数据的INSERT/UPDATE/DELETE操作触发事件触发器基于特定系统事件触发时间触发器基于时间触发CREATE TRIGGER trigger_name BEFORE|AFTER|INSTEAD OF DELETE|INSERT|UPDATE ON table_name[FOR EACH ROW]BEGIN --程序语句 END;管理操作ALTER TRIGGER trigger_name DISABLE;-- 禁用 ALTER TRIGGER trigger_name ENABLE;-- 启用 DROP TRIGGER trigger_name;-- 删除8.2 视图View视图是从一个或多个基表导出的虚表数据字典中只存放视图定义查询语句不存储实际数据。作用提供多角度观察数据的方式、简化用户数据模型、提供逻辑数据独立性、实现数据共享和安全保密CREATE[OR REPLACE]VIEW view_name[(column_list)]AS SELECT... FROM...[WITH READ ONLY];8.3 物化视图Materialized View物化视图与普通视图的核心区别在于存储了导出表的真实数据。特点占用存储空间、基表数据变化时物化视图数据会变陈旧、需通过刷新机制保持数据同步刷新选项FAST增量刷新 COMPLETE完全刷新 FORCE尽量快速刷新否则完全刷新 CREATE MATERIALIZED VIEW mv_name REFRESH FAST ON COMMITAS SELECT... FROM...;8.4 序列Sequence序列用于生成唯一数字序列常用于自增主键值。CREATE SEQUENCE seq_name INCREMENT BY1START WITH1MAXVALUE999999;--使用序列 SELECT seq_name.NEXTVAL FROM DUAL;SELECT seq_name.CURRVAL FROM DUAL;8.5 同义词Synonym同义词为数据库对象提供别名可简化对象引用CREATE SYNONYM emp FOR hr.employees;--之后可直接使用emp替代hr.employees SELECT * FROM emp;8.6 自增列Identity/Auto-increment自增列在插入数据时自动生成递增数值CREATE TABLE t(idINT IDENTITY(1,1)PRIMARY KEY, name VARCHAR(50));8.7 外部链接External Link/Database Link外部链接用于透明访问远程数据库中的对象CREATE EXTERNAL LINK link_name CONNECT TO remote_user IDENTIFIED BY passwordUSINGremote_host:port;--通过外部链接访问远程表 SELECT * FROM remote_tablelink_name;8.8 总结对象类型本质主要用途触发器自动执行的程序数据审计、业务逻辑自动处理视图查询定义虚表简化查询、安全控制物化视图存储查询结果提升查询性能、数据汇总序列数字生成器生成唯一主键值同义词对象别名简化引用、隐藏对象细节自增列自动编号列主键自动生成外部链接远程连接通道跨库数据访问https://eco.dameng.com