理解和實現(xiàn)最佳 SQL 查詢優(yōu)化目標可以大大改善很多查詢的響應時間。IBM® DB2® for i5/OS® on V5R4 提供了一種新方法，通過一些流行的動態(tài) SQL 接口控制查詢優(yōu)化。本文展示如何用 CLI 實現(xiàn)新的性能調(diào)優(yōu)增強。作為附加的優(yōu)點，您將必須利用 iSeries Navigator 的性能監(jiān)視器特性分析任何查詢的優(yōu)化目標。

DB2 for i5/OS on V5R4 經(jīng)過改進，現(xiàn)包含一個 Index Advice Condenser。查詢和 SQL 活動會留下一些索引建議。當考慮是否創(chuàng)建永久索引時，很多原始的索引建議可以看作是互補的或者可壓縮的。本文解釋新的 Index Advisor 壓縮特性，并演示如何在 iSeries Navigator 圖形界面中使用該特性，或直接用一個定制的用戶查詢來訪問該特性。

DB2 for i5/OS Index Advisor

Index Advisor 是 DB2 for i5/OS V5R4 中引入的特性，該特性記錄關(guān)于系統(tǒng)索引需要的統(tǒng)計信息。每當執(zhí)行一個查詢或 SQL 語句時，Index Advisor 都會記錄查詢優(yōu)化器建議使用的一些索引，留住提高性能的機會。這方面的基礎(chǔ)知識有助于理解這個新的 Index Advice Condenser。

每當建議一個索引時，就有一個新行被添加到系統(tǒng)索引建議表中，即模式 QSYS2 中的 SYSIXADV 表。這里是假設(shè)該索引之前還沒有被建議。Index Advisor 檢查以下列中的屬性，以判定一個索引是否已經(jīng)被優(yōu)化器建議：

LEADING_COLUMN_KEYSKEY_COLUMNS_ADVISEDTABLE_NAMETABLE_SCHEMAINDEX_TYPEPARTITION_NAMENLSS_TABLE_NAMENLSS_TABLE_SCHEMA表 3 給出了 SYSIXADV 表的完整布局。假如表中已經(jīng)有匹配的建議，則更新已有的行。例如，“Number of Times Advised條目加一，“Last Advised for Query Use被更新為當前時間。

就原始格式而言，索引建議不一定是冗長的。雖然表中所有條目都是惟一的，但是有些條目仍然存在冗余。一個很好的例子就是，假如有相同的一些列被建議，建議的順序有所不同，但這些鍵的順序無關(guān)緊要，那么就會出現(xiàn)上述情況。在這種情況下，可以用這些列創(chuàng)建覆蓋不同索引實例的一個索引。對被建議的鍵重新排序的時機可通過“Leading Keys Order Independent列表明。這一列中列出的鍵是可以被重新排序，且仍然能夠滿足被建議的索引的領(lǐng)頭鍵。

被建議索引的示例我們來看一些索引建議的示例，以便為 condenser 的簡化工作搭建背景。下面的例子假設(shè)所有建議都是基于相同表、相同分區(qū)、相同的索引類型和排序順序的。

表 1. 被建議索引的示例 1

被建議的鍵 領(lǐng)頭鍵，順序無關(guān)C1, C2, C3 C1, C2, C3C1, C3, C2 C1

在這個例子中，建議的第二行不易變通。為了滿足索引建議，一個永久的索引需要有順序為 { C1, C3, C2 } 的鍵。但是，建議的第一行有很大的靈活性。領(lǐng)頭鍵順序無關(guān)這個細節(jié)表明，鍵之間可以按任意順序排列，所以可以通過將 { C1, C2, C3 } 重新排序為 { C1, C3, C2 }，將兩行建議壓縮成一個永久索引。

假如擴展表 2 中的例子，使之包括第三行建議，那么仍然可以得出結(jié)論，附加的一個永久索引 { C1, C3, C2, C4 } 應該足以幫助優(yōu)化器永久地提高這些查詢的性能。

表 2. 被建議索引的示例 2

被建議的鍵 領(lǐng)頭鍵，順序無關(guān)C1, C2, C3 C1, C2, C3C1, C3, C2 C1C1, C3, C2, C4 C1, C2, C3, C4

Index Advisor 與壓縮的索引建議的比較

壓縮索引建議技術(shù)要求在服務器上安裝以下 V5R4M0 5722-SS1 PTF：SI25391、SI25469 和 SI25470。

當壓縮索引建議時，原始索引建議變成概要形式。原始建議中的一些字段是特定于建議實例的，在概要形式中沒有價值。為了保持壓縮的建議的重要性，其它字段則很重要。 表 3 逐列解釋了在索引建議壓縮期間原始索引建議發(fā)生的變化。跟 Index Advisor 下相比，壓縮的索引建議答應進行與之相同的 iSeries Navigator 動作，只有一個例外。由于壓縮的索引建議不是永久存在的，因此沒有“Remove from List動作。

表 3. Condenser 對列的更改

列名 列標題 壓縮動作Table_NAME 建議索引時所基于的表 保留，不作更改Table_SCHEMA 包含表的模式 保留，不作更改SYSTEM_TABLE_NAME 建議索引時所基于的系統(tǒng)表的表名 保留，不作更改PARTITION_NAME 索引的分區(qū)細節(jié) 保留，不作更改KEY_COLUMNS_ADVISED 被建議索引的列名 生成LEADING_COLUMN_KEYS 領(lǐng)頭的、順序無關(guān)的鍵，這些鍵在 Key_Columns_Advised 字段的開頭部分，可以重新排列該字段且仍能滿足被建議的索引 使用后丟棄INDEX_TYPE 基數(shù)（默認）或編碼向量索引（EVI） 保留，不作更改LAST_ADVISED 該行的上一次更新時間 使用最新建議時間戳TIMES_ADVISED 索引被建議的次數(shù) 通過總計生成ESTIMATED_CREATION_TIME 估計創(chuàng)建索引所需的秒數(shù) 使用最大值REASON_ADVISED 表明索引被建議的原因的原因碼 丟棄LOGICAL_PAGE_SIZE 建議用于索引的頁面大小 使用最大值MOST_EXPENSIVE_QUERY 查詢的執(zhí)行時間，單位為秒 使用最大值A(chǔ)VERAGE_QUERY_ESTIMATE 查詢的平均執(zhí)行時間，單位為秒 通過求平均值生成Table_SIZE 當索引被建議時表中的行數(shù) 使用最新建議中的值NLSS_TABLE_NAME 用于索引的排序順序表 保留，不作更改NLSS_TABLE_SCHEMA 排序順序表的庫名 保留，不作更改MTI_USED 因匹配的永久索引不存在，而使用與被建議定義相匹配的 MTI（被維護的臨時索引，自主索引）的次數(shù) 丟棄MTI_CREATED 這個特定的索引建議被用于創(chuàng)建 MTI 的次數(shù) 丟棄LAST_MTI_USED 上一次因匹配的永久索引不存在而使用 MTI 的時間 丟棄

下面的 SQL 語句用于生成 圖 1a 和 圖 1b 中包含的原始索引建議。

清單 1. 用于生成索引建議的數(shù)據(jù)庫和查詢-- Create sample database in CONDENSE schemaCALL QSYS.CREATE_SQL_SAMPLE('CONDENSE');SET SCHEMA Condense;SELECT e.firstnme, d.deptnmae FROM department d, employee eWHERE e.job = 'DESIGNER' AND YEAR(e.birthdate) > 1950 AND e.sex = 'M';SELECT e.firstnme, d.deptnmae FROM department d, employee eWHERE YEAR(e.birthdate) = 1953 AND e.job = 'DESIGNER' AND e.sex IN ('M');SELECT e.firstnme, d.deptnmae FROM department d, employee eORDER BY e.job, e.sex, e.birthdate;圖 1b 只包含 圖 1a 中放不下的索引建議屬性。注重，‘Keys Advised’列下的鍵的順序有所不同。

圖 1a. 被建議索引輸出圖 1b. 被建議索引輸出（續(xù)）在這個例子中，兩個被建議索引的鍵順序有足夠的靈活性，答應將建議壓縮成一個索引。圖 2 顯示了壓縮的索引建議。除了為用戶提供壓縮列鍵順序 { JOB, SEX, BIRTHDATE } 外，壓縮的建議還包括幫助確定建議的重要性的上下文信息。通過“Times Advised for Query Use和“Average of Query Estimates等列可以看出永久索引能為這個環(huán)境帶來多大的好處。而“Estimated Index Creation Time列用于判定是否要將創(chuàng)建索引作為預定的活動。

圖 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 視圖返回的數(shù)據(jù)。由于 CondensedIndexAdvice 視圖是用一個用戶定義表函數(shù)（UDTF）實現(xiàn)的，因此該視圖本身是只讀的。任何修改該視圖的嘗試都會出現(xiàn) SQL0150 錯誤。

清單 2. CondensedIndexAdvice 視圖定義QSYS2.CONDENSEDINDEXADVICE ( TABLE_NAME　FOR COLUMN TABNAME　VARCHAR(258) CCSID 37 NOT NULL ,TABLE_SCHEMAFOR COLUMN TABSCHEMACHAR(10) CCSID 37 NOT NULL ,SYSTEM_TABLE_NAME　 FOR COLUMN SYS_TNAMECHAR(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_ADVISEDFOR 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_QUERYFOR COLUMN QUERYCOSTINTEGER 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 CONDENSESELECT * FROM QSYS2.CONDENSEDINDEXADVICE WHERE table_schema = 'CONDENSE' ;-- Condensed index advice for a specific table, named EMPLOYEE in CONDENSE schemaSELECT * 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 secondsSELECT * FROM QSYS2.CONDENSEDINDEXADVICE　 WHERE table_schema LIKE 'Q%' AND average_query_estimate > 10 ;

新的 CondensedIndexAdvice 視圖使用的 Condense_Advice UDTF 還可以直接通過用戶查詢來訪問。下面是這個表函數(shù)的定義，另外還有一個簡單的例子，用于演示如何使用該 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 ;

結(jié)束語

不管是使用壓縮索引建議還是原始索引建議，都是改善索引策略的一個有效的辦法。在根據(jù)建議采取行動之前，需要考慮的重要的一點是查看已有的索引和它們的使用情況。由于任何索引都隱含著維護成本，因此任何性能調(diào)優(yōu)活動的共同之處就是限制永久索引的數(shù)量。本文的主旨是，查看壓縮的索引建議，以發(fā)現(xiàn)改善性能的機遇，并且在作出任何更改之前和之后，查看索引的使用情況。