mysql 批量更新优化

最近项目有一个批量修改的业务，使用常规的forEatch修改，速度效率非常慢，20万的数据大约需要一个小时才能修改完成，可能还会产生死锁。

 <foreach collection="list" item="entity" index="index" separator=";">
         UPDATE test
         <set>
                update_time = now()
        </set>
         WHERE id = #{entity.id}
 </foreach>

优化方案：

使用MySQL临时表进行insert into 来进行操作。

1.第一步：创建mysql临时表：

	CREATE TEMPORARY TABLE IF NOT EXISTS  tmp (
            id bigint(20) primary key,
            price double(10,3),
            money decimal(10,5),
            priceParamId bigint(20));

id一定要有，这个id就是要更新数据库关联主表的id。其他字段就是你要修改的字段。

IF NOT EXISTS 最好是加上，否则重复创建会报表已存在。再次说明，临时表的周期只在此次连接下，连接关闭临时表会自动删除。

2.第二步：执行自己的业务逻辑进行 insert into 操作。

INSERT INTO tmp (id,price,money,priceParamId)
		SELECT id id,
				10 price,
				100 money, 
				price_param_id priceParamId 
		FROM	test 
		WHERE status = 1;

3.第三步： 执行update 操作。

UPDATE test AS info,tmp
		SET info.price = tmp.price,
		info.money = tmp.money,
		info.price_param_id = tmp.priceParamId,
		info.update_time = now()
        WHERE  info.id = tmp.id;

三步可以写到一块进行操作如下图：

CREATE TEMPORARY TABLE IF NOT EXISTS  tmp (
            id bigint(20) primary key,
            price double(10,3),
            money decimal(10,5),
            priceParamId bigint(20));
						
		INSERT INTO tmp (id,price,money,priceParamId)
		SELECT id id,
				10 price,
				100 money, 
				price_param_id priceParamId 
		FROM	test
		WHERE status = 1;
	
	UPDATE testAS info,tmp
		SET info.price = tmp.price,
			info.money = tmp.money,
			info.price_param_id = tmp.priceParamId,
			info.update_time = now()
        WHERE  info.id = tmp.id;

 本项目测试：20万的数据 修改加上其他的业务逻辑操作30秒左右执行完成。 只是单独的修改20万的数据1-2秒左右主要看性能。

以下是项目的真实代码，仅供参考：

    <update id="updatePutDataList" parameterType="java.util.List">
        CREATE TEMPORARY TABLE IF NOT EXISTS put (
            id bigint(20) primary key,
            price double(10,3),
            money decimal(10,5),
            priceParamId bigint(20),
            wholeToraPrice double(10,3) ,
            skuFoldZeroPrice double(10,3),
            numberUnitPrice double(10,3),
            splitZeroMoney decimal(10,5),
            compensation double(20,4),
            boxMoney decimal(10,5));

        INSERT INTO put (id,price,money,priceParamId,wholeToraPrice,skuFoldZeroPrice,numberUnitPrice,
        splitZeroMoney,compensation,boxMoney)
        SELECT
            a.id id,
            b.unit_price price,
            b.unit_price money,
            b.id priceParamId,
            a.whole_tora_price wholeToraPrice,
            a.sku_fold_zero_price skuFoldZeroPrice,
            a.number_unit_price numberUnitPrice,
            a.split_zero_money splitZeroMoney,
            a.compensation compensation,
            a.box_money boxMoney
        FROM (
            SELECT
             id id,
             whole_tora_price,
             sku_fold_zero_price,
             number_unit_price,
             split_zero_money,
             compensation,
             box_money,
             #{priceParamIdNew} price_param_id
            FROM ${tableName}
            WHERE
                AND month = #{month}
                AND price_param_id = #{priceParamId}
                ORDER BY id ASC
                ) a
            LEFT JOIN price b ON a.price_param_id = b.id;

        UPDATE ${tableName} AS info,put 
        SET info.price = put.price,
            info.money = put.money,
            info.price_param_id = put.priceParamId,
            info.whole_tora_price = put.wholeToraPrice,
            info.sku_fold_zero_price = put.skuFoldZeroPrice,
            info.number_unit_price = put.numberUnitPrice,
            info.split_zero_money = put.splitZeroMoney,
            info.compensation = put.compensation,
            info.box_money = put.boxMoney,
            info.update_time = now()
        WHERE  info.id = put.id;

        TRUNCATE TABLE put;
    </update>

还有2个注意事项：
       1）TRUNCATE TABLE  put     清空临时表

       2）DROP TEMPORARY TABLE  put   删除临时表

mysql有些情况没有创建临时表权限，增加创建临时表权限：

GRANT create temporary tables ON *.* TO `账号`@`%`;