Shulou(Shulou.com)06/01 Report--

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 () too much 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.

How to create test data

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

two。 Write stored procedures and function execution (mode 1 of this article)

3. Temporary data table execution (this article implements method 2, which is highly recommended, very simple, fast data insertion, 100W, only a few seconds)

4. Insert line by line manually, (WTF, go to hell)

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

Method 1: using stored procedures and memory tables

Create a memory table

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

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

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

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 insert data stored procedure mysql > CREATE DEFINER= `root` @ `% `PROCEDURE `add_t_user_ roomy` (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.01 sec)

Call stored procedure

Mysql > CALL add_t_user_memory (1000000); ERROR 1114 (HY000): The table't qualified usernames

When the memory is full, modify the size of the max_heap_table_size parameter. I use 64m memory and insert 22W data, depending on the situation. However, this value is not 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: using temporary tables

Create temporary data table tmp_table

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

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

Bash (not recommended, it will be slow): bash iTun1; while [$I-le 1000000]; do echo $I; let iTunes 1; done > 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

Tens of millions of level data inserted 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 | 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 | 32 | 2019-11-13 22:49:43 | | 15 | bf5e2d88-7b84-11e9-9d6e-751d319e85c2 | userNickName15 | 91 | 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 | 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-11e9-9d6e-751d319e85c2 | userNickName20 | 683 | 91 | 2019-11-13 20:02:42 | 21 | bf5e2fcc-7b84-11e9-13 | 9d6e-751d319e85c2 | userNickName21 | 2013-11-13 21:16:48 | | 22 | bf5e3026-7b84-11e9-9d6e-751d319e85c2 | userNickName22 | 562nd | 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)

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

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.