sqlserver中分表和分库有什么区别

发布时间:2025-05-21 10:49:37 发布人:远客网络

一、sqlserver中分表和分库有什么区别

MS SQL Server：分区表、分区索引详解

使用分区表的主要目的，是为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。

大型表：数据量巨大的表。

访问模式：因目的不同，需访问的不同的数据行集，每种目的的访问可以称之为一种访问模式。

分区一方面可以将数据分为更小、更易管理的部分，为提高性能起到一定的作用；另一方面，对于如果具有多个CPU的系统，分区可以是对表的操作通过并行的方式进行，这对于提升性能是非常有帮助的。

注意：只能在 SQL Server Enterprise Edition中创建分区函数。只有 SQL Server Enterprise Edition支持分区。

下面详细描述的创建分区表、分区索引的步骤。

在开始做分区操作之前，首先要确定待分区表的访问模式，该模式决定了什么列适合做分区键。例如，对于销售数据，一般会先根据日期把数据范围限定在一个范围内，然后在这个基础上做进一步的查询，这样，就可以把日期作为分区列。

确定了分区列之后，需要进一步确定分区数，亦即分区表中需要包含多少数据，每个分区的数据应该限定在哪个范围。

为了有助于优化性能和维护，应该使用文件组分离数据。一般情况下，如果经常对分区的整个数据集操作，则文件组数最好与分区数相同，并且这些文件组通常应该位于不同的磁盘上，再配合多个CPU，则SQL Server可以并行处理多个分区，从而大大缩短处理大量复杂报表和分析的总体时间。

分区函数用于定义分区的边界条件，创建分区函数的语法如下：

CREATE PARTITION FUNCTION partition_function_name( input_parameter_type)

FOR VALUES( [ boundary_value [,...n ] ])

 partition_function_name

是分区函数的名称。分区函数名称在数据库内必须唯一，并且符合标识符的规则。

是用于分区的列的数据类型。当用作分区列时，除 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、别名数据类型或 CLR用户定义数据类型外，所有数据类型均有效。

实际列（也称为分区列）是在 CREATE TABLE或 CREATE INDEX语句中指定的。

为使用 partition_function_name的已分区表或索引的每个分区指定边界值。如果 boundary_value为空，则分区函数使用 partition_function_name将整个表或索引映射到单个分区。只能使用 CREATE TABLE或 CREATE INDEX语句中指定的一个分区列。

boundary_value是可以引用变量的常量表达式。这包括用户定义类型变量，或函数以及用户定义函数。它不能引用 Transact-SQL表达式。boundary_value必须与 input_parameter_type中提供的数据类型相匹配或者可隐式转换为该数据类型，并且如果该值的大小和小数位数与 input_parameter_type中相应的值的大小和小数位数不匹配，则在隐式转换过程中该值不能被截断。

如果 boundary_value包含 datetime或 smalldatetime文字值，则为这些文字值在计算时假设 us_english是会话语言。不推荐使用此行为。要确保分区函数定义对于所有会话语言都具有预期的行为，建议使用对于所有语言设置都以相同方式进行解释的常量，例如 yyyymmdd格式；或者将文字值显式转换为特定样式。有关详细信息，请参阅编写国际化 Transact-SQL语句。若要确定服务器的语言会话，请运行 SELECT@@LANGUAGE。

指定 boundary_value提供的值的数目，不能超过 999。所创建的分区数等于 n+ 1。不必按顺序列出各值。如果值未按顺序列出，则 Microsoft SQL Server 2005数据库引擎将对它们进行排序，创建函数并返回一个警告，说明未按顺序提供值。如果 n包括任何重复的值，则数据库引擎将返回错误。

指定当间隔值由数据库引擎按升序从左到右排序时，boundary_value [,...n ]属于每个边界值间隔的哪一侧（左侧还是右侧）。如果未指定，则默认值为 LEFT。

CREATE PARTITION FUNCTION PF_Left(int)

CREATE PARTITION FUNCTION PF_Right(int)

PF_Left和 PF_Right分区函数的区分：

PF_Left<= 10> 10 and<= 20> 20

PF_Right< 10>= 10 and< 20>= 20

创建分区函数后，必须将其与分区架构（Schema）相关联，以便将分区定向至特定的文件组。定义分区架构师，即使多个分区位于同一个文件组中，也必须为每个分区指定一个文件组。

GOCREATE PARTITION SCHEME partition_scheme_name

AS PARTITION partition_function_name

[ ALL ] TO({ file_group_name| [ PRIMARY ]} [,...n ])

 partition_scheme_name

分区方案的名称。分区方案名称在数据库中必须是唯一的，并且符合标识符规则。

 partition_function_name

使用分区方案的分区函数的名称。分区函数所创建的分区将映射到在分区方案中指定的文件组。partition_function_name必须已经存在于数据库中。

指定所有分区都映射到在 file_group_name中提供的文件组，或映射到主文件组（如果指定了 [PRIMARY]。如果指定了 ALL，则只能指定一个 file_group_name。

 file_group_name| [ PRIMARY ] [,...n]

指定用来持有由 partition_function_name指定的分区的文件组的名称。file_group_name必须已经存在于数据库中。

如果指定了 [PRIMARY]，则分区将存储于主文件组中。如果指定了 ALL，则只能指定一个 file_group_name。分区分配到文件组的顺序是从分区 1开始，按文件组在 [,...n]中列出的顺序进行分配。在 [,...n]中，可以多次指定同一个 file_group_name。如果 n不足以拥有在 partition_function_name中指定的分区数，则 CREATE PARTITION SCHEME将失败，并返回错误。

如果 partition_function_name生成的分区数少于文件组数，则第一个未分配的文件组将标记为 NEXT USED，并且出现显示命名 NEXT USED文件组的信息。如果指定了 ALL，则单独的 file_group_name将为该 partition_function_name保持它的 NEXT USED属性。如果在 ALTER PARTITION FUNCTION语句中创建了一个分区，则 NEXT USED文件组将再接收一个分区。若要再创建一个未分配的文件组来拥有新的分区，请使用 ALTER PARTITION SCHEME。

在 file_group_name[ 1,...n]中指定主文件组时，必须像在 [PRIMARY]中那样分隔 PRIMARY，因为它是关键字。

CREATE PARTITION FUNCTION myRangePF1(int)

AS RANGE LEFT FOR VALUES(1, 100, 1000);

CREATE PARTITION SCHEME myRangePS1

TO(test1fg, test2fg, test3fg, test4fg);

定义了分区函数（逻辑结构）和分区架构（物理结构）后，既可以创建分区表来利用它们。分区表定义应使用的分区架构，而分区架构又定义其使用的分区函数。要将这三者结合起来，必须指定应用于分区函数的列。范围分区始终只映射到表中的一列。

[ database_name. [ schema_name ].| schema_name. ] table_name

({<column_definition>|<computed_column_definition>}

[<table_constraint> ] [,...n ])

[ ON{ partition_scheme_name( partition_column_name)| filegroup

[{ TEXTIMAGE_ON{ filegroup|"default"} ]

索引对于提高查询性能非常有效，因此，一般应该考虑应该考虑为分区表建立索引，为分区表建立索引与为普通表建立索引的语法一直，但是，其行为与普通索引有所差异。

默认情况下，分区表中创建的索引使用与分区表相同分区架构和分区列，这样，索引将于表对齐。将表与其索引对齐，可以使管理工作更容易进行，对于滑动窗口方案尤其如此。若要启动分区切换，表的所有索引都必须对齐。

在创建索引时，也可以指定不同的分区方案（Schema）或单独的文件组（FileGroup）来存储索引，这样SQL Server不会将索引与表对齐。

在已分区的表上创建索引（分区索引）时，应该注意以下事项：

建立唯一索引（聚集或者非聚集）时，分区列必须出现在索引列中。此限制将使SQL Server只调查单个分区，并确保表中宠物的新键值。如果分区依据列不可能包含在唯一键中，则必须使用DML触发器，而不是强制实现唯一性。

对非唯一的聚集索引进行分区时，如果未在聚集键中明确指定分区依据列，默认情况下SQL Server将在聚集索引列中添加分区依据列。

对非唯一的非聚集索引进行分区时，默认情况下SQL Server将分区依据列添加为索引的包含性列，以确保索引与基表对齐，若果索引中已经存在分区依据列，SQL Server将不会像索引中添加分区依据列。

分区适用于可以缩放的大型表，所以随着时间和环境的变化，就会产生对分区的拆分、合并、移动的需求。

通过拆分或合并边界值更改分区函数。通过执行 ALTER PARTITION FUNCTION，可以将使用分区函数的任何表或索引的某个分区拆分为两个分区，也可以将两个分区合并为一个分区。

注意：多个表或索引可以使用同一分区函数。ALTER PARTITION FUNCTION在单个事务中影响所有这些表或索引。

ALTER PARTITION FUNCTION语法如下：

ALTER PARTITION FUNCTION partition_function_name()

| MERGE RANGE( boundary_value)

 partition_function_name

 SPLIT RANGE( boundary_value)

在分区函数中添加一个分区。boundary_value确定新分区的范围，因此它必须不同于分区函数的现有边界范围。根据 boundary_value，Microsoft SQL Server 2005数据库引擎将某个现有范围拆分为两个范围。在这两个范围中，新 boundary_value所在的范围被视为是新分区。

文件组必须处于联机状态，并且必须由使用此分区函数的分区方案标记为 NEXT USED，以保存新分区。在 CREATE PARTITION SCHEME语句中，将把文件组分配给分区。如果 CREATE PARTITION SCHEME语句分配了多余的文件组（在 CREATE PARTITION FUNCTION语句中创建的分区数少于用于保存它们的文件组），则存在未分配的文件组，分区方案将把其中的某个文件组标记为 NEXT USED。该文件组将保存新的分区。如果分区方案未将任何文件组标记为 NEXT USED，则必须使用 ALTER PARTITION SCHEME添加一个文件组或指定一个现有文件组来保存新分区。可以指定已保存分区的文件组来保存附加分区。由于一个分区函数可以参与多个分区方案，因此所有使用分区函数（您向其中添加了分区）的分区方案都必须拥有一个 NEXT USED文件组。否则，ALTER PARTITION FUNCTION将失败并出现错误，该错误显示缺少 NEXT USED文件组的一个或多个分区方案。

 MERGE [ RANGE( boundary_value) ]

删除一个分区并将该分区中存在的所有值都合并到剩余的某个分区中。RANGE(boundary_value)必须是一个现有边界值，已删除分区中的值将合并到该值中。如果最初保存 boundary_value的文件组没有被剩余分区使用，也没有使用 NEXT USED属性进行标记，则将从分区方案中删除该文件组。合并的分区驻留在最初不保存 boundary_value的文件组中。boundary_value是一个可以引用变量（包括用户定义类型变量）或函数（包括用户定义函数）的常量表达式。它无法引用 Transact-SQL表达式。boundary_value必须匹配或可以隐式转换为其对应列的数据类型，并且当值的大小和小数位数不匹配其对应 input_parameter_type时，将无法在隐式转换过程中被截断。

二、数据库为什么分库分表

1、数据库涉及各种领域。即使同一领域也有不同需求，且有各种数据库软件，分库是很正常的。一个数据库内需要各种关系表，来避免冗余信息，使得数据库储存、检索效率提高。

2、数据库(Database)是按照数据结构来组织、存储和管理数据的仓库，它产生于距今六十多年前，随着信息技术和市场的发展，特别是二十世纪九十年代以后，数据管理不再仅仅是存储和管理数据，而转变成用户所需要的各种数据管理的方式。数据库有很多种类型，从最简单的存储有各种数据的表格到能够进行海量数据存储的大型数据库系统都在各个方面得到了广泛的应用。

3、在信息化社会，充分有效地管理和利用各类信息资源，是进行科学研究和决策管理的前提条件。数据库技术是管理信息系统、办公自动化系统、决策支持系统等各类信息系统的核心部分，是进行科学研究和决策管理的重要技术手段。