格式化 SQL 来提高效率

本文由 伯乐在线 - cucr 翻译，黄利民 校稿。未经许可，禁止转载！
英文出处：msiman.ga。欢迎加入翻译小组。

背景

已格式化的SQL并不比未格式化SQL运行地更快。数据库可能真的不太在意你是否把逗号放在每个字段名称的前面或后面。为帮助你更理智和成为一名高效的SQL编写者，我建议你遵循一些格式化的指导方针。在这篇文章里，我将分享如何格式化SQL语句来提高工作效率。我对生产力这样定义，能够从SQL得到准确的结果，同时代码容易理解，修改和调试。我只会专注于SELECT语句，它占到我编写SQL的99%。格式化SQL代码是非常个性的选择，我很清楚，不同的人将钟爱他们自己的格式化规则。

问题样例

这是一个典型的SQL使用场景，业务上需要这样的报表，它的数据在三个表中，分别是customer、sales 和 location。在2015年1月，报表需要显示位于每一个邮政编码区域的客户数量和总销售额。这应该是一个简单的SQL语句，它连接三个表。

数据可能有问题

虽然SQL很容易，确保结果准确才是真正的挑战，以下是许多可能的原因中的一个，包括:

  • 数据可能来自不同数据源。这意味着在不同的表中的无法保证引用完整性。简单说，你不能假定 customer 表上的所有邮政编码是有效的，同时在 location 表上也存在该问题。
  • 存取客户数据的应用程序，可能没有适当的数据验证。可能已经存入不正确的邮政编码。
  • postcode 表可能没有所有邮政编码。新的邮政编码代码可能被引入，但自从上次更新还没有添加到表中。



第一原则

对我来说，格式化SQL更多地是从SQL获得正确的结果，因为它有明确的SQL，很容易跟踪。我做的第一件事编写获取客户总数的语句。这是个数字，我将在写完整个语句后进行对比。

我写的第一条语句是：

1
2
3
4
SELECT
   COUNT(DISTINCT cust_id) AS count_customers
FROM
        customers

Result:

count_customers
“10″

这个查询很重要，因为它遵循了 第一原则(外部链接) 。因为没有SQL连接，因此没有依赖，我知道这是正确的客户数量。我总是记下结果，因为我总是需要拿这个数字对比，在这篇文章是 10。

接下来我要做的就是添加必要的字段和表到这个查询。我强调添加这个词，因为根据我遵循的格式化规则，我可以注释掉查询的元素来得到和我应用第一原则时相同的结果。下面是我最终的格式化查询，使用格式化查询的方式。

格式化SQL

下面是我推荐的格式化的SQL，紧接后面是我进行的格式化选择的理由。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
 0
 ,c.cust_post_code
 ,p.location
 ,COUNT(DISTINCT c.cust_id) number_customers
 ,SUM(s.total_amount) AS total_sales
FROM
   customers c
   JOIN post_codes p ON c.cust_post_code = p.post_code
   JOIN sales s ON c.cust_id = s.cust_id
WHERE
   1=1
   AND s.sales_date BETWEEN '2015-01-01' AND '2015-01-31'
   --AND s.order_id = 5
GROUP BY
    c.cust_post_code
   ,p.location

总是使用表别名

这将会在你的SQL中得到证实。如果你不为参与查询的每个字段使用 别名(外部链接) ，有时候在后期，具有相同名称的字段添加到查询中使用的某个表中。你的查询和你的报表将出现一个错误(发现重复的字段名)。

逗号在字段前

当调试/测试我的查询时，这让我能轻易进行字段注释和取消注释，不需要在查询中修改任何其他行，以确保逗号在正确的地方。我看过一些文章，博主为了大事化小不得不改变另一个查询的一部分，以确保逗号是正确的，但是你如果花大部分时间编写和测试 SQL 语句，这是一个大问题。你按这种方式将会更有效率。这个在 SELECT 和 GROUP BY 查询部分都工作地很好。

我在开发环境使用 SELECT 0，同时倾向于进入生产环境之前删除它。它允许我把逗号放在所有字段前。如果没有 0，我想注释掉c.cust_post_code，它是第一个字段，我就必须注释掉第二个字段前面的逗号。我也会在 GROUP BY 子句做同样的事情。0 可以消除这个额外的工作。

在新的一行JOIN

将JOIN语句放在一个新行的优势包括：

  • 通过仅仅向下滚动JOIN语句列表就可以很容易地查看查询中所涉及到的所有表。
  • 使用 JOIN，相比将所有表和关系表达式都列在 WHERE 语句中，它可以将所有关系逻辑保持在一个地方。JOIN 语句也许不可能总是遵循在一行，但至少会在一个地方。
  • 注释掉 JOIN 会相对比较容易。在调试时，当你想知道哪个 JOIN 导致数据差异时，将很有用。

列模式编辑

在处理大量的字段时，列模式编辑非常方便。下面是我的第一次动画GIF展示，显示你如何注释掉所有非聚合字段。在实践中我使用

列模式编辑(外部链接)，不仅仅是注释字段还包括:

  • 大量创建索引
  • 在使用 UNION 语句时带有长的字段列表
  • 注 释GROUP BY 子句长的字段列表

测试查询结果

我不得不使用外连接来列出所有客户，因为并不是所有客户的邮政编码在 location 表中都能找到对应邮政编码。我能够做到这一点，通过在我的查询中反复包括和排除不同的字段和表，确保我能够与基于第一原则的最早查询保持一致。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
 0
 ,c.cust_post_code
 --,p.location
 ,COUNT(DISTINCT c.cust_id) number_customers
 ,SUM(s.total_amount) AS total_sales
FROM
   customers c
   --LEFT OUTER JOIN post_codes p ON c.cust_post_code = p.post_code
   JOIN sales s ON c.cust_id = s.cust_id
WHERE
   1=1
   AND s.sales_date BETWEEN '2015-01-01' AND '2015-01-31'
   --AND c.cust_post_code = 2000
   --AND p.post_code = 200
GROUP BY
    c.cust_post_code
   --,p.location

对我来说，像这样格式化SQL，意味着我不必编写为了检查数据做单独的测试。通过注释掉一些行，我能使用第一原则来测试数据的准确性。这可以提高我的效率，以及报表的准确性。

关于作者： cucr

posted @ 2015-04-28 08:57  Ranran  阅读(697)  评论(0)  编辑  收藏  举报