达梦数据库-SQL索引相关知识点

作为一个达梦的初学者，写这篇文章的目的是为了把学到的新知识做个总结梳理，在整理中查缺补漏，也希望大家看了我的文章能有收获，如有错误的地方欢迎指出。

索引概述：

        索引是与表相关的可选的结构（聚簇索引除外），索引的作用是为了加快对表的查询，更快速的定位到数据。数据库对表做DML操作，默认的维护索引。

索引分类：

• 聚集索引：每一个普通表有且只有一个聚集索引；
• 唯一索引：索引数据根据索引键唯一；
• 函数索引：包含函数/表达式的预先计算的值；
• 位图索引：对低基数的列创建位图索引；
• 位图连接索引：针对两个或者多个表连接的位图索引，主要用于数据仓库中；
• 全文索引：在表的文本列上而建的索引。

索引维护：

     创建索引

CREATE [OR REPLACE] [CLUSTER|NOT PARTIAL][UNIQUE | BITMAP| SPATIAL] INDEX <

索引名>

ON [<模式名>.]<表名>(<索引列定义>{,<索引列定义>}) [GLOBAL] [<STORAGE 子句>]

[NOSORT] [ONLINE];

<索引列定义>::= <索引列表达式>[ASC|DESC]

<STORAGE 子句>::=<STORAGE 子句 1>|<STORAGE 子句 2>

<STORAGE 子句 1>::= STORAGE(<STORAGE1 项> {,<STORAGE1 项>})

<STORAGE1 项> ::=

[INITIAL <初始簇数目>] |

[NEXT <下次分配簇数目>] |

[MINEXTENTS <最小保留簇数目>] |

[ON <表空间名>] |

[FILLFACTOR <填充比例>]|

[BRANCH <BRANCH 数>]|

[BRANCH (<BRANCH 数>, <NOBRANCH 数>)]|

[NOBRANCH ]|

[<CLUSTERBTR>]|

[SECTION (<区数>)]|

[STAT NONE]

<STORAGE 子句 2>::= STORAGE(<STORAGE2 项> {,<STORAGE2 项>})

<STORAGE2 项> ::= [ON <表空间名>]|[STAT NONE]

参数

1. UNIQUE 指明该索引为唯一索引；

2. BITMAP 指明该索引为位图索引；

3. SPATIAL 指明该索引为空间索引；

4. CLUSTER 指明该索引为聚簇索引（也叫聚集索引），不能应用到函数索引中；

5. NOT PARTIAL 指明该索引为非聚簇索引，缺省即为非聚簇索引；

6. <索引名> 指明被创建索引的名称，索引名称最大长度128字节；

7. <模式名> 指明被创建索引的基表属于哪个模式，缺省为当前模式；

8. <表名> 指明被创建索引的基表的名称；

9. <索引列定义> 指明创建索引的列定义。其中空间索引列的数据类型必须是DMGEO

包内的空间类型，如ST_GEOMETRY等；

10. <索引列表达式> 指明被创建的索引列可以为表达式；

11. GLOBAL 指明该索引为全局索引，仅堆表的水平分区表支持该选项，非水平分区表忽略该选项。 堆表上的PRIMARY KEY会自动变为全局索引；

12. ASC 递增顺序；

13. DESC 递减顺序；

14. <STORAGE子句> 普通表的索引参考<STORAGE子句1>， HUGE表的索引参考<STORAGE子句2>；

15. <STORAGE子句1>中， BRANCH和NOBRANCH只能用以指定聚集索引；

16. NOSORT 指明该索引相关的列已按照索引中指定的顺序有序，不需要在建索引时排序，提高建索引的效率。若数据非有序却指定了NOSORT，则在建索引时会报错；

17. ONLINE 表示支持异步索引，即创建索引过程中可以对索引依赖的表做增、删、改操作。

        重建索引 

SP_REBUILD_INDEX(SCHEMA_NAME varchar(256), INDEX_ID int);
或者
alter index ind_emp rebuild online;

         删除索引

drop index ind_emp;

 索引注意事项

      1、当模糊查询时，where条件为A like '%xxx'时是无法使用到A列上的索引。解决办法为建立函数索引，然后改写对应sql条件；若无法改写sql可以通过调整参数LIKE_OPT_FLAG。         LIKE_OPT_FLAG参数介绍：值为0为不优化；1：对于LIKE 表达式首尾存在通配符的情况，优化为POSITION()函数；对于 LIKE 表达式首部存在通配符，并且条件列存在REVERSE()函数索引时，优化为 REVERSE()函数；2：对于 COL1 LIKE COL2 || '%'的情况，优化为POSITION()函数；4：对于 COL1 LIKE ‘A’||‘B%’的情况，优化为 COL1 LIKE ‘AB%’。

开启参数后创建函数索引：

CREATE INDEX index_name ON table_name(POSITION('XXX',A));

      2、存在空值的列上创建索引时需要注意，空值数据信息是不被记录在索引里的，所以当涉及到这些数据的查询时，是无法使用索引的，可以利用一些小技巧，将null的信息也存放在索引中，如创建nvl(列名,0) 或者如下图

CREATE INDEX index_name ON table_name(列名,常量);

      3、不支持建立分区函数索引

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

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

      6、位图索引：创建方式和普通索引一致， 对低基数的列创建位图索引，能够有效提高，基于该列的查询效率，特别注意位图索引会严重影响到插入和更新操作，不能在业务表上建立。位图索引还具有以下约束：

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

        2) 不支持对大字段创建位图索引；

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

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

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

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

        7) MPP 环境下不支持位图索引的创建；

        8) 不支持快速装载建有位图索引的表；

        9) 不支持全局位图索引；

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

       7、空间索引： 创建时需指定SPATIAL关键字，删除方式和普通索引一样。 只能在DMGEO包内的空间类型的列上创建，如果使用DMGEO包内的空间位置进行查询时，使用空间索引能够提高查询效率。空间索引具有以下约束：

         1) 只支持在空间类型列上创建；

         2) 不支持使用 ONLINE 选项异步方式创建；

         3) 不支持在水平分区表上创建空间索引；

         4) 不支持在 MPP 环境和复制环境下创建空间索引；

         5) 空间索引不支持组合索引，只能在一个列上创建；

         6) 不支持在 4K 的页上建立空间索引。

达梦社区地址：https://eco.dameng.com