MySQL如何快速的创建千万级测试数据-6ren

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

转载作者：qq735679552 更新时间：2022-09-27 22:32:09

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章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 。

 
    ? 
   
         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 bash(不推荐，会比较慢): bash i=1; while [ $i -le 1000000 ]; do echo $i; let i+=1; done > 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 解决。

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)

总结。

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对我的支持.

原文链接：https://juejin.im/post/5ce372c36fb9a07ef63fb191 。

最后此篇关于MySQL如何快速的创建千万级测试数据的文章就讲到这里了,如果你想了解更多关于MySQL如何快速的创建千万级测试数据的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！。

文章推荐： Python基于scrapy采集数据时使用代理服务器的方法

文章推荐： Swift使用WKWebView在iOS应用中调用Web的方法详解

文章推荐： Swift自定义iOS中的TabBarController并为其添加动画

文章推荐：在Python的gevent框架下执行异步的Solr查询的教程

MSSQL监控数据库的DDL操作(创建，修改，删除存储过程，创建，修改，删除表等)
前言：有时候，一个数据库有多个帐号，包括数据库管理员，开发人员，运维支撑人员等，可能有很多帐号都有比较大的权限，例如DDL操作权限(创建，修改，删除存储过程，创建，修改，删除表等），账户多了，管理
javascript - 使用 Storybook 创建 React App 创建 webpack 问题
所以我用 Create React App 创建并设置了一个大型 React 应用程序。最近我们开始使用 Storybook 来处理和创建组件。它很棒。但是，当我们尝试运行或构建应用程序时，我们不断遇
javascript - 创建 Angular Directive(指令)以重用代码 - 创建 html 时解析错误
遵循我正在创建的控件的代码片段。这个控件用在不同的地方，变量也不同。我正在尝试编写指令来清理代码，但在 {{}} 附近插入值时出现解析错误。刚接触 Angular ，无法确定我错过了什么。请帮忙。
java - 创建 JAX-RS 提供程序以从 InputStream 创建 Java Image
我正在尝试创建一个 image/jpeg jax-rs 提供程序类，它为我的基于 post rest 的 Web 服务创建一个图像。我无法制定请求来测试以下内容，最简单的测试方法是什么？ @POST
c - 当我使用 FILE 创建 txt 文件时，Dev C++ 创建 test.txt
我一直在 Windows 10 的模拟器中练习 c。后来我改用dev C++ IDE。当我在 C 中使用 FILE 时。创建的文件的名称为 test.txt ，而我给出了其他名称。请帮助解决它。下面
ios - 为什么我们不遵循使用 xib 创建 customTableViewCell 的相同过程，就像使用 xib 创建 customView 一样？
当我们创建自定义 View 时，我们将 View 文件的所有者设置为自定义类，并使用 initWithFrame 或 initWithCode 对其进行实例化。当我们创建 customUITable
创建 Pthreads
我正在尝试为函数 * Producer 创建一个线程，但用于创建线程的行显示错误。我为这句话加了星标，但我无法弄清楚它出了什么问题...... #include #include #include
创建、调用JavaScript对象的方法集锦
今天在做项目时，遇到了需要创建JavaScript对象的情况。所以Bing了一篇老外写的关于3种创建JavaScript对象的文章，看后跟着打了一遍代码。感觉方法挺好的，在这里与大家分享一下。 &
python - 创建 StringToSign
我正在阅读将查询字符串传递给 Amazon 的 S3 以进行身份验证的文档，但似乎无法理解 StringToSign 的创建和使用方式。我正在寻找一个具体示例来说明 (1) 如何构造 String
c# - 创建、不等待和确保任务完成的正确方法
前言:我对 C# 中任务的底层实现不太了解，只了解它们的用法。为我在下面屠宰的任何东西道歉: 对于“我怎样才能开始一项任务但不等待它？”这个问题，我找不到一个好的答案。在 C# 中。更具体地说，即使任
linq - 创建 ILookups
我有一个由一些复杂的表达式生成的 ILookup。假设这是按姓氏查找人。 (在我们简单的世界模型中，姓氏在家庭中是唯一的) ILookup families; 现在我有两个对如何构建感兴趣的查询。首
WIX bundle 创建
我试图创建一个 MSI，其中包含和 exe。在 WIX 中使用了捆绑选项。这样做时出错。有人可以帮我解决这个问题。下面是代码: 错误 error LGH
Yii 创建、更新具有不同字段的表单
在 Yii 中，Create 和 Update 通常使用相同的形式。因此，如果我在创建期间有电子邮件、密码、...other_fields...等字段，但我不想在更新期间专门显示电子邮件和密码字段，但
qt - 创建 QModelIndex
上周我一直在努力创建一个给定一行和一列的 QModelIndex。或者，我会满足于在已经存在的 QModelIndex 中更改 row() 的值。任何帮助，将不胜感激。编辑: QModelInd
C: 创建、传递和访问指向常量字符串的常量指针数组
出于某种原因，这不起作用: const char * str_reset_command = "\r\nReset"; const char * str_config_command = "\r\nC
r - 创建 "other"字段
现在，我有以下由 original.df %.% group_by(Category) %.% tally() %.% arrange(desc(n)) 创建的 data.frame。 DF 5),
vim - 创建〜/.vimrc后错误打开文件
在今天之前，我使用/etc/vim/vimrc来配置我的vim设置。今天，我想到了创建.vimrc文件。所以，我用 touch .vimrc cat /etc/vim/vimrc > .vimrc 所
iPhone:创建 MKAnnotation
我可以创建一个 MKAnnotation，还是只读的？我有坐标，但我发现使用 setCooperative 手动创建 MKAnnotation 并不容易。想法？最佳答案 MKAnnotation
iphone - 创建 NSDictionary
在以下代码中，第一个日志语句按预期显示小数，但第二个日志语句记录 NULL。我做错了什么？ NSDictionary *entry = [[NSDictionary alloc] initWithOb
php - 创建/添加多维数组值时的未定义偏移
我正在使用与此类似的代码动态添加到数组； $arrayF[$f+1][$y][$x+1] = $value+1; 但是我在错误报告中收到了这个: undefined offset :1 问题:尝试创

qq735679552

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

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