首先mysql8号称性能是mysql5的2倍以上，并且一直听讲单表500万数据是一个瓶颈，那么我打算在最新版的mysql上导入一个1000万数据，验证看看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');

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

图1:生成sql文件

图2:1千万条数据

这个文本文件比较大，在mac上有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, --password[=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其实还是可以扛住的。

方案四：利用kettle导入千万级数据

环境准备

在虚拟机中安装好mysql8.0.19，存储引擎选择innoDB，并新增一个数据库，并创建t_user表，为简单起见，就2个字段，id和name；为减少网络可能存在问题。不需要建立索引，毕竟导入大数据，有索引的话，还需而外维护，那性能更慢。

create table t_user(id int, name varchar(20));

可到官网下载KETTLE，国内网站只更新到8.0，我下了最新版本《pdi-ce-9.0.0.0-423.zip》，使用方法一样，解压就能用，当然它是java编写的，所以需要jdk支持，测试过，只支持jdk1.8，jdk11以上不支持。所以我改了我本机的jdk环境。

具体操作

• kettle可以放在某个本机目录下，然后解压如图所示

kettle启动，windows下执行spoon.bat,mac下执行spoon.sh,spoon是kettle的图形化界面。

我这是mac机器，所以执行以下命令

./spoon.sh

正常的话就能看到启动页面

• 新建一个转换。

在输入选项里面选择文本输入，文本输入可以理解为数据源，把这个数据源导入到mysql当中，文本文件数据类似下图，有1000万条数据，我是用程序自动生成。简单起见，只有id和name字段。

拖拽文本输入控件，并做一些简单配置，因为我这txt文件用了逗号分隔，所以只要再内容选项中设置分隔符，其余保持默认就好了

在字段选项生成字段

• 新增一个表输出，此项就是需要把数据导入的目标。因为我们需要导入到mysql当中，所以需要把mysql驱动导入到kettle的安装目录的lib包下，需要重启kettle。

配置数据源，选择mysql，配置比较简单

最后，在文本输入和表输出之间建立一个关联关系，有个专门的术语-跳，表示数据的流向关系，从文本获取数据源，传到数据库表中。大概的配置就完成了，之后再执行前，做一些优化工作，提高性能。

优化建议

在db连接选项中配置参数，目的是使用批量插入的方式，并且利用压缩数据方式，提高传递到服务器的效率

useServerPrepStmts=false

rewriteBatchedStatements=true

useCompression=true

因为是大批量数据迁移,主要影响抽取速度的是表输出,因此目标数据数据库我开了10个线程进行写入。这里注意，我文本输入1000万数据都在一个文本中，不需要开线程，否则会重复读取数据，当然可以建立多个输入，这样可以重复利用cpu。

这样就可以执行数据导入了。

最终结果每秒读取11万条数据，最终完成1000万数据导入，2分钟不到。效率同mysqlimport命令，当然理论来说，jdbc效率是没有原生导入命令高的，所以不要指望kettle按秒来导入。