sql關聯查詢優化

❶ 【基於ORACLE資料庫的SQL語句優化分析】 資料庫查詢語句的優化

【摘要】隨著資料庫應用范圍及規模的不斷擴大，資料庫的性能問題逐漸顯現，優化資料庫有助於維持系統的穩定性以及運行的高效性。本文主要依據筆者在實際工作中的精坦敏拍英，對SQL語句優化的目的、SQL語句優化技術及原則進行全面分析和闡述。
【關鍵詞】ORACLE資料庫；SQL語句；優化
1前言
隨著現代化信息技術的迅猛發展，互聯網應用的日益普及，資料庫技術的影響力越來越大。作為信息系統管理的核心，資料庫的主要操作就是查詢，資料庫的應用效率在很大程度上是由查詢速度決定的，特別是對於規模較大的資料庫而言，查詢速度十分關鍵。查詢速度在SQL語句中佔有很大比重，所以，通過對查詢語句進行優化有助於促進應用系統性能及效率的進一步提升。
2SQL語句優化分析
2.1SQL語句優化的目的
對於一個資料庫而言，在確保設計無誤的前提下，要想避免出現性能問題必須確保其擁有合理的SQL語句拿喚結構。最簡單的資料庫尋找數據路徑是對SQL語句進行調整，ORACLE資料庫性能提升的主要途徑就是對SQL語句進行適當的調整。從本質上講，SQL語句優化就是確保所使用的語句可以被優化器識別，對索引進行有效利用以便控製表掃描的I/O次數，有效防止出現表搜索。用高性能的SQL語句替代低性能的SQL語句，確定最佳的數據查找路徑，盡可能使CPU時間與I/O時間保持平衡是進行優化的主要目的。在對SQL語句進行優化的過程中，以系統需求為依據確定最有可能實現性能提升的語句並進行優化。
2.2SQL語句優化技術及原則
當數據量積累到一定程度之後，對於資料庫全表SQL語句進行一次掃描，若查詢策略較好，一般只用幾秒鍾，但如果SQL語句性能較低，就需要用幾分鍾甚至更多時間。從這點不難看出，SQL語句性能對於查詢速度具有極大的影響，所以，對於應用系統而言，不僅能滿足功能的實現，還要保證SQL語句的質量。
（1）採取適宜的索引。為達到優化查詢的目的，一項重要工作就是確定相適應的索引，並嚴格依照原則加以使用，與此同時，為有效控制I/O競爭，不可以在同一個磁碟中同時建立索引和用戶表空間。
語句1：SELECT CUS_NO, CUS_NAME FROM CUSTOMER WHERE CUS_NO NOT IN
(SELECT CUS_NO FROM SERVICE);
語句2： SELECT CUS_NO, CUS_NAME FROM CUSTOMER WHERE NOT EXISTS
(SELECT * FROM SERVICE WHERE SERVICE.CUS_NO=CUSTOMER.CUS_NO);
上述兩個語句可以達到一致的查詢結果，對二者進行對比，當執行語句1時，由於ORACLE未利用CUSTOMER 表上CUS_NO索引，所以就會掃描整表，在執行語句2的過讓羨程中，ORACLE所掃描的只是CUSTOMER 表子查詢中的聯合查詢，並且使用了CUS_NO索引，因此，在執行效率方面明顯優於前者。
（2）避免在SELECT子句中出現「*」。ORACLE在進行解析時，需要按照一定順序對「*」進行轉換，該項轉換工作的進行需要對資料庫的數據字典進行查詢，勢必需要花費較多的時間，這樣就會導致較低的效率，所以，要避免在SELECT子句中出現「*」。
（3）如果必要可以利用COMMIT提交事務。ORACLE能夠自動提交DDL語句，而諸如DML等類型的語句的提交則是通過手動方式或者回滾事務實現的。在編寫應用程序的過程中，在操作諸如insert、delete以及update 等較為復雜的語境的時候，利用COMMIT提交事務可以講會話中持有的鎖加以釋放，將存在於緩存中的未經修改的數據塊進行清除，進而將系統資源予以釋放，促進系統性能的進一步提升，因此，如果有必要，可以利用COMMIT對相關事務進行提交。
（4）聯合查詢連接順序的確定。如果查詢操作涉及到多個表，基礎表應當是交叉表，所謂交叉表具體是指被其他表引用的表。連接執行效果在很大程度上受到FROM語句中表的順序的影響，對於FROM中所包含的表，ORACLE解析器進行處理的順序是由右至左，SQL語句中所選擇的基礎表會因優化器的不同而有所區別，在使用CBO的情況下，優化器會對SQL語句中各個表的物理大小以及索引狀態進行檢查，在此基礎上確定一個花費最小的執行路徑；在使用RBO的情況下，如果全部的連接條件均有索引與之相對應，那麼，FROM子句中位置最後面的表就是基礎表。
（5）IN用EXISTS取代。在對數個基礎表查詢過程中，一般需要進行表的連接。因為利用IN的子查詢過程中，ORACLE的掃描對象是全表，因此，出於提高查詢效率目的的考慮，應當將IN用EXISTS取代。
（6）在索引列中不使用計算。當通過對函數進行引用在WHERE子句中進行計算的時候，假如索引列只是函數的一部分，優化器就會針對全表進行掃描，而不會使用索引，所以，在索引列中不能使用函數。
3結語
綜上所述，隨著現代化信息技術的迅猛發展，互聯網應用的日益普及，資料庫技術的影響力越來越大。在信息量迅速激增的形勢下，資料庫優化調整成為當前所面臨的一大關鍵性問題，特別是對規模較大的資料庫而言，及時進行優化的意義更加倍重大。對於資料庫的運行性能而言，最主要的影響因素主要體現在以下幾點：資料庫系統架構的設計是否合理，資源配置是否科學以及SQL語句編寫效率等。筆者從事的是電信企業的運營分析工作，每天都要從資料庫取各種數據，可以說是離不開資料庫，所以在實踐中，我覺得嚴格遵守SQL語句優化原則及方法，並在實踐中及時總結經驗教訓，可以實現對系統響應時間的有效控制，促進運行效率的提升。
參考文獻
[1] 許開宇,胡文驊. 如何提高ORACLE資料庫應用程序的性能[J]. 計算機應用與軟體. 2002(10)
[2] 鄭耀,吳建嵐. 基於Oracle資料庫的語句優化策略[J]. 信息與電腦(理論版). 2011(07)
[3] 高攀,施蔚然. 基於Oracle資料庫的SQL語句優化[J]. 電腦編程技巧與維護. 2010(22)
[4] 鍾小權,葉猛. Oracle資料庫的SQL語句優化[J]. 計算機與現代化. 2011(03)
作者簡介：
王勇軍，男，（1981.1-），吉林通化人，就職於中國聯合網路通信有限公司長春市分公司，通信工程師，本科，研究方向：SQL使用
（作者單位：中國聯合網路通信有限公司長春市分公司）

❷ SQL執行與優化

SQL優化

執行計劃,表關聯查詢順序,優化策略與思路

下面再向前走一些，容我根據自己的認識說一下查詢執行的流程是怎樣的：

1.連接

1.1客戶端發起一條Query請求，監聽客戶端的『連接管理模塊』接收請求

1.2將請求轉發到『連接進/線程模塊』

1.3調用『用戶模塊』來進行授權檢查

1.4通過檢查後，『連接進/線程模塊』從『線程連接池』中取出空閑的被緩存的連接線程和客戶端請求對接，如果失敗則創建一個新的連接請求

2.處理

2.1先查詢緩存，檢查Query語句是否完全匹配，接著再檢查是否具有許可權，都成功則直接取數據返回

2.2上一步有失敗則轉交給『命令解析器』，經過詞法分析，語法分析後生成解析樹

2.3接下來是預處理階段，處理解析器無法解決的語義，檢查許可權等，生成新的解析樹

2.4再轉交給對應的模塊處理

2.5如果是SELECT查詢還會經由『查詢優化器』做大量的優化，生成執行計劃

2.6模塊收到請求後，通過『訪問控制模塊』檢查所連接的用戶是否有訪問目標表和目標欄位的許可權

2.7有則調用『表管理模塊』，先是查看table cache中是否存在，有則直接對應的表和獲取鎖，否則重新打開表文件

2.8根據表的meta數據，獲取表的存儲引擎類型等信息，通過介面調用對應的存儲引擎處理

2.9上述過程中產生數據變化的時候，若打開日誌功能，則會記錄到相應二進制日誌文件中

3.結果

3.1Query請求完成後，將結果集返回給『連接進/線程模塊』

3.2返回的也可以是相應的狀態標識，如成功或失敗等

3.3『連接進/線程模塊』進行後續的清理工作，並繼續等待請求或斷開與客戶端的連接

接下來再走一步，讓我們看看一條SQL語句的前世今生。

首先看一下示例語句

示例語句

執行順序

SQL解析

1. FROM

當涉及多個表的時候，左邊表的輸出會作為右邊表的輸入，之後會生成一個虛擬表VT1。

(1-J1)笛卡爾積

計算兩個相關聯表的笛卡爾積(CROSS JOIN) ，生成虛擬表VT1-J1。

兩次全表掃描

哈希索引,查找復雜度都是 O(1) 。

2. WHERE

對VT1過程中生成的臨時表進行過濾，滿足WHERE子句的列被插入到VT2表中。

注意：

此時因為分組，不能使用聚合運算；也不能使用SELECT中創建的別名；

與ON的區別：

如果有外部列，ON針對過濾的是關聯表，主表（保留表）會返回所有的列；

如果沒有添加外部列，兩者的效果是一樣的；

應用：

對主表的過濾應該放在WHERE；

對於關聯表，先條件查詢後連接則用ON，先連接後條件查詢則用WHERE；

hash join 哈希連接 驅動表和被驅動表都只會訪問0次或1次

應用場景:一個大表一個小表/表上沒有索引/返回結果集比較大

3. GROUP BY

這個子句會把VT2中生成的表按照GROUP BY中的列進行分組。生成VT3表。

注意：

其後處理過程的語句，如SELECT,HAVING，所用到的列必須包含在GROUP BY中，對於沒有出現的，得用聚合函數；

原因：

GROUP BY改變了對表的引用，將其轉換為新的引用方式，能夠對其進行下一級邏輯操作的列會減少；

原作者的理解是：

根據分組欄位，將具有相同分組欄位的記錄歸並成一條記錄，因為每一個分組只能返回一條記錄，除非是被過濾掉了，而不在分組欄位裡面的欄位可能會有多個值，多個值是無法放進一條記錄的，所以必須通過聚合函數將這些具有多值的列轉換成單值；

GROUP BY 重新聚合查詢

4. HAVING

這個子句對VT3表中的不同的組進行過濾，只作用於分組後的數據，滿足HAVING條件的子句被加入到VT4表中。

7.LIMIT

LIMIT子句從上一步得到的VT6虛擬表中選出從指定位置開始的指定行數據。

注意：

offset和rows的正負帶來的影響；

當偏移量很大時效率是很低的，可以這么做：

採用子查詢的方式優化，在子查詢里先從索引獲取到最大id，然後倒序排，再取N行結果集

採用INNER JOIN優化，JOIN子句里也優先從索引獲取ID列表，然後直接關聯查詢獲得最終結果

當前未用到索引,

三次full scan , table1 AS a / table2 AS b / GROUP BY

盡量少做重復的工作

控制同一語句的多次執/減少多次的數據轉換/

杜絕不必要的子查詢和連接表，子查詢在執行計劃一般解釋成外連接，多餘的連接表帶來額外的開銷

關於臨時表和表變數的選擇

臨時表產生使用SELECT INTO和CREATE TABLE + INSERT INTO的選擇，一般情況下，SELECT INTO會比CREATE TABLE + INSERT INTO的方法快很多，但是SELECT INTO會鎖定TEMPDB的系統表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用戶並發環境下，容易阻塞其他進程，所以建議，在並發系統中，盡量使用CREATE TABLE + INSERT INTO，而大數據量的單個語句使用中，使用SELECT INTO。

子查詢的用法

相關子查詢可以用IN、NOT IN、EXISTS、NOT EXISTS引入

NOT IN、NOT EXISTS的相關子查詢可以改用LEFT JOIN代替寫法

如果保證子查詢沒有重復 ，IN、EXISTS的相關子查詢可以用INNER JOIN 代替

IN``的相關子查詢用EXISTS代替

不要用 COUNT (*)的子查詢判斷是否存在記錄，最好用 LEFT` `JOIN 或者EXISTS，比如有人寫這樣的語句：

建立索引後，並不是每個查詢都會使用索引，在使用索引的情況下，索引的使用效率也會有很大的差別。只要我們在查詢語句中沒有強制指定索引，

不要對索引欄位進行運算，而要想辦法做變換

不要對索引欄位進行格式轉換

不要對索引欄位使用函數

不要對索引欄位進行多欄位連接

join關聯查詢的計算是很復雜的，特別是數據量比較大的情況下，實際情況還是拆解較快的

Join拆解的核心就是利用In關鍵字

要麼用空間換時間，要麼用時間換空間

多表連接的連接條件對索引的選擇有著重要的意義，所以我們在寫連接條件條件的時候需要特別注意。

A、多表連接的時候，連接條件必須寫全，寧可重復，不要缺漏。

B、連接條件盡量使用聚集索引

C、注意ON、WHERE和HAVING部分條件的區別

ON是最先執行， WHERE次之，HAVING最後，因為ON是先把不符合條件的記錄過濾後才進行統計，它就可以減少中間運算要處理的數據，按理說應該速度是最快的，WHERE也應該比 HAVING快點的，因為它過濾數據後才進行SUM，在兩個表聯接時才用ON的，所以在一個表的時候，就剩下WHERE跟HAVING比較了

考慮聯接優先順序：

(1)INNER JOIN

(2)LEFT JOIN (註：RIGHT JOIN 用 LEFT JOIN 替代)

(3)CROSS JOIN

索引並不適用於所有情況：a.少量數據；b.頻繁進行改動的欄位，不適合做索引；c.很少使用的欄位，不需要加索引

索引會提高數據查詢效率，但是會降低「增、刪、改」的效率。當不使用索引的時候，我們進行數據的增刪改，只需要操作源表即可，但是當我們添加索引後，不僅需要修改源表，也需要再次修改索引，很麻煩。

先執行順序, 是否走索引, 有無類型轉換

18000 字的SQL優化大全

步步深入：MySQL架構總覽->查詢執行流程->SQL解析順序

MySQL索引總結（4）——btree與hash區別