<rp id="wnpn7"><ruby id="wnpn7"></ruby></rp>
<progress id="wnpn7"><track id="wnpn7"><rt id="wnpn7"></rt></track></progress>
<ruby id="wnpn7"></ruby>
<ruby id="wnpn7"><blockquote id="wnpn7"><div id="wnpn7"></div></blockquote></ruby>

    1. <em id="wnpn7"><ruby id="wnpn7"><input id="wnpn7"></input></ruby></em>
      1. <button id="wnpn7"><acronym id="wnpn7"></acronym></button><button id="wnpn7"><acronym id="wnpn7"></acronym></button>

        <rp id="wnpn7"><acronym id="wnpn7"></acronym></rp>

        <li id="wnpn7"><object id="wnpn7"><u id="wnpn7"></u></object></li>
        VB.net 2010 視頻教程 VB.net 2010 視頻教程 python基礎視頻教程
        SQL Server 2008 視頻教程 c#入門經典教程 Visual Basic從門到精通視頻教程
        當前位置:
        首頁 > 數據庫 > SQL教程 >
        • sql教程之SQL Server大表如何快速刪除數據

        本站最新發布    SQL Server 2016數據庫視頻教程

        試聽地址   http://www.squ68.com/eschool/SQLxin3721/
         
        在SQL Server中,如何快速刪除大表中的數據呢?  回答這個問題前,我們必須弄清楚上下文環境和以及結合實際、具體的需求,不同場景有不同的應對方法。
         
         
        1: 整張表的數據全部刪除
         
         
        如果是整張表的數據全部清空、刪除,這種場景倒是非常簡單,TRUNCATE TABLE肯定是最快的。 反而用DELETE處理的話,就是一個糟糕的策略。
         
         
        2: 大表中刪除一部分數據
         
         
        對于場景1、非常簡單,但是很多實際業務場景,并不能使用TRUNCATE這種方法,實際情況可能只是刪除表中的一部分數據或者進行數據歸檔后的刪除。假設我們遇到的表為TEST,需要刪除TEST表中的部分數據。那么首先我們需要對表的數據量和被刪除的數據量做一個匯總統計,具體,我們應該采用下面方法:
         
        ·           檢查表的數據量,以及要刪除的數據量。然后計算刪除的比例,
         
            sp_spaceused 'dbo.TEST';
         
            SELECT COUNT(*) AS DELETE_RCD WHERE TEST WHERE ......<刪除條件>
         
         
        2.1 刪除大表中絕大部分的數據,但是這個絕大部分怎么定義不好量化,所以我們這里就量化為60%。如果刪除的數據比例超過60%,就采用下面方法:
         
        1: 新建表TEST_TMP
         
        2:  將要保留的數據轉移到TEST_TMP
         
        3: 將原表TEST重命名為TEST_OLD, 而將TEST_TMP重命名為TEST
         
        4: 檢查相關的觸發器、約束,進行觸發器或約束的重命名
         
        5: 核對操作是否正確后,原表(TEST_OLD)要么TRUANCATE后,再DROP掉。要么保留一段時間,保險起見。
         
        注:至于這個比例60%是怎么來的。這個完全是個經驗值,有簡單的測試,但是沒有很精確和科學的概率統計驗證。
         
         
        另外,還要考慮業務情況,如果一直有應用程序訪問這個表,其實這種方式也是比較麻煩的,因為涉及數據的一致性,業務中斷等等很多情況。但是,如果程序較少訪問,或者在某個時間段沒有訪問,那么完全可以采用這種方法。
         
         
        2.2 刪除大表中部分數據,如果比例不超過60%
         
         
        1:先刪除或禁用無關索引(無關索引,這里指執行計劃不用到的索引,這里是指對當前DELETE語句無用的索引)。因為DELETE操作屬于DML操作,而且大表的索引一般也非常大,大量DELETE將會對索引進行維護操作,產生大量額外的IO操作。
         
        2:用小批量,分批次刪除(批量刪除比一次性刪除性能要快很多)。不要一次性刪除大量數據。一次性刪除大量記錄。會導致鎖的粒度范圍很大,并且鎖定的時間非常長,而且還可能產生阻塞,嚴重影響業務等等。而且數據庫的事務日志變得非常大。執行的時間變得超長,性能非常糟糕。
         
        批量刪除時,到底一次性刪除多少數量的記錄數,SQL效率最高呢?  這個真沒有什么規則計算,個人測試對比過,一次刪除10000或100000,沒有發現什么特別規律。(有些你發現的“規律”,換個案例,發現不一樣的結果,這個跟環境有關,有時候可能是一個經驗值)。不過一般用10000,在實際操作過程,個人建議可以通過做幾次實驗對比后,選擇一個合適的值即可。
         
        案例1:
         
        DECLARE @delete_rows INT;
        DECLARE @delete_sum_rows INT =0;
        DECLARE @row_count INT=100000
         
        WHILE 1 = 1
            BEGIN
                DELETE TOP ( @row_count )
                FROM    dbo.[EmployeeDayData]
                WHERE    WorkDate < CONVERT(DATETIME, '2012-01-01 00:00:00',120);
                    
                SELECT  @delete_rows = @@ROWCOUNT;
                    
                    SET @delete_sum_rows +=@delete_rows
                    IF @delete_rows = 0
                    BREAK;
                END;
        SELECT @delete_sum_rows;
         
         
         
        案例2:
         
        DECLARE @r INT;
        DECLARE @Delete_ROWS  BIGINT;
         
        SET @r = 1;
        SET @Delete_ROWS =0
        WHILE @r > 0
        BEGIN
            BEGIN TRANSACTION;
                DELETE TOP (10000) -- this will change
                   YourSQLDba..YdYarnMatch
                   WHERE Remark='今日未入' and Operation_Date<CONVERT(datetime, '2019-05-30',120);
         
                  SET @r = @@ROWCOUNT;
                  
                  SET @Delete_ROWS += @r;
         
            COMMIT TRANSACTION;
            
            PRINT(@Delete_ROWS);
         
        END
         
         
        該表有下面兩個索引
         
        USE [YourSQLDba]
        GO
         
         
        IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[YdYarnMatch]') AND name = N'IX_YdYarnMatch_N2')
        DROP INDEX [IX_YdYarnMatch_N2] ON [dbo].[YdYarnMatch] WITH ( ONLINE = OFF )
        GO
         
        USE [YourSQLDba]
        GO
         
         
        CREATE NONCLUSTERED INDEX [IX_YdYarnMatch_N2] ON [dbo].[YdYarnMatch] 
        (
            [Job_No] ASC,
            [GK_No] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
        GO
         
        USE [YourSQLDba]
        GO
         
        IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[YdYarnMatch]') AND name = N'IX_YdYarnMatch_N1')
        DROP INDEX [IX_YdYarnMatch_N1] ON [dbo].[YdYarnMatch] WITH ( ONLINE = OFF )
        GO
         
        USE [YourSQLDba]
        GO
         
        CREATE NONCLUSTERED INDEX [IX_YdYarnMatch_N1] ON [dbo].[YdYarnMatch] 
        (
            [Operation_Date] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
        GO
         
         
        重點:實踐證明,如果新建一個索引,能夠避免批量刪除過程中執行計劃走全表掃描,也能大大加快刪除的速度。個人對這個案例進行了測試、驗證。發現加上合適索引后,讓DELETE語句走Index Seek后,刪除效率確實大大提升。
         
         
        刪除索引IX_YdYarnMatch_N2,保留索引IX_YdYarnMatch_N1,但是發現SQL執行計劃走全表掃描,執行SQL時,刪除非常慢
         
        clip_image001
         
        刪除索引IX_YdYarnMatch_N1,重新創建索引IX_YdYarnMatch_N1后,執行計劃走Index Seek,刪除效率大大提示。
         
        CREATE NONCLUSTERED INDEX [IX_YdYarnMatch_N1] ON [dbo].[YdYarnMatch]
        (
            [Operation_Date] ASC ,
            Remark
           
        )
         
        注意:此處索引名相同,但是索引對應的字段不一樣。
         
        clip_image002
         
         
        所以正確的做法是:
         
        1:先刪除或禁用無關索引(對當前DELETE語句無用的索引),刪除前生成對應的SQL,以便完成數據刪除后,重新創建索引。注意,前提是在操作階段,這個操作不會影響應用。否則應重新考慮。
         
        2:檢查測試當前SQL的執行計劃,能否創建合適的索引,加快DELETE操作。如上面例子所示
         
        3:批量循環刪除記錄。
         
        4:在ORACLE數據庫中,有些表的設置可以減少對應DML操作的日志生成量,但是SQL Server沒有這些功能,但是要及時關注或調整事務日志的備份情況。
         
            如果我們能將將數據庫的恢復模式設置為SIMPLE,那么可以減少日志備份引起的額外的IO開銷。但是很多生產環境不能切換用戶數據庫的恢復模式。
         
         
        其實說了這么多,SQL Server中大表快速刪除索引的方法就是將一次性刪除改成分批刪除,逐次提交而已。其它的方式都是一些輔助方式而已。另外,如果你想親自做一些細節測試,建議參考博客https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes
         
         
         
         
        作者:瀟湘隱者

        相關教程
                
        免费看成年人视频大全_免费看成年人视频在线观看