ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 程序员:“仅索引” 更新和删除的秘密

程序员:“仅索引” 更新和删除的秘密

原创 Linux操作系统 作者:ArtCode 时间:2009-04-23 17:11:18 0 删除 编辑

神秘的 PLAN_TABLE 信息

在我早期使用 DB2 的时候,我专注于应用程序的性能。那时候查找性能问题的工具比较少,其中之一是 EXPLAIN,我专门学习了 PLAN_TABLE 中列的含义。随着时间的推移,我能够将该数据转换成调优 SQL 的有用信息。

奇怪的是,PLAN_TABLE 给我带来的主要收获不是提高 SQL 性能,而是增强了我对 DB2 优化器设计思想的理解。我花费数小时寻找 DB2 优化器选择一个特定路径而不是另一个路径的原因,以及选择的访问路径在运行时会发生什么情况。换句话说,就是如何寻找和使用索引页和表页。

有时,对于单个查询,PLAN_TABLE 中的某些列看上去互相矛盾。这些 “矛盾” 之一(“仅索引” UPDATE/DELETE 的迷惑)是这篇专栏的主题。其它 “矛盾” 是下一个专栏的主题。

“仅索引” UPDATE 和 DELETE

那么,让我们来解决第二个秘密。当被解释的 SQL 语句是 UPDATE 或 DELETE 语句时,PLAN_TABLE 上的 INDEX_ONLY 标记怎么会是 Y 呢?我第一次看到这种情况时非常困惑。对不起,但是确切的讲,UPDATE(必须更改表数据)或 DELETE(必须从表中删除行)怎么可以 “仅索引” 呢?

典型的 DELETE

让我们看一下 DB2 内部的典型 DELETE 发生什么情况。假设我们的 SQL 语句如下:

DELETE FROM BIG_TABLE B
WHERE TRANDATE = "2008-06-24"
      AND STATUS = "C"

对这条语句运行 EXPLAIN 时,我们看到 DB2 选择了 TRANDATE、CUSTNAME 上的名为 IX4 的两列索引(PLAN_TABLE 列 ACCESSNAME 被设置为 IX4),并且匹配该索引的第一个列(MATCHCOLS = 1)。正如 DELETE 预期的那样,INDEX_ONLY 列为 N。

您应该知道 IX4 允许重复并且不是 CLUSTER 索引,索引中的每个条目都有与其关联的一个或多个 RID。运行时,DB2 将查找索引,找到 TRANDATE 等于 SQL 语句中的日期的索引行。假设 DB2 找到 200 个相邻行。这 200 个条目中,每个都具有一个长度可变的 RID 链,一些只有一个 RID;另一些有一个长度在 2 个 RID 到 300 个 RID 之间的 RID 链。对于我们的 TRANDATE,所有限制的 RID 的总数目是 2200。

对于我们的访问路径和 2200 个 RID 中的每一个 RID,DB2 将随机读取 BIG_TABLE(记住,这个索引的 CLUSTERRATIO 非常低)并且有可能执行 2200 次 GET PAGE 和 2200 次读 I/O。因为我们已经为这个表空间选择 LOCKSIZE PAGE,所以预期 DB2 在每个接触的表页上获得一个 X 锁。但是,DB2 不会获得 X 锁,因为在 STATUS 上应用非索引谓词之前,它并不知道是否实际执行 DELETE。DB2 首先在每个页上获得一个 U 锁(用于 UPDATE OF 的 READ 锁)。然后 DB2 在 STATUS 上应用谓词。如果 STATUS 不是 C,U 锁将在内部指针离开表页时被释放(我们在包中绑定了 ISOLATION CS)。如果 STATUS 是 C,U 锁将被升级到 X 锁并一直保持到 COMMIT。

总之,我们将在两个或三个相邻索引页上读取 200 个相邻索引行,并找到 2200 个限制的 RID。DB2 将为每个 RID 读取表。假设 500 行的 STATUS 不是 C。如果 DB2 没有找到完全限制的行,最坏情况下的开销是:

  1. 500 次 GET PAGE
  2. 500 次读 I/O
  3. 获取 500 个 U 锁,每次一个
  4. 释放 500 个 U 锁,每次一个(当指针离开每个页面而不执行 DELETE 时)。

如果 DB2 找到完全限制行(其它 1700 行),锁开销是:

  1. 1700 次 GET PAGE
  2. 1700 次读 I/O
  3. 获取 1700 个 U 锁
  4. 获取(升级)1700 个 X 锁
  5. 一次 COMMIT 来释放 1700 个 X 锁。

每个获取 U 锁的请求和每个升级 U 锁到 X 锁的请求都需要对 IRLM 地址空间进行跨内存服务调用,此时是被告知 no、wait(锁挂起)或 heck、no(超时/死锁)的好机会。

在我们的例子中,将执行 2200 次 GET PAGE 和 2200 次读 I/O,获取 2200 个 U 锁,释放 500 个 U 锁(离开页时每次释放一个),升级 1700 个 U 锁到 X 锁(每次一个)并在 COMMIT 时释放 1700 个 X 锁。我们将执行 4401 次跨内存服务调用,有 3900 次机会被锁管理器告知 no。

“仅索引” DELETE

让我们看一下,DB2 内部的神秘 “仅索引” DELETE 会发生什么情况。假设 SQL 语句不变,但是索引有所不同。我们的 SQL 语句仍然是:

DELETE FROM BIG_TABLE B
WHERE TRANDATE = "2008-06-24"
      AND STATUS = "C"

这次运行 EXPLAIN 时,会看到 DB2 选择 TRANDATE、CUSTNAME、STATUS 上的三列索引,并且匹配该索引的第一个列(MATCHCOLS = 1)。与 DELETE 预期的不同,INDEX_ONLY 标志为 Y。

同样,选择的索引不是 CLUSTER 索引并且允许重复。运行时,DB2 将查找索引,找到 TRANDATE 等于 SQL 语句中的日期的相邻索引行。这次,有 400 个这种条件为真的相邻行。因为我们已经从每 CUSTNAME 每 TRANDATE 一行变成每 STATUS(colcard = 2)每 CUSTOMER 每 TRANDATE 一行,所以数目翻倍。这 400 个条目中的每一个都具有一个可变长度的 RID 链,一些只有一个 RID,另一些有一个长度在 2 个 RID 到 150 个 RID 之间的 RID 链。

但是,这是两个路径是性能发生变化的交叉点。虽然我们读取的相邻索引行的数目翻倍(400 而不是 200),但是 DB2 现在可以将 STATUS 上的谓词应用于匹配范围内的索引数据。DB2 扫描匹配范围时在 STATUS 上应用谓词被称为 INDEX SCREENING。只有 1700 个限制的 RID(两个谓词都为真)。

对于新访问路径的 1700 个 RID 中的每一个 RID,DB2 将读取表并且可能进行 1700 次 GET PAGE 和 1700 次读 I/O。我们预期 DB2 在每个接触的表页上获得一个 X 锁。这次达到了预期:它确实这样做。为什么?因为 DB2 完全确定它将真正地执行 DELETE。没有任何进一步的谓词应用于表行。使用索引数据,行已经完全限制。

DELETE 不是 “仅索引” 的;行限定是 “仅索引” 的。我们知道,不管是 UPDATE 还是 DELETE,都不可能实现真正的 “仅索引”。因此,DB2 使用 PLAN_TABLE 列为我们提供语句的有用信息。

我们将在四个或五个相邻索引页上读取 400 个相邻的索引行,并找到 1700 个限制的 RID。使用 “仅索引” DELETE,开销是:

  1. 1700 次 GET PAGE
  2. 1700 次读 I/O
  3. 获取 1700 个 X 锁
  4. COMMIT 来释放 1700 个 X 锁。

我们没有任何升级开销;已经将 4401 次跨内存服务调用减少到 1701 次,并且已经将潜在的锁挂起和超时从 4400 次减少到 1700 次。这种新技术的最大收获在于:因为不需要请求另外的 2700 个锁,我们不会被告知 no。


解开谜底

现在我们知道了为什么 PLAN_TABLE 中的 INDEX_ONLY 标志对于 UPDATE 或 DELETE 可以是 Y。并且我们已经看到:向索引添加单个一字节的列能够提升性能。如果这个专栏更长一点的话,我将详细说明为什么添加的列不应该频繁地更新,不应过大而导致过度增加索引页的数目,不应导致索引增加许多层次,以及不应对性能产生影响。

另一个细微差别

DSNZPARM XLKUPDLT(值为 YES、TARGET 和默认值 NO)可能对一些 DB2 子系统有用。请记住,ZPARMS 应用于整个 DB2 子系统,而不仅仅是一个包或一个表空间。将 XLKUPDLT ZPARM 设置为 YES 告知 DB2 立即为为 UPDATE 或 DELETE 读取的每个页获取一个 X 锁,而不管谓词应用是什么。

这种方法消除所有 U 锁到 X 锁的升级开销(以及跨内存服务调用和由此产生的额外超时)。但是,为这个参数使用 YES 意味着您必须非常确信大部分 X 锁都适合的,通过索引谓词完全限定大部分表行,并且应用非索引谓词时不会取消限定。(顺便说一下,如果页上没有任何行限定,那么不友好的 X 锁将立即释放,不会保持到 COMMIT)。

在我们的第一个例子中,使用两行索引,并且 XLKUPDLT 使用 YES,开销为:

  1. 2200 次 GET PAGE
  2. 2200 次读 I/O
  3. 获取 2200 个 X 锁,一次一个
  4. 释放 500 个 X 锁,一次一个(在每个没有任何限定行的页上)
  5. 一次 COMMIT 来释放 1700 个合适的 X 锁。

ZPARM 值为 YES 时,会带来更多不友好(并且不必要)的 X 锁,从而增加超时的可能性。请记住,该协议适用于 SQL 搜索的 UPDATE 或 DELETE 引用的所有表,甚至是在 WHERE 子句的子选择中命名的表。

第三个选项是 NO 和 YES 之间的折衷。如果为 ZPARM 使用值 TARGET,那么只为 UPDATE 或 DELETE 需要的表请求 YES 协议。NO 协议将用于 SQL 语句中引用的其它表。

应该考虑将 ZPARM 的 TARGET 值用于数据共享环境(它通过良好过滤索引访问数据)。在性能争用/并发环境中,通过不良过滤索引使用表空间扫描或表访问的 UPDATE 或 DELETE 可能会产生破坏性后果。

已经解开了两个秘密,还有一个

我们又揭示了 DB2 世界的另一个秘密。在下一期的专栏中,将尝试解开最后一个谜团:添加 ORDER BY 子句如何能够消除排序,而取消 ORDER BY 子句反而会导致排序。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15082138/viewspace-591603/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-08-05

  • 博文量
    269
  • 访问量
    558152