分區和現有索引

我有一張表，它消耗了將近 900 GB 的空間，並且有很多歷史數據，這些數據大多是不相關的。由於執行批量刪除會導致很多繁重的工作，因此我嘗試使用水平分區的概念來創建多個分區，每個分區都包含早期數據（例如：2017,2018…2021），以便我可以簡單地截斷那些不到一年的分區，這樣我就只能保留相關的操作數據。

這是我心中的行動計劃

• 創建一個分區函式，在年度範圍內拆分數據
• 定義分區方案。
• 修改表以使用和應用分區
• 截斷包含不相關歷史數據的分區

我在 SQL Server 中說明了上述操作（截斷部分除外），例如

-> Create partition
CREATE PARTITION FUNCTION Yearly_Exp (datetime2)
    AS RANGE RIGHT FOR VALUES
    ('2016-01-01 00:00:00', '2017-01-01 00:00:00', '2018-01-01 00:00:00'
     '2019-01-01 00:00:00', '2020-01-01 00:00:00', '2021-01-01 00:00:00');
     

-> Define the partition Scheme.
CREATE PARTITION SCHEME Yearly_Scheme
    AS PARTITION Yearly_Exp ALL TO ([PRIMARY]);
     

-> Modify the table to use and apply partition
CREATE UNIQUE CLUSTERED INDEX Partition_Ind ON existing_table(some_timestamp_col, id) ON Yearly_Scheme(some_timestamp_col);

現在回到我的實際問題，因為該表已經存在了一段時間，所以表中有預先存在的索引。更準確地說，該表有

1. 兩個非聚集索引，其中一個索引將我的分區鍵作為索引列
2. 通過主鍵約束應用的一個聚集索引。

我應該在分區之前採取任何措施來更改這些索引嗎？（因為已經存在以分區鍵作為索引列的索引）還是應該保持原樣？

分區后索引性能會受到影響嗎？

總結一下您的目標，您希望避免“繁重”的大型或批量刪除，以最大限度地減少阻塞並保持應用程序的正常執行時間。

我認為你目前的計劃不會實現這個目標

我認為您目前的計劃不會實現該目標，因為分區涉及重新集群表，這是一種數據大小操作，並且可能會產生很大影響。

SQL Server 只能有一個聚集索引。您的表已經在您的 PK 上有一個聚集索引。因此，您需要刪除現有的聚群索引（臨時創建一個堆），然後添加新的聚群索引，然後添加一個非聚群 PK 約束來替換已刪除的聚群 PK。表暫時是堆的時間會對性能產生影響，直到創建新的 PK 和新的聚集索引。

但是該操作也會導致所有非聚集索引被重寫兩次。非聚集索引的儲存方式，它們包含聚集鍵或行 ID（用於堆）以指向行的其餘部分。刪除現有聚集索引將重寫非聚集索引以將聚集鍵更改為 RID，然後添加新聚集索引將再次重寫它們以將 RID 引用更改為新聚集鍵。所有非聚集索引的重寫將在單個事務中發生，這在大表上將是一個長/大事務，並且可能導致顯著的日誌增長。

從功能上講，您可以添加步驟以刪除現有的集群 PK，然後將其作為非集群 PK 添加回來，您的計劃可能會產生所需的最終結果，但會比您預期的影響更大。

相反，您應該考慮遷移到新表。

相反，您應該考慮遷移到新表。我寫了一篇關於將列從 int 更改為 bigint的詳細部落格文章，無需停機，但可以根據您的需要使用相同的過程。

1. 創建一個具有新（分區）結構的新空表。
2. 在舊表上創建觸發器以將插入/更新/刪除雙重寫入新表。
3. 將您想要保留的數據從舊表遷移到新表。如果有您現在要清除的數據，請不要費心遷移它。使用批處理遍歷 PK 以將所有數據複製到新表（這對於identity列最簡單，但對於任何 PK 都可以）。
4. 將舊表重命名為新名稱，並將新表重命名為原始名稱。
5. 放下舊桌子。

這仍然是（單個）表重建，但通過使“重建整個表”過程成為單個後台程序來保留正常執行時間。可以在表重命名之前的幾天或幾週內以可恢復、可控的方式建構新表。它還將有影響的“切換”減少為相對快速的表重命名，而不是幾次大型索引重建。

引用自：https://dba.stackexchange.com/questions/299454