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 a large number of rand() or uUID () will cause performance degradation
background
In the performance test of query operation or SQL optimization, we often need to build a large amount of basic data in the offline environment for us to test and simulate the real environment online.
Duh, you can’t let me test on the line, I’ll get hacked to death by the DBA
How to create test data
1. Write codes and plug libraries in batches through codes (I have used them, the steps are too tedious and the performance is not high, not recommended) 2. Write stored procedures and function execution (implementation 1) 3. Temporary data table execution (implementation 2, highly recommended, very simple, fast data insertion, 100W, only a few seconds) 4. Insert line by line manually, (WTF, go to hell)Copy the codeCreate the underlying table structure
Anyway, I’m going to plug in that table and I’m going to create it
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;
Copy the codeMethod 1: Use stored procedures and memory tables
- Create memory table
To take advantage of the fast insertion speed of MySQL memory table, we first use functions and stored procedures to generate data in 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;
Copy the code- Create functions and stored procedures
# create random string and random time function
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 insert data store proceduremysql> 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.01sec)Copy the code- Calling a stored procedure
mysql> CALL add_t_user_memory(1000000);
ERROR 1114 (HY000): The table 't_user_memory'Change the size of max_heap_table_size. I used 64MB of memory and inserted 22W of data, depending on the situation, but this value should not be too large. Default is 32MB or 64MBCopy the code- Inserts a regular table from a memory 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
Copy the codeMethod 2: Use a temporary table
- Create temporary table tmp_table
CREATE TABLE tmp_table (
id INT,
PRIMARY KEY (id)
);
Copy the code- Generate 100W data files using Python or bash (Python will do it in a flash)
Python (recommended): python-c"for i in range(1, 1+1000000): print(i)" > base.txt
Copy the code- Import data into temporary table tMP_table
mysql> load data infile '/Users/LJTjintao/temp/base.txt'replace into table tmp_table; 2. To be deflected or deflected. 3. To be deflected or deflected. Query OK, 1000000 Rows affected (2.55 SEC) Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0 millionCopy the codeMysql does not have secure_file_priv enabled by default. Secure_file_priv is used to limit the effects of DATA import and export operations, such as LOAD DATA, SELECT… INTO OUTFILE statement and the LOAD_FILE() function. These operations require the user to have FILE permission.
Add secure_file_priv = /Users/ ljtvalue /temp/ ‘to your mysql configuration file (my.ini or my.conf) and restart mysql
- Insert data into T_user based on temporary table, 100W data insert takes 10.37 seconds
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
Copy the code- 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
Copy the codemysql> select * from t_user limit30; +----+--------------------------------------+----------------+---------------+-----------+---------------------+ | 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 rowsin set (0.01 sec)
Copy the code