编写 SQL 查询的最佳实践

本文介绍了一些为数据分析师和数据科学家编写 SQL 查询的最佳实践。我们的大部分讨论将围绕通用 SQL，但我们将包含一些关于 Metabase 特有功能的注释，这些功能使编写 SQL 变得轻而易举。

正确性、可读性，然后是优化：按此顺序

此处适用关于过早优化的标准警告。在知道查询返回您要查找的数据之前，请避免调整 SQL 查询。即使那样，也只有在查询运行频繁时（例如为热门仪表盘提供支持）或查询遍历大量行时，才应优先考虑优化查询。一般来说，在担心性能之前，应优先考虑准确性（查询是否产生预期的结果）和可读性（其他人是否可以轻松理解和修改代码）。

在寻找针之前，尽可能缩小您的草堆

可以说，我们已经开始进行优化了，但目标应该是告诉数据库扫描检索结果所需的最小数量的值。

SQL 的部分美妙之处在于其声明性。您只需告诉数据库您需要哪些记录，数据库就应该找出获取该信息的最有效方法，而不是告诉数据库如何检索记录。因此，关于提高查询效率的大部分建议只是关于向人们展示如何使用 SQL 中的工具来更精确地表达他们的需求。

我们将回顾查询执行的一般顺序，并在沿途提供减少搜索空间的技巧。然后，我们将讨论添加到您的实用工具包中的三个基本工具：INDEX、EXPLAIN 和 WITH。

首先，了解您的数据

在编写单行代码之前，请先熟悉您的数据，方法是研究元数据以确保列确实包含您期望的数据。Metabase 中的 SQL 编辑器 具有方便的数据参考选项卡（可通过书本图标访问），您可以在其中浏览数据库中的表，并查看其列和连接

您还可以查看特定列的示例值

Metabase 为您提供了许多不同的方式来探索您的数据：您可以 X 射线 表格，使用查询构建器编写问题，将已保存的问题转换为 SQL 代码，或从现有的 SQL 查询构建。我们在其他文章中介绍了这一点；现在，让我们回顾一下查询的通用工作流程。

开发您的查询

每个人的方法都会有所不同，但以下是开发查询时要遵循的工作流程示例。

• 如上所述，研究列和表格元数据。如果您使用的是 Metabase 的原生 (SQL) 编辑器，您还可以搜索包含您正在使用的表格和列的 SQL 代码的 SQL 代码片段。代码片段允许您查看其他分析师如何查询数据。或者您可以从现有的 SQL 问题开始查询。
• 要了解表格的值，请从您正在使用的表格中 SELECT *，并限制您的结果。在优化列（或通过连接添加更多列）时，请保持应用 LIMIT。
• 将列缩小到回答问题所需的最少集合。
• 将任何过滤器应用于这些列。
• 如果您需要聚合数据，请聚合少量行，并确认聚合是否符合您的预期。
• 一旦您有一个查询返回您需要的结果，请查找要另存为公共表表达式 (CTE) 的查询部分，以封装该逻辑。
• 使用 Metabase，您还可以将代码另存为 SQL 代码片段，以便在其他查询中共享和重用。

查询执行的一般顺序

在我们深入探讨编写 SQL 代码的各个技巧之前，重要的是要了解数据库将如何执行您的查询。这与您用于编写查询的阅读顺序（从左到右，从上到下）不同。查询优化器可以更改以下列表的顺序，但是编写 SQL 时，最好记住 SQL 查询的这种通用生命周期。我们将使用执行顺序来对以下编写良好 SQL 的技巧进行分组。

这里经验法则是：在此列表的前面消除数据越早越好。

1. FROM（和 JOIN）获取查询中引用的表。这些表表示查询指定的最大搜索空间。如果可能，请在继续操作之前限制此搜索空间。
2. WHERE 过滤数据。
3. GROUP BY 聚合数据。
4. HAVING 过滤掉不符合条件的聚合数据。
5. SELECT 获取列（然后在调用 DISTINCT 时删除重复行）。
6. UNION 将选定的数据合并到结果集中。
7. ORDER BY 对结果进行排序。

当然，在某些情况下，您的特定数据库的查询优化器会设计出不同的查询计划，因此不要过于纠结于此顺序。

一些查询指南（而非规则）

以下技巧是指南，而不是规则，旨在让您避免麻烦。每个数据库处理 SQL 的方式都不同，具有略有不同的功能集，并采用不同的方法来优化查询。甚至在我们开始比较使用列式存储格式的传统事务数据库和分析数据库（它们具有截然不同的性能特征）之前，情况就是如此。

注释您的代码，尤其是原因

通过添加注释来解释代码的不同部分，从而帮助他人（包括三个月后的您自己）。此处要捕获的最重要的事情是“为什么”。例如，很明显，下面的代码会过滤掉 ID 大于 10 的订单，但这样做是因为前 10 个订单用于测试。

SELECT
  id,
  product
FROM
  orders
-- filter out test orders
WHERE
  order.id > 10

这里的症结在于您引入了一些维护开销：如果您更改代码，则需要确保注释仍然相关且是最新的。但这对于可读代码来说只是很小的代价。

SQL 关于 FROM 的最佳实践

使用 ON 关键字连接表

尽管可以使用 WHERE 子句“连接”两个表（即执行隐式连接，例如 SELECT * FROM a,b WHERE a.foo = b.bar），但您应该首选显式 JOIN

SELECT
  o.id,
  o.total,
  p.vendor
FROM
  orders AS o
  JOIN products AS p ON o.product_id = p.id

主要是为了提高可读性，因为 JOIN + ON 语法将连接与旨在过滤结果的 WHERE 子句区分开来。

为多个表设置别名

在查询多个表时，请使用别名，并在 select 语句中使用这些别名，以便数据库（和您的读者）无需解析哪个列属于哪个表。请注意，如果您的多个表中的列名称相同，则需要使用表名或别名显式引用它们。

避免

SELECT
  title,
  last_name,
  first_name
FROM fiction_books
  LEFT JOIN fiction_authors
  ON fiction_books.author_id = fiction_authors.id

推荐

SELECT
  books.title,
  authors.last_name,
  authors.first_name
FROM fiction_books AS books
  LEFT JOIN fiction_authors AS authors
  ON books.author_id = authors.id

这是一个简单的示例，但是当查询中的表和列数量增加时，您的读者不必跟踪哪个列在哪个表中。这样一来，如果您连接一个具有歧义列名的表（例如，两个表都包含一个名为 Created_At 的字段），您的查询可能会中断。

请注意，字段过滤器与表别名不兼容，因此您需要删除别名才能将过滤器小部件连接到您的字段过滤器。

SQL 关于 WHERE 的最佳实践

在 HAVING 之前使用 WHERE 进行过滤

使用 WHERE 子句来过滤掉多余的行，这样您就无需首先计算这些值。只有在删除不相关的行之后，以及在聚合这些行并对它们进行分组之后，您才应该包含 HAVING 子句来过滤掉聚合。

避免在 WHERE 子句中对列使用函数

在 WHERE 子句的列上使用函数会显著降低查询速度，因为函数会使查询变为不可搜索参数 (即，它阻止数据库使用索引来加速查询)。函数作用于列上会迫使数据库在表的每一行上运行该函数，而不是使用索引跳转到相关行。

请记住，连接运算符 || 也是一个函数，因此不要尝试使用连接字符串来过滤多列。 建议使用多个条件代替。

避免

SELECT hero, sidekick
FROM superheros
WHERE hero || sidekick = 'BatmanRobin'

推荐

SELECT hero, sidekick
FROM superheros
WHERE
  hero = 'Batman'
  AND
  sidekick = 'Robin'

优先使用 = 而不是 LIKE

情况并非总是如此。 了解 LIKE 比较字符，并且可以与通配符运算符（如 %）配对，而 = 运算符比较字符串和数字以进行精确匹配，这一点很重要。= 可以利用索引列。 并非所有数据库都是这种情况，因为 LIKE 可以使用索引（如果字段存在索引），只要您避免在搜索词前加上通配符运算符 %。 这就引出了我们的下一点

避免在 WHERE 语句中使用首尾通配符

使用通配符进行搜索可能开销很大。 建议将通配符添加到字符串的末尾。 在字符串前加上通配符可能会导致全表扫描。

避免

SELECT column FROM table WHERE col LIKE "%wizar%"

推荐

SELECT column FROM table WHERE col LIKE "wizar%"

优先使用 EXISTS 而不是 IN

如果您只需要验证表中是否存在某个值，请优先使用 EXISTS 而不是 IN，因为 EXISTS 进程在找到搜索值后立即退出，而 IN 将扫描整个表。IN 应用于在列表中查找值。

同样，优先使用 NOT EXISTS 而不是 NOT IN。

SQL 关于 GROUP BY 的最佳实践

按降基数对多个分组进行排序

如果可能，请按降基数顺序对 GROUP BY 列进行分组。 也就是说，先按具有更多唯一值的列（如 ID 或电话号码）分组，然后再按具有较少不同值的列（如州或性别）分组。

SQL 关于 HAVING 的最佳实践

仅使用 HAVING 过滤聚合

在 HAVING 之前，先使用 WHERE 子句过滤掉值，然后再聚合和分组这些值。

SQL 关于 SELECT 的最佳实践

SELECT 列，而不是星号

指定您想要在结果中包含的列（尽管在首次浏览表时使用 * 也可以 - 只是记得 LIMIT 您的结果）。

SQL 关于 UNION 的最佳实践

优先使用 UNION ALL 而不是 UNION

如果重复项不是问题，UNION ALL 不会丢弃它们，并且由于 UNION ALL 的任务不是删除重复项，因此查询效率更高。

SQL 关于 ORDER BY 的最佳实践

尽可能避免排序，尤其是在子查询中

排序开销很大。 如果必须排序，请确保您的子查询没有不必要地对数据进行排序。

SQL 关于 INDEX 的最佳实践

本节是为在场的数据库管理员准备的（并且该主题太大，无法放入本文中）。 人们在数据库查询中遇到性能问题时，最常见的情况之一是缺乏足够的索引。

您应该索引哪些列通常取决于您要过滤的列（即，通常最终出现在您的 WHERE 子句中的列）。 如果您发现您始终按一组常见的列进行过滤，则应考虑为这些列建立索引。

添加索引

为外键列和频繁查询的列建立索引可以显着减少查询时间。 这是一个创建索引的示例语句

CREATE INDEX product_title_index ON products (title)

有不同类型的索引可用，最常见的索引类型使用 B 树来加速检索。 查看我们关于使仪表板更快的文章，并查阅您的数据库文档以了解如何创建索引。

使用部分索引

对于特别大的数据集或倾斜数据集，其中某些值范围出现频率更高，请考虑使用 WHERE 子句创建索引以限制索引的行数。 部分索引对于日期范围也很有用，例如，如果您只想索引过去一周的数据。

使用复合索引

对于通常在查询中一起使用的列（例如 last_name、first_name），请考虑创建复合索引。 语法类似于创建单个索引。 例如

CREATE INDEX full_name_index ON customers (last_name, first_name)

EXPLAIN

查找瓶颈

某些数据库（如 PostgreSQL）可以根据您的 SQL 代码深入了解查询计划。 只需在代码前加上关键字 EXPLAIN ANALYZE。 您可以使用这些命令来检查您的查询计划并查找瓶颈，或者比较查询的一个版本的计划与另一个版本的计划，以查看哪个版本更有效。

这是一个使用 PostgreSQL 可用的 dvdrental 示例数据库的示例查询。

EXPLAIN ANALYZE SELECT title, release_year
FROM film
WHERE release_year > 2000;

以及输出

 Seq Scan on film  (cost=0.00..66.50 rows=1000 width=19) (actual time=0.008..0.311 rows=1000 loops=1)
   Filter: ((release_year)::integer > 2000)
 Planning Time: 0.062 ms
 Execution Time: 0.416 ms

您将看到规划时间、执行时间以及成本、行数、宽度、时间、循环、内存使用量等所需的毫秒数。 阅读这些分析在某种程度上是一门艺术，但您可以使用它们来识别查询中的问题区域（例如嵌套循环或可以从索引中受益的列），并在您改进它们时进行优化。

这是 PostreSQL 关于使用 EXPLAIN 的文档。

WITH

使用公用表表达式 (CTE) 组织您的查询

使用 WITH 子句将逻辑封装在公用表表达式 (CTE)中。 这是一个查询示例，该查询查找 2019 年单位平均收入最高的产品，以及最大值和最小值。

WITH product_orders AS (
  SELECT o.created_at AS order_date,
          p.title AS product_title,
          (o.subtotal / o.quantity) AS revenue_per_unit
   FROM orders AS o
   LEFT JOIN products AS p ON o.product_id = p.id
   -- Filter out orders placed by customer service for charging customers
   WHERE o.quantity > 0
)
SELECT product_title AS product,
       AVG(revenue_per_unit) AS avg_revenue_per_unit,
       MAX(revenue_per_unit) AS max_revenue_per_unit,
       MIN(revenue_per_unit) AS min_revenue_per_unit
FROM product_orders
WHERE order_date BETWEEN '2019-01-01' AND '2019-12-31'
GROUP BY product
ORDER BY avg_revenue_per_unit DESC

WITH 子句使代码可读，因为主查询（您实际要查找的内容）不会被长子查询中断。

如果您的数据库具有命名笨拙的字段，或者需要少量数据整理才能获得有用的数据，您也可以使用 CTE 使您的 SQL 更具可读性。 例如，在处理 JSON 字段时，CTE 可能很有用。 这是一个从用户事件的 JSON blob 中提取和转换字段的示例。

WITH source_data AS (
  SELECT events->'data'->>'name'  AS event_name,
    CAST(events->'data'->>'ts' AS timestamp) AS event_timestamp
    CAST(events->'data'->>'cust_id' AS int) AS customer_id
  FROM user_activity
)
SELECT event_name,
       event_timestamp,
       customer_id
FROM source_data

或者，您可以将子查询另存为 SQL 代码段

是的，正如您可能预料的那样，空气动力学皮革巨嘴鸟实现了最高的单位平均收入。

使用 Metabase，您甚至不必使用 SQL

SQL 很棒。 但 Metabase 的查询构建器也很棒。 您可以使用 Metabase 的图形界面编写查询，以连接表、过滤和汇总数据、创建自定义列等等。 借助自定义表达式，您可以处理绝大多数分析用例，而无需使用 SQL。 使用 查询构建器 编写的问题也受益于自动下钻，这允许图表的查看者点击并浏览数据，这是 SQL 编写的问题不具备的功能。

明显的错误或遗漏？

关于 SQL 的书籍浩如烟海，因此我们在这里只是略窥门径。 您可以在我们的论坛上与其他 Metabase 用户分享您的 SQL 魔法秘诀。