| 无论您是在用原型证明某一概念，还是开发一个全新的应用程序，或者只是学习
SQL，您都需要在您的应用程序上运行测试数据。本文讨论了如何生成足够的、对测试有用的、具有期望的值分布和列间相关性的测试数据。 |
| 使用
SQL 生成大量测试数据 |
|
无论您是在用原型证明某一概念，还是开发一个全新的应用程序，或者只是学习
SQL，您都需要在您的应用程序上运行测试数据。为了有效地测试应用程序的性能，您必须拥有足够的测试数据，以便暴露潜在的性能问题。只要可以得到，用实际数据来进行测试总是更可取一些。如果没有可用的实际数据，那么在许多情况下，也可以生成足够的假想数据。一般来说，从头开始构造大量数据是件很容易的工作，您自己就可以快速地独立完成。
本文提供了一些如何利用 SQL 脚本来生成测试数据的示例，而这些脚本本身就是较好的
SQL 实践。并且还讨论了一些为了生成尽可能真实的数据而应该注意的问题。
|
| 生成大量记录 |
| 即使数据库是新创建且仍然为空的，也总是会带有系统表和视图，因此，您可以按以下方法使用它们： |
CREATE
TABLE DB2ADMIN.SALES
(CUSTOMER_ID INT NOT NULL, ITEM_ID INT NOT NULL,
SALE_QUANTITY SMALLINT NOT NULL, SALE_DATE DATE
NOT NULL);
INSERT INTO SALES
SELECT
SYSFUN.RAND()*500 + 1 AS CUSTOMER_ID,
SYSFUN.RAND()*100 + 1 AS ITEM_ID,
1 + SYSFUN.RAND()*10 AS SALE_QUANTITY,
DATE('01/01/2003') + (SYSFUN.RAND()*200) DAYS AS
SALE_DATE
FROM SYSCAT.COLUMNS; |
|
SALES 表中的记录数就与 SYSCAT.COLUMNS
中的完全一样了。请注意，多个列都是用随机值来填充的。例如，SALE_QUANTITY 列中的所有值都是处于
1 到 10 之间，约 10% 的记录具有各不相同的值。如果您需要更多记录，就可以根据需要多次重复执行这条
INSERT 语句。您还可以像下面这样使用交叉连接（CROSS JOIN），以便每条语句获得更多记录：
|
|
注意：本例中，表 T1 和 T2
的连接是不含任何条件的，因此，T1
中的每一行会匹配 T2
中的每一行。这种类型的连接称作交叉连接。关于交叉连接的更多信息，请查阅
Joe Celko 的 SQL
for Smarties 一书。
注意：这条 INSERT
语句所涉及的事务可能会相当大，以致于您的服务器无法加以处理。如果您遇到“log
full”的情况（SQL0964C
数据库的事务日志已满），您可能需要增加日志空间，或者通过指定
T1 或 T2 或两者中的 WHERE
子句来获得一个较小的事务。
您可以使用该方法来生成大量记录，然而，该方法有点过分简单了，因为所有的值都是均匀分布的，而且它们之间不存在相关性。
|
| 填充子表 |
| 您的数据库中很可能存在多对一的关系。下列示例展示了如何填充子表，以使每一条父记录都具有随机的多条子记录。 |
CREATE
TABLE DB2ADMIN.PARENT_TABLE(PARENT_ID INT NOT
NULL, NUM_CHILDREN INT NOT NULL);
INSERT INTO DB2ADMIN.PARENT_TABLE
SELECT ROW_NUMBER() OVER(), SYSFUN.RAND()*5 + 1
FROM SYSCAT.TABLES;
ALTER TABLE DB2ADMIN.PARENT_TABLE ADD PRIMARY
KEY(PARENT_ID);
CREATE TABLE DB2ADMIN.CHILD_TABLE(PARENT_ID
INT NOT NULL, CHILD_NUM INT NOT NULL);
INSERT INTO DB2ADMIN.CHILD_TABLE
SELECT PARENT_ID, SEQUENCE_TABLE.NUM
FROM DB2ADMIN.PARENT_TABLE
JOIN
(SELECT ROW_NUMBER() OVER() AS NUM
FROM SYSCAT.TABLES) AS SEQUENCE_TABLE
ON AUXILIARY_TABLE.NUM<NUM_CHILDREN;
|
| 最后一条
INSERT 语句的结果是，每一条父记录有 1 到 6 条子记录。SEQUENCE_TABLE
是一个表表达式。关于表表达式的更多信息，请查阅 Sheryl
Larsen 的文章。 |
| 使用辅助表模仿数据倾斜 |
| 如果一列中的某些值所出现的频率比其他的要大很多，则该数据存在
数据倾斜（data skew） 。例如： |
SELECT
CITY, COUNT(*) FROM CUSTOMER
GROUP BY CITY
ORDER BY COUNT(*) DESC
CHICAGO 236
MILWAKEE 95
ROCKFORD 4
NAPERVILLE 3
SPRINGFIELD 3
(snip)
279 rows selected
|
| 每当您有理由期望在生产数据中出现数据倾斜时，您就可能需要在测试数据中再现数据倾斜，首先，在一个表中存储预计频率： |
CREATE
TABLE COLOR_FREQUENCY(COLOR CHAR(10), FREQUENCY
SMALLINT);
INSERT INTO COLOR_FREQUENCY VALUES
('RED', 37), ('SILVER',12), ('AMBER', 3),
('GREEN', 3), ('WHITE',2),('BLACK',
1),('BLUE',1); |
| 接着，创建一个辅助表（更明确地说，是一个序列表）。 |
CREATE
TABLE CONSECUTIVE_NUMBER(NUM INT NOT NULL);
INSERT INTO CONSECUTIVE_NUMBER
SELECT ROW_NUMBER() OVER() AS NUM FROM
SYSCAT.COLUMNS; |
| 注意：
Joe Celko 的 SQL for Smarties 一书中有一章是关于辅助表的。现在，让我们连接这两个表： |
SELECT
COLOR, FREQUENCY, NUM
FROM COLOR_FREQUENCY JOIN CONSECUTIVE_NUMBER
ON NUM BETWEEN 1 AND FREQUENCY ORDER BY
FREQUENCY, COLOR;
COLOR FREQUENCY NUM
---------- --------- -----------
BLACK 1 1
BLUE 1 1
WHITE 2 1
WHITE 2 2
AMBER 3 1
AMBER 3 2
AMBER 3 3
(SNIP)
|
| 正如我们所看到的，COLOR_FREQUENCY
表中的每一行都连接了 CONSECUTIVE_NUMBER 表中的 FREQUENCY 行。该示例生成了您需要用于获得所需值分布的确切内容： |
| CREATE
TABLE T_SHIRT(COLOR VARCHAR(30) NOT NULL, SIZE
CHAR(1) NOT NULL);
INSERT INTO T_SHIRT
SELECT COLOR, 'M' AS SIZE
FROM COLOR_FREQUENCY JOIN CONSECUTIVE_NUMBER
ON NUM BETWEEN 1 AND FREQUENCY;
SELECT COLOR, COUNT(*) FROM T_SHIRT GROUP BY
COLOR;
COLOR 2
------------------------------ -----------
AMBER 3
BLACK 1
BLUE 1
GREEN 3
RED 37
SILVER 12
WHITE 2 ;
|
| 因此，T_SHIRT
表现在有 37+12+3+3+2+1+1 = 57 行。该表刚好具有所需的值分布。 |
| 为几个列生成具有给定值分布的数据 |
| 使用前一章中的所用表，您还可以为
SIZE 列指定值分布： |
CREATE
TABLE SIZE_FREQUENCY(SIZE CHAR(1), FREQUENCY SMALLINT);
INSERT INTO SIZE_FREQUENCY VALUES
('S', 5), ('M',7), ('L', 9); |
| 并使用两个表表达式来填充
T_SHIRT 表： |
INSERT
INTO T_SHIRT
SELECT COLOR, SIZE
FROM
(SELECT COLOR FROM COLOR_FREQUENCY JOIN
CONSECUTIVE_NUMBER ON NUM BETWEEN 1 AND
FREQUENCY) C,
(SELECT SIZE FROM SIZE_FREQUENCY JOIN
CONSECUTIVE_NUMBER ON NUM BETWEEN 1 AND
FREQUENCY) S |
| 第一个表表达式产生
57 行，而第二个表表达式则产生 5+7+9=21 行。由于我们没有指定任何连接条件，所以第一个结果集中的每一行将会连接第二个中的每一行，从而产生
57*21 行。 |
|
注意： 交叉连接可能会生成太多行。因此，该事务将太大，以致服务器无法处理。本例中，您可能需要几个较小一些的
INSERT 语句，例如在第一个 INSERT
中使用以下表表达式：
|
| (SELECT
SIZE FROM SIZE_FREQUENCY JOIN CONSECUTIVE_NUMBER
ON NUM BETWEEN 1 AND FREQUENCY AND SIZE='L') S |
|
并且在第二个 INSERT
语句中将这个表表达式修改为：
|
| (SELECT
SIZE FROM SIZE_FREQUENCY JOIN CONSECUTIVE_NUMBER
ON NUM BETWEEN 1 AND FREQUENCY AND SIZE<>'L')
S |
| 生成具有相关列的数据 |
| 假定我们需要生成几行记录来填充
CAR 表： |
CREATE
TABLE CAR(
MAKE VARCHAR(20) NOT NULL,
MODEL VARCHAR(20) NOT NULL,
OTHER_DATA VARCHAR(20)); |
|
如果尝试前一章中的方法，我们最后将获得一些不可能的
MAKE/MODEL 组合，例如“TOYOTA METRO”和“GEO CAMRY”。该状况称作
MAKE 列和 MODEL 列之间的相关性。正确的方法是指定有效对（MAKE，MODEL）及其频率：
|
CREATE
TABLE MAKE_MODEL_FREQUENCY(MAKE VARCHAR(20), MODEL
VARCHAR(20), FREQUENCY SMALLINT);
INSERT INTO MAKE_MODEL_FREQUENCY VALUES
('TOYOTA','CAMRY', 40), ('HONDA','ACCORD',40),
('CHEVY', 'PRIZM', 5), ('GEO','PRIZM', 5),
('CHEVY', 'METRO', 5), ('GEO', 'METRO', 10); |
| 一旦完成该工作，我们就可以按照前面一模一样的方法来连接
CONSECUTIVE_NUMBER 和 MAKE_MODEL_FREQUENCY 表了。 |
| 操纵群集因子 |
| 表的物理行次序将影响该表上几乎所有查询的性能。因此，所生成的数据具有理想的物理行次序是极其重要的。如果您期望一个索引具有较高的群集因子，就只要重组该索引上的表。相反，如果您期望该索引具有较低的群集因子，也可以容易地以随机次序来打乱该表的次序，从而使得该索引的群集因子接近于
0： |
CREATE
TABLE NAMES(
FULL_NAME VARCHAR(50) NOT NULL,
ORDERED_ON INT);
INSERT INTO NAMES(FULL_NAME, ORDERED_ON)
SELECT TABNAME || ', ' || COLNAME AS FULL_NAME,
SYSFUN.RAND() * 10000 AS ORDERED_ON
FROM SYSCAT.COLUMNS;
CREATE INDEX NAMES_FULL_NAME ON
NAMES(FULL_NAME);
CREATE INDEX NAMES_ORDER ON NAMES(ORDERED_ON);
REORG TABLE DB2ADMIN.NAMES INDEX
DB2ADMIN.NAMES_ORDER;
RUNSTATS ON TABLE DB2ADMIN.NAMES AND DETAILED
INDEXES ALL;
|
| 在进行重组之后，索引
NAMES_FULL_NAME 将具有一个极低的群集因子（接近于 0），因为现在的行是以随机次序存储的。 |
|
注意： 还可以重组该表，以使索引
NAMES_FULL_NAME 的群集因子接近 0 到 1
之间的任何给定值，但是，该内容超出了本文的范围。
|
| 结束语： |
| 本文讨论了如何构建一个测试数据集，以使该数据集达到用于测试的规模，并且具有期望的值分布和列间相关性。 |
| 参考资料： |
|
|