`

DB2数据库SQL编码优化基础

阅读更多
简介

  当要保证用 IBM DB2® Universal Database™(DB2 UDB)和 Borland® 工具(如 Delphi™、C++Builder™ 或 Kylix™)构建的企业应用程序拥有最优性能时,程序员可以利用 DB2 优化器的能力来处理即使是“难以处理的”SQL 语句并给出有效的存取路径。尽管如此,拙劣编码的 SQL 和应用程序代码仍可能给您带来性能问题,通过学习几条基本准则可以轻易地避免这些问题。我将向您演示 DB2 优化器的工作方式,并提供编写能发挥优化器最大效率的 SQL 的准则。但即使拥有了 DB2 的优化能力,编写有效的 SQL 语句仍可能是一件复杂的事情。如果程序员和开发人员还不熟悉关系数据库环境,这件事就尤其显得棘手。因此,在我们深入研究编码 SQL 以获得最佳性能的细节之前,先花一些时间来回顾 SQL 基础知识。

  基础知识

  由于 SQL 与过程化语言不同,它提供了更高的抽象级别,因此它可以让程序员把精力集中到他们需要 什么样的数据,而不是 如何检索数据。您不必使用嵌入式数据导航指令来编码 SQL。DB2 会分析 SQL,并“在幕后”制定数据导航指令。这些数据导航指令叫作 存取路径。让 DBMS 确定到数据的最优存取路径解除了程序员肩上沉重的负担。此外,数据库可以更好地理解它存储的数据的状态,从而可以生成到数据的更有效和动态的存取路径。其结果就是适当使用的 SQL 可以用于更快的应用程序开发。

  另一个 SQL 特性是它不仅仅是一种查询语言。您还可以使用它来定义数据结构;控制对数据的访问;以及插入、修改和删除数据的发生。通过提供一种公共语言,SQL 简化了 DBA、系统程序员、应用程序员、系统分析员和最终用户之间的通信。当项目的所有参与者都使用同一种语言时,他们之间所建立起来的协作就可以减少整体系统开发时间。

  历史证明,保证 SQL 成功的最重要的一个特性就是它使用类似英语的语法轻松地检索数据的能力。理解这种语言比理解数据页面的结构和程序源代码要容易得多:

  SELECT LASTNAME

  FROM EMP

  WHERE EMPNO = '000010';

  想想看:当访问文件中的数据时,程序员必须编码指令来打开文件、开始一个循环、读取记录、检查 EMPNO 字段是否等于适当的值、检查文件结尾、回到循环的开头等。

  SQL 本来就是非常灵活的。它使用自由格式的结构,该结构可以让用户开发 SQL 语句来适合他们的需要。DBMS 在执行之前会分析每个 SQL 请求,以检查语法是否正确和优化该请求。SQL 语句不需要从任何给定的列中开始,您可以将它们串在一行中,或者把它们拆成几行。例如,以下这条单行的 SQL 语句与我前面使用的三行示例等价:

  SELECT LASTNAME FROM EMP WHERE EMPNO = '000010';

  SQL 的另一个灵活特性是您可以用许多形式不同但功能等价的方法来制定一个请求。例如:SQL 可以连接表或嵌套查询。您始终可以将嵌套查询转换成等价的连接。您可以在大量的函数和谓词中看到这一灵活性的其它示例。具有等价功能的特性的示例包括:

BETWEEN vs <= / >=

IN vs 一系列和 OR 配合的谓词

INNER JOIN vs FROM 子句中串在一起并用逗号分隔的表

OUTER JOIN vs 带有 UNION 的简单 SELECT 和相关的子查询

CASE 表达式 vs 复杂的 UNION ALL 语句

  SQL 展示的这一灵活性并不总是称心的,因为形式不同但功能等价的 SQL 公式可以提供非常不同的性能。我将在本文的以后部分讨论该灵活性所造成的结果,并提供开发有效的 SQL 的准则。

  如我所说的,SQL 指定了要检索或操作什么数据,但没有指定数据库如何完成这些任务。这就使 SQL 本身变得很简单。如果您能够记得关系数据库的一次处理一个集合(set-at-a-time)的特点,您就开始掌握 SQL 的本质和性质了。一条 SQL 语句可以作用于多行。作用于一组数据而不需要建立如何检索和操作数据的能力将 SQL 定义成非过程化语言

  因为 SQL 是一种非过程化语言,所以一条语句可以代替一系列过程。同样,由于 SQL 使用集合级别的处理以及 DB2 优化查询来确定数据导航逻辑,所以这是可能的。有时,如果不使用 SQL 语句,一条或两条 SQL 语句可以完成的任务就需要完整的过程化程序来完成。

  优化器

  优化器是 DB2 的心脏和灵魂。它分析 SQL 语句并确定可以满足每条语句的最有效的存取路径(请参阅图 1)。DB2 UDB 通过解析 SQL 语句来确定必须访问哪些表和列,从而完成该操作。DB2 优化器然后查询存储在 DB2 系统目录中的系统信息和统计信息,以确定完成满足 SQL 请求所必需的任务的最佳方法。

  图 1. 运行中的 DB2 优化

  

  优化器在功能上等价于一个专家系统。专家系统是一个标准规则集合,当与情境数据组合时,它返回一个“专家”意见。例如,医学专家系统采用一个规则集合,用来确定哪些药可以用于哪些疾病,将规则集与描述疾病症状的数据组合,并将知识库应用于输入症状的列表。DB2 优化器会根据存储在 DB2 系统目录中的情境数据和 SQL 格式的查询输入来生成对数据检索方法的专家意见。

  在 DBMS 中优化数据访问的概念是 DB2 最强大的能力之一。请记住,您访问 DB2 数据时应告诉 DB2 要检索什么,而不是如何检索。无论数据实际上是如何存储和操作的,DB2 和 SQL 都可以访问该数据。从物理存储特征中分离出访问标准叫作物理数据独立性。DB2 的优化器是完成该物理数据独立性的组件。

  如果您不要索引,DB2 仍然能够访问数据(尽管效率会降低)。如果将一列添加到正在被访问的表中,DB2 仍然可以在不更改程序代码的情况下操作数据。因为到 DB2 数据的物理存取路径并不是由程序员在应用程序中编码的,而是由 DB2 生成的,所以这种情况是完全有可能发生的。

  这个特点与非 DBMS 系统非常不同,在那种系统中,程序员必须知道数据的物理结构。如果有索引,程序员就必须编写适当的代码来使用该索引。如果某人删除了索引,程序就不能工作,除非程序员进行更改。而使用 DB2 和 SQL 就不必如此。这一灵活性完全归功于 DB2 自动优化数据操作请求的能力。

  优化器根据许多信息执行复杂的计算。要使优化器的工作方式直观化,可以将优化器想象成执行一个四步骤的过程:

  1、接收并验证 SQL 语句的语法。

  2、分析环境并优化满足 SQL 语句的方法。

  3、创建计算机可读指令来执行优化的 SQL。

  4、执行指令或存储它们以便将来执行。

  这个过程的第二步是最有趣的。优化器怎样决定如何以它的方式执行您可以发送的大量 SQL 语句?

  优化器有许多类型的优化 SQL 的策略。它如何选择在优化存取路径中使用这些策略中的哪一个?IBM 并没有发布优化器如何确定最佳存取路径的真正和深入的详细信息,但优化器是一个 基于成本的优化器。这意味着优化器将始终尝试为每个查询制定减少总体成本的存取路径。要实现这个目标,DB2 优化器会应用查询成本公式,该公式对每条可能的存取路径的四个因素进行评估和权衡:CPU 成本、I/O 成本、DB2 系统目录中的统计信息和实际的 SQL 语句。

  性能准则



  因此,只要记住关于 DB2 优化器的信息,您就可以实现这些准则以便获得更好的 SQL 性能:

  1) 使 DB2 统计信息保持最新 :如果没有存储在 DB2 系统目录中的统计信息,优化器在优化任何事物时都会遇到困难。这些统计信息向优化器提供了与正在被优化的 SQL 语句将要访问的表状态相关的信息。存储在系统目录中的统计信息的类型包括:

关于 表的信息,包括总的行数、关于压缩的信息和总页数;

关于 列的信息,包括列的离散值的数量和存储在列中的值的分布范围;

关于 表空间的信息,包括活动页面的数量;

索引的当前状态,包括是否存在索引、索引的组织(叶子页的数量和级别的数量)、索引键的离散值的数量以及是否群集索引;

关于表空间和索引节点组或分区的信息。

  当执行 RUNSTATS 或 RUN STATISTICS 实用程序时,统计信息就会填充 DB2 系统目录。您可以从控制中心(Control Center)、批处理作业或通过使用命令行处理器来调用该实用程序。一定要与您的 DBA 一起工作以确保在适当的时候积累统计信息,尤其是在生产环境中。

  2) 构建适当的索引 :也许您为保证最佳 DB2 应用程序性能而可以做的最重要的事就是根据应用程序使用的查询为您的表创建正确的索引。当然,说总比做更容易。但我们可以从一些基础开始。例如,考虑以下这条 SQL 语句:

SELECT LASTNAME, SALARY FROM EMP WHERE EMPNO = '000010' AND DEPTNO = 'D01'




  什么索引会对这个简单查询有作用?首先,考虑您可以创建的所有可能的索引。您的第一个简短列表可能看起来如下:

EMPNO 上的 Index1

DEPTNO 上的 Index2

EMPNO 和 DEPTNO 上的 Index3

  这是一个好的开始,Index3 可能是最好的。它让 DB2 使用索引来立即查找满足 WHERE 子句中的两个简单谓词的行。当然,如果您已经有许多关于 EMP 表的索引,您也许应该检查再创建另一个关于表的索引所带来的影响。要考虑的因素包括:

  修改影响 :DB2 将自动维护您创建的每个索引。这表示对该表的每个 INSERT 和每个 DELETE 都将不仅在表中插入和删除,而且会在其索引中插入和删除。如果您对在索引中的列的值进行 UPDATE 操作,那么您还更新了该索引。因此索引加快了检索过程的速度,但减慢了修改的速度。

  现有索引中的列 :如果在 EMPNO 或 DEPTNO 上已经有了一个索引,那么创建另一个关于该组合的索引也许并不明智。但是,更改另一个索引以添加缺少的列也许可以起作用。但也不一定,因为索引中列的顺序也许会根据查询而有很大差异。例如,考虑以下查询:

  SELECT LASTNAME, SALARY

  FROM EMP

  WHERE EMPNO = '000010'

  AND DEPTNO > 'D01';

  在这种情况下,在索引中应该首先列出 EMPNO。然后列出 DEPTNO,从而允许 DB2 对第一列(EMPNO)执行直接索引查找,然后针对大于号扫描第二列(DEPTNO)。

  而且,如果已经存在关于这两列的索引(一个关于 EMPNO,一个关于 DEPTNO),DB2 可以使用它们来满足该查询,因此创建另一个索引也许是没有必要的。

  这种特定查询的重要性 :查询越重要,那么您可能就越应该通过创建索引来进行调优。如果您正在编码 CIO 要每天都运行的查询,那么您应该确保它提供最佳性能。因此,为该特定查询构建索引是很重要的。反之,职员的查询也许就没有必要看得那么重,所以也许应该利用现有索引来执行查询。当然,决定取决于应用程序对业务的重要性 - 而不只是用户的重要性。

  索引设计涉及的内容比到目前为止我所讨论的要多得多。例如,您也许要考虑索引重载以实现仅索引访问(index-only access)。如果 SQL 查询要寻找的所有数据都包含在索引中,那么 DB2 也许只使用索引就可以满足该请求。请考虑我们前面的 SQL 语句。给定了关于 EMPNO 和 DEPTNO 的信息,我们要寻找 LASTNAME 和 SALARY。我们还从创建关于 EMPNO 和 DEPTNO 列的索引开始。如果我们在索引中还包含了 LASTNAME 和 SALARY,我们就不再需要访问 EMP 表,因为我们需要的所有数据都已经在索引中。该技术可以大大提高性能,因为它减少了 I/O 请求的数量。

  请记住:使每个查询成为仅索引访问是不谨慎,甚至也是不可能的。您应该谨慎使用该技术以便用于特别棘手或重要的 SQL 语句。


SQL 编码准则



  当您编写访问 DB2 数据的 SQL 语句时,要确保遵循以下三个编码 SQL 的准则以获得最佳性能。当然,SQL 性能是一个复杂的话题,而且了解 SQL 的执行方式的每一个细微差别可能要花一生的时间。但是,这些简单的规则可以使您进入开发高性能 DB2 应用程序的正轨。

  第一条规则是始终在每条 SQL SELECT 语句的 SELECT 列表中只提供 确实需要检索的那些列 。另一种说法就是“不要使用 SELECT *”。简写 SELECT * 表示您要检索正在被访问的表中的所有列。这适用于“快捷但不恰当的方式获得的“(quick and dirty)查询,但却是应用程序的坏实践,因为:

  DB2 表在将来可能需要更改,以包括附加列。SELECT * 也会检索那些新的列,而如果没有进行费时的更改,您的程序也许无法处理附加的数据。

  DB2 将为被请求返回的每一列消耗附加资源。如果程序不需要数据,它就不会寻找它。即使程序需要每一列,最好根据 SQL 语句中的名称来显式地寻找每一列,以便增加清晰度和避免以前犯的错误。

  不要寻找您已经知道的东西 。这听起来似乎显而易见,但大多数程序员都曾经违反过这条规则。举一个典型的示例,考虑以下 SQL 语句有什么错误:

  SELECT EMPNO, LASTNAME, SALARY

  FROM EMP

  WHERE EMPNO = '000010';

  放弃吗?问题是 EMPNO 已经包含在 SELECT 列表中。您已经知道了 EMPNO 将等于值“000010”,因为那就是 WHERE 子句要 DB2 做的事。但在 WHERE 子句中列出了 EMPNO,DB2 还会尽职地检索该列。这会产生附加开销,从而降低性能。

  在 SQL 中 使用 WHERE 子句过滤数据 ,而不是在程序中到处使用它进行过滤。这也是新手容易犯的错误。在 DB2 将数据返回到程序之前,最好由 DB2 过滤数据。这是因为 DB2 使用附加 I/O 和 CPU 资源来获取每一行数据。传递到程序的行越少,SQL 的效率就越高:

  SELECT EMPNO, LASTNAME, SALARY

  FROM EMP

  WHERE SALARY > 50000.00;

  与只读取所有数据而不使用 WHERE 子句,然后在程序中检查 SALARY 是否大于 50000.00 的做法相比,该 SQL 更好。

  使用参数化查询 。参数化 SQL 语句包含了变量,也称作参数(或参数标记)。典型的参数化查询使用这些参数来代替文字值,因此 WHERE 子句条件可以在运行时更改。通常程序被设计成最终用户可以在运行查询之前提供参数的值。这允许使用一个查询根据提供给参数的不同的值返回不同的结果。

  参数化查询的主要性能好处是优化器可以制定在重复执行语句时能够再使用的存取路径。与每次 WHERE 子句中需要一个新值就发出一条全新的 SQL 语句相比,这可以给程序增加很大的性能收益。

  但是,这些规则并不是 SQL 性能调优的最终和最高目标 - 决不是。您可能需要附加的、深入的调优。但遵循前面的规则将确保您不会犯降低应用程序性能的“新手”错误。

  特定数据库应用程序开发技巧

  无论您使用的是 Delphi、C++Builder 还是 Kylix,某些技巧和准则将帮助您确保在访问 DB2 数据时获得好的性能。例如,在某些情况下,使用 dbExpress TM来代替 ODBC/JDBC 或 ADO 可以提高查询性能。dbExpress 是用于从 Delphi(或 Borland Kylix™)处理动态 SQL 的跨平台接口。

  要确保在您的应用程序中经常发出 COMMIT 语句。COMMIT 语句控制工作单元。发出 COMMIT 会将自上一个 COMMIT 语句之后的所有工作“永远”记录到数据库中。在发出 COMMIT 之前,可以使用 ROLLBACK 语句回滚工作。当修改数据(使用 INSERT、UPDATE 和 DELETE)但没有发出 COMMIT 时,DB2 将在数据上加一把锁并保持该锁 - 这把锁会使其它应用程序在等待检索被锁住的数据时超时。通过在工作完成时发出 COMMIT 语句,并且确保数据是正确的,就释放了该数据以供其它应用程序使用。

  另外,构建应用程序时要考虑使用情况。例如,当某个特定查询返回几千行给最终用户时,要慎重处理。对于在程序和最终用户之间的在线交互,很少会用到几百行以上的数据。您可以在 SQL 语句上使用 FETCH FIRST nROWS ONLY 子句来限制返回到查询的数据量。例如,考虑以下查询:

  SELECT EMPNO, LASTNAME, SALARY

  FROM EMP

  WHERE SALARY > 10000.00

  FETCH FIRST 200 ROWS ONLY;

  该查询将只返回 200 行。如果有超过 200 行符合条件也没有关系;如果您尝试从查询中 FETCH(访存)超过 200 行,DB2 将用 +100 SQLCODE 表明数据结束。当您想要限制返回给程序的数据量时,这种方法很有用。

  DB2 支持另一个名为 OPTIMIZE FOR nROWS 的子句,该子句不限制要返回给游标的行数,但从性能角度看可能是有帮助的。使用 OPTIMIZE FOR nROWS 子句告诉 DB2 如何处理 SQL 语句。例如:

  SELECT EMPNO, LASTNAME, SALARY

  FROM EMP

  WHERE SALARY > 10000.00

  OPTIMIZE FOR 20 ROWS;

  这告诉 DB2 尝试尽快访存前 20 行。如果您的 Delphi 应用程序在显示从数据库检索出来的数据行时每次显示 20 行,那么这将非常有用。

  对于只读游标,使用 FOR READ ONLY 子句确保游标无歧义。Delphi 不能在 DB2 游标中执行位置更新,因此将 FOR READ ONLY 附加到每条 SELECT 语句后面可以使游标成为无歧义的只读游标,从而对 DB2 有所帮助。例如:

  SELECT EMPNO, LASTNAME, SALARY

  FROM EMP

  WHERE SALARY > 10000.00

  FOR READ ONLY;

  结束语

  了解 SQL 编码以获得最佳性能的基础知识将使您的 Delphi 企业应用程序的性能立即得到增长。但我只揭露了冰山一角。您需要学习日益增多的 SQL 的复杂类型,包括连接、子选择和联合等。您还需要学习如何最好地编写这些 SQL 语句以及如何发现 DB2 选择的存取路径来满足您的 SQL 请求。确实,还有许多要学习。但是您已经学习了一些如何最大限度地利用 DB2 SQL 的初步知识,尽情地享用这些知识吧。
分享到:
评论

相关推荐

    DB2数据库基础学习

    ### DB2数据库基础学习 #### 一、DB2数据库概览 DB2是IBM公司开发的一款关系型数据库管理系统,自1983年发布以来,不断进化,支持多种平台,包括Windows、Linux、Unix和z/OS等。DB2不仅提供高性能的数据存储和检索...

    DB2数据库导入导出

    DB2 数据库导入导出 DB2 数据库导入导出的操作方法是指将用户的 DB2 数据库导出并导入到自己的 DB2 数据库上。由于 DB2 没有直接的数据库导出功能,因此需要导出表结构、表数据,然后在导入。 一、导出表结构 在 ...

    DB2数据库设计和最高性能原则

    在讨论DB2数据库设计时,不能不提的是程序编码逻辑和SQL语句质量。一个良好的设计不仅仅是逻辑上无懈可击,还需要具备高效的执行效率。数据库设计的好坏直接关系到程序的性能,因此在设计阶段就需要对性能有所考虑。...

    JDBC连接db2数据库.zip

    在本例中,"JDBC连接db2数据库.zip"是一个压缩包,包含了连接IBM DB2数据库所需的JDBC驱动。 DB2是IBM公司开发的一款关系型数据库管理系统,广泛应用于企业级应用,支持多种操作系统平台,如Windows、Linux、Unix和...

    DB2 jar包和连接DB2数据库代码

    在Java编程环境中,如果要与DB2数据库进行交互,通常需要引入特定的JDBC驱动,也就是DB2的jar包。本资源提供的两个jar包可能包含了DB2的JDBC驱动,它们是连接DB2数据库所必需的库文件。 1. **JDBC驱动介绍**:Java ...

    IBM DB2 数据库使用小技巧

    以下是对给定文件中提到的DB2数据库管理相关知识点的详细解释。 #### 1. 查看节点目录 `db2listnodedirectory` 该命令用于列出系统中所有已注册的节点。节点目录提供了关于网络中所有可用DB2实例的信息,包括节点...

    DB2数据库协议解析-DRDAV5.3

    DB2数据库协议解析-DRDA V5.3 在数据库领域,分布式关系数据库访问架构(Distributed Relational Database Architecture,简称DRDA)是一种标准通信协议,由IBM开发,用于连接不同系统上的数据库管理系统(DBMS)。...

    Mybatis连接DB2数据库生成逆向工程

    在逆向工程之前,你需要在DB2数据库中执行这个SQL脚本来创建表。 接下来,执行逆向工程的步骤如下: 1. 在Java项目中引入Mybatis Generator的依赖。 2. 配置`mybatisGenerator_db2.xml`,确保数据库连接信息正确。...

    java db2数据库驱动

    这些驱动程序使得开发者能够在Java环境中执行SQL查询,进行数据操作和管理DB2数据库。在本篇中,我们将详细探讨Java连接DB2数据库所需的驱动包以及如何使用它们。 1. **Java Database Connectivity (JDBC)**: JDBC...

    DB2 SQL 数据库函数

    ### DB2 SQL 数据库函数详解 #### 一、概述 DB2是一款由IBM开发的关系型数据库管理系统(RDBMS),广泛应用于各种规模的企业级环境中。它提供了丰富的SQL函数,用于处理数据,包括数学运算、字符串操作、日期时间处理...

    DB2-SQL 数据库函数

    DB2-SQL数据库函数是IBM DB2数据库管理系统中的一系列内置操作工具,用于处理数据的各种需求,包括数学计算、字符串操作、日期时间处理等。这些函数不仅能够简化SQL查询语句,提高数据处理效率,还能确保数据的准确...

    db2数据库常用命令

    `db2 -tvf &lt;脚本名称&gt;.sql` 可导入之前导出的表结构,`db2move &lt;数据库名&gt; load -lo replace` 用于加载数据并替换原有数据。 10. **查看表结构和表**: `db2 describe table &lt;表名&gt;` 显示表结构,`db2 list ...

    DB2数据库表导入MYSQL

    下面我们将详细探讨如何从DB2数据库将表导入到MySQL中,并在MyEclipse环境中进行操作。 首先,了解DB2和MySQL的基础是至关重要的。DB2是由IBM开发的一款关系型数据库管理系统,广泛应用于大型企业,支持复杂的事务...

    DB2数据库CODE码

    在DB2数据库管理中,SQLCode扮演着至关重要的角色,它是DB2处理SQL语句后返回的一个整数值,用于指示SQL语句执行的结果。SQLCode可以是正值、零或负值,分别表示警告、成功和错误。了解和解读SQLCode对于诊断和解决...

    db2数据库运维常用命令集 数据库运维.docx

    ### DB2数据库运维常用命令详解 #### 一、常识性命令 DB2作为IBM的一款高性能关系型数据库管理系统,在企业级应用中占据着重要的地位。为了更高效地管理和维护DB2数据库,掌握一些基本且常用的运维命令是十分必要的...

    DB2 SQL存储过程语法官方权威指南

    其中,存储过程是DB2中一个非常重要的特性,它允许开发者在数据库内编写可重用的代码块,这些代码块可以接受输入参数、返回输出参数并执行复杂的SQL操作。本文将详细介绍DB2 SQL存储过程的基本语法及其关键组成部分...

    推荐一款支持DB2的sql编辑软件

    为了更高效地管理和操作DB2数据库,选择一款功能强大的SQL编辑软件至关重要。本文将重点推荐并详细介绍一款能够支持DB2的SQL编辑软件,并探讨其关键特性和优势。 标题提及的“推荐一款支持DB2的sql编辑软件”,这...

    AIX和WINDOWS下DB2数据库互导

    【AIX和Windows下DB2数据库互导】的知识点涵盖了多个方面,主要涉及数据库的备份、迁移、数据抽取、DDL创建、数据加载以及外键和索引的建立。以下是详细说明: 1. **数据备份与恢复**:在AIX环境下,DB2数据库可以...

    JAVA数据库驱动 DB2 JDBC驱动

    在Java中,通过JDBC驱动程序,开发者可以编写代码来执行SQL语句,操作DB2数据库。本主题将深入讲解关于“JAVA数据库驱动 DB2 JDBC驱动”的相关知识点。 1. **JDBC概述** - JDBC(Java Database Connectivity)是...

Global site tag (gtag.js) - Google Analytics