mysql 批量插入为什么可以提升性能?

王大爷 2021年09月10日 438次浏览
  • 数据库的一个插入动作,包含了连接,传输,执行,提交/回滚 等等的动作,在 执行的时候可能还会遇到锁表,等待等等,所以,批量插比逐个插效率高,是大部分情况,而不是绝对情况大部分情况下,批量插和逐个插,在执行层面,耗时接近;而不用多次连接数据库,在数据传输层面,也是一次性传输效率高(网络传输和这个模型类似,也有很多前置后置过程),而提交,也是只发起了一次,因而显得效率高在特殊情况下,比如插入的数据是业务表,每行的数据量较大,且该表访问频繁,那么,插入1条的时候可能不会锁表,而插入10000行的时候,会遇到锁表的情况。如果并发插入,甚至发生死锁。这个时候,就要根据经验分析,调整每个批次的量,以避免影响使用

  • 减少网络io开销,多行数据放入一个事务并行处理

  • 数据插入性能(单个插入和批量插入)

public class Test {  

        private Long id;  

        private String test;  

        public Long getId() {  
            return id;  
        }  

        public void setId(Long id) {  
            this.id = id;  
        }  

        public String getTest() {  
            return test;  
        }  

        public void setTest(String test) {  
            this.test = test;  
        }  
    }

mapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?>  
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >  
    <mapper namespace="com.astrospace.test.mapper.TestMapper" >  
        <resultMap id="test" type="com.astrospace.test.dmo.Test" >  
            <id property="id" column="ID"/>  
            <result property="test" column="TEST" />  
        </resultMap>  

        <insert id="add" parameterType="com.astrospace.test.dmo.Test">  
            INSERT INTO TEST(ID,TEST) VALUES(#{id},#{test});  
        </insert>  

        <insert id="batchAdd" parameterType="java.util.List">  
            INSERT INTO TEST(ID,TEST)  
            VALUES  
            <foreach collection="list" item="item" index="index" separator="," >  
                (#{item.id},#{item.test})  
            </foreach>  
        </insert>  

    </mapper>

调用add和batchAdd方法即可。

不同数据量测试5次,结果如下:

单独插入50000条数据平均耗时:233748ms
批量插入50000条数据平均耗时:2590ms
对比:效率差50倍
单独插入10000条数据平均耗时:22036ms
批量插入10000条数据平均耗时:3330ms
对比:效率差6倍
单独插入1000条数据平均耗时:3122ms
批量插入1000条数据平均耗时:374ms
对比:效率差8倍

数据越多,效率愈发明显。