DB2数据库开发培训-DB2SQL性能.pdf

《DB2数据库开发培训-DB2SQL性能.pdf》由会员分享，可在线阅读，更多相关《DB2数据库开发培训-DB2SQL性能.pdf（30页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、-1-DB2 SQL 性能 数据仓库开发数据仓库开发系列培训系列培训 讲师：赵坚密讲师：赵坚密 日期：日期：20132013 年年 7 7 月月 1717 日日 -2-DBDB2 2 SQLSQL 性能性能 赵坚密（）培训介绍 本次课程主要包括四个方面：DB2 基础、DB2 逻辑设计、DB2 SQL 性能、数据仓库基础。讲述了长期积累的数据库开发和设计知识，以及数据仓库知识，也有经济资本计算引擎开发等实战经验的分享。本课程预计安排 4-5 次课程，七月两次，上下的八、九月份完成。附件是部分课程资料，课程资料尚在完善中，之后会陆续发给大家。课程目标：1、了解 DB2 数据库的常见对象与存储对象，

2、深入理解缓冲池和表空间的原理，并掌握表空间和缓冲池的设计；2、掌握第三范式原理，并能够在将来的设计中使用；3、掌握索引的原理，并且在调优中能够灵活运用；4、掌握 DB2 数据库常用特性表分区、MDC、MQT、DPF，并能够选择性的在数据仓库项目中使用；5、了解数据量对 SQL 性能的影响，掌握常见 SQL 的性能因素，并指导日常工作；6、了解常见数据仓库的概念和设计。本文内容 本文介绍了数据量对多种不同 SQL 性能的影响程度，以及如何在设计层面规避数据的增长对 SQL 性能的严重影响。重点讲解了常见 SQL 写法的性能关系，避免糟糕的 SQL。同时融入了笔者在经济资本计算引擎开发中的实战经历

3、，对 DPF 架构下的开发有很大的借鉴意义。1、数据量与 SQL 性能：数据量的增长对不同 SQL 的性能影响是不一样的，所以我们要按照数据量的增长对性能的影响程度来分类和识别 SQL，在设计和开发阶段就定位和优化，避免随着系统的运行而性能下降。2、保证 SQL 语句执行效率：良好的物理存储和准确的统计信息是 SQL 执行效率的保障。本节介绍那些操作会造成良好的物理存储结构遭到破坏，以及如何收集数据的统计信息。3、SQL 优化指引：制定 SQL 的书写规则，并有效地执行，能够很大程度上避免糟糕的SQL。4、经济资本计算引擎开发实战介绍：JAVA 中的数据处理和存储过程中有什么不同，DPF下如何

4、编写 SQL 才是最优的？5、开始行动：简要介绍了性能监控。本文适用操作系统平台为 IBM AIX 5.3，也可用于一般 UNIX 平台；数据库为 IBM DB2，版本 9.1。阅读说明 本文主要面向数据库设计和开发人员和性能调优人员。杭州滨江 2013 年 7 月 3 日 -3-4-目录目录 培训介绍.-2-本文内容.-2-阅读说明.-2-1 数据量与 SQL 性能.-6-1.1 增长的数据量.-6-1.2 操作对数据量增加的敏感程度.-7-1.2.1 受数据量增加的影响不大.-7-1.2.2 受到数据量增加的线性影响.-7-1.2.3 受到数据量增加的非线性影响.-7-2 保证 SQL 语

5、句执行效率.-8-2.1 分而治之，对付大数据.-8-2.1.1 分区表.-8-2.1.2 数据库分区.-9-2.1.3 分区表和数据库分区比较.-10-2.2 建立索引.-11-2.3 运行统计和重组.-12-2.3.1 通过运行统计来收集索引信息.-12-2.3.2 存储过程里执行运行统计语句.-13-2.3.3 重组表中的数据.-14-2.4 减少对数据库的更新和删除操作.-14-2.4.1 更新操作.-14-2.4.2 删除操作.-16-3 SQL 优化指引.-19-3.1 谓词.-19-3.2 多余的连接.-20-3.3 子查询.-20-3.4 外连接.-21-3.5 UNION A

6、LL 的使用.-21-3.6 Having 子句.-22-3.7 OFNR 和 FFNR 子句.-22-3.8 使用参数标记.-22-3.9 使用 With UR.-22-3.10 用临时表代替公用表表达式.-23-3.11 存在性判断.-23-3.12 集合操作.-23-3.13 DGTT.-23-3.14 如何使访问更高效.-23-6 开始行动.-24-6.1 用 topas 监控硬件使用情况.-24-6.2 从执行时间来确定主要矛盾.-24-6.3 进一步观察以决策.-26-6.3.1 DB2 Visual Explain.-26-5-6.3.2 DB2exfmt.-28-6.3.3 D

7、B2expln.-29-6.3.4 表快照监视器.-29-6.4 不同数据库处于不同实例.-29-6.5 开始行动.-30-6-1 数据量与数据量与 SQL 性能性能 1.1 增长的数据量增长的数据量 在数据量增加时我们总是要面对许多特殊的挑战。这包括高效搜索庞大的表，如何避免数据量稍有增长就可能出现的性能下降。尤其是，某些应用出于管理或者业务分析的目的，如数据仓库应用，需要在线保存数月，甚至数年不经常使用的数据。我们将会面临危机的考验批处理程序的耗时慢慢超过了它们可用的时间范围，干扰了客户的其他正常活动。项目的不同阶段，数据量经常会变化，如图所示。一开始，除了相对少量的参考数据被加入数据库之

8、外，几乎没有任何东西。在达到目标数据量（数据库以“巡航速度”运行时能处理的数据量）之前，第一次严重的性能问题通常会出现。当数据量很少或中等时，从最终用户角度看不出糟糕的查询和糟糕的算法的影响。硬件本身的处理能力会隐藏巨大错误，完整扫描几十万条记录的表也不到一秒时间。我们可能严重地滥用硬件能力来弥补程序的错误直到数据量变得相当可观时，真正的错误才被发现。当项目第一次出现性能危机时，通常会进行“专家调优”，增加几个本应一开始就有的索引。接下来的系统可能不太稳定，直到数据量达到目标量。通常会有两个“目标量”：计划目标量（系统设计应管理的数据量，通常会被高估）和实际目标量（系统实际处理的数据量，通常稍

9、有超出，因为遗留数据在项目最后会被考虑进来）。第二个性能危机（而且是较严重的危机）通常会随着实际目标量的到来而出现。当系统投入生产环境，架构设计的弱点被重新评估，一些关键处理被积极重写，系统最后达到“巡航速度”。随着业务的自然增长平稳成长和指数成长都有可能，数据量也会随之增长。上面描述了一个新的数据库应用生命周期的头几个月，颇有几分讽刺意味，-7-但这比“应有情况”更接近现实，因为导致这些问题的简单错误还是时常出现。压力、没时间进行充分测试，含糊不清的规格说明无论多努力工作，还是会有错误。不过，造成无法挽回失败的，是数据库设计错误和架构选择错误这两个主题密切相关，而且是系统的基础。如果地基不够

10、坚固，就必须把整栋建筑推倒重建，而其他错误需要某种程度上的检修。当然，不是所有危险都必定发生，编程时我们必须预先考虑数据量的增长，而且面对逐渐增加的数据量时，必须尽快找出使性能快速恶化的查询并改写他们。1.2 操作对数据量增加的敏感程度操作对数据量增加的敏感程度 当数据量增加时，对性能的影响程度因 SQL 操作不同而不同。有些 SQL 操作的性能受数据量增加的影响不大，有些 SQL 操作的性能则随着数据量增加而线性下降，还有些 SQL 操作面对大数据量性能非常糟糕。1.2.1 受数据量增加的影响不大受数据量增加的影响不大 通常，基于主键的搜索受数据量的影响不大，无论是 1000 笔还是 1,0

11、00,000笔不会有显著差异。常见的 B 树索引，其结构趋于扁平，效率很高，基于主键搜索单笔记录的性能不会受到表大小的影响。1.2.2 受到数据量增加的线性影响受到数据量增加的线性影响 最终用户通常认为，要返回的记录数量为原来的两倍，则查询会花更多的时间。但实际上，许多 SQL 操作花了两倍的执行时间，用户却没有意识到，就像通过全表扫描逐一返回记录时发生的情况一样。考虑聚合函数，例如计算 max()一定只返回单一记录，但 DBMS 所操作的记录数可能很多，不过最终用户只看到单一的记录被返回，所以他们会抱怨性能随着时间而下降。确保情况不会变糟的唯一方法，就是使用另一个条件（例如日期范围），对要处

12、理的记录数量设定上限。设定上限能让数据量保持在控制范围内。对于 max()的例子，可以查询给定日期之后的最大值，而不是所有值中的最大值。增加查询条件不是单纯的技术问题，还依赖于业务需求，但限定查询范围作为可选手段，值得在设计时进行讨论。1.2.3 受到数据量增加的非线性影响受到数据量增加的非线性影响 数据量增加时，排序操作所收的影响比扫描操作还大，因为排序是复杂操作，一般而言需要多遍处理。对 100 条随机的记录排序，所需成本并不是 10 条记录的 10 倍，而是大约 20 倍。排序 1000 条记录所需成本，比排序 10 条记录平均高300 倍。然而在实际中，记录很少是随机存储的，即使没有使

13、用“聚集索引”也是如此。DBMS 有时使用有序索引，以预期的顺序读取纪录，而不是先读取后排序-8-读取较大的有序集合时，性能降低一点并不奇怪。但注意，排序性能降低常是间歇发生的，因为较小型的排序将全部在内存中执行，而较大型的排序（涉及多个有序子集的合并）则需要经有序子集临时存储到慢得多的硬盘中。所以，通过调整分配给排序的内存数量来改善排序密集型操作的性能，是常见且有效的调优技巧。2 保证保证 SQL 语句执行效率语句执行效率 2.1 分而治之，对付大数据分而治之，对付大数据 为了避免数据量的增加，而暴露出来的两次危机，我们可以采取“分而治之”的策略。目前，在主流数据库中有两种技术：分区表和数据

14、库分区。这两种方式都是将大表数据进行物理上的拆分，使得数据量敏感的操作，拆分成多个子操作成为可能。分区表可以使数据按照分区字段分成物理独立的分区，使得查询能够定位到某分区内的数据，提高查询性能。数据库分区可以将数据分散到多台物理机器，更好地实现查询的并行性。2.1.1 分区表分区表 分区表根据表分区键将表数据分布到多个表分区中。比起普通表，分区表凭借其先天优势，在表功能、SQL 处理性能等多方面遥遥领先。它允许一个物理表被分成多个分散的物理存储对象，每个存储对象对应表的一部分，用值的范围来指定每个分区。分区表和普通表的最大不同就是分区表可以将各个分区位于不同的表空间中，以便管理和备份，并且提高

15、数据库并行性和性能。分区表的主要优势：首先，增加了表的功能。数据可以分区、分表空间存放，这也可以方便的进行数据的转入转出。其次，这样做提高了 SQL 处理的性能，由于各个月的数据分区存放，如果用户需要访问某一特定月的数据，可以直接对相应的分区访问即可，这样避免了对其他数据的访问。同时，数据存放在不同表空间，在进行数据访问时，可以并行 I/O，以提高访问效率。同时，使用分区表还有一个很大的好处，就是锁方面的好处。当应用程序访问某一月数据时，只需对相应的分区加锁，从而避免了像普通表一样对整个表加锁，这就大大提高了数据库并发性。转入转出功能对大数据量的表非常有用，数据可以灵活插拔，不用时转出，用时转

16、入。如果把不用的数据转出，可以减少数据量，显著提高性能。并且用转入转出删除和插入数据非常快。分区表还有其他优势，包括可以在不同的表空间内放置索引，提高效率。-9-2.1.2 数据库分区数据库分区 数据库分区是一种物理数据库设计技术，是将一个或多个数据库表分布在两台以及多台物理或者逻辑机器上，通过并行的方式对分布在多台机器上的数据库表进行处理，从而实现对数据的快速处理。数据库分区的主要目的是为了在特定的 SQL 操作中减少数据读写的总量以缩减响应时间。通过数据库分区技术可以提高对大容量数据的访问速度，对数据进行并行读取，并且数据库分区技术在一定程度上具有可扩展性。从架构上来看，数据库分区可划分为

17、三种典型的架构：共享内存(Shared Memory)模式，共享磁盘(Shared Disk)模式以及无共享(Shared Nothing)模式。这里将重点介绍无共享模式的 DB2数据库的分区特性。DB2 的企业版提供的数据库分区功能，即 DPF(Data Partitioning Feature)，这一功能主要用来为大规模数据处理提供支持。DB2 数据库分区采用 Share-nothing体系结构，数据库在一个非共享的环境中被分解为独立的分区，每个分区都具有自己的资源，例如内存、CPU 和磁盘以及自己的数据、索引、配置文件和事务日志。数据库分区有时称为节点或数据库节点，每个节点具有独立处理单

18、一任务的能力，每个子任务处理一部分数据，分区间数据通过高速网络进行交互。DB2 DPF 的扩展性较好，通过增加节点资源即可线性扩展节点。简单来说，它相当于将一个大的数据库分成多个小的数据库，而每一个小数据库分区拥有自己的一部分数据，同时达到节点同时并发的高效率处理能力。DPF 是一种跨多个彼此合作的实例，以建立单个大型数据库服务器的技术。这些实例既可以位于一个服务器中，也可以跨多个物理机器。DB2 DPF 是基于并行性的体系架构，这里的并行包括在节点之间的并行，以及在节点之内分区的并行；查询语句之间的并行，以及查询语句内部的并行等。在性能方面，采取分而治之的策略，没有限制的规模。在编译器方面，

19、采用基于开销的优化器和查询重写器，SQL 和实用工具完全平行运行。除此之外，DPF-10-还根据负载动态分流，同时支持异步 I/O 和平行 I/O 等。2.1.3 分区表和数据库分区比较分区表和数据库分区比较 为了便于理解，下面对分区表和数据库分区进行比较，如下表所示：分区表 数据库分区 数据组织 将所有行放在同一个数据分区的一个指定范围的维中 将行均匀地分布在多个数据库分区上 优点 数据移动：通过添加和删除整个数据分区，可以增加和删除大量数据 可伸缩性：随着数据库的增长增加计算资源（也就是数据库分区）适合的表特征 这种类型的表：周期性地添加大量数据，然后在数据到期后又删除大量数据 大型表：大

20、到无法仅依靠单独一组 CPU 和 I/O 通道来处理 事实表的特征 在事实表中，常常是每天都添加新数据。通常每月或每个季度删除过时的数据 事实表是最大的数据库表。它们常常包含数亿行数据，有时候甚至包含数千亿行数据 分区数据的列 表分区键 分布键 分区设计经验 选择一个基于时间的列。定义与每次转出的数据量相符的分区 首选是具有很多不同值的列 分区表和数据库分区可以同时存在与一个设计中。所以对于数据仓库等大型数据库系统，尽量同时采用分区表和数据库分区。采用分区表实现按照一定范围查询，并且方便数据的转入和转出。采用数据库分区实现更好的扩展性，并确保在各分区之间均匀分布数据。-11-2.2 建立索引建

21、立索引 使用索引实现关键数据的高效访问。但是需要知道每个索引都会给数据库更新带来额外的开销。这就意味着，低效的索引会给数据库带来灾难。对于数据库，我们必须关注关键数据的读取，为他们提供最高效的访问路径。对此，基本策略就是建立索引。在索引提供高效访问的同时，也带来了额外的系统开销。开销分为磁盘空间的开销和处理器开销。下面我们讨论一下处理器开销。每当在表中插入或删除记录时，该表的所有索引必须进行相应调整。每当对已建立索引的字段进行更新时，这种调整也会发生。举例子说，如果在未建立索引的表中插入数据需要 100 个单位时间，那么每增加一个索引就会增加 100 到 250个单位时间。有趣的是，维护索引的

22、开销与简单触发器带来的开销大致相当。在建立索引前线介绍一些最通俗的信息，这些信息来自 developWorks，列出这些信息是因为我觉得这些信息通常情况下是值得参考的：1.当要在一个合理的时间内结束查询时，应避免添加索引，因为索引会降慢更新操作的速度并消耗额外的空间。有时候还可能存在覆盖好几个查询的大型索引。2.基数较大的列很适合用来做索引。3.考虑到管理上的开销，应避免在索引中使用多于 5 个的列。4.对于多列索引，将查询中引用最多的列放在定义的前面。5.避免添加与已有的索引相似的索引。因为这样会给优化器带来更多的工作，并且会降慢更新操作的速度。相反，我们应该修改已有的索引，使其包含附加的列

23、。例如，假设在一个表的(c1,c2)上有一个索引 i1。您注意到查询中使用了where c2=?，于是又创建一个(c2)上的索引 i2。但是这个相似的索引没有添加任何东西，它只是 i1 的冗余，而现在反而成了额外的开销。6.如果表是只读的，并且包含很多的行，那么可以尝试定义一个索引，通过 CREATE INDEX 中的 INCLUDE 子句使该索引包含查询中引用的所有列（被 INCLUDE 子句包含的列并不是索引的一部分，而只是作为索引页的一部分来存储，以避免附加的数据FETCHES）。对于数据仓库（查询系统数据库）可以建立较多的索引（索引和数据的比例可以是 1：1）。决定是否使用索引，可以重

24、点考虑检索比率。即，判断索引有效性的依据，就使用键值作唯一性条件检索出的数据的百分比。百分比越低，索引越有效。做出这个论断的前提是一些假设，如磁盘访问的相关性能。索引键值相关记录的物理位置是否相邻也很重要，因为是通过块来操作数据的。建立了索引之后，如果索引键所指向的记录散布于整个表中，即使这些记录在表中占的比率很小，但因为它们分散在整个磁盘上，所以索引的性能就会大打折扣。另外值得注意的是，函数和类型转换可能导致索引失效。-12-2.3 运行统计和重组运行统计和重组 优化器需要知道索引的相关信息，来判断查询执行的“路径”，运行统计可以告诉优化器。经验：当表中数据量变化达到 10%时，需要重新运行

25、统计。在许多次的更新之后，表会成为碎片状，这导致性能严重下降。若收集了统计信息，但看不出有明显的性能改进，则重组表数据可能会有帮助。重组表数据时，根据指定的索引重新安排数据的物理顺序，并除去碎片数据中的空闲空间。这使该数据可以更快速的被存取，从而改进性能。2.3.1 通过运行统计来收集索引信息通过运行统计来收集索引信息 运行统计（runstats）的作用是收集索引的统计信息，如果数据库没有收集索引的统计信息，优化器就无从下手，只能按部就班，通过全表扫描来执行查询。所以，新创建的索引需要重新运行统计，否则索引无效。举一个例子，有个表 TABLE1，其中有一个字段 COL1 取值是“1”、“2”、

26、“3”三种，运行统计的结果是告诉数据库 TABLE1 中的数据其中字段 COL1 的各种取值所占的比重。示意如下：“1”12%；“2”66%；“3”22%。假设还有个字段 COL2 取值和数据所占的百分比如下：“A”50%；“B”50%。则查询语句 1：select*from TABLE1 where COL1=“1”and COL2=“A”，数据库优化器会优先选择字段 COL1 上的索引来定位表中的数据，因为通过COL1 上的索引就可以将结果集迅速定位在一个小范围内 12%。而相反的，对于查询语句 2：select*from TABLE1 where COL1=“2”and COL2=“A”

27、，数据库会优先选择 COL2 上的索引，因为对于语句 2 的查询条件 COL2 上的索引具有更好的区分度。从上面可以看出，数据库的优化器通常会优先选择区分度较高的索引（针对于查询条件，条件不同选择的索引可能不同）。数据库里的数据是变化的，所以某个时候采集的统计信息，过一段时间后可能会过时，甚至误导 DB2 优化器，这样同样会造成运行性能的低下。所以除了，最初建立索引时需要运行统计，在表中的数据发生变化时也需要运行统计。经验：当表中数据量变化达到 10%时，需要重新运行统计。-13-2.3.2 存储过程里存储过程里执行运行统计语句执行运行统计语句 运行统计的语句如下：runstats on ta

28、ble DB2ADMIN.TABLE with distribution and detailed indexes all;在存储过程中不能直接执行 runstats 语句，可以通过调用 ysproc.admin_cmd来实现，具体语句如下：call sysproc.admin_cmd(runstats 语句);下面用一个存储过程封装了此过程，在需要对一个表运行统计时，只需要调用 pr_runstats 存储过程即可。/*Name:pr_runstats *Description:运行统计表 *Creator:赵坚密 *Creation date:2008-12-30 *Parameters:

29、str*-*db2 connect to inte user db2admin using db2admin *编译过程 *db2-td-vf pr_runstats.db2 *运行过程 *db2 call pr_runstats()*/drop procedure pr_runstats(varchar(100)create procedure pr_runstats(IN_TABLE_NAME varchar(100)language sql begin declare rowcount integer default 1;declare errLog varchar(400);decla

30、re sqlcode integer;declare sqlstate char(5);declare str_sql varchar(150);declare not_found CONDITION FOR SQLSTATE 02000;declare CONTINUE HANDLER FOR not_found set rowcount=0;declare exit handler for sqlexception call pr_log(truncat_table,errLog,sqlcode,sqlstate);call pr_log(truncat_table,运行统计|IN_TAB

31、LE_NAME,0,0);call sysproc.admin_cmd(runstats on table DB2INST1.|IN_TABLE_NAME|with distribution and detailed indexes all);call pr_log(truncat_table,运行统计|IN_TABLE_NAME,0,0);commit;end -14-2.3.3 重组表中的数据重组表中的数据 重组（reorg）操作会重新排列表数据的物理顺序，并除去碎片数据中的空闲空间。由于删除操作不释放磁盘空间，在执行删除操作后，表会成为碎片状，这导致性能严重下降，在多次更新操作之后也会出

32、现这种情况。若收集了统计信息，但看不出有明显的性能改进，则重组表数据可能会有帮助。重组表数据时，根据指定的索引重新安排数据的物理顺序，并除去碎片数据中的空闲空间。这使该数据可以更快速的被存取，从而改进性能。2.4 减少对数据库的更新和删除操作减少对数据库的更新和删除操作 删除和更新操作的开销往往比插入高，所以一个好的设计需要减少对数据库的更新和删除操作。2.4.1 更新操作更新操作 数据库的更新操作会带来一连串的“效应”：更新操作需要记录日志（以便错误时回滚）；更新可变长字段（如，varchar 类型）会带来数据物理存储的变化（记录的移动）；更新索引字段会导致索引重建；更新主键会导致数据重组等

33、。这一切不但会造成更新操作本身效率低，而且由于磁片碎片的产生会造成以后查询性能的降低。为了应对这一情况，有两种策略：一、减少更新次数，把多个字段的更新写到同一个语句里；二、避免更新。这两种策略分别适用于不同的情况，下面将举例说明两种情况。2.4.1.1 减少更新次数减少更新次数 在整合库里有个代码清洗过程，就是通过连接代码表给业务数据的自编码字段赋值。代码清洗其实是通过关联代码表来更新业务数据表的一个过程，需要连接多个代码表，更新多个自编码字段。完成此更新，有两种更新语句的写法：一种是写成多个 SQL 语句，每个语句更新一个自编码字段；另一种写法是将所有更新写在一个语句中。更新银行代码的更新语

34、句如下所示：update TBL_INCOME_TMP A set BANKCODESELF=(select SELFCODE from TBL_BANKINFO B where A.BANKCODE=B.BANKCODE)通过一个更新语句实现多个自编码字段更新的语句示意如下：update TBL_INCOME_TMP set 代码 1 自编码=通过关联代码 1 表得到自编码，-15-代码 2 自编码=通过关联代码 2 表得到自编码，.，代码 n 自编码=通过关联代码 n 表得到自编码 利用两千万的测试数据。两种方法的测试结果如下表所示。从测试结果看出，一次更新方法性能提高了十倍，大大提高了性

35、能。处理过程 多次更新方法耗时 一次更新方法耗时 代码清洗 0:29:48 0:02:59 2.4.1.2 避免更新避免更新 下面举个通俗的例子，这类情况是经常遇到的。某公司有一套系统员工考勤系统，为了提高查询统计的性能，在原有系统基础上建立了一些包含冗余信息的表。以员工表为例，它获得数据的过程如图 12 所示。第一步把员工信息放到新表中，然后连接通过字段“部门 ID”连接更新“部门名称”。图12.关联更新 一般，为了节省存储开支把部门名称这样的字段设计成可变长的。所以在对它进行更新时会造成磁盘数据的重新组织，形成磁盘碎片，影响查询性能。为了避免这样的情况发生，我们可以使用如图 13 所示的方

36、法避免更新。这种方法一步完成了冗余数据表的插入，再插入时连接部门表获得“部门名称”，从而避免了更新操作。-16-图13.避免更新 2.4.2 删除操作删除操作 初学者可能认为删除操作很简单，可以快速完成。其实这是一个错误的理解，删除过程需要大量扫描磁盘；需要记录数据库日志；而且删除过程不释放磁盘空间，浪费磁盘，并且使磁盘上的数据支离破碎，这对后续查询的性能是一个致命的打击。通常用两种方式来应对：一、对经常做删除操作的表进行重组（reorg）；二、避免删除。2.4.2.1 重组重组 见 4.3.3 节。2.4.2.2 避免删除避免删除中间表和正式表模式中间表和正式表模式 在数据需要比较复杂的处理

37、的时候经常会用到中间表和正式表模式。数据在中间表中被处理，然后把满足条件的数据转移至正式表，不满足条件的数据保留在中间表中。图 14 示意了数据从中间表转移到正式表的过程：在完成数据处理之后，需要把中间表 temp1 中 flag=1 的数据插入到正式表，并删除中间表 temp1中 flag=1 的数据。-17-图14.从中间表向正式表转移数据 因为 flag 字段不是聚簇索引，所以当对中间表 temp1 进行删除后，会再磁盘中留下大量碎片，如图 15 所示。不但会留下那么多的磁盘碎片，而且已删除的数据的空间也不会自动释放。结果是不但浪费磁盘空间，而且查询性能会急剧下降。图15.删除操作后的磁

38、盘碎片 咱们可以使用清空表的命令来避免删除操作。除了中间表 temp1 和正式表，添加辅助临时表 temp2。如果 temp1 中保留的数据 flag=0 只占有 10%，这一优化将显著提升性能。具体步骤如下：1.将 temp1 中 flag=0 的数据，插入到 temp2 2.清空表 temp1 alter table temp1 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;3.将 temp2 中的数据插入 temp1 -18-2.4.2.3 存储过程里实现表清空存储过程里实现表清空 在存储过程里不能直接执行表清空语句来清空表，但是可以通过以

39、下命令来实现：EXECUTE IMMEDIATE str_sql;（其中 str_sql，为 sql 语句）下面用一个存储过程封装了此过程，在需要对一个表运行清空时，只需要调用 pr_truncat_table 存储过程即可。/*Name:pr_truncat_table *Description:将一个表初始化成空表（删除所有记录），不记日志 *Creator:赵坚密 *Creation date:2008-1-2 *Parameters:str *-*db2 connect to inte user db2admin using db2admin *编译过程 *db2-td-vf inco

40、me_to0206.db2 *运行过程 *db2 call pr_truncat_table()*/drop procedure pr_truncat_table(varchar(100)create procedure pr_truncat_table(IN_TABLE_NAME varchar(100)language sql begin declare rowcount integer default 1;declare errLog varchar(400);declare sqlcode integer;declare sqlstate char(5);declare str_sql

41、 varchar(150);declare not_found CONDITION FOR SQLSTATE 02000;declare CONTINUE HANDLER FOR not_found set rowcount=0;declare exit handler for sqlexception call logNote(truncat_table,errLog,sqlcode,sqlstate);call logNote(truncat_table,清空表|IN_TABLE_NAME|中的记录,0,0);set str_sql=alter table|IN_TABLE_NAME|ac

42、tivate not logged initially with empty table;EXECUTE IMMEDIATE str_sql;call logNote(truncat_table,清空表|IN_TABLE_NAME|中的记录完成,0,0);commit;end -19-3 SQL 优化指引优化指引 3.1 谓词谓词 首先要知道，不合理的谓词会限制优化器对索引和连接方法的选择。设计谓词时要注意下面的原则。（1）保证选择谓词足够简洁。选择谓词要尽量采用简单的形式，如：列名=常数表达式，这样便于匹配索引。还应该避免使用类型转换，如果有类型转换应该显式地写出转换函数，并放在表达式的常数

43、一边，例如：cast(colum_char10 as int)=100 应该写成：colum_char10=cast(100 as char(10)另外，使用参数标记（Parameter Marker）时，要尽量利用 Cast 函数表明它的类型，以免产生不必要的类型转换。同样，下面的谓词都应该写成更优化的形式：XPRESSN(C)=constant INTEGER(TRANS_DATE)/100=200802 WHERE(CUST_ID*100)+INT(CUST_CODE)=123456 ORDER BY 1,2,3 它们的正确形式应该为：C=INVERXPRESSN(constant)TR

44、ANS_DATE BETWEEN 20080201 AND 20080229 WHERE CUST_ID=1234 AND CUST_CODE=56 ORDER BY 1,2,3（2）使用合理的连接谓词（Join Predicate）。连接谓词是选择连接方法的基本依据。DB2 有三种连接方法：嵌套循环、合并连接和散列连接。注意非等式谓词不能形成合并和散列连接。只有两个表之间出现至少一个列名相等的等式谓词时，合并和散列连接才会被考虑。散列连接更是要求等式两端的列的长度相等。为了使 DB2 考了尽可能多的连接方法，连接谓词应该尽量简单，如列名 1=列名 2。对于等式谓词要做到等式两边的列名长度相等

45、。如果是 Varchar，Char类型，它们定义的长度应该相等，如果是 Decimal 类型，它们的精度应该相同。（3）不要使用多余的谓词。多余的谓词不但增加了系统的计算代价，而且会导致中间结果的估算不准确，并产生较差的访问计划。例如对于下面的谓词，（COL_0MONTH=199903）实际上是多余的，应该去掉。(COL_0MONTH=199705 -20-OR COL_0MONTH=199805)AND NOT(COL_0MONTH=199903)3.2 多余的连接多余的连接 有些连接看似合理，但也许是多余的。连接会涉及对表的多次扫描，去掉多余的连接，就会减少不必要的开销。比如，下面的查询：

46、select T1.*from(T1 join T2 as Q2 on T1.id=Q2.id and Q2.value=1)join T2 as Q3 on T1.id=Q3.id and Q3.value=2 去掉不必要的连接后，其形式为：select T1.*from T1,(select id from T2 where value in(1,2)group by id having count(*)=2)as Q1 where T1.id=Q1.id 这样减少了对 T2 表的一次连接，大幅度提高了查询效率。3.3 子查询子查询 在 SQL 语言中，当一个查询语句嵌套在另一个查询的查询

47、条件中时，称为子查询。如果一个来自外部查询的列出现在 Where 子句的子查询中，那么当外部查询中的列值改变后，子查询需要重新查询一次。查询嵌套的层次越多，效率越低。因此应当尽量避免子查询。如果子查询不可避免，那么首先首先要在子查询中尽量去掉多余的行，其次考虑将子查询转换成连接。子查询转换为连接后，可以使 DB2 有机会考虑索引以及更多的连接方法和顺序，从而生成更优的计划。特别是 Exist 和 IN 子句，可以方便的转换成连接。例如，select*from T1 where exists(select*from T2 where T1.c1=T2.c1)-21-可以转换为，select di

48、stinct T2.c1,T1.*from T1,T2 where T1.c1=T2.c1 注意除非 T2.c1 有唯一性（unique），否则关键词 distinct 是必需的，这是为了保证最终结果和原来的 SQL 一致。3.4 外连接外连接 要避免不必要的外连接（Outer Join）。Outer Join 会限制连接的顺序，从而导致一些较好的计划无法生成。因此要尽可能避免使用 Outer Join，无论是 Left、Right 或者 Full Outer Join。3.5 UNION ALL 的使用的使用 在使用 Union ALL 时，我们要注意 UNION ALL 之上的谓词是否被下

49、推。可以查看Optimized Statement，看看是否每个选择谓词都被下推到相应的子查询中。如：select*from(select*from T1 union all select*from T2)as V1 where c1 between 2000 and 2009 在 Optimized Statement 中应该看到选择谓词 c1 between 2000 and 2009 被下推到 Union All 里面变成：select*from(select*from T1 where c1 between 2000 and 2009 union all select*from T2

50、where c1 between 2000 and 2009)as V1 如果没有下推，可能由于选择谓词过于复杂，或者 DB2 的查询重写器无法判定下推谓词是否会提高效率。这时候如果确信下推后会产生较好的计划，可以手动改变 SQL 的书写形式。还可以考虑适当提高优化级别，使原来没有下推的谓词被下推。-22-3.6 Having 子句子句 检查Having子句中的谓词是否可以下推。应尽可能把Having子句中的谓词。一般情况下，根据一定的规则，Having 子句如果不含有聚合函数，会经过逻辑优化被下推到 Where 子句里面，但也会出现不下推的情况。为了避免出现没有下推的情况，在编写 SQL 是