《大数据量数据整合与查询性能优化(IBMAIX5.3)v1.0.doc》由会员分享，可在线阅读，更多相关《大数据量数据整合与查询性能优化(IBMAIX5.3)v1.0.doc（27页珍藏版）》请在三一办公上搜索。
1、大数据量数据整合与查询性能优化中软国际Jimmy（zhaojianmi）本文适用操作系统平台为IBM AIX 5.3，也可用于一般UNIX平台；数据库为IBM DB2，版本9.1。文中引用了笔者在金宏工程外汇管理局整合数据库、统计分析系统和综合查询子系统的开发经验。本文只考虑单一节点的数据库，没有讨论数据库集群。阅读说明本文主要面向数据库设计和开发人员、部署人员和性能调优人员。在项目的初始阶段（设计初期或更早），建议阅读本文的所有内容。在项目的后期（开发阶段或部署阶段），建议阅读的部分：3 缓冲池和表空间3.1 缓冲池3.2 缓冲池大小3.3 表空间页大小及容器分布（容器分布部分）3.4 系统
2、临时表空间3.5 区段大小和预取大小3.5.2 预取大小4 提高SQL语句执行效率4.1 建立索引4.3 减少对数据库的更新和删除操作4.4 如何使访问更高效5 参数调优5.1 AIX系统5.2 db2环境变量5.3 db2数据库实例参数5.4 db2数据库参数6 结论 赵坚密，完成于2009-2-12凌晨备忘：2008-2-13下午4点，于融科参加技术交流会目录大数据量数据整合与查询性能优化- 1 -中软国际Jimmy（zhaojianmi）- 1 -阅读说明- 1 -目录- 2 -1 DB2数据库- 4 -1.1 数据库对象- 4 -1.1.1 实例- 5 -1.1.2 数据库- 5 -1
3、.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 裸设备vs文件系统- 11 -2.3 系统管理vs数据库管理- 12 -2.4 如何合理创建表空间设备- 13 -2.4.1 原则- 13 -2.4.2 表空间容器的创建方法- 13 -2.4.3整合数据库表空间设备情况- 14 -2.5 建库语句参考-
4、 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 减少对数据库的更新和删除操作- 20 -4.4 如何使访问更高效- 21 -5 参数调优- 22 -5.1 AIX系统- 22 -5.2 DB2环境变量- 23 -5.3 DB2数据库实例参数- 23 -5.4 DB2数据
5、库参数- 24 -6 开始行动- 25 -7 参考文献- 26 -1 DB2数据库1.1 数据库对象关键的数据库对象包括：实例、数据库、节点组、表、视图、索引、模式、系统目录表。图1显示了一些数据库对象之间的关系。它也显示了表、索引和长整型数据存储在表空间中的情况。图1. 一些数据库对象之间的关系1.1.1 实例实例（有时称为数据库管理程序）是管理数据的DB2代码。它控制可对数据执行的操作，并管理分配给它的系统资源。每一个实例都是一个完整的环境。一个实例有它自己的数据库（其他的实例不能存取它）。它还有独立的安全性，不受同一机器（系统）上其他实例的影响。1.1.2 数据库关系数据库将数据表示成表
6、的集合。表由数目已定的列和任意数目的行组成。每个数据库都包括一组描述数据的逻辑和物理结构的系统目录表，一个包含为该数据库分配的参数值的配置文件以及一个带有正在进行的事务和可归档事务的恢复日志。1.1.3 节点组节点组是一个或多个数据库分区组成的集合。当您想为数据库创建表时，首先创建节点组以存储表空间，然后创建表空间以存储表。1.1.4 表关系数据库将数据表示成表的集合。表由逻辑排列的行和列数据组成。所有数据库的表数据都被存储在表空间中。1.1.5 视图视图是高效率的数据呈现方法（无需维护数据）。视图不是实际的表，不需要永久性存储器。创建并使用一个“虚拟表”。视图可以包括它所基于的表中的所有或某
7、些列或行。例如，可以在视图中连接一个部门表和一个员工表，以便可以列出特定部门中的所有雇员。图2显示了表和视图的关系。1.1.6 索引索引是一组关键字，其每一个都指向一些行。例如，图3中，表A的一个索引基于表中的员工号码。此关键字提供指向表行的指针：员工号码19指向员工KMP。通过使用指针创建指向数据的直接路径，索引是更有效的存取表行成为可能。SQL优化器自动选择最有效率的存取表中数据的方法。当确定最快速的数据存取路径时，优化器会将索引考虑在内。可创建唯一索引以确保索引关键字的唯一性。索引关键字是定义了索引的一个列或一些列的有序集合。使用唯一索引将确保在编入的索引的列中，每个索引关键字的值都是唯
8、一的。图3显示了索引与表之间的关系。图2. 表和视图之间的关系图3. 索引与表之间的关系1.1.7 模式模式是一个标识符，如用户ID，它帮助分组表和其他数据库对象。模式可以归个人拥有，拥有者可以控制对数据以及其中的对象的存取。模式也可以是数据库中的对象。它可以在创建模式中的第一个对象时自动创建。这样的对象可以是任何可以由模式名限定的对象，如表、索引、视图、程序包、单值类型、函数或触发器。1.1.8 系统目录表每个数据库都包括一组描述数据的逻辑和物理结构的系统目录表。DB2为每个数据库维护一大组系统目录表。这些表包含有关数据库对象（例如，用户表、视图和索引）的定义信息，以及用户对这些对象所拥有的
9、权限的安全性信息。它们在数据库创建时被创建，并在常规操作期间得到更新。不能显示的创建或卸载下它们，但是可以使用目录视图查询和查看它们的内容。1.2 存储器对象下列数据库对象用来定义在系统上存储数据的方式以及改进（与存取数据相关的）性能的方法：表空间、容器和缓冲池。1.2.1 表空间数据库由称为表空间的部件组成。表空间是用来存储表的位置。当创建表时，您可以决定将特定对象（如索引和大对象）数据与其余表数据分开存放。表空间也可以分布在一个或多个物理存储设备上。图4显示了在表空间之间分布数据时具有的一些灵活性。图4. 表空间将容器分配给表空间。容器是分配的物理存储器（如文件和设备）。表空间可以使系统管
10、理空间（SMS）或数据库管理空间（DMS）。对于SMS表空间，每个容器都是操作系统的文件空间中的一个目录，由操作系统的文件管理器控制存储空间。对于DMS表空间，每个容器或者是固定大小的预分配文件，或者是物理设备如磁盘，由数据库管理程序控制存储空间。表空间有三种类型：规则、临时和长整数。包含用户数据的表存放在规则表空间中。缺省用户表空间名为USERSPACE1。索引也存储在规则表空间中。系统目录表存放在规则表空间中。缺省系统目录表空间名为SYSCATSAPCE。包含长整数字段数据或长整数对象数据（如多媒体对象）的表存放在长整数表空间中。临时表空间分为系统临时表空间或用户临时表空间。系统临时表空间
11、用来存储SQL操作（如排序、重组表、创建索引和连接表）期间所需的内部临时数据。虽然可以创建任意数目个系统临时表空间，但建议您只适用大多数表所使用的页大小创建一个。缺省系统临时表空间名为TEMPSPACE1。用户临时表空间用来存储已说明全局临时表（已说明全局临时表存储的是应用程序临时数据）。用户临时表空间不是在数据库创建时缺省创建的。1.2.2 容器容器是物理存储设备。它可以由目录名、设备名或文件名标识。将为表空间分配容器。单个表空间可以横跨多个容器，但每个容器只能属于一个表空间。图5举例说明了表与数据库中的表空间、相关联的容器和磁盘之间的关系。图5. 表空间、相关联的容器和磁盘之间的关系EMP
12、LOYEE、DEPARTMENT和PROJECT表在HUMANRES表空间中，该表空间横跨容器0、1、2、3和4。此示例显示每个容器存在于不同的磁盘上。任何表的数据都以循环方式存储在表空间中的所有容器中。这能在属于给定表空间容器之间平衡数据。数据库管理程序在使用另一个容器之前写入一个容器的页数称为数据块大小。1.2.3 缓冲池缓冲池指的是从磁盘读去高速缓存表和索引数据页时或修改它们时分配给它们的主存储器。缓冲池的目的是改进系统性能。从内存存取数据要比从磁盘存取数据块的多；因此，数据库管理程序需要读写（I/O）的次数越少，性能也越好。（可以创建多个缓冲池，虽然在大多数情况下只需要一个。）因为可以
13、缩短慢速I/O所造成的延迟，所以缓冲池的配置是最重要的调整项目。 2 表空间存储在存储管理上，AIX系统采用逻辑卷管理策略。一个设备（逻辑卷）可以跨越多个磁盘，这就为并行读写创造了可能。目录表空间和日志表空间均存储在文件系统中，而数据表空间的存储类型通常是裸设备。一个表空间使用一个或多个设备（文件系统或裸设备）。2.1 AIX系统存储管理简介在AIX系统中，文件系统是建立在逻辑卷上的，逻辑卷是属于一个卷组的，卷组是由实际的物理磁盘组成，逻辑卷在实际物理硬盘中可以不连续，它可以跨越多个物理硬盘而存在。AIX系统中物理与逻辑区段的映射如图6所示图6. 物理到逻辑区段的映射在逻辑上，逻辑卷存在于卷组
14、中，而在物理上，它存在于物理卷上，物理卷是硬盘。硬盘的界面分为5个同心区域：外边缘（Outer-Edge）、外中间（Outer-Middle）、中央（Center）、内中间（Inner-Middle）和内边缘（Inner-Edge），如图7所示。由于磁盘机械装置的物理移动，边缘（Edge）区域的搜索时间最长。逻辑卷位于哪个区域上，对于逻辑卷的读写效率影响很大。其中，中央（Center）区域是磁盘搜索时间最短和访问速度最快的。通常，把经常要访问的逻辑卷放在磁盘的中央区域，把很少访问的逻辑卷放在磁盘的边缘（Edge）区域，其它逻辑卷放在磁盘的中间（Middle）区域。图7. 硬盘的分布2.2 裸设
15、备vs文件系统数据库的物理实现对数据库的运行时性能至关重要，物理实现更多的需要考虑硬件和系统平台。整合库部署在一台1900 MHz*4CPU，16GB内存的IBM小型机上，操作系统是IBM AIX 5.3，数据库管理系统是DB2 v9.1。数据库服务器的性能通常是通过事务吞吐量和应用程序响应时间来测量的，该性能主要取决于I/O子系统的性能。为了获得最大可能的I/O吞吐量，数据库管理员和系统管理员需要特别注意数据库表数据的布局。在AIX系统中通常有两种数据存储形式：裸设备、文件系统。裸设备，也叫裸分区（原始分区），是一种没有经过格式化，不被Unix通过文件系统来读取的特殊字符设备。它由应用程序负
16、责对它进行读写操作。不经过文件系统的缓冲。因为使用裸设备避免了再经过AIX操作系统这一层，数据库管理系统直接读写硬盘，所以使用裸设备对于读写频繁的数据库应用来说，可以极大地提高数据库系统的性能。当然，这是以磁盘的I/O非常大，磁盘I/O已经成为系统瓶颈的情况下才成立。如果磁盘读写确实非常频繁，以至于磁盘读写成为系统瓶颈的情况成立，那么采用裸设备确实可以大大提高性能。DB2数据库有三种表空间：目录表空间、数据表空间和日志表空间。目录表空间和日志表空间均存储在文件系统中，而数据表空间的存储类型是基于实际情况和以往的开发经验来决定。在我们的系统中，在ETL的任何一个阶段都要求高速的磁盘I/O，尤其是
17、数据抽取、匹配存储过程和数据导入整合区存储过程这三个过程，需要大批量的数据读取或写入，磁盘I/O是整合库系统性能的瓶颈。根据经验，数据表空间将采用裸设备的存储方式。于是，整合库的物理实现如下表所示。通常的系统可以采用这种模式。逻辑结构DB2表空间逻辑设备/文件系统空间大小备注目录表空间SYSCATSPACE/db2_tag3GB文件系统数据处理区TBSPTEMP/dev/rdb2tbsptemplv/dev/rdb2tbsptemp2lv10GB10GB裸设备代码对应区TBSPINTE/dev/rdb2tbspintelv20GB裸设备整合区日志表空间/intedb_log20GB文件系统2.
18、3 系统管理vs数据库管理图8. 表空间类型比较：系统管理和数据库管理2.4 如何合理创建表空间设备2.4.1 原则1. 一个表空间跨多个物理磁盘，以获得更好的I/O并行性2. 根据重要性不同，让表空间分布于磁盘的不同位置 索引、重要数据位于中心 将次重要数据、文件系统放在中间和边缘2.4.2 表空间容器的创建方法按以下说明创建逻辑卷，并根据设备的重要性依次创建（首先创建最重要的），可以保证最重要的位于中央，按重要性依次往外排列，可以做到读写性能最优。1. smit lv2. 选择多个物理盘（PHYSICAL VOLUME names），界面如图9所示。移动光标，用ESC+7选择多块物理磁盘P
19、V。图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显示的是整合数
20、据库在磁盘hdiskpower8上的设备创建情况。由图中看出，整合数据库严格遵循了设备按照重要性分布的原则： 索引、重要数据位于中心 将次重要数据、文件系统放在中间和边缘图11. 设备按照重要性分布2.5 建库语句参考统计分析系统数据库部分建库语句：-创建数据库在一个2G的文件系统/anly_tagdb2 create database anly on /anly_tag alias anly using codeset gbk territory cn collate using system-创建bufferpooldb2 connect to anly user db2admindb2
21、create bufferpool TMPBF32K immediate size 32000 pagesize 32k-创建数据表空间（8G*4）并指定相应的bufferpooldb2 create tablespace TBSPANLY pagesize 32 k managed by database using (device /dev/ranlytbsplv 262144) bufferpool TMPBF32Kdb2 alter tablespace TBSPANLY add(device /dev/ranlytbsplv2 262144)db2 alter tablespace
22、TBSPANLY add(device /dev/ranlytbsplv3 262144)db2 alter tablespace TBSPANLY add(device /dev/ranlytbsplv4 262144)-创建系统临时表空间（2G的文件系统/anly_tmp/tmp）并指定相应的bufferpooldb2 create system temporary tablespace TBSPTMP32K pagesize 32k managed by system using (/anly_tmp/tmp) extentsize 8 prefetchsize 16 bufferpoo
23、l TMPBF32K-修改日志文件大小为25000*4Kdb2 update db cfg for anly using LOGFILSIZ 25000db2 update db cfg for anly using LOGPRIMARY 20db2 update db cfg for anly using LOGSECOND 50db2 update db cfg for anly using NEWLOGPATH /anly_log-修改表空间大小db2 alter tablespace tbsptemp drop (device /dev/rdb2tbsptemp2lv)db2 alte
24、r tablespace tbsptemp resize (device /dev/rdb2tbsptemp3lv 327680)3 缓冲池和表空间3.1 缓冲池通常，为每一页面大小的系统临时表空间建立一个缓冲池就足够了。因为在缓冲池管理方面，我们完全可以信任DB2。如果想在这方面继续提高性能，则可以参考本节的文字。DB2在其缓冲池的自我调优方面十分擅长，并且会将经常被访问的行放入内存，因此一个缓冲池就足够了。（这一选择也避免了管理多个缓冲池的复杂性。）如果时间允许，并且需要进行改进，那么可能需要使用多个缓冲池。其思想是将访问最频繁的行放入一个缓冲池中。在那些随机访问或者很少访问的表之间共享一
25、个缓冲池可能会给缓冲池带来“污染”，因为有时候要为一个本来可能不会再去访问的行消耗空间，甚至可能将经常访问的行挤出到磁盘上。如果将索引保留在它们自己的缓冲池中，那么在索引使用频繁的时候（例如，索引扫描）还可以显著地提高性能。这与我们对表空间的讨论是紧密联系的，因为要根据表空间中表的行为来分配缓冲池。如果采用多缓冲池的方法，采用4个缓冲池比较合适：1. 一个中等大小的缓冲池，用于临时表空间。2. 一个大型的缓冲池，用于索引表空间。3. 一个大型的缓冲池，用于那些包含经常要访问的表的表空间。4. 一个小型的缓冲池，用于那些包含访问不多的表、随机访问的表或顺序访问的表的表空间。3.2 缓冲池大小缓冲
26、池大小需要综合考虑系统内存和服务器工作负载情况而定。通常，OLTP类型分配75%的可用内存；OLAP类型分配50%的可用内存，将剩下的50%分配给SORTHEAP。缓冲池对应于系统的内存空间，所以千万不要为缓冲池分配多于所能提供的内存，否则就会招致代价不菲的OS内存分页。通常来讲，如果没有进行监控，要想知道一开始为每个缓冲池分配多少内存是十分困难的。对于OLTP类型的工作负载，一开始将75%的可用内存分配给缓冲池比较合适。对于OLAP/DSS，经验法则告诉我们，应该将50%的可用内存分配给一个缓冲池（假设只有一种页面大小），而将剩下的50%分配给SORTHEAP。3.3 表空间页大小及容器分布
27、在页大小足以容纳一条记录的前提下，随机更新操作，倾向于用较小的页大小；而对于一次访问大量行的操作，较大的页大小可以提供更好的性能。每个表空间应该跨越多个物理磁盘。以获得更好的I/O并行性。为了创建一个表，必须有一个表空间，其页面大小应足以容纳一行。您可以选择使用 4、8、16或32KB这几种页面大小。有时候必须使用较大的页面大小，以回避某些数据库管理器的限制。例如，表空间的最大尺寸与表空间的页面大小成比例。如果使用4K的页面大小，那么表空间的大小（每个分区）最大是64GB，如果使用32K的页面大小，那么最大是512GB。对于执行随机更新操作的OLTP应用程序，采用较小的页面大小更为可取，因为这
28、样消耗的缓冲池中的空间更少。对于要一次访问大量连续行的OLAP应用程序，通常使用较大页面大小效果会更好些，因为这样可以减少在读取特定数量的行时发出的I/O请求的数量。较大的页面大小还允许您减少索引中的层数，因为在一页中可以保留更多的行指针。然而，也有例外情况。如果行长度小于页面大小的255分之1，则每一页中都将存在浪费的空间，因为每页最多只能有255行（对于索引数据页不适用）。在这种情况下，采用较小的页面大小或许更合适一些。3.4 系统临时表空间为每种页面大小的表空间，建立一个系统临时表空间，通常使用文件系统，大小根据排序和重组事物的大小确定。对于所使用的每种页面大小，必须存在一个具有匹配页面
29、大小的系统临时表空间（以支持排序和重组）。然后将所有享用匹配页面大小的表空间指派给具有相同页面大小的缓冲池。如果对性能还有更苛刻的要求，并且有时间投入，那么可以使用DMS表空间，并且根据使用情况来组织表。另外，还要遵循前面给出的关于使用多个缓冲池的建议。对于每种页面大小，创建一个：1. 系统临时表空间。2. 用于索引的常规表空间。3. 用于频繁访问的表的常规表空间。4. 用于访问不多的表、随机访问的表以及顺序访问的表的常规表空间。5. 用于 LOB 数据的大型表空间。3.5 区段大小和预取大小区段大小（extent size）和预取大小（prefetch size）是表空间的参数，对于数据存取
30、性能至关重要。区段大小跟表空间中表的平均大小有一定的比例关系，通常表越大，区段大小也越大。如果表空间驻留在磁盘阵列上，则区段大小应设置成条纹大小。预取大小通过公式确定：prefetch size = (# containers of the table space on different physical disks) * extent size。磁盘阵列的情况：prefetch size = extent size * (# of non-parity disks in array)。3.5.1 区段大小extent size指定在跳到下一个容器之前，可以写入到一个容器中的PAGESIZE
31、页面的数量，这个参数是在创建表空间时定义的（之后不能轻易修改）。处理较小的表时，使用较小的区段效率会更高一些。下面的经验法则是建立在表空间中每个表的平均大小的基础上的：1. 如果小于25MB，extent size为82. 如果介于25到250MB之间，则extent size为163. 如果介于250MB到2GB之间，则extent size为324. 如果大于2GB，则extent size为64对于OLAP数据库和大部分都要扫描（仅限于查询）的表，或者增长速度很快的表，应使用较大的值。如果表空间驻留在一个磁盘阵列上，则应将区段大小设置成条纹大小（也就是说，写入到阵列中一个磁盘上的数据）。
32、3.5.2 预取大小对于预取大小，可以通过使用ALTER TABLESPACE轻易地修改。最优设置差不多是这样的：prefetch size = (# containers of the table space on different physical disks) * extent size。如果表空间驻留在一个磁盘阵列上，则设置如下：prefetch size = extent size * (# of non-parity disks in array)。4 提高SQL语句执行效率4.1 建立索引使用索引实现关键数据的高效访问。但是需要知道每个索引都会给数据库更新带来额外的开销。这就意
33、味着，低效的索引会给数据库带来灾难。对于数据库，我们必须关注关键数据的读取，为他们提供最高效的访问路径。对此，基本策略就是建立索引。在索引提供高效访问的同时，也带来了额外的系统开销。开销分为磁盘空间的开销和处理器开销。下面我们讨论一下处理器开销。每当在表中插入或删除记录时，该表的所有索引必须进行相应调整。每当对已建立索引的字段进行更新时，这种调整也会发生。举例子说，如果在未建立索引的表中插入数据需要100个单位时间，那么每增加一个索引就会增加100到250个单位时间。有趣的是，维护索引的开销与简单触发器带来的开销大致相当。在建立索引前线介绍一些最通俗的信息，这些信息来自developWorks
34、，列出这些信息是因为我觉得这些信息通常情况下是值得参考的：1. 当要在一个合理的时间内结束查询时，应避免添加索引，因为索引会降慢更新操作的速度并消耗额外的空间。有时候还可能存在覆盖好几个查询的大型索引。2. 基数较大的列很适合用来做索引。3. 考虑到管理上的开销，应避免在索引中使用多于5个的列。4. 对于多列索引，将查询中引用最多的列放在定义的前面。5. 避免添加与已有的索引相似的索引。因为这样会给优化器带来更多的工作，并且会降慢更新操作的速度。相反，我们应该修改已有的索引，使其包含附加的列。例如，假设在一个表的 (c1,c2)上有一个索引i1。您注意到查询中使用了where c2=?，于是又
35、创建一个(c2)上的索引i2。但是这个相似的索引没有添加任何东西，它只是i1的冗余，而现在反而成了额外的开销。6. 如果表是只读的，并且包含很多的行，那么可以尝试定义一个索引，通过CREATE INDEX中的INCLUDE子句使该索引包含查询中引用的所有列（被INCLUDE子句包含的列并不是索引的一部分，而只是作为索引页的一部分来存储，以避免附加的数据FETCHES）。决定是否使用索引，可以重点考虑检索比率。即，判断索引有效性的依据，就使用键值作唯一性条件检索出的数据的百分比。百分比越低，索引越有效。做出这个论断的前提是一些假设，如磁盘访问的相关性能。索引键值相关记录的物理位置是否相邻也很重要
36、，因为是通过块来操作数据的。建立了索引之后，如果索引键所指向的记录散布于整个表中，即使这些记录在表中占的比率很小，但因为它们分散在整个磁盘上，所以索引的性能就会大打折扣。另外值得注意的是，函数和类型转换可能导致索引失效。4.2 采用表分区DB2 V9新增了表分区功能，分区技术可以改进大型数据库的管理。分区键的选择：让查询很快定位，但尽量避免数据库操作集中；使海量数据表拆分成小表，并使数据库操作平均分散到表分区中。分区在单节点数据库上，提高查询定位的速度，不提供查询并行性。DB2 V9新增了表分区功能，分区技术可以改进大型数据库的管理。DB2 V9新增了表分区功能，因此对一些大表，我们在DB2
37、V9中不再需要分拆成小表，再用UNION ALL视图的方式进行设计，而是直接用分区表实现一些这些功能。表分区功能是一种数据组织方案，即，表数据根据一个或多个表列中的值分布到多个存储对象（称为数据分区或范围）中。每个数据分区都是单独存储的。这些存储对象可以在不同的表空间中，也可以在相同表空间中。很多业务数据的查询包含时间条件，所以时间经常作为分区条件。业务数据在时间上的分布通常是平均的，所以用时间分区的另一个好处是使包含大型数据的表分成大小相当的几个分区，用户还可以针对数据量来制定分区，以控制每个分区的数据量在一个可以接受的范围内。在外汇管理局统计分析子系统中，用户的每一次查询均包含时间条件，所
38、以按时间分区是一个很好的选择。外汇局国际收支数据明细表每年有2000万数据，使用时间一长，数据量将非常之大。明细数据按月分区，可以将每个分区的数据量控制在200万左右，在查询时控制时间条件在一年内，这样可以避免查询时全表扫描带来的压力。但是按照时间分区也有弊端。一个业务系统，按时间按分区，当前时间下的分区插入、删除、更新或查询的操作必定比别的分区多。这种集中的操作必定给数据库带来巨大的压力。外汇管理局统计分析子系统还有另外一个特点：用户按照行政区划分散，查询时只关心所在行政区划的数据。我们就有一种构想：可否按照时间和行政区划两个条件来分区（按月、按省分区）？这一想法刚刚产生，还未执行，接下来打
39、算测试。分区键的选择：让查询很快定位，但尽量避免数据库操作集中；使海量数据表拆分成小表，并使数据库操作平均分散到表分区中。对于分区，我们进行了一些列测试，下面就是我们测试的结论。分区在单节点数据库上，提高查询定位的速度，不提供查询并行性。以一年的数据量，月分区为例。首先，考虑查询条件中有分区字段时的情况。当查询条件在一个月内时，DBMS可以直接定位到某个分区，在不考虑其他条件时，查询速度是不分区表的12倍。依此类推，当查询条件跨N（N=12）个月时，查询速度是不分区表的12/N倍。如果，查询条件中没有分区字段时，分区和不分区查询效率相当。4.3 减少对数据库的更新和删除操作删除和更新操作的开销
40、往往比插入高，所以一个好的设计需要减少对数据库的更新和删除操作。数据库的删除和更新操作会带来一连串的“附属操作”：删除和更新操作都需要记录日志；都会带来数据物理存储的变化（记录的移动）；都会导致数据重组（索引重建）等。对数据库的更改都会记录到日志中，以备回滚之用。所以更新操作和删除操作的每个细节都会记录到日志中。删除记录时，数据库还会移动记录，以把删除数据留出的空缺补上，索引也会被重建。更新数据库时，如果更新了索引字段，则会导致索引重建，如果更新的是变长的字段（Varchar），则需要移动记录。所以在设计时，应尽量减少对数据库的更新和删除操作。如果一个表有多个字段需要更新，则应该把多个更新写在
41、同一个SQL语句中。将数据处理放在一个较小的临时表里进行，然后将处理完的数据放入大表中，在大表中进行数据处理（更新操作）是不明智的做法。删除操作时，如果要删除一个表的大量数据，只保留少量的有效数据，可以将有效数据转移到临时表中，然后使用alter table TABLE_NAME activate not logged initially with empty table来删除表中所有记录，因为此命令不记录日志。4.4 如何使访问更高效本小节的内容很大一部分来自The Art of SQL这本书，这本书里集合了数据库开发的通用经验。虽然没有局限于具体的DBMS和硬件平台，但是却是一本实践性很强
42、的书。1一次连接数据库，做很多事情。直到处理完，才断开连接。2一个SQL语句包含尽量多的操作。形象地说：几千个语句，借助游标不断循环，很慢。换成几个语句，处理同样的数据，还是很慢。换成一个语句，解决问题，最好。3接近DBMS核心。尽量使用数据库自带的函数。减少自定义函数。因为再聪明的数据库优化器也不认识自定义函数。4一个语句不要连接太多的表，建议的上限是5个。5将频繁更新的列集中起来：当更新某一行时，DB2 会记录进行更改的所有列，因此将频繁更新的列放到一起可以减少 DB2 的记录工作。这只是一个有关性能的小建议，因此不应为实现它而进行重大的应用程序或数据库设计修改。6如果想了解，如何书写SQ
43、L语句才能更高效，可参考The Art of SQL。书中提出了不少有益的见解。5 参数调优关于数据库服务器的参数调优包括，操作系统参数、数据库管理系统参数和数据库参数的调优。其中操作系统调优的核心在于AIX系统的内存和CPU的调优，旨在提高数据库对内存和CPU的利用率。DB2数据库的调优分为3个方面：DB2环境变量、DB2实例参数和数据库参数。旨在提高数据库的并行性、读写性能、排序和汇总性能。参数设置和参数调优通常根据经验确定，以下小节介绍的是整合库的参数设置。如想了解综合查询系统数据库，可参看查询库性能调优的参数调优部分。5.1 AIX系统vmo -o maxclient%=20vmo -
44、o maxperm%=20vmo -o minperm%=5这三个参数是AIX内存管理相关的。下面介绍这三个参数。增强JFS文件系统为它的缓冲区高速缓存使用客户机文件。为了在增强JFS文件系统的高速缓存中建立硬性限制，可以调谐maxclient的参数。该参数代表了可用于缓冲区高速缓存的客户机页面的最大值。可以使用vmo -o maxclient命令进行更改。maxclient的值显示为实内存的百分比。在到达maxclient阈值后，LRU开始取用最近尚未访问过的客户机页面。如果没有足够的客户机页面可以被窃取，LRU会替换掉其它类型的页。通过减少maxclient的值，可以阻止增强JFS文件页的
45、访问不要替换掉工作存储页面，最小化调页空间的页面调度。另外，maxclient通常应该设置为一个小于或等于maxperm的值。操作系统通过把在曾经读写的内存页留在实内存满足不同要求。如果文件页面在它们的页帧被重新分配前被请求，那就节省了输入输出操作。页帧使用的文件相对用于计算的（工作或程序文本）段的文件的比例是松散地受控于minperm和maxperm的值的：如果RAM中文件页面所占的百分比高于maxperm，页面替换的窃取只用于文件页。如果RAM中文件页面所占的百分比低于minperm，页面替换的窃取同时用于文件页和计算页。如果RAM中文件页面所占的百分比介于minperm和maxperm之
46、间，页面替换只窃取文件页，除非文件页的重调入数量大于计算页数。5.2 DB2环境变量db2set DB2_PINNED_BP=YESdb2set DB2_FORCE_FCM_BP=NOdb2set DB2MEMMAXFREE=1048576db2set DB2_MMAP_WRITE=NOdb2set DB2_MMAP_READ=NOdb2set DB2_HASH_JOIN=Ydb2set DB2_RR_TO_RS=ON慎用，请详细了解后再使用以下参数：DB2_EVALUNCOMMITTED=YESDB2_SKIPDELETED=ONDB2_SKIPINSERTED=ONDB2_PARALLEL
47、_IO=YESDB2_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：这个参数将在记录锁之前进行谓词检查，尽
