共计 3815 个字符，预计需要花费 10 分钟才能阅读完成。
本篇内容介绍了“sql server 中 filegroup 与 partition 怎么使用”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
1. 基础知识
一直对于表分区和 filegroup 的概念不是很清晰，今天通过具体的实例来学习什么事 filegroup 和 partition，以及他们的作用。
1.1 通过文件组来管理文件的特性
对于用户角度来说，需对创建的对象指定存储的文件组只有三种数据对象：表, 索引和大对象（LOB）
使用文件组可以隔离用户和文件，使得用户针对文件组来建立表和索引，而不是实际磁盘中的文件。也就是可以指定将表和索引存储在不同的文件上面。
使用文件组来管理文件可以使得同一文件组内的不同文件分布在不同的硬盘中，极大的提高了 IO 性能.
SQL SERVER 会根据每个文件设置的初始大小和增长量会自动分配新加入的空间，假设在同一文件组中的文件 A 设置的大小为文件 B 的两倍，新增一个数据占用三页 (Page), 则按比例将 2 页分配到文件 A 中，1 页分配到文件 B 中.
1.2 文件的分类
首要文件: 这个文件是必须有的，而且只能有一个。这个文件额外存放了其他文件的位置等信息. 扩展名为.mdf
次要文件: 可以建任意多个，用于不同目的存放. 扩展名为.ndf，用于存放数据，而不是日志。
日志文件：存放日志, 扩展名为.ldf
在 SQL SERVER 2008 之后，还新增了文件流数据文件和全文索引文件.
我们可以通过 sys.database_files 这个视图查看数据库中的文件情况:
双击代码全选
1select*from sys.database_files
1.3 创建 filegroup，并将索引创建在指定的 filegroup 中
可 以通过 TSQL 语句来创建文件组，也可以通过 SSMS 来创建文件组，这个在后面会提到。这里不再重复。下面我们重点来介绍如何将索引创建在指定的 filegroup 中，而不跟数据放在一起。首先来看我创建好的 filegroup，已经这些 filegroup 所对应的 files，如下图所示：
然后我们通过如下 TSQL 语句来测试
双击代码全选
28use TESTDB
–step1. 插入数据
select * into OrderDetail from AdventureWorks2008R2.Sales.SalesOrderDetail
–step2: 查看表的索引信息，发现所有页都在 pagefid= 1 上面，并且 indexid 都为 0. 因为没有创建聚集索引之前是堆表
dbcc ind (TESTDB, [dbo.OrderDetail], -1)
–step3: 在分区上创建聚集索引，聚集索引不要放在 IndexStorage 这个 filegroup 当中，因为聚集索引就是数据本身。– 如果将聚集索引 on IndexStorage 的话，那么所有数据都将会在 IndexStorage 这个 filegroup 所对应的文件上 create clustered index idx_c_SSalesOrderDetailID on OrderDetail(SalesOrderDetailID)
–step4: 此时发现原先 indexid= 0 的都变成了 index=1 dbcc ind (TESTDB, [dbo.OrderDetail], -1)
–step5: 在 IndexStorage 这个 file group 上面创建非聚集索引 CREATE NONCLUSTERED INDEX idx_nc_SalesOrderID ON dbo.OrderDetail(SalesOrderID) on IndexStorage CREATE NONCLUSTERED INDEX idx_nc_CarrierTrackingNumber ON dbo.OrderDetail(CarrierTrackingNumber) on IndexStorage CREATE NONCLUSTERED INDEX idx_nc_UnitPrice ON dbo.OrderDetail(UnitPrice) on IndexStorage
–step6: 再次查看页信息我们发现只有 indexid= 1 的 pagefid=1，也就是说聚集索引都在 TESTDB.MDF 这个文件上，– 而 indexid=2,3,4 所对应的 pagefid=3，表明已经将索引建立到 IndexStorage 这个 filegroup 上面去了，对应的是 IndexStorage.ndf 这个文件。dbcc ind (TESTDB, [dbo.OrderDetail], -1)
–step7: 创建复合索引，CREATE NONCLUSTERED INDEX idx_nc_com ON dbo.OrderDetail(SalesOrderID,CarrierTrackingNumber,UnitPrice)
–step8: 默认情况下会使用 Primary 这个 filegroup，filefid=1. dbcc ind (TESTDB, [dbo.OrderDetail], -1)
总结：
在分区上创建聚集索引，聚集索引不要放在 IndexStorage 这个 filegroup 当中，因为聚集索引就是数据本身。如果将聚集索引 on IndexStorage 的话，那么所有数据都将会在 IndexStorage 这个 filegroup 所对应的文件上。
在创建非聚集索引的时候，通过在创建索引语句的最后加上 on [filegroup] 指定需要将这个索引放在哪一个 filegroup 当中，如果不加的话会使用默认 filegroup，我们这里的默认 filegroup 是 priamry。
1.4 使用多个文件的好处
使用多个文件分布数据到多个硬盘中可以极大的提高 IO 性能. 放在一个磁盘中基本没有效果。
场景描述
应用程序发来大量的并发语句在修改同一张表格里的记录，而表格架构设计以及用户业务逻辑使得这些修改都集中在同一个页面，或者数量不多的几个页面上。这些页面有的时候也被称为 Hot Page。这样的瓶颈通常只会发生在并发用户比较多的、典型的 OLTP 系统上。这种瓶颈是无法通过提高硬件配置解决的，只有通过修改表格设计或者业务逻辑，让修改分散到尽可能多的页面上，才能提高并发性能。
在现实环境里，可以试想下面的情形。一个股票交易系统，每一笔交易都会有一个流水号，是递增且不可重复的。而客户发过来的交易请求，都要存储在同一张交易表里。每一个新的交易，都要插入一条新记录。如果设计者选择在流水号上建聚集索引（这也是很自然的），就容易遇到 Hot Page 的 PAGELATCH 资源瓶颈。在同一时间，只能有一个用户插入一笔交易。
怎样才能解决或者缓解这种瓶颈呢？
最简单的方法，是换一个数据列建聚集索引，而不要建在 Identity 的字段上。这样表格里的数据就按照其他方式排序，同一时间的插入就有机会分散在不同的页面上。
如果实在是一定要在 Identity 的字段上建聚集索引，建议根据其他某个数据列在表格上建立若干个分区（Partition）。把一个表格分成若干个分区，可以使得接受新数据的页面数目增加。
还是以上面那个股票交易系统为例子。不同的股票属于不同的行业。开发者可以根据股票的行业属性，将一张交易表分成若干个分区。在 SQL Server 里，已分区表（Partitioned Table）的每个分区都是一个独立的存储单位。分属不同分区的数据行是严格分开存储的。所以同一个时间发生的交易记录，因其行业不同，也会被分别保存在不同的分区里。这样，在同一个时间点，可以插入不同行业的交易记录。每个分区上的 Hot Page（接受新数据插入的 page）就不那么 hot 了。
在我的事例中，是有一张 SalesOrderDetail 表，其数据量很大，我希望按照 UnitPrice 这个字段进行分区。下面来看具体步骤。
step1：创建 filegroup
在 sql server 中好像没有 create filegroup 的说法，只是在现成的数据库中添加 filegroup 而已。下面的代码中首先创建数据库，然后添加四个 filegroup，tsql 代码如下所示：
双击代码全选
12–step1—— – 创建数据库 create database TEST USE MASTER GO –40 万行分成 5 个文件组，PRIMARY 加下面四个文件组，– 命名规则:FG_数据库名_表名_字段名_流水号 ALTER DATABASE TEST ADD FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_1; ALTER DATABASE TEST ADD FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_2; ALTER DATABASE TEST ADD FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_3; ALTER DATABASE TEST ADD FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_4; GO
“sql server 中 filegroup 与 partition 怎么使用”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
