SQL Server哈希分区

表分区

--创建文件组
ALTER DATABASE Test
ADD FILEGROUP DistData;
GO
 
--添加文件到文件组
ALTER DATABASE Test 
ADD FILE 
(
    NAME = DistDatafile, --数据文件逻辑名称
    FILENAME = 'd:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\DistDatafile.ndf',  --存放位置--辅助数据文件
    SIZE = 5 MB,  --文件初始大小5mb
    MAXSIZE = UNLIMITED,  --最大文件大小
    FILEGROWTH = 5MB --或5%  --按5M或5%增长
)
TO FILEGROUP DistData;

--查看数据库文件组信息
sp_helpfile

--创建分区函数
CREATE PARTITION FUNCTION [Hashing] (INT) AS RANGE LEFT FOR VALUES (0, 1, 2)

--创建分区方案
CREATE PARTITION SCHEME [ps_Hashing] AS PARTITION [Hashing] ALL TO ([DistData])

--创建表
CREATE TABLE Invoice
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [invoiceMonth] [int] NULL,
    [invoiceDetail] [varchar](100) NULL,
    [custcode] [int] NULL,
    [dtDate] [datetime] NULL
)

--设置计算列
ALTER TABLE [dbo].[Invoice] 
ADD [HashValue]  AS ([id]%(3)) PERSISTED  NOT NULL
--ADD [HashValue]  AS CHECKSUM([id]) PERSISTED  NOT NULL

--创建索引
CREATE CLUSTERED INDEX [IX_Invoice_HashValue]  
ON [dbo].[Invoice]
([ID] ASC, [HashValue] ASC) 
ON [ps_Hashing]([HashValue])

--添加测试数据
INSERT INTO Invoice(invoiceMonth, invoiceDetail, custcode)
VALUES (1,'Test invoice 1',101),
    (2,'Test invoice 2 for second try',102),
    (3,'Test invoice 3 for second try',103),
    (4,'Test invoice 4 for second try',104),
    (5,'Test invoice 5 for second try',105),
    (6,'Test invoice 6 for second try',106),
    (7,'Test invoice 7 for second try',107),
    (8,'Test invoice 8 for second try',108)

--查看数据分发效果
SELECT $PARTITION.[Hashing]([HashValue]) AS 分区号,COUNT(*) AS 数据条数
FROM [Invoice]
GROUP BY $PARTITION.[Hashing]([HashValue])

 

文件和文件组

主要数据文件	.mdf	primary data file
次要数据文件	.ndf	secondary data file
事务日志文件	.ldf	log data file

 

 

 

 

主要数据文件（*.mdf）

主要数据文件的建议文件扩展名是 .mdf。
主要数据文件包含数据库的启动信息，并指向数据库中的其他文件，存储部分或全部的数据。用户数据和对象可存储在此文件中，也可以存储在次要数据文件中。
每个数据库有一个主要数据文件。
mdf 文件并非普通文件，因此不借助相应软件是无法打开 mdf 文件的。打开mdf文件的常用虚拟光驱软件主要有：Daemon Tools 、东方光驱魔术师等。

次要数据文件 （*.ndf）

次要数据文件的建议文件扩展名是 .ndf。
次要数据文件是可选的，由用户定义并存储用户数据，用于存储主数据文件未能存储的剩余数据和一些数据库对象。
通过将每个文件放在不同的磁盘驱动器上，次要文件可用于将数据分散到多个磁盘上。
如果数据库超过了单个 Windows 文件的最大大小，可以使用次要数据文件，这样数据库就能继续增长。

事务日志 （*.ldf）

事务日志的建议文件扩展名是 .ldf。
事务日志文件保存用于恢复数据库的事务日志信息。数据库的插入、删除、更新等操作都会记录在日志文件中，而查询不会记录在日志文件中。整个的数据库有且仅有一个日志文件。
每个数据库必须至少有一个日志文件。

 

使用文件组的优势

通常情况下，用户需要关注文件组，而不用关心文件的物理存储，即使DBA改变文件的物理存储，用户也不会察觉到，也不会影响数据库去执行查询。除了逻辑文件和物理文件的分离之外，SQL Server使用文件组还有一个优势，那就是分散IO负载，其实现的原理是：

对于单分区表，数据只能存到一个文件组中。如果把文件组内的数据文件分布在不同的物理硬盘上，那么SQL Server能同时从不同的物理硬盘上读写数据，把IO负载分散到不同的硬盘上。
对于多分区表，每个分区使用一个文件组，把不同的数据子集存储在不同的磁盘上，SQL Server在读写某一个分组的数据时，能够调用不同的硬盘IO。
这两种方式，其本质上，都是使每个硬盘均摊系统负载，提高IO性能。

创建分区表时，不同的分区可以使用相同的文件组，也可以使用不同的文件组。因此，在设计文件组时，应尽量把包含的文件包含在不同的硬盘上，以实现物理IO的最大分散化。

在创建文件时，服务器CPU核的数量，决定最大的并发IO度，应该根据CPU 核的数量创建多个文件。通常情况下，文件的数量和CPU核的数量一致，是最优化的设计。

还有，应该根据硬盘的性能来创建文件组，日志文件存储到性能最好的硬盘上，而查询延迟要求高的数据，也需要存储到性能最好的硬盘上。

不是所有的数据都是同等重要的，应该根据业务需求和查询延迟，对数据分级，因此，在设计文件组时，应该把级别高的数据分散，而把那些基本用不到的数据存储到性能差的，用于存储归档数据的硬盘上，以实现服务器性能的合理配置。

 

数据文件自动增长导致的问题

当数据文件爆满，没有空间存储数据时，此时执行insert命令，这会导致数据文件的增长。如果filegrowth选项设置的过大，会导致SQL Server耗费较长时间来实现文件的增长，在数据文件增长时，该文件是不能访问的，因此，即使用户仅插入一条数据，也要等待很长时间才能完成查询，对用户来说，体验不友好。

数据文件增长是非常耗费系统资源和影响性能，如果设置SQL Server 自动增长，可能会导致系统性能不够稳定，所以，应该预测可能的空间使用需求，并提前做好规划。尽量避免空间用尽而使得SQL Server不得不自动增长的现象发生。同时也要确保每一次自动增长都能够在可接受的时间内完成，及时满足客户端应用的需求。

 

指定文件组

--在创建表时，在 ON 子句中指定文件组
CREATE TABLE tb_name(
...
) ON fg_name

--在创建索引时，在 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])

 

参考：https://blog.csdn.net/culuo4781/article/details/107617292

           https://blog.csdn.net/Ruishine/article/details/114014930

           https://www.cnblogs.com/kissdodog/p/3156166.html