教你几招,快速创建 MySQL 五百万级数据,愉快的学习各种优化技巧

一个工具类搞定批量插入增加数据到Mysql数据库 只要学我一样编写这么一个工具类便可以实现批量插入多条数据,百条,千条,万条,后期还会继续优化增加数据时的速度!有点代码基础的保证能看懂,此项目已经有了前端界面你可以直接的导入然后运行测试既可以了,表结构都在包里,不懂得随时来问我 立即下载

如果你打算好好学习一下 MySQL,性能优化肯定是绕不过去一个问题。当你撸起袖子准备开始的时候,突然发现一个问题摆在眼前,本地数据库中没那么大的数据量啊,几条数据优化个毛线啊。生产库里数据多,但谁敢直接在生产环境动手啊,想被提前优化吗?

要知道,程序员从不轻言放弃,没有数据我们就自己创造数据嘛,new 对象这种事情可是我们的拿手好戏,对象都能 new 出来,更别说几百万条数据了。

使用官方数据

官方显然知道我们需要一些测试数据做个练习什么的,所以准备了一份测试数据给我们。可以到 https://github.com/datacharmer/test_db 上去下载,这个数据库包含约30万条员工记录和280万个薪水条目,文件大小为 167 M。

下载完成之后,直接使用 MySQL 客户端运行 sql 文件即可。

或者直接使用命令,然后输入密码导入。

mysql -u root -p < employees.sql
复制代码

这是最简单的一种方法,只要你能把 sql 文件下载下来就可以了。但是数据量不够大,员工表才 30 万条数据,还不够百万级别,而且字段都是定义好的,不能灵活定制。

背景说明

创建百万级数据的方式,要到达的目的有两点:

  1. 定制比较灵活,不能只是一两个字段了事,那没什么实际意义。

  2. 速度快,不能说弄个几百万数据好几个小时甚至更长,那不能接收。

本次目标是创建两个表,一个用户表,另外一个订单表,当然没有真实环境中的表字段那么多,但是对于学习测试来说差不多够了。

两个表的表结构如下:

 

用户表(user)创建 500 万条数据,id 使用 uuid,年龄从 1 到 120 随机,电话号码随机 11 位,省份编码和城市编码随机,创建时间和更新时间在某一时间范围内随机。

订单表(order)根据用户表生成,每个用户随机生成 0 到 3 个订单,订单编号采用 uuid,商品数量随机 1 到 5 个,价格随机,创建时间和更新时间在某一时间段内随机。由于每个用户产生 0 到 3 个订单,所以,产生的订单量应该大于 500 万,我在本地跑的时候基本上在 700多万左右。

创建总时间和表的字段个数以及字段的生成算法有直接关系,字段越多、算法越复杂,需要的时间就越多,比如使用 uuid 就比使用自增 id 花费更长时间,随机时间就比直接使用当前时间花费更长时间。

如果只插入 500 万自增 id 这一个字段,十几秒就能完成,但是无论是模拟线上环境还是自学性能优化技巧都没什么意义。

下面就来介绍三种方式来快速创建 500 万用户数据以及大于 500 万的订单数据。

写程序批量插入

作为一个开发人员,当你打算创建百万条数据的时候,大多数时候首先相当的应该就是写程序,毕竟 CURD 我们最拿手了。

用程序的方式插入也分两种情况,第一种就是逐条插入,这也是平时开发中最常用到的方法,直觉上我们可能会认为这样比较快。事实上并不是这样,虽然比起手动一条一条插入是快的多,但是,很有可能你在等待了一段时间后失去耐心,然后结束程序,不管你用哪种数据库连接池都一样,在百万数量级面前仍然慢的离谱。

第二种情况就是使用 MySQL 的批量插入方法,我们都知道 MySQL 支持一次性插入多条记录,就是下面这样的形式。

insert into `table_name` (id,column1) values (1,'value1'),(2,'value2'),(3,'value3');
复制代码

这样一来,比你一条一条语句执行要快很多,比如 1000 条记录执行一次 insert,一共执行 5000 次即可,如果是一条一条插入呢,那就要执行 500 万次。

由于后面两种方式用到了 Python 生成文件,所以这种方式也用了 Python 实现,实例代码如下。完整代码可在文末给出的 github 上获取。

def insert_data(self):
  cursor = self.conn.cursor()
  for x in range(5000):
    insert_user_sql = """
            insert into `user` ( `id`,`user_name`,`phone`,`age`, `province`, `city`, `create_time`,`update_time` )
                    VALUES(%s,%s,%s,%s,%s,%s,%s,%s)
                """
    insert_order_sql = """ insert into `order` ( `id`, `product_count`, `user_id`, `price`, `create_time`, `update_time`) 
                               values(%s,%s,%s,%s,%s,%s)
                               """
    user_values, order_values = [], []
    for i in range(1000):
      timestamp = self.randomTimestamp()
      time_local = time.localtime(timestamp)
      createTime = time.strftime("%Y-%m-%d %H:%M:%S", time_local)
      user_id = str(uuid.uuid4())
      user_values.append(
        (user_id, "名字" + str(x) + str(i), self.createPhone(), random.randint(1, 120),
         str(random.randint(1, 26)),
         str(random.randint(1, 1000)), createTime, createTime))

      random_order_count = random.randint(0, 3)
      if random_order_count > 0:
        for c in range(random_order_count):
          timestamp = self.randomTimestamp()
          time_local = time.localtime(timestamp)
          order_create_time = time.strftime("%Y-%m-%d %H:%M:%S", time_local)
          order_values.append((str(uuid.uuid4()), random.randint(1, 5), user_id,
                               random.randint(10, 2000), order_create_time, order_create_time))
          cursor.executemany(insert_user_sql, user_values)
          cursor.executemany(insert_order_sql, order_values)
          self.conn.commit()

          cursor.close()
复制代码

经过一段时间时间的等待后,运行完成了,整个运行过程耗时 1823 秒,30分钟。

image-20200805103601928

image-20200805103601928

最后成功生成用户记录 500 万条,订单记录 749 万多条。

速度还算能接受吧,马马虎虎吧。

再想速度快一点,可以开多线程,我用 5 个线程跑了一下,一个线程插入 100万条,最终最长的线程耗时 1294秒,21分钟,也没快多少,线程个数对时间多少有些影响,但是我没有试。

image-20200805115418647

生成 SQL 脚本

这种方式和上面的方式类似,只不过上面通过程序方式直接将拼接出来的 SQL 语句执行了,而这种方式是将拼接好的 SQL 语句写入文件中。当然还是以一条语句插入多行记录的形式。

insert into `table_name` (id,column1) values (1,'value1'),(2,'value2'),(3,'value3');
复制代码

写 500 万用户数据,加上随机的订单数据, sql 文件的过程耗时为 696 秒,11分钟左右。

当然这么大数据量拼接出来的脚本文件也很大,用户表脚本 680 多M,订单表脚本 1个G。

image-20200803235112353

最后将写好的这两个文件分别在 MySQL 中执行。

执行用户表脚本,耗时 3 分钟左右。

mysql -uroot -p mast_slave < sql/insert_user_500w.sql
复制代码

执行订单表脚本,耗时 7 分钟左右,订单量 750 多万个。

mysql -uroot -p mast_slave < sql/insert_order_500w+.sql
复制代码

一共耗时,20分钟左右,加上中间的手工操作,感觉不如第一种方法中的多线程方式省事。

load data infile 方式

最后这种方式是使用 load data infile 方式,这是 MySQL 提供的一种从文件快速导入的方式。比如按照特定符号分隔,导入对应的字段中。

本文例子中我是按照逗号分隔的,字段之间以逗号分隔,生成 500 万条用户行 和随机订单行。

依然是用 Python 脚本生成文件,生成文件的过程耗时 779 秒,12分钟左右。

image-20200804111127619

两个文件大小分别是 560 多M 和 900 M。

image-20200804112826430

image-20200804112826430

最后执行 load data infile 将文件导入到对应的表中,在执行这个命令后可能会出现下面这个错误提示。

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

这是因为 MySQL 自身的安全配置所致,需要更改 my.cnf,在其中加入下面的配置,然后重启服务。

secure_file_priv=
复制代码

等于号后边为空表示允许所有目录下的文件 load,如果要限定某个特定目录,在等于号后边填上对应的文件目录即可。

然后执行下面的语句,将用户记录导入到 user 表。

load data infile '/Users/fengzheng/知识管理/技术写作/mysql/创建测试数据/sql/load_user_txt_500w.txt' replace into table user FIELDS TERMINATED BY ',';
复制代码

500万条耗时 3分32秒。

image-20200804135355209

将订单记录导入到 order 表。

load data infile '/Users/fengzheng/知识管理/技术写作/mysql/创建测试数据/sql/load_order_txt_500w+.txt' replace into table `order` FIELDS TERMINATED BY ',';
复制代码

749 万条记录,耗时 8分31秒。

image-20200804140459790

image-20200804140459790

整个过程加起来 24 分钟左右。

最后

好了,现在可以愉快的做各种测试和优化了。

有同学看完可能要说了,20多分钟好像也不算快啊。因为数据量确实比较大,再有数据复杂度和导入时间也有很大关系,如果你只是导入一列自增id,别说 500 万,1000万都用不了一分钟就完成了。

其实还有一点优化空间的,比如说把数据库引擎改成 MYISAM 会更快一些,尤其是对于批量插入的情景,但是插入完成后还要再改回来,也需要耗费一些时间,而且来回切换也比较麻烦。

上面的几种方法都配合了 Python 脚本,当然你可以换成自己熟悉的语言,比如 Java,或者直接写 bash 脚本也可以。

脚本已经放到了 github 上,需要的同学请自取。地址:

点击获取源码


壮士且慢,先给点个赞吧,总是被白嫖,身体吃不消!

公众号「古时的风筝」,Java 开发者,全栈工程师,bug 杀手,擅长解决问题。 一个兼具深度与广度的程序员鼓励师,本打算写诗却写起了代码的田园码农!坚持原创干货输出,你可选择现在就关注我,或者看看历史文章再关注也不迟。长按二维码关注,跟我一起变优秀!

 

为什么说MySQL单表数据不要超过500万行 今天,探讨一个有趣的话题:MySQL 单表数据达到多少时才需要考虑分库分表?有人说 2000 万行,也有人说 500 万行。那么,你觉得这个数值多少才合适呢? 曾经在中国互联网技术圈广为流传着这么一个说法:MySQL 单表数据量大于 2000 万行,性能会明显下降。事实上,这个传闻据说最早起源于百度。具体情况大概是这样的,当年的 DBA 测试 MySQL性能时发现,当单表的量在 2000 万行量级的时候,SQL 操作的性能急剧下降,因此,结论由此而来。然后又据说百度的工程师流动到业界的其它公司,也带去了这个信息,所以,就在业界流传开这么一个说法。 再后来,阿里巴巴《Java 开发手册》提出单表 立即下载

相关推荐

使用JDBC在MySQL数据库中如何快速批量插入数据

使用JDBC连接MySQL数据库进行数据插入的时候,特别是大批量数据连续插入(10W+),如何提高效率呢? 在JDBC编程接口中Statement 有两个方法特别值得注意: void addBatch() throws SQLException Adds a set of parameters to this PreparedStatement object’s batch of commands. int[] executeBatch() throws SQLException Submits a batch of commands to the database for exec

MySQL生成百万条数据超详细步骤

本篇文章是关于MySQL生成百万条数据的全部过程,每一步都有具体的sql,并且每一步都有具体的注释,详细步骤如下: 1、创建数据库 CREATE DATABASE `test_bai` -- 创建数据库 USE `test_bai` -- 切换对应的数据库 2、创建表 CREATE TABLE `app_user`( `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键', `name` VARCHAR(50) DEFAULT '' COMMENT '用户名称',

weixin_47316183的博客 1万+

小米MIUI 12免REC解锁Root:Magisk Manager与Fastboot实战指南

本文详细介绍了小米MIUI 12免REC解锁Root的完整流程,涵盖Magisk Manager与Fastboot的实战操作。从解锁前的准备工作到提取boot.img、Magisk修补技巧,再到Fastboot刷入全流程,提供了常见问题解决方案和进阶优化建议,帮助用户安全高效地获取Root权限。

weixin_33698823的博客 228

MySQL一张表最多能存多少数据?

MySQL一张表中可以存放多少数据? 一个简单的测试 private static void insertDataDemo() { DButil dButil = new DButil(); myCon = dButil.getConnection(); try { int i = 0; while(1==1) { i++; String sql = "in...

promote的博客 2万+

基于ROS2系统开发机器人

以上是基于ROS 2开发机器人的基本流程和一个简单的发布者-订阅者示例。通过这个流程,你可以逐步扩展和优化你的机器人系统,实现更复杂的功能。

xuweilin的博客 1498

学术类PPT制作总结

本文分享了科技类PPT的制作技巧,希望相关的信息能帮助到读者。

奇妙水果的技术博客,欢迎交流技术~ 2888

Mysql单表数据500万是否一定要分库分表?

总是听人说单表500w一定要分库分表,上千万的数据查询一定慢,没有场景的前提,都是扯淡的,下面做个小测验。 数据库:mysql8.0.20 系统:win10 数据构建:数据构建地址 注:我将索引user_id去除了,添加了user_name和phone索引。 mysql配置如下: mysql配置全是安装后默认的,关键参数innodb_buffer_pool_size为128M.关于InnoDB缓冲池大小配置请参考官网:配置InnoDB缓冲池大小 数据条数:13442974 查询全部co

weixin_40757126的博客 3186

如何在n8n中高效调用playwright-mcp实现浏览器自动化(附避坑指南)

本文详细介绍了在n8n中高效集成playwright-mcp实现浏览器自动化的全流程指南,包括环境准备、工作流设计与优化、高级技巧与性能调优以及调试与问题排查。通过克服实际部署中的各种挑战,帮助用户打造稳定高效的自动化解决方案,特别适合需要浏览器自动化的工作流场景。

weixin_30379531的博客 388

三分钟搞定向MySQL插入百万级数据

三分钟搞定向MySQL插入百万级数据

工作、学习、生活分享 2897

WorkBuddy 连接百度网盘:能查能上传,但别指望 AI 帮你下载

本文介绍了如何通过WorkBuddy连接百度网盘实现文件管理功能。首先需要在百度开放平台创建应用并获取AppKey等凭证,然后通过拼接授权URL获取Access Token并填入WorkBuddy。实际测试表明,该连接器支持查看目录结构、搜索文件及上传文档,但无法实现文件下载和创建分享链接,原因是百度网盘MCP Server未提供相关接口。使用前需明确功能限制,该方案适合需要远程查阅网盘内容但不涉及下载的场景。

u011186532的专栏 2077

Linux系统中u-boot启动流程分析(详解)

代码部分,具体如下;

嵌入式技术开发 1993

java sql 写入万条数据_教你几招,快速创建 MySQL 五百万级数据,愉快的学习各种优化技巧

> 我是风筝,公众号「古时的风筝」,一个兼具深度与广度的程序员鼓励师,一个本打算写诗却写起了代码的田园码农! 文章会收录在 JavaNewBee 中,更有 Java 后端知识图谱,从小白到大牛要走的路都在里面。如果你打算好好学习一下 MySQL,性能优化肯定是绕不过去一个问题。当你撸起袖子准备开始的时候,突然发现一个问题摆在眼前,本地数据库中没那么大的数据量啊,几条数据优化个毛线啊。生产库...

weixin_39616339的博客 452

SAP 集成泛微 OA Open API 新建流程申请接口

《SAP ERP 与泛微 OA 的系统集成》系列文章。泛微 Open API 是基于 HTTPS 的 Restful 接口,上篇文章,我已经完成了调用业务接口之前的准备工作,这篇文章我以新建流程申请这个系统集成最重要的接口为例,讲解接口设计、泛微 API 调用的过程、遇到的问题、解决的办法。

SAP李斌,本人承诺所有原创文章均不使用任何AI生成技术,请大家放心阅读。 3558

mysql 两个字段拼接_教你几招,快速制造五百万级 MySQL 数据

如果你打算好好学习一下 MySQL,性能优化肯定是绕不过去一个问题。当你撸起袖子准备开始的时候,突然发现一个问题摆在眼前,本地数据库中没那么大的数据量啊,几条数据优化个毛线啊。生产库里数据多,但谁敢直接在生产环境动手啊,想被提前优化吗?要知道,程序员从不轻言放弃,没有数据我们就自己创造数据嘛,new 对象这种事情可是我们的拿手好戏,对象都能 new 出来,更别说几百万条数据了。使用官方数...

weixin_36254564的博客 340

100 道大数据开发技术原理与实战面试宝典(必过答案详细解析)I

在数字化浪潮席卷全球的今天,大数据开发工程师已成为企业数字化转型的核心力量。无论是互联网巨头的用户行为分析,还是金融机构的风险控制,亦或是智慧城市的数据中枢,都离不开大数据技术的支撑。然而,面对BAT等头部企业严格的面试要求,候选人不仅需要掌握扎实的理论基础,更需具备将技术落地的实战能力。本文基于历年高频面试题与企业真实场景需求,梳理100道必考题目,涵盖基础概念、框架原理、算法设计、性能优化等核心领域,助你构建完整的知识体系,从容应对面试挑战。

AI天才研究院 1393

WINCC7.3破解文件

WINCC7.3硬件够破解文件,有需要的下载!WINCC7.3硬件够破解文件,有需要的下载!

如何给mysql表添加百万条数据_给mysql一百万条数据的表添加索引

直接alter table add index 添加索引,执行一个小时没反应,并且会导致锁表;故放弃该办法,最终解决办法如下:一.打开mysql 命令行客户端这里我们那可以看到导出的数据文件所存放的默认位置 C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\二. 创建新表创建一张新表与要添加索引的表结构保持一致;CREATE TABLE 表名 LIKE...

weixin_39716160的博客 1355

文档:亲测可用解决vnc灰屏,黑屏,鼠标大黑叉情况

主要解决vnc-viewer灰屏,黑屏,鼠标大黑叉等现象,详细见文档。

MySQL单表数据不要超过500万行?真的假的?

今天,探讨一个有趣的话题:MySQL 单表数据达到多少时才需要考虑分库分表?有人说 2000 万行,也有人说 500 万行。那么,你觉得这个数值多少才合适呢?      曾经在中国互联网技术圈广为流传着这么一个说法:MySQL 单表数据量大于 2000 万行,性能会明显下降。事实上,这个传闻据说最早起源于百度。具体情况大概是这样的,当年的 DBA 测试 MySQL性能时发现,当单表的量在 2000 万行量级的时候,SQL 操作的性能急剧下降,因此,结论由此而来。然后又据说百度的工程师流动到业界的其.

梦~' 1537

计算机控制技术及应用

《计算机控制技术及应用》通过总结作者的科研成果和吸收国内外的先进理论、方法和技术,以计算机控制系统的网络化、开放化、智能化和集成化发展趋势为主线索,主要介绍计算机控制系统的基本概念和计算机控制系统的典型形式,计算机控制系统的数学描述与基本分析方法,以及数字PID控制器、最少拍控制系统、达林算法、状态变量设计法等设计方法及其各种改进算法。重点突出计算机控制系统输入输出通道接口的设计方法和典型控制系统的设计案例与实现方法等。

TMS_WebGMaps_1.9.8.0_Full_Source

TMS_WebGMaps_1.9.8.0_Full_Source 全源码 delphi cbuilder

从 MySQL 数据库里读取 500w 数据行进行处理,应该怎么做更效益化?

前言 由于现在 ORM 框架的成熟运用,很多小伙伴对于 JDBC 的概念有些薄弱,ORM 框架底层其实是通过 JDBC 操作的 DB JDBC(JavaDataBase Connectivity)是 Java 数据库连接, 说得直白点就是使用 Java 语言操作数据库 由 SUN 公司提供出一套访问数据库的规范 API, 并提供相对应的连接数据库协议标准, 然后 各厂商根据规范提供一套访问自家数据库的 API 接口 1 MySql JDBC 大数据量操作 整篇文章以大数据量操作为议题,通过开发过程

meser88的博客 1946

五百万数据 mysql count(*)优化

一、故事背景 有一张 500w 左右的表做 select count(*) 速度特别慢。 二、原 SQL 分析 Server version: 5.7.24-log MySQL Community Server (GPL) SQL 如下,仅仅就是统计 api_runtime_log 这张表的行数,一条简单的不能再简单的 SQL: select count(*) from api_runtime_log; 我们先去运行一下这条 SQL,可以看到确实运行很慢,要 40 多秒左右,确实很不正常~ mysql

MariaOzawa的博客 1万+

面向程序员的数据库访问性能优化法则

<br />面向程序员的数据库访问性能优化法则<br /> <br />特别说明:<br />1、  本文只是面对数据库应用开发的程序员,不适合专业DBA,DBA在数据库性能优化方面需要了解更多的知识;<br />2、  本文许多示例及概念是基于Oracle数据库描述,对于其它关系型数据库也可以参考,但许多观点不适合于KV数据库或内存数据库或者是基于SSD技术的数据库;<br />3、  本文未深入数据库优化中最核心的执行计划分析技术。<br /> <br />读者对像:<br />开发人员:如果你是做数据

MKing's Blog 13万+

[图解MySQL]MySQL组提交(group commit)

2019独角兽企业重金招聘Python工程师标准>>> ...

weixin_34272308的博客 277

java 依次单条数据 写入数据库 500万条

不多说废话,数据库采用SQLServer 2008 数据库名TestDB  字段 P17173_Name nvarchar(50) P17173_MD5Key nvarchar(50) P17173_SecurityEmail nvarchar(100) P17173_ICPassCard nvarchar(50) 从一个超级大的txt中读取数据写入到DB中。秒懂

chenqiangdage的专栏 3008
上一篇: 如何对 ElasticSearch 集群进行压力测试
下一篇: 不能错过的分布式ID生成器(Leaf ),好用的一批!
java架构师小芷
博客等级 码龄6年 216粉丝 519原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值