简介
调优 Informix SQL 时要考虑许多性能问题。其中的两个主要问题是 访问方法 和 连接方法。本文中，我将讨论您应该选择什么，以及如何确定每种选择的成本。一旦理解了自己的选择，就可以通过调整 OPTCOMPIND 参数，或者通过添加查询指示或索引，来影响优化器，从而选择最佳的查询执行计划。
访问方法
访问方法是指优化器从数据库表中读取（更确切地说是检索）数据的方法。基本上有两种方法。
最简单的方法是连续读取表中的数据，就是按照我们通常调用它的方法来执行表扫描。当无论如何都必须读取表中大多数数据时，或者当表没有索引时，优化器就会选择执行表扫描。
另一种方法就是使用索引。如果一些列上有索引，那么优化器也许能使用键索引扫描（key-only index scan）。
优化器将比较每种方法的成本，确定使用最好的一种。它将评估查询执行成本的方方面面，例如所需的磁盘 I/O 操作数目、将被检索的行数、排序成本等。
如果表中的数据进行了群集化处理，或者是有物理次序的，那么表扫描的成本就可能相当低。当请求磁盘页上的第一行时，就将该磁盘页读入内存中的一个缓冲页（buffer page）。下次请求该页上的数据时，就不需要再从磁盘进行读取该页。对该页上的后续行的请求都可以在这个缓冲区中得到满足，直到将该页上的所有行都处理完为止。当已经用完一个页面时，就必须读入下一行集的页。
为了加速这个过程，当第一个页面仍然在内存中时，Informix 就可以将下一数据页读入内存。为了有效利用这项功能，必须在配置文件中正确设置两个参数：RA_PAGES 和 RA_THRESHOLD。关于如何设置这些参数的细节，请参阅 。如果您数据库中的表只占用了少数几个区段，那么连续行的磁盘页将被放置在磁盘中的连续位置上，在读取数据页时，磁盘访问臂（access arm）就不必移动太多。此外，延时成本也会十分低。
对于小型表（有 1,000 行或更少行）来说，表扫描的成本不会很昂贵，尤其在 SQL 或应用程序中重复使用某个表的时候，这样，该表会一直保存在内存中。实际上，对内存中的表进行表扫描要比通过索引搜索同一表快得多，尤其是在使用 Informix 轻型扫描机制时。但是如果某个表有 100,000 或更多的行，那么重复的连续扫描将对性能产生致命影响。您可能需要考虑向该表添加索引，并将索引用作从表中访问数据的方法。
实际上，除了群集表之外，几乎所有表中的数据都不是有序的。拥有繁重事务的系统就是如此，例如银行系统。该例中，表扫描极其昂贵，因为 Informix 引擎必须随机地访问数据。当以与磁盘上其物理次序无关的顺序读取表中的行时，磁盘 I/O 成本更高。因为不是从磁盘连续读取页面的，所以在读取每个页面之前，都需要进行查找并存在循环延迟。因此，与按顺序读取相比，按随机顺序读取数据时的磁盘访问时间要长得多。更好的方法就是使用键索引扫描。
索引是用来定位数据页的顺序指针或键的集合。索引扫描是从下面的根页向叶子页面进行的。因为根页使用十分频繁，所以它通常驻留在内存缓冲区中。用以读取叶子页面的成本取决于索引的大小、查询的筛选器以及索引的惟一性。如果每个值仅在索引中出现一次，且查询是一个连接（join），那么所连接的每一行都需要非连续地查找索引，然后非连续地访问表中相关联的行。然而，如果每个不同的索引值都有许多重复的行，而所关联的表是高度群集的，那么通过索引添加的连接成本可能很低。
虽然索引项是连续的，但是不保证包含相邻索引项的行将驻留在同一数据页上。大多数情况下，对于通过索引定位的每一行，都必须进行单独的磁盘访问来读取该页。如果某个行的大小大于页大小（即大于 2 KB），那么包含该行的页面可能被置换出内存。换言之，在后续数据页被读入内存并被处理之前，它们可能已经从缓冲区中删除，并写回磁盘。那么，在以后处理这些后续页时，还必须再次将它们读入内存。
优化器将决定是否可以使用索引来评估筛选器。为此，索引列是一个包含索引的单列，或者是复合索引中指定的第一个列。如果只需要索引中包含的值，而不需要读取这些行。那么，每次可以直接从索引中读取值时，省略数据页的查找方法都要快得多。
在下列情况下，优化器将使用索引：
当索引了一列时。数据库服务器首先通过查找索引中的行，定位表中的相关行。本例中，键索引将比表扫描快得多。
当索引了一列，且要进行比较的值是另一表中的一行（连接表示）时。数据库服务器使用索引来查找匹配的值。
当处理 ORDER BY 子句时。如果该子句中所有的列都是以用单个索引要求的顺序出现的，那么数据库服务器就使用索引来按顺序读取行，因而避免了排序。
当处理 GROUP BY 子句时。如果该子句中所有的列都出现在一个索引中，那么数据库服务器就从索引中读取具有相等键的分组，而无需在从其表中检索行之后进行附加的处理。
如何为查询判定哪一种访问方法是最有效的呢？这没有固定的标准，因为每个组织的业务需求都是不同的。然而，有一些通用的指导原则。如果查询中的表极其小（通常少于 1,000 行）或者是群集表，而且查询根本没有选择性的话，那么使用表扫描会更好一些。但是如果这些表极其庞大，有许多行（比如几百万行），而且查询具有一定的选择性的话，那么使用索引就好得多。
优化器在选择访问方法方面通常非常智能，但是如果它没有按您所期望的方式进行，那么您可以通过使用查询命令影响其行为，强迫优化器选择您认为最好的访问方法。您可以分析查询执行计划输出，以确定这是否是必需的。
连接方法
如果查询包含不止一个表，那么就应该使用筛选器来连接那些表，以避免笛卡尔（Cartesian）连接。当评估查询执行计划时，优化器计算并比较每种连接方法的成本，然后选择要使用的最佳方法。最常用的连接方法就是嵌套循环连接、分类合并连接和动态哈希连接。现在，让我们详细查看每一种连接。
嵌套循环连接
在嵌套循环连接中，将扫描第一个（或外部）表，以查找满足查询规则的行。对于在外部表中找到的每一行，数据库服务器将在第二个（或内部）表中搜索其相应的行。通过索引扫描还是表扫描来访问外部表则取决于该表。如果有筛选器，数据库服务器首先会应用它们。如果内部表没有索引，那么数据库服务器就会将在表上构建索引的成本与连续扫描的成本进行比较，然后选择成本最低的那一种方法。总成本取决于连接列上是否有索引。如果连接列上有一个索引，那么其成本会相当低；否则，数据库服务器就必须对所有表（外部和内部表）执行表扫描。
动态哈希连接
动态哈希连接是 IDS 版本 7 以及更高版本中的新连接方法。当一个或多个连接表上没有索引时，或者当数据库服务器必须从所有连接表中读取大量行时，就使用这种方法。在该方法中，需要扫描其中的一个表，通常扫描较小的那个表，用它在内存中创建一个哈希表。通过哈希函数，将具有相同哈希值的行放在一个 bucket 中。在扫描完第一个表并将它放在哈希表中之后，就扫描第二个表，并在哈希表中查找该表中的每一行，看是否可以进行连接。如果连接中有更多表，那么数据库服务器将对每个连接表执行相同的操作。
动态哈希连接包含两个动作：构建哈希（或者是我们所称的构建阶段），以及探测哈希表（或探测阶段）。在构建阶段，数据库服务器读取一个表，并且在应用所有现有筛选器之后，在内存中创建一个哈希表。可以在概念上将哈希表认为是一系列的 bucket，每个 bucket 所拥有的地址是通过应用哈希函数从键值导出的。数据库服务器不会在特定的哈希 bucket 中对键进行分类。在探测阶段，数据库服务器将读取连接中的其他表，如果存在筛选器，就应用它们。在满足筛选器条件的每个行中，数据库服务器将对键应用哈希函数，并探测哈希表以查找匹配的键值。哈希连接通常比分类合并连接快，因为它没有涉及到分类操作。
子查询
子查询是 SQL 语句里的 SQL 语句，更确切地说，是嵌套的 SQL 语句。这些 SQL 语句可以用于许多情况下，如 SELECT、DELETE、UPDATE 和 INSERT。例如，下列 SQL 语句使用子查询来计算薪水最少的雇员数目：
select count(*) form employeewhere salary = (select min(salary) from employees);
有两种类型的子查询： 不相关的 和 相关的。不相关的子查询不依靠主查询来获取信息；因此，它们增加了主查询的能力和灵活性。另一方面，相关子查询需要一些信息来定位内部表中的行，这暗示在选择符合该查询条件的每一行时，数据库服务器都必须运行相关查询。以上查询包含一个相关子查询，因为主查询和子查询依靠于同一个表，即 employees 表。相关子查询将极大地降低数据检索的速度，对于拥有几百万个行但没有索引的大型表的非选择性查询更是如此。我们需要注意相关子查询，并尽可能地避免使用它们。
表和索引的区段
区段（extent）是一块物理位置连续的空间。然而，无法保证区段本身也是连续的。因此，如果一个表或索引只有一个区段，那么它会将其所有的数据或键保存在一块物理空间中。否则，其数据或键将分散在该表或索引所占用的所有区段中。物理数据或键的连续性对于表扫描或键-索引扫描的速度十分重要。当数据连续时，用于访问磁盘上数据的时间最少，数据库服务器可以更快地读取数据。这对于键来说也是一样的。如果表拥有太多区段，那些区段就很可能是交错的。这通常会损害性能，因为当您为某个表检索数据时，磁盘头必须查找属于该表的多个非连续的区段，而不是查询一个包含连续物理页面的大型区段。这会相当大地减慢磁盘查找速度。
如果表或索引有十个或更多的区段，那么您需要考虑重新构建除大型分段表之外的表和索引，合并其区段，以获得更好的性能。关于如何对表和索引进行评估并为它们分配区段大小的细节，请参阅 。
索引层次和惟一性
索引的层次和惟一性也会影响数据的访问速度。索引层次越多，Informix Dynamic Server 就必须执行越多的读取来获取索引叶子节点，并且就将花更多的时间来获得真正的数据。此外，如果在执行数据插入、删除或更新操作的过程中拆分或合并叶子节点，那么可能要花费更长的时间来让整个索引适应其更改。例如，如果一个索引只有两层，那么只需调整这两个层即可，但是，如果它有四个层，那么如果在索引的任一部分中进行了节点拆分或合并，那么就需要对所有的四层进行相应的调整。四层索引的调整时间当然就比仅仅两层索引的调整时间要长得多。OLTP 环境中尤其如此，该环境中的事务十分巨大，并且不断地要插入、删除和更新一些数据。因此，如果索引超过四层，那么您可能需要考虑删除一些层或用较少的层次来重新构建它，以便获得更好的性能。
索引的惟一性是指一个索引拥有多少个重复键。一个高度重复的索引可能会严重影响访问、更新和删除数据的速度。假设 customer 表中的 customer_type 列上有一个索引，那么只会有 5 种可能的 customer_type 代码。如果该表有一百万行，那么平均 200,000 行就会有相同的 customer_type 代码。B-树将存储键值，以及一个指向每个物理行的指针列表。在必须删除或更新任一键值时，就会出现问题。Informix Dynamic Server 必须搜索所有重复键，删除或更新它们，以找出正确的键，这样的话，要搜索 200,000 个键！
理想情况是，索引中的每个键值都是惟一的。现在，我们如何查明索引是不是惟一的呢？可以通过查询两个系统表来获得这方面的信息：systables 和 sysindexes。下面是该查询：
select nrows, nunique from systables a, sysindexes bwhere a.tabid = b.tabidand idxname = index_name
分段和 PDQ 优先级
分段和 PDQ 优先级也对于数据的检索速度有极大的影响，并有助于表扫描。分段是一种将数据库表和索引巧妙地划分成更小单位（叫作“分段表”）的方法；每个分段表都是表或索引中的一组行或索引键。用于分段的算法称作“分布模式”。Informix Dynamic Server 支持两种分布模式：轮循和基于表达式的分布模式。轮循分布模式使用 Informix 的内部规则将表和索引分段。而基于表达式的分布模式则使用用户定义的规则将表和索引分段。
分段的最终目标就是通过直接访问来包含满足 SQL 查询的数据分段表，减少检索时间。例如，如果将一个 100,000 行的大型表分成 10 个分段表，每个分段表包含 10,000 行，那么 Informix Dynamic Server 就可以直接进入包含查询所请求数据的分段表中，不用对其他分段表进行扫描。这将限制 IDS 仅扫描 100,000 行中的 10,000 个相关行。那是一个极大的节省，不是吗？关于分段的细节，请参阅 。
当应用 PDQ 优先级时，运用分段存储工作得最好。PDQ 优先级的工作原理是“各个击破（divide and conquer）”，Informix Dynamic Server 将复杂查询拆散成多个较小的部分。将每个小部分分配给不同的线程，然后，每个线程则并行地执行部分查询。当它们完成属于它们的那部分查询处理时，数据库服务器就重组结果数据集，并对其进行处理，然后将最后的答案展示给终端用户。因为 PDQ 优先级使用比常规方法更多的线程来处理一个查询，所以它将缩短数据访问时间。接下来的问题是，我们如何维持系统和 Informix 资源的使用平衡，使它不会受损害，更确切地说是不会减慢非 PDQ 查询的执行呢？这在 OLTP 环境中尤为重要，该环境中的事务十分巨大，而且查询响应时间十分关键。
示例和分析
现在，让我们获得一些我在工作中收集的具体的 SQL 调优示例，并看一看如何可以应用上面所讨论的这些调优指导原则。
我们的公司是一家通信公司，其数据库被用来存储通信性能管理数据、事件订阅列表以及网络元素配置信息。数据库中包含几百个表，大约有 40 GB 的数据。它运行在有 6 个 CPU 和 4 GB RAM 的 Sun Solaris 2.8 操作系统上。硬件平台是一台 Sun Enterprise 3500，带有 6 个 18 GB 的磁盘和两个相互镜像的 36 GB 的磁盘。
示例 1
原始查询及其执行计划：
QUERY:------SELECT UNIQUE MNE.MSO_ID, MNE.NE_INST, ANE.MSO_ID, 1 FROM NE MNE, NE ANE, CELL, NEIGH, CELL NCELL WHERE MNE.NE_TYPE = 0 {ACG} AND CELL.ACG_INSTANCE = MNE.NE_INST AND NEIGH.ADV_CELL_INST = CELL.CELL_INSTANCE AND NCELL.CELL_INSTANCE = NEIGH.NEIGH_CELL_INST AND ANE.NE_INST = NCELL.ACG_INSTANCE AND ANE.MSO_ID != MNE.MSO_ID AND MNE.WORKSPACE_ID = 41 AND ANE.WORKSPACE_ID = 41 AND CELL.WORKSPACE_ID = 41 AND NEIGH.WORKSPACE_ID = 41 AND NCELL.WORKSPACE_ID = 41Estimated Cost: 13555Estimated # of Rows Returned: 38 1) root.cell: INDEX PATH (1) Index Keys: workspace_id tz_inst (Serial, fragments: ALL) Lower Index Filter: root.cell.workspace_id = 41 2) omcadmin.mne: INDEX PATH Filters: omcadmin.mne.ne_type = 0 (1) Index Keys: workspace_id ne_inst (Serial, fragments: ALL) Lower Index Filter: (root.cell.acg_instance = omcadmin.mne.ne_inst AND omcadmin.mne.workspace_id = 41 )NESTED LOOP JOIN 3) root.neigh: INDEX PATH (1) Index Keys: workspace_id adv_cell_inst neigh_cell_inst (Key-Only) (Serial, fragments: ALL) Lower Index Filter: (root.neigh.adv_cell_inst = root.cell.cell_instance AND root.neigh.workspace_id = 41 )NESTED LOOP JOIN 4) omcadmin.ncell: INDEX PATH (1) Index Keys: workspace_id cell_instance (Serial, fragments: ALL) Lower Index Filter: (omcadmin.ncell.cell_instance = root.neigh.neigh_cell_inst AND omcadmin.ncell.workspace_id = 41 )NESTED LOOP JOIN 5) omcadmin.ane: INDEX PATH Filters: omcadmin.ane.mso_id != omcadmin.mne.mso_id (1) Index Keys: workspace_id ne_inst (Serial, fragments: ALL) Lower Index Filter: (omcadmin.ane.ne_inst = omcadmin.ncell.acg_instance AND omcadmin.ane.workspace_id = 41 )NESTED LOOP JOIN 优化的查询及其执行计划：
QUERY:------select unique mne.mso_id, mne.ne_inst, ane.mso_id, 1 from ne mne, ne ane, cell, neigh, cell ncellwhere cell.workspace_id = 41 and cell.acg_instance = mne.ne_inst and cell.workspace_id = mne.workspace_id and mne.ne_type = 0 and mne.mso_id !=ane.mso_id and mne.workspace_id=ane.workspace_id and ane.ne_inst=ncell.acg_instance and cell.cell_instance = neigh.adv_cell_inst and ncell.cell_instance = neigh.neigh_cell_inst and cell.workspace_id = ncell.workspace_id and ncell.workspace_id = neigh.workspace_idEstimated Cost: 6555Estimated # of Rows Returned: 38 1) root.cell: INDEX PATH (1) Index Keys: workspace_id tz_inst (Serial, fragments: ALL) Lower Index Filter: root.cell.workspace_id = 41 2) omcadmin.mne: INDEX PATH Filters: omcadmin.mne.ne_type = 0 (1) Index Keys: workspace_id ne_inst (Serial, fragments: ALL) Lower Index Filter: (root.cell.acg_instance = omcadmin.mne.ne_inst AND root.cell.workspace_id = omcadmin.mne.workspace_id )NESTED LOOP JOIN 3) root.neigh: INDEX PATH (1) Index Keys: workspace_id adv_cell_inst neigh_cell_inst (Key-Only) (Serial, fragments: ALL) Lower Index Filter: (root.cell.cell_instance = root.neigh.adv_cell_inst AND omcadmin.mne.workspace_id = root.neigh.workspace_id )NESTED LOOP JOIN 4) omcadmin.ncell: INDEX PATH (1) Index Keys: workspace_id cell_instance (Serial, fragments: ALL) Lower Index Filter: (omcadmin.ncell.cell_instance = root.neigh.neigh_cell_inst AND omcadmin.mne.workspace_id = omcadmin.ncell.workspace_id )NESTED LOOP JOIN 5) omcadmin.ane: INDEX PATH Filters: omcadmin.mne.mso_id != omcadmin.ane.mso_id (1) Index Keys: workspace_id ne_inst (Serial, fragments: ALL) Lower Index Filter: (omcadmin.ane.ne_inst = omcadmin.ncell.acg_instance AND root.cell.workspace_id = omcadmin.ane.workspace_id )NESTED LOOP JOIN
正如您可以从以上输出中观察到的，我们进行的惟一更改就是添加了一个条件来限制原始查询（为 cell 表指定 workspace_id），并将原始查询中的所有表连接在一起。那些更改将原始查询的成本缩减了一半，同时也极大地减少了查询响应时间。
示例 2
原始查询及其执行计划：
QUERY:------select min(ds0_start) from srate where srate.line_instance = 99930 and srate.workspace_id = 41 and srate.ds_instance in ( select ds_inst from ds where stream_type = 10 and workspace_id = 41)Estimated Cost: 5175Estimated # of Rows Returned: 1 1) root.srate: INDEX PATH Filters: root.srate.ds_instance = ANY(1) Index Keys: workspace_id line_instance (Serial, fragments: ALL) Lower Index Filter: (root.srate.line_instance = 99930 AND root.srate.workspace_id = 41 ) Subquery: --------- Estimated Cost: 5171 Estimated # of Rows Returned: 6400 1) root.ds: INDEX PATH Filters: root.ds.stream_type = 10 (1) Index Keys: workspace_id owner_ne (Serial, fragments: ALL) Lower Index Filter: root.ds.workspace_id = 41
优化的查询及其执行计划：
QUERY:------select min(ds0_start) from srate , ds where srate.line_instance = 99930 and srate.workspace_id = 41 and srate.ds_instance = ds.ds_inst and ds.stream_type = 10 and srate.workspace_id = ds.workspace_idEstimated Cost: 7Estimated # of Rows Returned: 1 1) root.srate: INDEX PATH (1) Index Keys: workspace_id line_instance (Serial, fragments: ALL) Lower Index Filter: (root.srate.line_instance = 99930 AND root.srate.workspace_id = 41 ) 2) root.ds: INDEX PATH Filters: root.ds.stream_type = 10 (1) Index Keys: workspace_id ds_inst (Serial, fragments: ALL) Lower Index Filter: (root.srate.ds_instance = root.ds.ds_inst AND root.srate.workspace_id = root.ds.workspace_id )NESTED LOOP JOIN
这里我们所做的惟一更改就是去掉了原始查询中的子查询。我们直接将两个表连接在一起，而不再使用子查询。因为该连接是在索引上执行的，所以其成本要低得多，而数据访问则快得多。
示例 3
原始查询及其执行计划：
QUERY:------SELECT workspace_impctlog.ne_instance, 1, br_status FROM workspace_impctlog, ne WHERE workspace_impctlog.workspace_id = 40 AND ((workspace_impctlog.impct_type = 10 AND workspace_impctlog.trans_type =! 2) OR workspace_impctlog.impct_type = 30) AND workspace_impctlog.ne_instance = ne.ne_inst AND ne.workspace_id = 40 AND ne.ne_type =! 8 AND ne.ne_inst NOT IN (SELECT ne_inst from ne WHERE ne.workspace_id = 40 and ne_type = 8)Estimated Cost: 7880Estimated # of Rows Returned: 1 1) root.workspace_impctlog: SEQUENTIAL SCAN Filters: (root.workspace_impctlog.workspace_id = 40 AND ((root.workspace_impctlog.impct_type = 10 AND root.workspace_impctlog.trans_type != 2 ) OR root.workspace_impctlog.impct_type = 30 ) ) 2) root.ne: INDEX PATH Filters: root.ne.ne_type != 8 (1) Index Keys: workspace_id ne_inst (Key-First) (Serial, fragments: ALL) Lower Index Filter: (root.workspace_impctlog.ne_instance = root.ne.ne_inst AND root.ne.workspace_id = 40 ) Key-First Filters: (root.ne.ne_inst != ALL)NESTED LOOP JOIN Subquery: --------- Estimated Cost: 7878 Estimated # of Rows Returned: 16 1) root.ne: INDEX PATH Filters: root.ne.ne_type = 8 (1) Index Keys: workspace_id manager_ne_inst (Serial, fragments: ALL) Lower Index Filter: root.ne.workspace_id = 40
优化的查询及其执行计划：
QUERY:------SELECT ne_inst from ne WHERE ne.workspace_id = 40unionSELECT ne_inst from ne WHERE ne.workspace_id = 40 AND ne_type = 8 into temp ne_temp1Estimated Cost: 103Estimated # of Rows Returned: 10957 1) root.ne: INDEX PATH (1) Index Keys: workspace_id ne_inst (Key-Only) (Serial, fragments: ALL) Lower Index Filter: root.ne.workspace_id = 40Union Query:------------ 1) root.ne: INDEX PATH Filters: root.ne.ne_type = 8 (1) Index Keys: workspace_id manager_ne_inst (Serial, fragments: ALL) Lower Index Filter: root.ne.workspace_id = 40QUERY:------SELECT workspace_impctlog.ne_instance, 1, br_status FROM workspace_impctlog, ne WHERE workspace_impctlog.workspace_id = 40 AND workspace_impctlog.impct_type = 10 AND workspace_impctlog.trans_type =! 2 AND workspace_impctlog.ne_instance = ne.ne_inst AND ne.workspace_id = 40 AND ne.ne_type =! 8 AND ne.ne_inst NOT IN (SELECT * from ne_temp1)unionSELECT workspace_impctlog.ne_instance, 1, br_status FROM workspace_impctlog, ne WHERE workspace_impctlog.workspace_id = 40 AND workspace_impctlog.impct_type = 30 AND workspace_impctlog.ne_instance = ne.ne_inst AND ne.workspace_id = 40 AND ne.ne_type =! 8 AND ne.ne_inst NOT IN (SELECT * from ne_temp1)Estimated Cost: 5Estimated # of Rows Returned: 2 1) root.workspace_impctlog: SEQUENTIAL SCAN Filters: ((root.workspace_impctlog.impct_type = 10 AND root.workspace_impctlog.workspace_id = 40 ) AND root.workspace_impctlog.trans_type != 2 ) 2) root.ne: INDEX PATH Filters: root.ne.ne_type != 8 (1) Index Keys: workspace_id ne_inst (Key-First) (Serial, fragments: ALL) Lower Index Filter: (root.workspace_impctlog.ne_instance = root.ne.ne_inst AND root.ne.workspace_id = 40 ) Key-First Filters: (root.ne.ne_inst != ALL)NESTED LOOP JOIN Subquery: --------- Estimated Cost: 1 Estimated # of Rows Returned: 1 1) omcadmin.ne_temp1: SEQUENTIAL SCANUnion Query:------------ 1) root.workspace_impctlog: SEQUENTIAL SCAN Filters: (root.workspace_impctlog.impct_type = 30 AND root.workspace_impctlog.workspace_id = 40 ) 2) root.ne: INDEX PATH Filters: root.ne.ne_type != 8 (1) Index Keys: workspace_id ne_inst (Key-First) (Serial, fragments: ALL) Lower Index Filter: (root.workspace_impctlog.ne_instance = root.ne.ne_inst AND root.ne.workspace_id = 40 ) Key-First Filters: (root.ne.ne_inst != ALL )NESTED LOOP JOIN Subquery: --------- Estimated Cost: 1 Estimated # of Rows Returned: 1 1) omcadmin.ne_temp1: SEQUENTIAL SCAN
我们首先尽可能多地替换原始查询中的子查询，然后使用 UNION 集合运算符代替 OR 运算符。优化后的执行计划输出显示出了极大的提高。
示例 4
下列查询是找出 Willson Market 公司发出了多少订单，并检索其合同信息，例如地址、电话等。该查询及其执行计划如下所示：
QUERY:------select companyname, address, city, state, country, phone, fax, email, count(*)from customer a, order bwhere a.custid = b.custidand a.custid = 'WILMK'group by 1,2,3,4,5,6,7,8Estimated Cost: 1135675Estimated # of Rows Returned: 1Temporary Files Required For: Group By 1) omcadmin.a: SEQUENTIAL SCAN Filters: informix.a.custid = 'WILMK' 2) omcadmin.b: SEQUENTIAL SCAN Filters: Table Scan Filters: informix.b.custid = 'WILMK'DYNAMIC HASH JOIN Dynamic Hash Filters: informix.a.custid = informix.b.custid
其成本太高。在检查这些表时，我们发现两个没有索引的连接列：customer.custid 和 order.custid。这导致 Informix server 执行连续扫描，本例中，连续扫描要比索引键扫描昂贵得多，因为 customer 和 order 表非常大；每个都包含几百万条记录。因此，我们在这些连接列上添加了索引，从下列查询执行计划中我们可以看到，查询成本减少了很多。
QUERY:------select companyname, address, city, state, country, phone, fax, email, count(*)from cust a, order bwhere a.custid = b.custidand a.custid = 'WILMK'group by 1,2,3,4,5,6,7,8Estimated Cost: 15Estimated # of Rows Returned: 1Temporary Files Required For: Group By 1) omcadmin.a: INDEX PATH (1) Index Keys: custid (Serial, fragments: ALL) Lower Index Filter: informix.a.custid = 'WILMK' 2) omcadmin.b: INDEX PATH (1) Index Keys: custid (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.a.custid = informix.b.custidNESTED LOOP JOIN
该查询的响应时间也从 10 分钟减至了 30 秒钟。这是一个极佳的性能提高！
结束语
SQL 查询的调优并不简单；它包含计划和设计都很好的测试策略、细致的观察和深入的分析。此外，各种平台上的测试结果可能不同，因此，您需要在测试环境中反映您的生产环境。但是“一分耕耘，一分收获”，当看到性能获得较大的提高时，您会感到十分兴奋。我希望上面的示例和分析证明是对您有所帮助的，并希望本文能充当 SQL 查询调优的简介。