主頁 > 知識庫 > MySQL如何快速的創(chuàng)建千萬級測試數(shù)據(jù)

MySQL如何快速的創(chuàng)建千萬級測試數(shù)據(jù)

備注：

此文章的數(shù)據(jù)量在100W，如果想要千萬級，調(diào)大數(shù)量即可,但是不要大量使用rand() 或者uuid() 會導(dǎo)致性能下降

背景

在進行查詢操作的性能測試或者sql優(yōu)化時，我們經(jīng)常需要在線下環(huán)境構(gòu)建大量的基礎(chǔ)數(shù)據(jù)供我們測試，模擬線上的真實環(huán)境。

廢話，總不能讓我去線上去測試吧，會被DBA砍死的

創(chuàng)建測試數(shù)據(jù)的方式

1. 編寫代碼，通過代碼批量插庫（本人使用過，步驟太繁瑣，性能不高，不推薦）

2. 編寫存儲過程和函數(shù)執(zhí)行（本文實現(xiàn)方式1）

3. 臨時數(shù)據(jù)表方式執(zhí)行（本文實現(xiàn)方式2，強烈推薦該方式，非常簡單，數(shù)據(jù)插入快速，100W，只需幾秒）

4. 一行一行手動插入，（WTF，去死吧）

創(chuàng)建基礎(chǔ)表結(jié)構(gòu)

不管用何種方式，我要插在那張表總要創(chuàng)建的吧

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;

方式1：采用存儲過程和內(nèi)存表

創(chuàng)建內(nèi)存表

利用 MySQL 內(nèi)存表插入速度快的特點，我們先利用函數(shù)和存儲過程在內(nèi)存表中生成數(shù)據(jù)，然后再從內(nèi)存表插入普通表中

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;

創(chuàng)建函數(shù)和存儲過程

# 創(chuàng)建隨機字符串和隨機時間的函數(shù)
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 ;

# 創(chuàng)建插入數(shù)據(jù)存儲過程
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)

調(diào)用存儲過程

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

出現(xiàn)內(nèi)存已滿時，修改 max_heap_table_size 參數(shù)的大小，我使用64M內(nèi)存，插入了22W數(shù)據(jù)，看情況改，不過這個值不要太大，默認32M或者64M就好，生產(chǎn)環(huán)境不要亂嘗試

從內(nèi)存表插入普通表

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

方式2：采用臨時表

創(chuàng)建臨時數(shù)據(jù)表tmp_table

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

用 python或者bash 生成 100w 記錄的數(shù)據(jù)文件（python瞬間就會生成完）

python(推薦): python -c "for i in range(1, 1+1000000): print(i)" > base.txt
bash(不推薦，會比較慢): bash i=1; while [ $i -le 1000000 ]; do echo $i; let i+=1; done > base.txt

導(dǎo)入數(shù)據(jù)到臨時表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

千萬級數(shù)據(jù) 20秒插入完成

注意： 導(dǎo)入數(shù)據(jù)時有可能會報錯，原因是mysql默認沒有開secure_file_priv（這個參數(shù)用來限制數(shù)據(jù)導(dǎo)入和導(dǎo)出操作的效果，例如執(zhí)行LOAD DATA、SELECT … INTO OUTFILE語句和LOAD_FILE()函數(shù)。這些操作需要用戶具有FILE權(quán)限。）

解決辦法：在mysql的配置文件中（my.ini 或者 my.conf）中添加 secure_file_priv = /Users/LJTjintao/temp/` , 然后重啟mysql 解決

以臨時表為基礎(chǔ)數(shù)據(jù)，插入數(shù)據(jù)到t_user中，100W數(shù)據(jù)插入需要10.37s

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

更新創(chuàng)建時間字段讓插入的數(shù)據(jù)的創(chuàng)建時間更加隨機

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)

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，謝謝大家對腳本之家的支持。

您可能感興趣的文章:

mysql千萬級數(shù)據(jù)量根據(jù)索引優(yōu)化查詢速度的實現(xiàn)
MySQL循環(huán)插入千萬級數(shù)據(jù)
MySQL 千萬級數(shù)據(jù)量如何快速分頁
MySQL 快速刪除大量數(shù)據(jù)（千萬級別）的幾種實踐方案詳解
MySQL千萬級大數(shù)據(jù)SQL查詢優(yōu)化知識點總結(jié)
MySql 快速插入千萬級大數(shù)據(jù)的方法示例
30個mysql千萬級大數(shù)據(jù)SQL查詢優(yōu)化技巧詳解
mysql千萬級數(shù)據(jù)分頁查詢性能優(yōu)化
如何優(yōu)化Mysql千萬級快速分頁
詳解MySQL數(shù)據(jù)庫千萬級數(shù)據(jù)查詢和存儲

標(biāo)簽：蘭州崇左衡水仙桃湘潭銅川黃山湖南

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL如何快速的創(chuàng)建千萬級測試數(shù)據(jù)》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。