熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

在SQL Server 2000數據倉庫中使用分區

2013-11-15 14:40:20  來源: SQL Server 

  摘要本文介紹如何使用分區來改善 SQL Server Enterprise Edition 中數據倉庫的可管理性查詢性能和加載速度並討論關系型數據庫和分析服務多維數據集中的矢量架構的水平分區
  
  概述
  本文討論數據倉庫中數據分區的作用關系型數據倉庫和分析服務多維數據集都支持數據分區分區的邏輯概念在 Microsoft® SQL Server&#; 的兩個引擎中是相同的通過鍵(例如日期)對數據進行水平分區在關系型數據庫中分區是通過創建單獨的物理表(例如為每個月的數據創建一個表)並且定義一個成員表的聯合視圖來實現的與此類似SQL Server Enterprise Edition 中的分析服務支持顯式的多維數據集分區在關系型數據庫和聯機分析處理 (OLAP) 引擎中物理存儲的復雜性對於分析用戶是不可見的
  
  數據倉庫分區的優點
  大大縮短查詢時間
  
  減少加載時間改善數據庫的可維護性
  
  解決從活動數據庫中刪除舊數據時出現的數據修剪問題
  該技術需要創建比非分區系統更復雜的數據分階段應用程序本文介紹設計實現和維護水平分區數據倉庫的最佳方法
  
  因為有效的分區計劃可以極大地改善查詢性能所以我們極力建議您對大型分析服務系統進行分區盡管對於某些特定的數據倉庫維護問題對關系型數據倉庫進行分區是有效的解決方案但通常不推薦您這樣做
  
  在 SQL Server 關系型數據倉庫中使用分區
  分區視圖聯接來自一組成員的水平分區數據使數據看起來象來自同一張表SQL Server 區分本地分區視圖和分布式分區視圖在本地分區視圖中所有相關表和視圖駐留在 SQL Server 的同一實例上在分布式分區視圖中相關表中至少有一張表駐留在其他某個(遠程)服務器上建議您不要將分布式分區視圖用於數據倉庫應用程序
  
  矢量數據倉庫圍繞事實(標量)和矢量構建從物理上通常表示為星形架構和雪花形架構極少有同時包含事實和矢量的完全非正交化的平面表由於矢量架構是最常見的關系型數據倉庫結構本文集中討論這類架構的分區下面的建議也適用於其他通用數據倉庫架構
  
  分區的優點
  數據修剪
  許多數據倉庫管理員會定期將陳舊的數據歸檔例如一個單擊流數據倉庫可能只將詳細數據聯機保留三至四個月其他常見的規則可能是聯機保留 個月 個月或 當舊數據不在活動窗口中時就歸檔並從數據庫中刪除這種滾動窗口結構是大數據倉庫通常采取的做法
  
  在沒有分區表的情況下從數據庫中刪除舊數據的進程需要一個很大的 DELETE 語句例如
  
  DELETE FROM fact_table
  WHERE date_key < 19990101
  
  執行該語句開銷會非常大,可能比同一張表的加載進程需要更多的時間。Tw.wingWit.CoM相反,對於分區表,管理員重新定義 UNION ALL 視圖以排除最舊的表,然後將該表從數據庫中刪除(假設已確保備份該表),這個過程幾乎可以在瞬間完成。
  
  後面我們會討論到,維護分區表的費用也很高。如果數據修剪是采用分區的唯一原因,設計者應考慮以數據分解的方式從未分區的表中刪除舊數據。在低優先級進程上連續運行一個每次刪除 1000 行(用“set rowcount 1000”命令)的腳本,直至刪除所有希望刪除的數據。該技術可在大系統上有效運用,比創建必要的分區管理系統更為直接。根據加載量和系統使用狀況,該技術適合於某些系統,並應該考慮在系統上進行基准測試。
  
  加載速度
  加載數據最快的方法是將數據加載至空表或沒有索引的表。通過加載至較小的分區表,漸變加載進程的效率將大大提高。
  
  可維護性
  一旦已建成支持分區的數據倉庫分階段應用程序,整個系統將變得容易維護。維護活動(包括加載數據、備份和還原表)可以並行地執行,這樣可以極大地改善性能。漸變填充下行數據流多維數據集的進程可以被加速和簡化。
  
  查詢速度
  查詢速度不應該作為對數據倉庫關系型數據庫進行分區的理由。對於分區和未分區的事實表,查詢性能都差不多。在正確設計的分區數據庫中,關系引擎僅在查詢計劃中包括解析查詢所需的相關分區。例如,如果數據庫按月分區,查詢條件為 2000 年 1 月,則查詢計劃僅包括 2000 年 1 月的分區。結果查詢將對分區表正確執行,與在分區鍵上帶有簇索引的已索引合並表上執行的大體相同。
  
  分區的缺點
  復雜性
  分區的主要缺點是需要管理員創建應用程序來管理分區。在尚未設計、測試和試運行應用程序來管理分區之前,將在關系型數據庫中使用水平分區的數據倉庫投入正式運行是不恰當的。本文的目的之一就是討論與分區管理應用程序有關的問題和設計決策。
  
  查詢設計約束
  要獲得最佳的查詢性能,所有的查詢都應將條件直接放在事實表中的篩選鍵上。將約束放在第二張表(例如以日期為矢量的表)的查詢將包括所有分區。
  
  設計時要考慮的因素
  矢量數據倉庫圍繞事實(標量)和矢量構建,從物理上通常表示為星形架構和雪花形架構,極少有同時包含事實和矢量的完全非正交化的平面表。典型情況下,矢量數據倉庫的管理員僅對事實表進行分區;對矢量表進行分區幾乎沒有什麼好處。在某些情況下,對包含多於一千萬個成員的大型矢量表進行分區會有些好處。也可以對非矢量關系型數據倉庫進行分區,本文中的一般觀點仍然適用。
  
  只有充分考慮系統體系結構和設計目標,才能制訂有效的分區計劃。即使使用相同的架構設計,僅用於填充服務分析多維數據集的關系型數據倉庫可能采用一個不同於分析員直接查詢的數據倉庫的分區結構。帶有滾動窗口的系統必須按時間分區,其他系統則不一定。
  
  如果數據倉庫包括分析服務多維數據集,Microsoft 建議關系型數據倉庫和分析服務數據庫中的分區應該為並行結構。維護應用程序被簡化了:應用程序在關系型數據庫中創建新表的同時創建一個新多維數據集分區。管理員僅需要掌握一種分區策略。不過,一個應用程序也可能有充分的理由對兩個數據庫以不同方式進行分區,唯一降低的將是數據庫維護應用程序的復雜性。
  
  分區設計概述
  SQL Server 數據庫中的分區表可以使用可更新或可查詢(不可更新)的分區視圖。在這兩種情況下,表分區都是由每個分區都包含正確數據的 CHECK 約束來創建的。一個可更新的分區視圖支持對視圖進行 INSERT (或 UPDATE 或 DELETE)操作,並將操作推入至正確的基礎表。這很有益處,但數據倉庫應用程序通常需要進行批量加載,而這是無法通過視圖執行的。下表總結了可更新和可查詢分區視圖的要求、優點和缺點。
  
  要求 優點 缺點
  可更新的分區視圖
  CHECK 約束強制使用的分區鍵
  
  主鍵的分區鍵部分
  
  無其他數據庫限制的分區鍵部分
  
  在成員表上定義的 UNION ALL 視圖
  查詢性能:查詢計劃僅包括解析相關查詢所需的成員表。
  
  維護應用程序的簡易性:數據可以被加載至 UNION ALL 視圖,然後插入合適的成員表中
  加載性能:通過視圖加載數據的速度太慢,以至這種方式對大多數的數據倉庫應用程序來說是不實用的。
  
  靈活性:數據庫設計對分區鍵可能要求額外的約束。
  
  可查詢的分區視圖
  CHECK 約束強制使用的分區鍵
  
  在成員表上定義的 UNION ALL 視圖
  查詢性能:查詢計劃僅包括解析查詢所必要的成員表。
  
  加載性能:可高效地直接將數據批量加載至成員表。
  
  存儲:盡管推薦聲明主鍵並在主鍵上創建索引的做法,但分區視圖不要求主鍵索引。
  視圖最多可有 256 個成員表。
  
  必須創建維護應用程序來管理分區和加載。
  
  Microsoft 建議的做法是定義主鍵,並將事實表設計為本地(單個服務器上)的分區聯合視圖。大多數情況下,該定義會產生可更新的分區視圖,但數據倉庫維護應用程序應設計為直接將大多數數據批量加載至成員表(而不是通過視圖進行)。
  
  語法示例
  以下代碼示例用來說明定義成員表和聯合視圖以及將數據插入視圖的語法:
  
  -- 創建 1999 年事實表
  CREATE TABLE [dbo].[sales_fact_19990101] (
  [date_key] [int] NOT NULL
  CHECK ([date_key] BETWEEN 19990101 AND 19991231),
  [product_key] [int] NOT NULL ,
  [customer_key] [int] NOT NULL ,
  [promotion_key] [int] NOT NULL ,
  [store_key] [int] NOT NULL ,
  [store_sales] [money] NULL ,
  [store_cost] [money] NULL ,
  [unit_sales] [float] NULL
  )
  ALTER TABLE [sales_fact_19990101]
  ADD PRIMARY KEY (
  [date_key], [product_key], [customer_key], [promotion_key], [store_key])
  ;
  -- 創建 2000 年事實表
  CREATE TABLE [dbo].[sales_fact_20000101] (
  [date_key] [int] NOT NULL
  CHECK ([date_key] BETWEEN 20000101 AND 20001231),
  [product_key] [int] NOT NULL ,
  [customer_key] [int] NOT NULL ,
  [promotion_key] [int] NOT NULL ,
  [store_key] [int] NOT NULL ,
  [store_sales] [money] NULL ,
  [store_cost] [money] NULL ,
  [unit_sales] [float] NULL
  )
  ALTER TABLE [sales_fact_20000101]
  ADD PRIMARY KEY (
  [date_key], [product_key], [customer_key], [promotion_key], [store_key]
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22153.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.