1. 先说 删除 吧，因为刚搞了。
删除缓慢的原因：主要是约束的问题。（数据库在有约束的时候，进行操作，会根据约束对相关表进行验证，可想而知，20W的数据验证要耗费多久的时间）。其次就是sql的编写。（sql如果查询中包含子查询等的可以 优化 的where会影响匹配的速度<查询的话就不多逼逼了>）。 索引的问题 请看下面的 补充部分
具体解决方法：
|
1 2 3 |
ALTER TABLE tableName DISABLE TRIGGER ALL ; delete 目标语句 ALTER TABLE tableName ENABLE TRIGGER ALL ; |
2. 更新 ，原因和删除大体一致，这里主要描述一下自己遇到的问题。
利用mybatis批量更新，第一张图是原本的批量更新的写法，发现在本地还可以的但是放在服务器会由于时间太久出现IO错误。
后来换成第二张图的方式来更新。当然也可以java代码for循环来单条更新来达到批量的目的。（对于这三种方式，第二种最快）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
< 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 > |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
< 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中新建一个数据库维护计划，选中[重新组织数据各索引页]选项，并设置每天运行一次。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
/* 刷新数据库视图.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 |
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://blog.csdn.net/weixin_38289303/article/details/82185081
查看更多关于PostgreSQL删除更新优化操作的详细内容...