达梦数据库中视图与索引的创建及使用详解

索引：

在数据库管理与应用开发过程中，视图和索引是两个非常重要的数据库对象。视图能够简化复杂查询、保障数据安全，索引则可以大幅提升数据查询效率。本文将针对达梦（DM）数据库，详细介绍视图和索引的概念、创建方法、使用场景以及相关注意事项，帮助数据库开发者和管理员更好地利用这两个工具优化数据库操作。

一、达梦数据库视图详解

1.1 视图的概念与作用

视图是一个虚拟的表，它基于一个或多个表（或其他视图）的查询结果集。视图本身不存储实际的数据，只存储对应的查询语句，当用户访问视图时，数据库会动态执行该查询语句并返回结果。

在达梦数据库中，视图主要有以下几个作用：

• 简化复杂查询：对于涉及多表关联、条件复杂的查询，可以将其定义为视图，用户后续只需查询视图即可，无需重复编写复杂的 SQL 语句。

• 数据安全控制：通过视图可以隐藏表中的部分敏感字段或记录，只向用户展示其有权访问的数据，保障数据安全。例如，对于员工表，可创建一个不包含工资字段的视图给普通查询用户。

• 数据独立性：当底层表的结构发生轻微变化（如增加字段）时，只要视图的查询逻辑不受影响，基于该视图的应用程序无需修改，提高了应用的可维护性。

1.2 视图的创建

在达梦数据库中，创建视图使用CREATE VIEW语句，语法格式如下：

CREATE [OR REPLACE] VIEW 视图名 [(列名1, 列名2, ...)]

AS

SELECT 查询语句

[WITH CHECK OPTION [CONSTRAINT 约束名]]

[WITH READ ONLY];

各参数说明：

• OR REPLACE：如果已存在同名视图，将其替换为新视图，避免因视图已存在而报错。

• 视图名：自定义的视图名称，需符合达梦数据库的命名规范（如以字母开头，不包含特殊字符等）。

• (列名1, 列名2, ...)：可选参数，用于指定视图的列名。若不指定，视图的列名将默认使用SELECT语句中查询的列名；若指定，列名数量需与SELECT语句返回的列数一致。

• SELECT 查询语句：定义视图数据来源的核心查询，可涉及单表、多表关联、子查询等，支持大部分SELECT语句的语法（如WHERE、GROUP BY、HAVING等）。

• WITH CHECK OPTION：可选参数，用于限制通过视图修改数据时，修改后的数据必须仍能被视图查询到。例如，若视图查询条件为dept_id = 1，则通过该视图修改数据时，dept_id不能改为其他值，否则会报错。CONSTRAINT 约束名用于为该检查选项指定约束名。

• WITH READ ONLY：可选参数，指定视图为只读视图，不允许通过该视图对底层表的数据进行插入、更新或删除操作。

示例 1：创建单表视图

假设有一个员工表EMP，包含EMP_ID（员工 ID）、EMP_NAME（员工姓名）、DEPT_ID（部门 ID）、SALARY（工资）字段，现在创建一个只包含部门 ID 为 1 的员工姓名和工资的视图EMP_VIEW_DEPT1：

CREATE VIEW EMP_VIEW_DEPT1 (EMP_NAME, SALARY)

AS

SELECT EMP_NAME, SALARY

FROM EMP

WHERE DEPT_ID = 1

WITH CHECK OPTION CONSTRAINT CHK_EMP_VIEW_DEPT1;

该视图只展示部门 1 的员工姓名和工资，且通过视图修改工资时，DEPT_ID仍需保持为 1，否则修改会失败。

示例 2：创建多表关联视图

假设有部门表DEPT（包含DEPT_ID、DEPT_NAME字段）和员工表EMP，现在创建一个关联两个表，展示员工姓名、部门名称的视图EMP_DEPT_VIEW：

CREATE OR REPLACE VIEW EMP_DEPT_VIEW

AS

SELECT E.EMP_NAME, D.DEPT_NAME

FROM EMP E

INNER JOIN DEPT D ON E.DEPT_ID = D.DEPT_ID;

通过该视图，用户可以直接查询到员工对应的部门名称，无需手动编写关联查询语句。

1.3 视图的使用

1.3.1 查询视图

查询视图的方式与查询普通表完全一致，使用SELECT语句即可。例如，查询上述EMP_VIEW_DEPT1视图中的数据：

SELECT * FROM EMP_VIEW_DEPT1;

查询EMP_DEPT_VIEW视图中部门名称为 “技术部” 的员工：

SELECT EMP_NAME

FROM EMP_DEPT_VIEW

WHERE DEPT_NAME = '技术部';

1.3.2 修改视图数据（非只读视图）

对于非只读且满足条件的视图，可以通过INSERT、UPDATE、DELETE语句修改底层表的数据，但需注意以下限制：

• 视图若基于多表关联创建，通常只能修改其中一个表的数据，且修改的列需是该表的非关联列。

• 若视图包含GROUP BY、DISTINCT、聚合函数（如SUM、COUNT）等，无法通过视图修改数据。

• 若视图指定了WITH CHECK OPTION，修改后的数据需符合视图的查询条件。

示例：通过EMP_VIEW_DEPT1视图更新员工 “张三” 的工资：

UPDATE EMP_VIEW_DEPT1

SET SALARY = 8000

WHERE EMP_NAME = '张三';

该操作会实际更新EMP表中 “张三” 的工资字段。

1.3.3 修改视图结构

若需要修改视图的查询逻辑或列定义，可以使用CREATE OR REPLACE VIEW语句（本质是替换原视图），或ALTER VIEW语句（达梦数据库支持ALTER VIEW修改视图的部分属性，如添加WITH READ ONLY等）。

示例：将EMP_VIEW_DEPT1视图修改为只读视图：

ALTER VIEW EMP_VIEW_DEPT1

SET WITH READ ONLY;

1.3.4 删除视图

当视图不再需要时，使用DROP VIEW语句删除视图，语法如下：

DROP VIEW [IF EXISTS] 视图名;

IF EXISTS用于避免删除不存在的视图时报错。

示例：删除EMP_VIEW_DEPT1视图：

DROP VIEW IF EXISTS EMP_VIEW_DEPT1;

1.4 视图使用注意事项

• 视图不存储数据，每次查询视图都会重新执行底层的SELECT语句，若底层查询复杂或数据量大，可能会影响查询性能，此时需结合索引或其他优化手段。

• 避免创建嵌套过深的视图（如视图基于另一个视图创建，而该视图又基于第三个视图），嵌套过深会增加查询解析难度，降低性能，且不利于维护。

• 对于频繁查询的视图，可考虑使用达梦数据库的 “物化视图”（Materialized View），物化视图会存储实际的数据，定期刷新，能大幅提升查询性能，但会占用额外的存储空间，且数据存在一定的延迟性。

二、达梦数据库索引详解

2.1 索引的概念与作用

索引是数据库中用于快速查找数据的数据结构（达梦数据库默认使用 B + 树索引），它通过将表中的一列或多列数据与对应的行地址关联起来，使得数据库在查询数据时，无需扫描整个表，只需通过索引快速定位到目标数据，从而显著提升查询效率。

索引的主要作用的是提升查询速度，但同时也有一些副作用：

• 增加数据写入（INSERT、UPDATE、DELETE）的开销：当表中的数据发生变化时，对应的索引也需要同步更新，会消耗额外的时间和资源。

• 占用存储空间：索引本身需要存储在磁盘上，一张表的索引越多，占用的存储空间越大。

因此，索引的设计需要权衡查询性能和写入性能，并非索引越多越好。

2.2 达梦数据库索引的类型

达梦数据库支持多种类型的索引，常见的有：

• B + 树索引：默认的索引类型，适用于大多数查询场景，尤其是范围查询（如WHERE age BETWEEN 20 AND 30）和等值查询（如WHERE id = 100）。

• 哈希索引：基于哈希表实现，适用于等值查询（如WHERE name = '张三'），查询速度极快，但不支持范围查询和排序操作，且哈希冲突可能影响性能。

• 位图索引：适用于列值重复率高的场景（如性别、部门 ID 等），通过位图的方式存储数据的位置信息，占用存储空间小，适合多条件组合查询，但不适合频繁更新的列。

• 函数索引：基于列的函数计算结果创建的索引，适用于查询条件中包含函数的场景（如WHERE UPPER(name) = 'ZHANGSAN'），若不创建函数索引，这类查询无法使用普通索引，只能全表扫描。

本文主要介绍最常用的 B + 树索引的创建与使用。

2.3 索引的创建

在达梦数据库中，创建 B + 树索引使用CREATE INDEX语句，语法格式如下：

CREATE [UNIQUE] [CLUSTERED] INDEX 索引名

ON 表名 (列名1 [ASC|DESC], 列名2 [ASC|DESC], ...)

[STORAGE (存储参数)]

[COMPUTE STATISTICS];

各参数说明：

• UNIQUE：可选参数，指定索引为唯一索引，即索引列的值不能重复（允许 NULL 值，且 NULL 值视为不重复）。唯一索引可以保证数据的唯一性，同时提升查询效率，若表的某列需要作为唯一标识（非主键），可创建唯一索引。

• CLUSTERED：可选参数，指定索引为聚簇索引。聚簇索引的特点是索引的顺序与表中数据的物理存储顺序一致，一张表只能有一个聚簇索引（达梦数据库中，若表有主键，主键默认是聚簇索引；若没有主键，可手动指定一个聚簇索引）。聚簇索引在范围查询和排序查询中性能优势明显，但数据插入和更新时，若索引列值变化，可能导致数据物理位置移动，开销较大。

• 索引名：自定义的索引名称，需符合命名规范，且在同一张表中不能重复。

• 表名：索引所属的表名。

• (列名1 [ASC|DESC], 列名2 [ASC|DESC], ...)：指定索引对应的列（即索引键），可以是单列，也可以是多列（复合索引）。ASC表示升序（默认），DESC表示降序，索引的排序顺序会影响查询时的排序性能。

• STORAGE (存储参数)：可选参数，用于指定索引的存储属性，如存储表空间、初始大小、增长方式等，例如STORAGE (TABLESPACE IDX_TBS INITIAL 10M NEXT 5M)。

• COMPUTE STATISTICS：可选参数，创建索引的同时收集索引的统计信息，这些统计信息有助于数据库优化器生成更优的查询执行计划。

示例 1：创建单列非唯一索引

为员工表EMP的DEPT_ID列创建一个非唯一索引IDX_EMP_DEPT_ID，用于提升按部门 ID 查询员工的效率：

CREATE INDEX IDX_EMP_DEPT_ID

ON EMP (DEPT_ID)

STORAGE (TABLESPACE IDX_TBS)

COMPUTE STATISTICS;

示例 2：创建唯一索引

为员工表EMP的EMP_NAME列创建一个唯一索引UNIQ_IDX_EMP_NAME，保证员工姓名不重复，同时提升按姓名查询的效率：

CREATE UNIQUE INDEX UNIQ_IDX_EMP_NAME

ON EMP (EMP_NAME)

COMPUTE STATISTICS;

若EMP表中已存在重复的员工姓名，该语句会执行失败，需先删除重复数据。

示例 3：创建复合索引

为员工表EMP的DEPT_ID和SALARY列创建一个复合索引IDX_EMP_DEPT_SAL，用于优化 “查询某部门中工资大于指定值的员工” 这类多条件查询：

CREATE INDEX IDX_EMP_DEPT_SAL

ON EMP (DEPT_ID ASC, SALARY DESC)

COMPUTE STATISTICS;

复合索引的查询效率与列的顺序有关，通常将过滤性强（重复率低）的列放在前面，且查询条件中若能匹配索引的前几列（即 “前缀匹配”），才能有效使用该索引。例如，上述索引可优化WHERE DEPT_ID = 1或WHERE DEPT_ID = 1 AND SALARY > 5000的查询，但无法优化WHERE SALARY > 5000的查询（未匹配索引前缀DEPT_ID）。

示例 4：创建函数索引

为员工表EMP的EMP_NAME列创建一个基于UPPER()函数的函数索引IDX_EMP_NAME_UPPER，用于优化查询条件包含UPPER(EMP_NAME)的查询：

CREATE INDEX IDX_EMP_NAME_UPPER

ON EMP (UPPER(EMP_NAME))

COMPUTE STATISTICS;

创建该索引后，执行SELECT * FROM EMP WHERE UPPER(EMP_NAME) = 'ZHANGSAN'时，数据库会使用该函数索引，避免全表扫描。

2.4 索引的使用与管理

2.4.1 索引的自动使用

在达梦数据库中，当执行SELECT查询语句时，数据库的查询优化器会根据表的数据量、索引的统计信息、查询条件等因素，自动判断是否使用索引。用户无需手动指定使用哪个索引（特殊情况除外，如强制索引）。

例如，执行以下查询时，若EMP表的DEPT_ID列有索引IDX_EMP_DEPT_ID，优化器会自动使用该索引定位数据：

SELECT EMP_NAME, SALARY

FROM EMP

WHERE DEPT_ID = 2;

2.4.2 查看索引信息

若需要查看表的索引信息，可查询达梦数据库的系统视图DBA_INDEXES（需 DBA 权限）、ALL_INDEXES（当前用户有权访问的索引）或USER_INDEXES（当前用户拥有的索引），例如：

-- 查看当前用户拥有的EMP表的所有索引

SELECT INDEX_NAME, INDEX_TYPE, UNIQUENESS, COLUMN_NAME

FROM USER_IND_COLUMNS

WHERE TABLE_NAME = 'EMP';

USER_IND_COLUMNS视图包含了索引名称、索引类型、是否唯一、索引列等信息，便于用户了解索引的配置情况。

2.4.3 修改索引

达梦数据库支持通过ALTER INDEX语句修改索引的部分属性，如重命名、重建、修改存储参数等。

• 重命名索引：

ALTER INDEX IDX_EMP_DEPT_ID RENAME TO IDX_EMP_DEPT_ID_NEW;

• 重建索引：当索引因数据频繁更新而产生大量碎片，导致查询性能下降时，可重建索引（重建索引会重新组织索引结构，消除碎片）：

ALTER INDEX IDX_EMP_DEPT_ID_NEW REBUILD COMPUTE STATISTICS;

2.4.4 删除索引

当索引不再被使用，或因增加写入开销而影响性能时，可使用DROP INDEX语句删除索引，语法如下：

DROP INDEX [IF EXISTS] 索引名;

示例：删除IDX_EMP_DEPT_ID_NEW索引：

DROP INDEX IF EXISTS IDX_EMP_DEPT_ID_NEW;

需注意，删除索引前需确认该索引不再被查询使用，避免删除后导致查询性能大幅下降。

2.5 索引使用注意事项

• 合理选择索引列：优先为查询频率高、过滤性强（重复率低）的列创建索引；避免为查询频率低、重复率高（如性别列，只有 “男”“女” 两个值）或频繁更新的列创建索引。

• 控制索引数量：一张表的索引数量不宜过多，通常建议不超过 5-8 个。过多的索引会显著增加INSERT、UPDATE、DELETE操作的开销，尤其是在数据写入频繁的表中。

• 复合索引的列顺序很重要：复合索引需遵循 “前缀匹配” 原则，查询条件中若能匹配索引的前几列，才能有效使用索引。因此，应将过滤性强、查询中频繁出现的列放在复合索引的前面。

• 避免索引失效场景：以下情况可能导致索引失效，查询无法使用索引而进行全表扫描：