SQL Server實戰： 表分區

最近使用SQL SERVER一個的緩衝，資料量一天100w的速度增長，同時接受客戶查詢，速度由於資料量越來越大越來越慢。

　　回顧下經常使用的索引

　　一 .叢集索引

　　叢集索引的頁層級包含了索引鍵，還包含資料頁，因此，關於 除了索引值以外叢集索引的分葉層級還存放了什麼的答案就是一切，也就是說，每行的所有欄位都在分葉層級種。

　　另一種說話是:資料本身也是叢集索引的一部分，叢集索引基於索引值保持表中的資料有序。

　　SQL SERVER 中，所有的叢集索引都是唯一的，如果在建立叢集索引時沒有指定UNIQUE 關鍵字，SQL SERVER 會在需要時通過往記錄中添加一個唯一識別碼(Uniqueifier)在內部保證索引的唯一性，該唯一識別碼是一個4位元組的值，作為附加在叢集索引鍵的欄位添加到資料中，只有那些聲明為索引鍵欄位並擁有重複值的行才會被添加。

　　二 .非叢集索引

　　對於非叢集索引，分葉層級不包含全部的資料。除了索引值以外，每個分葉層級(樹的最低層)中的索引行包含了一個書籤(bookmark),告訴SQL Server 可以在哪裡找到與索引鍵相應的資料行。一個書籤課能有兩種格式。如果表上存在叢集索引，書籤就是相應的資料行的叢集索引鍵。如果表是堆(heap)結構 ，就是沒有叢集索引的情況下 ，書籤就是一個行標識符 row identifier,rid ，以 檔案號 頁號 槽號 的格式來定位實際的行。

　　非叢集索引的存在與否並不影響資料分頁的組織，因此每張表上並不像叢集索引那樣只局限於擁有一個非叢集索引，SQL Server 2005 每張表能夠包含249 個非叢集索引 SQL Server 2008 每張表能夠包含999 個非叢集索引 ，但是實際上所用到的比這個數要少的多。

　　三 .包含索引

　　索引鍵欄位數量限制是16個，總共900個位元組大小 ，包含性列只在分葉層級中出現而且不以任何方式控制索引行的排序。它們的目的是使分葉層級能夠包含更多的資訊從而更大地發揮覆蓋索引(Covering index)的索引調優能力.覆蓋索引是一種非叢集索引，在其分葉層級就可以找到滿足查詢的全部資訊，這樣sql server就根本沒有必要訪問資料分頁了，在一些情況下 sql serer 會悄悄的為索引添加一個包含性列。這可能發生在索引建立於分區表 也就是我今天是發的部落格 O(∩_∩)O (partitioned table )上沒有指定 on filegroup 或者 no partition_scheme 的情況下。

　　一 .SQL SERVER 表分區介紹：

　　SQL Server 引入的表分區技術，讓使用者能夠把資料分散存放到不同的物理磁碟中，提高這些磁碟的平行處理效能以最佳化查詢效能……

　　二 .SQL SERVER 資料庫表分區由三個步驟來完成:

　　1.建立分區函數

　　2.建立分區架構

　　3.對錶進行分區

　　基於緩衝更新機制，我使用時間來進行分區，這裡大家根據業務的要求使用合適的欄位來作為分區

　　建立資料庫分區檔案數量，這裡儲存一年的資料分成十二個分區，需要現在D盤建立好Data 的檔案夾 裡麵包含Primary 檔案夾和 FG1 FG2 FG3 FG4............

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'AirAvCache')
DROP DATABASE [AirAvCache]
GO
CREATE DATABASE [AirAvCache]
ON PRIMARY
(NAME='Data Partition DB Primary FG',
FILENAME=
'D:\Data\Primary\AirAvCache Primary FG.mdf',
SIZE=5,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG1]
(NAME = 'AirAvCache FG1',
FILENAME =
'D:\Data\FG1\AirAvCache FG1.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG2]
(NAME = 'AirAvCache FG2',
FILENAME =
'D:\Data\FG2\AirAvCache FG2.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG3]
(NAME = 'AirAvCache FG3',
FILENAME =
'D:\Data\FG3\AirAvCache FG3.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG4]
(NAME = 'AirAvCache FG4',
FILENAME =
'D:\Data\FG4\AirAvCache FG4.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG5]
(NAME = 'AirAvCache FG5',
FILENAME =
'D:\Data\FG5\AirAvCache FG5.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),

FILEGROUP [AirAvCache FG6]
(NAME = 'AirAvCache FG6',
FILENAME =
'D:\Data\FG6\AirAvCache FG6.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),

FILEGROUP [AirAvCache FG7]
(NAME = 'AirAvCache FG7',
FILENAME =
'D:\Data\FG7\AirAvCache FG7.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),

FILEGROUP [AirAvCache FG8]
(NAME = 'AirAvCache FG8',
FILENAME =
'D:\Data\FG8\AirAvCache FG8.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),

FILEGROUP [AirAvCache FG9]
(NAME = 'AirAvCache FG9',
FILENAME =
'D:\Data\FG9\AirAvCache FG9.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),

FILEGROUP [AirAvCache FG10]
(NAME = 'AirAvCache FG10',
FILENAME =
'D:\Data\FG10\AirAvCache FG10.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),

FILEGROUP [AirAvCache FG11]
(NAME = 'AirAvCache FG11',
FILENAME =
'D:\Data\FG11\AirAvCache FG11.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),

FILEGROUP [AirAvCache FG12]
(NAME = 'AirAvCache FG12',
FILENAME =
'D:\Data\FG12\AirAvCache FG12.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 )

 

　建立好後

 

　　開啟FG1 檔案夾 看到多了AirAvCacheFG1.ndf 檔案

 

　　建立分區函數

USE AirAvCache
GO

-- 建立函數

CREATE PARTITION FUNCTION [AirAvCache Partition  Range](DATETIME)
AS RANGE LEFT FOR VALUES ('2010-09-01','2010-10-01','2010-11-01','2010-12-01','2011-01-01','2011-02-01','2011-03-01','2011-04-01','2011-05-01','2011-06-01','2010-07-01');

 

　　建立分區架構

CREATE PARTITION SCHEME [AirAvCache Partition Scheme]
AS PARTITION [AirAvCache Partition  Range]
TO ([AirAvCache FG1], [AirAvCache FG2], [AirAvCache FG3],[AirAvCache FG4],[AirAvCache FG5],[AirAvCache FG6],[AirAvCache FG7],[AirAvCache FG8],
[AirAvCache FG9],[AirAvCache FG10],[AirAvCache FG11],[AirAvCache FG12]);

 

 

　　建立一個使用AirAvCache Partitiion Scheme 架構的表

CREATE TABLE [dbo].[AvCache](
    [CityPair] [varchar](6)  NOT NULL,
    [FlightNo] [varchar](10)  NULL,
    [FlightDate] [datetime] NOT NULL,
    [CacheTime] [datetime] NOT NULL   DEFAULT (getdate()),
    [AVNote] [varchar](300)  NULL
)  ON [AirAvCache Partition Scheme] (FlightDate); --注意這裡使用[AirAvCache Partition Scheme]架構，根據FlightDate 分區

 

　　查詢分區情況

-- 查看使用方式
SELECT *, $PARTITION.[AirAvCache Partition  Range](FlightDate)
FROM dbo.AVCache

 

　　可以看到9 月和 10 月已經分開了。