温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

怎样进行MySQL中的批量初始化数据的对比测试

发布时间:2021-11-16 11:50:34 来源:亿速云 阅读:153 作者:柒染 栏目:MySQL数据库

本篇文章为大家展示了怎样进行MySQL中的批量初始化数据的对比测试,内容简明扼要并且容易理解,绝对能使你眼前一亮,通过这篇文章的详细介绍希望你能有所收获。

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

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

方案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秒,和第三种方案很相似,看起来略微复杂了或者啰嗦了一些。

上述内容就是怎样进行MySQL中的批量初始化数据的对比测试,你们学到知识或技能了吗?如果还想学到更多技能或者丰富自己的知识储备,欢迎关注亿速云行业资讯频道。

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI