How does mysql quickly generate millions of test data
- Implementation approach
- 1, create memory table and ordinary table
- 2. Create functions and stored procedures
-
- Creates a function that generates n random numbers
- Create the generating number function
- Create a random string function
- Create a stored procedure to insert memory table data
- Create a stored procedure for inserting data into a regular table
- 3. Call a stored procedure to insert data
-
- Mysql > alter table memory size
- Call another stored procedure I wrote: add_test_user_memory_to_outside
Implementation approach
In our daily work or study process, sometimes we need to generate a large number of test data in the database, at this time, we can take advantage of mysql memory table insertion speed characteristics, first use functions and stored procedures to generate data in the memory table, and then insert the common table from the memory table. In my tests, this scheme inserts data very quickly.
Here are the implementation steps.
1, create memory table and ordinary table
CREATE TABLE`test_user_memory` (
`id` int(11) NOT NULL AUTO_INCREMENT comment 'primary key id'.`user_id` varchar(36) NOT NULL comment 'user id'.`user_name` varchar(30) NOT NULL comment 'User name'.`phone` varchar(20) NOT NULL comment 'Mobile number'.`lan_id` int(9) NOT NULL comment 'Local network'.`region_id` int(9) NOT NULL comment 'regional'.`create_time` datetime NOT NULL comment 'Creation time',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`) ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4; CREATE TABLE CREATE TABLE`test_user` (
`id` int(11) NOT NULL AUTO_INCREMENT comment 'primary key id'.`user_id` varchar(36) NOT NULL comment 'user id'.`user_name` varchar(30) NOT NULL comment 'User name'.`phone` varchar(20) NOT NULL comment 'Mobile number'.`lan_id` int(9) NOT NULL comment 'Local network'.`region_id` int(9) NOT NULL comment 'regional'.`create_time` datetime NOT NULL comment 'Creation time',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code2. Create functions and stored procedures
Creates a function that generates n random numbers
This is used when generating a cell phone number
DELIMITER $$CREATE FUNCTION randNum(nint) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str varchar(20) DEFAULT '0123456789';
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()*10 ),1));
SET i = i +1;
END WHILE;
RETURN return_str;
END $$
DELIMITER;
Copy the codeCreate the generating number function
# Generate random phone numbers # define common phone headers130 131 132 133 134 135 136 137 138 139 186 187 189 151 157
#SET starts = 1+floor(rand()*15) *4; The interception of a string starts with1,5,9,13. In the first place. floor(rand()*15) is in the range of0~14
#SET head = substring(bodys,starts,3); DELIMITER $$CREATE FUNCTION generatePhone() RETURNS varchar(20)
BEGIN
DECLARE head char(3);
DECLARE phone varchar(20);
DECLARE bodys varchar(100) default "130 131 132 133 134 135 136 138 139 186 187 189 151 157";
DECLARE starts int;
SET starts = 1+floor(rand()*15) *4;
SET head = trim(substring(bodys,starts,3));
SET phone = trim(concat(head,randNum(8)));
RETURN phone;
END $$
DELIMITER ;
Copy the codeCreate a random string function
DELIMITER $$CREATE FUNCTION DELIMITER $$CREATE 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$$
DELIMITER;
Copy the codeCreate a stored procedure to insert memory table data
DELIMITER $$CREATE PROCEDURE Insert as many data as the n parameter is`add_test_user_memory`(IN n int)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i <= n) DO
INSERT INTO test_user_memory (user_id, user_name, phone, lan_id,region_id, create_time) VALUES (uuid(), randStr(20), generatePhone(), FLOOR(RAND() * 1000), FLOOR(RAND() * 100), NOW());
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;
Copy the codeCreate memory table data to insert into plain table stored procedures
This allows you to generate millions or tens of millions of bytes of data without changing mysql’s default max_HEAP_table_size. Max_heap_table_size The default value is 16 MB. Max_heap_table_size is used to set the size of temporary memory tables created by users. The larger the value is, the more data can be stored in the memory table.
Insert data from the memory table into the ordinary table. DELIMITER $$CREATE PROCEDURE DELIMITER $$CREATE PROCEDURE specifies the amount of data inserted into the memory table and regular table each time`add_test_user_memory_to_outside`(IN n int, IN count int)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i <= n) DO
CALL add_test_user_memory(count);
INSERT INTO test_user SELECT * FROM test_user_memory;
delete from test_user_memory;
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;
Copy the code3. Call a stored procedure to insert data
CALL add_test_user_memory(test_user_memory());10000); INSERT INTO test_user SELECT * FROM test_user_memory; Empty memory table datadelete from test_user_memory;
Copy the codeLet’s briefly test how long it takes to insert 10,000 bytes into a memory table
Inserting 10,000 entries from a memory table into a regular table takes only a short time, as shown in the figure below
Query the data for the normal table that was just inserted
Because I did not change the memory size of the database memory table, it is ok to insert 10,000 data in a single memory table, but not ok to insert 100,000 data in a single memory table, which will report the exception that the memory table is full. As shown in the figure below
What if you want to call a stored procedure to insert a hundred thousand or a million bytes into an ordinary table? There are two scenarios
Mysql > alter table memory size
1. Run the mysql command to change the password
SET GLOBAL tmp_table_size=2147483648;
SET GLOBAL max_heap_table_size=2147483648;
2. Modify the mysql configuration file
vi /etc/my.cnf
[mysqld]
max_heap_table_size = 2048M
tmp_table_size = 2048M
You can view the memory table storage size in the following ways
Call another stored procedure
add_test_user_memory_to_outside
The stored procedure repeats the process of inserting a memory table, fetching data from the memory table, inserting a regular table, and finally deleting the memory table until the loop ends.
# cycle100Times, generated each time10000CALL add_test_user_memory_to_outside(100.10000);
Copy the code
A million data
