SQLServer表分區

作者: jackyong 來源: 博客園發布時間: 2010-09-12 13:12 閱讀: 16253 次推薦: 3 原文鏈接 [收藏]

　　最近使用SQL SERVER一個的緩存，數據量一天100w的速度增長，同時接受客戶查詢，速度由于數據量越來越大越來越慢，這里感謝博客園的 KillKill 和邀約, 最近讀了一套書不錯，感興趣的同學可以讀讀<<活法>>

　　回顧下經常使用的索引：

　　一、聚集索引
　　聚集索引的頁級別包含了索引鍵，還包含數據頁，因此，關于除了鍵值以外聚集索引的葉級別還存放了什么的答案就是一切，也就是說，每行的所有字段都在葉級別種。
　　另一種說話是:數據本身也是聚集索引的一部分，聚集索引基于鍵值保持表中的數據有序。
　　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 月已經分開了。

　　后續繼續補充！