How to quickly create tens of millions of test data in MySQL

How to quickly create tens of millions of test data in MySQL

Remark:

The amount of data in this article is 1 million. If you want to have tens of millions of data, just increase the amount. However, do not use a large number of rand() or uuid(), which will cause performance degradation.

background

When performing performance testing or SQL optimization of query operations, we often need to build a large amount of basic data in an offline environment for our testing to simulate the real online environment.

Nonsense, you can't let me test it online, or I'll be killed by the DBA.

How to create test data

1. Write code and insert libraries in batches through code (I have used it, the steps are too cumbersome, the performance is not high, and it is not recommended)

2. Write stored procedures and execute functions (implementation method 1 in this article)

3. Execute in temporary data table mode (this article implements mode 2, which is highly recommended. It is very simple and the data insertion is fast. It only takes a few seconds for 100W data)

4. Manually insert line by line, (WTF, go to hell)

Create the basic table structure

No matter what method I use, I have to create the table I want to insert into.

CREATE TABLE `t_user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `c_user_id` varchar(36) NOT NULL DEFAULT '',
 `c_name` varchar(22) NOT NULL DEFAULT '',
 `c_province_id` int(11) NOT NULL,
 `c_city_id` int(11) NOT NULL,
 `create_time` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_user_id` (`c_user_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Method 1: Using stored procedures and memory tables

Create a memory table

Taking advantage of the fast insertion speed of MySQL memory table, we first use functions and stored procedures to generate data in the memory table, and then insert it into the ordinary table from the memory table

CREATE TABLE `t_user_memory` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `c_user_id` varchar(36) NOT NULL DEFAULT '',
 `c_name` varchar(22) NOT NULL DEFAULT '',
 `c_province_id` int(11) NOT NULL,
 `c_city_id` int(11) NOT NULL,
 `create_time` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_user_id` (`c_user_id`)
)ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

Creating functions and stored procedures

# Create a function that creates a random string and a random time mysql> delimiter $$
mysql> CREATE DEFINER=`root`@`%` FUNCTION `randStr`(n INT) RETURNS varchar(255) CHARSET utf8mb4
 -> DETERMINISTIC
 -> BEGIN
 -> DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
 -> DECLARE return_str varchar(255) DEFAULT '';
 -> DECLARE i INT DEFAULT 0;
 -> WHILE i < n DO
 -> SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND() * 62), 1));
 -> SET i = i + 1;
 -> END WHILE;
 -> RETURN return_str;
 -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DEFINER=`root`@`%` FUNCTION `randDataTime`(sd DATETIME,ed DATETIME) RETURNS datetime
 -> DETERMINISTIC
 -> BEGIN
 -> DECLARE sub INT DEFAULT 0;
 -> DECLARE ret DATETIME;
 -> SET sub = ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd));
 -> SET ret = DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*(sub-1)) SECOND);
 -> RETURN ret;
 -> END $$

mysql> delimiter;

# Create a procedure for inserting data into the data storage mysql> CREATE DEFINER=`root`@`%` PROCEDURE `add_t_user_memory`(IN n int)
 -> BEGIN
 -> DECLARE i INT DEFAULT 1;
 -> WHILE (i <= n) DO
 -> INSERT INTO t_user_memory (c_user_id, c_name, c_province_id,c_city_id, create_time) VALUES (uuid(), randStr(20), FLOOR(RAND() * 1000), FLOOR(RAND() * 100), NOW());
 -> SET i = i + 1;
 -> END WHILE;
 -> END
 -> $$
Query OK, 0 rows affected (0.01 sec)

Calling a stored procedure

mysql> CALL add_t_user_memory(1000000);
ERROR 1114 (HY000): The table 't_user_memory' is full

When the memory is full, modify the max_heap_table_size parameter. I used 64M memory and inserted 22W data. Change it according to the situation, but the value should not be too large. The default value of 32M or 64M is fine. Do not try it in a production environment.

Inserting from memory table to normal table

mysql> INSERT INTO t_user SELECT * FROM t_user_memory;
Query OK, 218953 rows affected (1.70 sec)
Records: 218953 Duplicates: 0 Warnings: 0

Method 2: Using a temporary table

Create a temporary data table tmp_table

CREATE TABLE tmp_table (
	id INT,
	PRIMARY KEY (id)
);

Use python or bash to generate a data file with 1 million records (python will generate it instantly)

python (recommended): python -c "for i in range(1, 1+1000000): print(i)" > base.txt
bash (not recommended, slow): bash i=1; while [ $i -le 1000000 ]; do echo $i; let i+=1; done > base.txt

Import data into the temporary table tmp_table

mysql> load data infile '/Users/LJTjintao/temp/base.txt' replace into table tmp_table;
Query OK, 1000000 rows affected (2.55 sec)
Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0

Tens of millions of data can be inserted in 20 seconds

Note: An error may occur when importing data because MySQL does not have secure_file_priv enabled by default (this parameter is used to limit the effects of data import and export operations, such as executing LOAD DATA, SELECT ... INTO OUTFILE statements, and LOAD_FILE() functions. These operations require the user to have the FILE privilege.)

Solution: Add secure_file_priv = /Users/LJTjintao/temp/` to the mysql configuration file (my.ini or my.conf), then restart mysql.

Using the temporary table as the basic data, insert data into t_user. It takes 10.37s to insert 1 million data.

mysql> INSERT INTO t_user
 -> SELECT
 -> id,
 ->uuid(),
 -> CONCAT('userNickName', id),
 -> FLOOR(Rand() * 1000),
 -> FLOOR(Rand() * 100),
 -> NOW()
 -> FROM
 -> tmp_table;
Query OK, 1000000 rows affected (10.37 sec)
Records: 1000000 Duplicates: 0 Warnings: 0

Update the creation time field to make the creation time of the inserted data more random

UPDATE t_user SET create_time=date_add(create_time, interval FLOOR(1 + (RAND() * 7)) year);

Query OK, 1000000 rows affected (5.21 sec)
Rows matched: 1000000 Changed: 1000000 Warnings: 0

mysql> UPDATE t_user SET create_time=date_add(create_time, interval FLOOR(1 + (RAND() * 7)) year);


Query OK, 1000000 rows affected (4.77 sec)
Rows matched: 1000000 Changed: 1000000 Warnings: 0
mysql> select * from t_user limit 30;
+----+--------------------------------------+----------------+-----------+-----------+---------------------+
| id | c_user_id | c_name | c_province_id | c_city_id | create_time |
+----+--------------------------------------+----------------+-----------+-----------+---------------------+
| 1 | bf5e227a-7b84-11e9-9d6e-751d319e85c2 | userNickName1 | 84 | 64 | 2015-11-13 21:13:19 |
| 2 | bf5e26f8-7b84-11e9-9d6e-751d319e85c2 | userNickName2 | 967 | 90 | 2019-11-13 20:19:33 |
| 3 | bf5e2810-7b84-11e9-9d6e-751d319e85c2 | userNickName3 | 623 | 40 | 2014-11-13 20:57:46 |
| 4 | bf5e2888-7b84-11e9-9d6e-751d319e85c2 | userNickName4 | 140 | 49 | 2016-11-13 20:50:11 |
| 5 | bf5e28f6-7b84-11e9-9d6e-751d319e85c2 | userNickName5 | 47 | 75 | 2016-11-13 21:17:38 |
| 6 | bf5e295a-7b84-11e9-9d6e-751d319e85c2 | userNickName6 | 642 | 94 | 2015-11-13 20:57:36 |
| 7 | bf5e29be-7b84-11e9-9d6e-751d319e85c2 | userNickName7 | 780 | 7 | 2015-11-13 20:55:07 |
| 8 | bf5e2a4a-7b84-11e9-9d6e-751d319e85c2 | userNickName8 | 39 | 96 | 2017-11-13 21:42:46 |
| 9 | bf5e2b58-7b84-11e9-9d6e-751d319e85c2 | userNickName9 | 731 | 74 | 2015-11-13 22:48:30 |
| 10 | bf5e2bb2-7b84-11e9-9d6e-751d319e85c2 | userNickName10 | 534 | 43 | 2016-11-13 22:54:10 |
| 11 | bf5e2c16-7b84-11e9-9d6e-751d319e85c2 | userNickName11 | 572 | 55 | 2018-11-13 20:05:19 |
| 12 | bf5e2c70-7b84-11e9-9d6e-751d319e85c2 | userNickName12 | 71 | 68 | 2014-11-13 20:44:04 |
| 13 | bf5e2cca-7b84-11e9-9d6e-751d319e85c2 | userNickName13 | 204 | 97 | 2019-11-13 20:24:23 |
| 14 | bf5e2d2e-7b84-11e9-9d6e-751d319e85c2 | userNickName14 | 249 | 32 | 2019-11-13 22:49:43 |
| 15 | bf5e2d88-7b84-11e9-9d6e-751d319e85c2 | userNickName15 | 900 | 51 | 2019-11-13 20:55:26 |
| 16 | bf5e2dec-7b84-11e9-9d6e-751d319e85c2 | userNickName16 | 854 | 74 | 2018-11-13 22:07:58 |
| 17 | bf5e2e50-7b84-11e9-9d6e-751d319e85c2 | userNickName17 | 136 | 46 | 2013-11-13 21:53:34 |
| 18 | bf5e2eb4-7b84-11e9-9d6e-751d319e85c2 | userNickName18 | 897 | 10 | 2018-11-13 20:03:55 |
| 19 | bf5e2f0e-7b84-11e9-9d6e-751d319e85c2 | userNickName19 | 829 | 83 | 2013-11-13 20:38:54 |
| 20 | bf5e2f68-7b84-11e9-9d6e-751d319e85c2 | userNickName20 | 683 | 91 | 2019-11-13 20:02:42 |
| 21 | bf5e2fcc-7b84-11e9-9d6e-751d319e85c2 | userNickName21 | 511 | 81 | 2013-11-13 21:16:48 |
| 22 | bf5e3026-7b84-11e9-9d6e-751d319e85c2 | userNickName22 | 562 | 35 | 2019-11-13 20:15:52 |
| 23 | bf5e3080-7b84-11e9-9d6e-751d319e85c2 | userNickName23 | 91 | 39 | 2016-11-13 20:28:59 |
| 24 | bf5e30da-7b84-11e9-9d6e-751d319e85c2 | userNickName24 | 677 | 21 | 2016-11-13 21:37:15 |
| 25 | bf5e3134-7b84-11e9-9d6e-751d319e85c2 | userNickName25 | 50 | 60 | 2018-11-13 20:39:20 |
| 26 | bf5e318e-7b84-11e9-9d6e-751d319e85c2 | userNickName26 | 856 | 47 | 2018-11-13 21:24:53 |
| 27 | bf5e31e8-7b84-11e9-9d6e-751d319e85c2 | userNickName27 | 816 | 65 | 2014-11-13 22:06:26 |
| 28 | bf5e324c-7b84-11e9-9d6e-751d319e85c2 | userNickName28 | 806 | 7 | 2019-11-13 20:17:30 |
| 29 | bf5e32a6-7b84-11e9-9d6e-751d319e85c2 | userNickName29 | 973 | 63 | 2014-11-13 21:08:09 |
| 30 | bf5e3300-7b84-11e9-9d6e-751d319e85c2 | userNickName30 | 237 | 29 | 2018-11-13 21:48:17 |
+----+--------------------------------------+----------------+-----------+-----------+---------------------+
30 rows in set (0.01 sec)

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:
  • Optimizing query speed of MySQL with tens of millions of data using indexes
  • MySQL loop inserts tens of millions of data
  • How to quickly paginate MySQL data volumes of tens of millions
  • Detailed explanation of several practical solutions for quickly deleting large amounts of data (tens of millions) in MySQL
  • Summary of SQL query optimization knowledge points for MySQL tens of millions of big data
  • MySql quick insert tens of millions of large data examples
  • Detailed explanation of 30 SQL query optimization techniques for MySQL tens of millions of large data
  • Optimizing the performance of paging query for MySQL with tens of millions of data
  • How to optimize MySQL fast paging for tens of millions of pages
  • Detailed explanation of MySQL database tens of millions of data query and storage

<<:  Detailed explanation of the use of Linux lseek function

>>:  Teach you step by step to develop a brick-breaking game with vue3

Recommend

Description of the hr tag in various browsers

Generally, we rarely meet HR, but once we do, it c...

CocosCreator learning modular script

Cocos Creator modular script Cocos Creator allows...

A universal nginx interface to implement reverse proxy configuration

1. What is a proxy server? Proxy server, when the...

Detailed explanation of CSS complex selectors and CSS font styles and color attributes

I have learned some basic selectors of CSS before...

js to realize automatic lock screen function

1. Usage scenarios There is such a requirement, s...

Detailed explanation of Linx awk introductory tutorial

Awk is an application for processing text files, ...

How to smoothly upgrade nginx after compiling and installing nginx

After nginx is compiled and installed and used fo...

CSS3 transition rotation perspective 2d3d animation and other effects example code

Table of contents CSS3 Box Model a. CSS3 filter b...

Build a severe weather real-time warning system with Node.JS

Table of contents Preface: Step 1: Find the free ...

Common operation commands of MySQL in Linux system

Serve: # chkconfig --list List all system service...

Detailed explanation of Linux Namespace User

User namespace is a new namespace added in Linux ...

Detailed explanation of the steps to create a web server with node.js

Preface It is very simple to create a server in n...

The best 9 foreign free picture material websites

It is difficult to find good image material websi...

How to clean up data in MySQL online database

Table of contents 01 Scenario Analysis 02 Operati...

Interviewers often ask questions about React's life cycle

React Lifecycle Two pictures to help you understa...