《数据仓库SQL优化》PPT课件.ppt

数据仓库系统调优课题 DB2数据库分区 数据库分区 databasepartitioning 设计 关系数据压缩 基本思路 字典表压缩Oracle 块级压缩块 大小介于4KB 32KB之间的存储单元当确定某个表要被压缩后 Oracle在每个数据块中保留空间 以便储存在该数据块中的多个位置上出现的数据的单一拷贝不能够跨块的边界寻找更大的重复模式只支持在批量加载操作期间进行数据压缩DB2 表级压缩需要事先提供样本数据可在INSERT操作过程中同时进行压缩 DB2行压缩技术 一 基于压缩字典的压缩方式通过使用较少的数据库页来表示相同数据 从而达到节省磁盘存储空间的目的 DB2行压缩技术 二 行压缩的不会明显降低UPDATE的效率由于数据占用的存储空间少了 所以访问数据所需要的IO也少了 而IO正是数据仓库系统常见的瓶颈与行压缩关联的成本取决于压缩和解压缩数据所需的额外CPU周期可使用REORGTABLE命令来创建压缩字典 在处理REORG命令时 现有的所有表行都要被压缩 Reorg完成之后 后续INSERT的数据会自动按照现有的压缩字典来压缩数据 如果数据业务逻辑发生了大的变更 压缩效果不理想 可以重新执行reorg操作分区表是一个分区一个压缩字典 所以分区表新分区的数据在没有reorg过之前是不会自动压缩的 DB2VSOracle 实验环境OS WindowsServer200364 bitMemory 2GCPU AMDopteron865processor1 80G 8核 Oracle企业版10gVSDB2版本 DB2企业版9 7数据集 中国移动GSM语音通话记录 gsm voic cdr dat 替换分隔符后总大小为4 247G 实验效果 7 7 查询语句selectcount fromGSMwhereEXCHANGECODE 8613900121 更新语句UPDATEGSMSETLACCODE GSM WHEREDURATION 100删除语句DELETEFROMGSMWHEREDURATION 100 压缩后的数据仅占原来的38 76 Oracle 或24 55 DB2 VARCHAR的结构信息在未压缩的DB2上占用较多空间压缩后数据增 删 改 查的效率更高移动数据上非常适合压缩 索引优化 一 索引的优点 1 通过创建唯一性索引 可以保证数据库表中每一行数据的唯一性 2 可以大大加快数据的检索速度 这也是创建索引的最主要的原因 3 可以加速表和表之间的连接 特别是在实现数据的参考完整性方面特别有意义 4 在使用分组和排序子句进行数据检索时 同样可以显著减少查询中分组和排序的时间 5 通过使用索引 可以在查询的过程中 使用优化隐藏器 提高系统的性能 索引的缺点 1 创建索引和维护索引要耗费时间 这种时间随着数据量的增加而增加 2 索引需要占物理空间 除了数据表占数据空间之外 每一个索引还要占一定的物理空间 3 当对表中的数据进行增加 删除和修改的时候 索引也要动态的维护 这样就降低了数据的维护速度 索引优化 二 DB2在用户指定数据表主键时 自动生成以主键为关键字的聚簇索引 建立其他索引时有以下策略 1 避免在小表上建立索引因为索引的维护需要一定的代价 在表上进行增删改操作时 索引需要重组 这就增加了数据库的负担 如果对索引的插入或者更新损失的时间大于在查询中节省下来的时间 那么使用索引就是一个不明智的决定 2 在经常进行链接的列上建立索引 并且字段类型保持一致多表链接查询是数据库中最复杂 最耗时的操作之一 改进多表链接查询性能对系统性能的改进起很大的作用 在链接属性上存在索引时 DB2中采用索引嵌套循环链接 否则DB2使用哈希链接 3 在频繁进行groupby orderby的列上建立索引 4 建立索引字段的列的长度尽量小 避免在Blob Clob类型上建立索引 索引优化 三 5 在SQL语句中频繁进行比较运算的列上建立索引 6 避免在选择性太低的字段上建立索引 基数较大的列很适合用来做索引 选择性太低指的是索引中同一索引值的对应记录太多 在这种列上建立索引意义不大 DB2最优化处理器不会使用该列作为执行计划的一部分 7 建立组合索引需要注意索引列顺序如果在A B两列上顺序建立组合索引以后 那么在where子句中带有下列搜索条件会使用此索引 条件为A 条件为A B 如果条件仅为B则不会使用此索引 对于多列索引 将查询中引用最多的列放在定义的前面 8 由少数窄列组成 列宽度较大的列不适合作索引 考虑到管理上的开销 应避免在索引中使用多于5个的列 9 避免添加与已有的索引相似的索引 因为这样会给优化器带来更多的工作 并且会降慢更新操作的速度 相反 我们应该修改已有的索引 使其包含附加的列 多维群集 MDC 索引 一 基于块的索引 比常规索引小得多 扫描的时候更快MDC对性能的贡献在于提高检索数据的效率MDC块索引意味着需要的RID索引更少由于新行是插在表中具有近似值的行附近的位置 因此数据仍然是聚合的 而不需要运行REORG实用程序由于新行是插在表中具有近似值的行附近的位置 所以MDC表无法创建群集索引 clusterindex 索引的群集率也比较低 类似serv id 1100000000000000andserv id 1200000000000000的索引访问效率比较低 按照目前仓库的应用现状 清单类的表不适合建MDC要避免mdc字段被频繁的更新 因为引起被更新的行从原来的块迁移到新的块 多维群集 MDC 索引 二 MDC维的定义原则用于范围 等于或IN列表谓词用于转入 转出或其他大规模的行删除粗粒度 也就是说不同的值很少的列MDC维最多可以使用16列的组合典型的应用设计是用一个表示日期的列作为一个MDC维 再加上0到3个其他列作为其他维 例如地域 area 和产品类型 product type 多维群集 MDC 索引 三 CREATETABLE XJDSS LINGUO MD CU COLLIGATE ATTRI DAY 01 STAT DATE DECIMAL 8 0 SERV ID DECIMAL 18 0 ORGANIZEBYDIMENSIONS STAT DATE USER STATUS DATACAPTURENONEIN BAS WH MINE INDEXIN BAS WH MINE PARTITIONINGKEY SERV ID USINGHASHINGCOMPRESSYESVALUECOMPRESSION 系统会自动创建MDC索引 分区表 一 与MDC类似 它也可以将具有近似值的行存储在一起分区表支持按照一个维将一个表分区成多个数据分区通过分区表特性 用户可以手动地定义每个数据分区 包括将被包括到那个分区的值的范围每个分区表分区是一个单独的数据库对象因此 分区表支持为分区表附加和卸除数据分区 卸除的分区成为一个常规表 而且 必要时可以将每个数据分区放在它自己的表空间中 在DROP分区表的时候是一个分区一个分区地卸载 分区多的情况下比常规表慢很多分区表通过分区排除提高数据检索性能分区字段同样不允许UPDATE操作 分区表 二 分区表的每个表分区进行reorg操作 但是要把该分区的数据卸载 detach 到小表 然后再安装 attach 上去RANGE分区 未指定的分区值不能INSERT到数据库中添加分区操作ALTERTABLExjdss linguo md cu user day 03ADDPARTITIONSTAT DATE20080601STARTINGFROM20080601INCLUSIVEENDINGAT20080602 添加带数据分区的操作ALTERTABLExjdss linguo md cu user day 03ATTACHPARTITIONSTAT DATE20080601STARTINGFROM20080601INCLUSIVEENDINGAT20080602EXCLUSIVEfromXJDSS MD CU USER DAY20080601 卸载分区 可以用户清除数据 ALTERTABLEXJDSS LINGUO MD CU USER DAY 03DETACHPARTITIONSTAT DATE20080601INTOXJDSS MD CU USER DAY20080601 分区表 三 卸载分区 可以用户清除数据 ALTERTABLEXJDSS LINGUO MD CU USER DAY 03DETACHPARTITIONSTAT DATE20080601INTOXJDSS MD CU USER DAY20080601 分区转入后该分区的状态不正常可以同构完整性检查来回复SETINTEGRITYFORxjdss linguo md cu user day 03ALLOWWRITEACCESSIMMEDIATECHECKEDFOREXCEPTIONINxjdss linguo md cu user day 03USEXJDSS MD CU USER DAY tmpSETINTEGRITYFORxjdss linguo md cu user day 03ALLOWWRITEACCESSIMMEDIATECHECKED 分区表结构在DDL中看不出来 可以从系统字典表中看出来select fromsyscat datapartitionswithur 分区表 四 建表语句 推荐 CREATETABLEXJDSS LINGUO MD CU COLLIGATE ATTRI DAY 01 STAT DATE DECIMAL 8 0 SERV ID DECIMAL 18 0 MSISDN VARCHAR 15 DATACAPTURENONEIN BAS WH MID01 INDEXIN BAS WH MID01 IDX PARTITIONINGKEY SERV ID USINGHASHINGpartitionbyrange stat date PARTITIONSTAT DATE20081001STARTINGFROM20081001INCLUSIVEENDINGAT20081002EXCLUSIVE PARTITIONSTAT DATE20081002STARTINGFROM20081002INCLUSIVEENDINGAT20081003EXCLUSIVE COMPRESSYESVALUECOMPRESSION SQL优化 一 18 针对5月6日运行时间最长的100个脚本 共198个文件夹 每个文件夹中包含1 6个Perl程序 SQL语句嵌入到Perl程序中对数据库进行操作 现状1 插入语句嵌入到循环中 每次循环插入一条语句 然后断开与数据库的联系 进行非数据库操作 改进建议1 在每次与数据库的连接中 尽可能多地执行插入操作 减少与数据库连接的次数 现状2 不少插入语句和文件操作 或者插入语句和字符串操作交替运行INSERTINTO PDATADB table target TRG COL LIST SELECT TRG COL LISTFROM table today 交替着 UNIT DATTM substr DATA FNAME 8 8 改进建议2 在插入数据的过程中 尽可能在下一次插入语句之前不要进行其它文件I O或字符处理 全部集中最后一同处理 SQL优化 二 19 现状3 DELETEFROM PDATADB table targetWHERE FILTERINSERTINTO PDATADB table target TRG COL LIST 改进建议3 使用UPDATE代替DELETE和INSERT组合 减少数据库I O 现状4 WHERE子句中 直接在列名上使用函数 无法利用索引wherefunction colName operatorValue 改进建议4 函数转移到值上wherecolNameoperatorfunction Value 现状5 查询语句的WHERE子句中 直接在没有索引的列名上使用函数char trim tablename char trim tb 10400 04002 s 20041007 改进建议5 进行语句的等价转换 提高效率 tablenamelike tb 10400 04002 s 20041007 现状6 统计表中记录的个数 大部分情况使用了selectcount 改进建议6 进行语句的等价转换 尽量利用索引进行统计 SELECTrowsFROMsysindexesWHEREid OBJECT ID table name ANDindid 2 SQL语句索引的利用 采用函数处理的字段不能利用索引 如 substr hbs bh 1 4 5400 优化处理 hbs bhlike 5400 进行了显式或隐式的运算的字段不能进行索引 如 ss df 20 50 优化处理 ss df 30 X hbs bh X5400021452 优化处理 hbs bh 5400021542 条件内包括了多个本表的字段运算时不能进行索引 如 ys df cx df 无法进行优化qc bh kh bh 5400250000 优化处理 qc bh 5400 andkh bh 250000 表的三种Join方法 NLJOINHSJOINMSJOIN 对于被连接的数据子集较小的情况 nestedloop连接是个较好的选择 nestedloop就是扫描一个表 每读到一条记录