Mysql千万数据插入优化

批量向MySQL导入数据

1. 直接导入
2. 使用存储过程循环拼接
3. 使用load data infile
4. 修改ENGINE=InnoDB为MyISAM（v5.1之前是MyISAM，之后是InnoDB）
5. 减少IO次数
6. SQL写法优化（一条SQL语句插入多条数据）
7. 合理设置批量大小（在事务中进行插入处理，不要一条数据提交一次，肯定要分批处理）
8. 尽量顺序插入（减少索引的维护压力）
9. 合并事务+事务+有序数据的优化插入方式
10. 注意SQL批量插入的大小必须合理
11. 事务执行时间不要太长
12. 合理设置MySQL相应配置参数，增加缓存或减少不必要日志磁盘读写

合并SQL语句

一条SQL插入一条数据

insert into tb1(id,name,age,sex) values(1,'khue',25,'m');
insert into tb1(id,name,age,sex) values(2,'green',24,'w');
12

一条SQL插入多条数据

insert into tb1(id,name,age,sex) values(1,'khue',25,'m'),(2,'green',24,'w');
1

合并SQL语句能够提高程序的插入效率（原因在于合并后日志量减少了，降低日志刷盘的数据量和频率），也能减少SQL语句解析的次数，减少网络传输的IO

在事务中插入处理

将插入修改为

start transaction;
insert into tb1(id,name,age,sex) values(1,'khue',25,'m');
insert into tb1(id,name,age,sex) values(2,'green',24,'w');
commit;
1234

在事务中可以提高数据的插入效率，因为在进行一个insert操作时，MySQL内部会建立一个事务，在事务内才能进行真正的插入处理操作，这样可以减少创建事务的消耗，让所有插入都在执行后才进行提交操作

数据有序插入

数据有序插入是指插入记录在主键上是有序排列
无序插入：

insert into tb1(id,name,age,sex) values(3,'joo',26,'m');
insert into tb1(id,name,age,sex) values(5,'green'25,'w');
insert into tb1(id,name,age,sex) values(1,'khue',25,'m');
123

有序插入：

insert into tb1(id,name,age,sex) values(1,'khue',25,'m');
insert into tb1(id,name,age,sex) values(3,'joo',26,'m');
insert into tb1(id,name,age,sex) values(5,'green'25,'w');
123

由于数据库在插入时，需要维护索引数据，无序的记录会增大维护索引的成本（参照innodb使用的B+tree索引，如果每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小；如果插入的记录在索引中间，则需要B+tree进行分裂合并等操作，会消耗比较多的计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作）

测试

合并数据+事务的方法在较小数据量时，性能提高很明显；数据量较大时（1000万以上），性能急剧下降，因为此时数据量超过了innodb_buffer的容量，每次定位索引涉及较多的磁盘读写操作；使用合并数据+事务+有序数据则表现良好，因为有序数据索引定位较为方便，无需频繁对磁盘进行读写操作

注意：

1. SQL语句是有长度限制的，在合并数据时，不能超过SQL长度限制（通过max_allowed_packet配置可以修改，默认1M）
2. 事务也需要控制大小，事务过大会影响执行效率（MySQL有innodb_log_buffer_size配置项，超过值会把innodb数据刷到磁盘，此时效率会有所下降）