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)


調整佔用記憶體的大小,使其足夠容納.

SET SESSION max_heap_table_size = 1024 * 1024 * 200;

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

-- 建立一個使用 Memory 的 Table 

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

-- 建立一個 Stored Procedure 
DELIMITER $$ 
 
CREATE PROCEDURE prc_filler(cnt INT) 
BEGIN 
        DECLARE _cnt INT; 
        SET _cnt = 1; 
        WHILE _cnt <= cnt DO 
                INSERT 
                INTO    filler 
                SELECT  _cnt; 
                SET _cnt = _cnt + 1; 
        END WHILE; 
END 
$$ 
 
DELIMITER ; 

-- 建立一個一百萬筆的序列 
START TRANSACTION; 
CALL prc_filler(1000000); 

Query OK, 1 row affected (12.48 sec)

COMMIT; 

-- 產生一百萬筆的序列 只需12.48秒
接著我們可以再利用這個序列Table,再來產生其他測試資料.

CREATE TABLE ithelp1005 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
amount INT UNSIGNED NOT NULL,
price DECIMAL(20, 2) NOT NULL
);

INSERT INTO ithelp1005(amount, price)
SELECT CEILING(RAND(201310) * 1000) + 100,
       CEILING(RAND(201310) * 1000) + 30.00
  FROM filler;

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

-- 只需14.68秒 就產生了一百萬筆的測試資料.

-- 挑10筆資料來看看

asami@[akina]>select * from ithelp1005 limit 10;
+----+--------+--------+
| id | amount | price  |
+----+--------+--------+
|  1 |    617 | 547.00 |
|  2 |    668 | 598.00 |
|  3 |    389 | 319.00 |
|  4 |    841 | 771.00 |
|  5 |    935 | 865.00 |
|  6 |   1053 | 983.00 |
|  7 |    358 | 288.00 |
|  8 |    531 | 461.00 |
|  9 |    480 | 410.00 |
| 10 |    709 | 639.00 |
+----+--------+--------+

查詢占用的容量
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)