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