MySql插入大量数据解决方案
一、循环插入
循环插入是比较常见的方式，即通过sql语句一条一条向数据库添加数据。
1、示例
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES( value1, value2,...valueN );
2、优点
方便简单
适用于单条或者少量数据插入的场景
3、缺点
对于大量数据插入的场景，效率慢
频繁的连接数据库，系统开销大，数据库服务压力较大
4、总结
循环插入不适用于大量数据插入的场景。
二、一条sql语句插入多条数据
对于大量数据插入的场景，可以将要插入的数据通过一条sql语句完成。
1、示例
INSERT INTO `table_name` (`id`, `name`) VALUES ('2', 'xiaohua');
将上边两条插入语句合并成一条：
INSERT INTO `table_name` (`id`, `name`) VALUES ('1', 'xiaoming'), ('2', 'xiaohua');
2、优点
减少SQL语句解析的次数
减少数据库连接的I/O开销
如果使用的是innodb数据库引擎，语句合并后，减少了MySql的binlog日志等，降低了日志刷盘的数据量和频率
3、测试结果
4、总结
批量插入要比一条条插入效率高很多。但是如果批量插入的数据量太大，MySQL可能报错：
Packets larger than max_allowed_packet are not allowed
这是由于MySQL数据库有一个系统参数max_allowed_packet，其默认值为1048576(1M)，可以查看并修改max_allowed_packet的值来解决：
（1）查看max_allowed_packet
show VARIABLES like '%max_allowed_packet%';
如果没有查到，如下图，说明没有设置，需要设置max_allowed_packet
（2）设置max_allowed_packet
修改此参数的方法是在mysql文件夹找到my.ini文件，在my.ini文件[mysqld]中添加一行：max_allowed_packet=16777216 重启MySQL，这样将可以导入不大于16M的数据了，当然这数值可以根据需要作调整。
（3）修改max_allowed_packet
如果已经设置了max_allowed_packet，修改的语句如下： set global max_allowed_packet = 1024*1024*1024
三、在事务中进行插入处理
1、示例
START TRANSACTION;
INSERT INTO `t_test1` (`content`) VALUES ('xiaoming');
INSERT INTO `t_test1` (`content`) VALUES ('xiaohua');
COMMIT;
2、测试结果
3、总结
使用事务可以提高数据的插入效率。因为进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作。
四、数据有序插入
数据有序的插入是指插入记录在主键上是有序排列，例如：
INSERT INTO `insert_table` (`id`, `name`) VALUES ('1', 'xiaoming');
INSERT INTO `insert_table` (`id`, `name`) VALUES ('3', 'xiaoming');
INSERT INTO `insert_table` (`id`, `name`) VALUES ('2', 'xiaoming');
修改成：
INSERT INTO `insert_table` (`id`, `name`) VALUES ('1', 'xiaoming');
INSERT INTO `insert_table` (`id`, `name`) VALUES ('2', 'xiaoming');
INSERT INTO `insert_table` (`id`, `name`) VALUES ('3', 'xiaoming');
2、测试结果
从测试结果来看，该优化方法的性能有所提高，但是提高并不是很明显。
3、总结
由于数据库插入时，需要维护索引数据，无序的记录会增大维护索引的成本。我们可以参照InnoDB使用的B+tree索引，如果每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小；如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作。
五、使用存储过程
1、建表
CREATE TABLE `t_test`(`id` BIGINT(20) not null AUTO_INCREMENT,`content` VARCHAR(255) DEFAULT null,PRIMARY KEY(`id`)) ENGINE=MYISAM DEFAULT CHARSET=utf8;
这里选择数据库引擎是MYISAM。
2、建立存储过程
因为要插入100万条数据，不可能通过一条条的insert去做，这里采用存储过程：
delimiter$$DROP PROCEDURE IF EXISTS proc_batch_insert;CREATE PROCEDURE proc_batch_insert()BEGINDECLARE i INT;SET i=1;WHILE i<1000000 DOINSERT INTO t_test(`content`)VALUES(SUBSTRING(MD5(RAND()) FROM 1 FOR 6));SET i=i+1;END WHILE;END$$delimiter;
调用存储过程：
CALL proc_batch_insert;
从下图可以看出插入100万条数据需要2284.025s，大约38分钟。
上边演示了插入100万条数据，使用的数据库引擎是MYISAM。
3、变更数据库引擎
删除上面插入的100万条数据：可参考删除大量数据。将MYISAM改为INNODB：（具体可参考如何查看并修改数据库引擎）
ALTER TABLE t_test ENGINE=INNODB;
重新调用插入100万数据的存储过程：
从上面看出插入大量数据时，采用MYISAM或者INNODB相差不大。
六、性能综合测试
1、注意事项
SQL语句是有长度限制，在进行数据合并在同一SQL中务必不能超过SQL长度限制，通过max_allowed_packet配置可以修改，默认是1M，测试时修改为8M。事务需要控制大小，事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，超过这个值会把innodb的数据刷到磁盘中，这时，效率会有所下降。所以比较好的做法是，在数据达到这个这个值前进行事务提交。
2、总结
从测试结果可以看到，合并数据+事务的方法在较小数据量时，性能提高是很明显的，数据量较大时（1千万以上），性能会急剧下降，这是由于此时数据量超过了innodb_buffer的容量，每次定位索引涉及较多的磁盘读写操作，性能下降较快。而使用合并数据+事务+有序数据的方式在数据量达到千万级以上表现依旧是良好，在数据量较大时，有序数据索引定位较为方便，不需要频繁对磁盘进行读写操作，所以可以维持较高的性能。
更多技术请关注