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

3.1更新操作

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

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

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

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

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

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

處理過程

多次更新方法耗時(shí)

一次更新方法耗時(shí)

代碼清洗

0:29:48
0:02:59

3.1.2避免更新

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

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

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

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

圖13. 避免更新

3.2刪除操作

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

3.2.1 重組

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

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

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

在數(shù)據(jù)需要比較復(fù)雜的處理的時(shí)候經(jīng)常會(huì)用到中間表和正式表模式。數(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)對(duì)中間表temp1進(jìn)行刪除后，會(huì)再磁盤中留下大量碎片，如圖15所示。不但會(huì)留下那么多的磁盤碎片，而且已刪除的數(shù)據(jù)的空間也不會(huì)自動(dòng)釋放。結(jié)果是不但浪費(fèi)磁盤空間，而且查詢性能會(huì)急劇下降。

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

咱們可以使用清空表的命令來避免刪除操作。除了中間表temp1和正式表，添加輔助臨時(shí)表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和硬件平臺(tái)，但是卻是一本實(shí)踐性很強(qiáng)的書。

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

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