前臺(tái)的應(yīng)用程序要在數(shù)據(jù)庫(kù)服務(wù)器上起作用，最終靠的都是應(yīng)用程序中的一條條SQL語(yǔ)句。據(jù)不完全統(tǒng)計(jì)，SQL語(yǔ)句消耗了數(shù)據(jù)庫(kù)服務(wù)器80%左右的資源。所以，如何提高SQL語(yǔ)句的執(zhí)行效率，是在數(shù)據(jù)庫(kù)查詢優(yōu)化中必須要考慮的一個(gè)問(wèn)題。
但是，在實(shí)際工作中，許多程序員有個(gè)誤區(qū)，他們認(rèn)為數(shù)據(jù)庫(kù)查詢優(yōu)化不是他們的事情，而應(yīng)該是數(shù)據(jù)庫(kù)管理系統(tǒng)的任務(wù)，這是很多程序員的一個(gè)錯(cuò)誤認(rèn)識(shí)。他們錯(cuò)誤地認(rèn)為，他們所開(kāi)發(fā)的應(yīng)用程序的性能，跟他們所編寫(xiě)的SQL語(yǔ)句關(guān)系不大。一個(gè)好的查詢語(yǔ)句往往可以使得應(yīng)用程序的性能提高數(shù)十倍，而且，隨著記錄量的增加，這個(gè)效果還會(huì)以幾何級(jí)數(shù)上升。
另外，SQL語(yǔ)句是獨(dú)立于程序設(shè)計(jì)邏輯的，也就是說(shuō)，無(wú)論你的業(yè)務(wù)邏輯是怎么設(shè)計(jì)的，最后分解成SQL語(yǔ)句，就是那么幾個(gè)語(yǔ)法，所以，相對(duì)于應(yīng)用程序源代碼的優(yōu)化，SQL查詢語(yǔ)句的優(yōu)化在時(shí)間與風(fēng)險(xiǎn)上，都要低許多。
對(duì)于SQL語(yǔ)句的優(yōu)化，Oracle數(shù)據(jù)庫(kù)與SQL Server數(shù)據(jù)庫(kù)有類似的地方，也有一些差異。下面筆者就這兩者的差異與共同點(diǎn)做一個(gè)綜合介紹。
1、 通過(guò)索引來(lái)提高SQL語(yǔ)句的執(zhí)行效率。
一般來(lái)說(shuō)，對(duì)于一些經(jīng)常需要查詢的表，如產(chǎn)品信息表，我們可以通過(guò)建立外鍵來(lái)提高查詢效率。但是，也不是說(shuō)每個(gè)字段都要指定為外鍵。對(duì)于一些沒(méi)有指定外鍵的字段，我們可以為其建立索引，來(lái)提高數(shù)據(jù)表的查詢效率。
一般情況，在以下幾種情況下，我們可以為表建立索引來(lái)提高SQL語(yǔ)句的執(zhí)行效率。
一是對(duì)于一些經(jīng)常需要查詢的表，我們出于某種考慮，沒(méi)有設(shè)置外鍵，而是通過(guò)設(shè)置索引來(lái)提高對(duì)于表的查詢效率。在數(shù)據(jù)庫(kù)表中，外鍵的設(shè)置往往受到一些限制;而相對(duì)于外鍵來(lái)說(shuō)，索引的限制則要小得多。所以，在一些不使用外鍵的情況下，我們可以采用索引來(lái)提高對(duì)于表的查詢效率。
二是在需要頻繁進(jìn)行排序或者分組的表上，建立索引，可以極大地提高查詢效率。如ERP系統(tǒng)在設(shè)計(jì)的時(shí)候，可能需要頻繁地查詢采購(gòu)訂單明細(xì)，而且，這份報(bào)表是需要根據(jù)采購(gòu)訂單的號(hào)碼進(jìn)行排序。如此的話，在數(shù)據(jù)庫(kù)設(shè)計(jì)的時(shí)候，就可以把采購(gòu)訂單的號(hào)碼設(shè)置為索引，在每次運(yùn)行采購(gòu)訂單明細(xì)作業(yè)的時(shí)候，前臺(tái)ERP程序的性能就會(huì)高許多。而有時(shí)候，可能需要按供應(yīng)商來(lái)統(tǒng)計(jì)當(dāng)天的進(jìn)貨金額，此時(shí)，最好能夠在進(jìn)貨明細(xì)表中，給供應(yīng)商字段添加索引，這對(duì)于提高當(dāng)天進(jìn)貨匯總表作業(yè)的運(yùn)行效率，會(huì)有非常大的幫助。總之，在分組查詢或者排序查詢的表上，設(shè)置索引對(duì)于提高應(yīng)用程序的整體性能，具有不可忽視的作用。
三是如果待排序的列有多個(gè)，則需要在這些列上建立復(fù)合索引。如前臺(tái)應(yīng)用程序在生成當(dāng)天的進(jìn)貨明細(xì)表時(shí)，需要按供應(yīng)商、采購(gòu)訂單號(hào)、產(chǎn)品編號(hào)進(jìn)行排序。此時(shí)，也就是說(shuō)，在生成進(jìn)貨明細(xì)表這份報(bào)表時(shí)，要按這三個(gè)字段進(jìn)行排序。遇到這種情況時(shí)，對(duì)這些字段建立復(fù)合索引，提高查詢效率，是一個(gè)不錯(cuò)的選擇。
以上這些SQL語(yǔ)句優(yōu)化，Oracle數(shù)據(jù)庫(kù)與SQL Server數(shù)據(jù)庫(kù)都可以實(shí)現(xiàn)。雖然具體的實(shí)現(xiàn)語(yǔ)句可能稍有區(qū)別，但都是換湯不換藥，沒(méi)有本質(zhì)區(qū)別。
2、 把索引與數(shù)據(jù)文件存放在不同的磁盤(pán)中。
當(dāng)索引或者數(shù)據(jù)庫(kù)文件比較龐大時(shí)，把他們放在同一個(gè)磁盤(pán)中會(huì)加大輸入輸出等競(jìng)爭(zhēng)，從而抵消了索引的作用。為了解決過(guò)多的索引導(dǎo)致輸入輸出效率降低的問(wèn)題，在數(shù)據(jù)庫(kù)設(shè)計(jì)的時(shí)候，最好把索引跟用戶的表空間建立在不同的磁盤(pán)中。如把數(shù)據(jù)庫(kù)的表空間建立在一塊硬盤(pán)中，而把索引建立在另外一塊硬盤(pán)中。如此的話，就可以明顯地降低輸入輸出競(jìng)爭(zhēng)。也就是說(shuō)，這樣設(shè)計(jì)，隨著索引的增加，不會(huì)導(dǎo)致輸入輸出效率的低下。
不過(guò)，根據(jù)筆者的了解，索引與數(shù)據(jù)文件存放在不同的磁盤(pán)中，現(xiàn)在好像只有Oracle 數(shù)據(jù)庫(kù)可以做到，而微軟的SQL Server數(shù)據(jù)庫(kù)則無(wú)法實(shí)現(xiàn)這一點(diǎn)。
這也許根他們的定位不同。甲骨文的數(shù)據(jù)庫(kù)系統(tǒng)是針對(duì)大型的數(shù)據(jù)庫(kù)應(yīng)用而設(shè)計(jì)，所以，對(duì)于查詢的效率要求更加高。
3、 合理利用群集索引來(lái)提高SQL語(yǔ)句的執(zhí)行效率。
在一些特殊情況下，我們需要用到群集索引。如在ERP系統(tǒng)中，采購(gòu)部門(mén)經(jīng)常需要按月來(lái)查詢采購(gòu)訂單明細(xì)。如需要查詢2008年8月份的采購(gòu)訂單明細(xì)，而且這份報(bào)表需要按照供應(yīng)商、采購(gòu)訂單號(hào)碼、產(chǎn)品品號(hào)、交貨期等進(jìn)行排序，有時(shí)候還需要對(duì)供應(yīng)商進(jìn)行匯總。這一份簡(jiǎn)單的報(bào)表，用到了范圍查詢、多個(gè)字段記錄排序、記錄匯總等技術(shù)。此時(shí)，若能夠建立群集索引的話，對(duì)于提高這份報(bào)表的查詢效率，具有非常明顯的效果，特別是在數(shù)據(jù)記錄比較多的情況下，效果特別明顯。
所謂的群集索引與非群集索引的區(qū)別，主要是在于數(shù)據(jù)存放記錄上的差異。若我們采用群集索引的話，在存放記錄的時(shí)候，會(huì)按群集索引指定的規(guī)則存放。如對(duì)于采購(gòu)訂單中的供應(yīng)商ID字段采用群集索引，則在存放記錄的時(shí)候，會(huì)把相同的供應(yīng)商存放在一起。如此的話，在查詢的時(shí)候，效率就會(huì)高得多。而若沒(méi)有采用群集索引的話，則記錄保存時(shí)就是按記錄保存的先后順序來(lái)進(jìn)行記錄的存儲(chǔ)。
在建立群集索引的時(shí)候，Oracle 數(shù)據(jù)庫(kù)有一個(gè)，就是必須在數(shù)據(jù)庫(kù)表建立的時(shí)候，數(shù)據(jù)還沒(méi)導(dǎo)入之前就建立群集索引。也就是說(shuō)，若數(shù)據(jù)庫(kù)表中有記錄的話，則無(wú)法建立群集索引，這一點(diǎn)我們需要引起注意。
同時(shí)，若給某個(gè)表中的字段建立了群集索引，在記錄保存時(shí)，為了能夠按照群集索引所指定的規(guī)則存儲(chǔ)數(shù)據(jù)，需要對(duì)數(shù)據(jù)表中的記錄進(jìn)行一些調(diào)整，以符合原有的規(guī)則，如此的話，就會(huì)讓數(shù)據(jù)庫(kù)進(jìn)行一些額外的動(dòng)作，從而影響數(shù)據(jù)庫(kù)的性能。如在建立某個(gè)供應(yīng)商的采購(gòu)記錄時(shí)，為了把相同供應(yīng)商的記錄保存在一起(如我們把供應(yīng)商ID設(shè)置為群集索引)，就需要調(diào)整原有的記錄存儲(chǔ)結(jié)構(gòu)。雖然在保存的時(shí)候，犧牲一點(diǎn)效率，但是，這對(duì)于后續(xù)數(shù)據(jù)查詢，效率就會(huì)高許多。所以，對(duì)于群集索引的話，要讓其取得比較高的效果，有一個(gè)應(yīng)用前提，就是這個(gè)表中的數(shù)據(jù)要是經(jīng)常查詢的。如在ERP系統(tǒng)中，有一個(gè)庫(kù)存歷史交易報(bào)表，這個(gè)查詢就會(huì)經(jīng)常用到，而且，在查詢的過(guò)程中，都需要用到范圍查詢、排序、匯總等功能。所以，用在庫(kù)存歷史交易等數(shù)據(jù)庫(kù)表中，則效果會(huì)好得多。
若利用一句話來(lái)區(qū)分群集索引與非群集索引的區(qū)別，那就是群集索引“更新慢，查詢難快”。
在實(shí)際應(yīng)用中，如果利用SQL Server設(shè)計(jì)數(shù)據(jù)庫(kù)系統(tǒng)的過(guò)程中，很少用到群集索引技術(shù)(根據(jù)筆者的了解)。而在Oracle數(shù)據(jù)庫(kù)系統(tǒng)中，則應(yīng)用的相對(duì)比較廣泛一點(diǎn)。
不過(guò)，兩個(gè)數(shù)據(jù)庫(kù)在群集索引上都有一個(gè)共同點(diǎn)，就是要利用索引的話，必須在數(shù)據(jù)表建立的時(shí)候，就要設(shè)置群集索引。當(dāng)數(shù)據(jù)庫(kù)中有記錄的話，是不能建立群集索引的。
說(shuō)起區(qū)別，具體的實(shí)現(xiàn)語(yǔ)法有點(diǎn)差異，但是沒(méi)有什么本質(zhì)的區(qū)別。另外，對(duì)于甲骨文的數(shù)據(jù)庫(kù)來(lái)說(shuō)，可以把群集索引跟數(shù)據(jù)庫(kù)文件存放在不同的磁盤(pán)中，從而提高輸入輸出效率。但是，微軟的SQL Server數(shù)據(jù)庫(kù)則不行。
4、 使用Oracle數(shù)據(jù)庫(kù)自帶的優(yōu)化器優(yōu)化SQL語(yǔ)句。
在Oracle數(shù)據(jù)庫(kù)中，自帶了一個(gè)SQL語(yǔ)句的優(yōu)化工具，Oracle語(yǔ)句優(yōu)化器。利用這個(gè)工具，可以提高SQL語(yǔ)句的執(zhí)行效率。
一方面，Oracle數(shù)據(jù)庫(kù)語(yǔ)句優(yōu)化器是跟行鎖管理工具一起使用的，兩者往往需要配合使用，才能夠起到意想不到的作用。另一方面，對(duì)于“扶不起的阿斗”，Oracle語(yǔ)句優(yōu)化器也無(wú)能為力。也就是說(shuō)，對(duì)于一些實(shí)在寫(xiě)得很糟糕的SQL語(yǔ)句，語(yǔ)句優(yōu)化器對(duì)其也沒(méi)有絲毫辦法;只有對(duì)一些本來(lái)就比較合理的SQL語(yǔ)句，語(yǔ)句優(yōu)化器與行鎖管理工具，才能夠在這個(gè)基礎(chǔ)之上，再找到一些可以改善的地方，然后提出可行的改善意見(jiàn)。
具體來(lái)說(shuō)，語(yǔ)句優(yōu)化器，一方面確定SQL語(yǔ)句的最小代價(jià)執(zhí)行計(jì)劃，同時(shí)，確定數(shù)據(jù)的訪問(wèn)路徑，如是否采用索引或者表掃描;采用合理的表連接方式以及順序;判斷索引不可使用時(shí)是否需要進(jìn)行排序等等。綜合以上因素，然后給我們提出一個(gè)改善的建議。
在實(shí)際工作中，語(yǔ)句優(yōu)化器可以給我們找出一個(gè)SQL語(yǔ)句優(yōu)化過(guò)程中的盲點(diǎn)。而這個(gè)工具是微軟SQL Server數(shù)據(jù)庫(kù)所缺乏的，或者跟甲骨文的數(shù)據(jù)庫(kù)比起來(lái)，有差距的地方。
