编写 SQL 查询的最佳实践

使用 Metabase 学习 SQL

免费下载 Metabase，或免费试用 Metabase Cloud

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

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

此处适用避免过早优化的标准警告。在确定查询返回所需数据之前，请勿调整 SQL 查询。即使确定了，也只有当查询频繁运行（例如支持热门仪表盘）或查询遍历大量行时，才优先优化查询。通常，在担心性能之前，应优先考虑准确性（查询是否产生预期结果）和可读性（其他人是否能轻松理解和修改代码）。

在寻找针之前，尽量缩小您的“稻草堆”

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

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

我们将回顾查询执行的一般顺序，并在此过程中提供一些技巧，以缩小您的搜索范围。然后，我们将讨论三个必不可少的工具：索引 (INDEX)、解释 (EXPLAIN) 和通用表表达式 (WITH)。

首先，了解您的数据

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

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

Metabase 为您提供了多种探索数据的方式：您可以对表进行X 射线分析、使用查询构建器撰写问题、将保存的查询转换为 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 最佳实践

先用 WHERE 筛选，再用 HAVING 筛选

使用 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

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

这是 PostgreSQL 关于使用 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

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

是的，正如您所料，流线型皮革巨嘴鸟的每单位销售平均收入最高。

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

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

明显的错误或遗漏？

关于 SQL 的书籍浩如烟海，我们在此只触及了皮毛。您可以在我们的论坛上与其他 Metabase 用户分享您的 SQL 魔法秘诀。