性能調(diào)優(yōu)-課件第8周

DB2設(shè)計(jì)與性能優(yōu) 講師

【】和幻燈片為煉數(shù)成金網(wǎng)絡(luò)課程的教程以外范圍散播，違者將可能被法律和經(jīng)濟(jì)課程詳情煉數(shù)成金培訓(xùn) DB2設(shè)計(jì)與性能優(yōu) 講

提 MDCDB2設(shè)計(jì)與性能優(yōu) 講師

DB2何時(shí)使用全表掃表的大部分?jǐn)?shù)據(jù)行都必須 才能獲查詢(xún)結(jié)表很索引不存

Originalselectacct_id,namefromacctwherebalance>AccessTotalQuery |TABLE:

1DB2設(shè)計(jì)與性能優(yōu) 講

如何避免全表掃描的I/O操的緩沖池空掃描全部行需 的合適的索范圍定界謂詞（Range-完全索引掃描(Index-OnlyAccess支持雙向掃描的索MQT（物化視圖DB2設(shè)計(jì)與性能優(yōu) 講

利用索引提高性能避免不必要的全表掃避免排加速頻繁執(zhí)行的查內(nèi)表上創(chuàng)建的索引能加速多表連減少鎖等待和死

DB2設(shè)計(jì)與性能優(yōu) 講

使用索引提高性能使用EXPLAIN工具分析SQL的執(zhí)行計(jì)劃增加編譯或者準(zhǔn)備SQL實(shí)用工具的代價(jià)(RUNSTATS,REORG,DB2設(shè)計(jì)與性能優(yōu) 講

提 MDCDB2設(shè)計(jì)與性能優(yōu) 講師

索引 結(jié)構(gòu)-B+ (‘P’ (‘E’,(‘E’,rid)(‘F’,(‘G’,rid)(‘I’,rid)(‘N’,rid1,

(‘N’,rid3,rid4)(‘P’,

(‘T’(‘T’)‘T’‘T’‘I’‘P’‘N’‘E’‘N’‘N’,‘N’‘E’‘N’‘N’,“Q’‘G’DB2設(shè)計(jì)與性能優(yōu) 講

謂詞處理的方Residual謂

SalarySalary>ALLSearch(SARGs)謂

NameLIKEBuffer

Rang-delimiting謂詞IndexStart/StopIndexSargable謂詞 可用葉子節(jié)點(diǎn)中的數(shù)據(jù)計(jì)

DB2設(shè)計(jì)與性能優(yōu) 講WHERE子句IndexRange-delimitingRange-delimiting謂Index-sargable謂LikeDataLikeSargableSargable謂關(guān)聯(lián)子查量關(guān)聯(lián)子查量化子查L(zhǎng)ongvarchar等數(shù)據(jù)類(lèi)ResidualResidual謂 DB2設(shè)計(jì)與性能優(yōu) 講 ListPrefetch,IndexANDing和Index

(

隱含的IXOR

(

(|

TABLE:

(

TABLE:(|(

TABLE:

((

顯式的

((|

((|(INDEX:

(|INDEX:UDBP

(|INDEX:UDBP

INDEX:UDBP

INDEX:UDBPDB2設(shè)計(jì)與性能優(yōu) 講

列表預(yù)取(ListPrefetch)是如何工作311423331221242211341332

WHERENAME<1214233133

12123456789

123 的挖掘 （nonclustered）索引的好避免對(duì)同一個(gè)數(shù)據(jù)頁(yè)多次DB2設(shè)計(jì)與性能優(yōu) 講Explain詳細(xì)信息中的順序預(yù)IXSCAN:(IndexMAXPAGES: umpagesPREFETCH:(TypeofPrefetch)ROWLOCK:(RowLockSCANDIR:(ScanDirection)TABLOCK:(TableLockINTENT

TBSCAN:(TableMAXPAGES: umpagesPREFETCH:(TypeofPrefetch)ROWLOCK:(RowLockintent)NEXTKEYSHARESCANDIR:(ScanTABLOCK:(TableLockINTENTDB2設(shè)計(jì)與性能優(yōu) 講

索引或操作(Index* ==ABA'David'ABA'David'CBB C C ..DB2設(shè)計(jì)與性能優(yōu) 講..

B分專(zhuān)數(shù) 分專(zhuān)數(shù) 提 MDCDB2設(shè)計(jì)與性能優(yōu) 講師

完全索引掃描(IndexOnlyAccess)和包含列(INCLUDEColumns)CREATECREATEUNIQUEINDEXXACCTONACCT(ACCT_GRPASC,ACCT_IDDESC)INCLUDE(ACCT_NAME,ADDRESS)CREATEINDEX命令允許在唯一索引的鍵后面加上額外的數(shù)據(jù)這些列作為索引的一部分，但不是鍵的組一些查詢(xún)只 這樣的索引而避 基本表，從而獲得更好的性比如：SELECTacct_name,addressFROMacctWHEREacct_grpANDacct_id=?注意：當(dāng)需要更新這些包含列時(shí)，會(huì)給索引 帶來(lái)額外的開(kāi)DB2設(shè)計(jì)與性能優(yōu) 講

雙向索CREATEINDEX...ALLOWREVERSEDB2設(shè)計(jì)與性能優(yōu) 講

索引的空閑空間(FreeCREATEINDEXL_PKONLINEITEM(L_PARTKEY)PCTFREE40Values->Values->RIDValues->RIDValues->RIDValues->Values->FreePCTFREE(4040%空閑

Index

這些空間 索引或者REORG時(shí)使默認(rèn)設(shè)置為DB2設(shè)計(jì)與性能優(yōu) 講

索引 索CREATEINDEXIX1DB2設(shè)計(jì)與性能優(yōu) 講

CREATEINDEXIX2(product_id)索 DB2在輸入新的記錄時(shí)盡 這利用表的PCTFREE設(shè)置，可以為建 索引的表的數(shù) 預(yù)留空這可以在CREATETABLE指定或ALTERTABLECREATEINDEXCL_INXONLINEITEM(L_SHIPDATE)ALTERTABLELINEITEMPCTFREEDB2設(shè)計(jì)與性能優(yōu) 講

SELECTLASTNAME=SELECTLASTNAMEFIRSTNAME=SELECTLASTNAME='Free'FIRSTNAME>SELECTLASTNAME='Free'ANDAGE>38SELECTWHEREGROUPBY--

ORDERBY 索引的統(tǒng)計(jì)信索引的統(tǒng)計(jì)信息或db2RUNSTATSONTABLEUSERID.ACCTANDDETAILEDINDEXESALL或db2RUNSTATSONTABLEANDSAMPLEDDETAILEDINDEXESDB2設(shè)計(jì)與性能優(yōu) 講

索引的額外開(kāi)對(duì) OAD等操作增加了額外的CPU和IO開(kāi) 選擇，會(huì)增加查詢(xún)的編譯時(shí)DB2設(shè)計(jì)與性能優(yōu) 講

索引的最佳實(shí)對(duì)所有的主鍵PK和絕大多少外鍵FK建立索引。因?yàn)榇蟛糠諮OIN操作都發(fā)生在主鍵和外鍵 的列建立索引，但是<>這種謂詞除外。也就是說(shuō)范圍謂詞和 DB2設(shè)計(jì)與性能優(yōu) 講

提MDCDB2設(shè)計(jì)與性能優(yōu) 講師

設(shè)計(jì)向

ControlCenterDesignControlCenterDesign

集群表DB2設(shè)計(jì)與性能優(yōu) 講使用Design db2advis–dsample–mMICP–i- M- I-IndexesC-MDCtablesP-Workloadtypekeyword:(choose

SingleSQLSQLfrominputSQLfromQueryPatrollerSQLfromADVISE_WORKLOADtablebyworkloadnameGetworkloadfromdynamicSQLsnapshot - numberofMBavailableforindexesandMQTs(-1for - specifies in DB2設(shè)計(jì)與性能優(yōu) 講利用db2advis推薦索db2advis-dtpcds-icost.sql-disklimit2-onewindex.ddl>executionstartedattimestamp2012-05-17-11.54.08.236000found[3]SQLstatementsfromtheinputfilemendingtotaldiskspaceneededforinitialset 0.817]totaldiskspaceconstrainedtoTryingvariationsofthesolutionset.Optimizationfinished.2indexesincurrent

2.000][1118.4197]timerons(without [362.6014]timerons(withcurrentsolution)[67.58%]improvement--LIST MENDEDCREATEINDEXON"TPCD ("L_RETURNFLAG"ASC,"L_DISCOUNT"ASC,"L_EXTENDEDPRICE"ASC,"L_ORDERKEY"ALLOWREVERSESCANSRUNSTATSONTABLE"TPCD FORINDEX"DB2ADMIN"."IDX509062043470000";CREATEUNIQUEINDEXON ("O_ORDERDATE"ASC,"O_ORDERKEY"ASC,"O_CUSTKEY"ASC)ALLOWREVERSESCANS;RUNSTATSONTABLE FORINDEX“DB2ADMIN”.“IDX509062044160000”DB2設(shè)計(jì)與性能優(yōu) 講設(shè)計(jì)向?qū)У膱D形化界DB2設(shè)計(jì)與性能優(yōu) 講DB2設(shè)計(jì)與性能優(yōu) 講DB2設(shè)計(jì)與性能優(yōu) 講DB2設(shè)計(jì)與性能優(yōu) 講DB2設(shè)計(jì)與性能優(yōu) 講 提 MDCDB2設(shè)計(jì)與性能優(yōu) 講師

表每一個(gè)維度就是一個(gè)坐標(biāo)軸，MDC示例:3維MDC–nation,colour和

DB2設(shè)計(jì)與性能優(yōu) 講

MDC與塊索

CREATETABLEMDCTABLEYEARINT,STATECHAR(2),SALESINT,ORGANIZEBY(YEAR,onYEARDB2設(shè)計(jì)與性能優(yōu) 講

0-0-,4,8-12-,700-444-704-區(qū)

onRowIndexes-1indexentryperrowBlockIndexes1indexentryperbloc=Ro

=Extent(block)DB2設(shè)計(jì)與性能優(yōu) 講

索引的區(qū)

減少但不能消除reorg的需求(非強(qiáng)制的 數(shù)據(jù)在多個(gè)維度 數(shù)據(jù)在一個(gè)維塊索引(每維度一個(gè)塊索引 行索引(每個(gè)表只能有一 索引Re

Reorg基 索引來(lái)排序表中數(shù)據(jù)行的物為避免大量不必要的空間分配，維度的該適度粗（維度列的基數(shù)較低

列的粒度比MDC如果選擇了不合適的維度，可能增加表在磁盤(pán)不影響表在磁盤(pán)上的大小，除非你為 上的占用空

增大DB2設(shè)計(jì)與性能優(yōu) 講維度選擇的考在設(shè)計(jì)MDC表選擇維度列，需要考慮以下幾點(diǎn)那些查詢(xún)能受益于塊事實(shí)表（facttable）塊單元格的密可能的單元格數(shù)據(jù)=維度列 乘 很少的幾行數(shù)可以操作的三個(gè)方利用生成列將某個(gè)維度提升到更高的粒度，如DB2設(shè)計(jì)與性能優(yōu) 講

基于生成列的MDC維CREATETABLE(DateNationColorMonthgeneratedalwaysas...ORGANIZEBY(Month,ColorDB2設(shè)計(jì)與性能優(yōu) 講

MDC表的數(shù)據(jù)轉(zhuǎn)MDCLoad 迅速找到空閑DB2設(shè)計(jì)與性能優(yōu) 講

MDC設(shè)計(jì)最佳實(shí)調(diào)整維度的數(shù)調(diào)整某維度的粒度(利用生成列調(diào)整塊(區(qū)間extent)的大小以及頁(yè)大利用設(shè)計(jì)向?qū)?lái)推薦可能的MDC設(shè)計(jì)向?qū)У膱D形化界面（控制中心-db2advis-dmdb-iworkload.sql-mC–oDB2設(shè)計(jì)與性能優(yōu) 講

MDC設(shè)計(jì)向?qū)?1ofexecutionstartedattimestamp2005-09-26-found[4]SQLstatementsfromtheinputmendingMulti-Dimensionaltotaldiskspaceneededforinitialsettotaldiskspaceconstrained

0.035]5.855]Note:MDCselectionintheDB2DesignAdvisorrequiresthe tobepopulatedwithadatasample.ThissampleisusedforestimatingthenumberanddensityofMDCcellsinanyMDCsolutionthattheDesignAdvisorwill mend.IfyourdatabaseisemptytheDesignAdvisorwill mendPrioritizingMulti-dimensionalClusteringcandidateMulti-dimensionalClusteringcandidatetables,inpriorityTable0:numberofpagesblocksizeThereare1candidatetablesconsideredforMulti-dimensionalClusteringconversionSearchingthemultidimensionalspaceforsolutionsforHIST1...Percentageofsearchpoints 2clusteringdimensionsincurrent[8665.0000]timerons(without DB2設(shè)計(jì)與性能優(yōu) 講MDC設(shè)計(jì)向?qū)ISTOFMODIFIEDCREATE-TABLESTATEMENTS PARTITIONINGKEYSANDTABLESPACESAND/OR MULTI-DIMENSIONALCLUSTERINGS----CREATETABLE "."HI