Mysql单sql更新操作和批sql更新操作优缺点对比

背景

和数据库打交道应该是我们程序员日常工作中最经常遇到的问题了，而其中在循环中进行sql操作也是常常遇到的常见问题，本文就来简单总结下单sql更新操作和批sql更新操作的优缺点

单sql更新操作 VS 批sql更新操作

首先看下他们的tps性能：
单sql更新操作：

1.insert into table value ()
2.update table set field=xx where id=xx

批sql更新操作：

1.insert into table values (),(),(),()
2.update table 
<trim prefix="set" suffixOverrides=",">
    <trim prefix="field1 = case " suffix=" end, ">
        <foreach collection="list" item="item">
                when id = #{item.id} then #{item.field1}
        </foreach>
    </trim>
    <trim prefix="field2 = case " suffix=" end, ">
        <foreach collection="list" item="item">
                when id = #{item.id} then #{item.field2}
        </foreach>
    </trim>
</trim>
where id in
<foreach collection="list" item="item" open="(" close=")" separator=",">
    #{item.id}
</foreach>

形如上面的就是常见的单sql更新语句和批量sql更新语句，
一般单sql更新操作的性能能达到8k的tps，而批量sql更新操作的性能和每一批的数量有关，比如每一批操作100条记录，那么批量sql的tps大约可以达到80左右.
从应用的耗时角度来对比
显然1次批量sql更新相比N次单sql更新会节约N倍的网络往返耗时，这对于节省应用的耗时有较大的帮助。
从sql执行耗时来对比
单sql更新操作很快，是一个小的事务操作，对于操作db来说很快的可以释放db连接，而对于批量更新sql来说，这是相对更耗时的sql语句，是一个大的事务，这样会导致持有的db连接需要较长时间才能释放.
从数据库Mysql来对比
对于mysql来说，自然它不喜欢批量更新sql，因为这是个大事务，我们都知道大事务会导致主从复制延迟， Undo log日志膨胀，锁占用时间过长等问题，所以站在mysql数据库的角度，它不希望遇到大的事务

彩蛋：
为什么大事务会导致主从复制延迟？–这是使用最新的Mysql5.7以上版本
在主库和从库性能一样的前提下，更新同一个sql应该时间一样啊，为什么说会导致主从复制延迟呢？其实这和我们选择的从库的同步方式有关，我们从库一般使用的Row的方式同步主库的数据，也就是主库执行一条更新语句后所有的记录变化会同步到从库，然后从库把这些记录变化应用到从库上，只是说这个效果和从库直接执行更新语句的效果是一样的，但是其实从库并不是通过执行和主库一样的sql来进行数据同步的,所以自然主库更新的记录越多，从库同步延迟就越大.