SQLServer中怎樣優(yōu)化多表查詢

今天就跟大家聊聊有關(guān)SQLServer中怎樣優(yōu)化多表查詢，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

　　SQLServer多表查詢優(yōu)化方案有哪些

　　1.執(zhí)行路徑

　　ORACLE的這個(gè)功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存的使用:我們發(fā)現(xiàn),單表數(shù)據(jù)的統(tǒng)計(jì)比多表統(tǒng)計(jì)的速度完全是兩個(gè)概念.單表統(tǒng)計(jì)可能只要0.02秒,但是2張表聯(lián)合統(tǒng)計(jì)就

　　可能要幾十秒了.這是因?yàn)镺RACLE只對簡單的表提供高速緩沖(cachebuffering),這個(gè)功能并不適用于多表連接查詢..數(shù)據(jù)庫管理員必須在init.ora中為這個(gè)區(qū)域設(shè)置合適的參數(shù),當(dāng)這個(gè)內(nèi)存區(qū)域越大,就可以保留更多的語句,當(dāng)然被共享的可能性也就越大了.

　　2.選擇最有效率的表名順序(記錄少的放在后面)

　　ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最后的表(基礎(chǔ)表drivingtable)將被最先處理.在FROM子句中包含多個(gè)表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表.當(dāng)ORACLE處理多個(gè)表時(shí),會運(yùn)用排序及合并的方式連接它們.首先,掃描第一個(gè)表(FROM子句中最后的那個(gè)表)并對記錄進(jìn)行派序,然后掃描第二個(gè)表(FROM子句中最后第二個(gè)表),最后將所有從第二個(gè)表中檢索出的記錄與第一個(gè)表中合適記錄進(jìn)行合并.

　　例如:

　　表TAB116,384條記錄

　　表TAB21條記錄

　　選擇TAB2作為基礎(chǔ)表(最好的方法)

　　selectcount(*)fromtab1,tab2執(zhí)行時(shí)間0.96秒

　　選擇TAB2作為基礎(chǔ)表(不佳的方法)

　　selectcount(*)fromtab2,tab1執(zhí)行時(shí)間26.09秒

　　假如有3個(gè)以上的表連接查詢,那就需要選擇交叉表(intersectiontable)作為基礎(chǔ)表,交叉表是指那個(gè)被其他表所引用的表.

　　例如:EMP表描述了LOCATION表和CATEGORY表的交集.

　　SELECT*FROMLOCATIONL,CATEGORYC,EMPEWHEREE.EMP_NOBETWEEN1000AND2000ANDE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCN

　　將比下列SQL更有效率

　　SELECT*FROMEMPE,LOCATIONL,CATEGORYCWHEREE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCNANDE.EMP_NOBETWEEN1000AND2000

　　3.WHERE子句中的連接順序(條件細(xì)的放在后面)

　　ORACLE采用自下而上的順序解析WHERE子句,根據(jù)這個(gè)原理,表之間的連接必須寫在其他WHERE條件之前,那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾.

　　例如:

　　(低效,執(zhí)行時(shí)間156.3秒)

　　SELECT…FROMEMPEWHERESAL>50000ANDJOB=‘MANAGER’AND25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);(高效,執(zhí)行時(shí)間10.6秒)SELECT…FROMEMPEWHERE25<(selectcount(*)fromempwheremgr=e.empno)andsal>50000ANDJOB=‘MANAGER’;

　　4.SELECT子句中避免使用'*'

　　當(dāng)你想在SELECT子句中列出所有的COLUMN時(shí),使用動(dòng)態(tài)SQL列引用'*'是一個(gè)方便的方法.不幸的是,這是一個(gè)非常低效的方法.實(shí)際上,ORACLE在解析的過程中,會將'*'依次轉(zhuǎn)換成所有的列名,這個(gè)工作是通過查詢數(shù)據(jù)字典完成的,這意味著將耗費(fèi)更多的時(shí)間.

　　SQLServer多表查詢優(yōu)化方案有哪些

　　5.減少訪問數(shù)據(jù)庫的次數(shù)

　　當(dāng)執(zhí)行每條SQL語句時(shí),ORACLE在內(nèi)部執(zhí)行了許多工作:解析SQL語句,估算索引的利用率,綁定變量,讀數(shù)據(jù)塊等等.由此可見,減少訪問數(shù)據(jù)庫的次數(shù),就能實(shí)際上減少ORACLE的工作量.

　　方法1(低效)

　　SELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=342;SELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=291;

　　方法2(高效)

　　SELECTA.EMP_NAME,A.SALARY,A.GRADE,B.EMP_NAME,B.SALARY,B.GRADEFROMEMPA,EMPBWHEREA.EMP_NO=342ANDB.EMP_NO=291;

　　6.刪除重復(fù)記錄

　　最高效的刪除重復(fù)記錄方法(因?yàn)槭褂昧薘OWID)

　　DELETEFROMEMPEWHEREE.ROWID>(SELECTMIN(X.ROWID)FROMEMPXWHEREX.EMP_NO=E.EMP_NO);

　　7.用TRUNCATE替代DELETE

　　當(dāng)刪除表中的記錄時(shí),在通常情況下,回滾段(rollbacksegments)用來存放可以被恢復(fù)的信息.假如你沒有COMMIT事務(wù),ORACLE會將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地說是恢復(fù)到執(zhí)行刪除命令之前的狀況)，而當(dāng)運(yùn)用TRUNCATE時(shí),回滾段不再存放任何可被恢復(fù)的信息.當(dāng)命令運(yùn)行后,數(shù)據(jù)不能被恢復(fù).因此很少的資源被調(diào)用,執(zhí)行時(shí)間也會很短.

　　8.盡量多使用COMMIT

　　只要有可能,在程序中盡量多使用COMMIT,這樣程序的性能得到提高,需求也會因?yàn)镃OMMIT所釋放的資源而減少:

　　COMMIT所釋放的資源:

　　a.回滾段上用于恢復(fù)數(shù)據(jù)的信息.

　　b.被程序語句獲得的鎖

　　c.redologbuffer中的空間

　　d.ORACLE為管理上述3種資源中的內(nèi)部花費(fèi)(在使用COMMIT時(shí)必須要注意到事務(wù)的完整性,現(xiàn)實(shí)中效率和事務(wù)完整性往往是魚和熊掌不可得兼)

　　9.減少對表的查詢

　　在含有子查詢的SQL語句中,要特別注意減少對表的查詢.

　　例如：

　　低效：

　　SELECTTAB_NAMEFROMTABLESWHERETAB_NAME=(SELECTTAB_NAMEFROMTAB_COLUMNSWHEREVERSION=604)AND　DB_VER=(SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION=604

　　高效：

　　SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SELECTTAB_NAME,DB_VER)FROMTAB_COLUMNSWHEREVERSION=604)

　　Update多個(gè)Column例子:

　　低效：

　　UPDATEEMPSETEMP_CAT=(SELECTMAX(CATEGORY)FROMEMP_CATEGORIES),SAL_RANGE=(SELECTMAX(SAL_RANGE)FROMEMP_CATEGORIES)WHEREEMP_DEPT=0020;

　　高效:

　　UPDATEEMPSET(EMP_CAT,SAL_RANGE)=(SELECTMAX(CATEGORY),MAX(SAL_RANGE)FROMEMP_CATEGORIES)WHEREEMP_DEPT=0020;

　　10.用EXISTS替代IN,用NOTEXISTS替代NOTIN

　　在許多基于基礎(chǔ)表的查詢中,為了滿足一個(gè)條件,往往需要對另一個(gè)表進(jìn)行聯(lián)接.在這種情況下,使用EXISTS(或NOTEXISTS)通常將提高查詢的效率.

　　低效:

　　SELECT*FROMEMP(基礎(chǔ)表)WHEREEMPNO>0ANDDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC=‘MELB’)

　　高效:

　　SELECT*FROMEMP(基礎(chǔ)表)WHEREEMPNO>0ANDEXISTS(SELECT‘X’FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=‘MELB’)

　　(相對來說,用NOTEXISTS替換NOTIN將更顯著地提高效率)

　　在子查詢中,NOTIN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并.無論在哪種情況下,NOTIN都是最低效的(因?yàn)樗鼘ψ硬樵冎械谋韴?zhí)行了一個(gè)全表遍歷).為了避免使用NOTIN,我們可以把它改寫成外連接(OuterJoins)或NOTEXISTS.

　　例如:

　　SELECT…FROMEMPWHEREDEPT_NONOTIN(SELECTDEPT_NOFROMDEPTWHEREDEPT_CAT='A');

　　為了提高效率.改寫為:

　　(方法一:高效)

　　SELECT….FROMEMPA,DEPTBWHEREA.DEPT_NO=B.DEPT(+)ANDB.DEPT_NOISNULLANDB.DEPT_CAT(+)='A'

　　(方法二:最高效)

　　SELECT….FROMEMPEWHERENOTEXISTS(SELECT'X'FROMDEPTDWHERED.DEPT_NO=E.DEPT_NOANDDEPT_CAT='A');

　　當(dāng)然,最高效率的方法是有表關(guān)聯(lián).直接兩表關(guān)系對聯(lián)的速度是最快的!

　　11.識別'低效執(zhí)行'的SQL語句

　　用下列SQL工具找出低效SQL:

　　SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,SQL_TEXTFROMV$SQLAREAWHEREEXECUTIONS>0ANDBUFFER_GETS>0AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0.8ORDERBY4DESC; 　　(雖然目前各種關(guān)于SQL優(yōu)化的圖形化工具層出不窮,但是寫出自己的SQL工具來解決問題始終是一個(gè)最好的方法) 　

看完上述內(nèi)容，你們對SQLServer中怎樣優(yōu)化多表查詢有進(jìn)一步的了解嗎？如果還想了解更多知識或者相關(guān)內(nèi)容，請關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。