MySQL產生測試資料二

利用MySQL Memory 引擎來建立基本的filler table.
首先調整my.cnf
default-storage-engine= MEMORY
mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | YES     | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | DEFAULT | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)

假設mysql中已有test資料庫
mysql> use test;

這次還是搭配Memory Engine,速度可以較快,
但是將不會一下產生100萬筆,所以不需要先調高heap.

MySQL一般使用者,均能下SHOW GLOBAL STATUS,
亦即對 information_schema.GLOBAL_STATUS 有查詢的權限.
此Table的資料筆數隨著版本不同有差異,不過數量已經夠我們
用來產生測試資料的初始之用.

首先建立一個Table,使用Memory Engine.

CREATE TABLE hitofiller(
id INT UNSIGNED NOT NULL PRIMARY KEY
)ENGINE=Memory;

產生100筆基本的資料

INSERT INTO hitofiller(id)
SELECT @num := @num + 1
  FROM information_schema.GLOBAL_STATUS a
     , (SELECT @num := 0) b
 LIMIT 100;

Query OK, 100 rows affected (0.01 sec)
Records: 100  Duplicates: 0  Warnings: 0

直接利用hitofiller 增加自己的數量,到1萬筆.

INSERT INTO hitofiller(id)
SELECT @num := @num + 1
  FROM hitofiller a
     , (SELECT id
          FROM hitofiller
         LIMIT 99) b
     , (SELECT @num := 100) c;

Query OK, 9900 rows affected (0.02 sec)
Records: 9900  Duplicates: 0  Warnings: 0

檢查 hitofiller 的數值.

SELECT COUNT(1)
     , MAX(id)
     , MIN(id)
  FROM hitofiller;

+----------+---------+---------+
| COUNT(1) | MAX(id) | MIN(id) |
+----------+---------+---------+
|    10000 |   10000 |       1 |
+----------+---------+---------+

至此已經產生了1萬筆,遞增數列的資料.

應用範例:

例如要產生100萬筆的有日期型態的測試資料.

建立測試用的Table

CREATE TABLE hito_date(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
sdate DATE NOT NULL);

INSERT INTO hito_date(sdate)
SELECT DATE_ADD('2014-01-01', INTERVAL RAND(20140312) * 364 DAY)
  FROM hitofiller a
  JOIN (SELECT id
          FROM hitofiller
         LIMIT 100) b;

Query OK, 1000000 rows affected (7.91 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

檢查資料

SELECT COUNT(1) AS cnt
     , MIN(sdate)
     , MAX(sdate)
  FROM hito_date;

+---------+------------+------------+
| cnt     | MIN(sdate) | MAX(sdate) |
+---------+------------+------------+
| 1000000 | 2014-01-01 | 2014-12-31 |
+---------+------------+------------+

大約8秒的時間,就產生了100萬筆有日期型態的測試資料.
若需要其他型態的欄位,如數值,可自行使用RAND(),產生亂數,
適當增加即可.

查詢占用的容量
mysql> SELECT table_schema "test",
    -> sum( data_length + index_length ) / 1024 /
    -> 1024 "Data Base Size in MB",
    -> sum( data_free )/ 1024 / 1024 "Free Space in MB"
    -> FROM information_schema.TABLES
    -> GROUP BY table_schema ;
+--------------------+----------------------+------------------+
| test               | Data Base Size in MB | Free Space in MB |
+--------------------+----------------------+------------------+
| information_schema |           0.00781250 |       0.00000000 |
| mysql              |           0.62927818 |       0.00000000 |
| test               |          69.34022522 |       0.00000000 |
+--------------------+----------------------+------------------+
3 rows in set (0.01 sec)