MySQL中批量初始化数据的对比测试(r12笔记第71天)

一直以来对于MySQL的存储过程性能还是颇有微词的,说实话够慢的。有时候想做一些对比测试,存储过程初始化几万条数据都得好一会儿,这功夫Oracle类似的测试早都做完了,今天就赶个晚班车,把这个没做完的任务完成了。

我大体测试了一下,以100万数据为基准,初始化性能的提升会从近8分钟提升到10多秒钟。

我自己尝试了以下4种方案。

1.存储过程批量导入(近8分钟)

2.存储过程批量导入内存表,内存表导入目标表(近5分钟)

3.使用shell脚本生成数据,使用load data的方式导入数据(近20秒)

4.使用shell脚本生成数据,使用load data的方式导入内存表,内存表数据导入目标表(近18秒)

方案1:存储过程导入

我们测试使用的表为users,InnoDB存储引擎,计划初始化数据为100万。

create table users( userid int(11) unsigned not null, user_name varchar(64) default null, primary key(userid) )engine=innodb default charset=UTF8;

使用如下的方式来初始化数据,我们就使用存储过程的方式。

delimiter $$ drop procedure if exists proc_auto_insertdata$$ create procedure proc_auto_insertdata() begin declare init_data integer default 1; while init_data<=100000 do insert into users values(init_data,concat('user' ,init_data)); set init_data=init_data+1; end while; end$$ delimiter ; call proc_auto_insertdata();

因为我对这个过程还是信心不足,所以就抓取了十分之一的数据10万条数据,测试的结果是执行了47秒钟左右,按照这个数据量大概需要8分钟左右。 > source create_proc.sql Query OK, 0 rows affected, 1 warning (0.04 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 1 row affected (47.41 sec)

所以这个过程虽然是一步到位,但是性能还是差强人意,我看有 的同学在不同的配置下性能差别很大,有的同学达到了近50分钟。这一点上以自己的测试环境为准,然后能够得到一个梯度的数据就可以了。

我们来看看第二个方案。

方案2:使用内存表

第二个方案,我们尝试使用内存表来优化,这样一来我们就需要创建一个内存表,比如名叫users_memory。

create table users_memory( userid int(11) unsigned not null, user_name varchar(64) default null, primary key(userid) )engine=memory default charset=UTF8;

然后使用如下的存储过程来导入数据,其实逻辑和第一个存储过程几乎一样,就表名不一样而已,这个里面数据是入到内存表中。

delimiter $$ drop procedure if exists proc_auto_insertdata$$ create procedure proc_auto_insertdata() begin declare init_data integer default 1; while init_data<=1000000 do insert into users_memory values(init_data,concat('user' ,init_data)); set init_data=init_data+1; end while; end$$ delimiter ; call proc_auto_insertdata ;

这个过程可能会抛出table is full相关的信息,我们可以适当调整参数tmpdir(修改需要重启),max_heap_table_size(在线修改),然后重试基本就可以了。 > source create_proc_mem.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (4 min 40.23 sec)

这个过程用时近5分钟,剩下的内存表数据导入InnoDB表很快了,几秒钟即可搞定。 > insert into users select *from users_memory; 整个过程下来不到5分钟,和第一种方案相比快了很多。

方案3:使用程序/脚本生成数据,批量导入

第三种方案只是抛砖引玉,如果你对php熟悉,可以完全用php来写,对哪种语言脚本熟悉,只要实现需求即可。比如我使用shell,也没有使用什么特别的技巧。 shell脚本内容如下:

for i in {1..1000000} do echo $i,user_$i done > a.lst

脚本写得很简单,生成数据的过程大概耗时8秒钟,文件有18M左右。

# time sh a.sh real 0m8.366s user 0m6.312s sys 0m2.039s

然后使用load data来导入数据,整个过程花费时间大概在8秒钟左右,所以整个过程的时间在19秒以内。

> load data infile '/U01/testdata/a.lst' into table users fields terminated by ',' ; Query OK, 1000000 rows affected (8.05 sec) Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0

方案4:内存表,外部文件导入混合

第四种方案是临时想的,也是结合了这几种方案的一些特点,当然不能说它就是最好的。

首先使用脚本生成数据,还是和方案3一样,估算为9秒钟,导入数据到内存表users_memory里面。

> load data infile '/U01/testdata/a.lst' into table users_memory fields terminated by ',' ; Query OK, 1000000 rows affected (1.91 sec) Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0

然后把内存表的数据导入目标表users

> insert into users select *from users_memory; Query OK, 1000000 rows affected (7.48 sec) Records: 1000000 Duplicates: 0 Warnings: 0

整个过程耗时在18秒,和第三种方案很相似,看起来略微复杂了或者啰嗦了一些。

以上几种方案只是个人的一些简单测试总结,如果你有好的方案,希望多提意见,多多沟通。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-05-21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java技术栈

关系型数据的分布式处理系统:Cobar

Cobar简介 Cobar是关系型数据的分布式处理系统,它可以在分布式的环境下像传统数据库一样为您提供海量数据服务。 Github:https://github...

3878
来自专栏Laoqi's Linux运维专列

MySQL性能调优 – 你必须了解的15个重要变量

2.3K2
来自专栏张戈的专栏

实现WordPress提交评论的时删除该页面的WP-Super-Cache缓存的方法

这篇文章依然是围绕缓存问题展开的,lnmp 环境下的 WordPress 使用 WP-Super-Cache 插件,若要开启 mod_rewrite 缓存模式,...

35412
来自专栏Java帮帮-微信公众号-技术文章全总结

Sharding-JDBC—分库分表实例【面试+工作】

Sharding-JDBC是一个开源的适用于微服务的分布式数据访问基础类库,它始终以云原生的基础开发套件为目标。

4442
来自专栏专注于主流技术和业务

Selenium在Windows7上的系统搭建

Selenium自动化浏览器。就是这样!你用那种方式做什么完全取决于你自己。首先,它是为了测试目的自动化web应用程序,但肯定不限于此。无聊的基于web的管理任...

1392
来自专栏L宝宝聊IT

MySQL架构组成、逻辑模块组成

Mysql逻辑结构可以看成是二层架构,第一层通常叫做SQL Layer,在mysql数据库系统处理底层数据之前的所有工作都在这一层完成的,包括权...

903
来自专栏闵开慧

mysql性能调优

mysql调优思路: 1.数据库设计与规划--以后再修该很麻烦,估计数据量,使用什么存储引擎  2.数据的应用--怎样取数据,sql语句的优化  3.mysql...

3865
来自专栏哲学驱动设计

MIS性能优化常见问题与方案(辅助项目组性能优化的总结贴)

最近帮忙公司的几个项目组进行了不同方面的性能优化,发现几个项目都出现了一些共性的问题。这里写一篇文章,总结一下这几类问题,以及其对应的解决方案。方便其它项目组参...

2286
来自专栏cs

知识点找回2.0

Servlet(server Applet),全称Java Servlet, 是用java编写的服务器端程序。而这些Servlet都要实现Servlet的这个借...

841
来自专栏Linyb极客之路

MySQL 之 SQL 优化实战记录

N个机台将业务数据发送至服务器,服务器程序将数据入库至MySQL数据库。服务器中的javaweb程序将数据展示到网页上供用户查看。

1211

扫码关注云+社区

领取腾讯云代金券