sql语句大全之SQL Server 文件和文件组

数据库是数据的仓库，用于存储数据，而存储数据需要媒介，现在的存储媒介，最常用的是硬盘，土豪一点的服务器使用固态硬盘（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不得不自动增长的现象发生。同时也要确保每一次自动增长都能够在可接受的时间内完成，及时满足客户端应用的需求。

五，仅使用MDF和LDF文件还原数据库

在测试服务器上，有一个DB无法访问，原因是服务器上安装的是SQL Server的评估版本，在试用期过期之后，SQL Server实例无法打开，导致DB无法访问。该数据，只有MDF和LDF文件，没有做数据库的还原和分离操作，要还原数据库必须使用特殊的方法：重建一个名字相同DB，然后替换数据库的文件。

• Step1，新建一个同名的数据库，该DB和过期的DB有相同文件；
• Step2，将新DB脱机，删除其MDF和LDF文件；
• Step3，将过期DB的MDF和LDF文件复制到相应的路径下；
• Step4，将新DB联机，查看DB的数据，及时备份和还原。

 

 

推荐文档：

文件自动增长和自动收缩sql server