PostgreSQL删除更新优化

1. 先说删除吧，因为刚搞了。

删除缓慢的原因：主要是约束的问题。（数据库在有约束的时候，进行操作，会根据约束对相关表进行验证，可想而知，20W的数据验证要耗费多久的时间）。其次就是sql的编写。（sql如果查询中包含子查询等的可以优化的where会影响匹配的速度<查询的话就不多逼逼了>）。索引的问题（https://blog.youkuaiyun.com/steven088/article/details/7174566）

具体解决方法：

ALTER TABLE tableName DISABLE TRIGGER ALL;

        delete 目标语句

ALTER TABLE tableName ENABLE TRIGGER ALL;

2. 更新，原因和删除大体一致，这里主要描述一下自己遇到的问题。

利用mybatis批量更新，第一张图是原本的批量更新的写法，发现在本地还可以的但是放在服务器会由于时间太久出现IO错误。

后来换成第二张图的方式来更新。当然也可以java代码for循环来单条更新来达到批量的目的。（对于这三种方式，第二种最快）

<update id="batchUpdate2" parameterType="java.util.List">
		UPDATE lt_dzheinfo 
		 <trim prefix="set" suffixOverrides=",">
            <trim prefix="dzzhao =case" suffix="end,">
                <foreach collection="list" item="reader" index="index">
                     when dzid=#{reader.readerId} then #{reader.readerBarcode}
                </foreach>
            </trim>
            <trim prefix="xming =case" suffix="end,">
                <foreach collection="list" item="reader" index="index">
                     when dzid=#{reader.readerId} then #{reader.readerName}
                </foreach>
            </trim>
             <trim prefix="ztai =case" suffix="end,">
                <foreach collection="list" item="reader" index="index">
                     when dzid=#{reader.readerId} then #{reader.readerState}
                </foreach>
            </trim>
             <trim prefix="mima =case" suffix="end,">
                <foreach collection="list" item="reader" index="index">
                     when dzid=#{reader.readerId} then #{reader.readerPsw}
                </foreach>
            </trim>
            <trim prefix="dzlxid =case" suffix="end,">
                <foreach collection="list" item="reader" index="index">
                     when dzid=#{reader.readerId} then #{reader.readerType}
                </foreach>
            </trim>
            <trim prefix="zjlx =case" suffix="end,">
                <foreach collection="list" item="reader" index="index">
                     when dzid=#{reader.readerId} then #{reader.identityType}
                </foreach>
            </trim>
            <trim prefix="zjhma =case" suffix="end,">
                <foreach collection="list" item="reader" index="index">
                     when dzid=#{reader.readerId} then #{reader.identityId}
                </foreach>
            </trim>
            <trim prefix="qyrqi =case" suffix="end,">
                <foreach collection="list" item="reader" index="index">
                     when dzid=#{reader.readerId} then TO_TIMESTAMP(#{reader.enableDate}, 'YYYY-MM-DD HH24:MI:SS')
                </foreach>
            </trim>
            <trim prefix="jzrqi =case" suffix="end,">
                <foreach collection="list" item="reader" index="index">
                     when dzid=#{reader.readerId} then TO_TIMESTAMP(#{reader.disableDate}, 'YYYY-MM-DD HH24:MI:SS')
                </foreach>
            </trim>
            <trim prefix="libid =case" suffix="end,">
                <foreach collection="list" item="reader" index="index">
                     when dzid=#{reader.readerId} then #{reader.readerOfLibCode}
                </foreach>
            </trim>
            <trim prefix="xingbie =case" suffix="end,">
                <foreach collection="list" item="reader" index="index">
                	when dzid=#{reader.readerId} then 
		            <choose>
						<when test="reader.readerSex=='男'.toString()">
							true
						</when>
						<otherwise>
							false
						</otherwise>
					</choose>
                </foreach>
            </trim>
            <trim prefix="userid =case" suffix="end,">
                <foreach collection="list" item="reader" index="index">
                     when dzid=#{reader.readerId} then #{reader.readerOperatorId}
                </foreach>
            </trim>
            <trim prefix="beizhu =case" suffix="end,">
                <foreach collection="list" item="reader" index="index">
                     when dzid=#{reader.readerId} then #{reader.readerRemark}
                </foreach>
            </trim>
            <trim prefix="bzrqi =case" suffix="end,">
                <foreach collection="list" item="reader" index="index">
                     when dzid=#{reader.readerId} then TO_TIMESTAMP(#{reader.enableDate}, 'YYYY-MM-DD HH24:MI:SS')
                </foreach>
            </trim>
            <trim prefix="optime =case" suffix="end,">
                <foreach collection="list" item="reader" index="index">
                     when dzid=#{reader.readerId} then TO_TIMESTAMP(#{reader.enableDate}, 'YYYY-MM-DD HH24:MI:SS')
                </foreach>
            </trim>
        </trim>
        where dzid in
        <foreach collection="list" index="index" item="reader" separator="," open="(" close=")">
            #{reader.readerId}
        </foreach>
	</update>

<update id="batchUpdate" parameterType="java.util.List">
		<foreach collection="list" index="index" item="reader" separator=";">
			UPDATE lt_dzheinfo SET
			dzzhao = #{reader.readerBarcode},
			xming = #{reader.readerName},
			ztai = #{reader.readerState},
			mima = #{reader.readerPsw},
			dzlxid = #{reader.readerType},
			zjlx = #{reader.identityType},
			zjhma = #{reader.identityId},
			qyrqi = TO_TIMESTAMP(#{reader.enableDate}, 'YYYY-MM-DD HH24:MI:SS'),
			jzrqi = TO_TIMESTAMP(#{reader.disableDate}, 'YYYY-MM-DD HH24:MI:SS'),
			libid = #{reader.readerOfLibCode},
			xingbie = 
			<choose>
				<when test="reader.readerSex=='男'.toString()">
					true
				</when>
				<otherwise>
					false
				</otherwise>
			</choose>,
			userid = #{reader.readerOperatorId},
			beizhu = #{reader.readerRemark},
			bzrqi = TO_TIMESTAMP(#{reader.enableDate}, 'YYYY-MM-DD HH24:MI:SS'),
			optime = TO_TIMESTAMP(#{reader.enableDate}, 'YYYY-MM-DD HH24:MI:SS')
			<where>
				dzid = #{reader.readerId}
			</where>
		</foreach>
	</update>