编写SQL查询的最佳实践

本文将涵盖为数据分析师和数据科学家编写SQL查询的一些最佳实践。我们的讨论将主要集中在SQL的一般用法上，但也会包括一些关于Metabase特定功能的注意事项，这些功能使得编写SQL变得简单。

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

关于过早优化的标准警告也适用于此处。在你知道你的查询返回了你想要的数据之前，避免调整你的SQL查询。即使如此，也只有在查询频繁运行（例如，为流行的仪表板供电）或查询跨越大量行的情况下，才优先考虑优化你的查询。一般来说，优先考虑准确性（查询是否产生预期的结果），以及可读性（其他人是否可以轻松理解并修改代码），然后再考虑性能。

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

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

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

我们将回顾查询执行的通用顺序，并在过程中提供一些减少搜索空间的提示。然后，我们将讨论要添加到你的工具包中的三个基本工具：索引、EXPLAIN和WITH。

首先，了解你的数据

在编写任何代码之前，通过研究元数据熟悉你的数据，以确保某个列确实包含你期望的数据。Metabase中的SQL编辑器（通过书签图标访问）有一个方便的数据参考标签，你可以浏览数据库中的表，并查看它们的列和连接。

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

Metabase为你提供了许多探索数据的不同方式：你可以使用X-ray表格，使用查询构建器构建问题，将保存的问题转换为SQL代码，或从现有的SQL查询开始构建。我们将在其他文章中介绍这些内容；现在，让我们通过查询的一般工作流程来了解一下。

开发你的查询

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

• 如上所述，研究列和表元数据。如果你使用的是Metabase的本地（SQL）编辑器，你还可以搜索包含你正在处理的表和列的SQL代码的SQL片段。片段让你可以看到其他分析师是如何查询数据的。或者，你可以从一个现有的SQL问题开始编写查询。
• 为了感受一个表中的值，从你正在处理的表中SELECT *，并LIMIT你的结果。在细化列（或通过连接添加更多列）时，保持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

这里的难点是您引入了一点点维护开销：如果您更改了代码，您需要确保注释仍然相关并且是最新的。但这是为了可读代码而付出的小小代价。

FROM的SQL最佳实践

使用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
  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 的字段），您的查询可能会出错。

注意，字段过滤器与表别名不兼容，因此当将过滤器小部件连接到字段过滤器时，您需要移除别名。

WHERE子句的SQL最佳实践

在HAVING之前使用WHERE进行过滤

使用WHERE子句来过滤多余的行，这样您就不必首先计算这些值。只有移除不相关的行，并在聚合这些行并将它们分组之后，您才应该包括HAVING子句来过滤聚合。

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

在WHERE子句中使用列上的函数会严重影响查询速度，因为该函数使查询变为非Sargable（即，它阻止数据库使用索引来加速查询）。函数迫使数据库在表的每一行上运行函数，而不是使用索引跳到相关的行。

并且记住，连接运算符 || 也是一个函数，因此不要尝试通过连接字符串来过滤多个列。请优先使用多个条件。

避免

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。

GROUP BY的SQL最佳实践

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

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

HAVING的SQL最佳实践

仅使用HAVING进行聚合筛选

在HAVING之前，先使用WHERE子句过滤值，然后再对聚合和分组后的值进行筛选。

SELECT的最佳SQL实践

选择列，而不是星号(*)

指定您希望包含在结果中的列（尽管在初次探索表时可以使用*，但请记住使用LIMIT限制结果）。

UNION的最佳SQL实践

优先使用UNION ALL而不是UNION

如果不考虑重复值，UNION ALL不会丢弃它们，由于UNION ALL不负责删除重复值，因此查询将更高效。

ORDER BY的最佳SQL实践

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

排序很昂贵。如果必须排序，请确保您的子查询不需要进行排序。

INDEX的最佳SQL实践

本部分面向数据库管理员（以及一个太大而无法包含在这篇文章中的主题）。人们在数据库查询中遇到性能问题时，最常见的问题之一是缺乏足够的索引。

您应该索引哪些列通常取决于您正在过滤的列（即，哪些列通常出现在您的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

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

以下是在使用EXPLAIN的PostgreSQL文档。

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 片段

没错，如您所料，气动皮革 toucan 以每单位销售的平均收入最高。

在 Metabase 中，您甚至不需要使用 SQL

SQL 很神奇。但 Metabase 的 查询构建器 同样出色。您可以使用 Metabase 的图形界面来 连接表、筛选和汇总数据、创建 自定义列 等。通过 自定义表达式，您可以处理大多数分析用例，而无需触及 SQL。使用 查询构建器 编写的查询也受益于自动 钻取，这允许图表的查看者点击并探索数据，这是 SQL 编写的查询所不具备的功能。

有明显的错误或遗漏吗？

有关 SQL 的书籍库，所以我们只是触及了皮毛。您可以在我们的论坛 上与其他 Metabase 用户分享您的 SQL 巧妙之处。