mysql大批量数据插入技巧【百科全说】

最近在做MySQL大批量数据的测试，就简单总结一下遇到的问题

操作方法

01

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

这是插入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. 手动使用事物；
03

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

可以明显的看到插入百万数据是100S左右，速度提升了33倍之多。速度是提升了不少，那就加大插入的数据量，提升10倍，即插入千万的数据量，具体的SP如下：
05

可以看到时间差不多是1200s左右，因为字段加长了，可能也有影响插入的速度。为了具体验证，就按千万行插入，字段的长度为1000字节，来查看结果，具体的SP和结果如下：
06

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。
07

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

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

因为创建了索引，在查百万级的数据量时，时间是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，所占磁盘空间为下图

mysql大批量数据插入技巧

操作方法

相关推荐