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

這篇文章主要介紹“MySql刪除和更新操作對性能有沒有影響”，在日常操作中，相信很多人在MySql刪除和更新操作對性能有沒有影響問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySql刪除和更新操作對性能有沒有影響”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

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

3.1更新操作

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

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

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

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

復制代碼 代碼如下: updateTBL_INCOME_TMP

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

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

處理過程

多次更新方法耗時

一次更新方法耗時

代碼清洗

0:29:480: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如何使訪問更高效

1．一次連接數據庫，做很多事情。直到處理完，才斷開連接。

2．一個SQL語句包含盡量多的操作。形象地說：幾千個語句，借助游標不斷循環，很慢。換成幾個語句，處理同樣的數據，還是很慢。換成一個語句解決問題最好。

3．接近DBMS核心。盡量使用數據庫自帶的函數。減少自定義函數。因為再聰明的數據庫優化器也不認識自定義函數。

4．一個語句不要連接太多的表，建議的上限是5個。

5．將頻繁更新的列集中起來：當更新某一行時，DB2 會記錄進行更改的所有列，因此將頻繁更新的列放到一起可以減少 DB2 的記錄工作。這只是一個有關性能的小建議，因此不應為實現它而進行重大的應用程序或數據庫設計修改。

到此，關于“MySql刪除和更新操作對性能有沒有影響”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！