20120627db2数据库基础、设计与优化

来源：互联网发布：java httpget下载文件编辑：程序博客网时间：2024/04/28 03:31

鉴于CSDN无故删除博文，本博客不再更新，暂时迁至http://www.db365.net

商业智能群199567325，2012年6月27号《db2数据库基础、设计与优化》，讲解者：赵坚密Jimmy。

讲课方式，YY频道号：85536471

课件材料《大数据量数据整合与查询性能优化（DB2V9& AIXV5.3）v1.2 - 副本.pdf》，可从以下地址免费下载：http://wenku.baidu.com/view/21929d95daef5ef7ba0d3c6d.html

此YY频道每周三讲解，具体请加QQ群21025718下载讲课录音。

大数据量数据整合与查询性能优化（修订）

赵坚密（above@163.com）

前言

本文适用操作系统平台为IBM AIX 5.3，也可用于一般UNIX平台；数据库为IBM DB2，版本9.1文中引用了笔者在金宏工程外汇管理局整合数据库、统计分析系统和综合查询子系统的开发经验。本文的题目就来自于这样一个背景，因为整合库系统是一个大数据量数据整合系统，综合查询和统计分析系统是大数据量的查询系统，所以将本文命名为《大数据量数据整合与查询性能优化》。本文只考虑单一节点的数据库，没有讨论数据库集群。

关于性能优化有两大目标：（1）如何充分调动系统资源；（2）采取何种方案使得达到某一目的而消耗的系统资源最少。第一个目标是要提高系统资源利用率；第二个目标是要确保对资源的利用是高效的。

硬件产品的升级非常快，商用服务器通常是多核，多硬盘的。现今，就连家用机也配备了多核。如金宏工程采购的IBM小型机，便是多核的，中等配置为8CPU、32GB内存，稍高的配置为16CPU，甚至更高。而硬盘则是RAID5磁盘阵列，通常一台机器10块盘，或更多。

提高并行性便能很好的调动系统资源。通常从两个方面可以提高并行性：（1）几件事并行的做；（2）一件事使用多个处理器来做。在系统设计时，对处理流程进行优化，让多个独立的过程并行执行，从而占用多个处理器，提高处理器的利用率。在性能测试环境，或者生产环境上需要调整系统或者应用的相关参数，使得一个过程也能占用多个处理器，进一步提高处理器的利用率。

“消耗更少的系统资源，来完成相同的事情”是所有程序员追求的目标。为了完成这一目标，程序员们不断优化自己的代码，甚至不惜彻底的改变程序的核心算法。

对于SQL代码的优化，通常处于硬件、操作系统、数据库管理系统等优化之后。

为了更好的达到这一目标，通常要求程序员有很好的代码书写习惯，第一次就写出优良的代码。优化时，只要考虑算法的优化即可。算法的优化也不是盲目的，通常针对那些“需要优化的部分”来进行。“需要优化的部分”就是那些影响整体性能的，影响整体进度的，处于“关键路径”上的过程。

代码和算法的优化也是性能优化的重要方面。但这个方面不是本文介绍的重点。

阅读说明

本文主要面向数据库设计和开发人员、部署人员和性能调优人员。

前言...- 1 -

阅读说明...- 1 -

1 DB2数据库基本概念... - 4 -

1.1 数据库对象... - 4 -

1.1.1 实例... - 5 -

1.1.2 数据库... - 5 -

1.1.3 节点组... - 5 -

1.1.4 表... - 5 -

1.1.5 视图... - 5 -

1.1.6 索引... - 5 -

1.1.7 模式... - 7 -

1.1.8 系统目录表... - 7 -

1.2 存储器对象... - 7 -

1.2.1 表空间... - 7 -

1.2.2 容器... - 9 -

1.2.3 缓冲池... - 9 -

2 表空间存储... - 10 -

2.1 AIX系统存储管理简介... - 10 -

2.2 裸设备和文件系统... - 11 -

2.3 系统管理和数据库管理... - 12 -

2.4 如何合理创建表空间设备... - 13 -

2.4.1 原则... - 13 -

2.4.2 表空间容器的创建方法... - 13 -

2.4.3 整合数据库表空间设备情况... - 14 -

2.5 建库语句参考... - 14 -

3 缓冲池和表空间... - 16 -

3.1 缓冲池... - 16 -

3.2 缓冲池大小... - 16 -

3.3 表空间页大小... - 16 -

3.4 系统临时表空间... - 17 -

3.5 区段大小和预取大小... - 17 -

3.5.1 区段大小... - 18 -

3.5.2 预取大小... - 18 -

4 提高SQL语句执行效率... - 19 -

4.1 建立索引... - 19 -

4.2 采用表分区... - 20 -

4.3 运行统计和重组... - 21 -

4.3.1 通过运行统计来收集索引信息... - 21 -

4.3.2 存储过程里执行运行统计语句... - 22 -

4.3.3 重组表中的数据... - 23 -

4.4 减少对数据库的更新和删除操作... - 23 -

4.4.1 更新操作... - 23 -

4.4.2删除操作...- 25 -

4.5 如何使访问更高效... - 28 -

5 参数调优... - 29 -

5.1 AIX系统... - 29 -

5.2 DB2环境变量... - 30 -

5.3 DB2数据库实例参数... - 30 -

5.4 DB2数据库参数... - 31 -

6 开始行动... - 32 -

6.1 用topas监控硬件使用情况... - 32 -

6.2 从执行时间来确定主要矛盾... - 32 -

6.3 进一步观察以决策... - 33 -

6.3.1 DB2 Visual Explain. - 33 -

6.3.2 DB2exfmt- 36 -

6.3.3 DB2expln. - 37 -

6.3.4 表快照监视器... - 37 -

6.4 不同数据库处于不同实例... - 37 -

6.5 开始行动... - 38 -

7 参考文献... - 39 -

8索引...- 40 -

8.1索引与目录...- 40 -

8.2影响索引作用的因素...- 40 -

8.2.1区分度（检索比率）...- 40 -

8.2.2聚集度...- 41 -

8.2.3表大小...- 41 -

8.2.4业务类型...- 41 -

8.2.5函数与索引...- 42 -

8.3索引开销...- 42 -

8.4索引总结...- 43 -

9 SQL语句编写（DB2）... - 45 -

9.1谓词...- 45 -

9.2多余的连接...- 46 -

9.3子查询...- 46 -

9.4外连接...- 47 -

9.5 UNION ALL的使用... - 47 -

9.6 Having子句... - 48 -

9.7 OFNR和FFNR子句... - 48 -

9.8使用参数标记...- 48 -

1 DB2数据库基本概念

1.1 数据库对象

关键的数据库对象包括：实例、数据库、节点组、表、视图、索引、模式、系统目录表。图1显示了一些数据库对象之间的关系。它也显示了表、索引和长整型数据存储在表空间中的情况。

图1. 一些数据库对象之间的关系

1.1.1 实例

实例（有时称为数据库管理程序）是管理数据的DB2代码。它控制可对数据执行的操作，并管理分配给它的系统资源。每一个实例都是一个完整的环境。一个实例有它自己的数据库（其他的实例不能存取它）。它还有独立的安全性，不受同一机器（系统）上其他实例的影响。

1.1.2 数据库

关系数据库将数据表示成表的集合。表由数目已定的列和任意数目的行组成。每个数据库都包括一组描述数据的逻辑和物理结构的系统目录表，一个包含为该数据库分配的参数值的配置文件以及一个带有正在进行的事务和可归档事务的恢复日志。

1.1.3 节点组

节点组是一个或多个数据库分区组成的集合。当您想为数据库创建表时，首先创建节点组以存储表空间，然后创建表空间以存储表。

1.1.4 表

关系数据库将数据表示成表的集合。表由逻辑排列的行和列数据组成。所有数据库的表数据都被存储在表空间中。

1.1.5 视图

视图是高效率的数据呈现方法（无需维护数据）。视图不是实际的表，不需要永久性存储器。创建并使用一个“虚拟表”。

视图可以包括它所基于的表中的所有或某些列或行。例如，可以在视图中连接一个部门表和一个员工表，以便可以列出特定部门中的所有雇员。图2显示了表和视图的关系。

1.1.6 索引

索引是一组关键字，其每一个都指向一些行。例如，图3中，表A的一个索引基于表中的员工号码。此关键字提供指向表行的指针：员工号码19指向员工KMP。通过使用指针创建指向数据的直接路径，索引是更有效的存取表行成为可能。

SQL优化器自动选择最有效率的存取表中数据的方法。当确定最快速的数据存取路径时，优化器会将索引考虑在内。

可创建唯一索引以确保索引关键字的唯一性。索引关键字是定义了索引的一个列或一些列的有序集合。使用唯一索引将确保在编入的索引的列中，每个索引关键字的值都是唯一的。图3显示了索引与表之间的关系。

图2. 表和视图之间的关系

图3. 索引与表之间的关系

1.1.7 模式

模式是一个标识符，如用户ID，它帮助分组表和其他数据库对象。模式可以归个人拥有，拥有者可以控制对数据以及其中的对象的存取。

模式也可以是数据库中的对象。它可以在创建模式中的第一个对象时自动创建。这样的对象可以是任何可以由模式名限定的对象，如表、索引、视图、程序包、单值类型、函数或触发器。

1.1.8 系统目录表

每个数据库都包括一组描述数据的逻辑和物理结构的系统目录表。DB2为每个数据库维护一大组系统目录表。这些表包含有关数据库对象（例如，用户表、视图和索引）的定义信息，以及用户对这些对象所拥有的权限的安全性信息。它们在数据库创建时被创建，并在常规操作期间得到更新。不能显示的创建或卸载下它们，但是可以使用目录视图查询和查看它们的内容。

1.2 存储器对象

下列数据库对象用来定义在系统上存储数据的方式以及改进（与存取数据相关的）性能的方法：表空间、容器和缓冲池。

1.2.1 表空间

数据库由称为表空间的部件组成。表空间是用来存储表的位置。当创建表时，您可以决定将特定对象（如索引和大对象）数据与其余表数据分开存放。表空间也可以分布在一个或多个物理存储设备上。图4显示了在表空间之间分布数据时具有的一些灵活性。

图4. 表空间

将容器分配给表空间。容器是分配的物理存储器（如文件和设备）。

表空间可以使系统管理空间（SMS）或数据库管理空间（DMS）。对于SMS表空间，每个容器都是操作系统的文件空间中的一个目录，由操作系统的文件管理器控制存储空间。对于DMS表空间，每个容器或者是固定大小的预分配文件，或者是物理设备如磁盘，由数据库管理程序控制存储空间。

表空间有三种类型：规则、临时和长整数。

包含用户数据的表存放在规则表空间中。缺省用户表空间名为USERSPACE1。索引也存储在规则表空间中。系统目录表存放在规则表空间中。缺省系统目录表空间名为SYSCATSAPCE。

包含长整数字段数据或长整数对象数据（如多媒体对象）的表存放在长整数表空间中。

临时表空间分为系统临时表空间或用户临时表空间。系统临时表空间用来存储SQL操作（如排序、重组表、创建索引和连接表）期间所需的内部临时数据。虽然可以创建任意数目个系统临时表空间，但建议您只适用大多数表所使用的页大小创建一个。缺省系统临时表空间名为TEMPSPACE1。用户临时表空间用来存储已说明全局临时表（已说明全局临时表存储的是应用程序临时数据）。用户临时表空间不是在数据库创建时缺省创建的。

1.2.2 容器

容器是物理存储设备。它可以由目录名、设备名或文件名标识。

将为表空间分配容器。单个表空间可以横跨多个容器，但每个容器只能属于一个表空间。

图5举例说明了表与数据库中的表空间、相关联的容器和磁盘之间的关系。

图5. 表空间、相关联的容器和磁盘之间的关系

EMPLOYEE、DEPARTMENT和PROJECT表在HUMANRES表空间中，该表空间横跨容器0、1、2、3和4。此示例显示每个容器存在于不同的磁盘上。

任何表的数据都以循环方式存储在表空间中的所有容器中。这能在属于给定表空间容器之间平衡数据。数据库管理程序在使用另一个容器之前写入一个容器的页数称为数据块大小。

1.2.3 缓冲池

缓冲池指的是从磁盘读去高速缓存表和索引数据页时或修改它们时分配给它们的主存储器。缓冲池的目的是改进系统性能。从内存存取数据要比从磁盘存取数据块的多；因此，数据库管理程序需要读写（I/O）的次数越少，性能也越好。（可以创建多个缓冲池，虽然在大多数情况下只需要一个。）

因为可以缩短慢速I/O所造成的延迟，所以缓冲池的配置是最重要的调整项目。

2 表空间存储

在存储管理上，AIX系统采用逻辑卷管理策略。一个设备（逻辑卷）可以跨越多个磁盘，这就为并行读写创造了可能。

目录表空间和日志表空间均存储在文件系统中，而数据表空间的存储类型通常是裸设备。一个表空间使用一个或多个设备（文件系统或裸设备）。

2.1 AIX系统存储管理简介

在AIX系统中，文件系统是建立在逻辑卷上的，逻辑卷是属于一个卷组的，卷组是由实际的物理磁盘组成，逻辑卷在实际物理硬盘中可以不连续，它可以跨越多个物理硬盘而存在。AIX系统中物理与逻辑区段的映射如图6所示

图6. 物理到逻辑区段的映射

在逻辑上，逻辑卷存在于卷组中，而在物理上，它存在于物理卷上，物理卷是硬盘。硬盘的界面分为5个同心区域：外边缘（Outer-Edge）、外中间（Outer-Middle）、中央（Center）、内中间（Inner-Middle）和内边缘（Inner-Edge），如图7所示。

由于磁盘机械装置的物理移动，边缘（Edge）区域的搜索时间最长。逻辑卷位于哪个区域上，对于逻辑卷的读写效率影响很大。

其中，中央（Center）区域是磁盘搜索时间最短和访问速度最快的。通常，把经常要访问的逻辑卷放在磁盘的中央区域，把很少访问的逻辑卷放在磁盘的边缘（Edge）区域，其它逻辑卷放在磁盘的中间（Middle）区域。

图7. 硬盘的分布

2.2 裸设备和文件系统

数据库的物理实现对数据库的运行时性能至关重要，物理实现更多的需要考虑硬件和系统平台。整合库部署在一台1900 MHz*4CPU，16GB内存的IBM小型机上，操作系统是IBM AIX 5.3，数据库管理系统是DB2v9.1。

数据库服务器的性能通常是通过事务吞吐量和应用程序响应时间来测量的，该性能主要取决于I/O子系统的性能。为了获得最大可能的I/O吞吐量，数据库管理员和系统管理员需要特别注意数据库表数据的布局。在AIX系统中通常有两种数据存储形式：裸设备、文件系统。裸设备，也叫裸分区（原始分区），是一种没有经过格式化，不被Unix通过文件系统来读取的特殊字符设备。它由应用程序负责对它进行读写操作。不经过文件系统的缓冲。因为使用裸设备避免了再经过AIX操作系统这一层，数据库管理系统直接读写硬盘，所以使用裸设备对于读写频繁的数据库应用来说，可以极大地提高数据库系统的性能。当然，这是以磁盘的I/O非常大，磁盘I/O已经成为系统瓶颈的情况下才成立。如果磁盘读写确实非常频繁，以至于磁盘读写成为系统瓶颈的情况成立，那么采用裸设备确实可以大大提高性能。

DB2数据库有三种表空间：目录表空间、数据表空间和日志表空间。目录表空间和日志表空间均存储在文件系统中，而数据表空间的存储类型是基于实际情况和以往的开发经验来决定。在我们的系统中，在ETL的任何一个阶段都要求高速的磁盘I/O，尤其是数据抽取、匹配存储过程和数据导入整合区存储过程这三个过程，需要大批量的数据读取或写入，磁盘I/O是整合库系统性能的瓶颈。根据经验，数据表空间将采用裸设备的存储方式。于是，整合库的物理实现如下表所示。通常的系统可以采用这种模式。

逻辑结构

DB2表空间

逻辑设备/文件系统

空间大小

备注

目录表空间

SYSCATSPACE

/db2_tag

3GB

文件系统

数据处理区

TBSPTEMP

/dev/rdb2tbsptemplv

/dev/rdb2tbsptemp2lv

10GB

裸设备

代码对应区

TBSPINTE

/dev/rdb2tbspintelv

20GB

裸设备

整合区

日志表空间

－

/intedb_log

20GB

文件系统

2.3 系统管理和数据库管理

图8. 表空间类型比较：系统管理和数据库管理

2.4 如何合理创建表空间设备

2.4.1 原则

一个表空间跨多个物理磁盘，以获得更好的I/O并行性
根据重要性不同，让表空间分布于磁盘的不同位置

ú 索引、重要数据位于中心

ú 将次重要数据、文件系统放在中间和边缘

2.4.2 表空间容器的创建方法

按以下说明创建逻辑卷，并根据设备的重要性依次创建（首先创建最重要的），可以保证最重要的位于中央，按重要性依次往外排列，可以做到读写性能最优。

1. smitlv

2. 选择多个物理盘（PHYSICAL VOLUME names），界面如图9所示。移动光标，用ESC+7选择多块物理磁盘PV。

图9. 创建逻辑卷时选择多个物理盘

3. 在Logical volume TYPE中输入raw，即裸设备类型。

4. 物理盘区域（POSITION on physical volume）选择center，将范围（RANGE of physical volumes）选择最大（maximum）。如图10所示。

图10. 使逻辑卷位于中央

2.4.3 整合数据库表空间设备情况

整合数据库在创建表空间设备时完全遵循了2.4.1节中的原则。

在创建逻辑卷时按照图9所示创建，保证了一个设备跨越多个物理磁盘（尽可能多），以获得更好的I/O并行性。

并且，严格按照重要性降低的次序作为创建逻辑卷的顺序。图11显示的是整合数据库在磁盘hdiskpower8上的设备创建情况。由图中看出，整合数据库严格遵循了设备按照重要性分布的原则：

ú 索引、重要数据位于中心

ú 将次重要数据、文件系统放在中间和边缘

图11. 设备按照重要性分布

2.5 建库语句参考

综合查询系统数据库部分建库语句：

---创建数据库在一个2G的文件系统/anly_tag

db2 create database anly on /anly_tag alias anly using codeset gbk territory cn collate using system

---创建bufferpool

db2 connect to anly user db2admin

db2 create bufferpool TMPBF32K immediate size 32000 pagesize 32k

---创建数据表空间（8G*4）并指定相应的bufferpool

db2 create tablespace TBSPANLY pagesize 32 k managed by database using (device '/dev/ranlytbsplv' 262144) bufferpool TMPBF32K

db2 alter tablespace TBSPANLY add(device '/dev/ranlytbsplv2' 262144)

db2 alter tablespace TBSPANLY add(device '/dev/ranlytbsplv3' 262144)

db2 alter tablespace TBSPANLY add(device '/dev/ranlytbsplv4' 262144)

---创建系统临时表空间（2G的文件系统/anly_tmp/tmp）并指定相应的bufferpool

db2 create system temporary tablespace TBSPTMP32K pagesize 32k managed by system using ('/anly_tmp/tmp') extentsize 8 prefetchsize 16 bufferpool TMPBF32K

----修改日志文件大小为25000*4K

db2 update db cfg for anly using LOGFILSIZ 25000

db2 update db cfg for anly using LOGPRIMARY 20

db2 update db cfg for anly using LOGSECOND 50

db2 update db cfg for anly using NEWLOGPATH /anly_log

----修改表空间大小

db2 alter tablespace tbsptemp drop (device '/dev/rdb2tbsptemp2lv')

db2 alter tablespace tbsptemp resize (device '/dev/rdb2tbsptemp3lv' 327680)

3 缓冲池和表空间

3.1 缓冲池

通常，为每一页面大小的系统临时表空间建立一个缓冲池就足够了。因为在缓冲池管理方面，我们完全可以信任DB2。如果想在这方面继续提高性能，则可以参考本节的文字。

DB2在其缓冲池的自我调优方面十分擅长，并且会将经常被访问的行放入内存，因此一个缓冲池就足够了。（这一选择也避免了管理多个缓冲池的复杂性。）

如果时间允许，并且需要进行改进，那么可能需要使用多个缓冲池。其思想是将访问最频繁的行放入一个缓冲池中。在那些随机访问或者很少访问的表之间共享一个缓冲池可能会给缓冲池带来“污染”，因为有时候要为一个本来可能不会再去访问的行消耗空间，甚至可能将经常访问的行挤出到磁盘上。如果将索引保留在它们自己的缓冲池中，那么在索引使用频繁的时候（例如，索引扫描）还可以显著地提高性能。

这与我们对表空间的讨论是紧密联系的，因为要根据表空间中表的行为来分配缓冲池。如果采用多缓冲池的方法，采用4个缓冲池比较合适：

1. 一个中等大小的缓冲池，用于临时表空间。

2. 一个大型的缓冲池，用于索引表空间。

3. 一个大型的缓冲池，用于那些包含经常要访问的表的表空间。

4. 一个小型的缓冲池，用于那些包含访问不多的表、随机访问的表或顺序访问的表的表空间。

3.2 缓冲池大小

缓冲池大小需要综合考虑系统内存和服务器工作负载情况而定。通常，OLTP类型分配75%的可用内存；OLAP类型分配50%的可用内存，将剩下的50%分配给SORTHEAP。

缓冲池对应于系统的内存空间，所以千万不要为缓冲池分配多于所能提供的内存，否则就会招致代价不菲的OS内存分页。通常来讲，如果没有进行监控，要想知道一开始为每个缓冲池分配多少内存是十分困难的。对于OLTP类型的工作负载，一开始将75%的可用内存分配给缓冲池比较合适。对于OLAP/DSS，经验法则告诉我们，应该将50%的可用内存分配给一个缓冲池（假设只有一种页面大小），而将剩下的50%分配给SORTHEAP。

3.3 表空间页大小

在页大小足以容纳一条记录的前提下，随机更新操作，倾向于用较小的页大小；而对于一次访问大量行的操作，较大的页大小可以提供更好的性能。

为了创建一个表，必须有一个表空间，其页面大小应足以容纳一行。您可以选择使用 4、8、16或32KB这几种页面大小。有时候必须使用较大的页面大小，以回避某些数据库管理器的限制。例如，表空间的最大尺寸与表空间的页面大小成比例。如果使用4K的页面大小，那么表空间的大小（每个分区）最大是64GB，如果使用32K的页面大小，那么最大是512GB。

对于执行随机更新操作的OLTP应用程序，采用较小的页面大小更为可取，因为这样消耗的缓冲池中的空间更少。

对于要一次访问大量连续行的OLAP应用程序，通常使用较大页面大小效果会更好些，因为这样可以减少在读取特定数量的行时发出的I/O请求的数量。较大的页面大小还允许您减少索引中的层数，因为在一页中可以保留更多的行指针。然而，也有例外情况。如果行长度小于页面大小的255分之1，则每一页中都将存在浪费的空间，因为每页最多只能有255行（对于索引数据页不适用）。在这种情况下，采用较小的页面大小或许更合适一些。

3.4 系统临时表空间

为每种页面大小的表空间，建立一个系统临时表空间，通常使用文件系统，大小根据排序和重组事物的大小确定。

对于所使用的每种页面大小，必须存在一个具有匹配页面大小的系统临时表空间（以支持排序和重组）。然后将所有享用匹配页面大小的表空间指派给具有相同页面大小的缓冲池。

如果对性能还有更苛刻的要求，并且有时间投入，那么可以使用DMS表空间，并且根据使用情况来组织表。另外，还要遵循前面给出的关于使用多个缓冲池的建议。对于每种页面大小，创建一个：

1. 系统临时表空间。

2. 用于索引的常规表空间。

3. 用于频繁访问的表的常规表空间。

4. 用于访问不多的表、随机访问的表以及顺序访问的表的常规表空间。

5. 用于 LOB 数据的大型表空间。

3.5 区段大小和预取大小

区段大小（extent size）和预取大小（prefetch size）是表空间的参数，对于数据存取性能至关重要。

区段大小跟表空间中表的平均大小有一定的比例关系，通常表越大，区段大小也越大。如果表空间驻留在磁盘阵列上，则区段大小应设置成条纹大小。

预取大小通过公式确定：prefetch size = (#containers of the table space on different physical disks) * extent size。

磁盘阵列的情况：prefetch size = extentsize * (# of non-parity disks in array)。

3.5.1 区段大小

extent size指定在跳到下一个容器之前，可以写入到一个容器中的PAGESIZE页面的数量，这个参数是在创建表空间时定义的（之后不能轻易修改）。处理较小的表时，使用较小的区段效率会更高一些。

下面的经验法则是建立在表空间中每个表的平均大小的基础上的：

1. 如果小于25MB，extent size为8

2. 如果介于25到250MB之间，则extent size为16

3. 如果介于250MB到2GB之间，则extent size为32

4. 如果大于2GB，则extent size为64

对于OLAP数据库和大部分都要扫描（仅限于查询）的表，或者增长速度很快的表，应使用较大的值。

如果表空间驻留在一个磁盘阵列上，则应将区段大小设置成条纹大小（也就是说，写入到阵列中一个磁盘上的数据）。

3.5.2 预取大小

对于预取大小，可以通过使用ALTERTABLESPACE轻易地修改。最优设置差不多是这样的：prefetch size = (# containers of the tablespace on different physical disks) * extent size。

如果表空间驻留在一个磁盘阵列上，则设置如下：prefetch size = extent size * (# of non-parity disksin array)。

4 提高SQL语句执行效率

4.1 建立索引

使用索引实现关键数据的高效访问。但是需要知道每个索引都会给数据库更新带来额外的开销。这就意味着，低效的索引会给数据库带来灾难。

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

在建立索引前线介绍一些最通俗的信息，这些信息来自developWorks，列出这些信息是因为我觉得这些信息通常情况下是值得参考的：

1. 当要在一个合理的时间内结束查询时，应避免添加索引，因为索引会降慢更新操作的速度并消耗额外的空间。有时候还可能存在覆盖好几个查询的大型索引。

2. 基数较大的列很适合用来做索引。

3. 考虑到管理上的开销，应避免在索引中使用多于5个的列。

4. 对于多列索引，将查询中引用最多的列放在定义的前面。

5. 避免添加与已有的索引相似的索引。因为这样会给优化器带来更多的工作，并且会降慢更新操作的速度。相反，我们应该修改已有的索引，使其包含附加的列。例如，假设在一个表的 (c1,c2)上有一个索引i1。您注意到查询中使用了"wherec2=?"，于是又创建一个(c2)上的索引i2。但是这个相似的索引没有添加任何东西，它只是i1的冗余，而现在反而成了额外的开销。

6. 如果表是只读的，并且包含很多的行，那么可以尝试定义一个索引，通过CREATE INDEX中的INCLUDE子句使该索引包含查询中引用的所有列（被INCLUDE子句包含的列并不是索引的一部分，而只是作为索引页的一部分来存储，以避免附加的数据FETCHES）。

对于数据仓库（查询系统数据库）可以建立较多的索引（索引和数据的比例可以是1：1）。

决定是否使用索引，可以重点考虑检索比率。即，判断索引有效性的依据，就使用键值作唯一性条件检索出的数据的百分比。百分比越低，索引越有效。做出这个论断的前提是一些假设，如磁盘访问的相关性能。

索引键值相关记录的物理位置是否相邻也很重要，因为是通过块来操作数据的。建立了索引之后，如果索引键所指向的记录散布于整个表中，即使这些记录在表中占的比率很小，但因为它们分散在整个磁盘上，所以索引的性能就会大打折扣。

另外值得注意的是，函数和类型转换可能导致索引失效。

4.2 采用表分区

DB2 V9新增了表分区功能，分区技术可以改进大型数据库的管理。

每个表分区物理独立。

分区键的选择：让查询很快定位，但尽量避免数据库操作集中；使海量数据表拆分成“小表”，并使数据库操作平均分散到表分区中。

分区在单节点数据库上，提高查询定位的速度，不提供查询并行性。

DB2 V9新增了表分区功能，分区技术可以改进大型数据库的管理。DB2 V9新增了表分区功能，因此对一些大表，我们在DB2 V9中不再需要分拆成小表，再用UNION ALL视图的方式进行设计，而是直接用分区表实现一些这些功能。表分区功能是一种数据组织方案，即，表数据根据一个或多个表列中的值分布到多个存储对象（称为数据分区或范围）中。每个数据分区都是单独存储的。这些存储对象可以在不同的表空间中，也可以在相同表空间中。

很多业务数据的查询包含时间条件，所以时间经常作为分区条件。业务数据在时间上的分布通常是平均的，所以用时间分区的另一个好处是使包含大型数据的表分成大小相当的几个分区，用户还可以针对数据量来制定分区，以控制每个分区的数据量在一个可以接受的范围内。在外汇管理局统计分析子系统中，用户的每一次查询均包含时间条件，所以按时间分区是一个很好的选择。外汇局国际收支数据明细表每年有2000万数据，使用时间一长，数据量将非常之大。明细数据按月分区，可以将每个分区的数据量控制在200万左右，在查询时控制时间条件在一年内，这样可以避免查询时全表扫描带来的压力。

但是按照时间分区也有弊端。一个业务系统，按时间按分区，当前时间下的分区插入、删除、更新或查询的操作必定比别的分区多。这种集中的操作必定给数据库带来巨大的压力。

外汇管理局统计分析子系统还有另外一个特点：用户按照行政区划分散，查询时只关心所在行政区划的数据。我们就有一种构想：可否按照时间和行政区划两个条件来分区（按月、按省分区）？这一想法刚刚产生，还未执行，接下来打算测试。

分区键的选择：让查询很快定位，但尽量避免数据库操作集中；使海量数据表拆分成小表，并使数据库操作平均分散到表分区中。

对于分区，我们进行了一些列测试，下面就是我们测试的结论。分区在单节点数据库上，提高查询定位的速度，不提供查询并行性。以一年的数据量，月分区为例。首先，考虑查询条件中有分区字段时的情况。当查询条件在一个月内时，DBMS可以直接定位到某个分区，在不考虑其他条件时，查询速度是不分区表的12倍。依此类推，当查询条件跨N（N<=12）个月时，查询速度是不分区表的12/N倍。如果，查询条件中没有分区字段时，分区和不分区查询效率相当。

4.3 运行统计和重组

优化器需要知道索引的相关信息，来判断查询执行的“路径”，运行统计可以告诉优化器。经验：当表中数据量变化达到10%时，需要重新运行统计。

在许多次的更新之后，表会成为碎片状，这导致性能严重下降。若收集了统计信息，但看不出有明显的性能改进，则重组表数据可能会有帮助。重组表数据时，根据指定的索引重新安排数据的物理顺序，并除去碎片数据中的空闲空间。这使该数据可以更快速的被存取，从而改进性能。

4.3.1 通过运行统计来收集索引信息

运行统计（runstats）的作用是收集索引的统计信息，如果数据库没有收集索引的统计信息，优化器就无从下手，只能按部就班，通过全表扫描来执行查询。所以，新创建的索引需要重新运行统计，否则索引无效。

举一个例子，有个表TABLE1，其中有一个字段COL1取值是“1”、“2”、“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”，

数据库会优先选择COL2上的索引，因为对于语句2的查询条件COL2上的索引具有更好的区分度。

从上面可以看出，数据库的优化器通常会优先选择区分度较高的索引（针对于查询条件，条件不同选择的索引可能不同）。

数据库里的数据是变化的，所以某个时候采集的统计信息，过一段时间后可能会过时，甚至误导DB2优化器，这样同样会造成运行性能的低下。所以除了，最初建立索引时需要运行统计，在表中的数据发生变化时也需要运行统计。经验：当表中数据量变化达到10%时，需要重新运行统计。

4.3.2 存储过程里执行运行统计语句

运行统计的语句如下：runstats on table DB2ADMIN.TABLE withdistribution and detailed indexes all;

在存储过程中不能直接执行runstats语句，可以通过调用ysproc.admin_cmd来实现，具体语句如下：

callsysproc.admin_cmd('runstats语句');

下面用一个存储过程封装了此过程，在需要对一个表运行统计时，只需要调用pr_runstats存储过程即可。

/****************************************************************

* Name: pr_runstats

* Description: 运行统计表

* Creator: 赵坚密

* Creation date: 2008-12-30

* Parameters: 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);

declare 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_TABLE_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@

4.3.3 重组表中的数据

重组（reorg）操作会重新排列表数据的物理顺序，并除去碎片数据中的空闲空间。

由于删除操作不释放磁盘空间，在执行删除操作后，表会成为碎片状，这导致性能严重下降，在多次更新操作之后也会出现这种情况。若收集了统计信息，但看不出有明显的性能改进，则重组表数据可能会有帮助。重组表数据时，根据指定的索引重新安排数据的物理顺序，并除去碎片数据中的空闲空间。这使该数据可以更快速的被存取，从而改进性能。

4.4 减少对数据库的更新和删除操作

删除和更新操作的开销往往比插入高，所以一个好的设计需要减少对数据库的更新和删除操作。

4.4.1 更新操作

数据库的更新操作会带来一连串的“效应”：更新操作需要记录日志（以便错误时回滚）；更新可变长字段（如，varchar类型）会带来数据物理存储的变化（记录的移动）；更新索引字段会导致索引重建；更新主键会导致数据重组等。这一切不但会造成更新操作本身效率低，而且由于磁片碎片的产生会造成以后查询性能的降低。为了应对这一情况，有两种策略：一、减少更新次数，把多个字段的更新写到同一个语句里；二、避免更新。这两种策略分别适用于不同的情况，下面将举例说明两种情况。

4.4.1.1减少更新次数

在整合库里有个代码清洗过程，就是通过连接代码表给业务数据的自编码字段赋值。代码清洗其实是通过关联代码表来更新业务数据表的一个过程，需要连接多个代码表，更新多个自编码字段。完成此更新，有两种更新语句的写法：一种是写成多个SQL语句，每个语句更新一个自编码字段；另一种写法是将所有更新写在一个语句中。更新银行代码的更新语句如下所示：

updateTBL_INCOME_TMP A

setBANKCODESELF = (

select SELFCODE

from

TBL_BANKINFO B

where A.BANKCODE = B.BANKCODE )

通过一个更新语句实现多个自编码字段更新的语句示意如下：

updateTBL_INCOME_TMP

set 代码1自编码 = 通过关联代码1表得到自编码，

代码2自编码 =通过关联代码2表得到自编码，

...，

代码n自编码 =通过关联代码n表得到自编码

利用两千万的测试数据。两种方法的测试结果如下表所示。从测试结果看出，一次更新方法性能提高了十倍，大大提高了性能。

处理过程

多次更新方法耗时

一次更新方法耗时

代码清洗

0:29:48

0:02:59

4.4.1.2避免更新

下面举个通俗的例子，这类情况是经常遇到的。某公司有一套系统员工考勤系统，为了提高查询统计的性能，在原有系统基础上建立了一些包含冗余信息的表。以员工表为例，它获得数据的过程如图12所示。第一步把员工信息放到新表中，然后连接通过字段“部门ID”连接更新“部门名称”。

图12. 关联更新

一般，为了节省存储开支把部门名称这样的字段设计成可变长的。所以在对它进行更新时会造成磁盘数据的重新组织，形成磁盘碎片，影响查询性能。

为了避免这样的情况发生，我们可以使用如图13所示的方法避免更新。这种方法一步完成了冗余数据表的插入，再插入时连接部门表获得“部门名称”，从而避免了更新操作。

图13. 避免更新

4.4.2删除操作

初学者可能认为删除操作很简单，可以快速完成。其实这是一个错误的理解，删除过程需要大量扫描磁盘；需要记录数据库日志；而且删除过程不释放磁盘空间，浪费磁盘，并且使磁盘上的数据支离破碎，这对后续查询的性能是一个致命的打击。通常用两种方式来应对：一、对经常做删除操作的表进行重组（reorg）；二、避免删除。

4.4.2.1重组

见4.3.3节。

4.4.2.2避免删除——中间表和正式表模式

在数据需要比较复杂的处理的时候经常会用到中间表和正式表模式。数据在中间表中被处理，然后把满足条件的数据转移至正式表，不满足条件的数据保留在中间表中。图14示意了数据从中间表转移到正式表的过程：在完成数据处理之后，需要把中间表temp1中flag = 1的数据插入到正式表，并删除中间表temp1中flag = 1的数据。

图14. 从中间表向正式表转移数据

因为flag字段不是聚簇索引，所以当对中间表temp1进行删除后，会再磁盘中留下大量碎片，如图15所示。不但会留下那么多的磁盘碎片，而且已删除的数据的空间也不会自动释放。结果是不但浪费磁盘空间，而且查询性能会急剧下降。

图15. 删除操作后的磁盘碎片

咱们可以使用清空表的命令来避免删除操作。除了中间表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

4.4.2.3存储过程里实现表清空

在存储过程里不能直接执行表清空语句来清空表，但是可以通过以下命令来实现：

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 income_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 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||' activate not logged initially with empty table';

EXECUTE IMMEDIATE str_sql;

call logNote('truncat_table', '清空表'||IN_TABLE_NAME||'中的记录完成', 0, '0');

commit;

end@

4.5 如何使访问更高效

本小节的内容很大一部分来自《TheArt of SQL》这本书，这本书里集合了数据库开发的通用经验。虽然没有局限于具体的DBMS和硬件平台，但是却是一本实践性很强的书。

1．一次连接数据库，做很多事情。直到处理完，才断开连接。

2．一个SQL语句包含尽量多的操作。形象地说：几千个语句，借助游标不断循环，很慢。换成几个语句，处理同样的数据，还是很慢。换成一个语句，解决问题，最好。

3．接近DBMS核心。尽量使用数据库自带的函数。减少自定义函数。因为再聪明的数据库优化器也不认识自定义函数。

4．一个语句不要连接太多的表，建议的上限是5个。

5．将频繁更新的列集中起来：当更新某一行时，DB2 会记录进行更改的所有列，因此将频繁更新的列放到一起可以减少 DB2 的记录工作。这只是一个有关性能的小建议，因此不应为实现它而进行重大的应用程序或数据库设计修改。

6．如果想了解，如何书写SQL语句才能更高效，可参考《TheArt of SQL》。书中提出了不少有益的见解。

5 参数调优

关于数据库服务器的参数调优包括，操作系统参数、数据库管理系统参数和数据库参数的调优。其中操作系统调优的核心在于AIX系统的内存和CPU的调优，旨在提高数据库对内存和CPU的利用率。DB2数据库的调优分为3个方面：DB2环境变量、DB2实例参数和数据库参数。旨在提高数据库的并行性、读写性能、排序和汇总性能。

参数设置和参数调优通常根据经验确定，以下小节介绍的是整合库的参数设置。如想了解综合查询系统数据库，可参看《查询库性能调优》的参数调优部分。

5.1 AIX系统

vmo -o maxclient%=20

vmo -o maxperm%=20

vmo -o minperm%=5

这三个参数是AIX内存管理相关的。下面介绍这三个参数。

增强JFS文件系统为它的缓冲区高速缓存使用客户机文件。为了在增强JFS文件系统的高速缓存中建立硬性限制，可以调谐maxclient的参数。该参数代表了可用于缓冲区高速缓存的客户机页面的最大值。可以使用vmo -o maxclient命令进行更改。maxclient的值显示为实内存的百分比。

在到达maxclient阈值后，LRU开始取用最近尚未访问过的客户机页面。如果没有足够的客户机页面可以被窃取，LRU会替换掉其它类型的页。通过减少maxclient的值，可以阻止增强JFS文件页的访问不要替换掉工作存储页面，最小化调页空间的页面调度。另外，maxclient通常应该设置为一个小于或等于maxperm的值。

操作系统通过把在曾经读写的内存页留在实内存满足不同要求。如果文件页面在它们的页帧被重新分配前被请求，那就节省了输入输出操作。

页帧使用的文件相对用于计算的（工作或程序文本）段的文件的比例是松散地受控于minperm和maxperm的值的：

如果RAM中文件页面所占的百分比高于maxperm，页面替换的窃取只用于文件页。

如果RAM中文件页面所占的百分比低于minperm，页面替换的窃取同时用于文件页和计算页。

如果RAM中文件页面所占的百分比介于minperm和maxperm之间，页面替换只窃取文件页，除非文件页的重调入数量大于计算页数。

5.2 DB2环境变量

db2set DB2_PINNED_BP=YES

db2set DB2_FORCE_FCM_BP=NO

db2set DB2MEMMAXFREE=1048576

db2set DB2_MMAP_WRITE=NO

db2set DB2_MMAP_READ=NO

db2set DB2_HASH_JOIN=Y

db2set DB2_RR_TO_RS=ON

慎用，请详细了解后再使用以下参数：

DB2_EVALUNCOMMITTED=YES

DB2_SKIPDELETED=ON

DB2_SKIPINSERTED=ON

DB2_PARALLEL_IO=YES

DB2_PINNED_BP：这个AIX和HP-UNIX平台上的变量指定是否数据库使用的全局内存（包括缓冲池的内存）将被保留在系统主存储器以获得更加稳定的数据库性能。

DB2MEMMAXFREE：该变量指定每个DB2代理将保留可以用内存的数量；取值范围从0到2.0e+32字节。缺省是8,388,608个字节。

DB2_HASH_JOIN：指定排序时使用HASH排序，这样db2在表join时，先对各表做hash排序，再join，这样可以大大提高性能。

DB2_RR_TO_RS：该设置后，不能定义RR隔离级别，如果定义RR，db2也会自动降为RS。通过不锁定插入或者更新行的下一个键，可以改进并发性，提高性能。

DB2_EVALUNCOMMITTED：这个参数将在记录锁之前进行谓词检查，尽量减少锁的时间。对于V8，从V8.1 FP9（也即V8.2 FP2）开始，最佳的设置是=YES_DEFERISCANFETCH。对于 V9，只需指定=YES。

DB2_SKIPDELETED：这个参数将新insert且没有提交的数据跳过；例如，SELECT/UPDATE语句不会发现这条记录。

DB2_SKIPINSERTED：这个参数将新delete且没有提交的数据跳过；例如，SELECT/UPDATE语句不等待这条记录的提交，并且认为他已经被删除了。

DB2_PARALLEL_IO：允许表空间的并行I/O。

5.3 DB2数据库实例参数

db2 update dbm cfg using FEDERATED YES

db2 update dbm cfg using sheapthres 200000

db2 update dbm cfg using ASLHEAPSZ 120

db2 update dbm cfg using MAXAGENTS 200（连接进程数）

db2 update dbm cfg using NUM_POOLAGENTS 30

FEDERATED：数据库联邦。

sheapthres：排序堆阈值。

ASLHEAPSZ：

MAXAGENTS：指定可在任何给定时间接受应用程序请求的数据库管理器代理进程的最大数目。

NUM_POOLAGENTS：确定空闲代理进程池的最大大小。

5.4 DB2数据库参数

db2 update db cfg for database_name using PCKCACHESZ 2048

db2 update db cfg for database_name using SORTHEAP 10000（排序堆大小）

db2 update db cfg for database_name using APPLHEAPSZ 4096

db2 update db cfg for database_name using MAXLOCKS 30

db2 update db cfg for database_name using DFT_DEGREE 4

db2 update db cfg for database_name using DBHEAP 8192

db2 update db cfg for database_name using LOGBUFSZ 256

db2 update db cfg for database_name using DFT_PREFETCH_SZ 64

db2 update db cfg for database_name using LOCKLIST 5000

db2 update db cfg for database_name using NUM_IOCLEANERS 5

6 开始行动

6.1 用topas监控硬件使用情况

在IBM AIX操作系统中，可以用topas命令来监控CPU、硬盘、内存、网络等硬件使用率和使用情况。总结几点：

1. 执行数据库操作时，少量硬盘的使用率达到百分之百，大量硬盘处于空闲，并有读写等待现象。说明磁盘的并行性不好，需要通过合理分配磁盘来优化。

2. 少量CPU使用率达到百分之百，大量CPU处于空闲。说明应用的并行度不够高。

3. CPU大量处于I/O等待，是不理想的状态，需要优化磁盘I/O。

6.2 从执行时间来确定主要矛盾

这里的执行时间可以是一个DB2执行一个SQL语句的时间，也可以是一个存储过程的时间。

从执行时间来定位最需要优化的部分，是一个很好的选择。因为，执行时间长的过程（指SQL语句、存储过程等），通常意味着优化后效果更明显。试想，有两个过程，分别执行这两个过程，一个用了一个小时，一个用了十分钟。我们试图优化这两个过程，通过相同的努力使两个过程性能都提高了10%。这样，第一个过程执行时间缩短了6分钟，第二个过程缩短了1分钟。可以看出，付出相同努力的情况下第一个过程收效是第二个过程的6倍。所以，我们优先优化执行时间长的过程，如果需要进一步优化，再去优化那些执行时间较短，还有优化余地的过程。

DB2没有较好的统计执行时间的可视化工具，所以，用户通常需要自己来实现并记录各个过程的执行时间。下面介绍一种“土方法”。在数据库里创建一个日志表，建表语句如下：

create table TBL_PRO_LOG

(

ID INTEGER,

PRO_NAME CHAR(50),

DESCRIPTION VARCHAR(255),

PRO_TIME TIMESTAMP,

SQL_CODE INTEGER,

SQL_STATE VARCHAR(200),

constraint P_Key_1 primary key (ID)

);

同时创建一个记录日志的存储过程，用户需要时可以通过调用此存储过程来记录被监控过程的起始时间、完成时间和错误日志等。通过查看日志表，就可以统计过程执行的时间。在异常时调用此存储过程，可以掌握系统出错信息，能更好的定位错误，便于错误处理。

/****************************************************************

* Name: logNote

* Description: 输出日志，大多在其他存储过程中调用,往TBL_PRO_LOG里insert列。

* Creator: 陈伟

* Creation date: 2007-10-22

* Parameters:description，sCode，sState

* Input：description，sCode，sState Output:TBL_PRO_LOG

* ---------------------------------------------

* db2 connect to inte user db2admin using db2admin

*编译过程

*db2 -td@ -vf logNote.db2

*运行过程

*db2 call logNote()

* -------------------------------------------------------------

***************************************************************/

drop procedure logNote(CHAR(50),VARCHAR(255),INTEGER,CHAR(5))@

create procedure logNote(tblname char(50),description VARCHAR(255),sCode INTEGER,sState CHAR(5))

language sql

begin

insert into TBL_PRO_LOG(description,tbl_name ,Pro_Time, sql_code, sql_state)

values(description, tblname,current timestamp, sCode, sState);

commit;

end@

通过以上方法，咱们就可以统计各个过程的执行时间，并且可以挑选出执行时间较长的过程来作为优化的“重点关注对象”，而进入观察阶段。

6.3 进一步观察以决策

上一节确定了一些“重点关注”的过程，用来作为观察的对象，以作出是否需要优化这一决断。本节将介绍DB2的几种工具，用来查看SQL语句的执行情况。

6.3.1 DB2Visual Explain

DB2提供了非常直观有效的方法来查看查询的存取计划。DB2 Visual Explain能够获得可视化的查询计划，而DB2expln命令则可以获得文本形式的查询计划。有了查询计划，我们就可以有针对的对查询进行优化。根据查询计划找出代价最高的扫描（表扫描，索引扫描等）和操作（Join，Filter，Fetch等），继而通过改写查询或者创建索引消除代价较高的扫描或操作来优化查询。

DB2提供了多种方法来得到可视化查询计划。

通过DB2Control Center获得可视化查询计划。如图12：

图12. 可视化查询计划

点击“ExplainSQL”后输入要进行分析的查询语句以及查询标号和标签，点击Ok按钮便可得到可视化的查询计划。此时，查询计划会被存储在系统的Explain表中。用户可以通过图12中的“Show Explained Statements History”命令获得存储在Explain表中的所有查询计划。

通过CommandEditor（在DB2 8.2版本之前叫做CommandCenter）获得可视化的查询计划。如图13：

图13. 获得可视化的查询计划

在主窗口输入查询并连接数据库后，点击图中所示的按钮即可得到可视化的查询计划，如图14：

图14. 查询计划结果

在图14所示的查询计划中，还可以点击图示中的每个节点来察看详细的统计信息。譬如双击节点：“FETCH(13) 21,959.75”后将会弹出如图15所示的对话框：

图15. 详细的统计信息

图15中的统计信息主要包括此FETCH操作的总代价、CPU、I/O以及获得结果集中的第一行的代价。在这里，timerons是结合了CPU和I/O代价的成本单位。此外，图15中还收集了其他相关信息。譬如此操作读取了哪个表的哪些列，每个谓词的选择度（selectivity），使用了多少buffer等等。

6.3.2 DB2exfmt

DB2exfmt命令能够将Explain表中存储的存取计划信息以文本的形式进行格式化输出。DB2exfmt命令将各项信息更为直观的显示，使用起来更加方便。命令如下：

DB2exfmt -d <db_name> -e <schema> -g T -o <output> -u <user> <password> -w <timestamp>

Example: DB2exfmt -d test_db -e user -g T -o D:\temp\sql_1_result_DB2exfmt.txt

-u user password -w l

Query: sql_1.txt

Results: sql_1_result_DB2exfmt.txt

6.3.3 DB2expln

DB2expln是命令行下的解释工具，和前面介绍的Visual Explain功能相似。通过该命令可以获得文本形式的查询计划。命令如下：

DB2expln -d <db_name> -user <user> <password> -stmtfile <sql.file>-z @ -output <output> -g

Example: DB2expln -d test_db -user user password -stmtfile D:\temp\sql_1.txt

-z @ -output D:\temp\sql_1_result_DB2expln.txt –g

Query: sql_1.txt

Results: sql_1_result_DB2exfmt.txt

DB2expln将存取计划以文本形式输出，它只提供存取计划中主要的信息，并不包含每一个操作占用多少CPU、I/O、占用Buffer的大小以及使用的数据库对象等信息，方便阅读。但是DB2expln也会将各项有关存取计划的信息存入Explain表中，用户可以使用DB2exfmt察看详细的格式化文本信息。

6.3.4 表快照监视器

这是一个命令行参数（运行db2cmd），具体如下：

1．编目节点和数据库

db2 catalog tcpip node node16 remote 100.1.16.16 server 50000

db2 catalog db intedb at node node16

2．连接到数据库节点

db2 attach to node16 user db2admin

3．使用

db2 update monitor switches using table on

db2 get snapshot for tables on intedb

db2 update monitor switches using table off

用户可以查看到一段时间内（从on开始到查看的时间段），数据库表查询和更新操作的生效记录数。如果，某个表有大量读操作，而写操作很少，则往往需要优化，优化的方法通常是把where条件里的某个字段建立索引。

6.4 不同数据库处于不同实例

一个系统通常有开发环境，性能测试环境，功能测试环境，生产环境等。而且常常存在多个环境在同一台机器上。这些环境除了共用系统资源外，还会互相影响。开发环境，测试环境可能常常需要重启数据库（db2stop、db2start），而这样做会影响到生产环境。我们建立多个实例，将不同环境的数据库建立在相对独立的多个实例里，这样可以尽量避免多个环境相互影响。

6.5 开始行动

性能调优是一个大工程，而且包括了很多、很琐碎的方面，不可能所有的方案都采取，需要针对具体问题进行取舍。进行再多的讨论，对性能调优作用甚微。现在需要做的就是一边尝试着修改，一边监控性能的变化，然后决定采用哪些方案。

那么从现在开始行动吧！

7 参考文献

1. DB2 调优参考.txt

2. AIX 上影响 Oracle 的参数.htm

3. db2参数.htm

4. db2参数调整命令.htm

5. IBM DB2 内存分配与使用策略.htm

6. 基本性能调整.htm

7. 排序堆大小（SORTHEAP）和排序堆阈值（SHEAPTHRES）.txt

8. AIX 中 Paging Space 使用率过高的分析与解决(转载自IBM).htm

9. DB2编程序技巧 (六).htm

10. 管理指南-计划.pdf

11. 管理指南-实现.pdf

12. 管理指南-性能.pdf

13. ANLY建库（DB2 9， IBM AIX 5.3）.doc

14. DB2表分区技术.txt

15. DB2分区功能相关测试.doc

16. 调优 DB2 UDB v8_1 及其数据库的最佳实践.mht

17. DB2 基础表空间和缓冲池.mht

18. aix裸设备.txt

19. 针对基础设计、性能和可管理性的 DB2 最佳实践.mht

20. DB2 for z-OS DB2 数据库设计.mht

21. 关于 DB2 通用数据库中索引覆盖的基本事实.mht

22. 逻辑卷管理1.mht

The Art of SQL

DB2设计与性能优化——原理、方法与实践

8索引

8.1索引与目录

每一本书的前几页一般都是目录，而最后几页通常会有一个关键字索引。对于数据库来讲系统表（如：sysobjects等）就是目录，而标字段上的索引就如同书本后面的关键字索引。

数据库中，目录（数据字典）和索引的区别：目录纵向、索引横向。

8.2影响索引作用的因素

8.2.1区分度（检索比率）

优化器根据统计信息来生成执行计划，如果数据库没有收集索引的统计信息，优化器就无从下手，只能按部就班，通过全表扫描来执行查询。所以，新创建的索引需要重新运行统计，否则索引无效。

“1”－ 12%；

“2”－ 66%；

“3”－ 22%。

假设还有个字段COL2取值和数据所占的百分比如下：

“A”－ 50%；

“B”－ 50%。

则查询语句1：

select * from TABLE1 where COL1 = “1”and COL2 = “A”，

select * from TABLE1 where COL1 = “2”and COL2 = “A”，

数据库会优先选择COL2上的索引，因为对于语句2的查询条件COL2上的索引具有更好的区分度。

从上面可以看出，数据库的优化器通常会优先选择区分度较高的索引（针对于查询条件，条件不同选择的索引可能不同）。

数据库里的数据是变化的，所以某个时候采集的统计信息，过一段时间后可能会过时，甚至误导数据库优化器，这样同样会造成运行性能的低下。所以除了，最初建立索引时需要运行统计，在表中的数据发生变化时也需要运行统计。经验：当表中数据量变化达到10%时，需要重新运行统计。

8.2.2聚集度

范围扫描

8.2.3表大小

小型表

中大型表

超大型表

8.2.4业务类型

OLTP和OLAP

8.2.5函数与索引

函数，like语句。。。

Substring(col_name, 1, 3) vs. Substring(col_name,3, 3)

like ‘QQQ% vs. like ‘%QQQ’

8.3索引开销

性能利器

双刃剑

索引对插入操作的影响（Oracle）

索引对插入操作的影响（MySQL）

比较索引与促发器对性能的影响

8.4索引总结

使用索引实现关键数据的高效访问。但是需要知道每个索引都会给数据库更新带来额外的开销。这就意味着，低效的索引会给数据库带来灾难。

在建立索引前线介绍一些最通俗的信息，这些信息来自developWorks，列出这些信息是因为我觉得这些信息通常情况下是值得参考的：

7. 当要在一个合理的时间内结束查询时，应避免添加索引，因为索引会降慢更新操作的速度并消耗额外的空间。有时候还可能存在覆盖好几个查询的大型索引。

8. 基数较大的列很适合用来做索引。

9. 考虑到管理上的开销，应避免在索引中使用多于5个的列。

10. 对于多列索引，将查询中引用最多的列放在定义的前面。

11. 避免添加与已有的索引相似的索引。因为这样会给优化器带来更多的工作，并且会降慢更新操作的速度。相反，我们应该修改已有的索引，使其包含附加的列。例如，假设在一个表的 (c1,c2)上有一个索引i1。您注意到查询中使用了"wherec2=?"，于是又创建一个(c2)上的索引i2。但是这个相似的索引没有添加任何东西，它只是i1的冗余，而现在反而成了额外的开销。

12. 如果表是只读的，并且包含很多的行，那么可以尝试定义一个索引，通过CREATE INDEX中的INCLUDE子句使该索引包含查询中引用的所有列（被INCLUDE子句包含的列并不是索引的一部分，而只是作为索引页的一部分来存储，以避免附加的数据FETCHES）。

对于数据仓库（查询系统数据库）可以建立较多的索引（索引和数据的比例可以是1：1）。

另外值得注意的是，函数和类型转换可能导致索引失效。

9 SQL语句编写（DB2）

本章来自王鹏飞舞动DB2系列《DB2设计与性能优化——原理、方法与实践》，P242

9.1谓词

首先要知道，不合理的谓词会限制优化器对索引和连接方法的选择。设计谓词时要注意下面的原则。

（1）保证选择谓词足够简洁。选择谓词要尽量采用简单的形式，如：列名 = 常数表达式，这样便于匹配索引。还应该避免使用类型转换，如果有类型转换应该显式地写出转换函数，并放在表达式的常数一边，例如：

cast( colum_char10 as int ) = 100

应该写成：

colum_char10 = cast(100 as char(10) )

另外，使用参数标记（ParameterMarker）时，要尽量利用Cast函数表明它的类型，以免产生不必要的类型转换。

同样，下面的谓词都应该写成更优化的形式：

XPRESSN(C) = 'constant'

INTEGER(TRANS_DATE) / 100 = 200802

WHERE (CUST_ID * 100) + INT(CUST_CODE) = 123456 ORDERBY 1, 2, 3

它们的正确形式应该为：

C = INVERXPRESSN( 'constant' )

TRANS_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。对于等式谓词要做到等式两边的列名长度相等。如果是Varchar，Char类型，它们定义的长度应该相等，如果是Decimal类型，它们的精度应该相同。

（3）不要使用多余的谓词。多余的谓词不但增加了系统的计算代价，而且会导致中间结果的估算不准确，并产生较差的访问计划。例如对于下面的谓词，（COL_0MONTH = 199903）实际上是多余的，应该去掉。

( COL_0MONTH = 199705

OR COL_0MONTH= 199805 )

AND NOT

( COL_0MONTH = 199903 )

9.2多余的连接

有些连接看似合理，但也许是多余的。连接会涉及对表的多次扫描，去掉多余的连接，就会减少不必要的开销。比如，下面的查询：

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

wherevalue in (1, 2)

group byid

havingcount(*) = 2) as Q1

where T1.id = Q1.id

这样减少了对T2表的一次连接，大幅度提高了查询效率。

9.3子查询

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

select *

from T1

where exists(select * from T2 where T1.c1 = T2.c1)

可以转换为，

select distinct T2.c1, T1.*

from T1, T2

where T1.c1 = T2.c1

注意除非T2.c1有唯一性（unique），否则关键词distinct 是必需的，这是为了保证最终结果和原来的SQL一致。

9.4外连接

要避免不必要的外连接（OuterJoin）。Outer Join会限制连接的顺序，从而导致一些较好的计划无法生成。因此要尽可能避免使用Outer Join，无论是Left、Right或者Full Outer Join。

9.5 UNION ALL的使用

在使用UnionALL时，我们要注意UNION ALL之上的谓词是否被下推。可以查看Optimized Statement，看看是否每个选择谓词都被下推到相应的子查询中。如：

select *

from (select * from T1

union all

select * from T2) as V1

where c1 between 2000 and 2009

在OptimizedStatement中应该看到选择谓词c1 between 2000 and 2009被下推到Union All里面变成：

select *

from (select * from T1 where c1 between 2000 and 2009

union all

select * from T2 where c1 between 2000 and 2009) as V1

如果没有下推，可能由于选择谓词过于复杂，或者DB2的查询重写器无法判定下推谓词是否会提高效率。这时候如果确信下推后会产生较好的计划，可以手动改变SQL的书写形式。还可以考虑适当提高优化级别，使原来没有下推的谓词被下推。

9.6 Having子句

检查Having子句中的谓词是否可以下推。应尽可能把Having子句中的谓词。一般情况下，根据一定的规则，Having子句如果不含有聚合函数，会经过逻辑优化被下推到Where子句里面，但也会出现不下推的情况。为了避免出现没有下推的情况，在编写SQL是就应尽量将能下推的谓词写在Where子句里面。

9.7 OFNR和FFNR子句

OFNR（OPTIMIZE FOR N ROWS）子句使优化器选择那些执行时能最快得到前N行结果的访问计划，不过查询仍将返回完整的结果集。FFNR（FETCHFIRST N ROWS ONLY）子句显示查询结果只需返回N行，这样减少了返回结果集。根据应用程序的需要使用这两个子句可以提高SQL语句的性能。

注意DB2不会因为查询指定了FETCH FIRST N ROWS ONLY而选择返回前N行结果最快的访问计划。所以应该在使用FETCH FIRST N ROWS ONLY时，同时使用OPTIMIZE FOR N ROWS。

另外注意，如果应用程序要获取整个结果集，但却指定OPTIMIZE FOR N ROWS，可能会使性能降低。这是因为快速返回前N行的访问计划并不一定是对于获取整个结果集最佳的访问计划。

9.8使用参数标记

DB2可以通过在动态语句高速缓存中保存访问计划和SQL文本，来避免重复编译一个已编译过的动态SQL语句。然而，只要谓词在字面上有一点不同，这个语句与高速缓存中类似的SQL就无法匹配。例如，下面两个语句在动态语句高速缓存中会被看做不同的语句。

SELECT EMP_ID, EMP_NAME AGE FROM MANAGER_INFO WHEREEMP_ID = 918233

SELECT EMP_ID, EMP_NAME AGE FROM MANAGER_INFO WHEREEMP_ID = 920122

应该考虑把上述语句改成使用参数标记把谓词常数值传递给DB，而不要显式地在SQL语句中包含它。不过注意，复杂的查询如果使用参数标记，得到的访问计划可能不是最优的。