SQL Server 2005中處理表分區(qū)問(wèn)題
本文介紹了表分區(qū)的概念以及SQL Server 2005支持表分區(qū),允許所有的表分區(qū)都保存在同一臺(tái)服務(wù)器上。每一個(gè)表分區(qū)都和在某個(gè)文件組(filegroup)中的單個(gè)文件關(guān)聯(lián)。同樣的一個(gè)文件/文件組可以容納多個(gè)分區(qū)表。同時(shí),還通過(guò)一個(gè)簡(jiǎn)單的例子來(lái)了解表分區(qū)是如何發(fā)揮作用的。
數(shù)據(jù)庫(kù)性能調(diào)優(yōu)是每一個(gè)優(yōu)秀SQL Server管理員最終的責(zé)任。雖然保證數(shù)據(jù)的安全和可用性是我們的最高的目標(biāo),但是假如數(shù)據(jù)庫(kù)應(yīng)用程序無(wú)法滿足用戶的要求,那么DBA們會(huì)因?yàn)樾阅艿拖碌脑O(shè)計(jì)和實(shí)現(xiàn)而受到指責(zé)。SQL Server 2005在數(shù)據(jù)庫(kù)性能方面得到了很多提高,尤其是表分區(qū)的技術(shù)。如果你還沒不了解表分區(qū)的特征,那么請(qǐng)你花點(diǎn)時(shí)間讀這篇文章。
表分區(qū)的概念不是一個(gè)新的概念;只要你當(dāng)過(guò)一段時(shí)間的SQL Server DBA,那么你可能已經(jīng)對(duì)一些頻繁訪問(wèn)的表進(jìn)行過(guò)歸檔,當(dāng)這個(gè)表中的歷史數(shù)據(jù)變的不再經(jīng)常被訪問(wèn)的時(shí)候。比如,假設(shè)你有一個(gè)打印時(shí)間報(bào)表的應(yīng)用,你的報(bào)告很少會(huì)查詢1995年的數(shù)據(jù),因?yàn)榻^大部分的預(yù)算規(guī)劃會(huì)基于最近幾年的數(shù)據(jù)。
在SQL Server的早期版本中,你可以創(chuàng)建多個(gè)表。每一個(gè)表都具有相同的列結(jié)構(gòu),用來(lái)保存不同年份的數(shù)據(jù)。這樣,當(dāng)存在著對(duì)歷史數(shù)據(jù)訪問(wèn)的必要的時(shí)候,你可以創(chuàng)建一個(gè)視圖來(lái)對(duì)這些表進(jìn)行查詢處理。將數(shù)據(jù)保存在多個(gè)表中是很方便的,因?yàn)橄鄬?duì)于查詢時(shí)掃描整個(gè)大表,掃描小表會(huì)更快。但是這種好處只有在你預(yù)先知道哪些時(shí)間段的數(shù)據(jù)會(huì)被訪問(wèn)。同時(shí),一旦數(shù)據(jù)過(guò)期,你還需要?jiǎng)?chuàng)建新表并且轉(zhuǎn)移新產(chǎn)生的歷史數(shù)據(jù)。
SQL Server 7和SQL Server 2000支持分布式分區(qū)視圖(distributed partitioned views,又稱為物化視圖,materialized views)。分布式分區(qū)視圖由分布于多臺(tái)服務(wù)器上的、具有相同表結(jié)構(gòu)的表構(gòu)成,而且你還需要為每一個(gè)服務(wù)器增加鏈接服務(wù)器定義(linked server definitions),最后在其中一臺(tái)服務(wù)器上創(chuàng)建一個(gè)視圖將每臺(tái)服務(wù)器上返回的數(shù)據(jù)合并起來(lái)。這里的設(shè)計(jì)思想是數(shù)據(jù)庫(kù)引擎可以利用多臺(tái)服務(wù)器的處理能力來(lái)滿足查詢。
但是,分布式分區(qū)視圖(DPV)受到很多限制,你可以在SQL Server的在線幫助文檔中閱讀到。雖然DPV在一些情況下能夠提供性能上的提高,但是這種技術(shù)不能被廣泛的應(yīng)用。已經(jīng)被證明它們不能滿足逐步增長(zhǎng)的企業(yè)級(jí)應(yīng)用的要求。何況,DPV的實(shí)現(xiàn)是一個(gè)費(fèi)力的過(guò)程,需要DBA進(jìn)行很多工作。
SQL Server 2005開始支持表分區(qū),這種技術(shù)允許所有的表分區(qū)都保存在同一臺(tái)服務(wù)器上。每一個(gè)表分區(qū)都和在某個(gè)文件組(filegroup)中的單個(gè)文件關(guān)聯(lián)。同樣的一個(gè)文件/文件組可以容納多個(gè)分區(qū)表。
在這種設(shè)計(jì)架構(gòu)下,數(shù)據(jù)庫(kù)引擎能夠判定查詢過(guò)程中應(yīng)該訪問(wèn)哪個(gè)分區(qū),而不用掃描整個(gè)表。如果查詢需要的數(shù)據(jù)行分散在多個(gè)分區(qū)中,SQL Server使用多個(gè)處理器對(duì)多個(gè)分區(qū)進(jìn)行并行查詢。你可以為在創(chuàng)建表的時(shí)候就定義分區(qū)的索引。 對(duì)小索引的搜索或者掃描要比掃描整個(gè)表或者一張大表上的索引要快很多。因此,當(dāng)對(duì)大表進(jìn)行查詢,表分區(qū)可以產(chǎn)生相當(dāng)大的性能提升。
現(xiàn)在讓我們通過(guò)一個(gè)簡(jiǎn)單的例子來(lái)了解表分區(qū)是如何發(fā)揮作用的。在這篇文章中,我不想深入到分區(qū)的語(yǔ)法細(xì)節(jié)當(dāng)中,這些你可以在SQL Server的在線幫助文檔中找到。下面的例子基于存儲(chǔ)著一個(gè)時(shí)間報(bào)表系統(tǒng)的數(shù)據(jù)的數(shù)據(jù)倉(cāng)庫(kù)。除了默認(rèn)的文件組,我另外創(chuàng)建了7個(gè)文件組,每一個(gè)文件組僅包含一個(gè)文件,這個(gè)文件將存儲(chǔ)由分區(qū)函數(shù)定義的一部分?jǐn)?shù)據(jù)。
為了測(cè)試表分區(qū)的性能提升,我向這個(gè)分區(qū)表中插入了一千五百萬(wàn)行,同時(shí)向另外一個(gè)具有相同表結(jié)構(gòu)、但是沒有進(jìn)行分區(qū)的表插入了同樣的數(shù)據(jù)。對(duì)分區(qū)表執(zhí)行的INSERT語(yǔ)句運(yùn)行的更快一些。甚至在我的內(nèi)存不到1G的筆記本電腦上,對(duì)分區(qū)表的INSERT語(yǔ)句比不分區(qū)的表的INSERT語(yǔ)句要快上三倍。當(dāng)然,查詢的執(zhí)行時(shí)間依據(jù)硬件資源的差異而所有變化,但是你還是能夠在你的環(huán)境中感到不同程度的提升。
我將檢查更深入了一步,通過(guò)分別檢查同一條返回所有行的、簡(jiǎn)單SELECT語(yǔ)句在分區(qū)表和非分區(qū)表上的執(zhí)行計(jì)劃,返回的數(shù)據(jù)范圍通過(guò)WHERE語(yǔ)句來(lái)指定。同一條語(yǔ)句在這兩個(gè)不同的表上有不同的執(zhí)行計(jì)劃。對(duì)于分區(qū)表的查詢顯示出一個(gè)嵌套的循環(huán)和索引的掃描。從本質(zhì)上來(lái)說(shuō),SQL Server將兩個(gè)分區(qū)視為獨(dú)立的表,因此使用一個(gè)嵌套循環(huán)將它們連接起來(lái)。對(duì)非分區(qū)的表的同一個(gè)查詢則使用索引掃描來(lái)返回同樣的列。當(dāng)你使用同樣的分區(qū)策略創(chuàng)建多個(gè)表,同時(shí)在查詢中連接這些表,那么性能上的提升會(huì)更加明顯。
你可以使用下面的查詢來(lái)了解每一個(gè)分區(qū)中的行的個(gè)數(shù):
SELECT $PARTITION.TimeEntryDateRangePFN(time_entry_date) AS Partition,
COUNT(*) AS [COUNT] FROM fact_time_entry
GROUP BY $PARTITION.TimeEntryDateRangePFN(time_entry_date)
ORDER BY Partition
表分區(qū)對(duì)交易環(huán)境和數(shù)據(jù)倉(cāng)庫(kù)環(huán)境來(lái)說(shuō),都是一個(gè)重要的特征。數(shù)據(jù)倉(cāng)庫(kù)用戶最主要的抱怨是移動(dòng)事實(shí)表(fact table)會(huì)花費(fèi)太多時(shí)間。當(dāng)裝載數(shù)據(jù)到事實(shí)表的時(shí)候,用戶查詢(立方體處理查詢)的性能會(huì)明顯下降,甚至是完全無(wú)法成功。因此,裝載大量的數(shù)據(jù)到事實(shí)表的時(shí)候常常需要停機(jī)。如果使用表分區(qū),就不再出現(xiàn)這樣的情況——確切的講,你一眨眼的工夫就可以移動(dòng)事實(shí)表。為了演示這是如何生效的,我使用上面例子中相同的分區(qū)函數(shù)和表結(jié)構(gòu)來(lái)創(chuàng)建一個(gè)新的表,這個(gè)表叫做fact_time_entry2。表的主鍵從五千萬(wàn)開始,這樣fact_time_entry2就不會(huì)包含表fact_time_entry中已經(jīng)有的數(shù)據(jù)。
現(xiàn)在我把2007年的數(shù)據(jù)移動(dòng)到這張fact_time_entry2中。同時(shí)讓我們假設(shè)fact_time_entry表中包含著2007年之前的數(shù)據(jù)。在fact_time_entry2表完成數(shù)據(jù)的轉(zhuǎn)移,我執(zhí)行下面的語(yǔ)句:
ALTER TABLE fact_time_entry2
SWITCH PARTITION 8 TO fact_time_entry PARTITION 8
這條語(yǔ)句將編號(hào)為8的分區(qū),這個(gè)分區(qū)恰好包含著2007年的數(shù)據(jù),從fact_time_entry2移動(dòng)到了fact_time_entry表中,在我的筆記本電腦上,這個(gè)過(guò)程只花費(fèi)了3毫秒。在這短短的3毫秒中,我的事實(shí)表就增加了五百萬(wàn)條記錄!的確,我需要在交換分區(qū)之前,將數(shù)據(jù)移動(dòng)到中間表,但是我的用戶不需要擔(dān)心——事實(shí)表隨時(shí)都可以查詢!在這幕后,實(shí)際上沒有數(shù)據(jù)移動(dòng)——只是兩張表的元數(shù)據(jù)發(fā)生了變化。
我可以使用類似的查詢刪除事實(shí)表中不在需要的數(shù)據(jù)。例如,假設(shè)我們決定我們不再關(guān)心2004年的記錄。下面的語(yǔ)句可以將這些記錄轉(zhuǎn)移到我們創(chuàng)建的工作表中:
ALTER TABLE fact_time_entry
SWITCH PARTITION 2 TO fact_time_entry2 PARTITION 2
這樣的語(yǔ)句依舊在毫秒級(jí)內(nèi)完成了,F(xiàn)在,我可以刪除fact_time_entry2或者將它移到其他的服務(wù)器上。我的事實(shí)表不會(huì)包含2004年的任何記錄。這個(gè)分區(qū)還是需要在目的表中存在,而且它必須是空的。你不能將分區(qū)轉(zhuǎn)移到一個(gè)包含重復(fù)數(shù)據(jù)的表中。源表和目的表的分區(qū)必須一致,同時(shí)被轉(zhuǎn)移的數(shù)據(jù)必須在同一個(gè)文件組中。即使受到這么多的限制,轉(zhuǎn)換分區(qū)和無(wú)需停機(jī)就可以移動(dòng)數(shù)據(jù)表的功能必將讓數(shù)據(jù)倉(cāng)庫(kù)的實(shí)現(xiàn)變的前所未有的輕松。
關(guān)鍵字:SQL Server、服務(wù)器、數(shù)據(jù)庫(kù)
新文章:
- CentOS7下圖形配置網(wǎng)絡(luò)的方法
- CentOS 7如何添加刪除用戶
- 如何解決centos7雙系統(tǒng)后丟失windows啟動(dòng)項(xiàng)
- CentOS單網(wǎng)卡如何批量添加不同IP段
- CentOS下iconv命令的介紹
- Centos7 SSH密鑰登陸及密碼密鑰雙重驗(yàn)證詳解
- CentOS 7.1添加刪除用戶的方法
- CentOS查找/掃描局域網(wǎng)打印機(jī)IP講解
- CentOS7使用hostapd實(shí)現(xiàn)無(wú)AP模式的詳解
- su命令不能切換root的解決方法
- 解決VMware下CentOS7網(wǎng)絡(luò)重啟出錯(cuò)
- 解決Centos7雙系統(tǒng)后丟失windows啟動(dòng)項(xiàng)
- CentOS下如何避免文件覆蓋
- CentOS7和CentOS6系統(tǒng)有什么不同呢
- Centos 6.6默認(rèn)iptable規(guī)則詳解