MySQL批量插入和单条插入的性能

一、批量插入的实现方式

有两种实现方式：

1、开启一次数据库连接，在业务逻辑层用for循环，一条一条插入，关闭连接

<!-- 在外部for循环调用1000次 -->
<insert id="insert" parameterType="com.xxp.mybatis.Person">
    insert into person (id, name,sex,address)
    values 
    (#{id,jdbcType=INTEGER},#{name,jdbcType=VARCHAR},
    #{sex,jdbcType=VARCHAR},#{address,jdbcType=VARCHAR})
</insert>

2、sql语句：一次插入多条，例如下方sql：

 insert into table (id, name,sex,address)
 values
 (?,?,?,?),(?,?,?,?),(?,?,?,?),(?,?,?,?)

用mybatis实现就是：

<insert id="insertBatch" >
    insert into person (name,sex,address) 
    values 
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.name},#{item.sex},#{item.address})
    </foreach>
</insert>

二、由批量插入和单条插入的性能引起的反思

批量插入比一条一条的插入性能高原因如下：

• 批量插入SQL执行效率高的主要原因是合并后日志量（MySQL的binlog和innodb的事务让日志）减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。

ps：对于innodb，所有未提交的的二进制日志会被记录到一个缓存中去，等该事物提交时，直接将缓存中的二进制日志写入二进制日志文件中去，该缓存的大小由 binlog_cache_size决定，二进制日志记录了对MYSQL数据库执行更改的所有操作，但是不包括select和show这类操作，因为这类操作对数据本身并没有修改，二进制日志的功能：恢复，复制，审计。开启二进制当然会对性能有所影响，二进制日志并不是每次写的时候都同步到磁盘，参数sync_binlog表示多少次事物会从到磁盘中，如果是批量插入，表示只有1个事物

Mysql中常见的日志文件有：

• 错误日志：对MySQL的启动，运行，关闭过程进行了记录

• 二进制日志：j记录了对mysql数据库执行更改的所有操作，不包括select和show这种

• 慢查询日志：可以在mysql启动时，设一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中

• 查询日志：记录了所有对mysql的请求信息

innodb存储引擎中的文件：

• redolog

• undolog

• 表空间文件：innodb采用将存储的数据按表空间进行存放

Undo日志记录某数据被修改前的值，可以用来在事务失败时进行rollback；Redo日志记录某数据块被修改后的值，可以用来恢复未写入data file的已成功事务更新的数据。下面的示例来自于杨传辉《大数据分布式存储系统 原理解析与架构实践》，略作改动。

• 例如某一事务的事务序号为T1，其对数据X进行修改，设X的原值是5，修改后的值为15，那么Undo日志为<T1, X, 5>，Redo日志为<T1, X, 15>。

二进制日志文件和redolog的区别：

• 二进制日志会记录所有与mysql数据库有关的日志记录，包括其他存储引擎的日志，而redolog只记录innodb的

• 二进制日志记录的是一个事物的具体操作内容，如update t set a=1，而redolog记录的是物理改变，如上述例子

• 写入时间不同，二进制日志文件尽在事物提交前进行提交，而在事物进行的过程中，却不断的有重做日志被写入到重做日志文件中（因为重做日志记录的是改变，因此是在事物执行过程中记录每一个语句的改变，而二进制日志只是记录操作语句）