乾貨分享丨3種SQL語句最佳化方法，軟體測試必備！

關於SQL語句的最佳化，本質上就是儘量降低SQL語句的執行時間，對於如何降低SQL語句的執行時間，可以從以下幾個方面入手。

一、降低SQL語句執行時的資源消耗

這是我們在資料庫效能調優中常用的方法，該方法以分析SQL語句的執行計劃為切入點，核心思路是找到執行計劃中開銷較高的操作，透過改寫SQL語句或改變表訪問方式調整執行計劃，從而達到降低SQL語句執行消耗，縮短執行時間的目的。

對於改變表訪問方式，常見的手段是使用索引替代開銷較高的全表掃描，但這種方式不是最好的，是有一定的使用前提的，有時候，濫用索引反而會帶來較高的效能開銷。

以下列舉一些適合採用訪問索引的方式替代原有操作的案例。

1.使用索引替代全表掃描

如果查詢結果集只佔表中的一小部分資料，這時，可以採用索引訪問的方式替代全表掃描，即使不能達到索引覆蓋而產生回表操作，其開銷也小於採用全表掃描操作。

例如，使用Oracle資料庫儲存，一個使用者資訊表user(userid,username,sex,tel,code)，code列表示使用者的社保號資訊，系統執行之初允許社保號資訊為非必填項，這就導致少部分code列的值為null，為了提高對code列檢索的效率，我們為code列建立了普通B樹索引inx_code(code)，要查詢所有未提供社保號的那部分使用者資訊，之後系統為這部分使用者傳送資訊，提示補全社保號資訊，語句如下：

select*from user where code is null;

語句執行後，用時20秒左右，user表中存在50萬條左右的使用者記錄，返回的未提供社保號的使用者記錄有5000條左右。

透過分析執行計劃，在查詢執行時採用了全表掃描方式，這是造成查詢執行時間較長的主要原因。

對於B樹單列索引，null值將使得索引失效，所以最佳化器採用了全表掃描方式。

該查詢實際返回記錄5000條左右，表中有記錄50萬條左右，實際返回的記錄只佔表總記錄的1%，這時，可以採取使用索引掃描替代全表掃描。

如何使得包含null值的列在檢索時可以使用索引呢，這就需要將基於B樹的單列索引改為複合索引，將原有索引修改為inx_code(code,0)，再次執行查詢，對user表的訪問方式由全表掃描改為索引範圍掃描，執行時間降至1秒之下。

2.利用索引的有序性消除排序操作

在對資料庫的訪問中，排序是一種開銷較搞的操作，資料庫為了完成排序操作，需要掃描表中的所有記錄，之後採取相應的演算法對記錄進行排序。如果表中的記錄隨系統的執行累積增加，那麼排序操作的執行開銷會逐漸變大，執行時間會越來越長。

可以到我的個人號：atstudy-js，可以免費領取一份10G軟體測試工程師面試寶典文件資料。同時我邀請你進入我們的軟體測試學習交流平臺，大家可以一起探討交流軟體測試，共同學習軟體測試技術、面試等軟體測試方方面面，瞭解測試行業的最新趨勢，助你快速進階Python自動化測試/測試開發，穩住當前職位同時走向高薪之路。

索引是有序的，因為相應的索引鍵值已經事先按一定規則完成排序，如果SQL語句中需要按表中的某列進行排序，此時，可以為該列建立索引，從而達到透過索引掃描代替完成排序需要的全表掃描，達到降低訪問開銷，縮短執行時間的目的。

例如，在MySQL8.0資料庫中，如果需要對多個列進行排序，且排序順序有升有降，即：order by col1,col2 desc，此時，可以為兩個排序列建立一個複合降序索引idx_col1_col2(c1,c2 desc)。

3.利用索引改變表關聯方式

在執行表關聯查詢操作時，資料庫的最佳化器可能選擇了不合理的表關聯方式，使得表關聯查詢開銷較高，耗時較長。

例如，PostgreSQL資料庫中有user、ure、org三個表，分別儲存系統使用者資訊、已完成認證的使用者資訊和相應的組織資訊。有如下的查詢語句：

select*

from user join ure on user.id=ure.id join on org on ure.oid=org.oid

where org.pcode=’12012’

order by ure.update;

該查詢的執行計劃如下圖所示：

查詢執行用時32毫秒，開銷為2206。

透過執行計劃我們發現，user、ure和org三表關聯均採用了Hash Join的關聯方式，這種關聯方式是最優的嗎？

分析表連線方式，org表是第一個關聯的驅動表，該表過濾後的結果集只有24條記錄，結果集很小，所以，可以將org表與user表的連線方式調整為巢狀迴圈連線方式。此外，對org表的訪問採用了全表掃描，可將其調整為索引掃描。

為查詢org表的where條件列pcode列建立索引，同時為作為巢狀迴圈連線的被驅動表user表的關聯條件列oid列建立索引，本次調優後的執行計劃如下圖所示。

改變org表與user表的連線方式後，執行時間降低為4.85ms，執行開銷降低到273。

進一步分析調優後的執行計劃，org表與user表連線後的結果集只有47條記錄，該結果集與ure表的連線方式仍可以調整為巢狀迴圈連線，以該結果集作為驅動表，ure表作為被驅動表。為達到該目的，為ure表的連線條件列id設定索引即可。

最終的執行計劃如下圖所示。

由此可見，將表連線方式全部由Hash連線調整為巢狀迴圈連線後，執行時間最終降至1ms，執行開銷降至30。

二、並行執行SQL語句

這種方式是透過增加額外的資源消耗來換取SQL執行時間的縮短，其意義類似於程式碼最佳化中的“以空間換時間”的策略。

增加的額外資源主要是指資料庫伺服器的處理器（CPU）、記憶體、I/O等硬體資源。

例如：在Oracle資料庫中，對於一個查詢操作，如果其所作的工作可以分割成多個互不相關的部分，則該查詢可以由多個程序併發執行。可以並行執行的查詢操作主要有全表掃描、快速索引全掃描、分割槽索引範圍掃描、以及需要執行全表掃描完成的表連線。

三、避免不必要的資源爭用導致SQL執行效率下降

有些SQL語句，其執行時間不定，時快時慢，這些語句的執行計劃自身未存在問題。導致語句執行效率差的原因是語句執行時，資料庫伺服器在執行其他消耗資源的操作，出現資源爭用的情況。

例如：某系統每日凌晨定時執行統計資訊收集工作，如果這時對系統執行效能測試，涉及對該資料的查詢操作將受到影響，導致效能測試結果不準確。因此，效能測試需要避開資料庫執行統計資訊收集的時間。

以上對SQL語句的最佳化方法做了簡要的介紹，下面對SQL語句的最佳化步驟和方法做一個說明和總結。

1.找到執行時間較長、消耗資源較多的SQL語句。例如：MySQL資料庫可從慢查詢日誌中獲取，Oracle資料庫可檢視AWR報告。

2.分析以上獲取的效能較差的SQL語句的執行計劃，找到執行計劃中開銷較高的部分，評估執行計劃是否合理，是否需要調整。

3.對於執行計劃中開銷較高的部分，採取相應的措施降低執行開銷，縮短執行時間，例如如下方式：

（1）如果是統計資訊不準確導致生成了錯誤的執行計劃，需要首先重新收集統計資訊；

（2）如果是SQL語句編寫存在問題，可以在不改變業務邏輯的前提下對SQL語句進行適當的改寫；

（3）對於不必要的全表掃描或排序，可以透過建立合適索引消除全表掃描和排序；

（4）如果因為某些原因導致SQL語句的執行計劃不穩定，在條件允許的情況下，使用提示器（Hint）固定SQL語句的執行計劃；

（5）如果因為表或索引本身設定不合理，導致執行開銷較高，用時較長，則應該對錶或索引重新設計，例如：表中記錄過大，超過億級，此時應考慮分表分庫；

（6）對於具備並行執行的部分，考慮採用並行執行的方式；

（7）如果是業務設計不合理導致SQL語句執行效率低下，應考慮修改業務邏輯。