PostgreSQL7.0手冊(cè)-用戶(hù)手冊(cè)-7. 索引和鍵字

2019-09-08 23:33:13

字體：大中小

供稿：網(wǎng)友

第七章.　索引和鍵字

鍵字　
部分索引　

索引主要用來(lái)提高數(shù)據(jù)庫(kù)性能。它們應(yīng)該定義在那些常用于做重復(fù)查詢(xún)的資格條件的表的列（或者表的字段）上。對(duì)索引的不當(dāng)使用會(huì)導(dǎo)致性能的下降，因?yàn)楦潞筒迦霑r(shí)間在索引出現(xiàn)時(shí)都增加了。　
索引還可以用于強(qiáng)制表的主鍵的唯一性。當(dāng)定義一個(gè)索引為　UNIQUE，那么將不允許多行具有相同的索引字段。這里的目的是保證數(shù)據(jù)完整性，而不是改善性能，因此上面的關(guān)于不當(dāng)使用的話并不適用。　

可以定義兩種類(lèi)型的索引：　

對(duì)于值索引（value　index），索引的鍵字域聲明為字段名；如果索引訪問(wèn)模式支持多字段索引，可以聲明多個(gè)字段。　
對(duì)于　函數(shù)索引（functional　index），它是定義在一個(gè)函數(shù)的結(jié)果上的，這個(gè)用戶(hù)定義函數(shù)對(duì)某一個(gè)表的一個(gè)或多個(gè)字段進(jìn)行操作。這是一個(gè)單字段索引（也就是說(shuō)，函數(shù)結(jié)果），即使該函數(shù)使用多于一個(gè)輸入字段。函數(shù)索引可以用于獲得對(duì)那些基于操作符的數(shù)據(jù)的快速訪問(wèn)，這些數(shù)據(jù)通常需要做一些轉(zhuǎn)換，轉(zhuǎn)換成基本數(shù)據(jù)類(lèi)型來(lái)使用。　
Postgres　為從索引提供　btree，rtree　和　hash（散列）訪問(wèn)模式。btree　訪問(wèn)模式是一個(gè)　Lehman-Yao　高并發(fā)　btrees　的實(shí)現(xiàn)。rtree　訪問(wèn)模式用　Guttman　的二分算法實(shí)現(xiàn)了標(biāo)準(zhǔn)的　rtrees。hash（散列）訪問(wèn)模式是　Litwin　的線性散列的一個(gè)實(shí)現(xiàn)。我們單獨(dú)的列出這些所用的算法是要表明所有這些訪問(wèn)模式都是完全動(dòng)態(tài)的并且不必進(jìn)行周期性的優(yōu)化（例如，象靜態(tài)散列算法常見(jiàn)的那樣）。　
當(dāng)一個(gè)索引了的字段涉及到使用：　<，<=，=，>=，>　之一進(jìn)行比較時(shí)，　Postgres　的查詢(xún)優(yōu)化器將考慮在掃描中使用　btree　索引。　

當(dāng)一個(gè)索引了的字段涉及到使用：　<<，&<，&>，>>，@，~=，&&　之一進(jìn)行比較時(shí)，Postgres　的查詢(xún)優(yōu)化器將考慮在掃描中使用　rtree　索引。　

當(dāng)一個(gè)索引了的字段涉及到使用　=　進(jìn)行比較時(shí)，Postgres　的查詢(xún)優(yōu)化器將考慮在掃描中使用散列（hash）索引。　

目前，只有　btree　訪問(wèn)模式支持多字段索引。缺省最多可以聲明　16　的關(guān)鍵字（這個(gè)限制可以在制作　Postgres　時(shí)改變）。　

可以為某個(gè)索引的每個(gè)字段聲明一個(gè)操作符表（operator　class）。這個(gè)操作符表標(biāo)識(shí)該索引用于該字段要使用的操作符。例如，一個(gè)在4字節(jié)整數(shù)上的　btree　索引將使用　int4_ops　表；這個(gè)操作符表包括用于4字節(jié)整數(shù)的比較函數(shù)。實(shí)際上，該字段類(lèi)型的缺省操作符通常就足夠了。擁有操作符表的原因是：對(duì)于某些數(shù)據(jù)類(lèi)型，可能存在多于一個(gè)有意義的順序。例如，我們可能想排序兩個(gè)復(fù)數(shù)，既可能通過(guò)絕對(duì)值，也可能通過(guò)實(shí)數(shù)部分。我們可以通過(guò)為該數(shù)據(jù)類(lèi)型定義兩個(gè)操作符表，然后在建立索引時(shí)選擇合適的一個(gè)來(lái)實(shí)現(xiàn)這個(gè)目的。同樣還有一些有特殊用途的操作符表：　

操作符表　box_ops　和　bigbox_ops　都支持對(duì)　box　數(shù)據(jù)類(lèi)型的　rtree　索引。兩者的區(qū)別是　bigbox_ops　把方形的坐標(biāo)按比例縮小，以避免在對(duì)非常大的浮點(diǎn)數(shù)坐標(biāo)做乘法，加法和減法時(shí)出現(xiàn)浮點(diǎn)例外。如果你的方形所在的范圍的大小是　20,000　單位的平面或更大，你應(yīng)該用　bigbox_ops。　
int24_ops　操作符表在為　int2　類(lèi)型的數(shù)據(jù)構(gòu)建索引并且與查詢(xún)資格條件里的　int4　數(shù)據(jù)做比較時(shí)很有用。類(lèi)似的，int42_ops　支持對(duì)要和查詢(xún)里的　int2　數(shù)據(jù)做比較的　int4　數(shù)據(jù)進(jìn)行索引。　
下面的查詢(xún)顯示所有定義了的操作符表：　
SELECT　am.amname　AS　acc_name,
　　　　　　　opc.opcname　AS　ops_name,
　　　　　　　opr.oprname　AS　ops_comp
　　　　FROM　pg_am　am,　pg_amop　amop,
　　　　　　　　　pg_opclass　opc,　pg_operator　opr
　　　　WHERE　amop.amopid　=　am.oid　AND
　　　　　　　　　　amop.amopclaid　=　opc.oid　AND
　　　　　　　　　　amop.amopopr　=　opr.oid
　　　　ORDER　BY　acc_name,　ops_name,　ops_comp

使用　DROP　INDEX　刪除一個(gè)索引。　
關(guān)鍵字
作者：由　Herouth　Maoz　寫(xiě)作。這些最早出現(xiàn)在　1998-03-02　用戶(hù)郵件列表里關(guān)于"主鍵（PRIMARY　KEY）和唯一約束（UNIQUE　constraints）有什么異同"問(wèn)題的解答．
Subject:　Re:　[QUESTIONS]　PRIMARY　KEY　|　UNIQUE

　　　　　　　　What's　the　difference　between:（下面兩者有何區(qū)別？）

　　　　　　　　　　　　　　PRIMARY　KEY(fields,...)　and
　　　　　　　　　　　　　　UNIQUE　(fields,...)

　　　　　　　-　Is　this　an　alias?（這是別名嗎？）
　　　　　　　-　If　PRIMARY　KEY　is　already　unique,　then　why（如果　PRIMARY　KEY　已經(jīng)唯一，）
　　　　　　　　　is　there　another　kind　of　key　named　UNIQUE?（那么為什么有另外一個(gè)名為　UNIQUE　類(lèi)型的鍵字？）

主鍵是用于標(biāo)識(shí)某特定行的字段．例如，身份證（社會(huì)安全號(hào)）標(biāo)識(shí)一個(gè)人．　
一個(gè)簡(jiǎn)單的由字段組成的　UNIQUE　與行標(biāo)識(shí)毫無(wú)關(guān)系．它只是一個(gè)完整性約束．例如，我收集了一些互聯(lián)網(wǎng)鏈接．每一個(gè)互聯(lián)網(wǎng)鏈接用一個(gè)唯一的數(shù)字標(biāo)識(shí)，也就是主鍵，這個(gè)鍵用于表中。　

但是，我的應(yīng)用要求每套集合還要有一個(gè)唯一的名稱(chēng)．因?yàn)檫@樣做的話一個(gè)想更改一個(gè)互聯(lián)網(wǎng)鏈接集合的"人"就可以找出該集合．畢竟，如果你有兩個(gè)集合都叫"生命科學(xué)"，一個(gè)被標(biāo)識(shí)成24433，另一個(gè)是29882，而24433那個(gè)是你要找的，這樣找要比從擁有唯一集合名稱(chēng)的組中找難得多．　

所以，用戶(hù)用名稱(chēng)來(lái)選擇集合．因此，對(duì)這個(gè)數(shù)據(jù)庫(kù)我們確信名稱(chēng)是唯一的．但是數(shù)據(jù)庫(kù)中沒(méi)有其他表與鏈接集合表通過(guò)鏈接集合名稱(chēng)相關(guān)．因此這樣做很效率很底．　

另外，盡管是唯一的，組名稱(chēng)實(shí)際上并不定義集合！例如，如果某人決定將集合名從"生命科學(xué)"改為"生物學(xué)"，該集合仍然是同一集合，只是名字不同而已．只要名稱(chēng)仍然是唯一的即可．　

所以：　

主鍵：　
　　
用于標(biāo)識(shí)某行而且與之相關(guān)．　
是不可能（或很難）更新．　
不應(yīng)該允許空（NULL）．　
唯一域/字段：　
　　
用于作為訪問(wèn)某行的可選手段．　
只要唯一就可以更新．　
可以為空（NULLs）．　
那么為什么標(biāo)準(zhǔn)　SQL　語(yǔ)法里沒(méi)有非唯一鍵的顯式定義呢？這是因?yàn)樗饕桥c具體實(shí)現(xiàn)相關(guān)的．SQL　不定義實(shí)現(xiàn)，只定義數(shù)據(jù)庫(kù)內(nèi)數(shù)據(jù)間的關(guān)系，　Postgres的確允許非唯一索引，但是用于增強(qiáng)　SQL　鍵字的索引總是唯一的．　
因此，你可以通過(guò)任何表中的列的組合來(lái)查詢(xún)?cè)摫恚还苣闶欠裨谶@些列上建立了索引．索引只是每個(gè)　RDBMS　的實(shí)現(xiàn)提供給你的一個(gè)工具，以便令常用的查詢(xún)可以更有效的進(jìn)行．有些　RDBMS　可能會(huì)給你另外一些工具，例如在主存里保留一個(gè)鍵值．它們有一些特殊的命令，例如　

CREATE　MEMSTORE　ON　　COLUMNS　

（這不是一個(gè)實(shí)際的命令,只是一個(gè)例子）．　
實(shí)際上，當(dāng)你創(chuàng)建一個(gè)主鍵或一個(gè)域/字段的唯一組合時(shí)，在SQL　聲明中沒(méi)有任何地方提到創(chuàng)建了索引，用該鍵對(duì)數(shù)據(jù)的檢索也不會(huì)比一次順序搜索更高效！　

所以，如果你想用一個(gè)并不唯一的字段組合作為第二個(gè)鍵字，你實(shí)際上不用聲明任何東西-只要用那個(gè)組合檢索就行了．但是，如果你想令檢索更高效一些，你就不得不依賴(lài)你的RDBMS　提供者給你的工具－不管是索引還是我想象的那種　MEMSTORE　命令或是一個(gè)智能的　RDBMS，它在你還沒(méi)有意識(shí)到你常用一套特定的鍵字組合來(lái)查詢(xún)時(shí)就給你創(chuàng)建了索引...　（它從經(jīng)驗(yàn)中學(xué)來(lái)）。　

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

部分索引
作者：這是從　Paul　M.　Aoki　1998-08-11的在　e-mail　列表中回答的問(wèn)題中摘下來(lái)的。
部分索引（partial　index）是建立在一個(gè)表的子集上的索引；該子集是用斷言（謂語(yǔ)）定義的。Postgres支持帶有任意斷言（謂語(yǔ)）的部分索引。我相信　IBM　用于　as/400　的　DB2　用單一子句的斷言支持部分索引。　
部分索引的主要?jiǎng)訖C(jī)是：如果你發(fā)出的所有查詢(xún)都可以從落在某個(gè)范圍內(nèi)的索引中獲益，我們?yōu)槭裁匆獙?duì)整個(gè)表建立一個(gè)索引，而帶來(lái)相關(guān)的更多的空間/時(shí)間開(kāi)銷(xiāo)呢？（還有其他原因；參閱　Stonebraker,　M,　1989b　獲取更多細(xì)節(jié)。）　

創(chuàng)建，更新和查詢(xún)部分索引的機(jī)制不算太差。麻煩的部分是索引的選擇（我該建立哪個(gè)索引？）和查詢(xún)優(yōu)化（我該使用哪個(gè)索引？）；也就是說(shuō)，涉及到?jīng)Q定什么樣的斷言可以有效地匹配工作負(fù)荷/查詢(xún)的那部分。對(duì)那些深入數(shù)據(jù)庫(kù)理論的人而言，這個(gè)問(wèn)題基本上類(lèi)似于對(duì)應(yīng)的物化的視圖問(wèn)題，雖然有不同的開(kāi)銷(xiāo)參數(shù)和公式。通常而言，這些問(wèn)題對(duì)標(biāo)準(zhǔn)的SQL　類(lèi)型是很難的問(wèn)題；對(duì)黑盒擴(kuò)展類(lèi)型而言，這些問(wèn)題是超級(jí)難的問(wèn)題，因?yàn)檫x擇性評(píng)估理論太原始了。　

參考　Stonebraker,　M,　1989b，Olson,　1993　和　Seshardri,　1995　獲取更多信息。　

------------------------------------------------------------------

上一篇：PostgreSQL7.0手冊(cè)-用戶(hù)手冊(cè)-8. 數(shù)組

下一篇：PostgreSQL7.0手冊(cè)-用戶(hù)手冊(cè)-19. SQL命令-LOCK