SQL Server数据切片——实现高效分析

什么是数据切片？

数据切片是一种分布式数据库设计，在这种设计中，数据被分成一些水平分割，并在多个数据库服务器，也称为分片服务器，之间分布。每个服务器存储和操作数据子集的一部分，因此整个系统可以在相同或更小的总时间内处理更多的数据。

SQL Server中的数据切片

SQL Server是一种关系型数据库管理系统，可以通过数种方法实现数据切片。其中包括横向（水平）和纵向（垂直）分割。横向分割将表行分成均等的数据块，每个块都存在于不同的节点上。纵向分割则将表列细分为块，并将它们存储在不同的表中。

横向切片

横向分割是处理大型表的一种非常实用的方法，它可以通过将表中的数据分成多个数据块来提高查询性能。例如，如果有一个拥有数百万行的表，即使使用索引也可能需要几秒钟的查询时间。

可以通过以下示例进行横向切片：

--创建分区函数
CREATE PARTITION FUNCTION myRangePF (int)
AS RANGE LEFT FOR VALUES (100, 1000, 10000)
 
--创建分区架构，并在该架构上分割表。
CREATE PARTITION SCHEME myRangePS
AS PARTITION myRangePF
TO (Primary,[fg2], [fg3], [fg4]);
 
ALTER TABLE Sales.ProjectedSales
REBUILD PARTITION = ALL
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
          SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

在上面的示例中，将“ myRangePF ”函数定义为范围左侧函数。将此函数添加到“ myRangePS ”的分区架构中。在四个不同的文件组中创建四个分区。在表“ Sales.ProjectedSales ”上重新生成分区。

纵向切片

纵向分割适用于查询的模式大不相同或存在大数据及大量的空间的情况下。当一个表是由多个复杂的列组成时，进行纵向分割就可以通过减少不必要的I/O提高查询性能。

以下是示例查询：

SELECT *
FROM (
    SELECT vOrderNumber, Product_Cross AS Product, Quantity, Price  
    FROM Sales.VerticalPartition
    WHERE Product_Cross = 'jacket'
    UNION ALL
    SELECT vOrderNumber, Product_Color  AS Product,  Quantity, Price  
    FROM Sales.VerticalPartition
    WHERE Product_Color = 'purple'
    UNION ALL
    SELECT vOrderNumber, Product_Texture AS Product, Quantity, Price  
    FROM Sales.VerticalPartition
    WHERE Product_Texture = 'cashmere'
) AS p
ORDER BY vOrderNumber;

在上面的示例中，将三个列放入不同的表中。在下面的查询中，使用 UNION ALL 将它们合并在一起。这种纵向切片可以减少表的I/O量。

实现高效分析

数据切片的核心目标之一是提高查询性能。但是，如何实现高效分析呢？在下面的示例中，将演示如何在具有多个分区的大型表上执行分析查询。

SELECT State, COUNT(*) AS Orders,
    SUM(CASE WHEN TotalDue > 100000 THEN 1 ELSE 0 END) AS ExpensiveOrders,
    SUM(TotalDue) AS OrderTotal
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '20070101' AND '20070630'
GROUP BY State;

上述查询显示了每个州的订单总数，大于$100,000的订单数和订单总数。

使用以下代码，可以将 Sales.SalesOrderHeader 表水平分成5个分区，并为每个分区创建单独的文件组：

-- 创建分区函数
CREATE PARTITION FUNCTION SalesOrderRange (datetime)
AS RANGE LEFT FOR VALUES
  ('20020701','20030701','20040701','20050701','20060701','20070701','20080701','20090701','20100701','20110701');
 
-- 创建分区架构
CREATE PARTITION SCHEME SalesOrderPartScheme
AS PARTITION SalesOrderRange
TO (fg_SalesOrderRange1,fg_SalesOrderRange2,fg_SalesOrderRange3,fg_SalesOrderRange4,fg_SalesOrderRange5);
 
-- 更改 SalesOrderHeader 表以分区
ALTER TABLE Sales.SalesOrderHeader SET (LOCK_ESCALATION=TABLE)
ALTER TABLE Sales.SalesOrderHeader SWITCH PARTITION 1 TO Sales.SalesOrderHeader_Partition1;
ALTER TABLE Sales.SalesOrderHeader SWITCH PARTITION 2 TO Sales.SalesOrderHeader_Partition2;
ALTER TABLE Sales.SalesOrderHeader SWITCH PARTITION 3 TO Sales.SalesOrderHeader_Partition3;
ALTER TABLE Sales.SalesOrderHeader SWITCH PARTITION 4 TO Sales.SalesOrderHeader_Partition4;
ALTER TABLE Sales.SalesOrderHeader SWITCH PARTITION 5 TO Sales.SalesOrderHeader_Partition5;

使用以下代码，将 Sales.SalesOrderHeader 表的每个分区设置为可读/写状态：

ALTER PARTITION SCHEME SalesOrderPartScheme 
NEXT USED fg_SalesOrderRange1;
 
ALTER PARTITION FUNCTION SalesOrderRange() 
SPLIT RANGE ('20080101');

现在，可以通过以下查询执行类似的分析查询：

SELECT State, COUNT(*) AS Orders,
    SUM(CASE WHEN TotalDue > 100000 THEN 1 ELSE 0 END) AS ExpensiveOrders,
    SUM(TotalDue) AS OrderTotal
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '20070101' AND '20070630'
GROUP BY State;

上述查询将使用数据分区进行查询执行。这将大大提高查询性能。

总结

通过SQL Server的数据切片技术，可以将大型表有效地分割，以提高查询性能和分析效率。横向和纵向分割技术是实现数据切片的两种基本方法，可以根据不同情况进行选择使用。在合适的情况下，将表水平分割为多个分区可以大大提高查询速度。