dm8索引对象管理

1. 管理索引的准则

索引是与表相关的可选的结构（聚集索引除外），它能使对应于表的 SQL 语句执行得更快，因为有索引比没有索引能更快地定位信息。DM8 索引能提供访问表的数据的更快路径，可以不用重写任何查询而使用索引，其结果与不使用索引是一样的，但速度更快。DM8 提供了几种最常见类型的索引，对不同场景有不同的功能，它们是：

聚集索引：每一个普通表有且只有一个聚集索引；

唯一索引：索引数据根据索引键唯一；

函数索引：包含函数/表达式的预先计算的值；

位图索引：对低基数的列创建位图索引；

位图连接索引：针对两个或者多个表连接的位图索引，主要用于数据仓库中；

全文索引：在表的文本列上而建的索引。

索引在逻辑上和物理上都与相关的表的数据无关，作为无关的结构，索引需要存储空间。创建或删除一个索引，不会影响基本的表、数据库应用或其他索引。当插入、更改和删除相关的表的行时，DM8 会自动管理索引。如果删除索引，所有的应用仍继续工作，但访问以前被索引了的数据时速度可能会变慢。

1.1 在表中插入数据后创建索引

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

 

1.2 索引正确的表和列

使用下面的准则来决定何时创建索引：

a.如果需要经常地检索大表中的少量的行，就为查询键创建索引；

b.为了改善多个表的连接的性能，可为连接列创建索引；

c.主键和唯一键自动具有索引，在外键上很多情况下也创建索引；

d.小表不需要索引。

选取表中的索引列时可以考虑以下几点：

a. 列中的值相对比较唯一 ；

b. 取值范围大，适合建立索引；

c. CLOB 和 TEXT 只能建立全文索引、BLOB 不能建立任何索引。

 

1.3 为性能而安排索引列

在 CREATE INDEX 语句中列的排序会影响查询的性能。通常，将最常用的列放在最前面。

如果查询中有多个字段组合定位，则不应为每个字段单独创建索引，而应该创建一个组合索引。当两个或多个字段都是等值查询时，组合索引中各个列的前后关系是无关紧要的。

但是如果是非等值查询时，要想有效利用组合索引，则应该按等值字段在前，非等值字段在

后的原则创建组合索引，查询时只能利用一个非等值的字段。

 

1.4 限制每个表的索引数量

一个表可以有任意数量的索引。但是，索引越多，修改表数据的开销就越大。当插入或删除行时，表上的所有索引也要被更改；更改一个列时，包含该列的所有索引也要被更改。因此，在从表中检索数据的速度和更新表的速度之间有一个折衷。例如，如果一个表主要仅用于读，则索引多就有好处；如果一个表经常被更新，则索引不宜多建。

 

1.5 估计索引大小和设置存储参数

创建索引之前先估计索引的大小能更好地促进规划和管理磁盘空间。可以用索引以及回滚段、重做日志文件的组合估计的大小来决定支持所期望的数据库所需的磁盘空间的大小。通过这些估计，就可以购买合适的硬件和做出其他正确的决定。

用单个索引估计的大小能更好地管理索引使用的磁盘空间。创建索引时，可以设置适当的存储参数，并改善使用该索引的应用的 I/O 性能。例如，假设在创建索引之前估计索引的最大大小，之后就可以在创建该索引时设置适当的存储参数，就能很少为表的数据段分配簇。并且，所有的该索引的数据都被保存在相对连续的磁盘空间扇区中，这就减少了使用该索引的磁盘 I/O 操作所需的时间。

 

1.6 为每个索引指定表空间

可以在除临时表空间、日志表空间和回滚段表空间外的其他任何表空间中创建索引，也可以在其索引的表的相同或不同的表空间中创建索引。如果表及其索引使用相同的表空间 能更方便地对数据库进行管理（如表空间或文件备份）或保证应用的可用性，因为所有有关的数据总是在一起联机。然而，将表及其索引放在不同的表空间（在不同磁盘上）产生的性能比放在相同的表空间更好，因为这样做减少了磁盘竞争。但是将表及其索引放在不同的表空间时，如果一个表上某索引所在的表空间脱机了，则涉及这张表的 SQL 语句可能由于执行计划仍旧需要使用被脱机的索引而不能成功执行。

 

2. 创建索引

本节描述如何创建索引。要在用户自己的模式中创建索引，至少要满足如下条件之一：

a. 要被索引的表是在自己的模式中 ；

b. 在要被索引的表上有 CREATE INDEX 权限；

c. 具有 CREATE ANY INDEX 数据库权限。

要在其他模式中创建索引，用户必须具有 CREATE ANY INDEX 数据库权限。

 

2.1 明确的创建索引

可以用 CREATE INDEX 语句明确地创建索引。如下语句在TESTTAB表的TNO列上创建一个名为IND_TEST_TNO的索引

CREATE INDEX IND_TESTTAB_TNO ON TESTTAB(TNO) ;

 

2.2 创建聚集索引

DM8中表(列存储表和堆表除外)都是使用 B+树索引结构管理的，每一个普通表都有且仅有一个聚集索引，数据都通过聚集索引键排序，根据聚集索引键可以快速查询任何记录。

当建表语句未指定聚集索引键时，DM8 的默认聚集索引键是 ROWID。若指定索引键，表中数据都会根据指定索引键排序。

建表后，DM8 也可以用创建新聚集索引的方式来重建表数据，并按新的聚集索引排序。例如，可以对 TESTTAB 表以 TNO 列新建聚集索引。

CREATE CLUSTER INDEX CLU_TESTTAB_TNO ON TESTTAB(TNO);

新建聚集索引会重建这个表以及其所有索引，包括二级索引、函数索引，是一个代价非常大的操作。因此，最好在建表时就确定聚集索引键，或在表中数据比较少时新建聚集索引，而尽量不要对数据量非常大的表建立聚集索引。

创建聚集索引的约束条件：

a. 每张表中只允许有一个聚集索引，如果之前已经指定过 CLUSTER INDEX 或者指定了 CLUSTER PK，则用户新建立 CLUSTER INDEX 时系统会自动删除原先的聚集索引。但如果新建聚集索引时指定的创建方式（列，顺序）和之前的聚集索引一样，则会报错；

b. 指定 CLUSTER INDEX 操作需要重建表上的所有索引，包括 PK 索引；

c. 删除聚集索引时，缺省以 ROWID 排序，自动重建所有索引；

d. 若聚集索引是默认的 ROWID 索引，不允许删除；

e. 聚集索引不能应用到函数索引中；

f. 不能在列存储表上新建/删除聚集索引；

g. 建聚集索引语句不能含有 partition_clause 子句；

h. 在临时表上增删索引会使当前会话上临时 b 树数据丢失。

 

2.3 明确的创建唯一索引

索引可以是唯一的或非唯一的。唯一索引可以保证表上不会有两行数据在键列上具有相同的值，非唯一索引不会在键列上施加这个限制。

可用 CREATE UNIQUE INDEX 语句来创建唯一索引，如下例子创建一个唯一索引：

CREATE UNIQUE INDEX UNIQ_TESTTAB_TNO ON TESTTAB(TNO);

 

2.4 自动创建与约束相关的唯一索引

DM8 通过在唯一键或主键上创建一个唯一索引来在表上实施 UNIQUE KEY 或 PRIMARY KEY 完整性约束。当启用约束时DM8自动创建该索引。如下面的语句会自动在表TESTTAB的TNO列上创建一个唯一索引。

ALTER TABLE TESTTAB ADD CONSTRAINTS PK_TESTTAB_TNO PRIMARY KEY (TNO);

 

2.5 创建基于函数的索引

基于函数的索引促进了限定函数或表达式的返回值的查询，该函数或表达式的值被预先计算出来并存储在索引中。正确使用函数索引，可以带来以下好处：

a. 创建更强有力的分类，例如可以用 UPPER 和 LOWER 函数执行区分大小写的分类；

b. 预先计算出计算密集的函数的值，并在索引中将其分类。可以在索引中存储要经常访问的计算密集的函数，当需要访问值时，该值已经计算出来了。因此，极大地改善了查询的执行性能；

c. 增加了优化器执行范围扫描而不是全表扫描的情况的数量。

 

创建函数索引有以下约束条件：

a. 表达式可以由多列组成，不同的列不能超过 63 个；

b. 表达式不允许为时间间隔类型；

c. 表达式中不允许出现半透明加密列；

d. 函数索引表达式的长度理论值不能超过 816 个字符（包括生成后的指令和字符串）；

e. 函数索引不能为 CLUSTER 或 PRIMARY KEY 类型；

f. 表达式不支持集函数和不确定函数，不确定函数为每次执行得到的结果不确定，系统中不确定函数包括：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；

g. 快速装载不支持含有函数索引的表；

h. 若函数索引中要使用用户自定义的函数，则函数必须是指定了 DETERMINISTIC 属性的确定性函数；

i. 若函数索引中使用的确定性函数发生了变更或删除，用户需手动重建函数索引；

j. 若函数索引中使用的确定性函数内有不确定因素，会导致前后计算结果不同的情况。在查询使用函数索引时，使用数据插入函数索引时的计算结果为 KEY 值；修改时可能会导致在使用函数索引过程中出现根据聚集索引无法在函数索引中找到相应记录的情况，对此进行报错处理；

k. 临时表不支持函数索引；

l. MPP 环境下不支持函数索引的创建。

 

2.6 创建位图索引

位图索引主要针对含有大量相同值的列而创建。位图索引被广泛引用到数据仓库中，创建方式和普通索引一致，对低基数（不同的值很少）的列创建位图索引，能够有效提高基于该列的查询效率。且执行查询语句的 where 子句中带有 AND 和 OR 谓词时，效率更加明显。

 

位图索引具有以下约束：

a. 支持普通表、堆表和水平分区表创建位图索引；

b. 不支持对大字段创建位图索引；

c. 不支持对计算表达式列创建位图索引；

d. 不支持在 UNIQUE 列和 PRIMARY KEY 上创建位图索引；

e. 不支持对存在 CLUSTER KEY 的表创建位图索引；

f. 仅支持单列或者不超过 63 个组合列上创建位图索引；

g. MPP 环境下不支持位图索引的创建；

h. 不支持快速装载建含有位图索引的表；

i. 不支持全局位图索引；

j. 包含位图索引的表不支持并发的插入、删除和更新操作。

 

2.7 创建位图连接索引

位图连接索引是一种提高通过连接实现海量数据查询效率的有效方式，主要用于数据仓库环境中。区别于上一节所说的建立在单表上的位图索引，位图连接索引是针对两个或者多个表的连接而建立的位图索引，同时保存了连接的位图结果。对于索引列中的每一个值，位图连接索引在索引表中保存了对应行的 ROWID。

 

a. 适用于常规索引的基本限制也适用于位图连接索引；

b. 用于连接的列必须是维度表中的主键或存在唯一约束；如果是复合主键，则必须使用复合主键中的所有列；

c. 当多个事务同时使用位图连接索引时，同一时间只允许更新一个表；

d. 连接索引创建时，基表只允许出现一次；

e. 不允许对存在 cluster key 的表创建位图连接索引；

f. 位图连接索引表（内部辅助表，命名为 BMJ$_索引名）仅支持 select 操作，其他操作都不支持：如 insert、delete、update、alter、drop 和建索引等；

g. 不支持对位图连接索引所在事实表和维度表的备份还原，不支持位图连接索引表的表级备份还原；

h. 不支持位图连接索引表、位图连接索引以及虚索引的导出导入；

i. 位图连接索引及其相关表不支持快速装载；

j. 位图连接索引名称的长度限制为：事实表名的长度+索引名称长度+6<128；

k. 仅支持普通表、堆表和 HUGE 表；

l. WHERE 条件只能是列与列之间的等值连接，并且必须含有所有表；

m. 事实表上聚集索引和位图连接索引不能同时存在；

n. 不支持对含有位图连接索引的表中的数据执行 DML，如需要执行 DML,则先删除该索引；

o. 含有位图连接索引的表不支持下列 DDL 操作：删除、修改表约束，删除、修改列，更改表名。另外，含位图连接索引的堆表不支持添加列操作；

p. 不允许对含有位图连接索引的表并发操作；

q. 创建位图连接索引时，在存储参数中可指定存储位图的字节数，有效值为：1~ 128，服务器自动校正为 4 的倍数，默认值为 48。如 STORAGE(SECTION(4))，表示使用 4 个字节存储位图信息。

 

3. 重建索引

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

可以使用重建索引来对索引的数据进行重组，使数据更加紧凑，并释放不需要的空间，从而提高访问效率和空间效率。DM8 提供的重建索引的系统函数为：

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

SCHEAM_NAME 为索引所在的模式名，INDEX_ID 为索引 ID。

 

a. 水平分区子表，临时表和系统表上建的索引不支持重建

b. 虚索引和聚集索引不支持重建

 

4. 删除索引

用户可能出于以下某项原因需要删除一个索引：

a. 不再需要该索引；

b. 该索引没有为针对其相关的表所发布的查询提供所期望的性能改善。例如，表可能很小，或者尽管表中有许多行但只有很少的索引项；

c. 应用没有用该索引来查询数据。

要想删除索引，则该索引必须包含在用户的模式中或用户必须具有 DROP ANY INDEX数据库权限。索引删除之后，该索引的段的所有簇都返回给包含它的表空间，并可用于表空间中的其他对象。

如何删除索引，取决于是否是用 CREATE INDEX 语句明确地创建该索引的，是则可以用 DROP INDEX 语句删除该索引。删除不存在的索引会报错。若指定 IF EXISTS 关键字，删除不存在的索引，不会报错。

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

除了删除普通索引，DM8 还提供删除聚集索引，只要其聚集索引是通过 CREATE CLUSTER INDEX 明确建立的。

删除聚集索引其实是使用 ROWID 作为索引列重建聚集索引，即跟新建聚集索引一样会重建这个表以及其所有索引。

删除表就自动删除了所有与其相关的索引。

 

5. 查看索引信息

创建索引后，可以通过 INDEXDEF 系统函数查看索引的定义。

INDEXDEF(INDEX_ID int, PREFLAG int);

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

 

https://eco.dameng.com