SQL Server 的 PolyBase 中的性能注意事项

  • 项目

适用于：SQL Server 2016 (13.x) - Windows 及更高版本 SQL Server 2017 (14.x) - Linux 及更高版本 Azure Synapse Analytics

在适用于 SQL Server 的 PolyBase 中，对于可查询的文件数量或数据量没有硬性限制。 查询性能取决于数据量、数据格式、数据的组织方式，以及查询和联接的复杂性。

本文介绍了重要的性能主题和指南。

统计信息

收集外部数据的统计信息对于查询优化而言是最重要的操作之一。 实例对数据了解得越多，执行查询的速度就越快。 SQL 引擎查询优化器是基于成本的优化器。 此优化器会对各种查询计划的成本进行比较，并选择成本最低的计划。 在大多数情况下，所选计划也是执行速度最快的计划。

自动创建统计信息

从 SQL Server 2022 开始，数据库引擎会分析传入的用户查询以确定缺少的统计信息。 如果缺少统计信息，查询优化器会在查询谓词或联接条件中各个列上自动创建统计信息，以改进查询计划的基数估计。 自动创建统计信息的过程是同步完成的，因此，如果列中缺少统计信息，查询性能可能会轻微下降。 为单个列创建统计信息所耗用的时间取决于目标文件的大小。

创建 OPENROWSET 手动统计信息

通过将单列作为参数传递选择查询，即可以使用 sys.sp_create_openrowset_statistics 存储过程可以创建 OPENROWSET 路径的单列统计信息：

EXEC sys.sp_create_openrowset_statistics N' 
SELECT pickup_datetime 
FROM OPENROWSET( 
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'', 
 FORMAT = ''parquet'') AS filerows';

默认情况下，实例使用数据集中提供的 100% 的数据创建统计信息。 还可以选择使用 TABLESAMPLE 选项以百分比方式指定样本大小。 若要为多个列创建单列统计信息，请为每个列执行 sys.sp_create_openrowset_statistics。 不能为 OPENROWSET 路径创建多列统计信息。

若要更新现有统计信息，请先使用 sys.sp_drop_openrowset_statistics 存储过程将其删除，然后使用 sys.sp_create_openrowset_statistics 重新创建：

EXEC sys.sp_drop_openrowset_statistics 
N'SELECT pickup_datetime 
FROM OPENROWSET( 
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'', 
 FORMAT = ''parquet'') AS filerows 
';

创建外部表手动统计信息

用于在外部表上创建统计信息的语法与用于普通用户表的语法相似。 若要基于某个列创建统计信息，需要提供统计信息对象的名称和列的名称：

CREATE STATISTICS sVendor 
ON tbl_TaxiRides (vendorID) 
WITH FULLSCAN, NORECOMPUTE;

WITH 选项为必填项，对于样本大小，允许的选项为 SAMPLE n PERCENT 和 FULLSCAN。

  • 若要为多个列创建单列统计信息，请为每个列执行 CREATE STATISTICS。
  • 不支持多列统计信息。

查询分区数据

数据通常以子文件夹（也称为分区）形式组织。 可以指示 SQL Server 实例仅查询特定文件夹和文件。 此操作可减少查询需要读取和处理的文件数量和数据量，从而提高性能。 这种类型的查询优化称为分区修剪或分区清除。 可以在查询的 WHERE 子句中使用元数据函数 filepath() 从查询执行中清除分区。

首先，创建一个外部数据源：

CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net'
);
GO

下面的示例查询仅读取 2017 年最后三个月纽约市黄色出租车的数据文件：

SELECT 
    r.filepath() AS filepath 
    ,r.filepath(1) AS [year] 
    ,r.filepath(2) AS [month] 
    ,COUNT_BIG(*) AS [rows] 
FROM OPENROWSET( 
        BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
        DATA_SOURCE = 'NYCTaxiExternalDataSource', 
        FORMAT = 'parquet' 
    ) 
WITH ( 
    vendorID INT 
) AS [r] 
WHERE 
    r.filepath(1) IN ('2017') 
    AND r.filepath(2) IN ('10', '11', '12') 
GROUP BY 
    r.filepath() 
    ,r.filepath(1) 
    ,r.filepath(2) 
ORDER BY filepath;

如果存储的数据未分区，请考虑将数据分区以提高查询性能。

如果使用的是外部表，则 WHERE 子句中不支持 filepath() 和 filename() 函数。 你仍可以按 filename 或 filepath 进行筛选，前提是在计算列中使用它们。 下面的示例演示这一操作：

CREATE EXTERNAL TABLE tbl_TaxiRides ( 
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2, 
 tpepPickupDateTime DATETIME2, 
 tpepDropoffDateTime DATETIME2, 
 passengerCount INT, 
 tripDistance FLOAT, 
 puLocationId VARCHAR(8000), 
 doLocationId VARCHAR(8000), 
 startLon FLOAT, 
 startLat FLOAT, 
 endLon FLOAT, 
 endLat FLOAT, 
 rateCodeId SMALLINT, 
 storeAndFwdFlag VARCHAR(8000), 
 paymentType VARCHAR(8000), 
 fareAmount FLOAT, 
 extra FLOAT, 
 mtaTax FLOAT, 
 improvementSurcharge VARCHAR(8000), 
 tipAmount FLOAT, 
 tollsAmount FLOAT, 
 totalAmount FLOAT, 
 [Year]  AS CAST(filepath(1) AS INT), --use filepath() for partitioning 
 [Month]  AS CAST(filepath(2) AS INT) --use filepath() for partitioning 
) 
WITH ( 
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = NYCTaxiExternalDataSource, 
 FILE_FORMAT = DemoFileFormat 
); 
GO 
 
SELECT * 
      FROM tbl_TaxiRides 
WHERE 
      [year]=2017             
      AND [month] in (10,11,12);

如果存储的数据未分区，请考虑将数据分区以提高查询性能。

将计算推送到 Hadoop

仅适用于：SQL Server 2016 (13.x)、SQL Server 2017 (14.x) 和 SQL Server 2019 (15.x)

PolyBase 将一些计算推送到外部源，以优化总体查询。 查询优化器制定基于成本的决策，以在执行此操作将提升查询性能时将计算推送到 Hadoop。 查询优化器使用外部表上的统计来制定基于成本的决策。 推送计算会创建 MapReduce 作业并利用 Hadoop 的分布计算资源。 有关详细信息，请参阅 PolyBase 中的下推计算。

缩放计算资源

仅适用于：SQL Server 2016 (13.x)、SQL Server 2017 (14.x) 和 SQL Server 2019 (15.x)

若要提高查询性能，可以使用 SQL Server PolyBase 横向扩展组。 这使并行数据可以在 SQL Server 实例和 Hadoop 节点之间传输，并为处理外部数据添加计算资源。

重要

Microsoft SQL Server PolyBase 横向扩展组即将停用。 横向扩展组功能将从 SQL Server 2022 (16.x) 的产品中移除。 PolyBase 数据虚拟化将在 SQL Server 中作为纵向扩展功能继续完全受支持。 有关详细信息，请参阅 Microsoft SQL Server 平台上的大数据选项。

相关内容