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

52次阅读

共计 2827 个字符，预计需要花费 8 分钟才能阅读完成。

本篇文章为大家展示了怎样进行 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 中的批量初始化数据的对比测试，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注丸趣 TV 行业资讯频道。

正文完