In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-06 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
Let's talk about how MySQL can quickly create tens of millions of level test data. The secret of the text lies in being close to the topic. So, forget the gossip, let's go straight to the following, and I'm sure you'll benefit from reading this article on how MySQL can quickly create tens of millions of level test data.
Note: the amount of data in this article is 100W, if you want tens of millions of levels, you can increase the number, but do not use rand () or uuid () heavily will lead to performance degradation.
Background
In the performance test of query operation or sql optimization, we often need the offline environment to build a large amount of basic data for us to test and simulate the online real environment.
Nonsense, you can't let me go online to test it. It will be hacked to death by DBA.
The way to create test data 1. Write code, through the code batch plug-in library (I have used, the steps are too cumbersome, the performance is not high, not recommended) 2. Write stored procedures and function execution (method 1 of this article) 3. Temporary data table execution (this article implementation method 2, highly recommended, very simple, data insertion fast, 100W, only a few seconds) 4. Insert row by row manually, (WTF, go to hell) to create the underlying table structure
Either way, I'm going to insert it into that table. I have to create it.
CREATE TABLE `tuser` (`id` int (11) NOT NULL AUTO_INCREMENT, `croomuserid` varchar (36) NOT NULL DEFAULT'', `cname` varchar (22) NOT NULL DEFAULT'', `croomprovinceid` int (11) NOT NULL, `croomcityid` int (11) NOT NULL, `create_ time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `idx_user_ id` (`croomuserid`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; mode 1: use 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 memory table. Then insert CREATE TABLE `troomusery` (`id` int (11) NOT NULL AUTO_INCREMENT, `croomuserid` varchar (36) NOT NULL DEFAULT'', `c _ name` varchar (22) NOT NULL DEFAULT', `croomprovinceid` int (11) NOT NULL, `croomcity` int (11) NOT NULL, `create_ time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `idx_user_ id` (`croomuserid`) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 from the memory table into the regular table.
Create functions and stored procedures
# create functions mysql > delimiter $$mysql > CREATE DEFINER= `root` @ `% `root` @`% `RandStr` (n INT) RETURNS varchar-> DETERMINISTIC-> BEGIN-> DECLARE chars_str varchar 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 an insert data stored procedure mysql > CREATE DEFINER= `root` @ `% `PROCEDURE `root` (IN n int)-> BEGIN-> DECLARE i INT DEFAULT 1;-> WHILE (i INSERT INTO t_user_memory (c_user_id, c_name, cymboloid, create_time) VALUES (uuid (), randStr (20), FLOOR (RAND () * 1000), FLOOR (RAND () * 1000), NOW () -> SET I = I + 1;-> END WHILE;-> END-> $$Query OK, 0 rows affected (0.01sec)
Call stored procedure
Mysql > CALL add_t_user_memory (1000000); ERROR 1114 (HY000): modify the size of the max_heap_table_size parameter when The table's memory is full. I used 64m memory and inserted 22W data, depending on the situation. However, this value should not be too large. The default is 32m or 64m. Do not try randomly in the production environment.
Insert a regular table from a memory table
Mysql > INSERT INTO t_user SELECT * FROM tasking usernames: query OK, 218953 rows affected (1.70 sec) Records: 218953 Duplicates: 0 Warnings: 0 Mode 2: use temporary tables
Create temporary data table tmp_table
Mysql > INSERT INTO t_user SELECT * FROM tasking usernames: query OK, 218953 rows affected (1.70 sec) Records: 218953 Duplicates: 0 Warnings: 0
Use python or bash to generate 100w recorded data files (python will be generated in an instant)
Python (recommended): python-c "for i in range (1,1x 1000000): print (I)" > base.txt
Import data into 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 million level data insertion completed in 20 seconds
Note: errors may occur when importing data, because mysql does not turn on secure_file_priv by default (this parameter is used to limit the effect of data import and export operations, such as LOAD DATA, SELECT … The INTO OUTFILE statement and the LOAD_FILE () function. These actions require the user to have FILE privileges. )
Solution: add secure_file_priv = / Users/LJTjintao/temp/ `to the mysql configuration file (my.ini or my.conf), and then restart the mysql solution
Inserting data into t_user based on temporary table takes 10.37s to insert 100W 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: 0mysql > 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: 1000000000 Changed: 0mysql > 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 | | 2014 | 40 | 2014-11-13 20:57:46 | | 4 | bf5e2888-7b84-11e9-9d6e-751d319e85c2 | userNickName4 | 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 | 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 | 2019 | 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 | 74 | 2018-11-13 22:07:58 | | 17 | bf5e2e50-7b84-11e9-9d6e-751d319e85c2 | userNickName17 | 2018 | 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 | 81 | 2013-11-13 21:16:48 | 22 | bf5e3026-11-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)
Is there anything you don't understand about how the above MySQL can quickly create tens of millions of level test data? Or if you want to know more about it, you can continue to follow our industry information section.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.