SQL Server文件組的用法和原理
數(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ù)文件的組合,稱(chēng)作文件組(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 – 文件的邏輯名稱(chēng),用于數(shù)據(jù)壓縮 DBCC ShrinkFile 等;
- File Type – 文件類(lèi)型,有兩種:Rows Data(存儲(chǔ)數(shù)據(jù))和 Log(存儲(chǔ)日志);
- Initial Size – 文件初始大?。?/li>
- Autogrowth/Maxsize – Autogrowth 表示文件自動(dòng)增加的步長(zhǎng),Maxsize 表示文件大小的最大值限制;
- Path – 文件存放路徑;
- File Name – 文件的物理名稱(chēng),邏輯名稱(chēng)和物理名可以不同 。
用戶也可以通過(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)文章!
相關(guān)文章
SQL窗口函數(shù)之聚合窗口函數(shù)的使用(count,max,min,sum)
許多常見(jiàn)的聚合函數(shù)也可以作為窗口函數(shù)使用,包括AVG()、SUM()、COUNT()、MAX()以及MIN()等函數(shù),本文就詳細(xì)的介紹了SQL窗口函數(shù)之聚合窗口函數(shù)的使用,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2022-04-04Sql Server數(shù)據(jù)庫(kù)各版本功能對(duì)比
這篇文章主要介紹了Sql Server數(shù)據(jù)庫(kù)各版本功能對(duì)比的相關(guān)資料,包括各個(gè)版本新增的功能介紹,需要的朋友可以參考下2017-05-05SQL Server創(chuàng)建鏈接服務(wù)器的存儲(chǔ)過(guò)程示例分享
創(chuàng)建鏈接服務(wù)器。鏈接服務(wù)器讓用戶可以對(duì) OLE DB 數(shù)據(jù)源進(jìn)行分布式異類(lèi)查詢。在使用 sp_addlinkedserver 創(chuàng)建鏈接 服務(wù)器后,可對(duì)該服務(wù)器運(yùn)行分布式查詢。2014-07-07SQLServer導(dǎo)出數(shù)據(jù)到MySQL實(shí)例介紹
本文為大家詳細(xì)介紹下從SQLServer導(dǎo)出數(shù)據(jù)并將數(shù)據(jù)導(dǎo)入到MySQL,具體的實(shí)現(xiàn)如下,感興趣的朋友可以參考下哈2013-07-07Sql學(xué)習(xí)第一天——SQL 將變量定義為T(mén)able類(lèi)型(虛擬表)
sql語(yǔ)句中的變量時(shí)通常我們定義的都是像char,varchar,nvarchar之類(lèi)的,接下來(lái)教大家實(shí)現(xiàn)讓變量作為一個(gè)像虛擬表一樣,感性的各位可以參考下哈2013-03-03SQLServer導(dǎo)出sql文件/表架構(gòu)和數(shù)據(jù)操作步驟
只導(dǎo)出了數(shù)據(jù)庫(kù)的sql腳本,而表里的數(shù)據(jù)依然沒(méi)有導(dǎo)出來(lái),接下來(lái)將為你解決這個(gè)問(wèn)題,感興趣的看下教程哈2013-03-03SQL Server中統(tǒng)計(jì)每個(gè)表行數(shù)的快速方法
這篇文章主要介紹了SQL Server中統(tǒng)計(jì)每個(gè)表行數(shù)的快速方法,本文不使用傳統(tǒng)的count()函數(shù),因?yàn)樗容^慢和占用資源,本文講解的是另一種方法,需要的朋友可以參考下2015-02-02將string數(shù)組轉(zhuǎn)化為sql的in條件用sql查詢
將string數(shù)組轉(zhuǎn)化為sql的in條件就可以用sql查詢了,下面是具體是的示例,大家可以參考下2014-05-05