常用网站大全,如何建立网站视频,中国建设人才信息网站查询,网页版传奇哪个最好玩在 Azure Synapse Dedicated SQL Pool中优化 SQL 执行涉及了解底层体系结构#xff08;例如分布和分区#xff09;、查询优化#xff08;例如避免不必要的子查询和联接#xff09;#xff0c;以及利用具体化视图和 PolyBase 等工具进行高效数据加载。
1.有效使用分布和分…在 Azure Synapse Dedicated SQL Pool中优化 SQL 执行涉及了解底层体系结构例如分布和分区、查询优化例如避免不必要的子查询和联接以及利用具体化视图和 PolyBase 等工具进行高效数据加载。
1.有效使用分布和分区
Azure Synapse Dedicated SQL Pool使用分布方法将数据分布到多个分布区。选择正确的分布方法可以显著提高查询性能。
最佳实践
哈希分布非常适合经常与同一键上的其他大型表联接的表。它确保以最大程度地减少数据移动的方式将数据分布在各个分配中。Round-robin分布适用于没有明确 join key 的大表。但是如果涉及联接则可能会导致查询性能低下。Replicated表经常与大型事实表联接的小维度表可以复制到每个分配以避免数据移动。
例 假设您有两个表“Orders”大型事实数据表和 ‘Customers’小型维度表。
对 “Customers” 表使用 replicated distribution因为它很小并且经常在联接中使用。对 ‘CustomerID’ 等列上的 ‘Orders’ 表使用 哈希分配 以确保联接期间的最佳性能。
CREATE TABLE Customers
(CustomerID INT,CustomerName VARCHAR(255),...
)
WITH (DISTRIBUTION REPLICATE);CREATE TABLE Orders
(OrderID INT,CustomerID INT,OrderDate DATETIME,...
)
WITH (DISTRIBUTION HASH(CustomerID));2.利用聚集列存储索引
聚集列存储索引 CCI 针对大型数据扫描和聚合进行了优化。它们以压缩的列式格式存储数据从而显著减少存储并提高分析工作负载的查询性能。
最佳实践
始终对大型事实表使用 聚集列存储索引 CCI尤其是那些具有数百万行的事实表。避免在大型表上使用传统的行存储索引因为 CCI 为分析查询提供了更好的性能。
例
CREATE TABLE Sales
(SaleID INT,ProductID INT,Date DATE,Amount DECIMAL(18, 2)
)
WITH (CLUSTERED COLUMNSTORE INDEX);3.最大限度地减少数据移动
分配之间的数据移动 shuffleling 会严重影响查询性能。要最大程度地减少数据移动请执行以下操作
使用正确的联接键确保联接中使用的列分布在同一列上或被复制。提前筛选在查询中尽早应用筛选条件以减少正在处理的数据量。
例 如果您要联接两个大型表“Sales”和“Products”请确保两个表都分布在同一个键“ProductID”上以避免数据移动。
CREATE TABLE Sales
(SaleID INT,ProductID INT,Date DATE,Amount DECIMAL(18, 2)
)
WITH (DISTRIBUTION HASH(ProductID));CREATE TABLE Products
(ProductID INT,ProductName VARCHAR(255),Category VARCHAR(255)
)
WITH (DISTRIBUTION HASH(ProductID));SELECT s.SaleID, p.ProductName, s.Amount
FROM Sales s
INNER JOIN Products p ON s.ProductID p.ProductID;4.优化查询设计
高效的 SQL 查询设计在性能优化中起着至关重要的作用。
避免 SELECT :*始终在 SELECT 子句中指定所需的列以减少不必要的数据检索。提前筛选应用 ‘WHERE’ 子句以最小化数据集大小。 避免不必要的子查询尽可能使用联接而不是子查询以防止额外的处理。高效使用窗口函数‘ROW_NUMBER’、‘RANK’ 和 ‘SUM’ 等窗口函数有时可以替换复杂的子查询或聚合。
例 而不是
SELECT * FROM Sales WHERE Amount 1000;用
SELECT SaleID, Amount FROM Sales WHERE Amount 1000;5.对大型表进行分区
分区允许您将大型表划分为更小、更易于管理的部分从而提高查询性能尤其是对于范围查询例如日期。
最佳实践
在常用的筛选条件列例如日期列例如‘OrderDate’上对表进行分区。避免过多的分区;分区过多会导致开销。
例
CREATE TABLE Sales
(SaleID INT,ProductID INT,SaleDate DATE,Amount DECIMAL(18, 2)
)
WITH (PARTITION (SaleDate));6.使用物化视图
具体化视图存储复杂查询的预计算结果并允许更快地检索尤其是对于聚合密集型或联接密集型查询。
最佳实践
将具体化视图用于复杂的读取密集型查询例如聚合的销售数据。确保根据业务需求定期刷新物化视图。
例
CREATE MATERIALIZED VIEW mv_AggSales AS
SELECT ProductID, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY ProductID;7.资源类和查询优先级
Resource classes 控制分配给查询的资源量 内存、CPU 等。为需要更多资源的查询分配更高的资源类。
最佳实践
使用 small resource classes例如‘smallrc’进行轻量级查询。将 大型资源类例如‘largerc’用于资源密集型查询如大型聚合。
例
-- Assign a higher resource class for heavy queries
SET USER username; -- Implicitly assigned resource class based on user
EXEC sp_set_session_context resource_class, largerc;8.使用 Query Performance Insights 监控和优化性能
利用 Azure Synapse Studio 中的“查询性能见解”来识别资源瓶颈并分析执行计划。查找
高 CPU 使用率可能表示缺少索引或查询优化不佳。高 I/O可能表明您正在不必要地扫描大量数据。Large memory grants表示资源密集型查询。
例 执行查询后使用 Query Performance Insight 控制面板查看执行计划并识别慢速操作例如哈希联接、表扫描。
9.使用 PolyBase 优化数据加载
从外部源例如 Azure Blob 存储加载大型数据集时请使用 PolyBase 将数据高效加载到 Synapse SQL 池中。
最佳实践
使用 PolyBase 将数据从 Azure Data Lake 或 Blob Storage 等外部源加载到 SQL 池中。在加载源数据之前对源数据进行适当的分区。
例
CREATE EXTERNAL DATA SOURCE MyBlobStorage
WITH (TYPE HADOOP, LOCATION abfs://mycontainermyaccount.dfs.core.windows.net/);CREATE EXTERNAL FILE FORMAT TextFileFormat
WITH (FORMAT_TYPE DELIMITEDTEXT, FIELD_TERMINATOR ,, STRING_DELIMITER );CREATE EXTERNAL TABLE ExternalSales
(SaleID INT,ProductID INT,SaleDate DATE,Amount DECIMAL(18, 2)
)
WITH (DATA_SOURCE MyBlobStorage, FILE_FORMAT TextFileFormat);INSERT INTO Sales
SELECT * FROM ExternalSales;10.针对数据倾斜进行优化
当数据在分布区之间分布不均匀时就会发生数据倾斜这可能会导致查询处理不均匀从而导致性能下降。识别和解决数据倾斜对于高效执行查询至关重要。
最佳实践
监控数据分布确保数据分配不会导致某些分配过载而其他分配仍未得到充分利用。这可以通过 ‘sys.dm_pdw_nodes’ 视图的 distribution 列来识别。避免高度偏斜的数据应谨慎处理具有高度偏斜数据的列例如具有很少不同值的日期如“January 1st”。对于此类列请考虑对查找表使用 replicated table 或调整分配键。在大型表上使用哈希分布使用具有高基数且分布均匀的列例如‘OrderID’、‘ProductID’来分布大型表。
示例
假设您正在处理销售数据并且 ‘Region’ 列具有偏态分布例如90% 的数据仅来自一个区域。在这种情况下
您可以避免在 ‘Region’ 上进行 哈希分配。复制小表如 ‘Regions’ 进行查找。
-- Sales table with hash distribution on a more evenly distributed column
CREATE TABLE Sales
(SaleID INT,RegionID INT,SaleDate DATE,Amount DECIMAL(18, 2)
)
WITH (DISTRIBUTION HASH(SaleID));-- Regions table with replication to avoid skew in joins
CREATE TABLE Regions
(RegionID INT,RegionName VARCHAR(255)
)
WITH (DISTRIBUTION REPLICATE);11.最小化跨数据分布联接
当联接所需的数据驻留在不同的分配上时就会发生跨分配联接这可能会导致 数据移动 和性能问题。这种情况经常发生在非键联接或表未分布在同一列上时。
最佳实践
在分布式列上联接始终尝试联接在同一键例如‘CustomerID’、‘ProductID’上分布的表。广播小型表对于较小的维度表请使用 复制 以避免在联接期间移动数据。
示例
假设您有两个表“Orders”大和 “Customers”小。在 ‘CustomerID’ 上分配 ‘Orders’ 并复制 ‘Customers’ 以实现高效联接。
-- Replicated Customers table to avoid data movement
CREATE TABLE Customers
(CustomerID INT,CustomerName VARCHAR(255),...
)
WITH (DISTRIBUTION REPLICATE);-- Orders table distributed by CustomerID for efficient join
CREATE TABLE Orders
(OrderID INT,CustomerID INT,OrderDate DATE,Amount DECIMAL(18, 2)
)
WITH (DISTRIBUTION HASH(CustomerID));12.避免在查询中使用标量函数
标量函数会显著降低性能尤其是在大型数据集中因为它们可能会逐行执行。相反请尽可能使用 inline expressions 或 joins。
最佳实践
避免在 ‘SELECT’ 或 ‘WHERE’ 子句中使用 标量函数尤其是对于大型数据集。考虑使用 内联 ‘CASE’ 语句 或 joins 作为标量函数的替代方案。
示例
不要使用标量函数来计算折扣而是直接在 ‘SELECT’ 语句中执行。
不良做法
SELECT OrderID, CustomerID, dbo.fn_calculate_discount(Amount) AS Discount
FROM Orders;最佳实践
SELECT OrderID, CustomerID, Amount * 0.10 AS Discount
FROM Orders;13.使用资源类的查询并行性
Azure Synapse SQL 池使用 资源类 来控制查询并行度和资源。通过调整资源类您可以控制查询获取的资源数量从而提高性能。
最佳实践
为复杂的查询或操作设置适当的资源类。较大的资源类将为查询分配更多的内存和 CPU。为不同的工作负载使用正确的资源类 ‘smallrc’用于小型查询。‘mediumrc’用于中间工作负载。‘largerc’用于繁重的聚合和数据处理查询。
示例
如果要对大型数据集运行繁重的聚合查询则可能需要使用更大的资源类。
-- Assign the session to a larger resource class
SET RESOURCE_CLASS largerc;-- Complex aggregation query
SELECT ProductID, SUM(Amount) AS TotalAmount
FROM Sales
GROUP BY ProductID;14.使用 Query Store 分析和优化查询
Azure Synapse 具有 查询存储 功能可提供历史查询性能指标这有助于识别一段时间内的低效查询。
最佳实践
启用查询存储监控一段时间内的查询性能并识别可能导致性能问题的查询。分析执行计划使用 Query Store 分析运行缓慢的查询的执行计划并找到优化它们的方法例如索引、查询重写。
示例
启用并使用 Query Store 来分析历史查询
-- Enable Query Store
ALTER DATABASE [YourDatabase] SET QUERY_STORE ON;-- Query performance analysis via Query Store views
SELECT *
FROM sys.query_store_runtime_stats
ORDER BY execution_count DESC;15.优化聚合和分组依据
聚合操作可能占用大量资源。高效处理聚合尤其是在大型数据集上对于实现最佳性能至关重要。
最佳实践
有效使用 ‘GROUP BY’除非绝对必要否则尽量避免对大列进行分组。如果从大型非关键数据集读取数据请在聚合查询上使用 ‘WITH NOLOCK’。这可以减少阻塞。使用索引视图 或 具体化视图 来存储预先计算的聚合结果。
示例
使用索引视图或具体化视图来存储预先聚合的数据可以提高报表查询的性能。
CREATE MATERIALIZED VIEW mv_ProductSales AS
SELECT ProductID, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY ProductID;现在您可以查询具体化视图而不是直接查询 ‘Sales’
SELECT ProductID, TotalSales
FROM mv_ProductSales;16.Leverage Result Caching
Azure Synapse SQL 池支持结果缓存它缓存频繁执行的查询的结果。重新运行相同的查询可以从缓存中检索结果而不是重新执行查询。
最佳实践
利用结果缓存如果您经常运行相同的查询例如在控制面板或报告中结果缓存可以显著加快执行速度。监控缓存命中/未命中率您的查询与现有缓存结果的匹配度越高您的性能就越好。
示例
若要强制 Azure Synapse 在重大更改例如表更新后清除缓存可以使用以下内容
-- Clear the cache for a specific table
DROP TABLE IF EXISTS Sales;如果启用了缓存则可以减少重复查询的时间。 17.优化数据类型
使用适当的数据类型可以最大限度地减少 I/O 操作并减少类型转换的需求从而大大减少存储占用量并提高查询性能。
最佳实践
选择合适的数据类型避免使用不必要的大数据类型。例如如果值在 ‘INT’ 范围内则使用 ‘INT’ 而不是 ‘BIGINT’。在适当的情况下对可变长度字符串字段使用 ‘VARCHARn’ 而不是 ‘TEXT’。**仅在需要时使用 ‘DECIMAL’ 或 ‘NUMERIC’ 对于定点数;使用 ‘FLOAT’ 或 ‘REAL’ 进行近似值。
示例
选择更合适的数据类型
-- Instead of using VARCHAR(MAX), limit the size to save space
CREATE TABLE Products
(ProductID INT,ProductName VARCHAR(100), -- More efficient sizePrice DECIMAL(10, 2)
);18.谨慎使用表值函数 TVF
虽然表值函数 TVF 提供了可重用的逻辑但它们通常会由于逐行执行而降低性能尤其是在联接或聚合中使用时。
最佳实践
避免在复杂查询中使用 TVF当性能受到关注时请考虑将 TVF 替换为 内联查询 或 CTEs公用表表达式。使用内联表值函数 iTVF这些函数比多语句 TVF 更高效因为它们由查询计划程序进行优化。
示例
使用内联表值函数而不是多语句函数
-- Inline TVF: More efficient
CREATE FUNCTION dbo.fn_GetProductSales(ProductID INT)
RETURNS TABLE
AS
RETURN (SELECT SaleID, AmountFROM SalesWHERE ProductID ProductID
);-- Using the inline TVF
SELECT * FROM dbo.fn_GetProductSales(101);19.使用 ‘MERGE’ 进行高效的更新插入**
对于需要执行 更新插入insert/update operations 的场景‘MERGE’ 语句可能比单独执行 ‘INSERT’ 和 ’
UPDATE“ 操作。
示例
MERGE INTO TargetTable AS target
USING SourceTable AS source
ON target.ID source.ID
WHEN MATCHED THENUPDATE SET target.Value source.Value
WHEN NOT MATCHED BY TARGET THENINSERT (ID, Value) VALUES (source.ID, source.Value);20.根据工作负载设置适当的 SQL 池缩放
根据工作负载需求适当扩展 SQL 池对于性能和成本都很重要。
最佳实践
在高工作负载期间纵向扩展对于大型 ETL 或复杂查询请增加数据分发单元 DWU 的数量。在高工作负载后缩减不运行大型查询时缩减资源以降低成本。
示例
您可以使用以下命令调整 SQL 池的资源
-- Scale up to DW1000c for intensive queries
ALTER DATABASE [YourDatabase] MODIFY (SERVICE_OBJECTIVE DW1000c);-- Scale down after workload is complete
ALTER DATABASE [YourDatabase] MODIFY (SERVICE_OBJECTIVE DW100c);21.使用排序数据高效处理大型连接
在执行联接时尤其是在大型表上在联接之前对数据进行排序有时可以通过减少 shuffle 的需求并提高并行度来优化性能。
最佳实践
在执行联接之前对表进行预排序如果要联接大型表则提前按联接键对表进行排序可以减少联接过程中的数据随机排序。聚集列存储索引 CCI使用 CCI 时数据已以排序方式存储因此这可能会自然地针对联接进行优化。
示例
考虑在 ‘CustomerID’ 上联接两个大型表即 ‘Orders’ 和 ‘Customers’。提前对它们进行排序可以提高性能
-- Sorting Orders and Customers before joining
CREATE CLUSTERED INDEX idx_orders_customerid
ON Orders (CustomerID);CREATE CLUSTERED INDEX idx_customers_customerid
ON Customers (CustomerID);-- Then perform the join
SELECT o.OrderID, c.CustomerName, o.Amount
FROM Orders o
JOIN Customers c ON o.CustomerID c.CustomerID;通过在联接键上创建聚集索引Synapse 将使用这些排序键来最大程度地减少数据随机排序并改进并行查询执行。 22.针对资源类中的单节点查询进行优化
某些查询可能需要比其他查询多得多的资源对于这些类型的查询您可以优化它们以在单个节点上运行而不是使用整个分布式池。
最佳实践
使用 单节点查询 卸载不需要分布式处理的大型复杂分析查询。对无法从并行执行中受益的查询使用 ‘WITH QUERY_HINTS ‘FORCE_SINGLE_NODE’’。
示例
如果您正在运行不需要分布式执行的查询 e.g.它只是一个简单的查找或小的聚合强制它在单个节点上运行以加快处理速度
-- Force the query to run on a single node
SELECT CustomerName, SUM(Amount)
FROM Sales
GROUP BY CustomerID
OPTION (QUERY_HINTS FORCE_SINGLE_NODE);这将减少跨多个节点分发和协调执行的开销。 23.对基于时间的查询使用高效的日期处理
基于日期的查询通常涉及大量数据尤其是在 Synapse 等分析环境中。有效处理日期列是优化查询性能的关键。
最佳实践
按日期对表进行分区如果可能请按日期对大型表例如‘Sales’、‘Transactions’进行分区以通过缩小扫描范围来显著提高查询性能。使用适当的日期格式 对于基于特定日期范围的查询请确保您使用最有效的数据类型例如“DATE”与“DATETIME”。
示例
按 ‘YearMonth’ 对表进行分区以优化基于日期的查询的性能
CREATE TABLE Sales
(SaleID INT,ProductID INT,SaleDate DATE,Amount DECIMAL(18, 2)
)
WITH (PARTITION (YEAR(SaleDate), MONTH(SaleDate)));查询特定月份时Synapse 只能扫描该分区而不是整个表
SELECT ProductID, SUM(Amount)
FROM Sales
WHERE SaleDate BETWEEN 2025-01-01 AND 2025-01-31
GROUP BY ProductID;24.使用分组集针对聚合查询进行优化
聚合查询尤其是涉及多个 GROUP BY 列或 汇总操作的查询可以受益于 分组集 或 CUBE 等高级技术以减少冗余并提高性能。
最佳实践
使用 GROUPING SETS 在一次扫描中计算多个聚合从而减少对数据进行多次传递的需要。使用 CUBE 或 ROLLUP 在复杂查询中进行多级聚合。
示例
如果您需要同时按 ‘ProductID’ 和 ‘Region’ 以及两者来计算 ‘SUMAmount’则可以使用 GROUPING SETS 来减少冗余查询
SELECT ProductID, Region, SUM(Amount)
FROM Sales
GROUP BY GROUPING SETS((ProductID, Region),(ProductID),(Region),());此查询将按“ProductID”和“Region”有效地计算“Amount”的总和也可以按每个总和单独计算。 25.使用 Window Functions 优化复杂聚合
窗口函数如 ‘ROW_NUMBER’、‘RANK’ 和 ‘SUM’ 对于复杂的聚合场景可能非常有用但使用它们不当例如没有分区可能会导致查询效率低下。
最佳实践
按适当的列分区 以避免全表扫描。在窗口函数中谨慎使用 ‘ORDER BY’ 以避免不必要的排序。使用 ‘OVER’ 子句来避免多次传递数据。
示例
您可以使用窗口函数在一次传递中计算累积总计而不是运行子查询来计算累积总计
SELECT ProductID, SaleDate, Amount,SUM(Amount) OVER (PARTITION BY ProductID ORDER BY SaleDate) AS CumulativeSales
FROM Sales;在此示例中计算累积总和按 ‘ProductID’ 分区并按 ‘SaleDate’ 排序无需多次查询或联接。 26.使用查询调控器限制资源消耗
如果 SQL 池在多个用户或进程之间共享请务必通过大型查询来 限制资源消耗 以防止它们使系统不堪重负。这可以使用 Query Governor 功能来完成。
最佳实践
启用查询调控器您可以设置一个查询调控器限制来限制查询使用的资源量确保长时间运行的查询不会消耗太多资源。设置最大超时这在意外查询可能会消耗大量资源的环境中特别有用。
示例
-- Set query governor to limit execution time (in seconds)
EXEC sp_configure query governor, 120; -- Limit to 120 seconds
RECONFIGURE;这将自动终止运行时间超过指定时间限制的查询从而提高资源公平性。 27.对大型事实表进行分区以实现最佳查询
根据查询模式例如时间范围或 ID 范围对表进行分区可以提高 I/O 性能和 查询执行时间。
最佳实践
根据时间对事实数据表如 sales 或 transactions进行分区因为基于日期的查询在分析工作负载中很常见。对基于时间的数据或其他适当的列使用 范围分区。使用 ‘DROP PARTITION’ 和 ‘SWITCH PARTITION’ 来有效地管理分区。
示例
考虑这样一个场景您按 year and month** 对 sales 表进行分区
CREATE TABLE Sales
(SaleID INT,ProductID INT,SaleDate DATE,Amount DECIMAL(18, 2)
)
WITH (PARTITION (YEAR(SaleDate), MONTH(SaleDate)));要快速管理过去的数据您可以 切换 较旧的分区或 删除 不再需要的分区
-- Dropping an old partition for the year 2023
ALTER TABLE Sales SWITCH PARTITION 202301 TO Sales_Archive;这使得数据管理高效无需复杂的删除。 28.使用列存储索引实现压缩和查询速度
列存储索引 对于提高查询速度至关重要尤其是对于大规模、读取密集型查询。这是最适合分析工作负载的索引策略。
最佳实践
对大型表使用聚集列存储索引 CCI以受益于存储压缩和查询性能改进。将 非聚集列存储索引 用于较小的事实数据表或其他分析列。对于非常宽的表请谨慎使用因为它们可能会导致内存和 CPU 资源的使用效率低下。
示例
-- Using clustered columnstore index for a large fact table
CREATE TABLE Sales
(SaleID INT,ProductID INT,SaleDate DATE,Amount DECIMAL(18, 2)
)
WITH (CLUSTERED COLUMNSTORE INDEX);此表现在受益于列存储索引提供的压缩和优化的查询执行。 29.针对 PolyBase 和外部数据集成进行优化
如果使用 PolyBase 集成来自外部源如 Azure Blob 存储或 Azure Data Lake的大型数据集则适当的优化可以显著减少数据加载时间。
最佳实践
在 Azure Blob 存储或数据湖中对外部数据进行分区并在 Synapse 中相应地对目标表进行分区。如果外部数据文件已采用一致的结构化格式则对文件格式使用 ‘WITH NOFORMAT’。对外部数据利用 文件压缩例如 Parquet 或 ORC来减少 I/O 开销。
示例
使用 PolyBase 从分区源加载大型数据集
CREATE EXTERNAL DATA SOURCE BlobStorage
WITH (TYPE HADOOP, LOCATION abfs://containerstorageaccount.dfs.core.windows.net/);CREATE EXTERNAL FILE FORMAT ParquetFileFormat
WITH (FORMAT_TYPE PARQUET);CREATE EXTERNAL TABLE SalesExternal
(SaleID INT,ProductID INT,SaleDate DATE,Amount DECIMAL(18, 2)
)
WITH (LOCATION /data/sales/, DATA_SOURCE BlobStorage, FILE_FORMAT ParquetFileFormat);30.利用具体化视图实现更快的查询性能
具体化视图以物理方式存储查询结果并定期刷新它从而提高 复杂聚合查询 或 频繁的报表查询 的性能。
最佳实践
将 具体化视图 用于涉及大型聚合或联接的频繁执行的查询。确保您为具体化视图设置适当的 刷新策略 以平衡性能和数据新鲜度。
示例
CREATE MATERIALIZED VIEW mv_ProductSales AS
SELECT ProductID, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY ProductID;此视图现在将存储聚合结果并且查询它比每次都从基表重新计算聚合要快得多。
