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

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

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

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

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

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

SQL Server 的數(shù)據(jù)存儲(chǔ)在文件中，文件是實(shí)際存儲(chǔ)數(shù)據(jù)的物理實(shí)體，文件組是邏輯對(duì)象，SQL Server 通過(guò)文件組來(lái)管理文件。

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

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

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

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

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

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

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

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

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

指定文件組

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

CREATE TABLE tb_name(
...
) ON fg_name

例

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

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

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

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

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

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

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

create database TEST
on primary                    --主文件組
(
    name='Test_Data',    --邏輯名
    size=100mb,                --初始大小
    filegrowth=10%,            --自動(dòng)增長(zhǎng)步長(zhǎng)
    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)勢(shì)

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

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

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

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

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

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

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

數(shù)據(jù)文件自動(dòng)增長(zhǎng)導(dǎo)致的問(wèn)題

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

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

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

最新評(píng)論