知方可補不足~SQL為大數據引入分區表

回到目錄

一些概念

分區表在oracle和sqlserver中都上存在的，當數據表的數據量過大時，上千萬，上億的數據，在進行數據查詢時需要顯得比較慢，性能很差，這時是時候引入分區表了，分區表顧名思義，就是把物理表用一些文件NDF進行分塊存儲，以緩減ＩＯ的壓力，因為當你的ＳＱＬ文件過大的，這其實對系統的ＩＯ影響是最大的，這種分區表我感覺類似於數據的分片(mongodb)，它將有效的利用伺服器的ＣＰＵ多核資源，並行去處理你的請求，所以在大數據情況下，分區表是很好的一種選擇！

我們通常也把電腦的磁碟分成若幹的區，其中一種考慮也是為了性能，安全等

sql的分區表於若幹的文件組組成，它們可以被理解成依照某個條件（分區函數）來進行劃分的文件塊，當你進行curd操作時，ＳＱＬ會把它同時響應到對應的塊上去，文件組裡至少包含一個文件，當然可以是多個，它們也可以在不同的磁碟上。

說乾就乾

下麵來一步一步的實現一下分區表的建立邏輯

--建立兩個文件組
ALTER DATABASE Test ADD FILEGROUP BEFORE2000
ALTER DATABASE Test ADD FILEGROUP AFTER2000
ALTER DATABASE Test ADD FILEGROUP AFTER2010

--創建文件
ALTER DATABASE Test ADD FILE
(Name=N'Before2000',filename='c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Before2000.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) 
TO FILEGROUP Before2000
ALTER DATABASE Test ADD FILE
(Name=N'After2000',filename='c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\After2000.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) 
TO FILEGROUP After2000
ALTER DATABASE Test ADD FILE
(Name=N'After2010',filename='c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\After2010.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) 
TO FILEGROUP After2010

上面代碼在磁碟上建立了兩個文件組，用來存儲2000年以前，2000-2010年，以2010年以後的數據，而ndf是分區表文件的類型，一個分區表文件組可以由多個ndf文件構成

--編寫分區函數
CREATE PARTITION FUNCTION RangeTime (DATETIME) AS RANGE LEFT FOR VALUES ('2000-01-01','2010-01-01')
--編寫分區方案,  分區方案也就是將分區函數與文件組進行一個關聯
CREATE PARTITION SCHEME RangeSchema_CreateTime
AS PARTITION RangeTime
TO (BEFORE2000,AFTER2000,AFTER2010)

上面分區函數說明以哪裡依據進行分區，而分區方案是將它與分區文件組進行接合，或者和數據表進行打通，以後我們用的時候，直接用分區函數（表名）即可。

--創建分區表，先建表，再設主鍵，否則會出錯
CREATE TABLE Order
    (
      ID VARCHAR(50) ,
      UserId VARCHAR(50) ,
      CreateTime DATETIME
    )
ON  RangeSchema_CreateTime(CreateTime)

上面代碼建立一張表，併進行分區的配置，註意，在建立表後，再建立主鍵，我們填充一些數據就可以測試了，下麵介紹幾個常用的命令　

返回2001-2-2日這條數據會被分配到哪個分區了

--測試某個對象放在哪個分區里
SELECT  $PARTITION.RangeTime('2001-2-2')

查看指定分區內的數據

--查看某個分區表裡存放的數據
SELECT  *
FROM    shop
WHERE   $PARTITION.RangeTime(CreateTime) = 1

查看某個分區里，記錄的個數

--查看某個分區表的個數
SELECT  $PARTITION.RangeTime(CreateTime) AS 分區編號 ,
        COUNT(id) AS 記錄數
FROM    shop
GROUP BY $PARTITION.RangeTime(CreateTime)  

當數據量達到一定指數時，我們必須要進行調整，而選擇哪種方法是靠技術決策人的，我們每個開發人員都應該把自己當成是技術的決策人，對自己的代碼和自己的人生負責！

感謝您的閱讀！

回到目錄