資料庫是數據的倉庫,用於存儲數據,而存儲數據需要媒介,現在的存儲媒介,最常用的是硬碟,土豪一點的伺服器使用固態硬碟(SSD),特殊用途的伺服器使用記憶體。資料庫最常用的存儲文件是數據文件和日誌文件,數據文件用於存儲數據,由一個主數據文件(.mdf)和若幹個輔助數據文件(.ndf)構成;日誌文件用於存儲 ...
資料庫是數據的倉庫,用於存儲數據,而存儲數據需要媒介,現在的存儲媒介,最常用的是硬碟,土豪一點的伺服器使用固態硬碟(SSD),特殊用途的伺服器使用記憶體。資料庫最常用的存儲文件是數據文件和日誌文件,數據文件用於存儲數據,由一個主數據文件(.mdf)和若幹個輔助數據文件(.ndf)構成;日誌文件用於存儲事物日誌,由.ldf文件構成。不同的文件可以存分佈到不同的物理硬碟上,這樣便於分散硬碟IO,提高數據的讀取速度。
數據文件的組合,稱作文件組(File Group),資料庫不能直接設置存儲數據的數據文件,而是通過文件組來指定。
一,文件組和文件的作用
SQL Server的數據存儲在文件中,文件是實際存儲數據的物理實體,文件組是邏輯對象,SQL Server通過文件組來管理文件。
一個資料庫有一個或多個文件組,主文件組(Primary File Group)是系統自動創建的,用戶可以根據需要添加文件組。每一個文件組管理一個或多個文件,其中主文件組中包含主數據文件(master data file),擴展名是.mdf,這個文件是系統預設生成的,並且在資料庫中是唯一的;輔助文件的擴展名是.ndf,是用戶根據需要添加的。主文件組中也可以包含輔助文件,除了主文件組之外,其他文件組只能包含輔助文件。
例如,查看示例資料庫的文件組,Primary 是住文件組,勾選Default表示住文件組是預設的文件組,這意味著,如果在create table和create index中沒有指定FileGroup選項,那麼SQL Server將使用預設的文件組來存儲數據。
文件組是一個邏輯實體,實際上,數據存儲在文件中(.mdf和.ndf)中,每一個文件組中都包含文件,因此,在create table和create index命令中指定文件組,那麼數據就會存儲到文件組包含的文件中。
從上圖中可以看到,資料庫文件的元數據:
- Logical Name是數據文件的邏輯名稱,用於數據壓縮 DBCC ShrinkFile等;
- 資料庫文件有兩種類型:Rows Data(存儲數據)和Log(存儲日誌),
- Initial Size是文件的初始大小,
- 而Autogrowth表示文件自動增加的大小,Maxsize是文件大小的最大值,
- Path參數表示文件的路徑,
- File Name是文件的物理名稱,邏輯名稱和物理名可以是不同的。
用戶也可以使用sys.database_files 和 sys.filegroups 查看數據的文件和文件組的元數據。
二,使用文件組的優勢
在實際開發資料庫的過程中,通常情況下,用戶需要關註文件組,而不用關心文件的物理存儲,即使DBA改變文件的物理存儲,用戶也不會察覺到,也不會影響資料庫去執行查詢。除了邏輯文件和物理文件的分離之外,SQL Server使用文件組還有一個優勢,那就是分散IO負載,其實現的原理是:
- 對於單分區表,數據只能存到一個文件組中。如果把文件組內的數據文件分佈在不同的物理硬碟上,那麼SQL Server能同時從不同的物理硬碟上讀寫數據,把IO負載分散到不同的硬碟上。
- 對於多分區表,每個分區使用一個文件組,把不同的數據子集存儲在不同的磁碟上,SQL Server在讀寫某一個分組的數據時,能夠調用不同的硬碟IO。
這兩種方式,其本質上,都是使每個硬碟均攤系統負載,提高IO性能。
創建分區表時,不同的分區可以使用相同的文件組,也可以使用不同的文件組。因此,在設計文件組時,應儘量把包含的文件包含在不同的硬碟上,以實現物理IO的最大分散化。
在創建文件時,伺服器CPU核的數量,決定最大的併發IO度,應該根據CPU 核的數量創建多個文件。通常情況下,文件的數量和CPU核的數量一致,是最優化的設計。
還有,應該根據硬碟的性能來創建文件組,日誌文件存儲到性能最好的硬碟上,而查詢延遲要求高的數據,也需要存儲到性能最好的硬碟上。
不是所有的數據都是同等重要的,應該根據業務需求和查詢延遲,對數據分級,因此,在設計文件組時,應該把級別高的數據分散,而把那些基本用不到的數據存儲到性能差的,用於存儲歸檔數據的硬碟上,以實現伺服器性能的合理配置。
三,指定文件組
在創建表時,在on子句中指定文件組,那麼數據將存儲在該文件組包含的文件中:
CREATE TABLE [dbo].[student]( ... ) ON [PRIMARY]
在創建索引時,在on子句指定文件組選項,那麼該表的索引結構將存儲在文件組包含的文件中:
CREATE NONCLUSTERED INDEX [idx_student_stuname] ON [dbo].[student]( [stuname] ASC ) ON [PRIMARY]
在創建分區時,在TO自居中指定文件組,每一個分區將存儲到文件組中:
REATE PARTITION SCHEME scheme_name AS PARTITION function_name TO ([fg_name1], <....>, [fp_nameN])
四,數據文件大小增長導致的問題
當數據文件爆滿,沒有空間存儲數據時,此時執行insert命令,這會導致數據文件的增長。如果filegrowth選項設置的過大,會導致SQL Server耗費較長時間來實現文件的增長,在數據文件增長時,該文件是不能訪問的,因此,即使用戶僅插入一條數據,也要等待很長時間才能完成查詢,對用戶來說,體驗不友好。
數據文件增長是非常耗費系統資源和影響性能,如果設置SQL Server 自動增長,可能會導致系統性能不夠穩定,所以,應該預測可能的空間使用需求,並提前做好規劃。儘量避免空間用盡而使得SQL Server不得不自動增長的現象發生。同時也要確保每一次自動增長都能夠在可接受的時間內完成,及時滿足客戶端應用的需求。
推薦文檔: