教你几招,快速创建 MySQL 五百万级数据,愉快的学习各种优化技巧
source link: https://zhuanlan.zhihu.com/p/171732966
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
教你几招,快速创建 MySQL 五百万级数据,愉快的学习各种优化技巧
> 我是风筝,公众号「古时的风筝」,一个兼具深度与广度的程序员鼓励师,一个本打算写诗却写起了代码的田园码农! 文章会收录在 JavaNewBee 中,更有 Java 后端知识图谱,从小白到大牛要走的路都在里面。
如果你打算好好学习一下 MySQL,性能优化肯定是绕不过去一个问题。当你撸起袖子准备开始的时候,突然发现一个问题摆在眼前,本地数据库中没那么大的数据量啊,几条数据优化个毛线啊。生产库里数据多,但谁敢直接在生产环境动手啊,想被提前优化吗?
要知道,程序员从不轻言放弃,没有数据我们就自己创造数据嘛,new 对象这种事情可是我们的拿手好戏,对象都能 new 出来,更别说几百万条数据了。
创建百万级数据的方式,要到达的目的有两点:
- 定制比较灵活,不能只是一两个字段了事,那没什么实际意义。
- 速度快,不能说弄个几百万数据好几个小时甚至更长,那不能接收。
本次目标是创建两个表,一个用户表,另外一个订单表,当然没有真实环境中的表字段那么多,但是对于学习测试来说差不多够了。
两个表的表结构如下:
# 用户表
CREATE TABLE `user` (
`id` varchar(36) NOT NULL,
`user_name` varchar(12) DEFAULT NULL,
`age` tinyint(3) DEFAULT NULL,
`phone` varchar(11) DEFAULT NULL,
`province` varchar(10) DEFAULT NULL,
`city` varchar(10) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 订单表
CREATE TABLE `order` (
`id` varchar(36) NOT NULL,
`user_id` varchar(36) DEFAULT NULL,
`product_count` int(11) DEFAULT NULL,
`price` decimal(10,0) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
用户表(user)创建 500 万条数据,id 使用 uuid,年龄从 1 到 120 随机,电话号码随机 11 位,省份编码和城市编码随机,创建时间和更新时间在某一时间范围内随机。
订单表(order)根据用户表生成,每个用户随机生成 0 到 3 个订单,订单编号采用 uuid,商品数量随机 1 到 5 个,价格随机,创建时间和更新时间在某一时间段内随机。由于每个用户产生 0 到 3 个订单,所以,产生的订单量应该大于 500 万,我在本地跑的时候基本上在 700多万左右。
创建总时间和表的字段个数以及字段的生成算法有直接关系,字段越多、算法越复杂,需要的时间就越多,比如使用 uuid 就比使用自增 id 花费更长时间,随机时间就比直接使用当前时间花费更长时间。
如果只插入 500 万自增 id 这一个字段,十几秒就能完成,但是无论是模拟线上环境还是自学性能优化技巧都没什么意义。
下面就来介绍三种方式来快速创建 500 万用户数据以及大于 500 万的订单数据。
方式一:使用官方数据
官方显然知道我们需要一些测试数据做个练习什么的,所以准备了一份测试数据给我们。可以到 https://github.com/datacharmer/test_db 上去下载,这个数据库包含约30万条员工记录和280万个薪水条目,文件大小为 167 M。
下载完成之后,直接使用 MySQL 客户端运行 sql 文件即可。
或者直接使用命令,然后输入密码导入。
mysql -u root -p < employees.sql
这是最简单的一种方法,只要你能把 sql 文件下载下来就可以了。但是数据量不够大,员工表才 30 万条数据,还不够百万级别,而且字段都是定义好的,不能灵活定制。
方式二: 写程序批量插入
作为一个开发人员,当你打算创建百万条数据的时候,大多数时候首先相当的应该就是写程序,毕竟 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分钟。
最后成功生成用户记录 500 万条,订单记录 749 万多条。
速度还算能接受吧,马马虎虎吧。
再想速度快一点,可以开多线程,我用 5 个线程跑了一下,一个线程插入 100万条,最终最长的线程耗时 1294秒,21分钟,也没快多少,线程个数对时间多少有些影响,但是我没有试。
方式三: 生成 SQL 脚本
这种方式和上面的方式类似,只不过上面通过程序方式直接将拼接出来的 SQL 语句执行了,而这种方式是将拼接好的 SQL 语句写入文件中。当然还是以一条语句插入多行记录的形式。
insert into `table_name` (id,column1) values (1,'value1'),(2,'value2'),(3,'value3');
写 500 万用户数据,加上随机的订单数据, sql 文件的过程耗时为 696 秒,11分钟左右。
当然这么大数据量拼接出来的脚本文件也很大,用户表脚本 680 多M,订单表脚本 1个G。
最后将写好的这两个文件分别在 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分钟左右。
两个文件大小分别是 560 多M 和 900 M。
最后执行 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秒。
将订单记录导入到 order 表。
load data infile '/Users/fengzheng/知识管理/技术写作/mysql/创建测试数据/sql/load_order_txt_500w+.txt' replace into table `order` FIELDS TERMINATED BY ',';
749 万条记录,耗时 8分31秒。
整个过程加起来 24 分钟左右。
好了,现在可以愉快的做各种测试和优化了。
有同学看完可能要说了,20多分钟好像也不算快啊。因为数据量确实比较大,再有数据复杂度和导入时间也有很大关系,如果你只是导入一列自增id,别说 500 万,1000万都用不了一分钟就完成了。
其实还有一点优化空间的,比如说把数据库引擎改成 MYISAM 会更快一些,尤其是对于批量插入的情景,但是插入完成后还要再改回来,也需要耗费一些时间,而且来回切换也比较麻烦。
上面的几种方法都配合了 Python 脚本,当然你可以换成自己熟悉的语言,比如 Java,或者直接写 bash 脚本也可以。
获取脚本源码
发图不发种...,是不可能的 ,怎么能干这种事儿
脚本已经放到了 github 上,需要的同学请自取。地址:
Recommend
-
75
如果你车失控了,必须在撞死路人赔五十万、撞豪车赔五百万中二选一,该怎么选择? - 知乎登录后你可以不限量看优质回答私信答主深度交流精彩内容一键收藏
-
23
-
51
【价值上亿的3000㎡明代老宅】山西平遥人耿保国,做漆器有55年了。20年前,在一次拍卖会上,他用半生积蓄和借来的100万,买下了家乡一座有400年历史的明清建筑,作为自己的工作室和私宅。之后,他一边做漆器,一边修复房子,修了有20年,投入了四、五...
-
47
雷锋网(公众号:雷锋网)按:本文为雷锋字幕组编译的技术博客,原标题 Taming LSTMs: Variable-sized mini-batches and why PyTorch is good for your health,作者为 William Falcon 。 翻译 | 赵朋飞 马力群 涂世文 ...
-
30
-
10
教你几招泡面的正确吃法,总有你不会的小技巧_哔哩哔哩_bilibili 教你几招泡面的正确吃法,总有你不会的小技巧
-
4
Win11始终不够好用?教你几招让Win11变得更顺手 2022-02-03 00:15 出处:PConline原创 作者:幼儿园里的轩爸 ...
-
7
Windows 11自带播放器其实超好用!教你几招玩转它-51CTO.COM Windows 11自带播放器其实超好用!教你几招玩转它 作者:Aimo 2022-03-13 09:03:43 Windows 11自带播放器的体验也并非完美,下面笔者...
-
3
[PConline 技巧]很多朋友还保留着下载视频再播放的习惯,一款出色的本地视频播放器仍然必不可少。无论是PotPlayer这样的专业播放器,还是暴风影音这样的老牌流行软件,大家都已经耳熟能详了,但鲜为人知的是,装机率极高的系统自带视频播...
-
4
在竞价推广中,你是否经常遇见以下问题: 不清楚账户搭建的技巧,导致推广过程中问题百出? 制定账户推广策略把握不住核心点? 对数据查看与分析不了解,无从下手?
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK