数据库基础与实例教程（达梦DM8） 课件 4.4索引使用及管理

数据库基础（基于达梦DM8）数据查询及管理44.4索引使用及管理02索引的创建01理解管理索引的准则03索引的删除理解管理索引的准则4.4.14.4.1理解管理索引的准则为什么使用索引

在数据库中建立索引是为了加快数据的查询速度。数据库中的索引与书籍中的目录类似。正如在本书中查找索引的相关内容，利用目录可以快速查找到项目4是关于数据库查询管理方面的内容，接着再进入子目录可见任务4.4正是关于索引相关的内容，找到对应的页码直接翻开，这样就无须翻阅整本书依次翻看。

4.4.1理解管理索引的准则为什么使用索引在数据库中，索引使对数据的查找不需要对整个表进行扫描，就可以在其中找到所需数据。数据库中的索引是一个表中所包含的列值的列表，其中注明了表中包含各个值的行数据所在的存储位置。

索引是与表相关的可选的结构（聚簇索引除外），它能使对应于表的SQL语句执行得更快，因为有索引比没有索引能更快地定位信息。

4.4.1理解管理索引的准则常见索引类型聚集索引：每一个普通表有且只有一个聚集索引；唯一索引：索引数据根据索引键唯一；全文索引：在表的文本列上而建的索引。4.4.1理解管理索引的准则1.创建索引的基本准则有以下的情况需要考虑创建索引。如果需要经常地检索大表中的少量的行，就为查询键创建索引；为了改善多个表的连接的性能，可为连接列创建索引；主键和唯一键自动具有索引，在外键上很多情况下也创建索引；表中数据的查询量大大多于数据变化量的；小表不需要索引。4.4.1理解管理索引的准则1.创建索引的基本准则选取表中的索引列时可以考虑以下几点。列中的值相对比较唯一，重复较少；取值范围大，适合建立索引；CLOB和TEXT只能建立全文索引、BLOB不能建立任何索引。4.4.1理解管理索引的准则2．限制表中索引数量一个表可以有任意数量的索引。但是，索引越多，修改表数据的开销就越大。当插入或删除行时，表上的所有索引也要被更改；更改一个列时，包含该列的所有索引也要被更改，维护这个索引目录也增加了很多的开销。如果一个表主要仅用于读，则索引多就有好处；如果一个表经常被更新，则索引不宜多建。4.4.1理解管理索引的准则3．为每个索引指定表空间可以在除临时表空间、日志表空间和回滚表空间外的其他任何表空间中创建索引，也可以在其索引的表的相同或不同的表空间中创建索引。如果表及其索引使用相同的表空间能更方便地对数据库进行管理。然而，将表及其索引放在不同的表空间（在不同磁盘上）产生的性能比放在相同的表空间更好，因为这样做减少了磁盘竞争。4.4.1理解管理索引的准则4．为性能而安排索引列在CREATEINDEX语句中列的排序会影响查询的性能。通常，将最常用的列放在最前面。如果查询中有多个字段组合定位，则不应为每个字段单独创建索引，而应该创建一个组合索引。当两个或多个字段都是等值查询时，组合索引中各个列的前后关系是无关紧要的。但是如果是非等值查询时，要想有效利用组合索引，则应该按等值字段在前，非等值字段在后的原则创建组合索引，查询时只能利用一个非等值的字段。索引的创建4.4.24.4.2索引的创建1.通用索引的创建用户要在自己的模式中创建索引，至少要满足如下条件之一。要被索引的表是在自己的模式中；在要被索引的表上有CREATEINDEX权限；具有CREATEANYINDEX数据库权限。要在其他模式中创建索引，用户必须具有CREATEANYINDEX数据库权限。4.4.2索引的创建1.通用索引的创建创建索引的基本语法格式为。CREATE[ORREPLACE][CLUSTER|NOTPARTIAL][UNIQUE]INDEX<索引名>ON[<模式名>.]<表名>(<索引列定义>{,<索引列定义>})[GLOBAL][<STORAGE子句>][NOSORT][ONLINE][REVERSE];4.4.2索引的创建1.通用索引的创建CREATE[ORREPLACE][CLUSTER|NOTPARTIAL][UNIQUE]INDEX<索引名>ON[<模式名>.]<表名>(<索引列定义>{,<索引列定义>})[GLOBAL][<STORAGE子句>][NOSORT][ONLINE][REVERSE];①CLUSTER指明该索引为聚簇索引（也叫聚集索引），不能应用到函数索引中；②NOTPARTIAL指明该索引为非聚簇索引，缺省即为非聚簇索引；③UNIQUE指明该索引为唯一索引；4.4.2索引的创建1.通用索引的创建CREATE[ORREPLACE][CLUSTER|NOTPARTIAL][UNIQUE]INDEX<索引名>ON[<模式名>.]<表名>(<索引列定义>{,<索引列定义>})[GLOBAL][<STORAGE子句>][NOSORT][ONLINE][REVERSE];④<索引名>指明被创建索引的名称，索引名称最大长度128字节，方便后续管理，不能与其他索引名重复；⑤<模式名>指明被创建索引的基表属于哪个模式，缺省为当前模式；⑥<表名>指明被创建索引的基表的名称；4.4.2索引的创建1.通用索引的创建CREATE[ORREPLACE][CLUSTER|NOTPARTIAL][UNIQUE]INDEX<索引名>ON[<模式名>.]<表名>(<索引列定义>{,<索引列定义>})[GLOBAL][<STORAGE子句>][NOSORT][ONLINE][REVERSE];⑥<表名>指明被创建索引的基表的名称；⑦<索引列定义>指明创建索引的列定义；⑧<索引列表达式>指明被创建的索引列可以为列名、列名列表或表达式；4.4.2索引的创建1.通用索引的创建CREATE[ORREPLACE][CLUSTER|NOTPARTIAL][UNIQUE]INDEX<索引名>ON[<模式名>.]<表名>(<索引列定义>{,<索引列定义>})[GLOBAL][<STORAGE子句>][NOSORT][ONLINE][REVERSE];⑨GLOBAL指明该索引为全局索引，用于水平分区表，非水平分区表忽略该选项；⑩ASC递增顺序；⑪DESC递减顺序；创建通用索引案例CREATEINDEXINDEX_STAFF_XMONSCH_FACTORY.STAFF(姓名);

【案例4-50】为SCH_FACTORY.STAFF的姓名列创建索引INDEX_STAFF_XM。创建通用索引案例CREATEUNIQUEINDEXUNINDEX_STAFF_XMJGONSCH_FACTORY.STAFF(姓名,籍贯);

【案例4-51】以SCH_FACTORY.STAFF的姓名和籍贯为索引列建立唯一索引UNINDEX_STAFF_XMJG。4.4.2索引的创建2.全文索引的创建用户可以在指定的表的文本列上建立全文索引。全文索引创建的语法格式为。CREATECONTEXTINDEX<索引名>ON[<模式名>.]<表名>(<索引列定义>)[<STORAGE子句>][LEXER<分词参数>][<SYNC子句>];4.4.2索引的创建2.全文索引的创建CREATECONTEXTINDEX<索引名>ON[<模式名>.]<表名>(<索引列定义>)[<STORAGE子句>][LEXER<分词参数>][<SYNC子句>];①<索引名>指明要创建的全文索引的名称，由于系统会为全文索引名加上前缀与后缀，因此用户指定的全文索引名长度不能超过122字节；②<模式名>指明要创建全文索引的基表属于哪个模式，缺省为当前模式；4.4.2索引的创建2.全文索引的创建CREATECONTEXTINDEX<索引名>ON[<模式名>.]<表名>(<索引列定义>)[<STORAGE子句>][LEXER<分词参数>][<SYNC子句>];③<表名>指明要创建全文索引的基表的名称；④<分词参数>指明全文索引分词器的分词参数；⑤<storage子句>只有在指定表空间参数时有效，对于其他参数无效（即STORAGEONxxx或者TABLESPACExxx有效，而诸如INITIAL、NEXT等无效）；4.4.2索引的创建2.全文索引的创建CREATECONTEXTINDEX<索引名>ON[<模式名>.]<表名>(<索引列定义>)[<STORAGE子句>][LEXER<分词参数>][<SYNC子句>];⑥分词参数有5种：CHINESE_LEXER，中文最少分词；CHINESE_VGRAM_LEXER，机械双字分词，CHINESE_FP_LEXER，中文最多分词；ENGLISH_LEXER，英文分词；DEFAULT_LEXER，中英文最少分词，也是默认分词；4.4.2索引的创建2.全文索引的创建CREATECONTEXTINDEX<索引名>ON[<模式名>.]<表名>(<索引列定义>)[<STORAGE子句>][LEXER<分词参数>][<SYNC子句>];⑦<SYNC子句>指明全文索引的同步类型。不指定<SYNC子句>时创建全文索引后系统不进行全文索引填充；指定为SYNC时系统将在全文索引建立后对全文索引执行一次完全填充；指定为SYNCTRANSACTION时系统将在每次事务提交后，自动以增量更新方式填充全文索引，不需要用户手动填充。创建全文索引案例CREATECONTEXTINDEXFTINDEX_MANAGER_REMARKONSCH_FACTORY.MANAGER(备注)LEXERCHINESE_FP_LEXER;

【案例4-52】为SCH_FACTORY.MANAGER的备注列创建全文索引FTINDEX_MA