掌握 SQL 优化：从功能性到高效查询

原文：towardsdatascience.com/mastering-sql-optimization-from-functional-to-efficient-queries-74d8692f10be

SQL 可能是每个数据分析师和数据科学家都应该掌握的最基本的技术技能。它通常是面试过程的一部分，我们在工作中花费大量时间用 SQL 编写代码来收集数据。没有它，就没有分析或机器学习模型。然而，编写功能性查询与编写好的查询是不同的。

在我最近的工作中，我们的数据科学家每天在 Airflow 上安排了超过 1,000 个查询。我观察到我们的 Snowflake 信用额度在一年内增加了 50%。当我自愿检查前 10 个信用使用量最多的查询时，我很快意识到有许多优化机会，其中一些相当简单。这篇文章突出了六个简单而有效的技巧，帮助我每天减少了 50 小时的 SQL 查询运行时间。其中一些非常简单易行，但请记住，在 SQL 优化上投入 20%的努力可以带来 80%的性能提升。

注意：

1. 这篇文章是为那些已经熟悉基本 SQL 语法的人写的。如果你是那些每天编写 SQL 但希望改进查询（并让你的数据工程师朋友高兴）的数据分析师或数据科学家，这篇文章就是为你准备的！

2. 这篇文章主要基于我在 Snowflake SQL 上的经验。虽然我专注于普遍适用的技巧，但请记住，每个数据仓库的运行方式都不同，因此一些技巧可能对其他平台不太适用。请在实践中尝试并比较查询性能。

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/224f54071d03342120a8c738af707739.png

ChatGPT 生成的图像

在 Snowflake 中理解你的查询

在深入优化技术之前，了解你查询的底层机制并确定最耗资源的部分是至关重要的。

幸运的是，如果你在工作中使用 Snowflake SQL，他们有一个无价的查询配置文件工具。以下是步骤：

1. 前往“历史”标签页或“工作表”页面；

2. 找到你想要分析的查询并点击查询 ID；

3. 前往“配置文件”标签页并阅读操作树。这将显示查询在后台按顺序执行的特定操作，每个操作花费了多长时间，执行时间分解以及成本。

例如，在下面的屏幕截图中，你可以看到Join步骤花费了最长时间，其次是Aggregate (SUM)操作。

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/b210243971a18f77d074632755150582.png

来自 Snowflake 查询配置文件文档的截图

这个工具对于识别低效的查询步骤非常有帮助。当你尝试下面的优化技巧时，你也可以比较查询配置文件的前后变化，以更好地理解底层的变化。

SQL 查询的注意事项

为了更容易地解释这些技巧，让我们假设我们在一家电子商务公司工作，以下是我们使用的表：

• users: 一个宽的用户级表。每一行代表电子商务网站上注册的用户。列包括 _userid（主键），_signuptime，以及各种人口统计信息（例如，20+列）如age，gender，country，_billingaddress等。

• products: 一个产品级表。每一行代表我们在网站上销售的产品。列包括 _productid（主键），_productname，_productcategory等。

• orders: 一个订单级表。每一行代表用户在网站上放置的订单。列包括 _orderid（主键），_userid（外键），_ordertime等。假设它还有一个大列 _paymentinformation，存储着大量的 JSON 块，包含信用卡交易数据。

• ordered_products: 一个订单 x 产品级表。它说明了每个订单中包含哪些产品。列包括 _order_productid（主键），_orderid（外键），_productid（外键），和price。

现在让我们谈谈我总结的六个简单但有效的 SQL 查询优化技巧。

1. 只选择必要的列

这是最容易但经常被忽视的技巧，特别是对于使用列式存储如 Snowflake 的数据仓库。你应该只选择你需要的列，尤其是在处理大型表、运行窗口函数或多个连接时。写SELECT *很容易，但可能会非常昂贵并减慢你的查询速度。优化列选择有助于减少计算资源的使用和成本，并提高可读性。

我通过简单地指定我需要的列并省略大型 JSON 列，将查询速度提高了 4 倍。

做：

SELECT col_a,col_b,col_c

不要：

SELECT*

示例：

假设我们想要获取每个用户的第一个订单的金额。

低效版本：

--Inefficient version WITH first_order AS(SELECT*FROM orders QUALIFY ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY order_time)=1)SELECT first_order.user_id,first_order.order_id,sum(price)AS first_order_amount FROM first_order JOIN ordered_products ON first_order.order_id=ordered_products.order_id GROUP BY1,2;

更好的版本：

--Better version WITH first_order AS(SELECT user_id,order_id--remember we have a large JSON column payment_informationinorders table?--inthis CTE,allwe needisthe user_idandorder_id columns--doing a window functionisalready expensive,--so don't bring that largeanduneccessary JSON columnwithit FROM orders QUALIFY ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY order_time)=1)SELECT first_order.user_id,first_order.order_id,sum(price)AS first_order_amount FROM first_order JOIN ordered_products ON first_order.order_id=ordered_products.order_id GROUP BY1,2;

注意：QUALIFY是 Snowflake SQL 语法中的一个方便的过滤窗口函数输出的方法。如果你不熟悉，可以在这里查看它。

2. 早期过滤

总是减少后续操作中处理的数据量。如果你只需要表中的某些行，尽早应用过滤条件。

做：

尽早过滤表。

不要：

在做了所有的连接之后，经过几个 CTE，你才意识到需要应用一些过滤条件…

示例：

让我们继续上一个例子——假设我们现在只想获取来自US用户的第一个订单的金额。

低效版本：

--Inefficient version--here we run the window function to get the first order ofallusers--then wefiltertheminthenextstep when aggregating order amount WITH first_order AS(SELECT user_id,order_id FROM orders QUALIFY ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY order_time)=1)SELECT first_order.user_id,first_order.order_id,sum(price)AS first_order_amount FROM first_order JOIN ordered_products ON first_order.order_id=ordered_products.order_id JOIN users ON first_order.user_id=users.user_id WHERE users.country='US'GROUP BY1,2;

更好的版本：

--Better version WITH first_order AS(--When you put the users.countryfilterinthe CTE--Snowflake runs the WHERE before the window function--so this reduces the size of data being processedinthe window function SELECT orders.user_id,orders.order_id FROM orders JOIN users ON orders.user_id=users.user_id WHERE users.country='US'QUALIFY ROW_NUMBER()OVER(PARTITION BY users.user_id ORDER BY order_time)=1)SELECT first_order.user_id,first_order.order_id,sum(price)AS first_order_amount FROM first_order JOIN ordered_products ON first_order.order_id=ordered_products.order_id GROUP BY1,2;

3. 如果可能，在最后一步将非常大的表进行连接

有时候你有一个非常大的表，确实需要携带很多列和行。将这个表作为基础，并连接多个表到它，会导致疯狂的数据扫描和处理时间和成本，因为 Snowflake 在连接中传递数据。

然而，通过在最后连接大表，你可以先过滤和聚合较小的表，从而减少需要与大表连接的数据量。而且 Snowflake 不需要在每一步都携带那个大表的所有列。这可以显著减少 Snowflake 需要处理的中间数据量，从而提高性能。是的，你在这里可以看到一个反复出现的想法——最终目标始终是减少正在处理的数据量。

这种调整查询顺序的策略听起来很简单，但它帮助我将一个耗时 100 分钟的查询缩短到了 50 分钟。

执行:

如果可能，只在查询的最后阶段连接一个非常大的表

不要:

SELECT FROM giant_table_a LEFT JOIN table_b ON xxx LEFT JOIN table_c ON xxx LEFT JOIN table_d ON xxx LEFT JOIN table_e ON xxx

示例:

这次，我们想要创建一个宽表，它保留users表中的所有列，但还有一个额外的列first_order_amount，这是我们上面计算出来的。

低效版本：

--Inefficient version WITH first_order AS(SELECT users.*,orders.order_id--here we are running window function on this already wide users table--andcarryallthe datawithit FROM users LEFT JOIN orders ON users.user_id=orders.user_id QUALIFY ROW_NUMBER()OVER(PARTITION BY users.user_id ORDER BY order_time)=1),SELECT first_order.*,sum(price)AS first_order_amount--andhere we run aggregation onallthose columnsfromusers table--this will really slow things down FROM first_order LEFT JOIN ordered_products ON first_order.order_id=ordered_products.order_id GROUP BY ALL--yes GROUP BY ALLisa legitandhandy grammarinsnowflake:)--but don't overuse it just because itissimple;

更好的版本：

--Better version WITH first_order AS(SELECT user_id,order_id--here we only run the window function on orders tablewithtwo columns FROM orders QUALIFY ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY order_time)=1),first_order_amount AS(SELECT first_order.user_id,first_order.order_id,sum(price)AS first_order_amount--the aggregation hereisalso simplewithtwo columns grouped by FROM first_order JOIN ordered_products ON first_order.order_id=ordered_products.order_id GROUP BY1,2)--append the column by joining it to our large users table at the last step SELECT users.*,first_order_amount.first_order_amount FROM users LEFT JOIN first_order_amount ON users.user_id=first_order_amount.user_id;

4. 避免重复去重

当我们需要去重行时，这种情况经常发生。有几种方法可以实现这一点，包括使用

• UNION（而不是 UNION ALL）：当你需要从具有相同结构的几个分支中合并数据并避免重复时，

• DISTINCT：当你想要删除重复行时，

• 如 ROW_NUMBER()这样的窗口函数：当你想要根据某些标准保留具有唯一分区键的行时。

我知道有时我们只是喜欢在每个 CTE 中都放DISTINCT以避免重复。然而，这些都是昂贵的操作，所以避免同时使用它们。

执行:

避免在一个查询中混合使用 UNION、DISTINCT 和窗口函数进行去重。记住每个 CTE 输出的粒度是什么。

不要:

SELECT DISTINCT*FROM(SELECT*FROM tab_a UNION SELECT*FROM tab_b)QUALIFY ROW_NUMBER()OVER(PARTITION BY primary_key ORDER BY inserted_at)=1

示例:

假设我们想要一个包含每个用户的第一个和最后一个订单 ID 的表。

低效版本：

--Inefficient version--Using DISTINCT,window function,andUNION at the same time--get first orders SELECT DISTINCT user_id,order_id,'first_order'asorder_label FROM orders QUALIFY ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY order_time)=1UNION--get last orders SELECT DISTINCT user_id,order_id,'last_order'asorder_label FROM orders QUALIFY ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY order_time DESC)=1;

更好的版本：

--better version--For each branch,the window function already returns one order per user.--Therefore,we don't need to run DISTINCT on each branch.--And when we combine the two branches,--the new'order_label'column ensures no duplicates between the two branches.--Therefore,UNION ALLissufficient,no need to dedupwithUNION--get first orders SELECT user_id,order_id,'first_order'asorder_label FROM orders QUALIFY ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY order_time)=1UNION ALL--get last orders SELECT user_id,order_id,'last_order'asorder_label FROM orders QUALIFY ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY order_time DESC)=1;

5. 注意使用窗口函数

窗口函数是强大的工具，有助于检索第一/最后一行或计算累积指标，但它们也是资源密集型的。因此，你需要小心：

• 在运行窗口函数之前清理数据以限制数据大小会更好；

• 分区有助于将计算隔离到数据子集，因此请明智地使用 PARTITION BY；

• 当在窗口函数中使用 ORDER BY 时，确保排序对计算有意义；

• 确保在 PARTITION BY 和 ORDER BY 中使用的列上有适当的索引，以增强性能。

执行:

只在有必要时使用窗口函数，并问自己添加它之前需要处理多少数据。

不要:

写一个糟糕的窗口函数有很多方法……再次提醒，在使用之前三思！

示例：

例如，在我检查的查询中，85% 的分区键值是空的。这浪费了计算成本，因为最终只会从那些具有 NULL 分区键的行中保留一行随机行，提供零价值。通过首先过滤掉空行，我能够将查询运行时间减少了 70%。

6. 避免多层嵌套子查询，并用 CTEs 替换

编写包含嵌套子查询的大查询对人们来说很难导航。用公用表表达式（CTEs）替换它们会使查询更容易阅读和调试。这还使得逐步优化查询（通过应用上述原则）并只携带必要的数据片段成为可能。然而，请注意，不同的 SQL 引擎可能会以不同的方式优化 CTEs，所以请尝试优化 CTEs 并比较在实际数据仓库中的影响。

DO:

将嵌套子查询分解为 CTEs

DON’T:

一层又一层的子查询…

SELECT*FROM(SELECT*FROM table_a WHEREidIN(SELECTidFROM table_b JOIN table_c ON xxxx))LEFT JOIN table_d ON xxxx

示例:

在上述所有示例中，我使用 CTEs 逐步构建 SQL 查询，带有直观的标签和名称。这帮助我在脑海中规划操作序列并相应地进行优化。

最终思考

编写一个有效的即兴查询与编写一个优化速度和成本的优秀查询不同。实施这些优化技巧可以显著提高你的 SQL 查询性能，最终将时间返还给你，让你可以从事更多令人兴奋的数据科学项目。