數據庫系統中ＳＱＬ語句的優(yōu)化技術

數據庫系統中ＳＱＬ語句的優(yōu)化技術

摘要:SQL語句的優(yōu)化是將性能低下的SQL語句轉換成目的相同的性能優(yōu)異的SQL語句。人工智能自動SQL優(yōu)化就是使用人工智能技術，自動對SQL語句進行重寫，從而找到性能最好的等效SQL語句。

關鍵詞:人工智能;自動優(yōu)化;SQL語句優(yōu)化

一個數據庫系統的生命周期可以分成：設計、開發(fā)和成品三個階段。在設計階段進行數據庫性能優(yōu)化的成本最低，收益最大。在成品階段進行數據庫性能優(yōu)化的成本最高，收益最小。數據庫的優(yōu)化通?？梢酝ㄟ^對網絡、硬件、操作系統、數據庫參數和應用程序的優(yōu)化來進行。最常見的優(yōu)化手段就是對硬件的升級。根據統計，對網絡、硬件、操作系統、數據庫參數進行優(yōu)化所獲得的性能提升，全部加起來只占數據庫系統性能提升的40%左右，其余的60%系統性能提升來自對應用程序的優(yōu)化。許多優(yōu)化專家認為，對應用程序的優(yōu)化可以得到80%的系統性能的提升。應用程序的優(yōu)化通?？煞譃閮蓚€方面：源代碼和SQL語句。由于涉及到對程序邏輯的改變，源代碼的優(yōu)化在時間成本和風險上代價很高，而對數據庫系統性能的提升收效有限。

1.1為什么要優(yōu)化SQL語句

第一、SQL語句是對數據庫進行操作的惟一途徑，對數據庫系統的性能起著決定性的作用。

第二、SQL語句消耗了70%至90%的數據庫資源。

第三、SQL語句獨立于程序設計邏輯，對SQL語句進行優(yōu)化不會影響程序邏輯。

第四、SQL語句有不同的寫法，在性能上的差異非常大。

第五、SQL語句易學，但難精通。

優(yōu)化SQL語句的傳統方法是通過手工重寫來對SQL語句進行優(yōu)化。DBA或資深程序員通過對SQL語句執(zhí)行計劃的分析，依靠經驗，嘗試重寫SQL語句，然后對結果和性能進行比較，以試圖找到性能較佳的SQL語句。這種傳統上的作法無法找出SQL語句的所有可能寫法，且依賴于人的經驗，非常耗費時間。

1.2SQL優(yōu)化技術的發(fā)展歷程

第一代SQL優(yōu)化工具是執(zhí)行計劃分析工具。這類工具針對輸入的SQL語句，從數據庫提取執(zhí)行計劃，并解釋執(zhí)行計劃中關鍵字的含義。

第二代SQL優(yōu)化工具只能提供增加索引的建議，它通過對輸入的SQL語句的執(zhí)行計劃的分析，來產生是否要增加索引的建議。

第三代SQL優(yōu)化工具不僅分析輸入SQL語句的執(zhí)行計劃，還對輸入的SQL語句本身進行語法分析，經過分析產生寫法上的改進建議。

1.3人工智能自動SQL優(yōu)化人工智能自動SQL優(yōu)化出現在90年代末。目前在商用數據庫領域，LECCOTechnologyLimited(靈高科研有限公司)擁有該技術，并提供使用該技術的自動優(yōu)化產品LECCOSQLExpert，它支持Oracle、Sybase、MSSQLServer和IBMDB2數據庫平臺。該產品針對數據庫應用的開發(fā)和維護階段提供的模塊有：SQL語法優(yōu)化器、PL/SQL集成化開發(fā)調試環(huán)境(IDE)、掃描器、數據庫監(jiān)視器等。其核心模塊SQL語法優(yōu)化器的工作原理為：①輸入一條源SQL語句；②“人工智能反饋式搜索引擎”對輸入的SQL語句，結合檢測到的數據庫結構和索引進行重寫，產生N條等效的SQL語句輸出；③產生的N條等效SQL語句再送入“人工智能反饋式搜索引擎”進行重寫，直至無法產生新的輸出或搜索限額滿；④對輸出的SQL語句進行過濾，選出具有不同執(zhí)行計劃的SQL語句；⑤對得到的SQL語句進行批量測試，找出性能最好的SQL語句。

2LECCOSQLExpert自動優(yōu)化實例

2.1假設我們從源代碼中抽取出這條SQL語句(也可以通過內帶的掃描器或監(jiān)視器獲得SQL語句)：

SELECTCOUNT(*)論文下載

FROMEMPLOYEE

swheresEXISTS(SELECT'X'

FROMDEPARTMENT

swheresEMP_DEPT=DPT_ID

ANDDPT_NAMELIKE'AC%')

ANDEMP_IDIN(SELECTSAL_EMP_ID

FROMEMP_SAL_HISTB

swheresSAL_SALARY＞70000)

按下“優(yōu)化”按鈕后，經過10幾秒，SQLExpert就完成了優(yōu)化的過程，并在這10幾秒的時間里重寫產生了2267條等價的SQL語句，其中136條SQL語句有不同的執(zhí)行計劃。2.2“邊做邊學式訓練”提升SQL開發(fā)水平

LECCOSQLExpert不僅能夠找到最佳的SQL語句，它所提供的“邊做邊學式訓練”還能夠教開發(fā)人員和數據庫管理員如何寫出性能最好的SQL語句。LECCOSQLExpert的“SQL比較器”可以標明源SQL和待選SQL間的不同之處。

以上面優(yōu)化的結果為例，為了查看源SQL語句和SQL124在寫法上有什么不同，我們可以按下“比較器”按鈕，對SQL124和源SQL語句進行比較?！癝QL比較器”將SQL124相對于源SQL語句的不同之處以藍顏色表示了出來。如果選擇“雙向比較”復選框，“SQL比較器”可以將兩條SQL語句的不同之處以藍色表示。當然，我們也可以從源語句和重寫后的SQL語句中任選兩條進行比較。

從比較的結果可以看到，重寫后的SQL124把第一個Exists改寫成了In；在字段DPT_ID上進行了合并空字符串的操作，以誘導數據庫先執(zhí)行子查詢中的

(SELECTDPT_ID||''

FROMDEPARTMENT

WHEREDPT_NAMELIKE'AC%')

在子查詢完成后，再與EMPLOYEE表進行嵌套循環(huán)連接(NestedLoopJoin)。

如果覺得對寫法的改變難以理解，還可以點中“執(zhí)行計劃”復選框，通過比較兩條SQL語句的執(zhí)行計劃的不同，來了解其中的差異。在查看執(zhí)行計劃過程中，如果有什么不明白的地方，可以點中“SQL信息按鈕”，再點擊執(zhí)行計劃看不明白的地方，LECCOSQLExpert的上下文敏感幫助系統將提供執(zhí)行計劃該處的解釋。

在“SQL比較器”中，選中“統計信息”復選框后，可得到詳細的兩條SQL語句運行時的統計信息比較，這對于學習不同的SQL寫法對數據庫資源的消耗很有幫助。

2.3LECCOSQLExpert優(yōu)化模塊的特點

LECCOSQLExpert優(yōu)化模塊的特點主要表現為：自動優(yōu)化SQL語句；以獨家的人工智能知識庫“反饋式搜索引擎”來重寫性能優(yōu)異的SQL語句；找出所有等效的SQL語句及可能的執(zhí)行計劃；保證產生相同的結果；先進的SQL語法分析器能處理最復雜的SQL語句；可以重寫SELECT、SELECTINTO、UPDATE、INSERT和DELETE語句；通過測試運行，為應用程序和數據庫自動找到性能最好的SQL語句；提供微秒級的計時，能夠優(yōu)化Web應用程序和有大量用戶的在線事務處理中運行時間很短的SQL語句；為開發(fā)人員提供“邊做邊學式訓練”，迅速提高開發(fā)人員的SQL編程技能；提供上下文敏感的執(zhí)行計劃幫助系統和SQL運行狀態(tài)幫助；不是猜測或建議，而是獨一無二的SQL重寫解決方案。

2.4寫出專家級的SQL語句

LECCOSQLExpert的出現，使SQL的優(yōu)化變得極其簡單，只要能夠寫出SQL語句，它就能幫用戶找到最好性能的寫法。LECCOSQLExpert不僅能在很短的時間內找到所有可能的優(yōu)化方案，而且能夠通過實際測試，確