MySQL和Oracle的delete,truncate

在MySQL和Oracle中的delete,truncate還是存在著一些差別，明白了這些差別可能對(duì)于處理問題，理解問題會(huì)有一些幫助。

我們來簡(jiǎn)單通過一些測(cè)試來說明。我們創(chuàng)建兩個(gè)表test_del,test_tru來對(duì)比delete,truncate的操作。我們有一個(gè)臨時(shí)表t_fund_info大概有幾百萬的數(shù)據(jù)量。

創(chuàng)建test_del

創(chuàng)建test_tru

這個(gè)時(shí)候我們來查看對(duì)應(yīng)的文件，在MySQL中通用的方式，對(duì)于每個(gè)表會(huì)對(duì)應(yīng)單獨(dú)的數(shù)據(jù)文件和配置文件。

我們可以看到存在4個(gè)新的文件，大小都是一樣的。

我們開始測(cè)試兩者的不同。

這個(gè)時(shí)候再次查看目錄下的文件情況，可以赫然看到test_tru的數(shù)據(jù)文件徹底釋放了,而delete之后的文件大小還是保持原樣。

那么delete的表怎么釋放哪些空間呢，可以考慮重置存儲(chǔ)引擎。

整個(gè)過程非?？欤僮髦蟛榭次募?，大小已經(jīng)收縮了。

這里需要明白一點(diǎn)，就是修改存儲(chǔ)引擎的操作，還是和里面的數(shù)據(jù)有關(guān)，在上面的這個(gè)場(chǎng)景中，這是一個(gè)較為特殊的例子。我們drop表test_del,然后重建一次看看。

這個(gè)時(shí)候我們來看看存在大量數(shù)據(jù)的情況下，修改存儲(chǔ)引擎的情況。

可以看到操作的時(shí)間還是有著天壤之別

為什么要慢了很多呢，這是因?yàn)檫@個(gè)操作的底層操作就是復(fù)制數(shù)據(jù)。MySQL只是幫你做好了這些事情而已，可以看到在操作的過程中會(huì)創(chuàng)建一個(gè)臨時(shí)表。

delete和truncate還是有一定的適用場(chǎng)景，此外在MySQL中還有一種使用方式就蠻有特色了，那就是選擇性的刪除，比如刪除test_del中id排序的前n條數(shù)據(jù)，可以在delete里面使用order by limit的方式。

比如：

此外，MySQL和Oracle中還有一個(gè)較大的差別是，MySQL中的表數(shù)據(jù)文件是完全復(fù)用的，而在Oracle中有多種方式，比如append,reuse等，這個(gè)本身和設(shè)計(jì)也有關(guān)系。

比如在MySQL中我們delete一個(gè)表的數(shù)據(jù)，然后重新插入，那么這個(gè)空間是完全復(fù)用的。原有的文件幾乎不會(huì)有所變化。

而truncate的操作在MySQL是一個(gè)很快的操作，數(shù)據(jù)轉(zhuǎn)瞬即逝，在Oracle中有一些差別，可能這些數(shù)據(jù)還有恢復(fù)的可能。

比如我們?cè)贠racle端創(chuàng)建一個(gè)表空間，創(chuàng)建兩個(gè)表test_del,test_tru;

這個(gè)時(shí)候我們來測(cè)試一下兩者的差別。

這里的差別在于，如果我們delete了數(shù)據(jù)之后，表test_del已經(jīng)沒有數(shù)據(jù)，但是查詢的時(shí)候還是會(huì)全表掃描，掃描的數(shù)據(jù)塊和清理前基本是一樣的,而且代價(jià)還會(huì)更高一些。

而truncate操作在Oracle,MySQL都是一個(gè)極快的過程，在Oracle中不會(huì)直接抹去數(shù)據(jù)，數(shù)據(jù)還是依舊存在，在一定的條件下觸發(fā)才會(huì)回收。

我們使用dbms_rowid來解析一下

然后對(duì)這個(gè)數(shù)據(jù)塊做一個(gè)dump.

truncate前的數(shù)據(jù)塊的一些數(shù)據(jù)。

truncate之后的數(shù)據(jù)如下:

在MySQL中可以很飄逸的使用limit，在Oracle中換種寫法，類似MySQL的語法：

在Oracle中可能得這么些了，不考慮哪些分析函數(shù)的用法

下一篇：no boot device修復(fù)