[大数据]在Azure Synapse Analytics(专用 SQL 池)中对表进行分区

目录

（一）前言

（二）什么是表分区？

1. 表分区定义

2.?对加载的好处? ? ?

3.?对查询的好处

（三）分区大小

（四）Azure SQL DW中的分区语法

（五）实战案例

1. 表中现有数据

2. 重构建表语句

3. 保留原表并与新建表后的SQL统计作耗时对比

（1）未优化重新建表前

（2）优化后

（一）前言

? ? ? ?SQL Server、Azure SQL 数据库和Azure SQL 托管实例支持表和索引分区。 已分区表和索引的数据分为可以分布在数据库中的多个文件组或存储在单个文件组中的单位。 当文件组中存在多个文件时，数据使用?比例填充算法跨文件分布。 数据是按水平方式分区的，因此多组行映射到单个的分区。 单个索引或表的所有分区都必须位于同一个数据库中。 对数据进行查询或更新时，表或索引将被视为单个逻辑实体。本文重点探讨在专用SQL池中新建分区表。

? ? ? ?表分区在 Azure Synapse Analytics 的专用SQL池中也可用，语法存在一些差异。

重要

默认情况下，数据库引擎最多支持 15，000 个分区。 在早于 SQL Server 2012 (11.x) 的版本中，默认情况下，分区数限制为 1000 个。

（二）什么是表分区？

1. 表分区定义

? ? ? ?表分区可将数据分成更小的数据组。 大多数情况下，表分区都是根据日期列进行创建。 所有专用 SQL 池表类型（包括聚集列存储、聚集索引和堆）都支持分区。 所有分布类型（包括哈希分布或轮循机制分布）也都支持分区。

? ? ? ?分区可能有利于数据维护和查询性能。 分区是对二者都有利还是只对其中之一有利取决于数据加载方式，以及是否可以将同一个列用于两种目的，因为只能根据一个列来进行分区。

2.?对加载的好处
? ? ?

? ? ? ?在专用 SQL 池中进行分区的主要好处是通过分区删除、切换和合并来提高数据加载效率和性能。 大多数情况下，数据是根据日期列来分区的，而日期列与数据加载到 SQL 池中的顺序密切相关。 使用分区来维护数据的最大好处之一是可以避免事务日志记录。 虽然直接插入、更新或删除数据可能是最直接的方法，但如果在加载过程中使用分区，则只需付出一点点思考和努力就可以大大改进性能。

? ? ? ?可以使用分区切换来快速删除或替换表的一部分。 例如，销售事实表可能仅包含过去 36 个月的数据。 在每个月月底，便从表删除最旧月份的销售数据。 删除该数据时，可以使用 delete 语句删除最旧月份的数据。

? ? ? ?但是，使用 delete 语句逐行删除大量数据可能需要极长的时间，同时还会有执行大型事务的风险，这些大型事务在出现错误时进行回退的时间会很长。 更理想的方式是删除最旧的数据分区。 如果在某种情况下删除各个行可能需要数小时，则删除整个分区可能只需数秒钟。

3.?对查询的好处

? ? ? ?分区还可用来提高查询性能。 对分区数据应用筛选器的查询可以将扫描限制在合格的分区上。 此筛选方法可以避免全表扫描且仅扫描数据的一个较小子集。 引入聚集列存储索引以后，谓词消除的性能好处不再那么明显，但在某些情况下，可能会对查询有好处。

? ? ? ?例如，如果使用销售日期字段将销售事实表分区成 36 个月，以销售日期进行筛选的查询便可以跳过对不符合筛选条件的分区的搜索。

（三）分区大小

? ? ? ?虽然在某些情况下可以使用分区来改进性能，但如果在创建表时使用过多分区，则在某些情况下可能会降低性能。 对于聚集列存储表，尤其要考虑到这一点。

? ? ? ?若要使数据分区有益于性能，务必了解使用数据分区的时机，以及要创建的分区的数目。 对于多少分区属于分区过多并没有简单的硬性规定，具体取决于数据，以及要同时加载多少分区。 一个成功的分区方案通常只有数十到数百的分区，没有数千个。

? ? ? ?在“聚集列存储”表上创建分区时，务请考虑每个分区可容纳的行数 。 对于聚集列存储表来说，若要进行最合适的压缩并获得最佳性能，则每个分布和分区至少需要 1 百万行。 在创建分区之前，专用 SQL 池已将每个表细分到 60 个分发中。

? ? ? ? 向表添加的任何分区都是基于在后台创建的分布。 根据此示例，如果销售事实数据表包含 36 个按月进行的分区，并假设专用 SQL 池有 60 个分布区，则销售事实数据表每个月应包含 6000 万行，或者在填充所有月份后包含 21 亿行。 如果表包含的行数少于每个分区行数的最小建议值，可考虑使用较少的分区，以增加每个分区的行数。

（四）Azure SQL DW中的分区语法

专用 SQL 池引入了一种比 SQL Server 简单的定义分区的方法。 在专用 SQL 池中使用分区函数和方案的方法与在 SQL Server 中不一样。 只需识别分区列和边界点。

尽管分区的语法可能与 SQL Server 稍有不同，但基本概念是相同的。 SQL Server 和专用 SQL 池支持一个表一个分区列，后者可以是范围分区。

下例使用?CREATE TABLE
?语句根据?OrderDateKey?列对?FactInternetSales?表进行分区：

CREATE TABLE [dbo].[FactInternetSales]
(
    [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,   DISTRIBUTION = HASH([ProductKey])
,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                    (20000101,20010101,20020101
                    ,20030101,20040101,20050101
                    )
                )
);

（五）实战案例

? ? ? ?公司数仓中有一张APP和小程序埋点的记录表(每日导入更新一次)，每天生成的数据量比较庞大，目前已经有将近1.7亿条数据。为了更好满足未来业务对于数仓中这张表的使用，我在此用分区的方式重建这张表

1. 表中现有数据

2. 重构建表语句

(Partition分区原则采用2020年1月1日到2030年12月31日每个月作一个分区)

CREATE TABLE [dw].[XXXX]
(
	[TenantId] [int] NOT NULL,
	[CustomerId] [nvarchar](200) NULL,
	[Event] [nvarchar](4000) NOT NULL,
	[FieldDate] [datetime] NOT NULL,
	[ExternalId] [nvarchar](4000) NOT NULL,
	[Id] [bigint] NULL,
	[Attr1] [nvarchar](4000) NULL,
	[Attr2] [nvarchar](4000) NULL,
	[Attr3] [nvarchar](max) NULL,
	[Attr4] [nvarchar](max) NULL,
	[Attr5] [nvarchar](max) NULL,
	[ChannelAccount] [nvarchar](max) NULL,
	[ChannelType] [nvarchar](4000) NULL,
	[Device] [nvarchar](max) NULL,
	[Employee] [nvarchar](max) NULL,
	[Inbound] [int] NULL,
	[FieldLocation] [nvarchar](max) NULL,
	[Referrer] [nvarchar](max) NULL,
	[ReferrerName] [nvarchar](max) NULL,
	[Tag] [nvarchar](max) NULL,
	[TargetId] [nvarchar](max) NULL,
	[TargetName] [nvarchar](max) NULL,
	[Campaign] [nvarchar](max) NULL,
	[CampaignName] [nvarchar](max) NULL,
	[ContentName] [nvarchar](max) NULL,
	[Source] [nvarchar](max) NULL,
	[Summary] [nvarchar](max) NULL,
	[InternalContentId] [nvarchar](max) NULL,
	[LastForwarder] [nvarchar](max) NULL,
	[Score] [int] NULL,
	[LastUpdated] [datetime] NULL,
	[RefScore] [int] NULL,
	[PageId] [nvarchar](max) NULL,
	[EventGroup] [nvarchar](4000) NULL,
	[BatchId] [nvarchar](max) NULL,
	[FromCollector] [int] NULL,
	[AppName] [nvarchar](max) NULL,
	[AppVersion] [nvarchar](max) NULL,
	[Quantity1] [bigint] NULL,
	[Quantity2] [bigint] NULL,
	[Amount1] [decimal](18, 2) NULL,
	[Amount2] [decimal](18, 2) NULL,
	[IdentityValue] [nvarchar](4000) NULL,
	[IdentityType] [nvarchar](max) NULL,
	[IdentityValue2] [nvarchar](max) NULL,
	[IdentityType2] [nvarchar](4000) NULL,
	[IdentityValue3] [nvarchar](max) NULL,
	[IdentityType3] [nvarchar](4000) NULL,
	[IsFirstTry] [int] NULL,
	[IsFirstEngage] [int] NULL,
	[Duration] [int] NULL,
	[Utma] [nvarchar](max) NULL,
	[Utm] [nvarchar](max) NULL,
	[Quantity3] [bigint] NULL,
	[Date1] [bigint] NULL,
	[Date2] [bigint] NULL,
	[Platform] [nvarchar](4000) NULL,
	[Os] [nvarchar](4000) NULL,
	[OsVersion] [nvarchar](4000) NULL,
	[Browser] [nvarchar](4000) NULL,
	[BrowserVersion] [nvarchar](4000) NULL,
	[Model] [nvarchar](4000) NULL,
	[Network] [nvarchar](4000) NULL,
	[Ip] [nvarchar](4000) NULL,
	[IpCounty] [nvarchar](max) NULL,
	[IpCity] [nvarchar](max) NULL,
	[IpProvince] [nvarchar](max) NULL,
	[IpCountry] [nvarchar](max) NULL,
	[ScreenWidth] [nvarchar](max) NULL,
	[ScreenHeight] [nvarchar](max) NULL,
	[Domain] [nvarchar](max) NULL,
	[Path] [nvarchar](max) NULL,
	[Attr6] [nvarchar](max) NULL,
	[Attr7] [nvarchar](max) NULL,
	[Attr8] [nvarchar](max) NULL,
	[Attr9] [nvarchar](max) NULL,
	[Attr10] [nvarchar](max) NULL,
	[Amount3] [decimal](18, 2) NULL,
	[Date3] [bigint] NULL,
	[PageType] [nvarchar](max) NULL,
	[SessionId] [nvarchar](max) NULL,
	[Url] [nvarchar](max) NULL,
	[Attr11] [nvarchar](4000) NULL,
	[Attr12] [nvarchar](max) NULL,
	[Attr13] [nvarchar](max) NULL,
	[Attr14] [nvarchar](max) NULL,
	[Attr15] [nvarchar](max) NULL,
	[Attr16] [nvarchar](max) NULL,
	[Attr17] [nvarchar](max) NULL,
	[Attr18] [nvarchar](max) NULL,
	[Attr19] [nvarchar](max) NULL,
	[Attr20] [nvarchar](max) NULL,
	[Attr21] [nvarchar](max) NULL,
	[Attr22] [nvarchar](max) NULL,
	[Attr23] [nvarchar](max) NULL,
	[Attr24] [nvarchar](max) NULL,
	[Attr25] [nvarchar](max) NULL,
	[Attr26] [nvarchar](max) NULL,
	[Attr27] [nvarchar](max) NULL,
	[Attr28] [nvarchar](max) NULL,
	[Attr29] [nvarchar](max) NULL,
	[Attr30] [nvarchar](max) NULL,
	[Attr31] [nvarchar](max) NULL,
	[Attr32] [nvarchar](max) NULL,
	[Attr33] [nvarchar](max) NULL,
	[Attr34] [nvarchar](max) NULL,
	[Attr35] [nvarchar](max) NULL,
	[Attr36] [nvarchar](max) NULL,
	[Attr37] [nvarchar](max) NULL,
	[Attr38] [nvarchar](max) NULL,
	[Attr39] [nvarchar](max) NULL,
	[Attr40] [nvarchar](max) NULL,
	[Date4] [bigint] NULL,
	[Date5] [bigint] NULL,
	[Date6] [bigint] NULL,
	[Date7] [bigint] NULL,
	[Date8] [bigint] NULL,
	[Date9] [bigint] NULL,
	[Date10] [bigint] NULL,
	[Amount4] [decimal](18, 2) NULL,
	[Amount5] [decimal](18, 2) NULL,
	[Amount6] [decimal](18, 2) NULL,
	[Amount7] [decimal](18, 2) NULL,
	[Amount8] [decimal](18, 2) NULL,
	[Amount9] [decimal](18, 2) NULL,
	[Amount10] [decimal](18, 2) NULL,
	[Longitude] [decimal](18, 2) NULL,
	[Latitude] [decimal](18, 2) NULL,
	[Source1] [nvarchar](max) NULL,
	[Source2] [nvarchar](max) NULL,
	[Source3] [nvarchar](max) NULL,
	[Source4] [nvarchar](max) NULL,
	[Source5] [nvarchar](max) NULL,
	[Source6] [nvarchar](max) NULL,
	[Source7] [nvarchar](max) NULL,
	[Source8] [nvarchar](max) NULL,
	[FlowId] [int] NULL,
	[FlowVersion] [int] NULL,
	[StepId] [int] NULL,
	[ProfileId] [nvarchar](max) NULL,
	[Insertdatetime] [datetime] NULL,
	[Updatedatetime] [datetime] NULL,
	[FieldDateKey] [int] NOT NULL
)
WITH
(
    CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = HASH([Event]),
	PARTITION ([FieldDateKey] RANGE right FOR VALUES (20200101,
20200201,
20200301,
20200401,
20200501,
20200601,
20200701,
20200801,
20200901,
20201001,
20201101,
20201201,
20210101,
20210201,
20210301,
20210401,
20210501,
20210601,
20210701,
20210801,
20210901,
20211001,
20211101,
20211201,
20220101,
20220201,
20220301,
20220401,
20220501,
20220601,
20220701,
20220801,
20220901,
20221001,
20221101,
20221201,
20230101,
20230201,
20230301,
20230401,
20230501,
20230601,
20230701,
20230801,
20230901,
20231001,
20231101,
20231201,
20240101,
20240201,
20240301,
20240401,
20240501,
20240601,
20240701,
20240801,
20240901,
20241001,
20241101,
20241201,
20250101,
20250201,
20250301,
20250401,
20250501,
20250601,
20250701,
20250801,
20250901,
20251001,
20251101,
20251201,
20260101,
20260201,
20260301,
20260401,
20260501,
20260601,
20260701,
20260801,
20260901,
20261001,
20261101,
20261201,
20270101,
20270201,
20270301,
20270401,
20270501,
20270601,
20270701,
20270801,
20270901,
20271001,
20271101,
20271201,
20280101,
20280201,
20280301,
20280401,
20280501,
20280601,
20280701,
20280801,
20280901,
20281001,
20281101,
20281201,
20290101,
20290201,
20290301,
20290401,
20290501,
20290601,
20290701,
20290801,
20290901,
20291001,
20291101,
20291201,
20300101,
20300201,
20300301,
20300401,
20300501,
20300601,
20300701,
20300801,
20300901,
20301001,
20301101,
20301201,
20310101
) )
)

3. 保留原表并与新建表后的SQL统计作耗时对比

场景：假设需要统计2022年2月10日当日各个埋点行为事件的累计数字

（1）未优化重新建表前

（耗时43秒）

（2）优化后

（耗时10秒）