大資料操作：删除和去重

一些看似簡單的資料操作，當作用于海量資料集時，就會出現“意料之外，卻在情理之中”的問題，海量資料操作，需要采用特殊方法，才能“曲徑通幽”。在删除海量資料時，需要注意日志的增長，索引碎片的增加和資料庫的恢複模式，特别是利用大容量日志操作，來減少日志的增長和提高資料插入的速度。對于大資料去重，通過一些小小的改進，比如建立索引，設定忽略重複值選項等，能夠提高去重的效率。

一，從海量資料中删除資料

從海量資料表中删除一半資料，看似簡單，使用delete指令，如果真這麼幹，SQL Server産生的事務日志暴增，估計會把伺服器硬碟爆掉。資料庫的恢複模式會影響日志檔案的增長，在删除海量資料時，根據采用的方法，相應地把恢複模式設定為simple，或bulk_logged 模式，能夠在很大程度上減少删除操作産生的事務日志，進而避免日志暴增。

另外，在删除資料時，把表上的多餘索引删除（注意，是删除多餘的索引），隻保留一個必需的索引；在資料删除完成之後，再重建索引，能夠提高資料删除操作的性能。有人做過實驗，從存儲1.6億條記錄的大表中删除資料，每删除400萬條要消耗1.5 - 3小時，越到後面速度越慢，為什麼？這是因為，每次删除資料時，資料庫都要相應地更新索引，這是很慢的硬碟 IO操作，并且，越到後面，索引碎片越多，更新索引就越慢，這就是在删除400萬條記錄時，一開始隻消耗1.5小時，後面要消耗3小時原因。

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

1，循環删除，避免日志檔案暴增

在從海量資料表中删除大量資料時，為了避免日志檔案暴增，通常采用循環删除方法：首先設定恢複模式為simple，然後每次删除操作都隻删除部分資料，這樣，當單個删除操作執行完成時，事務日志會被及時清理，事務日志一般保持單個删除操作的事務日志量。

循環删除的僞代碼如下，該方法仍有一些局限性，耗時過長，并且會長期使資料庫處于簡單恢複模式下：

--ALTER DATABASE database_name SET RECOVERY SIMPLE ;  

while @index<@EndIndex
begin
    delete table_name 
    where index<=@index;

    set @index+=@Increment
end      

2，将資料插入到臨時表中，把原表drop

如果原始表有一半以上的資料要被删除，從原始表中執行delete指令删除資料，效率十分低下，可以考慮，把原始表中的資料通過select語句篩選出來，然後批量插入導新表中，這種方式利用了大容量日志（Bulk Logged）操作的優勢。由于 SELECT INTO，INSERT SELECT 是大容量日志操作，select指令不會産生大量日志檔案，是以，執行插入比執行删除的效率更高。最後，執行drop指令，删除整個原始表，幾乎不消耗任何時間。

--ALTER DATABASE database_name SET RECOVERY BULK_LOGGED ;  

insert into new_table
select column_list
from original_table
where filter_retain

drop table original_table      

把臨時表重命名，執行 sp_rename 或手動重命名，其中 @objtype 參數是可選的，預設值是NULL，對表重命名，設定參數 @objtype='object':

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name'   
    [ , [ @objtype = ] 'object_type' ]        

3，對分區表執行分區轉移操作

SQL Server的分區表實際上是一系列實體上獨立存儲的“表”（也叫做分區）構成的，如果要删除的資料位于同一個分區，或者，一個分區中的資料都需要被删除，那麼可以把該分區轉移（switch）到一個臨時表中，由于分區的轉移僅僅是中繼資料庫的變更，是以，不會産生任何的資料IO，分區轉移瞬間完成。被剝離的分區，通過drop指令删除，整個過程僅僅會産生少量的IO操作，用于中繼資料變更；而不會産生用于資料删除的IO操作，這種方法，耗時最短，資源消耗最小，效率最高。

alter table original_table
SWITCH  PARTITION source_partition_number
TO temporary_table

drop table temporary_table      

二，從海量資料中去重

資料去重，分為部分列去重和全部列去重，全部列去重，使用distinct子句來實作，由于distinct操作符會建立在tempdb中臨時表，是以，distinct操作是IO密集型的操作。而部分列去重，一般采用row_number排名函數來實作，也可以考慮使用忽略重複值的唯一索引來實作。在實際的項目開發中，部分列去重更為常見。

1，使用row_number函數來實作

選擇排名函數，是因為排名函數有部分列分區排序的功能：首先在部分列上建立索引，這樣資料庫引擎能夠根據索引列快速排序，然後通過row_number函數和cte來實作重複資料的删除。在資料去重時，需要注意，如果删除的資料量太大，資料庫引擎會産生大量的事務日志，導緻日志檔案暴增，在選擇該方法時，需要慎重。

create index index_name
on table_name
(
index_columns
)
with(data_compression=page);

with cte as 
(
    select index_columns,
        row_number() over(partition by index_columns order by ...) as rn
    from table_name
)
delete 
from cte
where rn>1      

2，使用忽略重複值的唯一索引來實作

通過插入和忽略重複值實作部分列的去重，相對來說，更容易控制，使用者可以通過循環插入方式來執行，這樣，在單獨的一個事務中，控制插入資料的數量，能夠控制産生的事務日志不至于太大，對于海量資料的去重，建議采用該方法。

建立一個臨時表，在部分列上建立忽略重複值的唯一索引：

create unique index index_name
on new_table
(
index_columns
)
with(ignore_dup_key=on)      

由于SQL Server不允許在包含重複值的資料表上建立唯一索引，是以，必須建立一個新的空表，新表時原始表的結構的複制，在部分列上建立忽略重複值的唯一索引。在執行插入操作時， IGNORE_DUP_KEY 選項會忽略重複的索引鍵值，并抛出警告（Warning）。

作者

：悅光陰

出處

：http://www.cnblogs.com/ljhdo/

本文版權歸作者和部落格園所有，歡迎轉載，但未經作者同意，必須保留此段聲明，且在文章頁面醒目位置顯示原文連接配接，否則保留追究法律責任的權利。