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查询的教程

mysql - 只有 Mysql OR mysql+sqlite OR mysql+自己的解决方案
目前我正在构建相当大的网络系统，我需要强大的 SQL 数据库解决方案。我选择 Mysql 而不是 Postgres，因为一些任务需要只读(MyISAM 引擎)而其他任务需要大量写入(InnoDB)。
mysql - Linux/mysql 将 mysql 表输出写入文件并保持 mysql 格式。
我在 mysql 中使用如下命令。当它显示表格数据时，它被格式化为一个非常干净的表格，间距均匀且 |作为列分隔符。 SELECT * FROM TABLE_NAME; 当我从 CLI 运行命令时，如下
mysql - 无法从终端加载 mysql 但可以使用系统首选项启动 mysql
我知道这个问题之前已经被问过好几次了，我已经解决了很多问题，但到目前为止没有任何效果。 MySQL 试图将自身安装到的目录 (usr/local/mysql) 肯定有问题。关于我的错误的奇怪之处在于我
mysql - 在 mysql 数据查询上获取不需要的输出 mysql
以下是我的 SQL 数据结构，我正在尝试如下两个查询: Select Wrk_ID, Wrk_LastName, Skill_Desc from Worker, Skill where
mysql - 将本地 mysql 服务器复制到基于云的 mysql
我们有一个本地 mysql 服务器(不在公共(public)域上)，并希望将该服务器复制到我们拥有的 google 云 sql 实例。我的问题是:1.这可能吗？2.我们的本地服务器只能在本地网络上访问
mysql - MySQL 触发器上 MySQL 变量的算术运算
我有一个表(test_table)，其中一些字段值(例如字段 A、B 和 C)是从外部应用程序插入的，还有一个字段(字段 D)，我想从现有表(store_table)插入其值，但在插入前者(A、B 和
mysql - 无法在创建 MySQL 服务器的同一计划中使用 mysql
我想创建一个 AWS RDS 实例，然后使用 terraform 管理数据库用户。因此，首先，我创建了一个 RDS 实例，然后使用创建的 RDS 实例初始化 mysql 提供程序，以进一步将其用于用户
mysql - MySql 用户数量对 MySql 性能影响大吗？
当用户在我的网站上注册时，他们会在我的一个数据库中创建自己的表格。该表存储用户发布的所有帖子。我还想做的是也为他们生成自己的 MySql 用户——该用户仅有权从他们的表中读取、写入和删除。创建它应该
mysql - mysql 表中的下和子类别(coldfusion，mysql)
我有一个关于 ColdFusion 和 Mysql 的问题。我有两个表:PRODUCT 和 PRODUCT_CAT。我想列出包含一些标记为:IS_EXTRANET=1 的特殊产品的类别。所以我写了这个
mysql - 如何使用 MYSQL 运算符选择列所有值都必须可用 - MYSQL
我想获取 recipes_id 列的值，以获取包含 ingredient_id 的 2,17 和 26 条目的值。假设 ingredient_id 2 丢失则不获取记录。我已经尝试过 IN 运算符
mysql - MySQL 服务器和 MySQL 客户端有什么区别
在 Ubuntu 中，我通常安装两者，但 MySQL 的客户端和服务器之间有什么区别。作为奖励，当一个新语句提到它需要 MySQL 5.x 时，它是指客户端、服务器还是两者兼而有之。例如这个链接ht
mysql - mysql - 如何在没有触发器和手动插入的情况下在插入时生成/自动增加 guid mysql？
我重新访问了我的数据库并注意到我有一些 INT 类型的主键。这还不够独特，所以我想我会有一个指导。我来自微软 sql 背景，在 ssms 中你可以选择类型为“uniqeidentifier”并自
mysql - Oracle MySQL 与 MySQL 相同吗？
我的系统上有 MySQL，我正在尝试确定它是 Oracle MySQL 还是 MySQL。 Oracle MySQL 有区别吗: http://www.oracle.com/us/products/m
mysql - 本地 mysql 服务器和生产 mysql 服务器之间的显着性能差异
我是在生产 MySQL 中运行的应用程序的新维护者。之前的维护者已经离开，留下的文档很少，而且联系不上了。我面临的问题是执行以下请求大约需要 10 秒: SELECT COUNT(*) FROM `
mysql - 如何自动将数据从一个 MySQL 数据库传输到另一个 MySQL 数据库？
我有两个位于不同机器上的 MySQL 数据库。我想自动将数据从一台服务器传输到另一台服务器。比方说，我希望每天早上 4:00 进行数据传输。可以吗？是否有任何 MySQL 内置功能可以让我们做到这一
mysql - 从 mysql 目录外的 mysql 表查询？
有什么方法可以使用 jdbc 查询位于 mysql 根目录之外的目录中的 mysql 表，还是必须将它们移动到 mysql 根目录内的数据库文件夹中？我在 Google 上搜索时没有找到任何东西。最
mysql - 使用另一个 mysql 表的值更新 Mysql 表
我在 mysql 数据库中有两个表。成员和 ClassNumbers。两个表都有一个付费年份字段，都有一个代码字段。我想用代码数字表中的值更新成员表中的付费年份，其中成员中的代码与 ClassNumb
mysql - 是否可以将本地 MySQL 数据库复制到远程 MySQL 数据库？
情况:我有 2 台服务器，其中一台当前托管一个实时 WordPress 站点，我希望能够将该站点转移到另一台服务器，以防第一台服务器出现故障。传输源文件很容易；传输数据库是我需要弄清楚如何做的。两台服
mysql - 使用 mysql 查询复制 mysql 数据库
Phpmyadmin 有一个功能是“复制数据库到”..有没有mysql查询来写这个函数？类似于将 db A 复制到新的 db B。最佳答案首先创建复制数据库: CREATE DATABASE du
mysql - 当 mySQL 已安装并由另一个应用程序配置时，为新应用程序配置 mySQL
我有一个使用 mySQL 作为后端的库存软件。我已经在我的计算机上对其进行了测试，并且运行良好。当我在计算机上安装我的软件时，我必须执行以下步骤: 安装 mySQL 服务器将用户名指定为“root

qq735679552

个人简介

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

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

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