利用優化概要文件進行 SQL 調優

優化概要文件概述

在現代數據庫系統中，SQL 優化是非常重要的工作，其結果直接決定了數據庫的性能。當今主流的數據庫產品，其優化器都采用了基於成本分析的方法來對 SQL 語句進行優化，希望得到效率最高的執行計劃。所謂基於成本分析的優化，簡而言之就是優化器根據數據的統計信息，來計算出各種可選執行計劃的成本，從而選取成本最低的，即最優的執行計劃。然而由於各種條件所限，有時數據庫中並不具有全部且准確的統計信息，或者在一些特定的情況下，基於統計分析的優化並不能得到真正的最優的執行計劃，那麼用戶就可以應用本篇文章中所要講到的方法，利用 SQL 優化概要文件，來讓優化器生成指定規則的執行計劃，從而繞開優化器自帶的基於成本分析的優化。

DB2 的這種調優方式，和 Oracle 數據庫的 Hint 機制相對應，都是數據庫系統提供給用戶的可以直接指定執行計劃的方式。但相對於 Oracle 的 Hint 機制來說，DB2 的優化概要文件配置更靈活，它即可以針對某一具體的數據表來指定其訪問方式，也可以針對一條特定的 SQL 語句，來指定其最終的執行計劃，而且並不像 Oracle Hint 機制一樣需要改動 SQL 語句本身。

具體來講，就是用戶按著 DB2 提供的指定格式，來編寫一個 XML 文件，針對某張特定的數據表或者 SQL 語句，來指定其具體的執行計劃細節。清單 1 裡是一個簡單的示例，這個示例會被應用到第三章的案例分析中。該示例針對給定的 SQL 語句，指定了一條具體的優化規則，指定表 t1 和 t2 之間采用 Hash Join 的連接方式，同時利用 t1 作為小表來建立 Hash Table，視 t2 為大表來進行匹配操作，用戶把這個 XML 文件導入到指定的工具表中，並通過設置環境變量使其生效，那麼之後，優化器對於這樣的 SQL 語句，就會按著這個優化概要文件中所指定的規則，來生成其執行計劃，細節將會在第三章中詳述。

清單 1. 優化概要文件示例

　<?XML　version="1.0"　encoding="UTF-8"?>　
　<OPTPROFILE　VERSION="9.7.0.0">　
　<STMTPROFILE　ID="Sample">　
　<STMTKEY　SCHEMA="XQPENG">　
　<![CDATA[select　a.c3,b.c3　from　t2　a,t1　b　where　a.c1=b.c1　and　a.c1<100　and　b.c2　>60]]>　
　</STMTKEY>　
　<OPTGUIDELINES>　
　<HSJN>　
　<Access　TABLEID='b'/>　
　<Access　TABLEID='a'/>　
　</HSJN>　
　</OPTGUIDELINES>　
　</STMTPROFILE>　
　</OPTPROFILE>　

優化規則分類

用戶可以通過優化概要文件來對特定表或特定 SQL 語句指定優化規則，本章將對可以在優化概要文件中指定的各種優化規則分類詳述。

常規優化規則

用戶可以在優化概要文件中指定的常規優化規則有以下 5 種：

REOPT，當 SQL 語句中含有主變量或者參數標記時（Host Variable、Parameter Marker），該選項可以指定是否讓編譯器在每次進行數據訪問前根據已知的主變量和參數標記的值進行重新優化。可用的選項有 ONCE、ALWAYS 或 NONE，ALWAYS 代表著將每次進行數據訪問前都根據已知的主變量和參數標記的值進行重新優化，而 ONCE 意味著只在第一次訪問前，根據已知的主變量或者參數標值記進行一次重新優化。

QRYOPT，優化級別選項。它用來指定 SQL 優化所選擇的優化級別，可選的優化級別有 1、2、3、5、7、9，DB2 編譯器缺省使用的優化級別是 5 級。

DEGREE，並行度選項。它用來指定生成的訪問計劃的並行度，若並行度為 -1，則表明讓優化器根據系統情況去自行選擇並行度。

清單 3. INLIST2JOIN 查詢重寫規則示例 1

　Query:　
　SELECT　*　FROM　T1　WHERE　T1.C2　IN　(1,3)　
　
　Enable　INLIST　to　JOIN　Guideline:　
　<INLIST2JOIN　OPTION=　’　ENABLE　’　TABLE=　’　T1　’　COLUMN=　’　C2　’　/>　
　
　Rewritten　query:　
　SELECT　Q3.C1　AS　"C1",　Q3.C2　AS　"C2“　FROM　(SELECT　$INTERNAL_FUNC$()　
　FROM　(VALUES　1,　2)　AS　Q1)　AS　Q2,　DEACONU2.T1　AS　Q3　WHERE　(Q3.C2　=　Q2.$C0)　

清單 4. INLIST2JOIN 查詢重寫規則示例 2

　Query:　
　SELECT　*　FROM　T1　WHERE　T1.C2　IN　(1,3)　
　
　Disable　INLIST　to　JOIN　Guideline:　
　<INLIST2JOIN　OPTION=　’　DISABLE　’　TABLE=　’　T1　’　COLUMN=　’　C2　’　/>　
　
　Rewritten　query:　
　SELECT　Q1.C1　AS　"C1",　Q1.C2　AS　"C2“　
　FROM　DEACONU2.T1　AS　Q1　WHERE　Q1.C2　IN　(1,　3)　

訪問方式規則

用戶可以在優化概要文件中指定的訪問方式規則主要有以下 5 種：

TBSCAN，該選項指定采用表掃描的方式對特定的數據進行訪問。

IXSCAN，該選項指定通過索引對特定的數據進行訪問。

LPREFETCH ，該選項指定采用 List Prefetch 的方式來對特定的數據進行訪問。

IXAND，該選項指定采用 Index And 的方式來對特定的數據進行訪問。

IXOR，該選項指定采用 Index Or 的方式來對特定的數據進行訪問。

清單 5. List Prefetch 訪問方式規則示例

　Query:　
　SELECT　C1　FROM　T1　
　
　Guideline:　
　<OPTGUIDELINES>　<LPREFETCH　TABLE='T1　‘　INDEX=　‘　T1_I2　’　/>　<OPTGUIDELINES>　
　
　Access　Plan:　
　-----------　
　RETURN　
　|　
　FETCH　
　/--------+---------\　
　RIDSCN　TABLE:　DDEACONU　T1　
　|　
　SORT　
　　　|　
　IXSCAN　
　|　
　INDEX:　DDEACONU　T1_I2　

連接方式規則

用戶可以在優化概要文件中指定的連接方式規則主要有以下 3 種：

NLJOIN，用來指定采用 Nest Loop Join 來對兩個表進行連接。

MSJOIN，用來指定采用 Merge Sort Join 來對兩個表來進行連接。

HSJOIN，用來指定采用 Hash Join 來對兩個表來進行連接。

清單 6. 連接方式規則示例

　Query:　
　SELECT　*　FROM　T1　JOIN　T2　ON　T1.C1　=　T2.C1　
　
　Nest　Loop　Join　Guideline:　
　<OPTGUIDELINES>　<NLJOIN>　<TBSCAN　TABLE='T1'/>　
　<TBSCAN　TABLE='T2'/>　</NLJOIN>　</OPTGUIDELINES>　
　
　Merge　Sort　Join　Guideline:　
　<OPTGUIDELINES>　<MSJOIN>　<TBSCAN　TABLE='T1'/>　
　<TBSCAN　TABLE='T2'/>　</MSJOIN>　</OPTGUIDELINES>　
　
　Hash　Join　Guideline:　
　<OPTGUIDELINES>　<HSJOIN>　<TBSCAN　TABLE='T1'/>　
　<TBSCAN　TABLE='T2'/>　</HSJOIN>　</OPTGUIDELINES>