mysql千万级数据量插入的几种方案耗时，看完就知道如何选择

首先8号称性能是mysql5的2倍以上，并且一直听讲单表500万数据是一个瓶颈，那么我打算在最新版的mysql上导入一个1000万数据，验证看看mysql对于单表大数据的表现情况。

mysql数据导入测试

环境准备

  • 在虚拟机中安装好mysql8.0.17，存储引擎选择innoDB，并新增一个study数据库，并创建t_user表，为简单起见，就2个字段，id和name；为减少网络可能存在问题，执行的sql放到本地执行。不需要建立索引，毕竟导入大数据，有索引的话，还需而外维护，那性能更慢。
create table t_user(id int, name varchar(20));
  • 虚拟机配置调整，为充分使用虚拟机的性能，调整虚机配置，4个cpu，4g，10g的数据盘。

 

由于是固态盘，相信更多的瓶颈在cpu和内存。限于配置，也只能评估个大概耗时。

生成sql测试文件

拼这个sql很简单，随便用什么语言，只要生产sql文件就行，单条的语句为：

insert into t_user values('1','程序不就是0和1');

这里我使用来生成sql文件，1000万数据大概花了45秒左右，实现代码截图参考图1，结果参考图2

图1:生成sql文件


图2:1千万条数据

这个文本文件比较大，在上有580多M，光打开都要花一定的时间，所以可以想象，如果通过远程方法导入，对网络延时要求肯定很高。

方案1:存储过程导入

先来看看存储过程导入千万条数据耗时，存过就不需要文件了，直接在过程体中循环拼接insert语句即可。代码如下：

begin
DECLARE v_i int unsigned DEFAULT 0;
 WHILE v_i < 10000000 DO
 insert into t_user values(v_i,'程序不就是0和1');
 SET v_i = v_i+1;
 END WHILE;

存储过程耗时

总耗时大约3个小时3分钟，还是比较慢的。

方案2:用mysql命令导入

mysql命令是mysql自带的命令，位于bin目录下,该命令比较简单，为了避免网络延迟，我们先把用java生成的sql文件传到服务器上，使用rz命令进行上传到mysql的bin目录下，这样执行命令稍微简单点，输入用户名和密码即可执行。

这里有个地方需要注意下：因为sql文件较大，默认的话会报一个错误：ERROR 2006 (HY000): MySQL server has gone away，意思是记录数超过最大值。我们可以更改全局变量，只在本次mysql进程中有效，重启mysql就失效了，如果想一直生效，可以在my.cnf中配置。更改后，就可以执行导入命令了。

set global max_allowed_packet=1024*1024*1000;##设置为1G

上传sql文件到服务器

 ./mysql -uroot -p study1 < t_user.sql

总共耗时大约3小时8分钟，跟第一种方案区别不大。总结一下，对于大批量数据基本不能使用insert into 的方案，改用文本导入方案。所以我们重新生成数据文件。java代码更改如下：

 

使用制表符号作为数据分隔符，方便下面命令使用。

方案3:用mysqlimport命令导入

mysql从安全考虑，默认对这种导入文件的方式是关闭的，可以用该命令查询：

show variables like 'local_infile';

 

在全局环境中打开可以使用命令：

set global local_infile=ON;

如过没有打开此参数，而执行了mysqlimport命令会收到一个报错：ERROR 1148 (42000): The used command is not allowed with this MySQL version。

mysqlimport命令的常用参数介绍：

  • --fields-terminated-by=字符串：设置字段间的分隔符，可以为单个或多个字符。默认值为制表符“t”，这就是上述我们生成文件时候故意使用制表符。
  • -L, --local：表示从客户端任意路径读取文件导入表中，未设置该选项时，默认只从datadir下同名数据库目录下读取文件导入。
  • -p, --pass[=name]：指定用户密码。
  • -u, --user=name：指定登入MySQL用户名。

我们现在执行下面命令执行导入：

 ./mysqlimport -uroot -p --local study2 t_user.txt

备注：

  1. 上面重新生成的数据文件（t_user.txt），同样先上传服务器。
  2. 文件名默认就是要导入的表名。
  3. 耗时大约1分18秒。

mysqlimport其实是load data infile 的命令行工具，理论讲它们速度应该是一样的，所以就不再演示load data infile 命令了。

数据有了，看看查询效率

因为默认没有索引，所以随便查一个数据，大约耗时14秒。

select * from t_user where id = 1

 

加上索引之后，耗时几乎为0。忽略不计

 

可见千万的单表数据在走索引情况下，mysql其实还是可以扛住的。

总结

直接看图吧

 

基本上在大数据导入的情况下，mysqlimport命令耗时可以忽略不计了。只不过使用该命令需要打开local_infile参数。

胜象大百科