性能调优-课件第8周

DB2设计与性能优 讲师

【】和幻灯片为炼数成金网络课程的教程以外范围散播，违者将可能被法律和经济课程详情炼数成金培训 DB2设计与性能优 讲

提 MDCDB2设计与性能优 讲师

DB2何时使用全表扫表的大部分数据行都必须 才能获查询结表很索引不存

Originalselectacct_id,namefromacctwherebalance>AccessTotalQuery |TABLE:

1DB2设计与性能优 讲

如何避免全表扫描的I/O操的缓冲池空扫描全部行需 的合适的索范围定界谓词（Range-完全索引扫描(Index-OnlyAccess支持双向扫描的索MQT（物化视图DB2设计与性能优 讲

利用索引提高性能避免不必要的全表扫避免排加速频繁执行的查内表上创建的索引能加速多表连减少锁等待和死

DB2设计与性能优 讲

使用索引提高性能使用EXPLAIN工具分析SQL的执行计划增加编译或者准备SQL实用工具的代价(RUNSTATS,REORG,DB2设计与性能优 讲

提 MDCDB2设计与性能优 讲师

索引 结构-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设计与性能优 讲

谓词处理的方Residual谓

SalarySalary>ALLSearch(SARGs)谓

NameLIKEBuffer

Rang-delimiting谓词IndexStart/StopIndexSargable谓词 可用叶子节点中的数据计

DB2设计与性能优 讲WHERE子句IndexRange-delimitingRange-delimiting谓Index-sargable谓LikeDataLikeSargableSargable谓关联子查量关联子查量化子查Longvarchar等数据类ResidualResidual谓 DB2设计与性能优 讲 ListPrefetch,IndexANDing和Index

(

隐含的IXOR

(

(|

TABLE:

(

TABLE:(|(

TABLE:

((

显式的

((|

((|(INDEX:

(|INDEX:UDBP

(|INDEX:UDBP

INDEX:UDBP

INDEX:UDBPDB2设计与性能优 讲

列表预取(ListPrefetch)是如何工作311423331221242211341332

WHERENAME<1214233133

12123456789

123 的挖掘 （nonclustered）索引的好避免对同一个数据页多次DB2设计与性能优 讲Explain详细信息中的顺序预IXSCAN:(IndexMAXPAGES: umpagesPREFETCH:(TypeofPrefetch)ROWLOCK:(RowLockSCANDIR:(ScanDirection)TABLOCK:(TableLockINTENT

TBSCAN:(TableMAXPAGES: umpagesPREFETCH:(TypeofPrefetch)ROWLOCK:(RowLockintent)NEXTKEYSHARESCANDIR:(ScanTABLOCK:(TableLockINTENTDB2设计与性能优 讲

索引或操作(Index* ==ABA'David'ABA'David'CBB C C ..DB2设计与性能优 讲..

B分专数 分专数 提 MDCDB2设计与性能优 讲师

完全索引扫描(IndexOnlyAccess)和包含列(INCLUDEColumns)CREATECREATEUNIQUEINDEXXACCTONACCT(ACCT_GRPASC,ACCT_IDDESC)INCLUDE(ACCT_NAME,ADDRESS)CREATEINDEX命令允许在唯一索引的键后面加上额外的数据这些列作为索引的一部分，但不是键的组一些查询只 这样的索引而避 基本表，从而获得更好的性比如：SELECTacct_name,addressFROMacctWHEREacct_grpANDacct_id=?注意：当需要更新这些包含列时，会给索引 带来额外的开DB2设计与性能优 讲

双向索CREATEINDEX...ALLOWREVERSEDB2设计与性能优 讲

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

Index

这些空间 索引或者REORG时使默认设置为DB2设计与性能优 讲

索引 索CREATEINDEXIX1DB2设计与性能优 讲

CREATEINDEXIX2(product_id)索 DB2在输入新的记录时尽 这利用表的PCTFREE设置，可以为建 索引的表的数 预留空这可以在CREATETABLE指定或ALTERTABLECREATEINDEXCL_INXONLINEITEM(L_SHIPDATE)ALTERTABLELINEITEMPCTFREEDB2设计与性能优 讲

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

ORDERBY 索引的统计信索引的统计信息或db2RUNSTATSONTABLEUSERID.ACCTANDDETAILEDINDEXESALL或db2RUNSTATSONTABLEANDSAMPLEDDETAILEDINDEXESDB2设计与性能优 讲

索引的额外开对 OAD等操作增加了额外的CPU和IO开 选择，会增加查询的编译时DB2设计与性能优 讲

索引的最佳实对所有的主键PK和绝大多少外键FK建立索引。因为大部分JOIN操作都发生在主键和外键 的列建立索引，但是<>这种谓词除外。也就是说范围谓词和 DB2设计与性能优 讲

提MDCDB2设计与性能优 讲师

设计向

ControlCenterDesignControlCenterDesign

集群表DB2设计与性能优 讲使用Design db2advis–dsample–mMICP–i- M- I-IndexesC-MDCtablesP-Workloadtypekeyword:(choose

SingleSQLSQLfrominputSQLfromQueryPatrollerSQLfromADVISE_WORKLOADtablebyworkloadnameGetworkloadfromdynamicSQLsnapshot - numberofMBavailableforindexesandMQTs(-1for - specifies in DB2设计与性能优 讲利用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设计与性能优 讲设计向导的图形化界DB2设计与性能优 讲DB2设计与性能优 讲DB2设计与性能优 讲DB2设计与性能优 讲DB2设计与性能优 讲 提 MDCDB2设计与性能优 讲师

表每一个维度就是一个坐标轴，MDC示例:3维MDC–nation,colour和

DB2设计与性能优 讲

MDC与块索

CREATETABLEMDCTABLEYEARINT,STATECHAR(2),SALESINT,ORGANIZEBY(YEAR,onYEARDB2设计与性能优 讲

0-0-,4,8-12-,700-444-704-区

onRowIndexes-1indexentryperrowBlockIndexes1indexentryperbloc=Ro

=Extent(block)DB2设计与性能优 讲

索引的区

减少但不能消除reorg的需求(非强制的 数据在多个维度 数据在一个维块索引(每维度一个块索引 行索引(每个表只能有一 索引Re

Reorg基 索引来排序表中数据行的物为避免大量不必要的空间分配，维度的该适度粗（维度列的基数较低

列的粒度比MDC如果选择了不合适的维度，可能增加表在磁盘不影响表在磁盘上的大小，除非你为 上的占用空

增大DB2设计与性能优 讲维度选择的考在设计MDC表选择维度列，需要考虑以下几点那些查询能受益于块事实表（facttable）块单元格的密可能的单元格数据=维度列 乘 很少的几行数可以操作的三个方利用生成列将某个维度提升到更高的粒度，如DB2设计与性能优 讲

基于生成列的MDC维CREATETABLE(DateNationColorMonthgeneratedalwaysas...ORGANIZEBY(Month,ColorDB2设计与性能优 讲

MDC表的数据转MDCLoad 迅速找到空闲DB2设计与性能优 讲

MDC设计最佳实调整维度的数调整某维度的粒度(利用生成列调整块(区间extent)的大小以及页大利用设计向导来推荐可能的MDC设计向导的图形化界面（控制中心-db2advis-dmdb-iworkload.sql-mC–oDB2设计与性能优 讲

MDC设计向导(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设计与性能优 讲MDC设计向导LISTOFMODIFIEDCREATE-TABLESTATEMENTS PARTITIONINGKEYSANDTABLESPACESAND/OR MULTI-DIMENSIONALCLUSTERINGS----CREATETABLE "."HI