MySQL 疾速建立万万级测试数据【MySQL教程】,mysql,千万级

备注：此文章的数据量在100W，假如想要万万级，调大数目即可,然则不要大批运用rand() 或许uuid() 会致使机能下落

背景

在举行查询操纵的机能测试或许sql优化时，我们常常须要在线下环境构建大批的基本数据供我们测试，模仿线上的实在环境。

空话，总不能让我去线上去测试吧，会被DBA砍死的

建立测试数据的体式格局

    1. 编写代码，经由历程代码批量插库（本人运用过，步骤太烦琐，机能不高，不引荐）
    2. 编写存储历程和函数实行（本文完成体式格局1）
    3. 暂时数据表体式格局实行 （本文完成体式格局2，强烈引荐该体式格局，异常简朴，数据插进去疾速，100W，只需几秒）
    4. 一行一行手动插进去，（WTF，去死吧）

建立基本表构造

不管用何种体式格局，我要插在那张表总要建立的吧

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：采纳存储历程和内存表

建立内存表

应用 MySQL 内存表插进去速度快的特性，我们先应用函数和存储历程在内存表中生成数据，然后再从内存表插进去一般表中

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;

建立函数和存储历程

# 建立随机字符串和随机时候的函数
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 ;

# 建立插进去数据存储历程
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)

挪用存储历程

mysql> CALL add_t_user_memory(1000000);
ERROR 1114 (HY000): The table 't_user_memory' is full
涌现内存已满时，修正 max_heap_table_size 参数的大小，我运用64M内存，插进去了22W数据，看情况改，不过这个值不要太大，默许32M或许64M就好，生产环境不要乱尝试

从内存表插进去一般表

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：采纳暂时表

建立暂时数据表tmp_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

用 python或许bash 生成 100w 纪录的数据文件（python霎时就会生成完）

python(引荐): python -c "for i in range(1, 1+1000000): print(i)" > base.txt

导入数据到暂时表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

万万级数据 20秒插进去完成

注重：导入数据时有可能会报错，原因是mysql默许没有开secure_file_priv（这个参数用来限定数据导入和导出操纵的结果，比方实行LOAD DATA、SELECT … INTO OUTFILE语句和LOAD_FILE()函数。这些操纵须要用户具有FILE权限。）

处理办法：在mysql的配置文件中（my.ini 或许 my.conf）中增加 secure_file_priv = /Users/LJTjintao/temp/`, 然后重启mysql 处理

以暂时表为基本数据，插进去数据到t_user中，100W数据插进去须要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

更新建立时候字段让插进去的数据的建立时候越发随机

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)

更多MySQL相干技术文章，请接见MySQL教程栏目举行进修！

以上就是MySQL 疾速建立万万级测试数据的细致内容，更多请关注ki4网别的相干文章！

正文

MySQL 疾速建立万万级测试数据【MySQL教程】,mysql,千万级

背景

建立测试数据的体式格局

建立基本表构造

体式格局1：采纳存储历程和内存表

体式格局2：采纳暂时表

相关阅读

如何修改mysql的默认时区_MySQL教程,mysql,时区

MySQL如何使用授权命令grant_MySQL教程,MySQL,grant

mysql数据库如何创建数据表_MySQL教程,mysql,数据表

MySQL 连接查询超级详解_MySQL教程,MySQL,连接查询

背景

建立测试数据的体式格局

建立基本表构造

体式格局1： 采纳存储历程和内存表

体式格局2： 采纳暂时表

相关阅读

如何修改mysql的默认时区_MySQL教程,mysql,时区

MySQL如何使用授权命令grant_MySQL教程,MySQL,grant

mysql数据库如何创建数据表_MySQL教程,mysql,数据表

MySQL 连接查询超级详解_MySQL教程,MySQL,连接查询

体式格局1：采纳存储历程和内存表

体式格局2：采纳暂时表