理解和實現最佳 SQL 查詢優化目標可以大大改善很多查詢的響應時間。IBM® DB2® for i5/OS® on V5R4 提供了一種新方法，通過一些流行的動態 SQL 接口控制查詢優化。本文展示如何用 CLI 實現新的性能調優增強。作為附加的優點，您將必須利用 iSerIEs Navigator 的性能監視器特性分析任何查詢的優化目標。
DB2 for i5/OS on V5R4 經過改進，現包含一個 Index Advice Condenser。查詢和 SQL 活動會留下一些索引建議。當考慮是否創建永久索引時，很多原始的索引建議可以看作是互補的或者可壓縮的。本文解釋新的 Index Advisor 壓縮特性，並演示如何在 iSerIEs Navigator 圖形界面中使用該特性，或直接用一個定制的用戶查詢來訪問該特性。
DB2 for i5/OS Index Advisor
Index Advisor 是 DB2 for i5/OS V5R4 中引入的特性，該特性記錄關於系統索引需要的統計信息。每當執行一個查詢或 SQL 語句時，Index Advisor 都會記錄查詢優化器建議使用的一些索引，留住提高性能的機會。這方面的基礎知識有助於理解這個新的 Index Advice Condenser。
每當建議一個索引時，就有一個新行被添加到系統索引建議表中，即模式 QSYS2 中的 SYSIXADV 表。這裡是假設該索引之前還沒有被建議。Index Advisor 檢查以下列中的屬性，以判斷一個索引是否已經被優化器建議：
LEADING_COLUMN_KEYS
KEY_COLUMNS_ADVISED
TABLE_NAME
TABLE_SCHEMA
INDEX_TYPE
PARTITION_NAME
NLSS_TABLE_NAME
NLSS_TABLE_SCHEMA
表 3 給出了 SYSIXADV 表的完整布局。如果表中已經有匹配的建議，則更新已有的行。例如，“Number of Times Advised”條目加一，“Last Advised for Query Use”被更新為當前時間。
就原始格式而言，索引建議不一定是冗長的。雖然表中所有條目都是惟一的，但是有些條目仍然存在冗余。一個很好的例子就是，如果有相同的一些列被建議，建議的順序有所不同，但這些鍵的順序無關緊要，那麼就會出現上述情況。在這種情況下，可以用這些列創建覆蓋不同索引實例的一個索引。對被建議的鍵重新排序的時機可通過“Leading Keys Order Independent”列表明。這一列中列出的鍵是可以被重新排序，且仍然能夠滿足被建議的索引的領頭鍵。
被建議索引的示例
我們來看一些索引建議的示例，以便為 condenser 的簡化工作搭建背景。下面的例子假設所有建議都是基於相同表、相同分區、相同的索引類型和排序順序的。
表 1. 被建議索引的示例 1
被建議的鍵 領頭鍵，順序無關 C1, C2, C3 C1, C2, C3 C1, C3, C2 C1
在這個例子中，建議的第二行不易變通。為了滿足索引建議，一個永久的索引需要有順序為 { C1, C3, C2 } 的鍵。但是，建議的第一行有很大的靈活性。領頭鍵順序無關這個細節表明，鍵之間可以按任意順序排列，所以可以通過將 { C1, C2, C3 } 重新排序為 { C1, C3, C2 }，將兩行建議壓縮成一個永久索引。
如果擴展表 2 中的例子，使之包括第三行建議，那麼仍然可以得出結論，附加的一個永久索引 { C1, C3, C2, C4 } 應該足以幫助優化器永久地提高這些查詢的性能。
表 2. 被建議索引的示例 2
被建議的鍵 領頭鍵，順序無關 C1, C2, C3 C1, C2, C3 C1, C3, C2 C1 C1, C3, C2, C4 C1, C2, C3, C4
Index Advisor 與壓縮的索引建議的比較
壓縮索引建議技術要求在服務器上安裝以下 V5R4M0 5722-SS1 PTF：SI25391、SI25469 和 SI25470。
當壓縮索引建議時，原始索引建議變成概要形式。原始建議中的一些字段是特定於建議實例的，在概要形式中沒有價值。為了保持壓縮的建議的重要性，其它字段則很重要。 表 3 逐列解釋了在索引建議壓縮期間原始索引建議發生的變化。跟 Index Advisor 下相比，壓縮的索引建議允許進行與之相同的 iSerIEs Navigator 動作，只有一個例外。由於壓縮的索引建議不是永久存在的，因此沒有“Remove from List”動作。
表 3. Condenser 對列的更改
列名 列標題 壓縮動作 Table_NAME 建議索引時所基於的表 保留，不作更改 Table_SCHEMA 包含表的模式 保留，不作更改 SYSTEM_TABLE_NAME 建議索引時所基於的系統表的表名 保留，不作更改 PARTITION_NAME 索引的分區細節 保留，不作更改 KEY_COLUMNS_ADVISED 被建議索引的列名 生成 LEADING_COLUMN_KEYS 領頭的、順序無關的鍵，這些鍵在 Key_Columns_Advised 字段的開頭部分，可以重新排列該字段且仍能滿足被建議的索引 使用後丟棄 INDEX_TYPE 基數（默認）或編碼向量索引（EVI） 保留，不作更改 LAST_ADVISED 該行的上一次更新時間 使用最新建議時間戳 TIMES_ADVISED 索引被建議的次數 通過總計生成 ESTIMATED_CREATION_TIME 估計創建索引所需的秒數 使用最大值 REASON_ADVISED 表明索引被建議的原因的原因碼 丟棄 LOGICAL_PAGE_SIZE 建議用於索引的頁面大小 使用最大值 MOST_EXPENSIVE_QUERY 查詢的執行時間，單位為秒 使用最大值 AVERAGE_QUERY_ESTIMATE 查詢的平均執行時間，單位為秒 通過求平均值生成 Table_SIZE 當索引被建議時表中的行數 使用最新建議中的值 NLSS_TABLE_NAME 用於索引的排序順序表 保留，不作更改 NLSS_TABLE_SCHEMA 排序順序表的庫名 保留，不作更改 MTI_USED 因匹配的永久索引不存在，而使用與被建議定義相匹配的 MTI（被維護的臨時索引，自主索引）的次數 丟棄 MTI_CREATED 這個特定的索引建議被用於創建 MTI 的次數 丟棄 LAST_MTI_USED 上一次因匹配的永久索引不存在而使用 MTI 的時間 丟棄
下面的 SQL 語句用於生成 圖 1a 和 圖 1b 中包含的原始索引建議。
清單 1. 用於生成索引建議的數據庫和查詢-- Create sample database in CONDENSE schema
CALL QSYS.CREATE_SQL_SAMPLE('CONDENSE');
SET SCHEMA Condense;
SELECT e.firstnme, d.deptnmae FROM department d, employee e
WHERE e.job = 'DESIGNER' AND YEAR(e.birthdate) > 1950 AND e.sex = 'M';
SELECT e.firstnme, d.deptnmae FROM department d, employee e
WHERE YEAR(e.birthdate) = 1953 AND e.job = 'DESIGNER' AND e.sex IN ('M');
SELECT e.firstnme, d.deptnmae FROM department d, employee e
ORDER BY e.job, e.sex, e.birthdate;
圖 1b 只包含 圖 1a 中放不下的索引建議屬性。注意，‘Keys Advised’列下的鍵的順序有所不同。
圖 1a. 被建議索引輸出
圖 1b. 被建議索引輸出（續）
在這個例子中，兩個被建議索引的鍵順序有足夠的靈活性，允許將建議壓縮成一個索引。圖 2 顯示了壓縮的索引建議。除了為用戶提供壓縮列鍵順序 { JOB, SEX, BIRTHDATE } 外，壓縮的建議還包括幫助確定建議的重要性的上下文信息。通過“Times Advised for Query Use”和“Average of Query Estimates”等列可以看出永久索引能為這個環境帶來多大的好處。而“Estimated Index Creation Time”列用於判斷是否要將創建索引作為預定的活動。
圖 2. 壓縮的索引建議
iSerIEs Navigator 壓縮器界面
在 iSerIEs Navigator 中，有 Index Advisor 的地方就有壓縮索引建議動作。圖 3 顯示了壓縮器的圖形化界面，該界面可以通過右鍵單擊一個模式對象打開。也可以從一個表對象中進行訪問。
圖 3. iSerIEs Navigator 壓縮器界面
為了使用壓縮器的 iSeries Navigator 界面，需要在客戶機上安裝最新的 V5R4M0 iSerIEs Access for Windows Service Pack。
可編程壓縮器接口
除了 iSerIEs Navigator 界面以外，還可以用一條 SQL 語句以可編程方式訪問索引建議壓縮器。DB2 for i5/OS 在 QSYS2 模式中提供了一個新的視圖 CondensedIndexAdvice。清單 2 顯示了新的 CondensedIndexAdvice 視圖返回的數據。由於 CondensedIndexAdvice 視圖是用一個用戶定義表函數（UDTF）實現的，因此該視圖本身是只讀的。任何修改該視圖的嘗試都會出現 SQL0150 錯誤。
清單 2. CondensedIndexAdvice 視圖定義QSYS2.CONDENSEDINDEXADVICE (
TABLE_NAME FOR COLUMN TABNAME VARCHAR(258) CCSID 37 NOT NULL ,
TABLE_SCHEMA FOR COLUMN TABSCHEMA CHAR(10) CCSID 37 NOT NULL ,
SYSTEM_TABLE_NAME FOR COLUMN SYS_TNAME CHAR(10) CCSID 37 NOT NULL ,
PARTITION_NAME FOR COLUMN TABPART VARCHAR(128) CCSID 37 DEFAULT NULL ,
KEY_COLUMNS_ADVISED FOR COLUMN KEYSADV VARCHAR(16000) CCSID 37 DEFAULT NULL ,
INDEX_TYPE CHAR(14) CCSID 37 DEFAULT NULL ,
LAST_ADVISED FOR COLUMN LASTADV TIMESTAMP DEFAULT NULL ,
TIMES_ADVISED FOR COLUMN TIMESADV BIGINT DEFAULT NULL ,
ESTIMATED_CREATION_TIME FOR COLUMN ESTTIME INTEGER DEFAULT NULL ,
LOGICAL_PAGE_SIZE FOR COLUMN "PAGESIZE" INTEGER DEFAULT NULL ,
MOST_EXPENSIVE_QUERY FOR COLUMN QUERYCOST INTEGER DEFAULT NULL ,
AVERAGE_QUERY_ESTIMATE FOR COLUMN QUERYEST INTEGER DEFAULT NULL ,
TABLE_SIZE BIGINT DEFAULT NULL ,
NLSS_TABLE_NAME FOR COLUMN NLSSNAME CHAR(10) CCSID 37 DEFAULT NULL ,
NLSS_TABLE_SCHEMA FOR COLUMN NLSSSCHEMA CHAR(10) CCSID 37 DEFAULT NULL )
通過下面的 SELECT 語句可以看到，編寫 SQL 語句來訪問壓縮的建議很簡單。如前所述，壓縮的索引建議分析可以在表級進行，也可以在模式級進行。
清單 3. CondensedIndexAdvice 查詢示例-- Condensed index advice for an entire schema, named CONDENSE
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE WHERE table_schema = 'CONDENSE' ;
-- Condensed index advice for a specific table, named EMPLOYEE in CONDENSE schema
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE
WHERE table_name = 'EMPLOYEE' AND table_schema = 'CONDENSE';
-- Condensed index advice for a range of schemas, where the average
-- query estimate driving the index advice is greater than 10 seconds
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE
WHERE table_schema LIKE 'Q%' AND average_query_estimate > 10 ;
新的 CondensedIndexAdvice 視圖使用的 Condense_Advice UDTF 還可以直接通過用戶查詢來訪問。下面是這個表函數的定義，另外還有一個簡單的例子，用於演示如何使用該 UDTF。
清單 4. Condense_Advice UDTFCREATE FUNCTION QSYS2.Condense_Advice(TABLE_SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128) )
RETURNS TABLE(TABLE_PARTITION VARCHAR(128), KEY_COLUMNS_ADVISED VARCHAR(16000),
INDEX_TYPE CHAR(14), LAST_ADVISED TIMESTAMP, TIMES_ADVISED BIGINT,
ESTIMATED_CREATION_TIME INTEGER, LOGICAL_PAGE_SIZE INTEGER,
MOST_EXPENSIVE_QUERY INTEGER, AVERAGE_QUERY_ESTIMATE INTEGER,
TABLE_SIZE BIGINT, NLSS_TABLE_NAME CHAR(10),NLSS_TABLE_SCHEMA CHAR(10))
LANGUAGE C
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SCRATCHPAD 325064
DISALLOW PARALLEL
FINAL CALL
CARDINALITY 1
EXTERNAL NAME 'QSYS/QDBSSUDF2(CONDENSE_ADVICE)'
PARAMETER STYLE DB2SQL;
-- Query the condenser UDTF directly, providing selection criteria and ordering
-- the results of the 15 most important condensed entrIEs
SELECT * FROM TABLE(QSYS2.CONDENSE_ADVICE('CRPDTA','B123456')) AS a
WHERE a.table_size >(1024*1024) AND MONTH(last_advised)= MONTH(CURRENT TIMESTAMP)
ORDER BY average_query_estimate DESC
FETCH FIRST 15 ROWS ONLY ;
結束語
不管是使用壓縮索引建議還是原始索引建議，都是改善索引策略的一個有效的辦法。在根據建議采取行動之前，需要考慮的重要的一點是查看已有的索引和它們的使用情況。由於任何索引都隱含著維護成本，因此任何性能調優活動的共同之處就是限制永久索引的數量。本文的主旨是，查看壓縮的索引建議，以發現改善性能的機遇，並且在作出任何更改之前和之後，查看索引的使用情況。
