一些看似簡單的數(shù)據(jù)操作，當(dāng)作用于海量數(shù)據(jù)集時，就會出現(xiàn)“意料之外，卻在情理之中”的問題，海量數(shù)據(jù)操作，需要采用特殊方法，才能“曲徑通幽”。在刪除海量數(shù)據(jù)時，需要注意日志的增長，索引碎片的增加和數(shù)據(jù)庫的恢復(fù)模式，特別是利用大容量日志操作，來減少日志的增長和提高數(shù)據(jù)插入的速度。對于大數(shù)據(jù)去重，通過一些小小的改進(jìn)，比如創(chuàng)建索引，設(shè)置忽略重復(fù)值選項等，能夠提高去重的效率。

一，從海量數(shù)據(jù)中刪除數(shù)據(jù)

從海量數(shù)據(jù)表中刪除一半數(shù)據(jù)，看似簡單，使用delete命令，如果真這么干，SQL Server產(chǎn)生的事務(wù)日志暴增，估計會把服務(wù)器硬盤爆掉。數(shù)據(jù)庫的恢復(fù)模式會影響日志文件的增長，在刪除海量數(shù)據(jù)時，根據(jù)采用的方法，相應(yīng)地把恢復(fù)模式設(shè)置為simple，或bulk_logged 模式，能夠在很大程度上減少刪除操作產(chǎn)生的事務(wù)日志，從而避免日志暴增。

另外，在刪除數(shù)據(jù)時，把表上的多余索引刪除（注意，是刪除多余的索引），只保留一個必需的索引；在數(shù)據(jù)刪除完成之后，再重建索引，能夠提高數(shù)據(jù)刪除操作的性能。有人做過實驗，從存儲1.6億條記錄的大表中刪除數(shù)據(jù)，每刪除400萬條要消耗1.5 - 3小時，越到后面速度越慢，為什么？這是因為，每次刪除數(shù)據(jù)時，數(shù)據(jù)庫都要相應(yīng)地更新索引，這是很慢的硬盤 IO操作，并且，越到后面，索引碎片越多，更新索引就越慢，這就是在刪除400萬條記錄時，一開始只消耗1.5小時，后面要消耗3小時原因。

最后，根據(jù)保留數(shù)據(jù)占總數(shù)據(jù)量的比例，選擇不同的方法刪除數(shù)據(jù)。如果大表中保留的數(shù)據(jù)較少，可以先把保留的數(shù)據(jù)存儲到臨時表中，然后，把原始表刪除，這樣能夠利用大容量日志操作，來減少日志的增長和提高數(shù)據(jù)插入的速度。

1，循環(huán)刪除，避免日志文件暴增

在從海量數(shù)據(jù)表中刪除大量數(shù)據(jù)時，為了避免日志文件暴增，通常采用循環(huán)刪除方法：首先設(shè)置恢復(fù)模式為simple，然后每次刪除操作都只刪除部分?jǐn)?shù)據(jù)，這樣，當(dāng)單個刪除操作執(zhí)行完成時，事務(wù)日志會被及時清理，事務(wù)日志一般保持單個刪除操作的事務(wù)日志量。

循環(huán)刪除的偽代碼如下，該方法仍有一些局限性，耗時過長，并且會長期使數(shù)據(jù)庫處于簡單恢復(fù)模式下：

萬碼學(xué)堂,電腦培訓(xùn),計算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

--ALTER DATABASE database_name SET RECOVERY SIMPLE ;  while @index<@EndIndexbegin
    delete table_name 
    where index<=@index;    set @index+=@Incrementend

萬碼學(xué)堂,電腦培訓(xùn),計算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

2，將數(shù)據(jù)插入導(dǎo)其他表中，把原表truncate

如果原始表有一半以上的數(shù)據(jù)要被刪除，從原始表中執(zhí)行delete命令刪除數(shù)據(jù)，效率十分低下，可以考慮，把原始表中的數(shù)據(jù)通過select語句篩選出來，然后批量插入導(dǎo)新表中，這種方式利用了大容量日志（Bulk Logged）操作的優(yōu)勢。由于 SELECT INTO，INSERT SELECT 是大容量日志操作，select命令不會產(chǎn)生大量日志文件，因此，執(zhí)行插入比執(zhí)行刪除的效率更高。最后，執(zhí)行drop命令，刪除整個原始表，幾乎不消耗任何時間。

萬碼學(xué)堂,電腦培訓(xùn),計算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

--ALTER DATABASE database_name SET RECOVERY BULK_LOGGED ;  insert into new_tableselect column_listfrom original_tabledrop table original_table

萬碼學(xué)堂,電腦培訓(xùn),計算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

3，對分區(qū)表執(zhí)行分區(qū)轉(zhuǎn)移操作

SQL Server的分區(qū)表實際上是一系列物理上獨立存儲的“表”（也叫做分區(qū)）構(gòu)成的，如果要刪除的數(shù)據(jù)位于同一個分區(qū)，或者，一個分區(qū)中的數(shù)據(jù)都需要被刪除，那么可以把該分區(qū)轉(zhuǎn)移（switch）到一個臨時表中，由于分區(qū)的轉(zhuǎn)移僅僅是元數(shù)據(jù)庫的變更，因此，不會產(chǎn)生任何的數(shù)據(jù)IO，分區(qū)轉(zhuǎn)移瞬間完成。被剝離的分區(qū)，通過drop命令刪除，整個過程僅僅會產(chǎn)生少量的IO操作，用于元數(shù)據(jù)變更；而不會產(chǎn)生用于數(shù)據(jù)刪除的IO操作，這種方法，耗時最短，資源消耗最小，效率最高。

alter table original_table
SWITCH  PARTITION source_partition_numberTO temporary_tabledrop table temporary_table

二，從海量數(shù)據(jù)中去重

數(shù)據(jù)去重，分為部分列去重和全部列去重，全部列去重，使用distinct子句來實現(xiàn)，由于distinct操作符會創(chuàng)建在tempdb中臨時表，因此，distinct操作是IO密集型的操作。而部分列去重，一般采用row_number排名函數(shù)來實現(xiàn)，也可以考慮使用忽略重復(fù)值的唯一索引來實現(xiàn)。在實際的項目開發(fā)中，部分列去重更為常見。

1，使用row_number來實現(xiàn)

首先在部分列上創(chuàng)建索引，然后通過row_number和cte來實現(xiàn)重復(fù)數(shù)據(jù)的刪除，在去重時，需要注意的時，如果刪除的數(shù)據(jù)量太大，有可能會產(chǎn)生大量的事務(wù)日志。

萬碼學(xué)堂,電腦培訓(xùn),計算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

create index index_nameon table_name
(
index_columns
)with(data_compression=page);with cte as (    select column_list,
        row_number() over(partition by index_columns order by ...) as rn    from table_name
)delete from ctewhere rn>1

萬碼學(xué)堂,電腦培訓(xùn),計算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

2，使用忽略重復(fù)值的唯一索引來實現(xiàn)

創(chuàng)建一個臨時表，在部分列上創(chuàng)建忽略重復(fù)值的唯一索引

萬碼學(xué)堂,電腦培訓(xùn),計算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

create unique index index_nameon table_name
(
index_columns
)with(ignore_dup_key=on)

萬碼學(xué)堂,電腦培訓(xùn),計算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

由于SQL Server不允許在包含重復(fù)值的數(shù)據(jù)表上創(chuàng)建唯一索引，因此，必須創(chuàng)建一個新的空表，新表時原始表的結(jié)構(gòu)的復(fù)制，在部分列上創(chuàng)建忽略重復(fù)值的唯一索引。在執(zhí)行插入操作時， IGNORE_DUP_KEY 選項會忽略重復(fù)的索引鍵值，并拋出警告（Warning）。

 

參考文檔：

--業(yè)精于勤而荒于嬉，行成于思而毀于隨--
--歡迎轉(zhuǎn)載，轉(zhuǎn)載請注明出處--

分類: SQL Server

http://www.cnblogs.com/ljhdo/p/4984644.html