![]() |
级别: 初级
Jianing Fan, 高级软件工程师, Motorola
如果您是一名 Informix® DBA，那么您是否想要知道如何更高效地创建表和索引？为了获得更好的性能，在创建表和索引之前，您需要思考数据库的物理结构。作者将详细介绍所有这些考虑事项，并用实际例子进行说明。
任何稍微了解 SQL（结构化查询语言）的人都知道如何在数据库中创建表和索引。但是就系统和数据库性能而言，如何才能更高效地创建表和索引呢？以下是在创建表和索引之前需要考虑的事项：
- 数据库空间（Dbspace）
- 区段大小（Extent size）
- 锁模式
- 约束键字
本文将仔细研究所有这些考虑事项，并通过工作环境中收集的实际例子进行说明。
数据库空间（Dbspace）是逻辑数据库对象和物理磁盘位置之间的链接。在创建诸如表和索引等逻辑数据库对象时，需要物理磁盘空间来存储这些对象，这些空间就称为数据库空间（dbspace）。Dbspace 由一个或多个磁盘空间块组成。对于 9.40 之前的 Informix 版本，块的大小是有限制的；每块不能超过 2 GB。现在已不再有块大小限制。当首次初始化数据库服务器时，有两个选择：可以配置数据库服务器，以便使用较小的块（每块小于或等于 2 GB），也可以使用较大的块（每块大于 2 GB）。
Dbspace 是由 Informix onspaces 实用程序创建并管理的。您既可以在格式化后文件系统上创建 Dbspace，也可以在原始磁盘设备上创建 Dbspace。格式化后文件系统指的是已格式化并安装的磁盘设备，而原始磁盘设备指的是那些还未安装的磁盘设备。在 UNIX® 系统中，为了获得更好的性能和更简单的数据库管理，Informix 推荐对 dbspace 使用原始磁盘设备，因为原始磁盘设备可以避免操作系统缓冲并创建到物理磁盘设备的符号链接。只有具有数据库管理员（DBA）权限的用户才可以使用这个 onspaces 实用程序。同时，在创建新的 dbspace 之后，不要忘记在 Informix 实例上进行一次完全备份。关于如何创建和管理 dbspace 的详细信息，请参阅 。
当 Informix 数据库服务器首次进行初始化时，它将自动创建一个 dbspace，以存储所有数据库对象。这个 dbspace 通常称为 rootdbs。下列列表展示了主要的 Informix 数据库对象：
- 逻辑日志数据。由逻辑日志生成的数据。
- 物理日志数据。由物理日志生成的数据。
- 表数据。数据库表中的数据。
- 索引数据。由表上的索引生成的数据。
- 大二进制对象数据，如大型文本和内部图形。
- 临时数据。对表和构建索引生成的数据进行排序，并对它们进行更改。
为了取得更好的数据库性能，Informix 文档建议为每个数据库对象创建一个不同的 dbspace。例如，应该为逻辑日志创建一个 dbspace，为表创建一个 dbspace，为索引创建一个 dbspace，等等。Informix 文档还建议跨磁盘展开 dbspace，以获得平衡后的磁盘 I/O。一种获得这种效果的方法就是应用现代的 RAID 技术来划分条带（stripe），并合并磁盘分片。另一方法则是在不同的磁盘上配置块（chunk）。要获得详细信息，请参阅 。
在正确创建这些 dbspace 的同时，还需要知道哪个 dbspace 是用于表的，哪个 dbspace 是用于索引的，这样，您就可以遵照建议，不去将不同的数据库对象放置在同一 dbspace 中。您可以在 CREATE TABLE 和 CREATE INDEX 中使用 IN 子句，将标和索引指派到合适的 dbspace 中。下面是一个例子：
CREATE TABLE element
(
workspace_id integer,
element_id integer,
cpbsc_instance integer,
acgid_bsc integer,
dap_cluster_id integer,
acgid_dap integer,
acg_class smallint,
preconfig_flag smallint,
cptimr_inst integer,
acgglob_inst integer,
tdapfr_primary integer,
tdapfr_secondary integer,
barred_state_tmr integer
) IN data1;
CREATE INDEX elmentidx1 on element (workspace_id,cpbsc_instance)
IN idx1 ;
|
以上例子将 element 表及其索引分别指派给 data1 和 idx1 表空间（dbspace）。
如果您发现某个 dbspace 具有比其他 dbspace 多得多的磁盘 I/O，并找到了磁盘 I/O 的源表，换言之，用户在十分频繁地访问这些表，那么 IN 子句还可以用于将表和索引从一个 dbspace 移至另一个 dbspace。操作过程如下所示：
- 保存您计划使用 Informix dbschema 实用程序进行移动的表和索引的模式。
- 从您计划移动的表中卸载数据。
- 删除该表。
- 用您所保存的同一模式重新创建该表。
- 重新加载表数据。
- 用您所保存的同一模式重新创建索引。
- 对该表执行 update statistics high，以激活索引。
在删除表之前，需要研究它与其他表的关系，并特别注意其外键约束。还需要极其小心地处理其外键约束；如果没有正确处理，就会打乱整个数据库的完整性。
区段（extent）是磁盘上一块物理位置连续的页面，用以存储数据库对象。在创建表时，默认的区段大小是 16 KB。这对于大多数据库表通常都太小。在填满了一个区段时，Informix 服务器将自动分配更多区段，直到它具有足够的区段来存储整个表中的数据。为了获得高性能，Informix 文档建议将一个表中的所有数据置于一、两个较大的区段中，而不是将它们置于许多较小的区段中。给出这样的建议有两个原因：
- 如果一个表有一个以上区段，则无法保证这些区段是连续的；区段可能分散在这个表驻留的整个数据库空间（dbspace）中。物理磁盘页面的连续性对于性能是至关重要的。当数据页面连续时，用于访问磁盘上数据的时间是最少的，数据库也可以连续读取行。如果表中有太多的区段，那么这些区段极有可能是交错的。这将大大损害性能，因为当您为某个表检索数据时，磁盘头需要查找同属于该表的大量非连续的区段，而非不是查找一个包含连续物理页面的大型区段。这极大地降低了磁盘寻址（disk-seeking）速度。
- 另一个原因是为了避免自动分配表的区段，这是一个代价极高的操作，将使用大量系统资源，例如 CPU 和内存。
为了实现 Informix 文档的建议，在创建表时，需要指定区段大小，不让 Informix 服务器使用 16 KB 的默认区段大小。CREATE TABLE 语句中的 EXTENT SIZE 和 NEXT SIZE 子句允许您指定 Informix 服务器将分配给所创建的每一个表的第一个和第二个区段大小。
但是，如何估算区段大小呢？这是一项很困难的任务，特别是对于 OLTP 数据库，要不断在这个数据库中更新和插入表。实质上，您需要知道该表将包含的行数、行的大小和系统的页大小。然后，必须对那些数字进行一些数学运算。Informix 文档为我们提供了关于完成这项工作的详细指南：
- 确定每个索引的长度。
- 确定索引的总长度。
- 计算索引开销。
- 确定表的初始大小。
- 索引空间需要的大小。
- 将索引空间转换成千字节（kilobyte）。
- 确定以字节为单位的页大小；用它减去 28 就可以获得可用的页面空间。
- 确定行的长度。
- 确定一页上可以完整放置多少行。
- 确定初始表的数据页的数目。
- 初始表中数据页面所需的空间大小。
- 将数据大小转换成千字节（kilobyte）。
- 确定以千字节（kilobyte）为单位的初始区段大小。
- 确定表的增长。
- 下个区段的大小。
现在，让我们来浏览一个例子。根据这个指南为 item 表一步步估算第一个和第二个区段大小。以下是 item 表的结构：
列 数据类型 长度（以字节为单位）*
Item_num smallint 2
Order_num integer 4
Stock_num smallint 2
Manu_code char(3) 3
Quantity smallint 2
Total_price money(8) 5
* 您可以在 中获得每种数据类型的长度。
|
现在，让我们假设该表在 order_num 上有一个索引，而且还有 stock_num 与 manu_code 上的复合索引。该表最初有 20,000 行，几个月内，它会增加 35,000 行。
以下是每项计算的结果：
步骤 描述 计算（以字节为单位）
1 确定每个索引长度 order_num 上的索引 = 4+9 = 13
stock_num 和 manu_code 上的索引 = 2+3+9 = 14
2 确定索引的总长度 13 + 14 = 27
3 计算索引开销 27 * 1.25 = 33.75
4 确定表的初始大小 20,000 行
5 所需的总的索引空间 20,000 * 33.75 = 675,000
6 将索引空间转换成千字节（kilobyte） 67,5000/1,024 = 660 (Kbytes)
7 确定以字节为单位的页大小
减去 28 以获得页面开销 2,048 - 28 = 2,020
8 确定行的长度 2 + 4 + 2 + 3 + 2 + 5 + 4 = 22
9 确定每页有多少整行 2020/22 = 91
10 确定初始表的数据页面数目 20,000/91 = 220
11 数据所需的总空间 220 * 2,048 = 450,560
12 将数据大小转换成千字节 450,560/1,024 = 440
13 确定初始区段大小 440 + 660 = 1,100
14 确定表的增长 增加 35,000 行
15 估算下个区段的大小 所需的索引空间 = 35,000 * 33.75 = 1,181,250
转换成 Kbytes = 1,181,250/1024 = 1,154
所需的附加数据页 = 35,000/91 = 385
转换成 bytes = 385 * 2,048 = 788,480
转换成 Kbytes = 788,480/1,024 = 770
所需的总空间 = 770 + 1,154 = 1,924
|
基于这些计算，就可以确定该表所需的第一个和下一个区段的大小，现在，您可以在 CREATE TABLE 语句中指定第一个和下一个区段的大小：
CREATE TABLE item(
Item_num smallint,
Order_num integer ,
Stock_num smallint,
Manu_code char(3) ,
Quantity smallint,
Total_price money(8))
EXTENT SIZE 1100 NEXT SIZE 1924;
|
在创建该表时，Informix 服务器将自动为该表分配前两个区段，并且如果您的估算正确，那么这两个区段即使不能包含该表的所有数据，也能包含其中的大部分数据。以上计算是极其机械的，并且易于在 Microsoft® Excel 电子数据表（spreadsheet）中实现，从而可以自动化整个过程。从上述计算中还可以发现，估算的基础就是表将包含的行数。这种估算当然是基于业务规则的，但很大程度上也取决于数据库中表之间的关系。例如，假设有两个表，customer 和 address，并知道有 100,000 位客户，每位客户最多可能有 4 个地址。因此，您可以很容易地计算出 address 表最多可能有 100,000 *4 行。
然后，该如何验证估算值，并查看该值与实际值是否接近呢？在创建表并装入数据之后，您可以使用 Informix oncheck 实用程序来获得该表的表空间（tblspace）报告。以下就是这条命令：
示例输出如下：
TBLspace Report for prod1:item
Physical Address 9:652153
Creation date 08/31/2004 11:41:05
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 220
Number of special columns 0
Number of keys 0
Number of extents 1
Current serial value 1
First extent size 1100
Next extent size 1924
Number of pages allocated 4107
Number of pages used 886
Number of data pages 885
Number of rows 7960
Partition partnum 7340289
Partition lockid 7340289
Extents
Logical Page Physical Page Size
0 9:299243 4107
|
以上输出告诉您，item 表仅有一个区段。如果表空间（tblspace）报告显示表中包含太多的区段，那么可以使用该统计数据来优化估算算法，然后使之更符合实际情况。
实际上，表空间（tblspace）报告包括两个部分，第一部分是关于表的区段信息，第二部分包含关于索引的区段信息。无法像对表那样直接为索引指定区段大小；在创建索引时，Informix 服务器将基于所指定的表区段大小，自动为索引分配区段。然而，您可以使用 oncheck 实用程序来查看索引获得了多少个区段。以下是示例输出：
Index item_idx1 fragment in DBspace prod1_idx
Physical Address 9:907200
Creation date 08/31/2004 12:48:45
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 220
Number of special columns 0
Number of keys 1
Number of extents 1
Current serial value 1
First extent size 224
Next extent size 32
Number of pages allocated 224
Number of pages used 36
Number of data pages 0
Number of rows 0
Partition partnum 7340624
Partition lockid 7340289
Extents
Logical Page Physical Page Size
0 9:831443 224
|
以上输出显示索引 item_idx1 仅有一个区段。关于如何使用 oncheck 的详细信息，请参阅 。
锁模式在并发控制中至关重要；IDS 使用这一机制来控制 Informix 会话对数据库表的同时访问。有三种锁模式，它们分别是页锁、表锁和行锁。页锁锁定整个物理磁盘页面（大小为 2 KB）以及该页面上所包含的所有行或记录，表锁锁定整个数据库表及其所有行，而行锁一次仅仅锁定一行或一条记录。显然，表锁允许最少的并行操作，如果以排他模式来锁定表，那么当您使用这个表时，其他人实际上就无法访问该表，甚至无法读取或查询该表。
您应该使用哪种锁模式呢？该问题的答案很大程度上取决于您的业务环境。例如，让我们假设您的业务是在银行中，因此您需要避免使用表锁；而且需要为系统用户提供更多并发性，以便更多的用户可以更新其账户，同时平衡信息。
选择锁模式时需要做一些权衡（tradeoff）。表锁允许最少的并发操作，但这样做节省了系统和数据库资源。另一方面，行锁允许最多的并发操作。所以用户可以同时更新同一表中的不同行，但这要使用多得多的锁。假设某一表包含两百万行，如果该表是处于行锁中，那么 Informix 对该表使用的锁就可能达到两百万个。Informix 可以使用的最大锁数目由 Informix 配置文件中的 LOCKS 参数指定，理论上，这个数目的上限是九百万个。当初始化 Informix 时，要预先分配锁，并且如果 Informix 达到其限制，那么 Informix 将自动增加 100,000 个锁，并且最多进行 14 次这样的操作。换言之，它将自动再分配 100,000 * 14 = 1.4 百万个锁，如果分配的锁超过了这个限制，那么 Informix 就会在 Informix 消息日志中提示错误消息，并中断该事务。在一些严重情况下，Informix 将崩溃。
如何指定锁模式呢？可以使用 CREATE TABLE 语句。下面是一个例子：
CREATE TABLE item(
Item_num smallint,
Order_num integer ,
Stock_num smallint,
Manu_code char(3) ,
Quantity smallint,
Total_price money(8))
LOCK MODE ROW;
|
在创建表时，页锁是默认的锁模式，因此，您不必指定任何东西。表锁仅仅在事务内部使用；因此，即使您的表是用行锁模式创建的，在事务执行期间也能修改它。然而，如果在创建数据库时没有记录日志，则无法使用表锁模式。关于如何选择合适的锁模式的详细信息，请参阅 。
许多人喜欢在 CREATE TABLE 语句中使用约束键字来创建主键、外键、惟一性和其他约束。他们认为这是一种创建约束的简单且方便的方法。下面是一个例子：
CREATE TABLE sub_accounts (
sub_acc INTEGER PRIMARY KEY,
ref_num INTEGER NOT NULL,
ref_type INTEGER NOT NULL,
sub_descr CHAR(20),
FOREIGN KEY (ref_num, ref_type) REFERENCES accounts
(acc_num, acc_type));
|
遗憾的是，对于数据库的性能和管理，这样做并不好。当 Informix 读取以上 CREATE TABLE 语句时，它将自动创建两个索引，一个用于 sub_acc 上的主键，一个用于 ref_num 和 ref_type 上的外键。然后，它将自动为每一个索引指派一个系统编号，并将该编号用作索引名，对于开发人员和设计人员而言，这没有任何意义。更糟的是，这些索引是在数据 dbspace 内部创建的，因此会对数据库总体性能产生负面影响，所以 Informix 文档并不建议这样做。此外，在删除该表的主键或外键约束时，将自动删除这些索引；因此，如果需要在查询中使用这些索引，则需要重新创建它们。如果表中有数百万行，那么这可能是一个十分耗时的过程。
因此，在表中创建或添加约束的正确过程如下：
- 创建不含约束键字的表。
- 在将使用约束的列上创建索引。
- 更改表来添加约束。
下面是一个很好的例子：
CREATE TABLE item(
Item_num smallint,
Order_num integer ,
Stock_num smallint,
Manu_code char(3) ,
Quantity smallint,
Total_price money(8)
)
IN data1;
CREATE UNIQUE INDEX item_idx1 ON item (item_num) IN idx1;
ALTER TABLE item ADD CONSTRIANT PRIMARY KEY (item_idx1);
|
本例中，您可以在不同的数据库空间（dbspace）中放置索引和数据，避免与索引一起删除主键约束。
正如上面所讨论的，在创建表和索引之前，需要稍稍考虑数据库的管理和性能，以便可以更高效地创建表和索引。还需要问自己以下这些问题：
- 应该将新的表和索引置于何处？
- 表将增大多少，而我应如何指定第一个和第二个区段的大小？
- 新表将使用哪种锁模式？
- 应该如何为表创建各种约束？
好的 CREATE TABLE 语句应该详细说明所有这些问题的答案：
CREATE TABLE item(
Item_num smallint,
Order_num integer ,
Stock_num smallint,
Manu_code char(3) ,
Quantity smallint,
Total_price money(8)
) IN data1 EXTENT SIZE 6700 NEXT SIZE 1100 LOCK MODE ROW;
CREATE UNIQUE INDEX item_idx1 ON item (item_num) IN idx1;
ALTER TABLE item ADD CONSTRIANT PRIMARY KEY (item_idx1).
|
![]() |
|
|
![]() |
Jianing Fan 是 Motorola 的一名软件工程师，专门从事关系数据库管理系统。他是 Informix 认证专家和 Oracle 认证专家，作为开发人员、系统管理员和 DBA，他有十多年的数据库和系统经验。您可以通过 与 Jianing 联系。 | |
