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

数据仓库系统调优课题,DB2数据库分区,数据库分区(database partitioning)设计,基本思路：字典表压缩 Oracle：块级压缩 块：大小介于4KB32KB之间的存储单元 当确定某个表要被压缩后，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,实验效果,7,7,查询语句 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 )在频繁进行 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 )避免在选择性太低的字段上建立索引，基数较大的列很适合用来做索引。选择性太低指的是索引中同一索引值的对应记录太多， 在这种列上建立索引意义不大。DB 2最优化处理器不会使用该列作为执行计划的一部分 。 ( 7 )建立组合索引需要注意索引列顺序如果在 A， B两列上顺序建立组合索引以后， 那么在w h e r e 子句中带有下列搜 索条件会使用此索引 ： 条件为 A； 条件为 A。 B 。如果条件仅为 B则不会使用此索引。对于多列索引，将查询中引用最多的列放在定义的前面。 ( 8 )由少数窄列组成，列宽度较大的列不适合作索引。考虑到管理上的开销，应避免在索引中使用多于 5 个的列。 ( 9 )避免添加与已有的索引相似的索引。因为这样会给优化器带来更多的工作，并且会降慢更新操作的速度。相反，我们应该修改已有的索引，使其包含附加的列。,多维群集（ MDC ）索引(一),基于块的索引，比常规索引小得多，扫描的时候更快 MDC 对性能的贡献在于提高检索数据的效率 MDC 块索引意味着需要的 RID 索引更少 由于新行是插在表中具有近似值的行附近的位置，因此数据仍然是聚合的，而不需要运行 REORG 实用程序 由于新行是插在表中具有近似值的行附近的位置，所以MDC表无法创建群集索引(cluster index)，索引的群集率也比较低，类似serv_id = 1100000000000000 and serv_id 1200000000000000 的索引访问效率比较低。按照目前仓库的应用现状，清单类的表不适合建MDC 要避免mdc字段被频繁的更新，因为引起被更新的行从原来的块迁移到新的块,多维群集（ MDC ）索引(二),MDC维的定义原则 用于范围、等于或 IN 列表谓词 用于转入、转出或其他大规模的行删除 粗粒度，也就是说不同的值很少的列 MDC维最多可以使用16列的组合 典型的应用设计是用一个表示日期的列作为一个 MDC 维，再加上 0 到 3 个其他列作为其他维，例如 地域(area) 和 产品类型(product_type),多维群集（ MDC ）索引(三),CREATE TABLE “XJDSS“.“LINGUO_MD_CU_COLLIGATE_ATTRI_DAY_01“ (“STAT_DATE“ DECIMAL(8, 0), “SERV_ID“ DECIMAL(18, 0), ) ORGANIZE BY DIMENSIONS (STAT_DATE,USER_STATUS) DATA CAPTURE NONE IN “BAS_WH_MINE“ INDEX IN “BAS_WH_MINE“ PARTITIONING KEY (SERV_ID ) USING HASHING COMPRESS YES VALUE COMPRESSION; 系统会自动创建MDC索引,分区表（一）,与 MDC 类似，它也可以将具有近似值的行存储在一起 分区表支持按照一个维将一个表分区成多个数据分区 通过分区表特性，用户可以手动地定义每个数据分区，包括将被包括到那个分区的值的范围 每个分区表分区是一个单独的数据库对象 因此，分区表支持为分区表附加和卸除数据分区。卸除的分区成为一个常规表。而且，必要时可以将每个数据分区放在它自己的表空间中。 在DROP分区表的时候是一个分区一个分区地卸载，分区多的情况下比常规表慢很多 分区表通过分区排除提高数据检索性能 分区字段同样不允许UPDATE操作,分区表（二）,分区表的每个表分区进行reorg操作，但是要把该分区的数据卸载(detach)到小表，然后再安装(attach)上去 RANGE分区，未指定的分区值不能INSERT到数据库中 添加分区操作ALTER TABLE xjdss.linguo_md_cu_user_day_03 ADD PARTITION STAT_DATE20080601 STARTING FROM 20080601 INCLUSIVE ENDING AT 20080602; 添加带数据分区的操作 ALTER TABLE xjdss.linguo_md_cu_user_day_03 ATTACH PARTITION STAT_DATE20080601 STARTING FROM 20080601 INCLUSIVE ENDING AT 20080602 EXCLUSIVE from XJDSS.MD_CU_USER_DAY20080601; 卸载分区(可以用户清除数据) ALTER TABLE XJDSS.LINGUO_MD_CU_USER_DAY_03 DETACH PARTITION STAT_DATE20080601 INTO XJDSS.MD_CU_USER_DAY20080601 ;,分区表（三）,卸载分区(可以用户清除数据) ALTER TABLE XJDSS.LINGUO_MD_CU_USER_DAY_03 DETACH PARTITION STAT_DATE20080601 INTO XJDSS.MD_CU_USER_DAY20080601 ; 分区转入后该分区的状态不正常 可以同构完整性检查来回复 SET INTEGRITY FOR xjdss.linguo_md_cu_user_day_03 ALLOW WRITE ACCESS IMMEDIATE CHECKED FOR EXCEPTION IN xjdss.linguo_md_cu_user_day_03 USE XJDSS.MD_CU_USER_DAY_tmp SET INTEGRITY FOR xjdss.linguo_md_cu_user_day_03 ALLOW WRITE ACCESS IMMEDIATE CHECKED; 分区表结构在DDL中看不出来，可以从系统字典表中看出来 select * from syscat.datapartitions with ur,分区表（四）,建表语句(推荐) CREATE TABLE XJDSS.LINGUO_MD_CU_COLLIGATE_ATTRI_DAY_01 (“STAT_DATE“ DECIMAL(8, 0), “SERV_ID“ DECIMAL(18, 0), “MSISDN“ VARCHAR(15) ) DATA CAPTURE NONE IN “BAS_WH_MID01“ INDEX IN “BAS_WH_MID01_IDX“ PARTITIONING KEY (SERV_ID ) USING HASHING partition by range (stat_date) ( PARTITION STAT_DATE20081001 STARTING FROM 20081001 INCLUSIVE ENDING AT 20081002 EXCLUSIVE ,PARTITION STAT_DATE20081002 STARTING FROM 20081002 INCLUSIVE ENDING AT 20081003 EXCLUSIVE ) COMPRESS YES VALUE COMPRESSION;,SQL优化(一),18,针对5月6日运行时间最长的100个脚本：共198个文件夹，每个文件夹中包含16个Perl程序，SQL语句嵌入到Perl程序中对数据库进行操作,现状1：插入语句嵌入到循环中，每次循环插入一条语句，然后断开与数据库的联系，进行非数据库操作,改进建议1：在每次与数据库的连接中，尽可能多地执行插入操作，减少与数据库连接的次数,现状2：不少插入语句和文件操作，或者插入语句和字符串操作交替运行 INSERT INTO $PDATADB.$table_target($TRG_COL_LIST) SELECT $TRG_COL_LIST FROM $table_today ; 交替着：$UNIT_DATTM = substr($DATA_FNAME,8,8);,改进建议2：在插入数据的过程中，尽可能在下一次插入语句之前不要进行其它文件I/O 或字符处理，全部集中最后一同处理,SQL优化(二),19,现状3：DELETE FROM $PDATADB.$table_target WHERE $FILTER INSERT INTO $PDATADB.$table_target($TRG_COL_LIST),改进建议3：使用UPDATE代替DELETE和INSERT组合 ，减少数据库I/O,现状4：WHERE子句中，直接在列名上使用函数，无法利用索引 where function(colName) operator Value ；,改进建议4：函数转移到值上 where colName operator function(Value),现状5：查询语句的WHERE子句中，直接在没有索引的列名上使用函数 char(trim(tablename)=char(trim(tb_10400_04002_s_20041007),改进建议5：进行语句的等价转换，提高效率： tablename like % tb_10400_04002_s_20041007% ,现状6：统计表中记录的个数，大部分情况使用了 select count(*),改进建议6：进行语句的等价转换，尽量利用索引进行统计：SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table_name) AND indid 2,SQL语句索引的利用,采用函数处理的字段不能利用索引，如： substr(hbs_bh,1,4)=5400，优化处理：hbs_bh like 5400% 进行了显式或隐式的运算的字段不能进行索引，如： ss_df+2050，优化处理：ss_df30 X|hbs_bhX5400021452，优化处理：hbs_bh5400021542 条件内包括了多个本表的字段运算时不能进行索引，如： 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万条记录,