共計 4014 個字符，預計需要花費 11 分鐘才能閱讀完成。
今天就跟大家聊聊有關 SQLServer 中怎樣優化多表查詢，可能很多人都不太了解，為了讓大家更加了解，丸趣 TV 小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。
SQLServer 多表查詢優化方案有哪些
1. 執行路徑
ORACLE 的這個功能大大地提高了 SQL 的執行性能并節省了內存的使用: 我們發現, 單表數據的統計比多表統計的速度完全是兩個概念. 單表統計可能只要 0.02 秒, 但是 2 張表聯合統計就
可能要幾十秒了. 這是因為 ORACLE 只對簡單的表提供高速緩沖(cachebuffering), 這個功能并不適用于多表連接查詢.. 數據庫管理員必須在 init.ora 中為這個區域設置合適的參數, 當這個內存區域越大, 就可以保留更多的語句, 當然被共享的可能性也就越大了.
2. 選擇最有效率的表名順序(記錄少的放在后面)
ORACLE 的解析器按照從右到左的順序處理 FROM 子句中的表名, 因此 FROM 子句中寫在最后的表 (基礎表 drivingtable) 將被最先處理. 在 FROM 子句中包含多個表的情況下, 你必須選擇記錄條數最少的表作為基礎表. 當 ORACLE 處理多個表時, 會運用排序及合并的方式連接它們. 首先, 掃描第一個表 (FROM 子句中最后的那個表) 并對記錄進行派序, 然后掃描第二個表(FROM 子句中最后第二個表), 最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并.
例如:
表 TAB116,384 條記錄
表 TAB21 條記錄
選擇 TAB2 作為基礎表(最好的方法)
selectcount(*)fromtab1,tab2 執行時間 0.96 秒
選擇 TAB2 作為基礎表(不佳的方法)
selectcount(*)fromtab2,tab1 執行時間 26.09 秒
假如有 3 個以上的表連接查詢, 那就需要選擇交叉表 (intersectiontable) 作為基礎表, 交叉表是指那個被其他表所引用的表.
例如: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 子句中的連接順序(條件細的放在后面)
ORACLE 采用自下而上的順序解析 WHERE 子句, 根據這個原理, 表之間的連接必須寫在其他 WHERE 條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在 WHERE 子句的末尾.
例如:
(低效, 執行時間 156.3 秒)
SELECT…FROMEMPEWHERESAL 50000ANDJOB=‘MANAGER’AND25 (SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);(高效, 執行時間 10.6 秒)SELECT…FROMEMPEWHERE25 (selectcount(*)fromempwheremgr=e.empno)andsal 50000ANDJOB=‘MANAGER’;
4.SELECT 子句中避免使用 *
當你想在 SELECT 子句中列出所有的 COLUMN 時, 使用動態 SQL 列引用 * 是一個方便的方法. 不幸的是, 這是一個非常低效的方法. 實際上,ORACLE 在解析的過程中, 會將 * 依次轉換成所有的列名, 這個工作是通過查詢數據字典完成的, 這意味著將耗費更多的時間.
SQLServer 多表查詢優化方案有哪些
5. 減少訪問數據庫的次數
當執行每條 SQL 語句時,ORACLE 在內部執行了許多工作: 解析 SQL 語句, 估算索引的利用率, 綁定變量, 讀數據塊等等. 由此可見, 減少訪問數據庫的次數, 就能實際上減少 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. 刪除重復記錄
最高效的刪除重復記錄方法(因為使用了 ROWID)
DELETEFROMEMPEWHEREE.ROWID (SELECTMIN(X.ROWID)FROMEMPXWHEREX.EMP_NO=E.EMP_NO);
7. 用 TRUNCATE 替代 DELETE
當刪除表中的記錄時, 在通常情況下, 回滾段 (rollbacksegments) 用來存放可以被恢復的信息. 假如你沒有 COMMIT 事務,ORACLE 會將數據恢復到刪除之前的狀態(準確地說是恢復到執行刪除命令之前的狀況)，而當運用 TRUNCATE 時, 回滾段不再存放任何可被恢復的信息. 當命令運行后, 數據不能被恢復. 因此很少的資源被調用, 執行時間也會很短.
8. 盡量多使用 COMMIT
只要有可能, 在程序中盡量多使用 COMMIT, 這樣程序的性能得到提高, 需求也會因為 COMMIT 所釋放的資源而減少:
COMMIT 所釋放的資源:
a. 回滾段上用于恢復數據的信息.
b. 被程序語句獲得的鎖
c.redologbuffer 中的空間
d.ORACLE 為管理上述 3 種資源中的內部花費(在使用 COMMIT 時必須要注意到事務的完整性, 現實中效率和事務完整性往往是魚和熊掌不可得兼)
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 多個 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
在許多基于基礎表的查詢中, 為了滿足一個條件, 往往需要對另一個表進行聯接. 在這種情況下, 使用 EXISTS(或 NOTEXISTS)通常將提高查詢的效率.
低效:
SELECT*FROMEMP(基礎表)WHEREEMPNO 0ANDDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC=‘MELB’)
高效:
SELECT*FROMEMP(基礎表)WHEREEMPNO 0ANDEXISTS(SELECT‘X’FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=‘MELB’)
(相對來說, 用 NOTEXISTS 替換 NOTIN 將更顯著地提高效率)
在子查詢中,NOTIN 子句將執行一個內部的排序和合并. 無論在哪種情況下,NOTIN 都是最低效的 (因為它對子查詢中的表執行了一個全表遍歷). 為了避免使用 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
當然, 最高效率的方法是有表關聯. 直接兩表關系對聯的速度是最快的!
11. 識別 低效執行 的 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;
(雖然目前各種關于 SQL 優化的圖形化工具層出不窮, 但是寫出自己的 SQL 工具來解決問題始終是一個最好的方法)
看完上述內容，你們對 SQLServer 中怎樣優化多表查詢有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注丸趣 TV 行業資訊頻道，感謝大家的支持。
