数据仓库SQL优化.ppt

数据仓库系统调优课题 DB2数据库分区 数据库分区(database partitioning)设计 CPU 高速内存 通道 内存环境 1Gbit高速网络通道 DW1 DW2 FC高速 IO通道 DS8100 阵列 数据库 分区 关系数据压缩 基本思路：字典表压缩 Oracle：块级压缩 块：大小介于4KB~32KB之间的存储单元 当确定某个表要被压缩后，Oracle在每个数据块中保留空间，以便储存在该数据块中的多个位置上出现的数据的单一拷贝 不能够跨块的边界寻找更大的重复模式 只支持在批量加载操作期间进行数据压缩 DB2：表级压缩 需要事先提供样本数据 可在INSERT 操作过程中同时进行压缩 DB2行压缩技术（一） 基于压缩字典的压缩方式 通过使用较少的数据库页来表示相同数据,从而达到节省磁盘存储空间的目的 DB2行压缩技术（二） 行压缩的不会明显降低UPDATE的效率 由于数据占用的存储空间少了，所以访问数据所需要的IO也少了，而IO正是数据仓库系统常见的瓶颈 与行压缩关联的成本取决于压缩和解压缩数据所需的额外 CPU 周期 可使用 REORG TABLE 命令来创建压缩字典?在处理 REORG 命令时,现有的所有表行都要被压缩? Reorg完成之后，后续INSERT的数据会自动按照现有的压缩字典来压缩数据，如果数据业务逻辑发生了大的变更，压缩效果不理想，可以重新执行reorg操作 分区表是一个分区一个压缩字典，所以分区表新分区的数据在没有reorg过之前是不会自动压缩的 DB2 VS Oracle 实验环境 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 实验效果 * * 载入时间 空间(M) 查询时间 更新时间 删除时间 Oracle未压缩 00:13:23 4971 00:01:11 00:12:03 00:05:13 DB2未压缩 00:11:42 8349 00:02:32 00:03:12 00:07:58 Oracle压缩 00:10:14 1927 00:00:40 00:04:22 00:05:31 DB2压缩 00:09:31 2050 00:00:30 00:02:50 00:03:16 查询语句 select count(*) from GSM where EXCHANGECODE = 8613900121‘ 更新语句 UPDATE GSM SET LACCODE = ‘GSM’ WHERE DURATION = 100 删除语句 DELETE FROM GSM WHERE DURATION = 100 压缩后的数据仅占原来的38.76%（Oracle）或24.55%（DB2） VARCHAR的结构信息在未压缩的DB2上占用较多空间 压缩后数据增、删、改、查的效率更高 移动数据上非常适合压缩 索引优化(一) 索引的优点 1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 2. 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 索引的缺点 1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。  2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。  3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 索引优化(二) DB2在用户指定数据表主键时， 自动生成以主键为关键字的聚簇索引。建立其他索引时有以下策略： ( 1 )避免在小表上建立索引 因为索引的维护需要一定的代价，在表上进行增删改操作时 ，索引需要重组，这就增加了数据库的负担， 如果对索引的插入或者更新损失的时间大于在查询中节省下来的时间，那么使用索引就是一个不明智的决定。 ( 2 )在经常进行链接的列上建立索引， 并且字段类型保持一致多表链接查询是数据库中最复杂、 最耗时的操作之一。改进多表链接查询性能对系统性能的改进起很大的作用。在链接属性上存在索引时， D B 2中采用索引嵌套循环链接 ，否则 D B 2使用哈希链接。 ( 3 )在频繁进行