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