??????? Csdn Blog在2007年，由于訪問(wèn)量和數(shù)據(jù)量的大幅度增長(zhǎng)，使得我們?cè)械脑?text 0.96版本上修改的代碼基本不堪重負(fù)。
在數(shù)據(jù)庫(kù)方面主要表現(xiàn)為，單單文章表，2007年1年的數(shù)據(jù)已經(jīng)達(dá)到了30G的量（最后的解決方案是對(duì)把文章表分為兩個(gè)表，分別存放文章相關(guān)信息和專門存放文章內(nèi)容的表，然后對(duì)兩個(gè)表作分區(qū)）。熟悉.Text得朋友可能知道，有一個(gè)專門記錄流量的表，頁(yè)面內(nèi)的每一次點(diǎn)擊，直接記錄到數(shù)據(jù)庫(kù)里面，反映到頁(yè)面上就是單個(gè)用戶的博客流量，分別是EntryID,BlogID,DateTime，表名稱叫做blog_viewcount，這個(gè)表我們現(xiàn)在已經(jīng)到了4億的數(shù)據(jù)，之前僅僅是優(yōu)化索引和索引試圖來(lái)在真實(shí)的環(huán)境中查看到底sql2005單表能負(fù)載多大的量。
最后得出結(jié)論，在sql2005中，單表的量最多不能超過(guò)200w數(shù)據(jù)。
而文章表和blog_viewcount這個(gè)表，我們現(xiàn)在采用2005得新特性，分區(qū)表來(lái)實(shí)現(xiàn)。事實(shí)上，反映還不錯(cuò)。以下寫(xiě)出創(chuàng)建分區(qū)表的步鄹（我們用blog_viewcount來(lái)做實(shí)例）：
先根據(jù)表數(shù)據(jù)的量大小，來(lái)拆分每一個(gè)分區(qū)，保證分區(qū)之間在200w以下的數(shù)據(jù)
CREATE
?PARTITION?
FUNCTION
?OneYearDateRangePFN(
datetime
)
?
AS
?RANGE?
LEFT
?
FOR
?
VALUES
?
(?
'
20070131?23:59:59.997
'
,
'
20070228?23:59:59.997
'
,?
'
20070331?23:59:59.997
'
,
'
20070430?23:59:59.997
'
,
'
20070531?23:59:59.997
'
,?
'
20070630?23:59:59.997
'
,
'
20070731?23:59:59.997
'
,
'
20070831?23:59:59.997
'
,
'
20070930?23:59:59.997
'
,
'
20071031?23:59:59.997
'
,
'
20071130?23:59:59.997
'
,
'
20071231?23:59:59.997
'
)?
然后我們需要添加相應(yīng)的文件組
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200701
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200702
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200703
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200704
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200705
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200706
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200707
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200708
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200709
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200710
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200711
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200712
]
第三步，我們需要在服務(wù)器上建立出單獨(dú)的文件（涉及到的朋友可能知道，我們?nèi)绻盐募植荚诓煌拇疟P上，可以最大限度的提高磁頭的讀寫(xiě)能力；另：這里微軟建議數(shù)據(jù)庫(kù)服務(wù)器最好作Raid0 + Raid1）
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200701
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200701.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200701
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200702
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200702.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200702
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200703
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200703.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200703
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200704
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200704.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200704
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200705
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200705.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200705
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200706
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200706.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200706
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200707
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200707.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200707
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200708
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200708.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200708
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200709
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200709.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200709
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200710
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200710.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200710
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200711
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200711.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200711
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200712
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200712.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200712
]
第四步，創(chuàng)建關(guān)聯(lián)
CREATE
?PARTITION?SCHEME?OneYearDateRangePScheme?
AS
PARTITION?OneYearDateRangePFN?
ALL
?
TO
?(
[
PRIMARY
]
)
第五步，創(chuàng)建表和索引（我這里只列出如何創(chuàng)建表）
CREATE
?
TABLE
?
[
dbo
]
.
[
Blog_ViewCount2
]
??
(
[
EntryID
]
?
[
int
]
?
NOT
?
NULL
,
[
BlogID
]
?
[
int
]
?
NOT
?
NULL
,
[
ViewDate
]
?
[
datetime
]
?
NOT
?
NULL
)
ON
?OneYearDateRangePScheme?(ViewDate)
GO
如果大家希望查詢此表中的相關(guān)數(shù)據(jù)，可以使用如下語(yǔ)句
select
?$partition.OneYearDateRangePFN(ViewDate)?
from
?
[
Blog_ViewCount2
]
?a?
order
?
by
?a.ViewDate?
asc
關(guān)于增量問(wèn)題，我會(huì)在下一篇文章中，和大家分享。
參考資料：
http://www.microsoft.com/china/msdn/library/data/sqlserver/sql2k5partition.mspx?mfr=true
http://www.builder.com.cn/2007/0724/436577.shtml
http://www.agilelabs.cn/blogs/woody/archive/2006/08/24/1574.aspx
http://net.stuun.cn/database/Mssql/jiqiao/39408.html
在數(shù)據(jù)庫(kù)方面主要表現(xiàn)為，單單文章表，2007年1年的數(shù)據(jù)已經(jīng)達(dá)到了30G的量（最后的解決方案是對(duì)把文章表分為兩個(gè)表，分別存放文章相關(guān)信息和專門存放文章內(nèi)容的表，然后對(duì)兩個(gè)表作分區(qū)）。熟悉.Text得朋友可能知道，有一個(gè)專門記錄流量的表，頁(yè)面內(nèi)的每一次點(diǎn)擊，直接記錄到數(shù)據(jù)庫(kù)里面，反映到頁(yè)面上就是單個(gè)用戶的博客流量，分別是EntryID,BlogID,DateTime，表名稱叫做blog_viewcount，這個(gè)表我們現(xiàn)在已經(jīng)到了4億的數(shù)據(jù)，之前僅僅是優(yōu)化索引和索引試圖來(lái)在真實(shí)的環(huán)境中查看到底sql2005單表能負(fù)載多大的量。
最后得出結(jié)論，在sql2005中，單表的量最多不能超過(guò)200w數(shù)據(jù)。
而文章表和blog_viewcount這個(gè)表，我們現(xiàn)在采用2005得新特性，分區(qū)表來(lái)實(shí)現(xiàn)。事實(shí)上，反映還不錯(cuò)。以下寫(xiě)出創(chuàng)建分區(qū)表的步鄹（我們用blog_viewcount來(lái)做實(shí)例）：
先根據(jù)表數(shù)據(jù)的量大小，來(lái)拆分每一個(gè)分區(qū)，保證分區(qū)之間在200w以下的數(shù)據(jù)
CREATE
?PARTITION?
FUNCTION
?OneYearDateRangePFN(
datetime
)
?
AS
?RANGE?
LEFT
?
FOR
?
VALUES
?
(?
'
20070131?23:59:59.997
'
,
'
20070228?23:59:59.997
'
,?
'
20070331?23:59:59.997
'
,
'
20070430?23:59:59.997
'
,
'
20070531?23:59:59.997
'
,?
'
20070630?23:59:59.997
'
,
'
20070731?23:59:59.997
'
,
'
20070831?23:59:59.997
'
,
'
20070930?23:59:59.997
'
,
'
20071031?23:59:59.997
'
,
'
20071130?23:59:59.997
'
,
'
20071231?23:59:59.997
'
)?
然后我們需要添加相應(yīng)的文件組
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200701
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200702
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200703
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200704
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200705
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200706
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200707
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200708
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200709
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200710
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200711
]
ALTER
?
DATABASE
?newcsdnblog?
ADD
?FILEGROUP?
[
ViewCount200712
]
第三步，我們需要在服務(wù)器上建立出單獨(dú)的文件（涉及到的朋友可能知道，我們?nèi)绻盐募植荚诓煌拇疟P上，可以最大限度的提高磁頭的讀寫(xiě)能力；另：這里微軟建議數(shù)據(jù)庫(kù)服務(wù)器最好作Raid0 + Raid1）
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200701
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200701.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200701
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200702
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200702.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200702
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200703
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200703.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200703
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200704
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200704.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200704
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200705
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200705.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200705
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200706
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200706.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200706
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200707
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200707.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200707
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200708
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200708.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200708
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200709
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200709.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200709
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200710
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200710.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200710
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200711
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200711.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200711
]
ALTER
?
DATABASE
?newcsdnblog
ADD
?
FILE
?
(NAME?
=
?N
'
ViewCount200712
'
,FILENAME?
=
?N
'
f:SmallTableViewCount200712.ndf
'
,SIZE?
=
?5MB,MAXSIZE?
=
?100MB,FILEGROWTH?
=
?5MB)
TO
?FILEGROUP?
[
ViewCount200712
]
第四步，創(chuàng)建關(guān)聯(lián)
CREATE
?PARTITION?SCHEME?OneYearDateRangePScheme?
AS
PARTITION?OneYearDateRangePFN?
ALL
?
TO
?(
[
PRIMARY
]
)
第五步，創(chuàng)建表和索引（我這里只列出如何創(chuàng)建表）
CREATE
?
TABLE
?
[
dbo
]
.
[
Blog_ViewCount2
]
??
(
[
EntryID
]
?
[
int
]
?
NOT
?
NULL
,
[
BlogID
]
?
[
int
]
?
NOT
?
NULL
,
[
ViewDate
]
?
[
datetime
]
?
NOT
?
NULL
)
ON
?OneYearDateRangePScheme?(ViewDate)
GO
如果大家希望查詢此表中的相關(guān)數(shù)據(jù)，可以使用如下語(yǔ)句
select
?$partition.OneYearDateRangePFN(ViewDate)?
from
?
[
Blog_ViewCount2
]
?a?
order
?
by
?a.ViewDate?
asc
關(guān)于增量問(wèn)題，我會(huì)在下一篇文章中，和大家分享。
參考資料：
http://www.microsoft.com/china/msdn/library/data/sqlserver/sql2k5partition.mspx?mfr=true
http://www.builder.com.cn/2007/0724/436577.shtml
http://www.agilelabs.cn/blogs/woody/archive/2006/08/24/1574.aspx
http://net.stuun.cn/database/Mssql/jiqiao/39408.html
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1922538
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
