《仓库管理_数据仓库开发系列培训课程》由会员分享，可在线阅读，更多相关《仓库管理_数据仓库开发系列培训课程（48页珍藏版）》请在金锄头文库上搜索。
1、数据仓库开发系列培训 DB2 SQL 性能 - 1 - DB2 SQL 性能 数据仓库开发数据仓库开发系列培训系列培训 http:/ 讲师：赵坚密讲师：赵坚密 日期：日期：20132013 年年 7 7 月月 1717 日日 数据仓库开发系列培训 DB2 SQL 性能 - 2 - DBDB2 2 SQLSQL 性能性能 赵坚密（） 培训介绍 本次课程主要包括四个方面：DB2 基础、DB2 逻辑设计、DB2 SQL 性能、数据仓库基础。 讲述了长期积累的数据库开发和设计知识， 以及数据仓库知识， 也有经济资本计算引擎开发 等实战经验的分享。 本课程预计安排 4-5 次课程，七月两次，上下的八、九
2、月份完成。 附件是部分课程资料，课程资料尚在完善中，之后会陆续发给大家。 课程目标： 1、了解 DB2 数据库的常见对象与存储对象，深入理解缓冲池和表空间的原理，并掌握 表空间和缓冲池的设计； 2、掌握第三范式原理，并能够在将来的设计中使用； 3、掌握索引的原理，并且在调优中能够灵活运用； 4、掌握 DB2 数据库常用特性表分区、MDC、MQT、DPF，并能够选择性的在数据仓库项目 中使用； 5、了解数据量对 SQL 性能的影响，掌握常见 SQL 的性能因素，并指导日常工作； 6、了解常见数据仓库的概念和设计。 本文内容 本文介绍了数据量对多种不同 SQL 性能的影响程度， 以及如何在设计层面
3、规避数据的增 长对 SQL 性能的严重影响。重点讲解了常见 SQL 写法的性能关系，避免糟糕的 SQL。同时融 入了笔者在经济资本计算引擎开发中的实战经历，对 DPF 架构下的开发有很大的借鉴意义。 1、数据量与 SQL 性能：数据量的增长对不同 SQL 的性能影响是不一样的，所以我们要 按照数据量的增长对性能的影响程度来分类和识别 SQL，在设计和开发阶段就定位和优化， 避免随着系统的运行而性能下降。 2、 保证 SQL 语句执行效率： 良好的物理存储和准确的统计信息是 SQL 执行效率的保障。 本节介绍那些操作会造成良好的物理存储结构遭到破坏，以及如何收集数据的统计信息。 3、SQL 优化
4、指引：制定 SQL 的书写规则，并有效地执行，能够很大程度上避免糟糕的 SQL。 4、 经济资本计算引擎开发实战介绍： JAVA 中的数据处理和存储过程中有什么不同， DPF 下如何编写 SQL 才是最优的？ 5、开始行动：简要介绍了性能监控。 本文适用操作系统平台为 IBM AIX 5.3，也可用于一般 UNIX 平台；数据库为 IBM DB2， 版本 9.1。 阅读说明 本文主要面向数据库设计和开发人员和性能调优人员。 杭州滨江 2013 年 7 月 3 日 数据仓库开发系列培训 DB2 SQL 性能 - 3 - 数据仓库开发系列培训 DB2 SQL 性能 - 4 - 目录目录 前言 错误
5、！未定义书签。错误！未定义书签。 阅读说明 - 2 - 1 数据量与 SQL 性能 - 6 - 1.1 增长的数据量 . - 6 - 1.2 操作对数据量增加的敏感程度 . - 7 - 1.2.1 受数据量增加的影响不大 . - 7 - 1.2.2 受到数据量增加的线性影响 . - 7 - 1.2.3 受到数据量增加的非线性影响 . - 7 - 2 保证 SQL 语句执行效率 - 8 - 2.1 分而治之，对付大数据 . - 8 - 2.1.1 分区表 . - 8 - 2.1.2 数据库分区 . - 9 - 2.1.3 分区表和数据库分区比较 . - 10 - 2.2 建立索引 . - 11
6、- 2.3 运行统计和重组 . - 12 - 2.3.1 通过运行统计来收集索引信息 . - 12 - 2.3.2 存储过程里执行运行统计语句 . - 13 - 2.3.3 重组表中的数据 . - 14 - 2.4 减少对数据库的更新和删除操作 . - 14 - 2.4.1 更新操作 . - 14 - 4.4.2 删除操作 . - 16 - 3 SQL 优化指引 - 19 - 3.1 谓词 . - 19 - 3.2 多余的连接 . - 20 - 3.3 子查询 . - 20 - 3.4 外连接 . - 21 - 3.5 UNION ALL 的使用 - 21 - 3.6 Having 子句 .
7、- 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 - 4 经济资本计算引擎开发介绍 - 24 - 4.1 环境介绍 . - 24 - 4.2 EC 优化之一：引擎计算存储过程 - 24 - 4.2.1 优化手段 . - 24 - 4.2.2 优化分析 . - 24 - 4.2.3 优化举
8、例 . - 26 - 数据仓库开发系列培训 DB2 SQL 性能 - 5 - 4.3 EC 优化手段之二： - 36 - 6 开始行动 - 42 - 6.1 用 topas 监控硬件使用情况 . - 42 - 6.2 从执行时间来确定主要矛盾 . - 42 - 6.3 进一步观察以决策 . - 44 - 6.3.1 DB2 Visual Explain - 44 - 6.3.2 DB2exfmt . - 46 - 6.3.3 DB2expln . - 47 - 6.3.4 表快照监视器 . - 47 - 6.4 不同数据库处于不同实例 . - 47 - 6.5 开始行动 - 48 - 数据仓库
9、开发系列培训 DB2 SQL 性能 - 6 - 1 数据量与数据量与 SQL 性能性能 1.1 增长的数据量增长的数据量 在数据量增加时我们总是要面对许多特殊的挑战。这包括高效搜索庞大的 表，如何避免数据量稍有增长就可能出现的性能下降。 尤其是，某些应用出于管理或者业务分析的目的，如数据仓库应用，需要在 线保存数月，甚至数年不经常使用的数据。我们将会面临危机的考验批处理 程序的耗时慢慢超过了它们可用的时间范围，干扰了客户的其他正常活动。 1 1 1 1 1 1 1 1 1 1 11 1 1 1 11 1 1 1 1 120 0 20 40 60 80 100 数 据 量 时间 第一次危机 第二
10、次危机 1 1 1 1 交易数据遗留数据参考数据 项目的不同阶段，数据量经常会变化，如图所示。一开始，除了相对少量的 参考数据被加入数据库之外，几乎没有任何东西。在达到目标数据量（数据库以 “巡航速度” 运行时能处理的数据量） 之前， 第一次严重的性能问题通常会出现。 当数据量很少或中等时，从最终用户角度看不出糟糕的查询和糟糕的算法的影 响。 硬件本身的处理能力会隐藏巨大错误，完整扫描几十万条记录的表也不到一 秒时间。 我们可能严重地滥用硬件能力来弥补程序的错误直到数据量变得相 当可观时，真正的错误才被发现。 当项目第一次出现性能危机时，通常会进行“专家调优” ，增加几个本应一 开始就有的索引
11、。接下来的系统可能不太稳定，直到数据量达到目标量。通常会 有两个“目标量” ：计划目标量（系统设计应管理的数据量，通常会被高估）和 实际目标量（系统实际处理的数据量，通常稍有超出，因为遗留数据在项目最后 会被考虑进来） 。第二个性能危机（而且是较严重的危机）通常会随着实际目标 量的到来而出现。当系统投入生产环境，架构设计的弱点被重新评估，一些关键 处理被积极重写，系统最后达到“巡航速度” 。随着业务的自然增长平稳成 长和指数成长都有可能，数据量也会随之增长。 数据仓库开发系列培训 DB2 SQL 性能 - 7 - 上面描述了一个新的数据库应用生命周期的头几个月，颇有几分讽刺意味， 但这比“应有
12、情况”更接近现实，因为导致这些问题的简单错误还是时常出现。 压力、没时间进行充分测试，含糊不清的规格说明无论多努力工作，还是会 有错误。不过，造成无法挽回失败的，是数据库设计错误和架构选择错误这 两个主题密切相关，而且是系统的基础。如果地基不够坚固，就必须把整栋建筑 推倒重建， 而其他错误需要某种程度上的检修。 当然， 不是所有危险都必定发生， 编程时我们必须预先考虑数据量的增长，而且面对逐渐增加的数据量时，必须尽 快找出使性能快速恶化的查询并改写他们。 1.2 操作对数据量增加的敏感程度操作对数据量增加的敏感程度 当数据量增加时，对性能的影响程度因 SQL 操作不同而不同。有些 SQL 操
13、作的性能受数据量增加的影响不大，有些 SQL 操作的性能则随着数据量增加而 线性下降，还有些 SQL 操作面对大数据量性能非常糟糕。 1.2.1 受数据量增加的影响不大受数据量增加的影响不大 通常，基于主键的搜索受数据量的影响不大，无论是 1000 笔还是 1,000,000 笔不会有显著差异。常见的 B 树索引，其结构趋于扁平，效率很高，基于主键 搜索单笔记录的性能不会受到表大小的影响。 1.2.2 受到数据量增加的线性影响受到数据量增加的线性影响 最终用户通常认为，要返回的记录数量为原来的两倍，则查询会花更多的时 间。但实际上，许多 SQL 操作花了两倍的执行时间，用户却没有意识到，就像
14、通过全表扫描逐一返回记录时发生的情况一样。考虑聚合函数，例如计算 max() 一定只返回单一记录，但 DBMS 所操作的记录数可能很多，不过最终用户只看 到单一的记录被返回，所以他们会抱怨性能随着时间而下降。确保情况不会变糟 的唯一方法，就是使用另一个条件（例如日期范围） ，对要处理的记录数量设定 上限。设定上限能让数据量保持在控制范围内。对于 max()的例子，可以查询给 定日期之后的最大值，而不是所有值中的最大值。增加查询条件不是单纯的技术 问题，还依赖于业务需求，但限定查询范围作为可选手段，值得在设计时进行讨 论。 1.2.3 受到数据量增加的非线性影响受到数据量增加的非线性影响 数据量
15、增加时， 排序操作所收的影响比扫描操作还大， 因为排序是复杂操作， 一般而言需要多遍处理。对 100 条随机的记录排序，所需成本并不是 10 条记录 的 10 倍，而是大约 20 倍。排序 1000 条记录所需成本，比排序 10 条记录平均高 300 倍。 然而在实际中，记录很少是随机存储的，即使没有使用“聚集索引”也是如 数据仓库开发系列培训 DB2 SQL 性能 - 8 - 此。 DBMS 有时使用有序索引， 以预期的顺序读取纪录， 而不是先读取后排序 读取较大的有序集合时，性能降低一点并不奇怪。但注意，排序性能降低常是间 歇发生的，因为较小型的排序将全部在内存中执行，而较大型的排序（涉及多个 有序子集的合并）则需要经有序子集临时存储到慢得多的硬盘中。所以，通过调 整分配给排序的内存数量来改善排序密集型操作的性能， 是常见且有效的调优技 巧。 2 保证保证 SQL 语句执行效率语句执行效率 2.1 分而治之，对付大数据分而治之，对付大数据 为了避免数据量的增加， 而暴露出来的两次危机， 我们可以采取 “分而治之” 的策略。目前，在主流数据库中有两种技术：分区表和数据库分区。这两种方式 都是将大表数据进行物理上的拆分，使得数据量敏感的操作，拆分成多个子操作 成为可
