程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> MySql刪除和更新操作對性能有影響嗎

MySql刪除和更新操作對性能有影響嗎

編輯:關於SqlServer

刪除和更新操作的開銷往往比插入高,所以一個好的設計需要減少對數據庫的更新和刪除操作。

3.1更新操作

數據庫的更新操作會帶來一連串的“效應”:更新操作需要記錄日志(以便錯誤時回滾);更新可變長字段(如,varchar類型)會帶來數據物理存儲的變化(記錄的移動);更新索引字段會導致索引重建;更新主鍵會導致數據重組等。這一切不但會造成更新操作本身效率低,而且由於磁片碎片的產生會造成以後查詢性能的降低。為了應對這一情況,有兩種策略:一、減少更新次數,把多個字段的更新寫到同一個語句裡;二、避免更新。這兩種策略分別適用於不同的情況,下面將舉例說明兩種情況。

3.1.1減少更新次數
在整合庫裡有個代碼清洗過程,就是通過連接代碼表給業務數據的自編碼字段賦值。代碼清洗其實是通過關聯代碼表來更新業務數據表的一個過程,需要連接多個代碼表,更新多個自編碼字段。完成此更新,有兩種更新語句的寫法:一種是寫成多個SQL語句,每個語句更新一個自編碼字段;另一種寫法是將所有更新寫在一個語句中。更新銀行代碼的更新語句如下所示:

updateTBL_INCOME_TMP A
setBANKCODESELF = (
 select SELFCODE
 from
 TBL_BANKINFO B
 where A.BANKCODE = B.BANKCODE )

通過一個更新語句實現多個自編碼字段更新的語句示意如下:

復制代碼 代碼如下:
updateTBL_INCOME_TMP

set 代碼1自編碼 = 通過關聯代碼1表得到自編碼,
    代碼2自編碼 =通過關聯代碼2表得到自編碼,
    ...,
    代碼n自編碼 =通過關聯代碼n表得到自編碼

利用兩千萬的測試數據。兩種方法的測試結果如下表所示。從測試結果看出,一次更新方法性能提高了十倍,大大提高了性能。

處理過程

多次更新方法耗時

一次更新方法耗時

代碼清洗

0:29:48
0:02:59

3.1.2避免更新

下面舉個通俗的例子,這類情況是經常遇到的。某公司有一套系統員工考勤系統,為了提高查詢統計的性能,在原有系統基礎上建立了一些包含冗余信息的表。以員工表為例,它獲得數據的過程如圖12所示。第一步把員工信息放到新表中,然後連接通過字段“部門ID”連接更新“部門名稱”。

圖12. 關聯更新

一般,為了節省存儲開支把部門名稱這樣的字段設計成可變長的。所以在對它進行更新時會造成磁盤數據的重新組織,形成磁盤碎片,影響查詢性能。

為了避免這樣的情況發生,我們可以使用如圖13所示的方法避免更新。這種方法一步完成了冗余數據表的插入,再插入時連接部門表獲得“部門名稱”,從而避免了更新操作。

圖13. 避免更新

3.2刪除操作

初學者可能認為刪除操作很簡單,可以快速完成。其實這是一個錯誤的理解,刪除過程需要大量掃描磁盤;需要記錄數據庫日志;而且刪除過程不釋放磁盤空間,浪費磁盤,並且使磁盤上的數據支離破碎,這對後續查詢的性能是一個致命的打擊。通常用兩種方式來應對:一、對經常做刪除操作的表進行重組(reorg);二、避免刪除。

3.2.1 重組

重組(reorg)操作會重新排列表數據的物理順序,並除去碎片數據中的空閒空間。

由於刪除操作不釋放磁盤空間,在執行刪除操作後,表會成為碎片狀,這導致性能嚴重下降,在多次更新操作之後也會出現這種情況。若收集了統計信息,但看不出有明顯的性能改進,則重組表數據可能會有幫助。重組表數據時,根據指定的索引重新安排數據的物理順序,並除去碎片數據中的空閒空間。這使該數據可以更快速的被存取,從而改進性能。

3.2.2 避免刪除——中間表和正式表模式

在數據需要比較復雜的處理的時候經常會用到中間表和正式表模式。數據在中間表中被處理,然後把滿足條件的數據轉移至正式表,不滿足條件的數據保留在中間表中。圖14示意了數據從中間表轉移到正式表的過程:在完成數據處理之後,需要把中間表temp1中flag = 1的數據插入到正式表,並刪除中間表temp1中flag = 1的數據。

圖14. 從中間表向正式表轉移數據

因為flag字段不是聚簇索引,所以當對中間表temp1進行刪除後,會再磁盤中留下大量碎片,如圖15所示。不但會留下那麼多的磁盤碎片,而且已刪除的數據的空間也不會自動釋放。結果是不但浪費磁盤空間,而且查詢性能會急劇下降。

圖15. 刪除操作後的磁盤碎片

咱們可以使用清空表的命令來避免刪除操作。除了中間表temp1和正式表,添加輔助臨時表temp2。如果temp1中保留的數據flag=0只占有10%,這一優化將顯著提升性能。具體步驟如下:

1.         將temp1中flag=0的數據,插入到temp2

2.         清空表temp1

復制代碼 代碼如下:
alter table temp1 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE ;

3.        將temp2中的數據插入temp1

3.3如何使訪問更高效

本小節的內容很大一部分來自《The Art of SQL》這本書,這本書裡集合了數據庫開發的通用經驗。雖然沒有局限於具體的DBMS和硬件平台,但是卻是一本實踐性很強的書。

1.一次連接數據庫,做很多事情。直到處理完,才斷開連接。
2.一個SQL語句包含盡量多的操作。形象地說:幾千個語句,借助游標不斷循環,很慢。換成幾個語句,處理同樣的數據,還是很慢。換成一個語句,解決問題,最好。
3.接近DBMS核心。盡量使用數據庫自帶的函數。減少自定義函數。因為再聰明的數據庫優化器也不認識自定義函數。
4.一個語句不要連接太多的表,建議的上限是5個。
5.將頻繁更新的列集中起來:當更新某一行時,DB2 會記錄進行更改的所有列,因此將頻繁更新的列放到一起可以減少 DB2 的記錄工作。這只是一個有關性能的小建議,因此不應為實現它而進行重大的應用程序或數據庫設計修改。

以上就是本文針對MySql刪除和更新對性能有影響嗎的全部內容,希望喜歡。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved