DB2 存储琐事

http://www.ibm.com/developerworks/cn/data/library/techarticles/0207adamache/0621_adamache.html

developerWorks 中国  >  Information Management  > DB2 存储琐事

文档选项 打印本页 将此页作为电子邮件发送 级别： 初级 Blair AdamacheIBM 多伦多实验室 2002 年 2 月 01 日 关系数据库只代表数据的逻辑视图，所以我们一定要关心数据的物理存储。要把最少访问的数据放在最慢的设备上。 DB2 存储的宏观管理 关系数据库仅表示数据的逻辑视图 — 那么我们为什么还要关心数据的物理布局呢？有几个值得关心的理由。首先，并非存储系统中的所有磁盘都具有相同的存取速度。假定我们可以分配给 DB2 管理的所有硬件可能不相同，那么对其中数据的存取速度就不可能相同。如果是这种情况，那么，将最少访问的数据存放到最慢的设备上，也许是值得一试的。 其次，您可能知道关于数据的某些事情，而 DB2 根本不会知道： 将来会发生什么，譬如某个给定月份的预计销售， 当本地电话区号进行分割之后将更改的电话号码（从而改变了潜在主键）， 或者您计划的兼并一家公司，其拥有的数据现在必须合并到现有的模式中。 如果这些事情都没有发生，您就不得不应验一句老话以证明有必要阅读本文的剩余部分：如果不能估量它，就不可能管理它。 让我们从一些基础知识开始：使用 DB2 可以获得多大的信息？回答这些问题的首要参考资料是 SQL Reference 手册的附录 A，它讨论了一般限制。下面是存储限制的列表，其中一些在 SQL Reference 手册中。这些也是绝对限制。有关特定页大小的强制限制，请参阅附录 A 中最后的一张表 —“Database Manager Page Size Specific Limits”。请注意有些限制是“每分区”或“每节点”的。在 DB2 个人版、工作组版和企业版中，这些都是绝对限制，但对于企业扩展版，它们可以乘以 999。（它允许 999 个节点，节点也称为分区。） SMS 表空间中表的最大数目 65 534 DMS 表空间中表的最大数目 51 000 数据库中表空间的最大个数 4096 表中的最大列数 500-1012（随页大小改变） 视图中的最大列数 5 000 包含所有开销的最大行长度 4005-32 677 字节（随页大小改变） Varchar 的最大长度 32 672 字节 长字段的最大长度（CLOB，BLOB） 2 GB 每分区中表的最大大小 64-512 GB（随页大小改变） 每分区中索引的最大大小 64-512 GB（随页大小改变） 每分区中表的最大行数 4,000,000,000 包含所有开销的最长索引键 1024 字节 索引键中的最大列数 16 表中的最大索引数 32 767 或存储器 SQL 语句／视图中引用的最大表数目 存储器 SQL 语句中主机变量引用的最大个数 32 767 最长的 SQL 语句（以字节计） 65 535 选择列表中的最大元素个数 1012 表中的最大约束数 存储器 INSERT 语句中的最大值数 1012 UNIQUE 约束中的最大列数  （由 UNIQUE 索引支持） 16 UNIQUE 约束中列的最大合并长度    （由 UNIQUE 索引支持） 1012  引用的最大合并长度 1024 分区键中  的最大列数 500 服务器的最大并行用户数 64 000 级联触发器的最大运行时深度 16 常规 DMS 表空间的最大大小 512 GB 长 DMS 表空间的最大大小 2 TB 临时 DMS 表空间的最大大小 2 TB 每实例的最大并行用户数 64 000 最大分区数 999 DMS 表空间中的最大表对象数 51 000 最大数据库（不包含长字段） 4096*65,535*0.5 TB 最大 EEE 数据库（不包含长字段） 4096*65,535*0.5 TB * 999 个节点 您做的三个基本选择决定了上述限制： EEE 数据库还是未分区数据库（如工作组版或企业版） 选择 4K、8K, 16K 还是 32K 的页大小 SMS 表空间还是 DMS 表空间（系统管理的还是数据库管理的）   EEE 数据库还是未分区数据库 比起其它 DB2 版本，选择企业扩展版会使大多数物理限制增加三个数量级，因此这是您攻破“声障”（或其它对于用户等价的限制）的较简单武器。另有两篇文章讨论了选择 EEE 的含义和优点： http://www7b.boulder.ibm.com/dmdd/library/techarticle/adamache/0430_adamache2.html http://www7b.boulder.ibm.com/dmdd/library/techarticle/adamache/0529_adamache.html 选择 4K、8K, 16K 还是 32K 的页大小 4K 页是缺省值。对于您想要的每种另外的页大小，必须创建具有相匹配的页大小的缓冲池、表空间和临时表空间。较大的页大小的主要好处是表可以包含： 更多数据（4K 页大小可包含 64 GB 数据，页大小 8K、16K 和 32K 成倍递增直到 512 GB） 更多列（4K 页大小可包含 500 列，所有更大的页大小可包含 1012 列） 更宽行（这 4 种页大小的最大行宽度分别为 4005、8101、16 293 或 32 677字节） 更长的 Varchar（大致和行宽度相同） 回页首 SMS 表空间还是 DMS 表空间（系统管理的还是数据库管理的）：生的与熟的 SMS 表示系统管理的存储（system managed storage）：由操作系统通过使用文件系统来管理所有 DB2 对象。对于迅速增长和收缩的表空间（如临时表空间），或者对于您无法执行大量维护和监控的数据库中的所有数据，这是一种很理想的方式。DMS（数据库管理的存储(Database Managed Storage)）要求您预测数据的增长和／或准备好将容器添加到正在运行且磁盘空间不足的表空间中。容器可以是文件系统中的文件或原始设备（raw device）。在原始设备（还有原始日志）上的 DMS 表空间被称为“生的”。使用文件系统的 SMS 表空间和 DMS 表空间被称为“熟的”（可能是为了向您选择的 Fine Young Cannibals 或 Claude Levi-Strauss 表示敬意）。 让我们首先来确定 SMS 还是 DMS：   SMS DMS 划分带区 是 是 （两者都能分割数据用于并行 I/O）   对象管理 操作系统（具有唯一的文件名称） DB2 空间分配 按要求增长／收缩 预分配 管理的容易程度 好 DBA 密集型 性能 好 优 正如上面的第二篇参考文章（2）所提到的，系统管理的存储（SMS）表空间适用于系统数据（临时表空间和目录表空间）和只能进行少量维护的数据库 — 因为它们数量太多而无法保证在设计和维护上对其进行大量投资。对于临时表空间需要的快速磁盘空间分配和取消分配而言，SMS 是正确的选择。 DMS 使您能够在最多三个独立表空间中拥有一个表的数据、多个长字段（如 LOB）和索引。这使您可以选择将长字段放到较慢的磁盘上而将索引放到较快的磁盘上。您还获得了颗粒度更小的备份和恢复选项。对于长字段（LOB 或 LONG VARCHAR），使用具有文件系统容器的 SMS 或 DMS。注：应该避免使用 LONG VARCHAR：VARCHAR 可以和长字段一样，而 LONG VARCHAR 就函数和标准支持方面而言，是 LOB 的绝对子集。LONG VARCHAR 仅仅是 DB2 版本 1 遗留的一个数据类型而已，DB2 因此获得了向后兼容的好名声。 将长字段放置到文件系统（无论 SMS 还是 DMS）上的容器中，使它们可以利用操作系统文件系统的高速缓存。DB2 将不使用它自己的内存（在缓冲池中）来高速缓存长字段。对于目录表空间：使用具有文件系统容器和小数据块大小（2 或 4 页）的 SMS 或 DMS。在目录表空间中有许多相对较小的表，而 DMS 对于每个表需要两倍的数据块（比 SMS 多一倍）。此外，目录表中还有几个 LOB 列，它们都以与上面描述的用户 LOB 相同的方式利用文件系统高速缓存。 对于常规用户数据，是选择 SMS 还是 DMS 取决于一些因素。应避免在文件系统上使用 DMS，因为它集中了 SMS 和 DMS 的缺点（与原始设备相比，文件系统的速度有可能较慢，而同样需要额外管理，以预测和估量当表增长时所需要增加的额外容器）。为了使便利程度最大化和实现很好的性能，选择 SMS，特别是如果您正在使用带有高级文件系统的先进操作系统（如 AIX 和 Solaris 的最新发行版）。这些操作系统擅长将重要文件高速缓存到内存中。为了使性能最佳，选择具有原始容器的 DMS。原始容器通常胜过文件容器，因为它们避免了要通过操作系统及其文件系统的额外路径让 DB2 读取数据。 决定如何跨表空间分配表取决于一些因素。DB2 OS/390 版的 DBA 偏爱将每个表放到它自己的表空间中。对于 Unix 和 Windows 上的 DB2 而言，这只有在某些时候是正确的。如果您经常希望使用前滚时间点并且避免检查挂起状态（在 RI 关系中，如果各表处于不同的时间点，DB2 就会以这种方式冻结表，并可能违背引用约束），那么通过引用完整性关联的表应该在同一个表空间中。另一方面，作为 LOAD 目标的表应该在它们自己的表空间中，因为在 DB2 版本 7.2 或更早版本中 LOAD 需要对表空间的独占访问（这个约束将在未来被取消）。如果 LOAD 到需要 RI 的表中，会怎么样呢？这些表必须在同一表空间中。因为它们通过 RI 链接起来，所以当其中一个在 LOAD 期间脱机时，无论如何您大概都不会希望有针对其它表的事务。如果您希望允许对 RI 关系中并没有被 LOAD 的表进行 SELECT，则将它们放到不同的表空间中。 回页首 DB2 存储的微观管理 上面的 DB2 存储的宏观管理一节讨论了对表的放置位置的控制。现在，让我们研究如何对数据进行微观管理，以及决定将行和列安置在哪里。控制每个字节在磁盘上占用哪个扇区的危险在于您所作的选择可能会比 DB2 的差。如果您能知道 DB2 根本无法知道的事情，您或许能够优化性能或磁盘空间的使用情况。例如，DB2 没有范围分区。相反，在集群机器环境中（或在大型 SMP 上使用多个逻辑分区），将行散列到所有分区以确保数据的平均分布。对于即席查询（ad hoc query）而言，这很理想。对于滚入和滚出仓库的数据而言，范围分区是有优势的。尤其是，如果您只需要保持最近 n 个十年、年、月、星期、日、小时、分钟或秒的数据联机，那么能够在您删除了 2000 年 7 月份数据之后添加 2001 年 7 月份数据的功能就很强大。这是节省磁盘空间的好方法。然而，如果所有 7 月份数据是集群的，则控制它的硬件可能会超负荷（考虑一下当银行客户在 8 月的第一个星期中收到他们的 7 月份月结单信件时，将要请求 7 月份数据的查询数）。7 月数据节点的超负荷摧毁了购买多台机器以建立数据仓库的意图：如果四个处理器都因为处理对 7 月数据的请求而饱和了（而 44 个处理器对其它十一个月的数据只进行很少的处理），则您购买的硬件比您正在使用的要多。有多少用户会在 8 月打电话查询 1 月份月结单呢？ 范围分区还可能成为 DBA 的额外工作。如果您比较 http://www.tpc.org/tpch/results/h-ttperf.idc 上 FDR（完全公开报告(Full Disclosure Reports)）中的基准测试工具箱，会发现在数据库管理系统中创建带范围分区的模式，所用的 DDL 行数是用 DB2 散列分区创建模式所需行数的四倍。在 TPC-H 的前身 TPC-D 中，差异甚至更惊人：10:1。TPC-D 允许实现视图、汇总表和多列索引（以及其它许多功能），因此其它因素也成为造成这一差异的原因。 使用 DB2，如果您希望所有 7 月份数据都在一个分区上，那么需要为每个月创建一个表，并使用 UNION 将所有数据合并到一起。这在 EEE 上并不是最佳方案（EEE 不共享任何东西，当数据随机分布时它工作得更好），而 UNION 也不象其它合并答案集的方法那样高效。更好的选择是让 DB2 使用散列分区来将数据分散到所有节点，并利用数据的到达来在保留散列分区好处的同时获得范围分区的好处。毕竟，7 月份数据往往在 7 月到达。ALTER TABLE 语句将使 7 月份数据在每个节点上保持连续，什么时候发出这条语句更好呢？ 回页首 在此范围内 在我们揭示这个魔法之前，让我们都来回忆一下，关系表中的行是没有内在顺序的。用户和应用程序程序员都必须理解：如果要按他们希望的顺序获得数据，就必需使用 ORDER BY。按月（或者您选择的其它任意范围）的集群数据是一种合理的情况（如果 2001 年 7 月份的行数超过了 2000 年 7 月份的行数，而您又没有预留足够的额外空间，它就不会被重复）。这还使我们回忆起关系表中列也没有内在顺序。我们将等到本文结束时再推翻这一法则。 我们通过选择一个范围开始。既然已经使用了月，就让我们从它开始。在我们的仓库中，保持了 13 个月的数据。当 2001 年 7 月份数据到来时，它将取代 2000 年 6 月份数据。2001 年 7 月份数据应该更大：除了 7 月比 6 月多一天以外，我们通常希望业务随经济一起增长（或更快，如果我们有股票期权的话）。假设增长率为 15%，而且在我们为 2002 年和以后几年的复合增长担心之前，可能会对表进行重新组织。我们创建表时在每页上预留 15% 的可用空间，以应付未来的增长。通常，这样做是为了给插入预留空间，但在本例中这是为了在我们将数据滚入和滚出时用 2001 年 7 月的行代替 2000 年 6 月的行。下面的示例将使用单节点表来演示。在创建表之后，在我们关心的范围内使用索引来预留空间。我们在 PCTFREE 中指定保存 15% 的空间： CREATE TABLE RECEIPTS (RECEIPT_DATE DATE NOT NULL, CUST_NUM INT NOT NULL, RECEIPT_KEY TIMESTAMP NOT NULL, AMOUNT DEC(10,2), PRIMARY KEY(CUST_NUM, RECEIPT_KEY)) PARTITIONING KEY (CUST_NUM, RECEIPT_KEY) CREATE INDEX DATE_IND ON RECEIPTS (RECEIPT_DATE) PCTFREE 15 现在逐月插入数据： INSERT INTO RECEIPTS VALUES ('2000-06-06',1,CURRENT TIMESTAMP,1) INSERT INTO RECEIPTS VALUES ('2001-06-16',40,CURRENT TIMESTAMP,33)   当 2001 年 7 月份数据到来时，删除 2000 年 6 月份数据： DELETE FROM RECEIPTS WHERE MONTH(RECEIPT_DATE)=6 AND YEAR(RECEIPT_DATE)=2000   现在可以开始插入 2001 年 7 月份数据： INSERT INTO RECEIPTS VALUES ('2001-07-01',1,CURRENT TIMESTAMP,1)   如果 2001 年 7 月所拥有的数据比它覆盖的月（2000 年 6 月）的数据更多，我们就必须更改 PCTFREE 值。注：虽然初始 PCTFREE 是在 CREATE INDEX 中指定的，但是 DB2 并没有 ALTER INDEX 语句。您在 CREATE INDEX 中创建了初始 PCTFREE，但此后要用 ALTER TABLE 语句来调整它： ALTER TABLE RECEIPTS PCTFREE 10 或 ALTER TABLE RECEIPTS PCTFREE 0   上述示例都很简单。我们没有研究节点组（nodegroup）的定义（它将指定将 RECEIPTS 散布在哪些分区），也没有研究如果成批地装入每月的数据，将如何处理装入问题。我们还避免了集群索引，而存储了冗余数据（时间戳记是 RECEIPT_DATE 列的超集）。我们为什么要经历这些痛苦呢？因为，在任何一个月，特定月的所有数据在每个节点中的磁盘上都是连续的，但那个月的所有数据都使用分区键（CUST_NUM，RECEIPT_KEY）上的散列键平均地分散到所有节点。注：我们查询的那个月的列（RECEIPT_DATE）并不是分区键的一部分。当我们在查询中寻找 2001 年 7 月的行时，每个节点提供相等的行数（因此所有硬件都参与了工作，并且从数据的分布中得到了最大利益），但是在每个节点内部，2001 年 7 月的行在磁盘上是连续的：它们将一起呆在缓冲池中，也可能在整个 2001 年 8 月都呆在内存里，以便在客户电话询问他们的 7 月份月结单时查询得更快。对 7 月份月结单的打印和查询也会更快，因为 7 月的行在磁盘上是连续的，所以使 I/O 更快。最终，通过腾出 2000 年 6 月的空间（当它们过时的时候，就被删除了）来保存 2001 年 7 月的行，节省了磁盘空间。我要第三次说这句话：2001 年 7 月的数据可能比 2000 年 6 月数据多 15%，因为我们使用索引上的 PCTFREE 预留了可用空间。我们可以通过改变表来设低 PCTFREE 的值，从而将行放置到这个可用空间中。 回页首 欲速则不达 上述示例利用了 DB2 的这种自发性：DB2 通过用 INSERT 扫描整个表将新行置入空槽。在本例中，我们删除了 6 月的数据，而当插入 2001 年 7 月的数据时 DB2 查找空槽。这使得 INSERT 变慢，因此 SELECT 可以更快。相反的情况：当我们希望 INSERT 变快而又不打算对数据使用太多 SELECT 语句时（而且可能不关心磁盘空间），会怎么样呢？继续我们的银行示例：数据仓库保持 13 个月的数据联机，但银行并不真的删除 2000 年 6 月数据。审计师喜欢追溯七年或十年以前的数据，并问您为什么不上缴在大街上捡到的 100 元钱却存入了您的银行帐户。虽然 99% 的请求都是关于过去 13 个月的，但应用程序还是会在一些较旧的慢速磁盘的表空间中为旧数据创建一个表： CREATE TABLE OLD_RECEIPTS LIKE RECEIPTS IN SLOW_DISK_TBSP 在删除 2000 年 6 月的行之前，我们使用一个带子查询的 INSERT 语句将它们移到 OLD_RECEIPTS 表： INSERT INTO OLD_RECEIPTS SELECT * FROM RECEIPTS WHERE MONTH(RECEIPT_DATE)=6 AND YEAR(RECEIPT_DATE)=2000 现在可以删除 2000 年 6 月的行了： DELETE FROM RECEIPTS WHERE MONTH(RECEIPT_DATE)=6 AND YEAR(RECEIPT_DATE)=2000 注：在本例中，我们可以不关心为 SLOW_DISK_TBSP 表空间使用的设备节省磁盘空间。考虑一下因为我们只是将数据移到这个表中，而且只有少于 1% 的查询是针对它的，所以下列事情很可能是真的： 只要银行在运作，表就会增长。 当插入新行时，DB2 不必搜索空槽，因为只是将行插入这个表，而绝对不会删除或更新它。银行多久才会在交易发生 13 个月之后对其记录进行一次更改呢？ 可以通过将该表改变成 APPEND ON 来通知 DB2 在执行 INSERT 时不必搜索空槽： ALTER TABLE OLD_RECEIPTS APPEND ON   这将使 INSERT 更快。万一银行有时要迅速地做些假帐，可以关闭 APPEND，而在 OLD_RECEIPTS 表中会暴露出一堆 20 世纪 90 年代后期执行的 INSERT、UPDATE 和 DELETE 语句。 第五列 我们已经学习了一些如何对磁盘上的行进行排序的有用方法，但请记住，如果用户在乎顺序，他们必须使用 ORDER BY 语句进行指定。不使用 ORDER BY，DB2 不会保证以某种顺序传递行。维护或新发行版导致了存取计划的更改，或者单独处理器向协调程序节点传送行的速度的改变，使得任何结果集的顺序都是任意的，除非您指定了 ORDER BY 语句。列的情况基本相同，但略有差异。在 SELECT 语句中指定您想要的列顺序。SELECT * 按创建表时指定的列顺序产生列。列的顺序很重要吗？也许吧。请注意后面的信息仅适用于 Windows、Unix 和 OS/2 上的 DB2。其它平台上的列处理有所不同。此外，下面讨论的内容在任何将来的 DB2 发行版中有可能更改（尽管如果它真的更改了，则需要重要工程技术将所有在 DB2 版本 7.2 或更早版本中创建的表移为新格式）。 当您创建表时，要拥有更快的 SELECT 操作，对列进行排序没有最佳方法。然而，在创建完表时，如果知道哪些是最频繁更新的列，首先将它们列出是有好处的。当用 UPDATE 语句更新了行之后，DB2 必须将 UPDATE 的结果写到日志并立即（或最终）写到存储这行的磁盘中（通常是在刷新缓冲池时）。不必将整行写入日志；只需要将更改的列写入即可。写到日志中的是这一行的第一列，以及此后的每一行，直到 DB2 达到最后一个更改的列为止。如果客户的社会保险号是不变的，将它放到行的末尾附近。经常更改的资料（如帐户余额）应该在或者接近行的开头。偶尔更改的资料（如地址）应该放在帐户余额和社会保险号之间。 让我们看看列在磁盘上是如何布局的。如果您创建了一个只有定长列的表，将严格按照 CREATE 语句中指定的顺序安排它们： CREATE TABLE TESTORD (COL1 INT, COL2 CHAR(5), COL3 DEC(10,2), COL4 FLOAT) 图 1.   如果表拥有变长列（如 VARCHAR），列仍然按照 CREATE TABLE 语句中指定的顺序排序，但可变数据本身在行的末尾： CREATE TABLE TESTORD (COL1 INT, COL2 VARCHAR(5), COL3 DEC(10,2))   图 2.   如果表有长字段，它将不随每行直接插入。因为行宽受页大小限制（4K 到 32K），所以行只有一个指向长字段的指针，而将长字段与行分开放置在数据库页中： CREATE TABLE TESTORD (COL1 INT, COL2 CLOB(100 K), COL3 DEC(10,2))   图 3.   回页首 一站式购物 索引创建了一些数据冗余，但向用户隐瞒了这一点（当用户更新表时，不必为更新索引操心，那是 DB2 的任务）。DB2 比其它许多数据库管理系统更多地依赖于索引，这主要是因为它拥有完善的优化器。数据库世界中将仅索引访问称为“一站式购物”：如果 SELECT 需要的所有数据都在索引中，则 DB2 只须读取索引即可。考虑下列表及 NAME 上的索引，NAME 是主键： CREATE TABLE ADDRESS (NAME CHAR(20) not null, STREET CHAR(20), CITYPROV CHAR(20), POSTCODE CHAR(7), HOMEPHONE CHAR(12), WORKPHONE CHAR(12), NOTES CHAR(20)) CREATE UNIQUE INDEX NAME_INDX ON ADDRESS (NAME)   如果发出： SELECT NAME FROM ADDRESS 或 SELECT NAME FROM ADDRESS WHERE NAME = 'Adamache, Blair'   DB2 将仅通过扫描索引来收集查询结果。如果发出： SELECT NAME, WORKPHONE FROM ADDRESS DB2 将扫描表：索引没有提供任何好处。您可以通过从“命令中心”发出上述所有命令，并使用“Create Access Plan”图标查看 DB2 优化器对数据采取的路径来测试这些存取路径。上述的所有三个 SELECT 都进行一站式购物：单独的表或索引都能满足查询。当优化器希望使用索引，但又需要读取表以获得其它列时会怎么样呢？这个 SELECT 语句将同时需要扫描索引和读取表： SELECT NAME, WORKPHONE FROM ADDRESS WHERE NAME = 'Adamache, Blair'   如果这是一种公共访问方法（人们希望查看某人的电话号码，并通过对姓名的搜索开始），您可以告诉 DB2 在索引中包含额外的列。WORKPHONE 将不会成为主键的一部分：它只是包含在索引中，这样就可以使用仅索引访问来满足更多的查询。先从除去单列索引开始： DROP INDEX NAME_INDX   现在，重新创建索引，并包含用户通常希望查看的额外列： CREATE UNIQUE INDEX NAME_INDX ON ADDRESS (NAME) INCLUDE (WORKPHONE, HOMEPHONE)   请牢记，尾随的额外列越多，UPDATE 和 INSERT 花费的时间就越长：包含的每个列现在都必须在表和索引中进行更改。使用了包含索引，下列查询现在可以使用仅索引访问来运行了： SELECT NAME, WORKPHONE FROM ADDRESS WHERE NAME = 'Adamache, Blair'