一、 在數(shù)據(jù)庫排序查詢優(yōu)化上的差異。
在講解這個(gè)內(nèi)容之前，為了讀者能夠清楚我講的內(nèi)容，我要先談一個(gè)概念。命中率，它是指從內(nèi)存中取得數(shù)據(jù)而不從磁盤中取得數(shù)據(jù)的比率。我們在前幾篇文章中都提到過，當(dāng)在數(shù)據(jù)庫中查詢數(shù)據(jù)時(shí)，數(shù)據(jù)庫服務(wù)器都是先從內(nèi)存中尋找數(shù)據(jù)。只有在內(nèi)存中數(shù)據(jù)不存在的情況下，才會(huì)去讀取數(shù)據(jù)庫文件的內(nèi)容。而且，從內(nèi)存中查詢數(shù)據(jù)要比在數(shù)據(jù)庫文件中查詢數(shù)據(jù)效率高得多。從這方面講，我們?nèi)裟軌蛱岣哌@個(gè)查詢的命中率，則顯然可以提高數(shù)據(jù)庫系統(tǒng)的查詢效率。
雖然SQL Server與Oracle在這方面都有所作為，但是，筆者認(rèn)為，Oracle數(shù)據(jù)庫在這方面的優(yōu)勢比較明顯。特別是Oracle數(shù)據(jù)庫采用了臨時(shí)段的管理機(jī)制，明顯提高了數(shù)據(jù)庫查詢的命中率。
那什么叫作數(shù)據(jù)庫的臨時(shí)段呢?假設(shè)當(dāng)我們剛查完員工信息表后，此時(shí)，員工信息表的內(nèi)容就存在數(shù)據(jù)庫服務(wù)器的內(nèi)存中。此時(shí)，我們需要對這個(gè)表進(jìn)行排序查詢，如我們希望查詢出工齡超過兩年的員工，并且按工齡的長短進(jìn)行排序。此時(shí)，Oracle數(shù)據(jù)庫服務(wù)器會(huì)設(shè)法在內(nèi)存中排序區(qū)對所有行進(jìn)行排序。而這個(gè)排序區(qū)的大小則有數(shù)據(jù)庫的初始化文件init.ora進(jìn)行確定。當(dāng)這個(gè)排序區(qū)不夠大，不能夠容納我們所查詢出來的員工信息記錄數(shù)時(shí)，數(shù)據(jù)庫就會(huì)在排序操作期間，在數(shù)據(jù)庫服務(wù)器中開辟臨時(shí)段。很明顯，在查詢操作過程中，若開辟臨時(shí)段的話，會(huì)減低數(shù)據(jù)庫的命中率，降低排序查詢的效率。我們現(xiàn)在希望這個(gè)排序能夠在內(nèi)存中完成，而不需要開辟額外的臨時(shí)段，如此的話，就可以消除向臨時(shí)段寫數(shù)據(jù)的開銷，提高排序查詢的效率。所以，當(dāng)我們數(shù)據(jù)庫中的數(shù)據(jù)比較龐大時(shí)，我們可以考慮增加這個(gè)排序區(qū)的長度，以避免臨時(shí)段的需要。正是這個(gè)臨時(shí)段的問題，如我們剛查詢完員工信息表，查詢完成之后，再對該表進(jìn)行排序查詢，就覺得好像仍然是新的查詢一樣。其實(shí)，這個(gè)排序查詢的問題，我們可以通過一定的方法對此進(jìn)行優(yōu)化，以提高排序查詢的效率。
修改方法：
第一步:先利用查詢語句判斷，是否有臨時(shí)段需求的產(chǎn)生。
select * from v$sysstat where name='dtmfg(disk)' or name='dtmfg(memory)';
如我們可以定時(shí)利用以上語句，來查詢是否有臨時(shí)段需求的產(chǎn)生。這條語句中，dtmfg是具體的數(shù)據(jù)庫實(shí)例名，我們只需要修改這個(gè)名字，就可以查詢到我們需要的內(nèi)容。若在查詢結(jié)果中，發(fā)現(xiàn)有臨時(shí)段需求產(chǎn)生的話，則就需要考慮修改相關(guān)的配置文件，以優(yōu)化排序查詢性能。
第二步：修改參數(shù)配置文件。
我們需要修改inint.ora文件，修改里面的SORT-AREA-SIZE的值。不過，修改這個(gè)配置文件之后，還必須重新啟動(dòng)數(shù)據(jù)庫才會(huì)生效。一般情況下，數(shù)據(jù)庫管理員需要定時(shí)查詢這個(gè)臨時(shí)段需求，然后根據(jù)情況，不斷的進(jìn)行調(diào)整，做好數(shù)據(jù)排序查詢優(yōu)化功能。
而在微軟的SQL Server數(shù)據(jù)庫中，筆者沒有發(fā)現(xiàn)類似的功能。
二、 利用哈希聯(lián)接，提高多表查詢性能。
多表之間的關(guān)聯(lián)查詢，無論是哪種關(guān)聯(lián)類型，到數(shù)據(jù)量比較大時(shí)，對于數(shù)據(jù)庫服務(wù)器的查詢性能都是一個(gè)非常大的考驗(yàn)。所以，在實(shí)際數(shù)據(jù)庫設(shè)計(jì)中，當(dāng)數(shù)據(jù)量比較大的時(shí)候，需要采用哈希聯(lián)接，來提高數(shù)據(jù)庫多表查詢的效率。一般來說，哈希聯(lián)接比其他幾種表之間的連接方式，對于服務(wù)器來說開銷要小得多，從而可以提高服務(wù)器的查詢效率。
哈希聯(lián)接一共有三種聯(lián)接方式，分別為內(nèi)存中的哈希聯(lián)結(jié)、Gracle哈希聯(lián)接與遞歸哈希聯(lián)接。
所謂內(nèi)存的哈希聯(lián)接，是指先掃描或計(jì)算整個(gè)生成輸入，然后在內(nèi)存中生成哈希表。根據(jù)哈希鍵計(jì)算出哈希值，然后將每行插入哈希存儲(chǔ)。如果整個(gè)生成輸入比可用內(nèi)存少，則可以將所有行都插入到哈希表中。生成階段之后就是探測階段。一次一行的3對整個(gè)探測輸入進(jìn)行掃描或者計(jì)算，并為每個(gè)探測行計(jì)算哈希健的值，并按一定的規(guī)則生成匹配項(xiàng)。
其他兩種哈希聯(lián)接也各有各的用途，在這里就不重復(fù)描述了。下面，筆者談?wù)勗谶@兩個(gè)數(shù)據(jù)庫中，對于哈希聯(lián)接所持的不同態(tài)度。
在微軟的SQL Server數(shù)據(jù)庫中，默認(rèn)情況下，是采用哈希聯(lián)接的。在優(yōu)化過程中并不能夠確定到底采用上面的那種聯(lián)接方式。所以，微軟的數(shù)據(jù)庫系統(tǒng)默認(rèn)情況下，實(shí)采用內(nèi)存中的哈希聯(lián)接，然后再根據(jù)生成輸入的大小逐漸轉(zhuǎn)換到GRACLE哈希聯(lián)接以及遞歸哈希聯(lián)接。
但是，若是在數(shù)據(jù)量不大的情況下，哈希聯(lián)接不但不會(huì)提高數(shù)據(jù)庫的查詢效率，反而會(huì)有所下降。所以，Oracle數(shù)據(jù)庫在默認(rèn)情況下，是沒有啟用哈希聯(lián)接的，而是在實(shí)際需要時(shí)，可添加一些設(shè)置使得數(shù)據(jù)庫在有多大聯(lián)接查詢發(fā)生時(shí)才利用哈希聯(lián)結(jié)。也就是說，Oracle數(shù)據(jù)庫可以設(shè)置判斷條件，數(shù)據(jù)庫管理員可以指定，當(dāng)滿足一定的條件時(shí)才調(diào)用哈希聯(lián)結(jié)，利用它來提高多表查詢的效率。
三、 大表查詢優(yōu)化。
一般來說，在數(shù)據(jù)庫設(shè)計(jì)中，數(shù)據(jù)庫設(shè)計(jì)人員會(huì)利用索引等技術(shù)來提高數(shù)據(jù)庫的查詢效率。但是，索引的作用，也不是無限擴(kuò)大的，它受到一定的限制。一般來說，他跟數(shù)據(jù)量是成反比的，當(dāng)數(shù)據(jù)量越大時(shí)，他的作用就越小。確切的說，剛開始時(shí)隨著數(shù)據(jù)量的增大其對數(shù)據(jù)庫查詢的優(yōu)化作用會(huì)逐漸增大;但是，當(dāng)數(shù)據(jù)數(shù)量累積到一定程度時(shí)，其效果就會(huì)逐漸減小。當(dāng)數(shù)據(jù)量達(dá)到一定的程度，如一百萬條時(shí)，索引的作用就非常微小了。
針對這種大容量記錄的表，若需要查詢，其查詢效率不高。為此，數(shù)據(jù)庫該采用什么方式來提高這個(gè)大表的查詢效率呢?
這兩大數(shù)據(jù)庫不約而同的采用了哈希族的方式，來提高大表的查詢。
如現(xiàn)在在設(shè)計(jì)一個(gè)圖書館管理系統(tǒng)，這個(gè)系統(tǒng)中，讀者的信息有幾百萬、幾千萬條。當(dāng)讀者的信息存儲(chǔ)在一個(gè)普通表中的時(shí)候，這些記錄按照存儲(chǔ)到數(shù)據(jù)庫中的先后順序，物理地保存到分配的塊中。也就是說，數(shù)據(jù)庫服務(wù)器的數(shù)據(jù)文件，或者數(shù)據(jù)表，就好像一個(gè)個(gè)抽屜，數(shù)據(jù)庫是按先后順序一條記錄一條記錄地從上到下存放數(shù)據(jù)。當(dāng)整個(gè)表的容量逐漸增加時(shí)，該表相應(yīng)的速度就會(huì)非常慢。
在剛開始的時(shí)候，人們想到了利用鏃表的方式來提高這個(gè)性能。也就是說，把整個(gè)抽屜分成幾排，然后每排給他們歸一類，如按辦圖書卡時(shí)的年齡進(jìn)行分類，10歲以下的一類，10歲到15歲的一類等等。如此的話，在存讀者信息的時(shí)候，就不會(huì)簡單的按照辦卡時(shí)間來存儲(chǔ)，而是按照類別來存儲(chǔ)。如果是屬于10歲到15歲整個(gè)類，就會(huì)被物理的存儲(chǔ)在同一個(gè)系列的塊中。如此的話，就可以分類查找信息的速度。如果可以按類別查找數(shù)據(jù)信息，速度會(huì)非常快。
但是，隨著數(shù)據(jù)庫中的鏃塊增加，會(huì)影響數(shù)據(jù)庫的整體運(yùn)行性能。這個(gè)問題發(fā)生之后，數(shù)據(jù)庫開發(fā)人員又想到了利用哈希函數(shù)來解決這個(gè)問題。哈希函數(shù)將會(huì)給定一個(gè)數(shù)值用來限定鏃塊數(shù)的數(shù)量的預(yù)計(jì)范圍。
也就是說，現(xiàn)在我們要建立一個(gè)圖書館用戶的表格，我們可以利用圖書卡的卡號作為鏃主鍵將有利于數(shù)據(jù)的存儲(chǔ)分布。但是，當(dāng)讀者增加時(shí)，就需要使用一個(gè)哈希函數(shù)來約束鏃塊的數(shù)量。
不過Oracle數(shù)據(jù)庫在使用鏃技術(shù)來優(yōu)化大表數(shù)據(jù)查詢之外，還采用了另外一種獨(dú)有的技術(shù)，即分區(qū)表的形式，來提高用戶對于大表的查詢效率。
在Oracle數(shù)據(jù)庫中，可以將一個(gè)大表分開放置在幾個(gè)邏輯分區(qū)中，或者是將一個(gè)大表分成幾張小表。在查詢時(shí)，即可以單獨(dú)的對這些小表進(jìn)行查詢，而且，也可以利用union all參數(shù)進(jìn)行一起查詢。
如在設(shè)計(jì)銷售訂單管理系統(tǒng)時(shí)，我們可以按年度把銷售訂單表分割成幾張小表，如此的話，后續(xù)的查詢效率會(huì)比一張大表高很多。不過，這個(gè)技術(shù)的應(yīng)用，關(guān)鍵在于如何對表進(jìn)行分割，以及如何把表放置在幾個(gè)邏輯分區(qū)中，這需要有一定經(jīng)驗(yàn)的數(shù)據(jù)庫設(shè)計(jì)工程師才能設(shè)計(jì)出一個(gè)好的方案。關(guān)于這方面的內(nèi)容，在后續(xù)的文章中，筆者會(huì)有所闡述。
