[計(jì)算機(jī)]DB2數(shù)據(jù)庫優(yōu)化ppt課件

1、DB2數(shù)據(jù)庫優(yōu)化 DB2 統(tǒng)計(jì)信息保持最新 假如沒有存儲在 DB2 系統(tǒng)目錄中的統(tǒng)計(jì)信息，優(yōu)化器在優(yōu)化任何事物時都會遇到困難。這些統(tǒng)計(jì)信息向優(yōu)化器提供了與正在被優(yōu)化的 SQL 語句將要訪問的表狀態(tài)相關(guān)的信息。存儲在系統(tǒng)目錄中的統(tǒng)計(jì)信息的類型包括： 關(guān)于 表表的信息，包括總的行數(shù)、關(guān)于壓縮的信息和總頁數(shù)； 關(guān)于 列列的信息，包括列的離散值的數(shù)量和存儲在列中的值的分布范圍； 關(guān)于 表空間表空間的信息，包括活動頁面的數(shù)量； 索引索引的當(dāng)前狀態(tài)，包括是否存在索引、索引的組織葉子頁的數(shù)量和級別的數(shù)量、索引鍵的離散值的數(shù)量以及是否群集索引； 關(guān)于表空間和索引節(jié)點(diǎn)組或分區(qū)的信息。 當(dāng)執(zhí)行 RUNSTATS

2、或 RUN STATISTICS 實(shí)用程序時，統(tǒng)計(jì)信息就會填充 DB2 系統(tǒng)目錄。您可以從控制中心Control Center、批處理作業(yè)或通過使用命令行處理器來調(diào)用該實(shí)用程序。一定要確保在適當(dāng)?shù)臅r候積累統(tǒng)計(jì)信息，尤其是在消費(fèi)環(huán)境中。RUNSTATS語法表：表： db2 RUNSTATS ON TABLE 表名 索引：索引： db2 RUNSTATS ON TABLE 表名 FOR INDEXES ALL 表和索引：表和索引： db2 RUNSTATS ON TABLE 表名 AND INDEXES ALL 增強(qiáng)的統(tǒng)計(jì)信息增強(qiáng)的統(tǒng)計(jì)信息表：表： db2 RUNSTATS ON TABLE 表名

3、 WITH DISTRIBUTION 索引：索引： db2 RUNSTATS ON TABLE 表名 FOR DETAILED INDEXES ALL 表和索引：表和索引： db2 RUNSTATS ON TABLE 表名 WITH DISTRIBUTION AND DETAILED INDEXES ALL 常用例子常用例子RUNSTATS ON TABLE TR1ADMIN.MID_GSM_VOICE_CDR_000 ON KEY COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND SAMPLED DETAILED INDEXES ALL ALLOW R

4、EAD ACCESS TABLESAMPLE BERNOULLI 1 REPEATABLE 1 SET PROFILE NONE分布鍵分布鍵是一列或一組列，用來確定存儲特定數(shù)據(jù)行的數(shù)據(jù)庫分區(qū)。分布鍵是使用 CREATE TABLE 語句在表上定義的。假如沒有為分布在數(shù)據(jù)庫分區(qū)組中的多個數(shù)據(jù)庫分區(qū)中的表空間中的表定義分布鍵，在缺省情況下將會根據(jù)主鍵的第一列創(chuàng)立分布鍵。假設(shè)未指定主鍵，那么缺省分布鍵是在該表中定義的第一個非長型字段列。長型包括所有長型數(shù)據(jù)類型和所有大對象LOB數(shù)據(jù)類型。假如正在與單一分區(qū)數(shù)據(jù)庫分區(qū)組相關(guān)的表空間中創(chuàng)立表，且您希望有分布鍵，那么必須顯式定義該分布鍵。缺省情況下，不會創(chuàng)

5、立該分布鍵。當(dāng)定義分布鍵時，應(yīng)該考慮以下幾點(diǎn)：不支持創(chuàng)立只包含長型數(shù)據(jù)類型LONG VARCHAR、LONG VARGRAPHIC、BLOB、CLOB 或 DBCLOB的多分區(qū)表。 不能改變分布鍵定義。 分布鍵應(yīng)該包括最頻繁連接的列。 分布鍵應(yīng)該由經(jīng)常參與 GROUP BY 子句的列組成。 任何唯一鍵或主鍵必須包含所有分布鍵列。 索引索引是行位置的列表，按一個或多個指定列的內(nèi)容來排序。索引通常用于加速對表的訪問。創(chuàng)立索引的一些準(zhǔn)那么包括：在適當(dāng)?shù)胤蕉x主鍵和唯一索引。 創(chuàng)立關(guān)于查詢用來連接表Join 謂詞的任何列的索引。 創(chuàng)立關(guān)于基于常規(guī)根底從中搜索特定值的任何列的索引。 創(chuàng)立關(guān)于通常用在 O

6、RDER BY 子句中的列的索引。 確保使用了僅檢索您需要的數(shù)據(jù)的謂詞。例如，確保謂詞的選擇性值表示您想要返回的那部分表。 當(dāng)創(chuàng)立多列索引時，索引的第一列應(yīng)該為查詢中的謂詞最常用的那一列。 確保索引造成的磁盤和更新維護(hù)的開銷不會太高。 根據(jù)查詢所使用的列建立多列索引 對于一個特定的查詢，可以為某一個表所有出如今查詢中的列建立一個結(jié)合索引，包括出如今 select 子句和條件語句中的列。但簡單的建立一個覆蓋所有列的索引并不一定能有效進(jìn)步查詢，因?yàn)樵诙嗔兴饕辛械捻樞蚴欠浅Ｖ匾?。這個特性是由于索引的 B+ 樹構(gòu)造決定的。一般情況下，要根據(jù)謂詞的選擇度來排列索引中各列的位置，選擇度大的謂詞所使用的

7、列放在索引的前面，把那些只存在與 select 子句中的列放在索引的最后。 select add_date from temp.customer where city = WASHINGTON and cntry_code = USA; 在 temp.customer 上建立 city，cntry_code，add_date 索引。 根據(jù)條件語句中的謂詞的選擇度創(chuàng)立索引 建立索引需要占用數(shù)據(jù)庫的存儲空間，所以需要在空間和時間性能之間進(jìn)展權(quán)衡。很多時候，只考慮那些在條件子句中有條件判斷的列上建立索引會也會同樣有效，同時節(jié)約了空間。 1. select count* from temp.custo

8、mer where city = WASHINGTON and cntry_code = USA; 2. select count* from temp.customer where city = WASHINGTON; 3. select count* from temp.customer where cntry_code = USA; Results:Results: 1. 1404 2. 1407 3. 128700 選擇度越大，過濾掉的記錄越多，返回的結(jié)果集也就越小。從清單 6 的結(jié)果可以看到，第二個查詢的選擇度幾乎有和整個條件語句一樣。因此可以直接建立單列索引 city，其性能與索引

9、 city，cntry_code，add_date 具有相差不多的性能。 從下表中可以看到單列索引 city 具有更加有效的性能空間比，也就是說占有盡可能小的空間得到盡可能高的查詢速度。 索引索引 查詢方案總代價索引大小查詢方案總代價索引大小cust_i1city,cntry_code,add_date 28.94 timerons 19.52Mcust_i3city 63.29 timerons 5.48M防止在建有索引的列上使用函數(shù) 這是一個很簡單的原那么。假如在建有索引的列上使用函數(shù)，由于函數(shù)的單調(diào)性不確定，函數(shù)的返回值和輸入值可能不會一一對應(yīng)，就可能存在索引中位置差異很大的多個列值可以

10、滿足帶有函數(shù)的謂詞條件，因此 DB2 優(yōu)化器將無法進(jìn)展 Matching Index Scan，更壞的情況下可能會導(dǎo)致直接進(jìn)展表掃描。 在那些需要被排序的列上創(chuàng)立索引 這里的排序不僅僅指 order by 子句，還包括 distinct 和 group by 子句，他們都會產(chǎn)生排序的操作。由于索引本身是有序的，在其創(chuàng)立過程中已經(jīng)進(jìn)展了排序處理，因此在應(yīng)用這些語句的列上創(chuàng)立索引會降低排序操作的代價。這種情況一般針對于沒有條件語句的查詢。假如存在條件語句，DB2 優(yōu)化器會首先選擇出滿足條件的紀(jì)錄，然后才對中間結(jié)果集進(jìn)展排序。對于沒有條件語句的查詢，排序操作在總的查詢代價中會占有較大比重，因此可以較

11、大限度的利用索引的排序構(gòu)造進(jìn)展查詢優(yōu)化。此時可以創(chuàng)立單列索引，假如需要創(chuàng)立結(jié)合索引那么需要把被排序的列放在結(jié)合索引的第一列。 合理使用 include include 關(guān)鍵詞創(chuàng)立索引 include include 只能用在創(chuàng)立唯一性索引中。只能用在創(chuàng)立唯一性索引中。 select cust_name from temp.customer where cust_num between 0007000000 and 0007200000 可以在 cust_num 和 cust_name 上建立結(jié)合索引來進(jìn)步查詢性能。但是由于 cust_num 是主鍵，可以使用 include 關(guān)鍵字創(chuàng)立唯一性索

12、引：create unique index temp.cust_i7 on temp.customercust_num include cust_name使用 include 后，cust_name 列的數(shù)據(jù)將只存在于索引樹的葉子節(jié)點(diǎn)，并不存在于索引的關(guān)鍵字中。這種情況下，使用帶有 include 列的唯一索引會帶來優(yōu)于結(jié)合索引的性能，因?yàn)槲ㄒ凰饕梢苑乐挂恍┎槐匾牟僮?，如排序?一般情況下，當(dāng)查詢的 where 子句中存在主鍵的謂詞我們就可以創(chuàng)立帶有 include 列的唯一索引，形成純索引訪問來進(jìn)步查詢性能。 指定索引的排序?qū)傩?select maxadd_date from temp.

13、employeecreate index temp.employee_i1 on temp.employeeadd_date這里存在一個誤區(qū)，大家可能認(rèn)為既然查詢里要獲得的是 add_date 的最大值，而我們又在 add_date 上建立了一個索引，優(yōu)化器應(yīng)該知道從索引樹中直接去尋找最大值。但是實(shí)際情況并非如此，因?yàn)閯?chuàng)立索引的時候并沒有指定排序?qū)傩?，默認(rèn)為 ASC 升序排列，DB2 將會掃描整個索引樹的葉子節(jié)點(diǎn)獲得所有值后，然后取其最大。我們可以通過設(shè)置索引的排序?qū)傩詠磉M(jìn)步查詢性能。create index temp.employee_i1 on temp.employeeadd_date

14、desc對于降序排列的索引，DB2 不需要掃描整個索引數(shù)的葉子節(jié)點(diǎn)，因?yàn)榈谝粋€節(jié)點(diǎn)便是最大的。我們同樣可以使用 ALLOW REVERSE SCANS 來指定索引為雙向掃描，具有和 DESC 近似的查詢性能。ALLOW REVERSE SCANS 可以被認(rèn)為是 ASC 和 DESC 的組合，只是在以后數(shù)據(jù)更新的時候維護(hù)本錢會相對高一些。假如無法改變索引的排序?qū)傩裕俏覀兙哂蓄~外的信息，該公司每個月都會有新員工入職，那么這個查詢就可以改寫成：select maxadd_date from temp.employee where add_date current timestamp - 1 mo

15、nth這樣通過限定一個查詢范圍也會有效地進(jìn)步查詢性能。謂詞 用戶應(yīng)用程序使用查詢語句從數(shù)據(jù)庫懇求一組行，該查詢語句指定要作為結(jié)果集返回的特定行的限定詞。這些限定符通常出如今查詢的 WHERE 子句。這種限定符稱為謂詞。 可以將謂詞分組為四種類別，按如何在求值過程中使用該謂詞以及何時使用它來確定這些類別。這些類別列示如下，按最正確性能到最差性能的順序排列： 范圍定界謂詞 索引控制謂詞 數(shù)據(jù)控制謂詞 殘留謂詞 DB2 組件和謂詞組件簡介關(guān)系數(shù)據(jù)效勞RDS從應(yīng)用程序那里接收到SQL懇求，并返回結(jié)果集;除了剩余謂詞外，RDS將所有謂詞都發(fā)送給數(shù)據(jù)管理效勞DMS;剩余謂詞由RDS進(jìn)展評估。DMS評估數(shù)據(jù)

16、參數(shù)謂詞。假如SELECT列表中存在一些不能由索引搜索進(jìn)展評估的列，那么，DMS就會直接掃描數(shù)據(jù)頁。索引管理器從DMS那里接收、評估范圍界定謂詞和索引參數(shù)謂詞，然后，將數(shù)據(jù)頁的行標(biāo)識符RID返回給DMS。謂詞類型特征特征 謂詞類型謂詞類型 范圍定界范圍定界索引控制索引控制數(shù)據(jù)控制數(shù)據(jù)控制殘留殘留減少索引 I/O 是 否 否 否 減少數(shù)據(jù)頁 I/O 是 是 否 否 減少內(nèi)部傳送的行數(shù) 是 是 是 否 減少合格行的數(shù)目 是 是 是 是 范圍定界 范圍定界謂詞限制索引掃描的范圍。它們提供索引搜索的開場和停頓鍵值。范例：、INDEX IX1: NAMEASC,DEPTASC,MGRDESC,SALAR

17、Y DESC, YEARS ASC 、WHERE NAME = :hv1AND DEPT = :hv2AND YEARS :hv5前兩個謂詞NAME = :hv1 和 DEPT = :hv2是范圍定界謂詞，而 YEARS :hv5 是索引控制謂詞。優(yōu)化器在對這些謂詞求值時使用索引數(shù)據(jù)而不是讀取根本表。這些索引控制謂詞減小需要從表中讀取的行集，但它們不影響訪問的索引頁數(shù)。索引控制 索引控制謂詞不能限制搜索范圍，但可根據(jù)該索引來求值，因?yàn)樵谠撝^詞中涉及到的列是索引鍵的一部分。 數(shù)據(jù)控制 索引管理器不能求值但數(shù)據(jù)管理效勞可以求值的謂詞稱為數(shù)據(jù)控制謂詞。這些謂詞通常需要訪問表中的單獨(dú)行。假如需要，“數(shù)

18、據(jù)管理效勞檢索對該謂詞求值需要的列以及任何其他列以滿足 SELECT 列表中不能從該索引獲取的列。通常，這種謂詞需要從根本表中存取個別行，假如需要的話，數(shù)據(jù)管理效勞還會提取需要的列來評估該謂詞。例如，假設(shè)索引定義在表project的projno列上，而不是deptno列上，執(zhí)行下面的查詢：SELECT projno，projname，repemp FROM projectWHEREdeptno=D11ORDER BY projno謂詞“deptno=D11是數(shù)據(jù)參數(shù)謂詞，因?yàn)闆]有索引定義在deptno列上，必須存取根本表來評估該謂詞。殘留謂詞殘留謂詞比訪問表需要更多的 I/O 本錢。它們可具有

19、以下特征： 使用相關(guān)子查詢 使用量化子查詢，這些子查詢包含 ANY、ALL、SOME 或 IN 子句 讀取 LONG VARCHAR 或 LOB 數(shù)據(jù)，該數(shù)據(jù)存儲在與表分開的文件中剩余謂詞是用關(guān)系數(shù)據(jù)效勞RDS來進(jìn)展評估的，而且，它在這四類謂詞中本錢最昂貴。由于相對范圍界定謂詞和索引參數(shù)謂詞來說，剩余謂詞和數(shù)據(jù)參數(shù)謂詞的本錢比較高，所以，我們應(yīng)該盡可能地限制范圍界定謂詞和索引參數(shù)謂詞界定的行數(shù)。在只使用一條語句即可做到時防止使用多條語句 INSERT INTO tab_comp VALUES item1, price1, qty1; INSERT INTO tab_comp VALUES it

20、em2, price2, qty2; INSERT INTO tab_comp VALUES item3, price3, qty3; INSERT INTO tab_comp VALUES item1, price1, qty1, item2, price2, qty2, item3, price3, qty3; SET A = expr1; SET B = expr2; SET C = expr3; VALUES expr1, expr2, expr3 INTO A, B, C; 從多個 SQL 語句到一個 SQL 表達(dá)式 SQL 語言提供了兩類條件構(gòu)造：過程型IF 和 CASE 語句和函

21、數(shù)型CASE 表達(dá)式。在大多數(shù)環(huán)境中，可使用任何一種構(gòu)造來表達(dá)計(jì)算，到底使用哪一種只是愛好問題。但是，使用 CASE 表達(dá)式編寫的邏輯不但比使用 CASE 或 IF 語句編寫的邏輯更緊湊，而且更有效。 IF Price = MaxPrice THEN INSERT INTO tab_compId, Val VALUESOid, Price; ELSE INSERT INTO tab_compId, Val VALUESOid, MaxPrice; END IF; INSERT INTO tab_compId, Val VALUESOid, CASE WHEN Price = MaxPrice

22、THEN Price ELSE MaxPrice END; 防止過程層和數(shù)據(jù)流層之間的上下文切換 謂詞中的行表達(dá)式 謂詞比較已得到擴(kuò)展，可支持將一組值稱為行表達(dá)式與另一組值進(jìn)展比較。 值比較SELECT * FROM t1 WHERE c1, c2, SUBSTRc3,1,2 = 10, c4, SQSELECT * FROM t1 WHERE c1 = 10 AND c2 = c4 AND SUBSTRc3,1,2 = SQ 子查詢SELECT * FROM t1 WHERE c1, c2, SUBSTRc3,1,1 = SELECT a, b, c FROM mytable WHERE d

23、=t1.c4 在無副作用的情況下，請使用 SQL 函數(shù) SQL 過程和 SQL 函數(shù)是使用不同技術(shù)實(shí)現(xiàn)的。SQL 過程中的查詢是單獨(dú)編譯的，每個查詢都成為包中的一個節(jié)。編譯是在過程創(chuàng)立時進(jìn)展的，直到重新創(chuàng)立過程或者直到重新綁定其相關(guān)的包時才重新編譯這些查詢。另一方面，SQL 函數(shù)中的查詢是一起編譯的，就好象函數(shù)體是一個查詢一樣。每當(dāng)編譯一條使用 SQL 函數(shù)的語句時，也會對 SQL 函數(shù)進(jìn)展編譯。與 SQL 過程中所發(fā)生的情況不同，SQL 函數(shù)中的過程語句與數(shù)據(jù)流語句是在同一個層中執(zhí)行的。因此，每當(dāng)控制從過程語句流向數(shù)據(jù)流語句或相反時，并不發(fā)生上下文切換。因?yàn)榇嬖谶@些區(qū)別，所以當(dāng)給定的過程代碼

24、段作為函數(shù)實(shí)現(xiàn)時的執(zhí)行速度通常比作為過程實(shí)現(xiàn)時要快。只是從數(shù)據(jù)庫抽取數(shù)據(jù)而不執(zhí)行任何更改時，請考慮使用只是從數(shù)據(jù)庫抽取數(shù)據(jù)而不執(zhí)行任何更改時，請考慮使用 SQL SQL 函數(shù)而不是使用函數(shù)而不是使用 SQL SQL 過程過程 CREATE PROCEDURE GetPrice IN Vendor CHAR20, IN Pid INT, OUT price DECIMAL10,3 LANGUAGE SQL BEGIN IF Vendor = Vendor 1 THEN SET price = SELECT ProdPrice FROM V1 WHERE Id = Pid; ELSE IF Ven

25、dor = Vendor 2 THEN SET price = SELECT Price FROM V2 WHERE Pid = GetPrice.Pid; END IF; END CREATE FUNCTION GetPrice Vendor CHAR20, PId INT RETURNS DECIMAL10,3 LANGUAGE SQL BEGIN DECLARE price DECIMAL10,3; IF Vendor = Vendor 1 THEN SET price = SELECT ProdPrice FROM V1 WHERE Id = Pid; ELSE IF Vendor =

26、 Vendor 2 THEN SET price = SELECT Price FROM V2 WHERE Pid = GetPrice.Pid; END IF; RETURN price; END 使用用于臨時數(shù)據(jù)的臨時表 臨時表的操作通常比對常規(guī)表的操作快 臨時表的創(chuàng)立不會涉及向目錄中插入項(xiàng)，并且臨時表的使用也不會涉及對目錄的訪問；因此，不會有目錄爭用問題。 臨時表只能由創(chuàng)立它們的應(yīng)用程序訪問，因此在其操作中不會涉及鎖定問題。 假如指定了 NOT LOGGED 選項(xiàng)，那么不對臨時表上的操作記錄日志當(dāng)然，這樣就不可能回滾更改。因此，假如您的存儲過程生成了大量臨時數(shù)據(jù)，并只打算在數(shù)據(jù)庫的一個會

27、話中使用它們，那么請將這些數(shù)據(jù)存儲進(jìn)臨時表，這樣可以顯著地改進(jìn)性能。 臨時表本卷須知分布鍵DECLARE GLOBAL TEMPORARY TABLE SESSION. .WJL_TMP_M TIME_ID INTEGER, , AREA_ID INTEGER, , USR_ID BIGINT, , SMART_CODE VARCHAR20, , SM_CNT INTPARTITIONING KEYPARTITIONING KEYUSR_IDUSR_ID USING HASHING USING HASHINGWITH REPLACE ON COMMIT PRESERVE ROWS NOT LO

28、GGED IN TBS_USR_TMP; ;索引CREATE INDEX SESSION.IDX0803110001 ON SESSION.WJL_TMP_MUSR_ID,SMART_CODE表空間的選擇小表盡量放在單分區(qū)的表空間上，防止查詢時的播送維表、參數(shù)表、日志表、小數(shù)量的數(shù)據(jù)表最好放在單分區(qū)的表空間上主鍵主鍵用整型會極大的進(jìn)步查詢效率，而字符型的比較開銷要比整型的比較開銷大很多，用字符型數(shù)據(jù)作主鍵會使數(shù)據(jù)插入、更新與查詢的效率降低。數(shù)據(jù)量小的時候這點(diǎn)降低可能不會被注意，可是當(dāng)數(shù)據(jù)量大的時候，小的改進(jìn)也可以進(jìn)步系統(tǒng)的響應(yīng)速度。 distinct使用distinct是為了保證在結(jié)果集中不出現(xiàn)重復(fù)值，但是distinct會產(chǎn)生一張工作表，并進(jìn)展排序來刪除重復(fù)記錄，這會大大增加查詢和I/O的操作次數(shù)。因此應(yīng)當(dāng)防止使用distinct關(guān)鍵字。 負(fù)邏輯 負(fù)邏輯如!=、not in等，都會導(dǎo)致DB2用表掃描來完成查詢。當(dāng)表較大時，會嚴(yán)重影響系統(tǒng)性能，可以用別的操作來代替。 選擇數(shù)據(jù)類型對于比較短的列，盡量使用定長的CHAR而不是變長的VARCHAR。雖然，當(dāng)數(shù)據(jù)的長度參差不齊時，VARCHAR可以