编写 SQL 查询的最佳实践

本文介绍了一些为数据分析师和数据科学家编写 SQL 查询的最佳实践。我们的大部分讨论将涉及一般的 SQL，但我们也会介绍一些 Metabase 特有的功能，这些功能让编写 SQL 变得轻而易举。

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

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

在寻找你的针之前，先把你的干草堆弄得尽可能小

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

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

我们将回顾查询执行的一般顺序，并在此过程中提供一些减少搜索空间的技巧。然后我们将讨论三种需要添加到您的工具带中的基本工具：INDEX、EXPLAIN 和 WITH。

首先，了解你的数据

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

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

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

开发您的查询

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

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

查询执行的一般顺序

在我们讨论编写 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 语句中使用这些别名，这样数据库（和您的读者）就不需要解析哪个列属于哪个表。请注意，如果您在多个表中有同名的列，您需要用表名或别名明确引用它们。

避免

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` 子句的列上使用函数会严重拖慢您的查询速度，因为该函数会使查询不可搜索参数化（即，它会阻止数据库使用索引来加速查询）。列上的函数会迫使数据库对表的每一行都运行该函数，而不是使用索引跳到相关的行。

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

避免

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

您将看到计划时间和执行时间所需的毫秒数，以及成本、行数、宽度、次数、循环、内存使用情况等等。阅读这些分析报告有点像一门艺术，但您可以用它们来识别查询中的问题区域（例如嵌套循环，或者可以从索引中受益的列），并在您优化它们时使用。

这里是 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` 子句使代码可读，因为主查询（您实际要查找的内容）不会被一个长的子查询打断。

如果您的数据库字段命名 awkwardly，或者需要一些数据整理才能获得有用的数据，您也可以使用 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

或者，您可以将子查询保存为代码片段

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

有了 Metabase，你甚至不需要使用 SQL

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

有明显的错误或遗漏吗？

关于 SQL 的书籍可以堆满图书馆，所以我们在这里只是触及了皮毛。您可以在我们的论坛上与其他 Metabase 用户分享您的 SQL 魔法秘诀。