數據庫基礎與實例教程（達夢DM8） 課件 4.4索引使用及管理

數據庫基礎（基于達夢DM8）數據查詢及管理44.4索引使用及管理02索引的創(chuàng)建01理解管理索引的準則03索引的刪除理解管理索引的準則4.4.14.4.1理解管理索引的準則為什么使用索引

在數據庫中建立索引是為了加快數據的查詢速度。數據庫中的索引與書籍中的目錄類似。正如在本書中查找索引的相關內容，利用目錄可以快速查找到項目4是關于數據庫查詢管理方面的內容，接著再進入子目錄可見任務4.4正是關于索引相關的內容，找到對應的頁碼直接翻開，這樣就無須翻閱整本書依次翻看。

4.4.1理解管理索引的準則為什么使用索引在數據庫中，索引使對數據的查找不需要對整個表進行掃描，就可以在其中找到所需數據。數據庫中的索引是一個表中所包含的列值的列表，其中注明了表中包含各個值的行數據所在的存儲位置。

索引是與表相關的可選的結構（聚簇索引除外），它能使對應于表的SQL語句執(zhí)行得更快，因為有索引比沒有索引能更快地定位信息。

4.4.1理解管理索引的準則常見索引類型聚集索引：每一個普通表有且只有一個聚集索引；唯一索引：索引數據根據索引鍵唯一；全文索引：在表的文本列上而建的索引。4.4.1理解管理索引的準則1.創(chuàng)建索引的基本準則有以下的情況需要考慮創(chuàng)建索引。如果需要經常地檢索大表中的少量的行，就為查詢鍵創(chuàng)建索引；為了改善多個表的連接的性能，可為連接列創(chuàng)建索引；主鍵和唯一鍵自動具有索引，在外鍵上很多情況下也創(chuàng)建索引；表中數據的查詢量大大多于數據變化量的；小表不需要索引。4.4.1理解管理索引的準則1.創(chuàng)建索引的基本準則選取表中的索引列時可以考慮以下幾點。列中的值相對比較唯一，重復較少；取值范圍大，適合建立索引；CLOB和TEXT只能建立全文索引、BLOB不能建立任何索引。4.4.1理解管理索引的準則2．限制表中索引數量一個表可以有任意數量的索引。但是，索引越多，修改表數據的開銷就越大。當插入或刪除行時，表上的所有索引也要被更改；更改一個列時，包含該列的所有索引也要被更改，維護這個索引目錄也增加了很多的開銷。如果一個表主要僅用于讀，則索引多就有好處；如果一個表經常被更新，則索引不宜多建。4.4.1理解管理索引的準則3．為每個索引指定表空間可以在除臨時表空間、日志表空間和回滾表空間外的其他任何表空間中創(chuàng)建索引，也可以在其索引的表的相同或不同的表空間中創(chuàng)建索引。如果表及其索引使用相同的表空間能更方便地對數據庫進行管理。然而，將表及其索引放在不同的表空間（在不同磁盤上）產生的性能比放在相同的表空間更好，因為這樣做減少了磁盤競爭。4.4.1理解管理索引的準則4．為性能而安排索引列在CREATEINDEX語句中列的排序會影響查詢的性能。通常，將最常用的列放在最前面。如果查詢中有多個字段組合定位，則不應為每個字段單獨創(chuàng)建索引，而應該創(chuàng)建一個組合索引。當兩個或多個字段都是等值查詢時，組合索引中各個列的前后關系是無關緊要的。但是如果是非等值查詢時，要想有效利用組合索引，則應該按等值字段在前，非等值字段在后的原則創(chuàng)建組合索引，查詢時只能利用一個非等值的字段。索引的創(chuàng)建4.4.24.4.2索引的創(chuàng)建1.通用索引的創(chuàng)建用戶要在自己的模式中創(chuàng)建索引，至少要滿足如下條件之一。要被索引的表是在自己的模式中；在要被索引的表上有CREATEINDEX權限；具有CREATEANYINDEX數據庫權限。要在其他模式中創(chuàng)建索引，用戶必須具有CREATEANYINDEX數據庫權限。4.4.2索引的創(chuàng)建1.通用索引的創(chuàng)建創(chuàng)建索引的基本語法格式為。CREATE[ORREPLACE][CLUSTER|NOTPARTIAL][UNIQUE]INDEX<索引名>ON[<模式名>.]<表名>(<索引列定義>{,<索引列定義>})[GLOBAL][<STORAGE子句>][NOSORT][ONLINE][REVERSE];4.4.2索引的創(chuàng)建1.通用索引的創(chuàng)建CREATE[ORREPLACE][CLUSTER|NOTPARTIAL][UNIQUE]INDEX<索引名>ON[<模式名>.]<表名>(<索引列定義>{,<索引列定義>})[GLOBAL][<STORAGE子句>][NOSORT][ONLINE][REVERSE];①CLUSTER指明該索引為聚簇索引（也叫聚集索引），不能應用到函數索引中；②NOTPARTIAL指明該索引為非聚簇索引，缺省即為非聚簇索引；③UNIQUE指明該索引為唯一索引；4.4.2索引的創(chuàng)建1.通用索引的創(chuàng)建CREATE[ORREPLACE][CLUSTER|NOTPARTIAL][UNIQUE]INDEX<索引名>ON[<模式名>.]<表名>(<索引列定義>{,<索引列定義>})[GLOBAL][<STORAGE子句>][NOSORT][ONLINE][REVERSE];④<索引名>指明被創(chuàng)建索引的名稱，索引名稱最大長度128字節(jié)，方便后續(xù)管理，不能與其他索引名重復；⑤<模式名>指明被創(chuàng)建索引的基表屬于哪個模式，缺省為當前模式；⑥<表名>指明被創(chuàng)建索引的基表的名稱；4.4.2索引的創(chuàng)建1.通用索引的創(chuàng)建CREATE[ORREPLACE][CLUSTER|NOTPARTIAL][UNIQUE]INDEX<索引名>ON[<模式名>.]<表名>(<索引列定義>{,<索引列定義>})[GLOBAL][<STORAGE子句>][NOSORT][ONLINE][REVERSE];⑥<表名>指明被創(chuàng)建索引的基表的名稱；⑦<索引列定義>指明創(chuàng)建索引的列定義；⑧<索引列表達式>指明被創(chuàng)建的索引列可以為列名、列名列表或表達式；4.4.2索引的創(chuàng)建1.通用索引的創(chuàng)建CREATE[ORREPLACE][CLUSTER|NOTPARTIAL][UNIQUE]INDEX<索引名>ON[<模式名>.]<表名>(<索引列定義>{,<索引列定義>})[GLOBAL][<STORAGE子句>][NOSORT][ONLINE][REVERSE];⑨GLOBAL指明該索引為全局索引，用于水平分區(qū)表，非水平分區(qū)表忽略該選項；⑩ASC遞增順序；?DESC遞減順序；創(chuàng)建通用索引案例CREATEINDEXINDEX_STAFF_XMONSCH_FACTORY.STAFF(姓名);

【案例4-50】為SCH_FACTORY.STAFF的姓名列創(chuàng)建索引INDEX_STAFF_XM。創(chuàng)建通用索引案例CREATEUNIQUEINDEXUNINDEX_STAFF_XMJGONSCH_FACTORY.STAFF(姓名,籍貫);

【案例4-51】以SCH_FACTORY.STAFF的姓名和籍貫為索引列建立唯一索引UNINDEX_STAFF_XMJG。4.4.2索引的創(chuàng)建2.全文索引的創(chuàng)建用戶可以在指定的表的文本列上建立全文索引。全文索引創(chuàng)建的語法格式為。CREATECONTEXTINDEX<索引名>ON[<模式名>.]<表名>(<索引列定義>)[<STORAGE子句>][LEXER<分詞參數>][<SYNC子句>];4.4.2索引的創(chuàng)建2.全文索引的創(chuàng)建CREATECONTEXTINDEX<索引名>ON[<模式名>.]<表名>(<索引列定義>)[<STORAGE子句>][LEXER<分詞參數>][<SYNC子句>];①<索引名>指明要創(chuàng)建的全文索引的名稱，由于系統會為全文索引名加上前綴與后綴，因此用戶指定的全文索引名長度不能超過122字節(jié)；②<模式名>指明要創(chuàng)建全文索引的基表屬于哪個模式，缺省為當前模式；4.4.2索引的創(chuàng)建2.全文索引的創(chuàng)建CREATECONTEXTINDEX<索引名>ON[<模式名>.]<表名>(<索引列定義>)[<STORAGE子句>][LEXER<分詞參數>][<SYNC子句>];③<表名>指明要創(chuàng)建全文索引的基表的名稱；④<分詞參數>指明全文索引分詞器的分詞參數；⑤<storage子句>只有在指定表空間參數時有效，對于其他參數無效（即STORAGEONxxx或者TABLESPACExxx有效，而諸如INITIAL、NEXT等無效）；4.4.2索引的創(chuàng)建2.全文索引的創(chuàng)建CREATECONTEXTINDEX<索引名>ON[<模式名>.]<表名>(<索引列定義>)[<STORAGE子句>][LEXER<分詞參數>][<SYNC子句>];⑥分詞參數有5種：CHINESE_LEXER，中文最少分詞；CHINESE_VGRAM_LEXER，機械雙字分詞，CHINESE_FP_LEXER，中文最多分詞；ENGLISH_LEXER，英文分詞；DEFAULT_LEXER，中英文最少分詞，也是默認分詞；4.4.2索引的創(chuàng)建2.全文索引的創(chuàng)建CREATECONTEXTINDEX<索引名>ON[<模式名>.]<表名>(<索引列定義>)[<STORAGE子句>][LEXER<分詞參數>][<SYNC子句>];⑦<SYNC子句>指明全文索引的同步類型。不指定<SYNC子句>時創(chuàng)建全文索引后系統不進行全文索引填充；指定為SYNC時系統將在全文索引建立后對全文索引執(zhí)行一次完全填充；指定為SYNCTRANSACTION時系統將在每次事務提交后，自動以增量更新方式填充全文索引，不需要用戶手動填充。創(chuàng)建全文索引案例CREATECONTEXTINDEXFTINDEX_MANAGER_REMARKONSCH_FACTORY.MANAGER(備注)LEXERCHINESE_FP_LEXER;

【案例4-52】為SCH_FACTORY.MANAGER的備注列創(chuàng)建全文索引FTINDEX_MA