DB2 性能优化快速入门

案例分析

查询优化案例

接下来这里从一个试验来看一下 DML 操作过程中优化的详细步骤和具体数据。首先看一个查询优化的例子，下面是试验中的建表语句：


CREATE TABLE MCLAIM.T1_DMS (
C11 VARCHAR (10)  NOT NULL ,
  C12 VARCHAR (15)  NOT NULL  ,
  C13 VARCHAR (20)  NOT NULL  ,
  CONSTRAINT C11_PK PRIMARY KEY ( C11)  ) IN DMS_Space;
  
CREATE TABLE MCLAIM.T2_DMS (
C21 VARCHAR (15)  NOT NULL ,
C22 VARCHAR (25)  NOT NULL  ,
C23 VARCHAR (30)  NOT NULL  ,
CONSTRAINT C21_PK PRIMARY KEY ( C21)  ) IN DMS_Space;

CREATE TABLE MCLAIM.T3_DMS (
C31 VARCHAR (10)  NOT NULL ,
C32 VARCHAR (25)  NOT NULL  ,
C33 VARCHAR (35)  NOT NULL  ,
CONSTRAINT C31_PK PRIMARY KEY ( C31)  ) IN DMS_Space;

  


最初的环境没有优化，表空间类型 SMS 表空间，查询的表中没有索引，sortheap 过小等等。在这种情况下执行下列查询语句：

select C12 from TESTOPT.T1_SMS,%SCHEMA%.T2_SMS,%SCHEMA%.T3_SMS
where substr(C12,1,10)=substr(C21,1,10) and C22=C32
order by C12 asc
  


在没有优化的情况下得到的总的执行时间是 653 秒，而经过优化后得到总的执行时间是大概是 15 秒左右。在优化中采用了如下优化步骤：

选择 DMS 表空间。
添加索引：
CREATE UNIQUE INDEX INDEX_C12 on T1_DMS (C12 ASC);
CREATE UNIQUE INDEX INDEX_C22 on T2_DMS (C22 ASC);
CREATE UNIQUE INDEX INDEX_C32 on T2 _DMS (C32 ASC);



增大 sortheap 的大小
执行 runstats
选择适当的优化级别
改进表结构，增加冗余字段。以空间换时间：
ALTER TABLE T1 ADD C12_Red VARCHAR(10);
ALTER TABLE T2 ADD C21_Red VARCHAR(10);
UPDATE T1 SET C12_Red=SUBSTR(C12,1,10);
UPDATE T2 SET C21_Red=SUBSTR(C21,1,10);
  

查询语句变成：
select C12 from TESTOPT.T1_DMS, TESTOPT.T2_DMS, TESTOPT.T3_DMS
where C12_Red=C21_Red and C22=C32 order by C12 asc
  



图 1. 查询操作优化示意图


从图中可以看出选择好的表空间类型 ( 数据库管理表空间 ) 和添加索引会对性能有很大的改善作用。而添加冗余字段对性能的改进作用最大。当然这会涉及表结构的变化，是需要在数据库设计阶段考虑的因素。同时代价是增加磁盘的占用空间。

写入操作优化

接下来是一个写操作的例子（插入）。下面是试验的脚本：


CONNECT TO FFTEST;
CREATE SCHEMA TESTOPT;
DROP TABLE TESTOPT.T3;
CREATE TABLE TESTOPT.T3 (
C31 VARCHAR (10)  NOT NULL ,
C32 VARCHAR (15)  NOT NULL  ,
CONSTRAINT C31_A CHECK ( C31 LIKE 'A%' or C31 LIKE 'a%'));
       
CREATE INDEX TESTOPT.INDEX_C31 on TESTOPT.T3 (C31 ASC);
ALTER TABLE TESTOPT.T3 ADD CONSTRAINT C31_A CHECK (substr(C31,1,1)= ’ a ’
   or substr(C31,1,1)= ’ A ’ )
ALTER TABLE TESTOPT.T3 APPEND OFF;
CONNECT RESET;
  


最初的表没有优化，含有索引，约束等因素，插入 4 万条记录大约花了 68 秒钟，而最终优化后插入 4 万条记录只需 6 秒钟。如下是优化步骤：

去除索引。
去除约束。
在 insert 语句中包括多行。
采用 Append 模式
屏蔽表的日志操作。
采用并行写操作。
采用严格的隔离级别。

图 2. 插入操作优化示意图


从图中可以看出减少索引和约束可以大幅度提高插入性能，而将多条插入语句合并成一行产生的效果更加明显。

性能调优注意事项

为了得到高性能将缓冲池调得过大，导致数据库连不上。这对没有经验的用户来说可能是个灾难，这意味着数据库可能要重建。最初我们曾经犯过这样的错误。现在可以通过调节 DB2 注册参数 DB2_OVERRIDE_BPF 来设置缓冲池的大小，从而能够再次连接数据库。当然最好将 STMM 激活，使内存能够自动调整。
往往忽视 runstats 和 reorg 的作用，我们发现不止一个的性能问题，都是由于优化器选择了错误的 access plan 导致系统整体性能下降。而对外显示的则不光是 SQL 执行慢，同时也能会表现出 I/O 瓶颈或系统响应时间长。这往往会误导我们去分析其他地方。但究其根源，很多时间是由于优化器的错误。这些问题往往在重新执行 runstats 和 reorg 之后就解决了。所以这两个命令也要特别注意。
在进行数据加载的时候往往忽略了索引因素，导致性能加载性能下降。我们遇到过这样的一个例子，一张表导入 1000 条记录花了 5 分钟，检查了很多配置找不到原因，最后发现这张表上有 1 个主键，还有 4 个外键。将他们删除后重新导入只花了几秒钟。所以在进行 load 或者是 insert 的时候尽量将主外键或相关索引删除，加载完成后重建相关索引。主外键尽量通过加载程序来保证它的数据完整性。这一点往往会被忽略，所以在加载数据前先检查一下所有表的索引状态及引用关系。
在修改 db2 参数的时候，一次最好修改一个参数，然后看看效果，在调节其他参数。否则一次多个参数，调好了也没弄清楚是哪个参数起的作用。下次还得全部来一遍。还要注意，并非所有参数都是越大越好，有时可能会适得其反。
注意索引的试用，优化好的索引对查询语句性能的提高往往会产生数十倍的性能改进。所以，调优前可以先察看一下相关语句的索引利用情况。这可以通过察看 SQL 语句和执行计划，看一下已有索引是否被利用起来了或是否需要建立新的索引。这往往比 DB2 系统调优更重要。但切记考虑插入操作，索引也会降低插入的性能。这一点要综合考虑。
由于 XML 数据可以跨页存储，在设计 XML 数据库时要尽可能的使用较大的数据页，这样可以避免 XML 数据跨页查询，以提高查询性能。
采用表分区：有这样一个例子：客户有一张表的数据量非常大，每天都会产生大约 30 万条记录，同时每天都会删除五天前的记录，所以此表大概有 150 万条记录，现在客户在每天的第一次查询时要重新对表进行索引（因为晚上会产生很多数据，所以新增加的数据都没有建索引），导致响应非常慢！对于这种问题，后来采用了表分区，用 6 个分区表来分别装载原来 6 天的数据。所以查询和插入都只涉及一张表，所以响应速度得到大幅度提高。
了解 CHNGPGS_THRESH 参数，是缓冲池写日志的阀值。有一个例子，在创建索引时比较慢，经过检查发现 CHNGPGS_THRESH 参数过大，造成每次写日志的时候数据量过大，造成 I/O 瓶颈，适当减小这个参数值，可以增加写日志的次数，但数减少每次写日志的数据量，这对于大缓冲池里的大表上创建索引时很有效的。
在导入数据时尽量采用 load, 少用 import, 我们做过统计，用 import 花费 10 分钟的数据，用 load 大概只需要 1 分钟，这大大提高了工作效率。
注意 db2diag.log 的大小，当这个文件很大的时候，数据库的所有操作，包括停启 db2 都会特别的慢，有时甚至挂起。所以要经常看看这个文件的大小，过大时最好删掉，重启 db2 。当然 DIAGLEVEL 不要设得太高，除非为了诊断某个问题获得更多信息，一般默认的 3 足够了。