| 無論您是在用原型證明某一概念，還是開發一個全新的應用程序，或者只是學習 javascript:;" onClick="javascript:tagshow(event, 'SQL');" target="_self">SQL，您都需要在您的應用程序上運行測試數據。本文討論了如何生成足夠的、對測試有用的、具有期望的值分布和列間相關性的測試數據。 |
| 使用 SQL 生成大量測試數據 |
|
無論您是在用原型證明某一概念，還是開發一個全新的應用程序，或者只是學習 SQL，您都需要在您的應用程序上運行測試數據。為了有效地測試應用程序的性能，您必須擁有足夠的測試數據，以便暴露潛在的性能問題。只要可以得到，用實際數據來進行測試總是更可取一些。如果沒有可用的實際數據，那么在許多情況下，也可以生成足夠的假想數據。一般來說，從頭開始構造大量數據是件很容易的工作，您自己就可以快速地獨立完成。
本文提供了一些如何利用 SQL 腳本來生成測試數據的示例，而這些腳本本身就是較好的 SQL 實踐。并且還討論了一些為了生成盡可能真實的數據而應該注意的問題。 |
| 生成大量記錄 |
| 即使數據庫是新創建且仍然為空的，也總是會帶有系統表和視圖，因此，您可以按以下方法使用它們： |
clearcase/" target="_blank" >cccccc height=17>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 一書中有一章是關于輔助表的?，F在，讓我們連接這兩個表： |
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 之間的任何給定值，但是，該內容超出了本文的范圍。 |
| 結束語： |
| 本文討論了如何構建一個測試數據集，以使該數據集達到用于測試的規模，并且具有期望的值分布和列間相關性。 |
| 參考資料： |
|
|
模板
教程
環境
性能
功能
管理
