Mysql批量插入更新性能优化

Mysql批量插入更新性能优化

---

对于数据量较大的插入和更新，因io/cpu等性能瓶颈，会产生大量的时间消耗，目前主流的优化主要包括预编译、单条sql插入多条数据、事务插入等，下面详细介绍一下：

单条插入（Mybatis）

        INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) VALUES
        (${cityCode}, ${cityName}, ${provinceName}, ${alias}, ${abbrePy})

单条预编译插入（Mybatis）

采用预编译可以节约mysql服务的解析时间，mytatis中就是采用#变量

        INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) VALUES
        (#{cityCode}, #{cityName}, #{provinceName}, #{alias}, #{abbrePy})

单条sql插入多条数据

即是拼接sql，在一个sql中插入多条或更新多条数据。

        INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) VALUES
        ("cityCode1", "cityName1", "provinceName1" "alias1", "abbrePy1"),("cityCode2", "cityName2", "provinceName2" "alias2", "abbrePy2")

快的原因

1. 合并后日志量（MySQL的binlog和innodb的事务让日志）减少了，降低日志刷盘的数据量和频率，从而提高效率；
2. 通过合并SQL语句，减少网络传输的IO；
3. 通过合并SQL语句，减少SQL语句解析的次数；

注意事项

1. 数据库sql长度是有限制，sql长度别溢出，会报错；
2. 乱序插入时候速度超过innodb_buffer的容量，每次定位索引涉及较多的磁盘读写操作，性能下降较快；

事务插入

事务插入即在插入前开启事务，插入结束关闭事务进行提交即可。

快的原因

1. 进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗；

注意事项

1. 事务不能过大，MySQL有innodb_log_buffer_size配置项，事务超出这个时，会刷磁盘，导致性能下降；
2. 乱序插入时候速度超过innodb_buffer的容量，每次定位索引涉及较多的磁盘读写操作，性能下降较快；

测试结果
环境：i5-4200U 1.6GHZ，12G内存，固态硬盘

\	:单条插入:	:单条预编译:	:单条插入多条:	:事务插入:
1000	4600毫秒	3334毫秒	8毫秒	704毫秒
10000	27204毫秒	26249毫秒	2959毫秒	2959毫秒
100000	240954毫秒	254716毫秒	17286毫秒	20539毫秒

总结
采用合并sql+事务插入组合，效率最高，乱序插入时候速度超过innodb_buffer的容量，每次定位索引涉及较多的磁盘读写操作，性能下降较快；尽量采用非乱序方式即可