MySQL 快速创建千万级测试数据

 Docker君   2019-06-07 09:01   105 人阅读  0 条评论

mysql.png

背景

在进行查询操作的性能测试或者 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:采用存储过程和内存表

  • 创建内存表

  1. 利用 MySQL 内存表插入速度快的特点,我们先利用函数和存储过程在内存表中生成数据,然后再从内存表插入普通表中


  2. CREATE TABLE `t_user_memory` (

  3. `id` int(11) NOT NULL AUTO_INCREMENT,

  4. `c_user_id` varchar(36) NOT NULL DEFAULT '',

  5. `c_name` varchar(22) NOT NULL DEFAULT '',

  6. `c_province_id` int(11) NOT NULL,

  7. `c_city_id` int(11) NOT NULL,

  8. `create_time` datetime NOT NULL,

  9.  PRIMARY KEY (`id`),

  10.  KEY `idx_user_id` (`c_user_id`)

  11. ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

  • 创建函数和存储过程

  1. # 创建随机字符串和随机时间的函数

  2. mysql> delimiter $$

  3. mysql> CREATE DEFINER=`root`@`%` FUNCTION `randStr`(n INT) RETURNS varchar(255) CHARSET utf8mb4

  4.    ->     DETERMINISTIC

  5.    -> BEGIN

  6.    ->     DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

  7.    ->     DECLARE return_str varchar(255) DEFAULT '' ;

  8.    ->     DECLARE i INT DEFAULT 0;

  9.    ->     WHILE i < n DO

  10.    ->         SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND() * 62), 1));

  11.    ->         SET i = i + 1;

  12.    ->     END WHILE;

  13.    ->     RETURN return_str;

  14.    -> END$$

  15. Query OK, 0 rows affected (0.00 sec)


  16. mysql> CREATE DEFINER=`root`@`%` FUNCTION `randDataTime`(sd DATETIME,ed DATETIME) RETURNS datetime

  17.    ->     DETERMINISTIC

  18.    -> BEGIN

  19.    ->     DECLARE sub INT DEFAULT 0;

  20.    ->     DECLARE ret DATETIME;

  21.    ->     SET sub = ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd));

  22.    ->     SET ret = DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*(sub-1)) SECOND);

  23.    ->     RETURN ret;

  24.    -> END $$


  25. mysql> delimiter ;


  26. # 创建插入数据存储过程

  27. mysql> CREATE DEFINER=`root`@`%` PROCEDURE `add_t_user_memory`(IN n int)

  28.    -> BEGIN

  29.    ->     DECLARE i INT DEFAULT 1;

  30.    ->     WHILE (i <= n) DO

  31.    ->         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());

  32.    ->         SET i = i + 1;

  33.    ->     END WHILE;

  34.    -> END

  35.    -> $$

  36. 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

CREATE TABLE tmp_table (    id INT,    PRIMARY KEY (id));
  • 用 python或者bash 生成 100w 记录的数据文件(python瞬间就会生成完)

python(推荐): python -c "for i in range(1, 1+1000000): print(i)" > base.txt
  • 导入数据到临时表tmp_table中

  1. mysql> load data infile '/Users/LJTjintao/temp/base.txt' replace into table tmp_table;

  2. Query OK, 1000000 rows affected (2.55 sec)

  3. Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0


  4. 千万级数据 20秒插入完成

注意:导入数据时有可能会报错,原因是mysql默认没有开securefilepriv( 这个参数用来限制数据导入和导出操作的效果,例如执行LOAD DATA、SELECT … INTO OUTFILE语句和LOAD_FILE()函数。这些操作需要用户具有FILE权限 )

解决办法:在mysql的配置文件中(my.ini 或者 my.conf)中添加 securefilepriv = /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
  • 更新创建时间字段让插入的数据的创建时间更加随机


  1. UPDATE t_user SET create_time=date_add(create_time, interval FLOOR(1 + (RAND() * 7)) year);


  2. Query OK, 1000000 rows affected (5.21 sec)

  3. Rows matched: 1000000 Changed: 1000000 Warnings: 0


  4. mysql> UPDATE t_user SET create_time=date_add(create_time, interval FLOOR(1 + (RAND() * 7)) year);



  5. Query OK, 1000000 rows affected (4.77 sec)

  6. Rows matched: 1000000 Changed: 1000000 Warnings: 0


  7. mysql> select * from t_user limit 30;

  8. +----+--------------------------------------+----------------+---------------+-----------+---------------------+

  9. | id | c_user_id                            | c_name         | c_province_id | c_city_id | create_time         |

  10. +----+--------------------------------------+----------------+---------------+-----------+---------------------+

  11. |  1 | bf5e227a-7b84-11e9-9d6e-751d319e85c2 | userNickName1  |            84 |        64 | 2015-11-13 21:13:19 |

  12. |  2 | bf5e26f8-7b84-11e9-9d6e-751d319e85c2 | userNickName2  |           967 |        90 | 2019-11-13 20:19:33 |

  13. |  3 | bf5e2810-7b84-11e9-9d6e-751d319e85c2 | userNickName3  |           623 |        40 | 2014-11-13 20:57:46 |

  14. |  4 | bf5e2888-7b84-11e9-9d6e-751d319e85c2 | userNickName4  |           140 |        49 | 2016-11-13 20:50:11 |

  15. |  5 | bf5e28f6-7b84-11e9-9d6e-751d319e85c2 | userNickName5  |            47 |        75 | 2016-11-13 21:17:38 |

  16. |  6 | bf5e295a-7b84-11e9-9d6e-751d319e85c2 | userNickName6  |           642 |        94 | 2015-11-13 20:57:36 |

  17. |  7 | bf5e29be-7b84-11e9-9d6e-751d319e85c2 | userNickName7  |           780 |         7 | 2015-11-13 20:55:07 |

  18. |  8 | bf5e2a4a-7b84-11e9-9d6e-751d319e85c2 | userNickName8  |            39 |        96 | 2017-11-13 21:42:46 |

  19. |  9 | bf5e2b58-7b84-11e9-9d6e-751d319e85c2 | userNickName9  |           731 |        74 | 2015-11-13 22:48:30 |

  20. | 10 | bf5e2bb2-7b84-11e9-9d6e-751d319e85c2 | userNickName10 |           534 |        43 | 2016-11-13 22:54:10 |

  21. | 11 | bf5e2c16-7b84-11e9-9d6e-751d319e85c2 | userNickName11 |           572 |        55 | 2018-11-13 20:05:19 |

  22. | 12 | bf5e2c70-7b84-11e9-9d6e-751d319e85c2 | userNickName12 |            71 |        68 | 2014-11-13 20:44:04 |

  23. | 13 | bf5e2cca-7b84-11e9-9d6e-751d319e85c2 | userNickName13 |           204 |        97 | 2019-11-13 20:24:23 |

  24. | 14 | bf5e2d2e-7b84-11e9-9d6e-751d319e85c2 | userNickName14 |           249 |        32 | 2019-11-13 22:49:43 |

  25. | 15 | bf5e2d88-7b84-11e9-9d6e-751d319e85c2 | userNickName15 |           900 |        51 | 2019-11-13 20:55:26 |

  26. | 16 | bf5e2dec-7b84-11e9-9d6e-751d319e85c2 | userNickName16 |           854 |        74 | 2018-11-13 22:07:58 |

  27. | 17 | bf5e2e50-7b84-11e9-9d6e-751d319e85c2 | userNickName17 |           136 |        46 | 2013-11-13 21:53:34 |

  28. | 18 | bf5e2eb4-7b84-11e9-9d6e-751d319e85c2 | userNickName18 |           897 |        10 | 2018-11-13 20:03:55 |

  29. | 19 | bf5e2f0e-7b84-11e9-9d6e-751d319e85c2 | userNickName19 |           829 |        83 | 2013-11-13 20:38:54 |

  30. | 20 | bf5e2f68-7b84-11e9-9d6e-751d319e85c2 | userNickName20 |           683 |        91 | 2019-11-13 20:02:42 |

  31. | 21 | bf5e2fcc-7b84-11e9-9d6e-751d319e85c2 | userNickName21 |           511 |        81 | 2013-11-13 21:16:48 |

  32. | 22 | bf5e3026-7b84-11e9-9d6e-751d319e85c2 | userNickName22 |           562 |        35 | 2019-11-13 20:15:52 |

  33. | 23 | bf5e3080-7b84-11e9-9d6e-751d319e85c2 | userNickName23 |            91 |        39 | 2016-11-13 20:28:59 |

  34. | 24 | bf5e30da-7b84-11e9-9d6e-751d319e85c2 | userNickName24 |           677 |        21 | 2016-11-13 21:37:15 |

  35. | 25 | bf5e3134-7b84-11e9-9d6e-751d319e85c2 | userNickName25 |            50 |        60 | 2018-11-13 20:39:20 |

  36. | 26 | bf5e318e-7b84-11e9-9d6e-751d319e85c2 | userNickName26 |           856 |        47 | 2018-11-13 21:24:53 |

  37. | 27 | bf5e31e8-7b84-11e9-9d6e-751d319e85c2 | userNickName27 |           816 |        65 | 2014-11-13 22:06:26 |

  38. | 28 | bf5e324c-7b84-11e9-9d6e-751d319e85c2 | userNickName28 |           806 |         7 | 2019-11-13 20:17:30 |

  39. | 29 | bf5e32a6-7b84-11e9-9d6e-751d319e85c2 | userNickName29 |           973 |        63 | 2014-11-13 21:08:09 |

  40. | 30 | bf5e3300-7b84-11e9-9d6e-751d319e85c2 | userNickName30 |           237 |        29 | 2018-11-13 21:48:17 |

  41. +----+--------------------------------------+----------------+---------------+-----------+---------------------+

  42. 30 rows in set (0.01 sec)

注意:此文章的数据量在100W,如果想要千万级,调大数量即可,但是不要大量使用rand() 或者uuid() 会导致性能下降。


往期精彩

《Docker是什么?》

Kubernetes是什么?

《Kubernetes和Docker到底有啥关系?》

教你如何快捷的查询选择网络仓库镜像tag

《Docker镜像进阶:了解其背后的技术原理》

《教你如何修改运行中的容器端口映射》

k8s学习笔记:介绍&上手

k8s学习笔记:缩扩容&更新

《Docker君带你认识Docker Swarm》

Docker 基础用法和命令帮助

在K8S上搭建Redis集群

灰度部署、滚动部署、蓝绿部署

PM2实践指南

Docker垃圾清理

Kubernetes(k8s)底层网络原理刨析

容器环境下Node.js的内存管理



免责声明:本文内容来源于网络,所载内容仅供参考。转载仅为学习和交流之目的,如无意中侵犯您的合法权益,请及时联系西岐Docker君!



dockerchina

本文地址:http://dockerchina.cn/?id=20
版权声明:本文为原创文章,版权归 Docker君 所有,欢迎分享本文,转载请保留出处!

 发表评论


表情

还没有留言,还不快点抢沙发?