DB2 调优经验总结

frank2005 贡献于2012-01-16

作者 jiaoly  创建于2008-12-18 09:16:00   修改者丁跃彬  修改于2012-01-16 11:37:00字数17617

文档摘要:DB2 调优经验总结
关键词:

经验总结 DB2调优 经验技巧总结 目 录 1 文档摘要 4 1.1 文档分类 4 1.2 关键字/Tag 4 1.3 摘要 4 1.4 作者、协作者及评审人员 4 1.5 定义、首字母缩写词及缩略语 4 2 简介 4 第 页 共 20 页 经验总结 2.1 场景描述 5 2.2 适用环境 5 3 经验总结 5 3.1 调优数据库介绍 5 3.2 优化应用程序 7 3.2.1 调整索引 14 3.2.2 提高索引的性能的建议 16 3.2.3 自动汇总表和物化查询表 16 3.3 减少连接数 17 3.4 小结 18 3.4.1 物化查询表: 18 3.4.2 系统维护的MQT: 18 3.4.3 用户维护的 MQT 19 3.4.4 自动汇总表(总结表): 20 3.4.5 staging 表 20 3.4.6 相关内容 22 3.5 存在问题 22 1 文档摘要 1.1 文档分类 设计开发à经验总结 第 页 共 24 页 经验总结 1.2 关键字/Tag DB2、调优 1.3 摘要 1.4 作者、协作者及评审人员 作者: 协作者: 评审人员: 1.5 定义、首字母缩写词及缩略语 2 简介 2.1 场景描述 在支持过程中,或多或少会遇到DB2数据库的调优问题,本文为你介绍几种DB2的调优方式。 2.2 适用环境 3 经验总结 3.1 调优数据库介绍 性能调整准则 第 页 共 24 页 经验总结 以下准则可帮助您制定一个调整性能的总体方案。 记住递减返回定律:最大的性能收益通常来自于最初的努力。以后的更改通常只能产生越来越小的效益,并且需要更多努力。 不要只为调整而调整:进行调整以释放标识的约束。如果调整的资源不是造成性能问题的主要原因,这种调整对响应时间几乎不产生影响,除非您释放了主要约束,而且这种调整实际上会使后续调整工作更加困难。如果有可能明显提高性能的话,则关键在于对某些作为影响响应时间的主要因素的资源的性能提高。 考虑整个系统:永远不能片面地调整一个参数或系统。在进行任何调整前,务必考虑它将对整个系统带来的影响。 一次更改一个参数:不要一次更改多个性能调整参数。即使您肯定所有更改都有好处,也没有任何办法来评估每个更改所带来的影响。如果一次更改多个参数,也不能有效地判断所做的更改的利弊。如果每次调整一个参数来改进一方面,几乎总是会影响至少一个您可能没有考虑到的其它方面。通过一次更改一个,允许您使用基准程序来评估您是否需要进行更改。 按级别测量和重新配置:和一次只应更改一个参数的理由一样,一次也只能调整系统的一个级别。可使用以下的系统级别列表作为参考: · 硬件 · 操作系统 · 应用程序服务器和请求器 · 数据库管理器 · SQL 语句 · 应用程序 检查是否存在硬件和软件问题:某些性能问题可通过维修硬件和/或修订软件来解决。如果通过维修或修订就可解决问题,就不需要花过多时间来监视和调整系统。 在升级硬件前搞清楚问题:即使增加存储器或提高处理器能力可立即改善性能,也应花时间了解系统的瓶颈所在。可能花钱增加磁盘存储器后,才发现系统没有处理能力或可利用它的通道。 在开始调整前执行回退过程:正如前面所讲,某些调整可能产生意外的性能结果。如果此调整使性能降低,应撤销该调整,改试另一种调整。如果保存了以前的设置并可重新调用它,那么撤销不正确的信息将变得非常容易。 数据库的调优计划: Operating System l Paging l Memory allocations l Process priorities l I/O subsystem n Disk Adapters n Physical Disk configuration Application Server l Application parameters 第 页 共 24 页 经验总结 l Operating System l Connection Pooling l Memory Allocations l Process priorities Web Server l Application parameters l Operating System l Connections l Memory Allocations l Process priorities l Connection Pooling DB2 l DBM parameters n Intrapartition parallelism n Maxquerydegree l DB CFG Parameters n Locklist n Sortheap n Sheapthres and sheapthres_shr n Package cache n Catalog cache n Logging n Softmax n Mincommit n Maxlocks n Deadlock check time n Lock timeout n Maxfilop l Registry and environmental variables l Operating-specific DB2 parameters DB2 Application l SQL l Application-related DBM and DB CFG parameters l Optimization Class l Sargability of predicates l Indexes l Multidimensional clustering l Clustering l Range clustered tables l Registry and environmental variable 第 页 共 24 页 经验总结 3.2 优化应用程序 l 优化应用程序 应用程序开发人员应该具备的知识: l DML l Optimization Classes l Join Techniques l DB2 Access paths and DB2 Explains l Dynamic and Static SQL l Intrapartition parallelism l Range delimiting, Index SARGable, and data Sargable predicates l Predicate selectivity l Various DB2 Explain tools l Commit/Savepoints/Roolbacks l DB2 SQL Compiler l SQL Procedures l Stored Procedures DBA 需要对数据库的逻辑,物理设计把关,并且要审核数据库中所用的SQL. Top-performing applications cannot be developed by developers alone - the DBA is an important role in the application development life cycle. l 使用优化器 需要了解的知识包括: l Join techniques n Nested loop join n Merge scan join n Hash join n Database Index ANDing/ORing l Access methods n List prefetch n Table scan n Sequential prefetch n Block index scan 连接以及更复杂的 SQL 语句怎样呢?DB2 优化器有一系列可供自己使用的技术来用于连接表数据。当在 FROM 子句中引用多个 DB2 表(或指定了 JOIN 子句)时,SQL 会请求 DB2 执行连接操作。根据连接标准,必会执行一系列的指令来组合表中的数据。 DB2 如何做这件事?每个多表查询会分解成数个单独的存取路径。为完成此连接操作,DB2 优化器先选择其中的两张表并创建一条经过优化的存取路径。它不是随机地做这件事,而是根据它认为是此连接的最优方式来进行选择。然后,优化器继续连接其它表,直到优化完整条查询。 第 页 共 24 页 经验总结 在连接表时,优化器将必须确定要使用的最佳连接算法。连接算法(或连接方法)定义了组合表的基本过程。DB2 可以采用三类连接方法:嵌套循环(nested loop)、归并扫描(merge scan)和散列连接(hash join)。每种连接方法的运行方式各不相同,但可得出相同的结果。然而,连接方法的选用会极大地影响到连接性能。DB2 根据这样的方式来采用每种连接方法:基于一组统计,采用这种方法可以达到最佳性能。所以,您应该掌握各种连接方法,以及促成选择这些方法的因素。 每种连接方法通常都涉及一些特定的基本步骤。通常,首先确定先处理哪个表。称这个表为外表。做出决定之后,对该外表执行一系列的操作,为连接做准备。然后,将该表中的各行与第二个表(称之为内表)进行组合。另外,还要对内表执行的一系列操作,这可以在连接发生之前进行,也可以连接发生时进行,或者在这两者时进行。虽然所有连接方法包含的步骤都类似,但除这一点之外,这三种连接方法都各不相同。优化器知道每种方法的优缺点,知道采用哪种方法会怎样影响到性能。根据系统目录中的当前统计,优化器还知道哪些表最适合做内表,哪些表最适合做外表。以下从较高层面汇总了优化器所要考虑的一些事项: · 表越小,越有可能被选为外表。这有助于减少必须再次访问内表的次数。 · 如果选择谓词可以应用到某个表,则该表更适合于被选作外表,因为在访问内表时只会用那些符合这些谓词(应用于该外表的)的行。 · 如果可能对其中某个表做索引查找,则该表很适合于作为内表。如果一个表没有索引,则最好不要将其作为内表,因为每扫描外表中的一行,就要扫描一遍整个内表。 · 在连接操作中,重复元素最少的表倾向于被选作外表。 当然,这些不是固定不变的规则。最后,优化器将根据详细的代价估计来选择外表和内表。现在,我将讨论可用于 DB2 的连接类型,以及这些连接类型之间的区别。 最常用的连接类型可能是嵌套循环连接(nested loop join,NLJ)。要执行 NLJ,先在外表中确定符合条件的行,然后扫描内表来搜索匹配。符合条件的行是指与针对表中列的谓词相匹配的行。在完成对内表的扫描之后,再在外表中确定另一符合条件的行。然后,再扫描内表以查找匹配,如此反复。通常,用索引来重复扫描内表以将 I/O 代价降到最低。 DB2 采用的第二类连接方法是归并连接(merge join,MJ)。用 MJ 时,需要按照连接谓词对要连接的表进行排序。这意味着必须按照指定连接标准的列的顺序访问每个表。这个顺序可以用排序或索引式访问来实现。在确保对外表和内表正确排序之后,按照顺序读取每个表,然后匹配连接列。在归并扫描连接中,每个表只扫描一遍。 第三类连接取决于运行 DB2 的平台。对于 DB2 for OS/390 and z/OS,存在混合连接(hybrid join)。混合连接组合数据和指针来访问和组合正在连接的表中的行。关于这种连接类型的完整讨论超出了本文的讨论范围。 对于 DB2 for Linux、UNIX 和 Windows,第三类连接是散列连接(hash join)。散列连接要求有一个或多个 table1.ColX = table2.ColY 形式的谓词,并要求列类型必须相同。扫描内表,然后将行复制到为排序堆分配的内存缓冲区。根据“散列代码”将内存缓冲区分成几个分区,这些“散列代码”是根据连接谓词的列计算得来的。如果第一个表的大小超过了可用的排序堆空间,则选中分区的缓冲区被写到临时表中。处理完内表之后,扫描外表,通过比较“散列代码”,将外表的行与内表的行进行匹配。散列连接可能需要大量内存。所以,要使散列连接真正提高性能,可能需要更改 sortheap 数据库配置参数和 sheapthres 数据库管理器配置参数的值。 可是,您知道何时应该使用哪种连接方法?通常,当符合连接的行数较少时,就执行代价而言,建议使用嵌套循环连接。随着行数的增加,归并连接成为较好的选择。最后,在散列连接这种情形下,内表是保存在内存缓冲区中。如果内存缓冲区太少,则散列连接不得不溢出。优化器会试图避免这种情况,所以选两个表中较小的表作为内表,较大的作为外表。 第 页 共 24 页 经验总结 最终性能一般取决于确切的符合条件的行数以及其它因素(譬如,数据库的设计、数据库的组织、统计信息的精确性、硬件类型和 DB2 环境的设置 对于静态SQL,它的访问计划是存储在DB2 catalog table, sysibm.syspackages ( syscat.syspackages view), 对于动态SQL,它的访问计划是被缓存在包缓存里面(静态SQL为了避免物理的去读写磁盘的sysibm.syspackages 也会将其缓存到包缓存里面).包缓存的算法保证了只保留最近执行的动态和静态SQL,有效的减少了磁盘I/O 。 用优化等级指定搜索策略 连接方法的选择还取决于正在使用的优化等级。优化等级指定了各种搜索策略,当编译和优化 SQL 语句时,优化器将使用这些策略。所以,优化器并非总是使用上面所描述的每种存取路径技术。相反,根据优化等级,优化器使用各种不同的技术。优化等级的用途是通过它来指导 DB2 何时采用哪种搜索策略和优化技术。通常,优化器考虑的搜索策略越多,用于查询的存取方案就越好。然而,当优化器被指导去考虑的搜索策略越多,把 SQL 编译成可执行的存取路径的时间就越长。幸运的是,可以设置优化等级来限制优化查询时所应用的技术数目。对于较简单的查询、资源受限系统和动态 SQL,这是非常有用的。表 1 概括了优化等级。 表 1. DB2 优化等级。 等级 描述 0 指导优化器使用最少的优化来生成存取方案。只可使用嵌套循环连接和索引扫描访问方法。限制使用统计信息(例如,不考虑非一致性分布统计)。 1 类似于等级 0,但添加了归并连接、表扫描和非常基本的查询重写(再加一些额外的特性)。 3 极大地改进了等级 1,但比等级 3 所付出的编译代价要低得多。这个等级利用了所有可用的统计信息、大多数查询重写规则、列表预取装和汇总表路由。类似于等级 5,但它使用贪婪的连接枚举(Greedy join enumeration),而不是动态编程。 5 该等级最接近于 DB2 for OS/390® 所采用的查询优化。它提供了中等数量的优化,需要中等数量资源来编译。 7 提供了极多的优化,需要比等级 3 更多的资源来编译。优化器智能地确定何时不保证额外资源用于动态 SQL。对于兼有复杂和较简单的查询这种混合情形,等级 5 是很好的选择。 第 页 共 24 页 经验总结 9 该等级类似于等级 5,但它添加了一些在等级 5 中不可用的优化技术。该等级不会确定对于动态 SQL 什么时候会出现额外资源不足。 0 使用所有可用的优化技术。 虽然可以选择上表中所描述的任何查询优化等级,但只有很少一些情形才会使用等级 0 和 9。等级 0、1 和 2 使用贪婪连接枚举算法;对于复杂查询,这个算法与等级 3 及其之上的等级相比,考虑的备用计划极少,因此编译时间也少得多 。等级 3 和这之上的等级使用动态编程连接枚举(Dynamic Programming join enumeration)算法;这个算法与等级 0、1 和 2 相比,考虑的备用计划更多,这可能促成需要极其多的编译时间。 设定具体的查询优化等级方式取决于是使用静态 SQL 还是使用动态 SQL。对于静态 SQL 语句,在 PREP 和 BIND 命令上指定优化等级。SYSCAT.PACKAGES 目录表中的 QUERYOPT 列记录了用于绑定包的优化等级。动态 SQL 语句使用由 CURRENT QUERY OPTIMIZATION 专用寄存器指定的优化等级,可以用 SQL SET 语句来设置 CURRENT QUERY OPTIMIZATION。 最后,让我总结一下两类搜索策略及其特征。第一类,贪婪连接枚举,等级 0、1 和 2 使用该算法。使用贪婪连接枚举时,对于两个表,一旦选定连接方法之后,在进一步的优化期间,不会更改连接方法。所以,当连接许多表时,这种策略所选择的可能不是绝对最佳的存取方案。对于仅连接几个表的查询而言,另一类搜索策略(动态编程连接枚举)所选定的存取方案极有可能与贪婪连接枚举所选定的存取方案相同。随着正在连接的表的数目增加,动态编程连接枚举将需要更多的时间和资源。这比贪婪连接枚举更有可能选出最佳存取方案。 l 程序准备 The Query Compiler generates an executable plan for static SQL that is stored in the form of a package in the DB2 catalog tables. Again, for dynamic SQL, this process occurs at run-time and the access plan is stored in the package 第 页 共 24 页 经验总结 cache. For dynamic SQL, DB2 verifies authorities and objects at run-time. DB2 uses object descriptors stored in the catalog cache to accomplish this. If the required object descriptors are not present in the catalog cache, DB2 reads them into the catalog cache from the DB2 catalog using a direct read. After a prepare is done for dynamic SQL, the section is cached in the package cache. If the dynamic SQL is run again, DB2 will find a hit in the cache for the SQL section and another prepare will not be required. This results in less CPU consumption and results in better overall response to the application. There are restrictions on the types of parameters that dynamic SQL can use and still enable DB2 to use the cached statement, versus doing another prepare. 影响查询优化的配置参数 有一些配置参数影响 SQL 或 XQuery 编译器选择的访问方案。其中的许多参数都适合单一分区数据库环境,而某些参数仅适合分区数据库环境。在分区数据库环境中,用于每个参数的值应该在所有数据库分区上是相同的。 注: 当动态更改配置参数时,优化器可能会由于程序包高速缓存中的旧访问方案而不立即读取已更改的参数值。要复位程序包高速缓存,执行 FLUSH PACKAGE CACHE 命令。 在联合系统中,如果大多数查询都将访问昵称,那么在更改环境之前评估您发送的查询类型。例如,在联合数据库中,缓冲池不高速缓存数据源中的页,这些数据源是 DBMS 和联合系统中的数据。因此,增大缓冲区的大小并不保证当优化器为包含昵称的查询选择访问方案时将考虑其他访问方案备用。但是,优化器可以决定数据源表的本地具体化是否是成本最低的方法,或者是否是排序操作的必需步骤。在这种情况下,增加可用的资源可能会提高性能。 下列配置参数或因子影响 SQL 或 XQuery 编译器选择的访问方案: · 当创建或改变缓冲池时指定的缓冲池大小 当优化器选择访问方案时,优化器要考虑将页从磁盘访存至缓冲池的 I/O 成本并估计满足查询所需要的 I/O 次数。估计包括预测缓冲池使用情况,因为不需要其他的物理 I/O 来读取已在缓冲池中的页中的行。 优化器考虑 BUFFERPOOLS 系统目录表以及在分区数据库环境中的 BUFFERPOOLDBPARTITION 系统目录表中的 npages 列的值。 读取表的 I/O 成本可影响: o 如何连接两个表 o 是否将使用非集群索引来读取数据 · 缺省度(dft_degree) dft_degree 配置参数通过为 CURRENT DEGREE 专用寄存器和 DEGREE 绑定选项提供缺省值来指定并行性。值 1 表示无分区内并行性。值 -1 表示优化器根据处理器数目和查询类型来确定分区内并行度。 注: 除非通过设置 intra_parallel 数据库配置参数来启用内部并行处理,否则不会发生内部并行处理。 · 缺省查询优化类(dft_queryopt) 虽然在编译 SQL 或 XQuery 查询时可以指定查询优化类,但还可以设置缺省查询优化类。 第 页 共 24 页 经验总结 · 活动应用程序平均数(avg_appls) 优化器使用 avg_appls 参数来帮助估计运行时期间可用于所选访问方案的缓冲池的个数。较高的此参数值会影响优化器,使它选择在缓冲池的使用情况方面更节省的访问方案。如果指定值 1,那么优化器认为整个缓冲池将可用于应用程序。 · 排序堆大小(sortheap) 如果要排序的行占用的空间超过排序堆中可用的空间,那么执行几遍排序,每一遍都要对整个行集的某个子集排序。每遍排序的结果存储在缓冲池中的一个临时表内,可以将该表写入磁盘。当所有排序都完成时,将这些已排序的子集合并到单个排序的行集。如果排序不需要临时表来存储最终的已排序的数据列表,那么可认为该排序是“管道”排序。即,可以按单一的顺序访问方式来读取排序的结果。管道排序的性能优于非管道排序,所以应尽可能使用管道排序。 当选择访问方案时,优化器要通过下列操作来估计排序操作的成本,包括评估是否可使用管道传送排序: o 估计要排序的数据量 o 查看 sortheap 参数,以确定是否有足够的空间通过管道传送排序。 · 锁定列表的最大存储器(locklist)和升级前锁定列表的最大百分比(maxlocks) 当隔离级别是可重复读(RR)时,优化器考虑 locklist 和 maxlocks 参数的值,以确定是否可以将行级别锁定升级到表级别锁定。如果优化器估计将对表访问发生锁定升级,那么它为访问方案选择一个表级别锁定,这样就不会在查询执行期间产生锁定升级的开销。 · CPU 速度(cpuspeed) 优化器使用 CPU 速度来估计执行特定操作的成本。CPU 成本估计和各种 I/O 成本估计有助于选择查询的最佳访问方案。 一台机器的 CPU 速度可显著影响所选的访问方案。当安装或迁移数据库时,会自动将此配置参数设置为一个适当的值。除非您要在测试系统上为生产环境建立模型或评估硬件更改的影响,否则不应调整此参数。使用此参数为不同的硬件环境建立模型可以使您找出可为该环境选择的访问方案。要让数据库管理器重新计算此自动配置参数的值,将它设置为 -1。 · 语句堆大小(stmtheap) 尽管语句堆的大小不影响优化器选择不同的访问路径,但是,它会影响对复杂的 SQL 或 XQuery 语句执行的优化量。 如果未将 stmtheap 参数设置得足够大,您可能接收到警告,指示无足够可用内存来处理语句。例如,SQLCODE +437(SQLSTATE 01602)可能指示用于编译语句的优化量小于您请求的量。 · 通信带宽(comm_bandwidth) 优化器使用通信带宽来确定访问路径。优化器使用此参数中的值来估计在分区数据库环境的各数据库分区服务器之间执行特定操作的成本。 · 应用程序堆大小(applheapsz) 大模式要求应用程序堆中有足够的空间。 l SQL调整提示 l RUNSTATS DB2 优化器使用目录统计信息来确定最佳的访问路径,而更新这些目录统计信息所采用的主要方法就是运行 RUNSTATS 实用程序。当用户表中发生数据修改时,目录统计信息表不会自动被修改。必须在表和索引上执行 RUNSTATS 命令,用最新的信息更新目录表中的列。 第 页 共 24 页 经验总结 可以查询下列目录列,确定是否在表和索引上执行了 RUNSTATS: · 如果对于某个表,SYSCAT.TABLES 视图的 CARD 列显示的值为 -1,或者 STATS_TIME 列显示的值为 NULL,那么这表示没有对该表运行 RUNSTATS 实用程序。 · 如果对于某个索引,SYSCAT.INDEXES 视图的 NLEAF、NLEVELS 和 FULLKEYCARD 列显示的值为 -1,或者 STATS_TIME 列显示的值为 NULL,那么这表示没有对该索引运行 RUNSTATS 实用程序。 在第一次将数据载入到表中之后,就无法避免地要对表进行更新、删除和插入等形式的修改。可以添加或删除索引。特定列中的数据分布可能随时间发生改变。目录中不会反映这些修改,除非在这些表和索引上执行 RUNSTATS。一段时间之后,随着表和数据发生更改,目录统计信息可能会过时。 执行 RUNSTATS 是很重要的,但是频繁地运行可能会带来问题。对于较小的表,发出 RUNSTATS 命令将是一项普通任务。然而,随着表的增长,完成 RUNSTATS 命令将占用更多时间、CPU 和内存资源。最终,您必须考虑分配更多时间和资源来运行 RUNSTATS 与不执行该命令的潜在性能下降之间的平衡。通常只在关键查询的速度开始减慢时,管理员才会对 RUNSTATS 给予适当的注意。您可以通过制定高效、有效收集统计信息的策略,避免未经思考就调优查询和执行 RUNSTATS。 3.2.1 调整索引 l 索引和性能 索引是一个或多个键的集合,每个键指向表中的一行。SQL 优化器自动选择最有效率的访问表中数据的方法。当确定最快速的数据访问路径时,优化器会将索引考虑在内。 索引由数据库管理器用来: · 提高性能。在大多数情况下,使用索引访问数据的速度更快。虽然不能为视图创建索引,但为视图所基于的表创建的索引有时可以提高该视图的操作性能。 · 确保唯一性。具有唯一索引的表不能包含具有完全相同的键的行。 将数据添加到表时,除非已对该表和正在添加的数据执行了其他操作,否则简单地将数据追加至该表的底部。数据是无序的。搜索特定数据行时,必须检查从第一行到最后一行的每个表行。将索引用作按顺序访问表中的数据的一种方法,该顺序在其他情况下可能不可用。 可以使用数据行中的列值来标识整个行。可能需要一列或多列来标识该行。这种列称为键。一个列可以在多个键中使用。 索引按键中的值进行排序。键可以是唯一的,也可以是非唯一的。每个表应该至少有一个唯一键;但还可以有其他非唯一键。每个索引正好有一个键。例如,可以使用职员标识编号(唯一)作为一个索引的键,并使用部门号(非唯一)作为另一个索引的键。 第 页 共 24 页 经验总结 索引的类型 有四种类型的索引:唯一索引、非唯一索引、集群索引和非集群索引。 唯一索引和非唯一索引 唯一索引是这样一种索引,它通过确保表中没有两个数据行具有完全相同的键值来帮助维护数据完整性。 尝试为已经包含数据的表创建唯一索引时,将检查组成该索引的列中的值是否唯一;如果该表包含具有重复键值的行,那么索引创建过程将失败。为表定义唯一索引后,将在插入和更新操作期间强制唯一性。除了强制数据值的唯一性外,唯一索引还可用来提高查询处理期间检索数据的性能。 另一方面,非唯一索引不用于对与它们关联的表强制执行约束。相反,非唯一索引通过维护频繁使用的数据值的排序顺序,仅仅用于提高查询性能。 集群索引和非集群索引 索引体系结构分为集群或非集群。集群索引是根据数据在磁盘上排序所依据的那个键构建的索引。在某些关系数据库管理系统中,集群索引的叶子节点对应于实际数据,而不是对应于指向位于其他地方的数据的指针,非集群索引的叶子节点就对应于这种指针。 由于集群索引的叶子级别对应于实际数据这个事实,表中的数据将根据索引进行排序,因此,给定表中只能存在一个集群索引,但可以存在许多非集群索引。 非集群索引是在任何键的基础上构建的索引。每种关系都可以有一个集群索引和许多个非集群索引。集群索引通常将实际记录存储在数据结构中,因此其速度比非集群索引要快很多。非集群索引被强制仅将记录标识存储在数据结构中,并且需要至少一个其他 I/O 操作才能检索到实际记录。 重组与所选索引相关的表将对数据重新建立集群。建立了集群的索引对于带有范围谓词的列非常有用,因为它允许对表中的数据作更有效的顺序访问。由于相似的值在同一数据页上,从而减少页访存次数。 通常,表中只有一个索引可以具有较高的集群度。 主键或唯一键约束与唯一索引之间的差别 了解主唯一键约束与唯一索引之间没有很大差别这一点很重要。数据库管理器使用唯一索引和 NOT NULL 约束的组合来实现主键约束和唯一键约束的关系数据库概念。因此,唯一索引本身不强制执行主键约束,因为它们允许空值。(虽然空值表示未知值,但在建立索引时,将一个空值视为与其他空值相同。) 因此,如果唯一索引由单个列组成,那么只允许一个空值 - 多个空值将违反唯一约束。同样,如果唯一索引由多个列组成,那么值和空值的特定组合只能使用一次。 使用集群索引提高性能 由于集群索引使存储在页面中的数据的访问路径更线性化,所以它们可以提高大多数查询操作的性能。此外,由于具有相似索引键值的行都存储在一起,所以在使用集群索引时预取效率通常更高。 第 页 共 24 页 经验总结 但是,不能将集群索引指定为与 CREATE TABLE 语句配合使用的表定义的一部分。相反,只通过执行指定了 CLUSTER 选项的 CREATE INDEX 语句来创建集群索引。因此,如果要指定的主键是与 CREATE TABLE 语句配合使用的表定义的一部分 - 在创建主键后,就不能修改其关联索引。相反,在创建该表时不应指定主键,并且 CREATE INDEX ... CLUSTER 语句应用于创建集群索引。然后,ALTER TABLE 语句应用于在表中添加与创建的集群索引对应的主键。然后,此集群索引将用作表的主键索引。 通常,如果集群索引是唯一的,那么集群维护起来就更有效率。 双向索引 CREATE INDEX 语句的 ALLOW REVERSE SCANS 子句同时启用正向和反向索引扫描,也就是说,按创建索引时的顺序和相反(或反向)顺序。此选项允许您: · 便于使用 MIN 和 MAX 函数 · 访存先前的键 · 不需要数据库管理器创建临时表来进行反向扫描 · 消除冗余反向顺序索引 3.2.2 提高索引的性能的建议 1.在索引创建或重组时把INTRA_PARALLEL设为yes 2.定义大的utility堆栈 3.尽量把表和索引放在不同的表空间 4.及时地更新表和索引的统计信息 5.必要时,重组索引 6.对于内容快速变化的表,尽量用volatile类型 7.分析EXPLAIN取得关于index使用状况的信息。对于在查询中不用的index,删除它 3.2.3 自动汇总表和物化查询表 自动汇总表(ASTs)和物化查询表(MQTs) 物化查询表(MQT)是一种以一次查询的结果为基础定义的表。包含在物化查询表中的数据来自定义物化查询表时所基于的一个或多个表。而 总结表(也称自动总结表,AST)对于 IBM® DB2® Universal Database™(UDB)for Linux、 UNIX® 和 Windows®(DB2 UDB)的用户来说应该感到比较熟悉,它们可以看作是特殊的 MQT。fullselect 是总结表定义的一部分,它包含一个 GROUP BY 子句,该子句总结 fullselect 中所引用表中的数据。 您可以将 MQT 看作一种物化的视图。视图和 MQT 都是基于一个查询来定义的。每当视图被引用时,视图所基于的查询便会运行。但是,MQT 实际上则是将查询结果保存为数据,您可以使用 MQT 中的这些数据,而不是使用底层表中的数据。 第 页 共 24 页 经验总结 自动汇总表(ASTs)是一种特殊类型的 MQT,它们的 fullselect 包含一个 GROUP BY 子句,该子句总结 fullselect 中所引用表中的数据。 自动汇总表(ASTs) 物化查询表(MQTs) 3.3 减少连接数 减少连接数 减少锁等待 对于每个数据库都有一个锁列表,该列表包含所有同时连接到数据库的应用程序所持有的锁。在 32 位的平台上,一个对象上的第一个锁要求占 72 字节,而其他的锁要求占 36 字节。在 64 位平台上,第一个锁要求占 112 字节,而其他锁要求占 56 字节。 当一个应用程序使用的 LOCKLIST 的百分比达到 MAXLOCKS 时,数据库管理器将执行一次锁升级(lock escalation),在这个操作中将使得行锁换成单独的一个表锁。而且,如果 LOCKLIST 快要耗尽,数据库管理器将找出持有一个表上的最多行锁的连接,并将这些行锁换成表锁,以释放 LOCKLIST 内存。锁整个表可以大大减少并发性,死锁的几率也增加了。 如果 "Lock list memory in 使用 (Bytes)"超出了定义的 LOCKLIST 大小的 50%,那么应增加 LOCKLIST 4 KB 大小的页面的数量。如果发生了 "Lock escalations"或 鈥淓xclusive lock escalations鈥_,则应该或者增加 LOCKLIST,或者增加 MAXLOCKS,抑或同时增加两者。 关于锁的数据库快照部分包含大量有价值的信息。看看 "Locks held currently"、 "Lock waits"、 "Time database waited on locks (ms)"、 "Agents currently waiting on locks"和 "Deadlocks detected"中是否存在高值,如果有的话,就可能是差于最优访问计划、事务时间较长或者应用程序并发问题的症状。如果要发现死锁,那么需要创建一个针对死锁的事件监视器,事件监视器带有详细信息,以便查看当前正在发生的事情。 要了解关于锁问题的详细信息,请参阅 Bill Wilkins 撰写的文章 Diagnosing and Resolving Lock Problems with DB2 Universal Database。 您可以做下列事情来减少锁: 确保应用程序正在使用最低的隔离级别。 经常执行 COMMIT。 当执行很多更新时,在更新前显式地锁整个表(使用 LOCK TABLE 语句)。 尽量使用 Cursor Stability 隔离级别(缺省情况),以便减少被持有的共享锁的数量。(如果应用程序能够承受脏读,那么 Uncommitted Read 可以进一步减少锁。) 第 页 共 24 页 经验总结 LOCKTIMEOUT (DB) 指定应用程序在获得一个锁之前所等待的秒数。这可以帮助避免全局死锁的情况。如果该值为 -1,如果出现锁等待,则应用程序将会出现。Bill Wilkins 关于锁的文章 也以较大的篇幅包含了这一点。 对于生产系统中的 OLAP,一开始为 60 (秒)比较好,对于 OLTP 大约为 10 秒比较好。对于开发环境,应该使用 -1,以识别和解决锁等待的情况。如果有大量的并发用户,可能需要增加 OLTP 时间,以避免回滚。 如果 "Lock Timeouts"是一个较高的数,那么可能由以下原因造成:(1) LOCKTIMEOUT 的值太低,(2) 某个事务持有锁的时间有所延长,或者(3) 锁升级。 调优排序 排序会耗db2和服务器很在的资源。 包缓存 3.4 相关示例 3.4.1 物化查询表 建立物化查询表的时必须定义关联项为unique ,如果不定义将会报SQL20058N错,原因码是7. 错误描述如下: 7 当指定 REFRESH IMMEDIATE 时: o 具体查询表一定不能包含重复行 o 当指定 GROUP BY 子句时,所有 GROUP BY 荐必须包括在选择列表中 o 当指定包含 GROUPING SETS、CUBE 或 ROLLUP 的 GROUP BY 子句时,不能重复任何分组集合, 并且如果 C 是出现在 GROUPING SETS、CUBE 或 ROLLUP 中的可空 GROUP BY 项,则 GROUPING(C) 必须出现在选择列表中 o 如果没有 GROUP BY 子句,则每个基础表必须至少定义一个唯一键,而且这些键的所有列必 须出 现在具体查询表定义的选择列表中 alter table employee add unique (empno); alter table department add unique (deptno); 物化询表分为系统维护的MQT和用户维护的MQT. 第 页 共 24 页 经验总结 3.4.2 系统维护的MQT: create table emp as (select e.empno, e.firstnme, e.lastname, e.phoneno,d.deptno,substr(d.deptname, 1, 12) as department, d.mgrno from employee e, department d where e.workdept = d.deptno) data initially deferred refresh immediate 通过以面的语句不能进行查询,必须对它执行 SET INTEGRITY 语句 set integrity for emp immediate checked not incremental IMMEDIATE CHECKED 子句规定,根据用于定义该 MQT 的查询对数据进行检查,并刷新数据。 NOT INCREMENTAL 子句规定对整个表进行完整性检查。 REFRESH后面的参数分为IMMEDIATE 和DEFERRED 如果是IMMEDIATE ,当employee 和department 更新后emp 表就能够查询到新增的数据。 如果是DEFERRED,当employee 和department 更新后emp 表不能查询到相关的数据。 必须通过REFRESH TABLE 语句来刷新。 REFRESH TABLE emp 不管是 REFRESH DEFERRED 还是 REFRESH IMMEDIATE 类型的系统维护的 MQT,对它们的 insert、update 或 delete 操作都是不允许的 3.4.3 用户维护的 MQT 只有 REFRESH DEFERRED 物化查询表可以定义为 MAINTAINED BY USER。不能对用户维护的 MQT 发出 REFRESH TABLE 语句(用于系统维护的 MQT)。但是,用户维护的 MQT 却 允许对它们执行 insert、update 或 delete 操作。 创建和使用用户维护的MQT: 1. 创建用户维护的MQT create table ontario_1995_sales_team as (select distinct e.empno, e.firstnme, e.lastname, e.workdept, e.phoneno, 'Ontario' as region, year(s.sales_date) as year from employee e, sales s where e.lastname = s.sales_person and year(s.sales_date) = 1995 and left(s.region, 3) = 'Ont') data initially deferred refresh deferred maintained by user 2. 初始化表,取消表的检查暂挂状态 第 页 共 24 页 经验总结 set integrity for ontario_1995_sales_team materialized query immediate unchecked 3. 用户将需要的数据导出到一个临时文件里: export to ontario_1995_sales_team.del of del select distinct e.empno, e.firstnme, e.lastname, e.workdept, e.phoneno, 'Ontario' as region, year(s.sales_date) as year from employee e, sales s where e.lastname = s.sales_person and year(s.sales_date) = 1995 and left(s.region, 3) = 'Ont' 4. 将临时文件的数据导入用户维护的MQT表里: import from ontario_1995_sales_team.del of del insert into ontario_1995_sales_team 5. 用户维护的MQT与系统维护的MQT的区别就是用户维护的MQT可以把表进行增,删,改: insert into ontario_1995_sales_team values ('006900', 'RUSS', 'DYERS', 'D44', '1234', 'Ontario', 1995) 3.4.4 自动汇总表(总结表) 总结表是一种特殊类型的 MQT,它们的 fullselect 包含一个 GROUP BY 子句,该子句总结 fullselect 中所引用表中的数据。 创建总结表: create table sales_summary as (select sales_person, region, sum(sales) as total_sales from sales group by sales_person, region) data initially deferred refresh deferred 刷新总结表: refresh table sales_summary 如果不执行以上操作将会出现以下错误提示: SQL0668N 由于表 "ZHOUJIAN.SALES_SUMMARY" 上的原因码为 "1",所以不允许操作。 SQLSTATE=57016 当对基础表进行增,删,改的时候都必须对总结表进行刷新,总结表的数据才能及时更新。 第 页 共 24 页 经验总结 3.4.5 staging表 staging 表是一个类似SQL复制中的中间表,它是用来记录MQT关连的基础表里面的变化。 创建MQT表: create table emp_summary as (select workdept, job, count(*) as count from employee group by workdept, job) data initially deferred refresh deferred 创建staging 表: create table emp_summary_s for emp_summary propagate immediate 初始化MQT表和staging 表: set integrity for emp_summary materialized query immediate unchecked set integrity for emp_summary_s staging immediate unchecked 在这个时候,我们进行对汇总表进行查询会发现没有任何数据: select * from emp_summary 当我们刷新汇总表的时候数据库报错: refresh table emp_summary SQL1594W 数据库管理器将非增量数据的完整性保持为未验证。 SQLSTATE=01636 在基础表里面新增一条记录: insert into employee values ('006900', 'RUSS', 'L', 'DYERS', 'D44', '1234', '1960-05-05', 'FIELDREP', 5, 'M', '1940-04-02', 10000, 100, 1000) staging 表里相就的会增加一条记录: select * from emp_summary_s WORKDEPT JOB COUNT GLOBALTRANSID GLOBALTRANSTIME -------- -------- ----------- -------------------... -----------------------------... D44 FIELDREP 1 x'00000000000000CD' x'20050822201344536158000000' 而此时MQT表的记录没有增加: select * from emp_summary WORKDEPT JOB COUNT 第 页 共 24 页 经验总结 -------- -------- ----------- 0 record(s) selected. 重新刷新MQT表并查询,发现MQT表的数据已经能够查询到了。 C:\Documents and Settings\zhoujian>db2 refresh table emp_summary SQL1594W 数据库管理器将非增量数据的完整性保持为未验证。 SQLSTATE=01636 select * from emp_summary WORKDEPT JOB COUNT -------- -------- ----------- D44 FIELDREP 1 1 record(s) selected. 3.4.6 相关内容 通过以下语句可以查看MQT表的相关信息: select substr(tabname,1,24) as tabname, dtype, substr(bname,1,24) as bname, btype from syscat.tabdep where tabschema = 'ZHOUJIAN' and dtype = 'S' 3.5 存在问题 DB2数据库优化方式本文中只列出了几种,还有很多的优化方案需要针对相应的需求才能实施。 第 页 共 24 页

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享文档获得金币 ] 8 人已下载

下载文档