Efficiency comparison of three methods of batch updating data in mybatis

This paper discusses the efficiency of three writing methods of batch updating data.

There are three ways to implement it,

1> Use the for loop to loop out N sql through the parameter set passed through the loop,

2> Use the case when condition of mysql to judge the disguised for batch update (recommended)

3> Batch update with ON DUPLICATE KEY UPDATE

Note that for the first method to succeed, you need to take a parameter after the db link url & allowmultiqueries = true

Namely: JDBC: mysql://localhost:3306/mysqlTest?characterEncoding=utf -8&allowMultiQueries=true

    <!-- The first method of batch updating is to receive the transmitted parameters list The assembly cycle is going on sql -->
     <update id="updateBatch" parameterType="java.util.List" >
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            update standard_relation
            <set >
                <if test="item.standardFromUuid != null" >
                    standard_from_uuid = #{item.standardFromUuid,jdbcType=VARCHAR},
                <if test="item.standardToUuid != null" >
                    standard_to_uuid = #{item.standardToUuid,jdbcType=VARCHAR},
                <if test="item.gmtModified != null" >
                    gmt_modified = #{item.gmtModified,jdbcType=TIMESTAMP},
            where id = #{item.id,jdbcType=BIGINT}

    <!-- Batch update the second method, through case when Batch update in disguised form -->
    prefix:stay trim Inside label sql Statements are prefixed.
    suffix:stay trim Inside label sql Add a suffix to the statement.
    prefixOverrides:Specifies to remove redundant prefix content
    suffixOverrides:Specify to remove redundant suffix contents, such as: suffixOverrides=",",remove trim Inside label sql Statement redundant suffix",". 
    <update id="updateBatch" parameterType="java.util.List" >
        update standard_relation
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="standard_from_uuid =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.standardFromUuid!=null">
                        when id=#{i.id} then #{i.standardFromUuid}
            <trim prefix="standard_to_uuid =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.standardToUuid!=null">
                        when id=#{i.id} then #{i.standardToUuid}
            <trim prefix="gmt_modified =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.gmtModified!=null">
                        when id=#{i.id} then #{i.gmtModified}
        <foreach collection="list" separator="or" item="i" index="index" >

 <!-- Batch update the third method, using ON DUPLICATE KEY UPDATE  -->
 <insert id="updateBatch" parameterType="java.util.List">
        insert into standard_relation(id,relation_type, standard_from_uuid,
        standard_to_uuid, relation_score, stat,
        last_process_id, is_deleted, gmt_created,
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.id,jdbcType=BIGINT},#{item.relationType,jdbcType=VARCHAR}, #{item.standardFromUuid,jdbcType=VARCHAR},
            #{item.standardToUuid,jdbcType=VARCHAR}, #{item.relationScore,jdbcType=DECIMAL}, #{item.stat,jdbcType=TINYINT},
            #{item.lastProcessId,jdbcType=BIGINT}, #{item.isDeleted,jdbcType=TINYINT}, #{item.gmtCreated,jdbcType=TIMESTAMP},
        id=VALUES(id),relation_type = VALUES(relation_type),standard_from_uuid = VALUES(standard_from_uuid),standard_to_uuid = VALUES(standard_to_uuid),
        relation_score = VALUES(relation_score),stat = VALUES(stat),last_process_id = VALUES(last_process_id),
        is_deleted = VALUES(is_deleted),gmt_created = VALUES(gmt_created),
        gmt_modified = VALUES(gmt_modified),relation_desc = VALUES(relation_desc)

The actual bottled sql in scheme 2:

	update standard_relation
	set standard_from_uuid =case when id=#{i.id} then #{i.standardFromUuid} 
								 when id=#{i.id} then #{i.standardFromUuid} end,
	standard_to_uuid =case  when id=#{i.id} then #{i.standardToUuid} 
							when id=#{i.id} then #{i.standardToUuid} end,
	gmt_modified =case when id=#{i.id} then #{i.gmtModified} 
					   when id=#{i.id} then #{i.gmtModified} end
	where id=#{i.id} or id=#{i.id}
    public void updateStandardRelations() {
        List<StandardRelation> list=standardRelationMapper.selectByStandardUuid("xiemingjieupdate");
        for(StandardRelation tmp:list){
        long begin=System.currentTimeMillis();
        long end=System.currentTimeMillis();
        System.out.print("How long does the current batch update method take"+(end-begin)+"ms");

Efficiency comparison:

In fact, the efficiency of sql statement for loop is quite high, because it only has one loop body, but there are many update statements in the end, and a large amount may cause sql blocking.

Although there is only one update statement at the end of case when, there are a lot of loop bodies in xml. Each case when has to cycle through the list set, so it will be slow to spell sql in large quantities, so the efficiency problem is serious. It is recommended to insert in batches when using.

Duplicate key update can be seen as the fastest, but it is generally disabled by large companies. Companies generally prohibit the use of replace into and INSERT INTO... ON DUPLICATE KEY UPDATE. This kind of sql may cause data loss and inconsistency between the self increment id value of the master-slave table. And when using this update, remember to add id, and the values () bracket contains the database field, not the attribute field of java object.

According to the comprehensive consideration of efficiency and safety, it is very important to select the appropriate one.

Reference to the original text: https://blog.csdn.net/q957967519/article/details/88669552


Posted by Vinze on Sat, 14 May 2022 17:14:32 +0300