《数据仓库SQL优化》由会员分享，可在线阅读，更多相关《数据仓库SQL优化（22页珍藏版）》请在金锄头文库上搜索。
1、数据仓库系统调优课题DB2数据库分区 数据库分区(database partitioning)设计CPU高速内存通道内存环境1Gbit高速网络通道DW1DW2FC高速IO通道DS8100 阵列数据库分区关系数据压缩 基本思路：字典表压缩 Oracle：块级压缩 块：大小介于4KB32KB之间的存储单元 当确定某个表要被压缩后，Oracle在每个数据块中保留空 间，以便储存在该数据块中的多个位置上出现的数据的单 一拷贝 不能够跨块的边界寻找更大的重复模式 只支持在批量加载操作期间进行数据压缩 DB2：表级压缩 需要事先提供样本数据 可在INSERT 操作过程中同时进行压缩DB2行压缩技术（一）l
2、基于压缩字典的压缩方式l通过使用较少的数据库页来表示相同数据,从而达到节省磁盘存储空间 的目的DB2行压缩技术（二）l行压缩的不会明显降低UPDATE的效率l由于数据占用的存储空间少了，所以访问数据所需要的IO也少了，而 IO正是数据仓库系统常见的瓶颈l与行压缩关联的成本取决于压缩和解压缩数据所需的额外 CPU 周期l可使用 REORG TABLE 命令来创建压缩字典在处理 REORG 命令时,现 有的所有表行都要被压缩lReorg完成之后，后续INSERT的数据会自动按照现有的压缩字典来压缩 数据，如果数据业务逻辑发生了大的变更，压缩效果不理想，可以重 新执行reorg操作l分区表是一个分区
3、一个压缩字典，所以分区表新分区的数据在没有 reorg过之前是不会自动压缩的DB2 VS Oraclep实验环境 OS: Windows Server 2003 64-bit Memory: 2G CPU: AMD opteron 865 processor 1.80G（8核） Oracle企业版 10g VS DB2版本：DB2 企业版 9.7 数据集:中国移动GSM语音通话记录（gsm_voic_cdr.dat），替换分隔 符后总大小为4.247G 实验效果77载入时间空间 (M)查询时间更新时间删除时间Oracle未压缩00:13:23497100:01:1100:12:0300:05:
4、13DB2未压缩00:11:42 834900:02:3200:03:1200:07:58 Oracle压缩00:10:14192700:00:4000:04:2200:05:31 DB2压缩00:09:31205000:00:3000:02:5000:03:16o 查询语句 nselect count(*) from GSM where EXCHANGECODE = 8613900121 o 更新语句 nUPDATE GSM SET LACCODE = GSM WHERE DURATION = 100 o 删除语句 nDELETE FROM GSM WHERE DURATION = 100压
5、缩后的数据仅占原来的38.76%（Oracle） 或24.55%（DB2）VARCHAR的结构信息在未压缩的DB2上占 用较多空间压缩后数据增、删、改、查的效率更高移动数据上非常适合压缩索引优化(一) 索引的优点1. 通过创建唯一性索引，可以保证数 据库表中每一行数据的唯一性。 2. 可以大大加快数据的检索速度，这 也是创建索引的最主要的原因。 3. 可以加速表和表之间的连接，特别 是在实现数据的参考完整性方面特别有 意义。 4. 在使用分组和排序子句进行数据检 索时，同样可以显著减少查询中分组和 排序的时间。 5. 通过使用索引，可以在查询的过程 中，使用优化隐藏器，提高系统的性能。 索引的
6、缺点1. 创建索引和维护索引要耗费时间， 这种时间随着数据量的增加而增加。 2. 索引需要占物理空间，除了数据表 占数据空间之外，每一个索引还要占一 定的物理空间。 3.当对表中的数据进行增加、删除和修 改的时候，索引也要动态的维护，这样 就降低了数据的维护速度。 索引优化(二) DB2在用户指定数据表主键时， 自动生成以主键为关键字的聚簇索引。建立其 他索引时有以下策略： ( 1 )避免在小表上建立索引 因为索引的维护需要一定的代价，在表上进行增删改操作时 ，索引需 要重组，这就增加了数据库的负担， 如果对索引的插入或者更新损失的时间 大于在查询中节省下来的时间，那么使用索引就是一个不明智的
7、决定。 ( 2 )在经常进行链接的列上建立索引， 并且字段类型保持一致多表链接查询 是数据库中最复杂、 最耗时的操作之一。改进多表链接查询性能对系统性能 的改进起很大的作用。在链接属性上存在索引时， D B 2中采用索引嵌套循环 链接 ，否则 D B 2使用哈希链接。 ( 3 )在频繁进行 g r o u p b y o r d e r b y的列上建立索引 ( 4 )建立索引字段的列的长度尽量小，避免在 B l o b C l o b类型上 建立索引。索引优化(三) ( 5 )在 S QL语句中频繁进行比较运算的列上建立索引。 ( 6 )避免在选择性太低的字段上建立索引，基数较大的列很适合用
8、来做索引 。选择性太低指的是索引中同一索引值的对应记录太多， 在这种列上建立索 引意义不大。DB 2最优化处理器不会使用该列作为执行计划的一部分 。 ( 7 )建立组合索引需要注意索引列顺序如果在 A， B两列上顺序建立组合索引 以后， 那么在w h e r e 子句中带有下列搜 索条件会使用此索引 ： 条件为 A； 条件为 A。 B 。如果条件仅为 B则不会使用此索引。对于多列索引，将查询中 引用最多的列放在定义的前面。 ( 8 )由少数窄列组成，列宽度较大的列不适合作索引。考虑到管理上的开销 ，应避免在索引中使用多于 5 个的列。 ( 9 )避免添加与已有的索引相似的索引。因为这样会给优化
9、器带来更多的工 作，并且会降慢更新操作的速度。相反，我们应该修改已有的索引，使其包含 附加的列。多维群集（ MDC ）索引(一)l基于块的索引，比常规索引小得多，扫描的时候更快lMDC 对性能的贡献在于提高检索数据的效率lMDC 块索引意味着需要的 RID 索引更少l由于新行是插在表中具有近似值的行附近的位置，因此数据仍然是聚 合的，而不需要运行 REORG 实用程序l由于新行是插在表中具有近似值的行附近的位置，所以MDC表无法创建 群集索引(cluster index)，索引的群集率也比较低，类似serv_id = 1100000000000000 and serv_id 50，优化处理：s
10、s_df30X|hbs_bhX5400021452，优化处理：hbs_bh5400021542p条件内包括了多个本表的字段运算时不能进行索引，如： ys_dfcx_df，无法进行优化qc_bh|kh_bh=5400250000，优化处理：qc_bh=5400 and kh_bh=250000表的三种Join方法(NLJOIN HSJOIN MSJOIN ） 对于被连接的数据子集较小的情况，nested loop连接是个 较好的选择。nested loop就是扫描一个表，每读到一条记 录，就根据索引去另一个表里面查找，没有索引一般就不 会是 nested loops。 Hash join是大数据集连接时的常用方式。优化器扫描小 表（或数据源），利用连接键（也就是根据连接字段计算 hash 值）在内存中建立hash表，然后扫描大表，每读到一 条记录就来探测hash表一次，找出与hash表匹配的行。 对连接的每个表做table access full；对table access full的结果按照连接键进行排序；进行msjoin对排序结果 进行合并。 因为其排序成本高，大多为hash join替代。SQL优化效果 实验环境：处理器 AMD865，主频 1.8GHz，内存 31.8GB 实验数据：每表包 含10万条记录
