MYSQL 大批量数据插入

首先我是简单的写了一个MYSQL的循环插入数据的SP，具体如下：

这是插入100W数据的过程和结果，可以看到是换了55min +20S约3320秒（约300rows/s）,看到之后我是只崩溃，就在网上查了些提速的方法：

0. 最快的当然是直接 copy 数据库表的数据文件（版本和平台最好要相同或相似）；
1. 设置 innodb_flush_log_at_trx_commit = 0 ，相对于 innodb_flush_log_at_trx_commit = 1 可以十分明显的提升导入速度；
2. 使用 load data local infile 提速明显；
3. 修改参数 bulk_insert_buffer_size， 调大批量插入的缓存；
4. 合并多条 insert 为一条: insert into t values(a,b,c),  (d,e,f) ,,,
5. 手动使用事物；

当数据量较大时，如上百万甚至上千万记录时，向MySQL数据库中导入数据通常是一个比较费时的过程。通常可以采取以下方法来加速这一过程：

一、对于Myisam类型的表，可以通过以下方式快速的导入大量的数据。 ALTER TABLE tblname DISABLE KEYS; loading the data ALTER TABLE tblname ENABLE KEYS; 这两个命令用来打开或者关闭Myisam表非唯一索引的更新。在导入大量的数据到一个非空的Myisam表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的Myisam表，默认就是先导入数据然后才创建索引的，所以不用进行设置。

二、对于Innodb类型的表，有以下几种方式可以提高导入的效率： ①因为Innodb类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果Innodb表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高导入数据的效率。

②在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

③如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

而我创建的是Innodb类型的表，分了128个分区。而我依照以上的方法，设置如下：

插入百万数据的SP如下：

可以明显的看到插入百万数据是100S左右，速度提升了33倍之多。

速度是提升了不少，那就加大插入的数据量，提升10倍，即插入千万的数据量，具体的SP如下：

可以看到时间差不多是1200s左右，因为字段加长了，可能也有影响插入的速度。

为了具体验证，就按千万行插入，字段的长度为1000字节，来查看结果，具体的SP和结果如下：

可以看到用时33min 51s月（约2031秒），即（4900row/s），速度下降很多，字符长度看来是用影响的。

varchar字段

字段的限制在字段定义的时候有以下规则： 
a) 存储限制 
varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度（长度超过255时需要2个字节），因此最大长度不能超过65535。 
b) 编码长度限制 
字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766; 
　　字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845。 
　　对于英文比较多的论坛 ，使用GBK则每个字符占用2个字节，而使用UTF－8英文却只占一个字节。 
　　若定义的时候超过上述限制，则varchar字段会被强行转为text类型，并产生warning。 
c) 行长度限制 
　　导致实际应用中varchar长度限制的是一个行定义的长度。 MySQL要求一个行的定义长度不能超过65535。若定义的表长度超过这个值，则提示 
　　ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to 　　　change some columns to TEXT or BLOBs。 
2、计算例子 
　　举两个例说明一下实际长度的计算。 
a) 若一个表只有一个varchar类型，如定义为 
create table t4(c varchar(N)) charset=gbk; 
则此处N的最大值为(65535-1-2)/2= 32766。 
减1的原因是实际行存储从第二个字节开始‘; 
减2的原因是varchar头部的2个字节表示长度; 
除2的原因是字符编码是gbk。 

b) 若一个表定义为 
create table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8; 
则此处N的最大值为 (65535-1-2-4-30*3)/3=21812 
减1和减2与上例相同; 
减4的原因是int类型的c占4个字节; 
减30*3的原因是char(30)占用90个字节，编码是utf8。 
如果被varchar超过上述的b规则，被强转成text类型，则每个字段占用定义长度为11字节，当然这已经不是“varchar”了。

在mysql 中用"SHOW VARIABLES LIKE ‘%CHAR%‘"查看字符集：

再次升级插入的数据量，提升10倍，看插入的时间及占用的内存，字段的字节同样为1000，具体的SP和结果如下：

从上图可以清楚的看到，插入1亿条数据的时间为5hours +20 min+ 56s=19256s，平均插入的条数为（5193 rows/s）。根上次插入1千万条的时间差不多，再看所耗磁盘空间，用了98G的空间，跟上次插入千万条数据时的（26G-17G=9G）也是成线性关系的。按照本机500G的磁盘空间，存储1行1K字节大小的数据，本机可以存储理想极限情况下为5亿条数据，保守为4~4.5亿左右合适，以防其他的应用或者数据库的UNDO，索引空间占用。

最后再看一次查询的时间，上次插入百万数，查询数据量的时间

因为创建了索引，在查百万级的数据量时，时间是1秒左右，在数据量上升到千万时，查询1亿5百万时，时间为3Min 30S,再插入1亿数据，查询数据量，时间达到27min 43s,可见，不是线性关系，是几何级增加的。

=====================================================================================================

现在描述集群环境的测试

集群：32G内存 ，500G硬盘，3台虚拟机也就是3个节点：188.188.2.181（主节点，数据节点和SQL节点）、188.188.2.182（数据节点和SQL节点）和188.188.2.183（数据节点和SQL节点）。/root目录分区磁盘空间200G（原先默认的是50G）、插入的数据量为8000KW，所占磁盘空间为下图

插入前的内存：

插入后内存：

数据内存所占空间：(910051-5)*32K=27.77G  -----300W条/G

索引内存所占空间：54980*8K=430M

插入前的磁盘空间：

插入后的磁盘空间：

磁盘空间：200G*（34%-5%）=58G    -----143W条/G

插入前的条数：

插入后的条数：

条数的：82551267条

MYSQL 大批量数据插入

标签：