背景

项目中有这么一个需求，数据库是MySQL：

  1. 提供一个接口，能够批量插入或更新业务数据，对新数据进行插入操作，已存在的记录进行更新操作。
  2. 更新时，需要判断业务中的某些状态，对符合一定条件的状态进行更新。
  3. 接口的响应速度要快。

由于我们需要执行插入或删除操作，因此，这两个操作能不能合并成一条SQL语句，我们只需要编写一个插入或更新的方法呢？

答案是肯定的，MySQL中的”ON DUPLICATE KEY UPDATE“语法能满足我们的需求，如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致唯一值列重复的问题，则插入新行。

注意：ON DUPLICATE KEY UPDATE只是MySQL的特有语法，并不是SQL标准语法！

使用实例：

<insert id="batchInsertOrUpdateBySite" parameterType="com.leiting.ads.model.entity.TouTiaoProductDetail">
        INSERT INTO ads_jrtt_product_detail (
        id,create_date, modify_date,
        advertiser_id, site_id,`name`,
        `status`, site_type, function_type, thumbnail, link
        )
        VALUES
        <foreach collection="paramList" item="p" separator=",">
            (
            null,
            #{p.createDate},
            #{p.modifyDate},
            #{p.advertiserId},
            #{p.siteId},
            #{p.name},
            #{p.status},
            #{p.siteType},
            #{p.functionType},
            #{p.thumbnail},
            #{p.pageList,typeHandler=com.leiting.ads.mybatis.handlers.JSONArrayHandler}
//若返回数据是json格式，则要在后面定义json类型
            )
        </foreach>
        ON DUPLICATE KEY UPDATE
        modify_date = VALUES (modify_date),
        advertiser_id = VALUES (advertiser_id),
        `name` = VALUES (`name`),
        `status` = VALUES (`status`),
        site_type = VALUES (site_type),
        function_type = VALUES (function_type),
        thumbnail = VALUES (thumbnail),
        link = VALUES (link)
    </insert>
  • 方案二

前面虽然解决了批量执行插入或更新的问题，但是仍然没有解决性能问题，如果数据量大的话，批量插入或更新方法insertOrUpdate()动态SQL语句会十分长；另外依然需要循环查询数据记录做逻辑判断。那么我们该如何解决动态SQL语句长的问题呢？

根据实际经验，如果请求接口数据量大于1000条的话，执行速度会相当慢，估计需要耗时10多秒，这样的速度客户是无法忍受的。那么该如何优化呢？我们可不可以将数据进行分页，每页数据指定大小，比如100条数据执行一下insertOrUpdate()呢？答案是肯定的，如果按分页思想进行批量插入或更新的话，性能可以提高到50%左右。

  • 方案三

虽然方案二已经使性能有了很大的提高，但是还是不能满足现有业务的要求，我们还能不能再缩短一些呢？答案是肯定的，我们只需要在方案二的基础上，把循环查询数据记录做逻辑判断这一部分进行优化即可，只要我们可以让数据一次性查询出来存放在内存中，而不需要每次都连数据库查询，基于这个思想，我们是不是又能更进一步呢？