1.1:增加次數(shù)據(jù)文件
從SQL SERVER 2005開始,數(shù)據(jù)庫不默認生成NDF數(shù)據(jù)文件,一般情況下有一個主數(shù)據(jù)文件(MDF)就夠了,但是有些大型的數(shù)據(jù)庫,由于信息很多,而且查詢頻繁,所以為了提高查詢速度,可以把一些表或者一些表中的部分記錄分開存儲在不同的數(shù)據(jù)文件里
由于CPU和內(nèi)存的速度遠大于硬盤的讀寫速度,所以可以把不同的數(shù)據(jù)文件放在不同的物理硬盤里,這樣執(zhí)行查詢的時候,就可以讓多個硬盤同時進行查詢,以充分利用CPU和內(nèi)存的性能,提高查詢速度。 在這里詳細介紹一下其寫入的原理,數(shù)據(jù)文件(MDF、NDF)和日志文件(LDF)的寫入方式是不一樣的:
數(shù)據(jù)文件:SQL Server按照同一個文件組里面的所有文件現(xiàn)有空閑空間的大小,按這個比例把新的數(shù)據(jù)分布到所有有空間的數(shù)據(jù)文件里,如果有三個數(shù)據(jù)文件A.MDF,B.NDF,C.NDF,空閑大小分別為200mb,100mb,和50mb,那么寫入一個70mb的東西,他就會向ABC三個文件中一次寫入40、20、10的數(shù)據(jù),如果某個日志文件已滿,就不會向其寫入
日志文件:日志文件是按照順序?qū)懭氲?,一個寫滿,才會寫入另外一個
由上可見,如果能增加其數(shù)據(jù)文件NDF,有利于大數(shù)據(jù)量的查詢速度,但是增加日志文件卻沒什么用處。
1.2:設置文件自動增長(大數(shù)據(jù)量,小數(shù)據(jù)量無需設置)
在SQL Server 2005中,默認MDF文件初始大小為5MB,自增為1MB,不限增長,LDF初始為1MB,增長為10%,限制文件增長到一定的數(shù)目,一般設計中,使用SQL自帶的設計即可,但是大型數(shù)據(jù)庫設計中,最好親自去設計其增長和初始大小,如果初始值太小,那么很快數(shù)據(jù)庫就會寫滿,如果寫滿,在進行插入會是什么情況呢?當數(shù)據(jù)文件寫滿,進行某些操作時,SQL Server會讓操作等待,直到文件自動增長結束了,原先的那個操作才能繼續(xù)進行。如果自增長用了很長時間,原先的操作會等不及就超時取消了(一般默認的閾值是15秒),不但這個操作會回滾,文件自動增長也會被取消。也就是說,這一次文件沒有得到任何增大,增長的時間根據(jù)自動增長的大小確定的,如果太小,可能一次操作需要連續(xù)幾次增長才能滿足,如果太大,就需要等待很長時間,所以設置自動增長要注意一下幾點:
1)要設置成按固定大小增長,而不能按比例。這樣就能避免一次增長太多或者太少所帶來的不必要的麻煩。建議對比較小的數(shù)據(jù)庫,設置一次增長50 MB到100 MB。對大的數(shù)據(jù)庫,設置一次增長100 MB到200 MB。
2)要定期監(jiān)測各個數(shù)據(jù)文件的使用情況,盡量保證每個文件剩余的空間一樣大,或者是期望的比例。
3)設置文件最大值,以免SQL Server文件自增長用盡磁盤空間,影響操作系統(tǒng)。
4)發(fā)生自增長后,要及時檢查新的數(shù)據(jù)文件空間分配情況。避免SQL Server總是往個別文件寫數(shù)據(jù)。
因此,對于一個比較繁忙的數(shù)據(jù)庫,推薦的設置是開啟數(shù)據(jù)庫自動增長選項,以防數(shù)據(jù)庫空間用盡導致應用程序失敗,但是要嚴格避免自動增長的發(fā)生。同時,盡量不要使用自動收縮功能。
1.3 數(shù)據(jù)和日志文件分開存放在不同磁盤上
數(shù)據(jù)文件和日志文件的操作會產(chǎn)生大量的I/O。在可能的條件下,日志文件應該存放在一個與數(shù)據(jù)和索引所在的數(shù)據(jù)文件不同的硬盤上以分散I/O,同時還有利于數(shù)據(jù)庫的災難恢復。
優(yōu)化②:表分區(qū),索引分區(qū) (優(yōu)化①粗略的進行了表分區(qū),優(yōu)化②為精確數(shù)據(jù)分區(qū))
為什么要表分區(qū)?
當一個表的數(shù)據(jù)量太大的時候,我們最想做的一件事是什么?將這個表一分為二或者更多分,但是表還是這個表,只是將其內(nèi)容存儲分開,這樣讀取就快了N倍了
原理:表數(shù)據(jù)是無法放在文件中的,但是文件組可以放在文件中,表可以放在文件組中,這樣就間接實現(xiàn)了表數(shù)據(jù)存放在不同的文件中。能分區(qū)存儲的還有:表、索引和大型對象數(shù)據(jù) 。
SQL SERVER 2005中,引入了表分區(qū)的概念, 當表中的數(shù)據(jù)量不斷增大,查詢數(shù)據(jù)的速度就會變慢,應用程序的性能就會下降,這時就應該考慮對表進行分區(qū),當一個表里的數(shù)據(jù)很多時,可以將其分拆到多個的表里,因為要掃描的數(shù)據(jù)變得更少 ,查詢可以更快地運行,這樣操作大大提高了性能,表進行分區(qū)后,邏輯上表仍然是一張完整的表,只是將表中的數(shù)據(jù)在物理上存放到多個表空間(物理文件上),這樣查詢數(shù)據(jù)時,不至于每次都掃描整張表
2.1什么時候使用分區(qū)表:
1、表的大小超過2GB。
2、表中包含歷史數(shù)據(jù),新的數(shù)據(jù)被增加到新的分區(qū)中。
2.2表分區(qū)的優(yōu)缺點
表分區(qū)有以下優(yōu)點:
1、改善查詢性能:對分區(qū)對象的查詢可以僅搜索自己關心的分區(qū),提高檢索速度。
2、增強可用性:如果表的某個分區(qū)出現(xiàn)故障,表在其他分區(qū)的數(shù)據(jù)仍然可用;
3、維護方便:如果表的某個分區(qū)出現(xiàn)故障,需要修復數(shù)據(jù),只修復該分區(qū)即可;
4、均衡I/O:可以把不同的分區(qū)映射到磁盤以平衡I/O,改善整個系統(tǒng)性能。
缺點:
分區(qū)表相關:已經(jīng)存在的表沒有方法可以直接轉化為分區(qū)表。不過 Oracle 提供了在線重定義表的功能。
2.3表分區(qū)的操作三步走
2.31 創(chuàng)建分區(qū)函數(shù)
CREATE PARTITION FUNCTION xx1(int)
AS RANGE LEFT FOR VALUES (10000, 20000);
注釋:創(chuàng)建分區(qū)函數(shù):myRangePF2,以INT類型分區(qū),分三個區(qū)間,10000以內(nèi)在A 區(qū),1W-2W在B區(qū),2W以上在C區(qū).
2.3.2創(chuàng)建分區(qū)架構
CREATE PARTITION SCHEME myRangePS2
AS PARTITION xx1
TO (a, b, c);
注釋:在分區(qū)函數(shù)XX1上創(chuàng)建分區(qū)架構:myRangePS2,分別為A,B,C三個區(qū)間
A,B,C分別為三個文件組的名稱,而且必須三個NDF隸屬于這三個組,文件所屬文件組一旦創(chuàng)建就不能修改
2.3.3 對表進行分區(qū)
常用數(shù)據(jù)規(guī)范--數(shù)據(jù)空間類型修改為:分區(qū)方案,然后選擇分區(qū)方案名稱和分區(qū)列列表,結果如圖所示:
也可以用sql語句生成
CREATE TABLE [dbo].[AvCache]( [AVNote] [varchar](300) NULL, [bb] [int] IDENTITY(1,1) ) ON [myRangePS2](bb);
--注意這里使用[myRangePS2]架構,根據(jù)bb分區(qū)
2.3.4查詢表分區(qū)
SELECT *, $PARTITION.[myRangePF2](bb) FROM dbo.AVCache
這樣就可以清楚的看到表數(shù)據(jù)是如何分區(qū)的了
2.3.5創(chuàng)建索引分區(qū)
優(yōu)化③:分布式數(shù)據(jù)庫設計
分布式數(shù)據(jù)庫系統(tǒng)是在集中式數(shù)據(jù)庫系統(tǒng)的基礎上發(fā)展起來的,理解起來也很簡單,就是將整體的數(shù)據(jù)庫分開,分布到各個地方,就其本質(zhì)而言,分布式數(shù)據(jù)庫系統(tǒng)分為兩種:1.數(shù)據(jù)在邏輯上是統(tǒng)一的,而在物理上卻是分散的,一個分布式數(shù)據(jù)庫在邏輯上是一個統(tǒng)一的整體,在物理上則是分別存儲在不同的物理節(jié)點上,我們通常說的分布式數(shù)據(jù)庫都是這種2.邏輯是分布的,物理上也是分布的,這種也成聯(lián)邦式分布數(shù)據(jù)庫,由于組成聯(lián)邦的各個子數(shù)據(jù)庫系統(tǒng)是相對“自治”的,這種系統(tǒng)可以容納多種不同用途的、差異較大的數(shù)據(jù)庫,比較適宜于大范圍內(nèi)數(shù)據(jù)庫的集成。
分布式數(shù)據(jù)庫較為復雜,在此不作詳細的使用和說明,只是舉例說明一下,現(xiàn)在分布式數(shù)據(jù)庫多用于用戶分區(qū)性較強的系統(tǒng)中,如果一個全國連鎖店,一般設計為每個分店都有自己的銷售和庫存等信息,總部則需要有員工,供應商,分店信息等數(shù)據(jù)庫,這類型的分店數(shù)據(jù)庫可以完全一致,很多系統(tǒng)也可能導致不一致,這樣,各個連鎖店數(shù)據(jù)存儲在本地,從而提高了影響速度,降低了通信費用,而且數(shù)據(jù)分布在不同場地,且存有多個副本,即使個別場地發(fā)生故障,不致引起整個系統(tǒng)的癱瘓。 但是他也帶來很多問題,如:數(shù)據(jù)一致性問題、數(shù)據(jù)遠程傳遞的實現(xiàn)、通信開銷的降低等,這使得分布式數(shù)據(jù)庫系統(tǒng)的開發(fā)變得較為復雜,只是讓大家明白其原理,具體的使用方式就不做詳細的介紹了。
優(yōu)化④:整理數(shù)據(jù)庫碎片
如果你的表已經(jīng)創(chuàng)建好了索引,但性能卻仍然不好,那很可能是產(chǎn)生了索引碎片,你需要進行索引碎片整理。
什么是索引碎片?
由于表上有過度地插入、修改和刪除操作,索引頁被分成多塊就形成了索引碎片,如果索引碎片嚴重,那掃描索引的時間就會變長,甚至導致索引不可用,因此數(shù)據(jù)檢索操作就慢下來了。
如何知道是否發(fā)生了索引碎片?
在SQLServer數(shù)據(jù)庫,通過DBCC ShowContig或DBCC ShowContig(表名)檢查索引碎片情況,指導我們對其進行定時重建整理。
通過對掃描密度(過低),掃描碎片(過高)的結果分析,判定是否需要索引重建,主要看如下兩個:
Scan Density [Best Count:Actual Count]-掃描密度[最佳值:實際值]:DBCC SHOWCONTIG返回最有用的一個百分比。這是擴展盤區(qū)的最佳值和實際值的比率。該百分比應該盡可能靠近100%。低了則說明有外部碎片。
Logical Scan Fragmentation-邏輯掃描碎片:無序頁的百分比。該百分比應該在0%到10%之間,高了則說明有外部碎片。
解決方式:
一是利用DBCC INDEXDEFRAG整理索引碎片
二是利用DBCC DBREINDEX重建索引。
兩者區(qū)別調(diào)用微軟的原話如下:
DBCC INDEXDEFRAG 命令是聯(lián)機操作,所以索引只有在該命令正在運行時才可用,而且可以在不丟失已完成工作的情況下中斷該操作。這種方法的缺點是在重新組織數(shù)據(jù)方面沒有聚集索引的除去/重新創(chuàng)建操作有效。
重新創(chuàng)建聚集索引將對數(shù)據(jù)進行重新組織,其結果是使數(shù)據(jù)頁填滿。填滿程度可以使用 FILLFACTOR 選項進行配置。這種方法的缺點是索引在除去/重新創(chuàng)建周期內(nèi)為脫機狀態(tài),并且操作屬原子級。如果中斷索引創(chuàng)建,則不會重新創(chuàng)建該索引。也就是說,要想獲得好的效果,還是得用重建索引,所以決定重建索引。