數(shù)據(jù)庫基礎(chǔ)與實(shí)例教程（達(dá)夢(mèng)DM8） 課件 4.4索引使用及管理

數(shù)據(jù)庫基礎(chǔ)（基于達(dá)夢(mèng)DM8）數(shù)據(jù)查詢及管理44.4索引使用及管理02索引的創(chuàng)建01理解管理索引的準(zhǔn)則03索引的刪除理解管理索引的準(zhǔn)則4.4.14.4.1理解管理索引的準(zhǔn)則為什么使用索引

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

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

索引是與表相關(guān)的可選的結(jié)構(gòu)（聚簇索引除外），它能使對(duì)應(yīng)于表的SQL語句執(zhí)行得更快，因?yàn)橛兴饕葲]有索引能更快地定位信息。

4.4.1理解管理索引的準(zhǔn)則常見索引類型聚集索引：每一個(gè)普通表有且只有一個(gè)聚集索引；唯一索引：索引數(shù)據(jù)根據(jù)索引鍵唯一；全文索引：在表的文本列上而建的索引。4.4.1理解管理索引的準(zhǔn)則1.創(chuàng)建索引的基本準(zhǔn)則有以下的情況需要考慮創(chuàng)建索引。如果需要經(jīng)常地檢索大表中的少量的行，就為查詢鍵創(chuàng)建索引；為了改善多個(gè)表的連接的性能，可為連接列創(chuàng)建索引；主鍵和唯一鍵自動(dòng)具有索引，在外鍵上很多情況下也創(chuàng)建索引；表中數(shù)據(jù)的查詢量大大多于數(shù)據(jù)變化量的；小表不需要索引。4.4.1理解管理索引的準(zhǔn)則1.創(chuàng)建索引的基本準(zhǔn)則選取表中的索引列時(shí)可以考慮以下幾點(diǎn)。列中的值相對(duì)比較唯一，重復(fù)較少；取值范圍大，適合建立索引；CLOB和TEXT只能建立全文索引、BLOB不能建立任何索引。4.4.1理解管理索引的準(zhǔn)則2．限制表中索引數(shù)量一個(gè)表可以有任意數(shù)量的索引。但是，索引越多，修改表數(shù)據(jù)的開銷就越大。當(dāng)插入或刪除行時(shí)，表上的所有索引也要被更改；更改一個(gè)列時(shí)，包含該列的所有索引也要被更改，維護(hù)這個(gè)索引目錄也增加了很多的開銷。如果一個(gè)表主要僅用于讀，則索引多就有好處；如果一個(gè)表經(jīng)常被更新，則索引不宜多建。4.4.1理解管理索引的準(zhǔn)則3．為每個(gè)索引指定表空間可以在除臨時(shí)表空間、日志表空間和回滾表空間外的其他任何表空間中創(chuàng)建索引，也可以在其索引的表的相同或不同的表空間中創(chuàng)建索引。如果表及其索引使用相同的表空間能更方便地對(duì)數(shù)據(jù)庫進(jìn)行管理。然而，將表及其索引放在不同的表空間（在不同磁盤上）產(chǎn)生的性能比放在相同的表空間更好，因?yàn)檫@樣做減少了磁盤競爭。4.4.1理解管理索引的準(zhǔn)則4．為性能而安排索引列在CREATEINDEX語句中列的排序會(huì)影響查詢的性能。通常，將最常用的列放在最前面。如果查詢中有多個(gè)字段組合定位，則不應(yīng)為每個(gè)字段單獨(dú)創(chuàng)建索引，而應(yīng)該創(chuàng)建一個(gè)組合索引。當(dāng)兩個(gè)或多個(gè)字段都是等值查詢時(shí)，組合索引中各個(gè)列的前后關(guān)系是無關(guān)緊要的。但是如果是非等值查詢時(shí)，要想有效利用組合索引，則應(yīng)該按等值字段在前，非等值字段在后的原則創(chuàng)建組合索引，查詢時(shí)只能利用一個(gè)非等值的字段。索引的創(chuàng)建4.4.24.4.2索引的創(chuàng)建1.通用索引的創(chuàng)建用戶要在自己的模式中創(chuàng)建索引，至少要滿足如下條件之一。要被索引的表是在自己的模式中；在要被索引的表上有CREATEINDEX權(quán)限；具有CREATEANYINDEX數(shù)據(jù)庫權(quán)限。要在其他模式中創(chuàng)建索引，用戶必須具有CREATEANYINDEX數(shù)據(jù)庫權(quán)限。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指明該索引為聚簇索引（也叫聚集索引），不能應(yīng)用到函數(shù)索引中；②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ù)管理，不能與其他索引名重復(fù)；⑤<模式名>指明被創(chuàng)建索引的基表屬于哪個(gè)模式，缺省為當(dā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)建索引的列定義；⑧<索引列表達(dá)式>指明被創(chuàng)建的索引列可以為列名、列名列表或表達(dá)式；4.4.2索引的創(chuàng)建1.通用索引的創(chuàng)建CREATE[ORREPLACE][CLUSTER|NOTPARTIAL][UNIQUE]INDEX<索引名>ON[<模式名>.]<表名>(<索引列定義>{,<索引列定義>})[GLOBAL][<STORAGE子句>][NOSORT][ONLINE][REVERSE];⑨GLOBAL指明該索引為全局索引，用于水平分區(qū)表，非水平分區(qū)表忽略該選項(xiàng)；⑩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<分詞參數(shù)>][<SYNC子句>];4.4.2索引的創(chuàng)建2.全文索引的創(chuàng)建CREATECONTEXTINDEX<索引名>ON[<模式名>.]<表名>(<索引列定義>)[<STORAGE子句>][LEXER<分詞參數(shù)>][<SYNC子句>];①<索引名>指明要?jiǎng)?chuàng)建的全文索引的名稱，由于系統(tǒng)會(huì)為全文索引名加上前綴與后綴，因此用戶指定的全文索引名長度不能超過122字節(jié)；②<模式名>指明要?jiǎng)?chuàng)建全文索引的基表屬于哪個(gè)模式，缺省為當(dāng)前模式；4.4.2索引的創(chuàng)建2.全文索引的創(chuàng)建CREATECONTEXTINDEX<索引名>ON[<模式名>.]<表名>(<索引列定義>)[<STORAGE子句>][LEXER<分詞參數(shù)>][<SYNC子句>];③<表名>指明要?jiǎng)?chuàng)建全文索引的基表的名稱；④<分詞參數(shù)>指明全文索引分詞器的分詞參數(shù)；⑤<storage子句>只有在指定表空間參數(shù)時(shí)有效，對(duì)于其他參數(shù)無效（即STORAGEONxxx或者TABLESPACExxx有效，而諸如INITIAL、NEXT等無效）；4.4.2索引的創(chuàng)建2.全文索引的創(chuàng)建CREATECONTEXTINDEX<索引名>ON[<模式名>.]<表名>(<索引列定義>)[<STORAGE子句>][LEXER<分詞參數(shù)>][<SYNC子句>];⑥分詞參數(shù)有5種：CHINESE_LEXER，中文最少分詞；CHINESE_VGRAM_LEXER，機(jī)械雙字分詞，CHINESE_FP_LEXER，中文最多分詞；ENGLISH_LEXER，英文分詞；DEFAULT_LEXER，中英文最少分詞，也是默認(rèn)分詞；4.4.2索引的創(chuàng)建2.全文索引的創(chuàng)建CREATECONTEXTINDEX<索引名>ON[<模式名>.]<表名>(<索引列定義>)[<STORAGE子句>][LEXER<分詞參數(shù)>][<SYNC子句>];⑦<SYNC子句>指明全文索引的同步類型。不指定<SYNC子句>時(shí)創(chuàng)建全文索引后系統(tǒng)不進(jìn)行全文索引填充；指定為SYNC時(shí)系統(tǒng)將在全文索引建立后對(duì)全文索引執(zhí)行一次完全填充；指定為SYNCTRANSACTION時(shí)系統(tǒng)將在每次事務(wù)提交后，自動(dòng)以增量更新方式填充全文索引，不需要用戶手動(dòng)填充。創(chuàng)建全文索引案例CREATECONTEXTINDEXFTINDEX_MANAGER_REMARKONSCH_FACTORY.MANAGER(備注)LEXERCHINESE_FP_LEXER;

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