數(shù)據(jù)文件組

SQL Server 數(shù)據(jù)庫最常用的存儲文件是數(shù)據(jù)文件和日志文件。

• 數(shù)據(jù)文件用于存儲數(shù)據(jù)，由一個主要數(shù)據(jù)文件（.mdf）和若干個次要數(shù)據(jù)文件（.ndf）構(gòu)成；
• 日志文件用于存儲事物日志，由.ldf文件構(gòu)成。

不同的文件可以存分布到不同的物理硬盤上，這樣便于分散硬盤IO，提高數(shù)據(jù)的讀取速度。

數(shù)據(jù)文件的組合，稱作文件組（File Group），數(shù)據(jù)庫不能直接設(shè)置存儲數(shù)據(jù)的數(shù)據(jù)文件，而是通過文件組來指定。

文件和文件組的關(guān)系

SQL Server 的數(shù)據(jù)存儲在文件中，文件是實際存儲數(shù)據(jù)的物理實體，文件組是邏輯對象，SQL Server 通過文件組來管理文件。

• 一個數(shù)據(jù)庫有一個或多個文件組，其中主文件組（Primary File Group）是系統(tǒng)自動創(chuàng)建的，用戶可以根據(jù)需要添加文件組。
• 每一個文件組管理一個或多個文件，其中主文件組中包含主要數(shù)據(jù)文件（*.mdf），主文件組中也可以包含次要數(shù)據(jù)文件 。（主要數(shù)據(jù)文件是系統(tǒng)默認生成的，并且在數(shù)據(jù)庫中是唯一的；次要數(shù)據(jù)文件是用戶根據(jù)需要添加的。）
• 除了主文件組之外，其他文件組只能包含輔助文件。

如下示例數(shù)據(jù)庫，系統(tǒng)已自動創(chuàng)建主文件組 PRIMARY，勾選 Default 表示將主文件組設(shè)置為默認文件組，即如果在 create table 和 create index 時沒有指定 FileGroup 選項，那么 SQL Server 將使用默認的 PRIMARY 文件組來存儲數(shù)據(jù)。

文件組是一個邏輯實體，實際上，數(shù)據(jù)存儲在文件中（.mdf和.ndf）中，每一個文件組中都包含文件，如下圖：

由上圖可以看到，數(shù)據(jù)庫文件的元數(shù)據(jù)：

• Logical Name – 文件的邏輯名稱，用于數(shù)據(jù)壓縮 DBCC ShrinkFile 等；
• File Type – 文件類型，有兩種：Rows Data（存儲數(shù)據(jù)）和 Log（存儲日志）；
• Initial Size – 文件初始大??；
• Autogrowth/Maxsize – Autogrowth 表示文件自動增加的步長，Maxsize 表示文件大小的最大值限制；
• Path – 文件存放路徑；
• File Name – 文件的物理名稱，邏輯名稱和物理名可以不同 。

用戶也可以通過 sys.filegroups 和 sys.database_files 查看數(shù)據(jù)的文件組和文件的元數(shù)據(jù)。

select * from sys.filegroups;
select * from sys.database_files;

本例中，出系統(tǒng)自動創(chuàng)建的主文件組外，并未創(chuàng)建其他文件組。若數(shù)據(jù)庫包含多個文件組，在 create table 和 create index 命令中指定文件組，數(shù)據(jù)就會存儲到指定文件組包含的文件中。

應(yīng)該如何指定呢？

指定文件組

在創(chuàng)建表時，在 ON 子句中指定文件組，那么數(shù)據(jù)將存儲在該文件組包含的文件中：

CREATE TABLE tb_name(
...
) ON fg_name

例

create table Test
(
    Tid int primary key identity,
    Title01 nvarchar(100) default('標題01'),    
    Title02 nvarchar(100) default('標題02'),    
    Title03 nvarchar(100) default('標題03'),    
    DataStatus tinyint default(0) --0~255 size:1字節(jié)
) on PRIMARY

在創(chuàng)建索引時，在 ON 子句指定文件組選項，那么該表的索引結(jié)構(gòu)將存儲在文件組包含的文件中：

CREATE NONCLUSTERED INDEX index_name 
ON tb_name( [colume] ASC ) 
ON fg_name

在創(chuàng)建分區(qū)時，在 TO 字句中指定文件組，每一個分區(qū)將存儲到文件組中：

REATE PARTITION SCHEME scheme_name
AS PARTITION function_name 
TO ([fg_name1], <....>, [fp_nameN])

SQL 語句創(chuàng)建數(shù)據(jù)庫并創(chuàng)建多個文件組

現(xiàn)提供示例如下：

create database TEST
on primary                    --主文件組
(
    name='Test_Data',    --邏輯名
    size=100mb,                --初始大小
    filegrowth=10%,            --自動增長步長
    maxsize=1024mb,            --最大值
    filename=N'F:\Program Files\Microsoft SQL Server\MSSQL11.STUDR\MSSQL\DATA\Test_Data.mdf'--存放路徑及文件名
),
(
    name='Test_Data1',
    size=100mb,
    filegrowth=10%,
    maxsize=1024mb,
    filename=N'E:\DATA\Test_Data1.mdf'
),
filegroup TestData --TestData文件組
(
    name='TestData1',
    size=100mb,
    filegrowth=10%,
    maxsize=1024mb,
    filename=N'F:\Program Files\Microsoft SQL Server\MSSQL11.STUDR\MSSQL\DATA\Test_Data1.ndf.ndf'
),
(
    name='TestData2',
    size=100mb,
    filegrowth=10%,
    maxsize=1024mb,
    filename=N'E:\DATA\TestData2.ndf'
)
log on --日記
(
    name='Test_Log1',
    size=5mb,
    filegrowth=5%,
    filename=N'F:\Program Files\Microsoft SQL Server\MSSQL11.STUDR\MSSQL\DATA\Test_log1.ldf'
),
(
    name='Test_Log2',
    size=5mb,
    filegrowth=5%,
    filename=N'E:\DATA\Test_log2.ldf'
)
go

使用文件組的優(yōu)勢

在實際開發(fā)數(shù)據(jù)庫的過程中，通常情況下，用戶需要關(guān)注文件組，而不用關(guān)心文件的物理存儲，即使DBA改變文件的物理存儲，用戶也不會察覺到，也不會影響數(shù)據(jù)庫去執(zhí)行查詢。除了邏輯文件和物理文件的分離之外，SQL Server使用文件組還有一個優(yōu)勢，那就是分散IO負載，其實現(xiàn)的原理是：

• 對于單分區(qū)表，數(shù)據(jù)只能存到一個文件組中。如果把文件組內(nèi)的數(shù)據(jù)文件分布在不同的物理硬盤上，那么SQL Server能同時從不同的物理硬盤上讀寫數(shù)據(jù)，把IO負載分散到不同的硬盤上。
• 對于多分區(qū)表，每個分區(qū)使用一個文件組，把不同的數(shù)據(jù)子集存儲在不同的磁盤上，SQL Server在讀寫某一個分組的數(shù)據(jù)時，能夠調(diào)用不同的硬盤IO。

這兩種方式，其本質(zhì)上，都是使每個硬盤均攤系統(tǒng)負載，提高IO性能。

創(chuàng)建分區(qū)表時，不同的分區(qū)可以使用相同的文件組，也可以使用不同的文件組。因此，在設(shè)計文件組時，應(yīng)盡量把包含的文件包含在不同的硬盤上，以實現(xiàn)物理IO的最大分散化。

在創(chuàng)建文件時，服務(wù)器CPU核的數(shù)量，決定最大的并發(fā)IO度，應(yīng)該根據(jù)CPU 核的數(shù)量創(chuàng)建多個文件。通常情況下，文件的數(shù)量和CPU核的數(shù)量一致，是最優(yōu)化的設(shè)計。

還有，應(yīng)該根據(jù)硬盤的性能來創(chuàng)建文件組，日志文件存儲到性能最好的硬盤上，而查詢延遲要求高的數(shù)據(jù)，也需要存儲到性能最好的硬盤上。

不是所有的數(shù)據(jù)都是同等重要的，應(yīng)該根據(jù)業(yè)務(wù)需求和查詢延遲，對數(shù)據(jù)分級，因此，在設(shè)計文件組時，應(yīng)該把級別高的數(shù)據(jù)分散，而把那些基本用不到的數(shù)據(jù)存儲到性能差的，用于存儲歸檔數(shù)據(jù)的硬盤上，以實現(xiàn)服務(wù)器性能的合理配置。

數(shù)據(jù)文件自動增長導(dǎo)致的問題

當數(shù)據(jù)文件爆滿，沒有空間存儲數(shù)據(jù)時，此時執(zhí)行insert命令，這會導(dǎo)致數(shù)據(jù)文件的增長。如果filegrowth選項設(shè)置的過大，會導(dǎo)致SQL Server耗費較長時間來實現(xiàn)文件的增長，在數(shù)據(jù)文件增長時，該文件是不能訪問的，因此，即使用戶僅插入一條數(shù)據(jù)，也要等待很長時間才能完成查詢，對用戶來說，體驗不友好。

數(shù)據(jù)文件增長是非常耗費系統(tǒng)資源和影響性能，如果設(shè)置SQL Server 自動增長，可能會導(dǎo)致系統(tǒng)性能不夠穩(wěn)定，所以，應(yīng)該預(yù)測可能的空間使用需求，并提前做好規(guī)劃。盡量避免空間用盡而使得SQL Server不得不自動增長的現(xiàn)象發(fā)生。同時也要確保每一次自動增長都能夠在可接受的時間內(nèi)完成，及時滿足客戶端應(yīng)用的需求。

以上就是SQL Server文件組詳解的詳細內(nèi)容，更多關(guān)于SQL Server文件組的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論