SQL Server On Linux(21)—— SQL Server On Linux性能(7)——性能进阶简介——分区(1)

本人新书上市,请多多关照: 《SQL Server On Linux运维实战 2017版从入门到精通》

本篇开始专门对性能进行一系列讲解,这一系列不限于Linux平台,更多的是针对SQL Server本身。

SQL Server性能新特性

SQL Server发展至今,为了不断提升性能,引入了不少最新技术。它们主要以分区表/索引,In-Memory OLTP 和列存储索引这三类核心技术为主。虽然这些技术有各自的使用场景,但是都具有一个共同的目标:针对关键系统大幅度提高性能。这些功能不仅仅是提升性能,而且还是大幅度提升!

接下来会简单介绍这三种技术,有机会的话再细说具体技术。由于工作需要,首先从分区表和分区索引入手。

分区表和分区索引

这个严格意义来说不能称为“新技术“,因为从SQL Server 2005开始就有了。而且业界很多数据库产品都有分区功能。分区的出现源自于大量关系数据的出现。当一个表存储了亿级(请更新你对几百万行数据就成为“大表“的叫法),增删改查的性能将明显下降,其维护成本(比如备份/重建索引/更新统计信息等等)都会变得非常大。

因此,业界通常会对表进行拆分操作。拆分通常有***垂直拆分***和***横向拆分***, 垂直拆分大概就是把一个有很多列的表,按照业务逻辑,拆成多个行数相等但是列数更少的表。这种拆分***并不减少数据行数***,但是可以减少列数,可以减少很多开销,特别是索引相关的开销。横向拆分也是根据业务,把数据按某些条件来拆分搬到别的相同结构的新表中,比如按照日期、地区等,把一个存有全国一年数据的表,拆成34个表(按照省级行政区域),也可以拆成12个表(按照月份)或者综合这两种方法,使其体积降到原有的1/n,大大减少单表体积,增大数据操作的性能。

其中垂直拆分通常会针对明细表或者DW类型的宽表,因为这个方式更多是减少列数。但是对于常规的OLTP系统而言,其问题往往在于行数太多,这时候通常用横向拆分,也就是把表的部分数据搬到其他表中。

在SQL Server 2000时代,只能通过把数据拆到“实体”表中,实现横向拆分,但是这种方式下,表的个数可能会突增,而且需要很多额外维护操作,在编写SQL语句时,也需要使用类似UNION/UNION ALL或者视图等方式来合并起来,同时需要记住某些筛选条件需要访问哪些具体的实体表,总而言之,用起来很不友好。

从SQL 2005开始,引入了分区技术,简单来说,它就是由SQL Server帮你管理分区的功能,最大限度降低运维和使用成本的前提下面,减少最终直接操作的表的数据行数。不过需要提醒的是,分区实际上跟垂直和横向拆分是不一样的。

从业务出发,有一些数据天生就是可被“切片”的。比如前面提到的地区、时间,或者种类这些相对固化的特性。使用分区之前,先了解一下技术概念:

  1. 分区函数(partition function):用于通过使用一个范围的值来定义分区的数量和分区的范围(边界) 。就是定义哪个列的哪里到哪里的值属于某个分区。(定义范围)
  2. 分区方案(partition schem e ):定义分区函数使用那些文件组,通常一个分区会映射到一个或多个“用户”文件组上,但是这个过程只能用TSQL命令实现。(定义物理存储)
  3. 分区列(partition column):是具体分区的依据,分区函数配合这个列及其值来做分区,最常用的分区列就是日期了。

很多人听说过,分区的优点源自于数据量的减少。但是从技术来说,应该是来自于叫“partition elimination”(分区消除)。

但是很多人又可能认为,一旦出现了扫描操作,那么就意味着没有使用partition elimination,后面将会演示一下分区消除的内容。

分区演示

在本篇中,我们先快速演示一下简单的分区创建:

我们使用WideWorldImporters库做演示,因为这个库已经做了相应的分区,所以我们这里就不实操创建过程,不过可以展示一下,我们可以在数据库的【存储】看到创建了什么分区函数和分区方案:

然后查看红框的两个地方看一下它们具体的实现:

导出来的脚本如下:

USE [WideWorldImporters]
GO

/****** Object:  PartitionFunction [PF_TransactionDate]    Script Date: 2020/1/8 11:20:30 ******/
CREATE PARTITION FUNCTION [PF_TransactionDate](date) AS RANGE RIGHT 
FOR VALUES (N'2014-01-01T00:00:00.000', N'2015-01-01T00:00:00.000', 
N'2016-01-01T00:00:00.000', N'2017-01-01T00:00:00.000')
GO


USE [WideWorldImporters]
GO

/****** Object:  PartitionScheme [PS_TransactionDate]    Script Date: 2020/1/8 11:20:13 ******/
CREATE PARTITION SCHEME [PS_TransactionDate] AS PARTITION [PF_TransactionDate] 
TO ([USERDATA], [USERDATA], [USERDATA], [USERDATA], [USERDATA], [USERDATA])
GO

下面来解释一下:

  1. 分区函数定义了分区是基于一个date类型的列,同时指定了5个分区,每个分区是一个自然年。RANGE RIGHT意味着第五个分区是所有≥2017-01-01的值。
  2. 分区方案把分区函数映射到USERGROUP的文件组中,但是这个文件组包含了5个USERDATA的文件。这种方式可以分摊I/O性能和减缓单个磁盘的空间压力。并且可以通过使用“文件组”备份来处理超大规模的数据库备份工作。

现在回到表上面,我们导出表的脚本看看:

CREATE TABLE [Sales].[CustomerTransactions](
      [CustomerTransactionID] [int] NOT NULL,
      [CustomerID] [int] NOT NULL,
      [TransactionTypeID] [int] NOT NULL,
      [InvoiceID] [int] NULL,
      [PaymentMethodID] [int] NULL,
      [TransactionDate] [date] NOT NULL,
      [AmountExcludingTax] [decimal](18, 2) NOT NULL,
      [TaxAmount] [decimal](18, 2) NOT NULL,
      [TransactionAmount] [decimal](18, 2) NOT NULL,
      [OutstandingBalance] [decimal](18, 2) NOT NULL,
      [FinalizationDate] [date] NULL,
       [IsFinalized]  AS (case when [FinalizationDate] IS NULL then 
CONVERT([bit],(0)) else CONVERT([bit],(1)) end) PERSISTED,
      [LastEditedBy] [int] NOT NULL,
      [LastEditedWhen] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Sales_CustomerTransactions] PRIMARY KEY NONCLUSTERED
(
      [CustomerTransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [USERDATA]
) ON [PS_TransactionDate]([TransactionDate])
GO
CREATE CLUSTERED INDEX [CX_Sales_CustomerTransactions] ON [Sales].
[CustomerTransactions]
(
      [TransactionDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = 
ON) ON [PS_TransactionDate]([TransactionDate])
GO

表上的非聚集索引是基于CustomerTransactionID,但是分区是基于TransactionDate。同时聚集索引也是TransactionDate列,称之为“对齐(align)”

在表中的数据是根据元数据,并且按照分区函数中的范围来划分分区。分区函数和分区方案都是独立的,所以可以被重用,如果你查看[Purchasing].[SupplierTransactions]表,可以看到它和CustomerTransaction使用同样的分区函数和分区方案。

分区之后,来看看性能表现:

SET STATISTICS IO ON
GO
SET STATISTICS XML ON
GO
SELECT COUNT(*) FROM Sales.CustomerTransactions
WHERE TransactionDate between '2013-01-01' and '2014-01-01'
GO

开启执行计划后,我们看一下XML格式的执行计划:

PartitionAccessed表名访问了2个分区,然后分别是1和2(看 PartitionRange )。这个表名即使执行计划里面是扫描,底层执行的时候也不会真的需要访问全表。

到这里为止,我演示了简单的分区创建和简单的查询,主要目的是一种引入,接下来的文章会对分区做一个比较深入的介绍。

我来评几句
登录后评论

已发表评论数()

相关站点

热门文章