知方可補(bǔ)不足~SQL為大數(shù)據(jù)引入分區(qū)表

一些概念

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

我們通常也把電腦的磁盤分成若干的區(qū)，其中一種考慮也是為了性能，安全等

sql的分區(qū)表于若干的文件組組成，它們可以被理解成依照某個(gè)條件（分區(qū)函數(shù)）來進(jìn)行劃分的文件塊，當(dāng)你進(jìn)行curd操作時(shí)，ＳＱＬ會(huì)把它同時(shí)響應(yīng)到對(duì)應(yīng)的塊上去，文件組里至少包含一個(gè)文件，當(dāng)然可以是多個(gè)，它們也可以在不同的磁盤上。

說干就干

下面來一步一步的實(shí)現(xiàn)一下分區(qū)表的建立邏輯

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

--創(chuàng)建文件
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

上面代碼在磁盤上建立了兩個(gè)文件組，用來存儲(chǔ)2000年以前，2000-2010年，以2010年以后的數(shù)據(jù)，而ndf是分區(qū)表文件的類型，一個(gè)分區(qū)表文件組可以由多個(gè)ndf文件構(gòu)成

--編寫分區(qū)函數(shù)
CREATE PARTITION FUNCTION RangeTime (DATETIME) AS RANGE LEFT FOR VALUES ('2000-01-01','2010-01-01')
--編寫分區(qū)方案,  分區(qū)方案也就是將分區(qū)函數(shù)與文件組進(jìn)行一個(gè)關(guān)聯(lián)
CREATE PARTITION SCHEME RangeSchema_CreateTime
AS PARTITION RangeTime
TO (BEFORE2000,AFTER2000,AFTER2010)

上面分區(qū)函數(shù)說明以哪里依據(jù)進(jìn)行分區(qū)，而分區(qū)方案是將它與分區(qū)文件組進(jìn)行接合，或者和數(shù)據(jù)表進(jìn)行打通，以后我們用的時(shí)候，直接用分區(qū)函數(shù)（表名）即可。

--創(chuàng)建分區(qū)表，先建表，再設(shè)主鍵，否則會(huì)出錯(cuò)
CREATE TABLE Order
    (
      ID VARCHAR(50) ,
      UserId VARCHAR(50) ,
      CreateTime DATETIME
    )
ON  RangeSchema_CreateTime(CreateTime)

上面代碼建立一張表，并進(jìn)行分區(qū)的配置，注意，在建立表后，再建立主鍵，我們填充一些數(shù)據(jù)就可以測試了，下面介紹幾個(gè)常用的命令　

返回2001-2-2日這條數(shù)據(jù)會(huì)被分配到哪個(gè)分區(qū)了

--測試某個(gè)對(duì)象放在哪個(gè)分區(qū)里
SELECT  $PARTITION.RangeTime('2001-2-2')

查看指定分區(qū)內(nèi)的數(shù)據(jù)

--查看某個(gè)分區(qū)表里存放的數(shù)據(jù)
SELECT  *
FROM    shop
WHERE   $PARTITION.RangeTime(CreateTime) = 1

查看某個(gè)分區(qū)里，記錄的個(gè)數(shù)

--查看某個(gè)分區(qū)表的個(gè)數(shù)
SELECT  $PARTITION.RangeTime(CreateTime) AS 分區(qū)編號(hào) ,
        COUNT(id) AS 記錄數(shù)
FROM    shop
GROUP BY $PARTITION.RangeTime(CreateTime)

當(dāng)數(shù)據(jù)量達(dá)到一定指數(shù)時(shí)，我們必須要進(jìn)行調(diào)整，而選擇哪種方法是靠技術(shù)決策人的，我們每個(gè)開發(fā)人員都應(yīng)該把自己當(dāng)成是技術(shù)的決策人，對(duì)自己的代碼和自己的人生負(fù)責(zé)！

感謝您的閱讀！

回到目錄

posted @ 2016-02-24 14:05 張占嶺閱讀(1068) 評(píng)論(0) 收藏舉報(bào)

刷新頁面返回頂部

知方可補(bǔ)不足~SQL為大數(shù)據(jù)引入分區(qū)表

一些概念

說干就干

公告