1. 先说删除吧，因为刚搞了。
删除缓慢的原因：主要是约束的问题。（数据库在有约束的时候，进行操作，会根据约束对相关表进行验证，可想而知，20w的数据验证要耗费多久的时间）。其次就是sql的编写。（sql如果查询中包含子查询等的可以优化的where会影响匹配的速度<查询的话就不多逼逼了>）。索引的问题 请看下面的 补充部分
具体解决方法：
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>
补充：数据库突然变慢…索引作怪！
本人在最近发现，有些比容量较大的sql数据库，在使用一段时间后，会突然变很慢很慢。特别体现在一些经常进行操作及查询的大表中。经过深入研究，这一般是索引的问题！如果把此表的索引重建一次，问题即可解决。
为更好地解决此问题，建意在sql中新建一个数据库维护计划，选中“重新组织数据各索引页”选项，并设置每天运行一次。
/* 刷新数据库视图.sql */ alter proc p_refreshview as declare @vname sysname declare refresh_cursor cursor for select name from sysobjects where xtype = 'v' order by crdate for read only open refresh_cursor fetch next from refresh_cursor into @vname while @@fetch_status = 0 begin --刷新指定视图的元数据。由于视图所依赖的基础对象的更改，视图的持久元数据会过期。 exec sp_refreshview @vname print '视图' + @vname + '已更新' fetch next from refresh_cursor into @vname end close refresh_cursor deallocate refresh_cursor go set quoted_identifier off go set ansi_nulls on go
以上为个人经验，希望能给大家一个参考，也希望大家多多支持www.887551.com。如有错误或未考虑完全的地方，望不吝赐教。