快捷導(dǎo)航

PostgreSQL刪除更新優(yōu)化操作

更新時(shí)間：2020年12月28日 12:39:56 投稿：jingxian

這篇文章主要介紹了PostgreSQL刪除更新優(yōu)化操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧

1. 先說刪除吧，因?yàn)閯偢懔恕?/h3>

刪除緩慢的原因：主要是約束的問題。（數(shù)據(jù)庫(kù)在有約束的時(shí)候，進(jìn)行操作，會(huì)根據(jù)約束對(duì)相關(guān)表進(jìn)行驗(yàn)證，可想而知，20W的數(shù)據(jù)驗(yàn)證要耗費(fèi)多久的時(shí)間）。其次就是sql的編寫。（sql如果查詢中包含子查詢等的可以優(yōu)化的where會(huì)影響匹配的速度<查詢的話就不多逼逼了>）。索引的問題請(qǐng)看下面的補(bǔ)充部分

具體解決方法：

ALTER TABLE tableName DISABLE TRIGGER ALL; 
  delete 目標(biāo)語句 
ALTER TABLE tableName ENABLE TRIGGER ALL;

2. 更新，原因和刪除大體一致，這里主要描述一下自己遇到的問題。

利用mybatis批量更新，第一張圖是原本的批量更新的寫法，發(fā)現(xiàn)在本地還可以的但是放在服務(wù)器會(huì)由于時(shí)間太久出現(xiàn)IO錯(cuò)誤。

后來換成第二張圖的方式來更新。當(dāng)然也可以java代碼for循環(huán)來單條更新來達(dá)到批量的目的。（對(duì)于這三種方式，第二種最快）

<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>

補(bǔ)充：數(shù)據(jù)庫(kù)突然變慢...索引作怪！

本人在最近發(fā)現(xiàn)，有些比容量較大的SQL數(shù)據(jù)庫(kù)，在使用一段時(shí)間后，會(huì)突然變很慢很慢。特別體現(xiàn)在一些經(jīng)常進(jìn)行操作及查詢的大表中。經(jīng)過深入研究，這一般是索引的問題！如果把此表的索引重建一次，問題即可解決。

為更好地解決此問題，建意在SQL中新建一個(gè)數(shù)據(jù)庫(kù)維護(hù)計(jì)劃，選中“重新組織數(shù)據(jù)各索引頁”選項(xiàng)，并設(shè)置每天運(yùn)行一次。

/*
 刷新數(shù)據(jù)庫(kù)視圖.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
--刷新指定視圖的元數(shù)據(jù)。由于視圖所依賴的基礎(chǔ)對(duì)象的更改，視圖的持久元數(shù)據(jù)會(huì)過期。
 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

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。

您可能感興趣的文章: