SQL Server文件组的用法和原理


    数据文件组
    SQL Server 数据库最常用的存储文件是数据文件和日志文件。
    
  • 数据文件用于存储数据,由一个主要数据文件(.mdf)和若干个次要数据文件(.ndf)构成;
  • 日志文件用于存储事物日志,由.ldf文件构成。

    不同的文件可以存分布到不同的物理硬盘上,这样便于分散硬盘IO,提高数据的读取速度。
    数据文件的组合,称作文件组(File Group),数据库不能直接设置存储数据的数据文件,而是通过文件组来指定。
    文件和文件组的关系
    SQL Server 的数据存储在文件中,文件是实际存储数据的物理实体,文件组是逻辑对象,SQL Server 通过文件组来管理文件。
    
    
  • 一个数据库有一个或多个文件组,其中主文件组(Primary File Group)是系统自动创建的,用户可以根据需要添加文件组。
  • 每一个文件组管理一个或多个文件,其中主文件组中包含主要数据文件(*.mdf),主文件组中也可以包含次要数据文件 。(主要数据文件是系统默认生成的,并且在数据库中是唯一的;次要数据文件是用户根据需要添加的。)
  • 除了主文件组之外,其他文件组只能包含辅助文件。

    如下示例数据库,系统已自动创建主文件组 PRIMARY,勾选 Default 表示将主文件组设置为默认文件组,即如果在 create table 和 create index 时没有指定 FileGroup 选项,那么 SQL Server 将使用默认的 PRIMARY 文件组来存储数据。
    
    文件组是一个逻辑实体,实际上,数据存储在文件中(.mdf和.ndf)中,每一个文件组中都包含文件,如下图:
    
    由上图可以看到,数据库文件的元数据:
    
  • Logical Name – 文件的逻辑名称,用于数据压缩 DBCC ShrinkFile 等;
  • File Type – 文件类型,有两种:Rows Data(存储数据)和 Log(存储日志);
  • Initial Size – 文件初始大小;
  • Autogrowth/Maxsize – Autogrowth 表示文件自动增加的步长,Maxsize 表示文件大小的最大值限制;
  • Path – 文件存放路径;
  • File Name – 文件的物理名称,逻辑名称和物理名可以不同 。

    用户也可以通过 sys.filegroups 和 sys.database_files 查看数据的文件组和文件的元数据。
    
select * from sys.filegroups;
select * from sys.database_files;

    本例中,出系统自动创建的主文件组外,并未创建其他文件组。若数据库包含多个文件组,在 create table 和 create index 命令中指定文件组,数据就会存储到指定文件组包含的文件中。
    应该如何指定呢?
    指定文件组
    在创建表时,在 ON 子句中指定文件组,那么数据将存储在该文件组包含的文件中:
    
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字节
) on PRIMARY

    在创建索引时,在 ON 子句指定文件组选项,那么该表的索引结构将存储在文件组包含的文件中:
    
CREATE NONCLUSTERED INDEX index_name 
ON tb_name( [colume] ASC ) 
ON fg_name

    在创建分区时,在 TO 字句中指定文件组,每一个分区将存储到文件组中:
    
REATE PARTITION SCHEME scheme_name
AS PARTITION function_name 
TO ([fg_name1], <....>, [fp_nameN])

    SQL 语句创建数据库并创建多个文件组
    现提供示例如下:
    
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

    使用文件组的优势
    在实际开发数据库的过程中,通常情况下,用户需要关注文件组,而不用关心文件的物理存储,即使DBA改变文件的物理存储,用户也不会察觉到,也不会影响数据库去执行查询。除了逻辑文件和物理文件的分离之外,SQL Server使用文件组还有一个优势,那就是分散IO负载,其实现的原理是:
    
  • 对于单分区表,数据只能存到一个文件组中。如果把文件组内的数据文件分布在不同的物理硬盘上,那么SQL Server能同时从不同的物理硬盘上读写数据,把IO负载分散到不同的硬盘上。
  • 对于多分区表,每个分区使用一个文件组,把不同的数据子集存储在不同的磁盘上,SQL Server在读写某一个分组的数据时,能够调用不同的硬盘IO。

    这两种方式,其本质上,都是使每个硬盘均摊系统负载,提高IO性能。
    创建分区表时,不同的分区可以使用相同的文件组,也可以使用不同的文件组。因此,在设计文件组时,应尽量把包含的文件包含在不同的硬盘上,以实现物理IO的最大分散化。
    在创建文件时,服务器CPU核的数量,决定最大的并发IO度,应该根据CPU 核的数量创建多个文件。通常情况下,文件的数量和CPU核的数量一致,是最优化的设计。
    还有,应该根据硬盘的性能来创建文件组,日志文件存储到性能最好的硬盘上,而查询延迟要求高的数据,也需要存储到性能最好的硬盘上。
    不是所有的数据都是同等重要的,应该根据业务需求和查询延迟,对数据分级,因此,在设计文件组时,应该把级别高的数据分散,而把那些基本用不到的数据存储到性能差的,用于存储归档数据的硬盘上,以实现服务器性能的合理配置。
    数据文件自动增长导致的问题
    当数据文件爆满,没有空间存储数据时,此时执行insert命令,这会导致数据文件的增长。如果filegrowth选项设置的过大,会导致SQL Server耗费较长时间来实现文件的增长,在数据文件增长时,该文件是不能访问的,因此,即使用户仅插入一条数据,也要等待很长时间才能完成查询,对用户来说,体验不友好。
    数据文件增长是非常耗费系统资源和影响性能,如果设置SQL Server 自动增长,可能会导致系统性能不够稳定,所以,应该预测可能的空间使用需求,并提前做好规划。尽量避免空间用尽而使得SQL Server不得不自动增长的现象发生。同时也要确保每一次自动增长都能够在可接受的时间内完成,及时满足客户端应用的需求。