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

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

3.1更新操作

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

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

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

通過一個更新語句實(shí)現(xiàn)多個自編碼字段更新的語句示意如下：

set 代碼1自編碼 = 通過關(guān)聯(lián)代碼1表得到自編碼，
    代碼2自編碼 =通過關(guān)聯(lián)代碼2表得到自編碼，
    ...，
    代碼n自編碼 =通過關(guān)聯(lián)代碼n表得到自編碼

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

處理過程

多次更新方法耗時

一次更新方法耗時

代碼清洗

0:29:48
0:02:59

3.1.2避免更新

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

圖12. 關(guān)聯(lián)更新

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

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

圖13. 避免更新

3.2刪除操作

初學(xué)者可能認(rèn)為刪除操作很簡單，可以快速完成。其實(shí)這是一個錯誤的理解，刪除過程需要大量掃描磁盤；需要記錄數(shù)據(jù)庫日志；而且刪除過程不釋放磁盤空間，浪費(fèi)磁盤，并且使磁盤上的數(shù)據(jù)支離破碎，這對后續(xù)查詢的性能是一個致命的打擊。通常用兩種方式來應(yīng)對：一、對經(jīng)常做刪除操作的表進(jìn)行重組（reorg）；二、避免刪除。

3.2.1 重組

重組（reorg）操作會重新排列表數(shù)據(jù)的物理順序，并除去碎片數(shù)據(jù)中的空閑空間。

由于刪除操作不釋放磁盤空間，在執(zhí)行刪除操作后，表會成為碎片狀，這導(dǎo)致性能嚴(yán)重下降，在多次更新操作之后也會出現(xiàn)這種情況。若收集了統(tǒng)計信息，但看不出有明顯的性能改進(jìn)，則重組表數(shù)據(jù)可能會有幫助。重組表數(shù)據(jù)時，根據(jù)指定的索引重新安排數(shù)據(jù)的物理順序，并除去碎片數(shù)據(jù)中的空閑空間。這使該數(shù)據(jù)可以更快速的被存取，從而改進(jìn)性能。

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

在數(shù)據(jù)需要比較復(fù)雜的處理的時候經(jīng)常會用到中間表和正式表模式。數(shù)據(jù)在中間表中被處理，然后把滿足條件的數(shù)據(jù)轉(zhuǎn)移至正式表，不滿足條件的數(shù)據(jù)保留在中間表中。圖14示意了數(shù)據(jù)從中間表轉(zhuǎn)移到正式表的過程：在完成數(shù)據(jù)處理之后，需要把中間表temp1中flag = 1的數(shù)據(jù)插入到正式表，并刪除中間表temp1中flag = 1的數(shù)據(jù)。

圖14. 從中間表向正式表轉(zhuǎn)移數(shù)據(jù)

因?yàn)閒lag字段不是聚簇索引，所以當(dāng)對中間表temp1進(jìn)行刪除后，會再磁盤中留下大量碎片，如圖15所示。不但會留下那么多的磁盤碎片，而且已刪除的數(shù)據(jù)的空間也不會自動釋放。結(jié)果是不但浪費(fèi)磁盤空間，而且查詢性能會急劇下降。

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

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

1.         將temp1中flag=0的數(shù)據(jù)，插入到temp2

2.         清空表temp1

3.        將temp2中的數(shù)據(jù)插入temp1

3.3如何使訪問更高效

本小節(jié)的內(nèi)容很大一部分來自《The Art of SQL》這本書，這本書里集合了數(shù)據(jù)庫開發(fā)的通用經(jīng)驗(yàn)。雖然沒有局限于具體的DBMS和硬件平臺，但是卻是一本實(shí)踐性很強(qiáng)的書。

1．一次連接數(shù)據(jù)庫，做很多事情。直到處理完，才斷開連接。
2．一個SQL語句包含盡量多的操作。形象地說：幾千個語句，借助游標(biāo)不斷循環(huán)，很慢。換成幾個語句，處理同樣的數(shù)據(jù)，還是很慢。換成一個語句，解決問題，最好。
3．接近DBMS核心。盡量使用數(shù)據(jù)庫自帶的函數(shù)。減少自定義函數(shù)。因?yàn)樵俾斆鞯臄?shù)據(jù)庫優(yōu)化器也不認(rèn)識自定義函數(shù)。
4．一個語句不要連接太多的表，建議的上限是5個。
5．將頻繁更新的列集中起來：當(dāng)更新某一行時，DB2 會記錄進(jìn)行更改的所有列，因此將頻繁更新的列放到一起可以減少 DB2 的記錄工作。這只是一個有關(guān)性能的小建議，因此不應(yīng)為實(shí)現(xiàn)它而進(jìn)行重大的應(yīng)用程序或數(shù)據(jù)庫設(shè)計修改。

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