原文地址：SQL Tutorial: How To Write Better Queries

原文作者：Karlijn Willems

译文出自：掘金翻译计划

本文永久链接：https://github.com/xitu/gold-miner/blob/master/TODO/sql-tutorial-how-to-write-better-queries.md

译者：临书

校对者：steinliber, xiaoyusilen

SQL 指引：如何写出更好的查询

结构化查询语言（SQL）是数据科学行业的一种不可或缺的技能，一般来说，学习这项技能是相当简单的。然而大多数人都忘记 SQL 不仅仅是写查询语句，这只是第一步。确保查询高性能，或者符合上下文语意又完全是另外一回事了。

这就是为什么本篇 SQL 教程要引导你，可以通过以下步骤来评估你的查询：

你对 SQL 课程感兴趣吗？那就来学习 DataCamp 的数据科学的 SQL 简介课程吧！

为什么我应该为数据科学学习 SQL？

SQL 远未消亡：无论你是申请数据分析师，数据工程师，数据科学家还是任何其他职位，你都可以从数据科学行业的职位描述中发现 SQL 是最需要的技能之一。参加 O'Reilly 数据科学工资调查报告的 70％的受访者证实了这一点，他们表示他们会在专业场景中使用 SQL。而且，在本次调查中，SQL（70%）远胜于 R（57％）和 Python（54％）编程语言。

你得知一个情况：当你正在努力找数据科学行业的工作时，SQL 是一项必须具备的技能。

对于一个20世纪70年代初开发的语言来说，还不错，对吧？

但是为什么被使用的如此频繁？为什么 SQL 不会消失，即使它已经存在了很长时间了？

有几个原因：第一个原因是大多数公司将数据存储在关系型数据库管理系统（RDBMS）或关系数据流管理系统（RDSMS）中，你需要 SQL 才能访问这些数据。 SQL 是数据的通用语言：它使你能够与几乎任何数据库进行交互，甚至可以在本地建立自己的数据库！

如果这还不够，请记住有很多 SQL 的实现在供应商之间不兼容，并不一定遵守标准。因而，了解标准 SQL 是你在（数据科学）行业中找到一条路的要求之一。

除此之外，可以肯定地说，SQL 也被更新的技术所接受，例如 Hive，用于查询和管理大型数据集的类 SQL 查询语言界面，或可用于执行 SQL 查询的 Spark SQL。虽然你发现标准可能与你已知的有所不同，但学习曲线将会更加容易。

如果你想做一个比较，认为它和学线性代数一样：通过把所有的精力放在这个主题上，你甚至可以使用它来掌握机器学习！

简而言之，这就是为什么你应该学习这门查询语言：

即使对于新手它也是相当容易学习的。学习曲线是相当容易和平滑的，以至于在学习的任何阶段你都能写出查询。
遵循“一旦学习，处处适用”的原则，所以这是一个对你时间的伟大投资！
它是对编程语言的极好补充; 在某些情况下，编写查询甚至比编写代码更为优先，因为它性能更高！
…

你还在等什么呢？

SQL 处理 & 查询执行

为了提高你 SQL 查询的性能，当你按快捷方式运行查询时，你首先需要知道内部发生了什么。

首先，查询被解析成“解析树”；分析查询，看是否符合语法和语义要求。解析器创建输入查询的内部表示。然后将输出传递给重写引擎。

然后，优化器的任务是找到给定查询的最佳执行或查询的计划。执行计划准确地定义了每个操作使用什么算法，以及如何协调操作的执行。

为了找到最佳的执行计划，优化器列举所有可能的执行计划，确定每个计划的性质或成本，获取有关当前数据库状态的信息，然后选择其中最佳的一个作为最终的执行计划。由于查询优化器可能并不完善，因此数据库用户和管理员有时需要手动检查并调整优化器生成的计划以获得更好的性能。

现在你可能想知道什么是一个“好的查询计划”。

如你所见，一个计划的质量在查询中起着重要的作用。更具体地说，评估计划所需的磁盘 I/O，CPU成本和数据库客户端可以观察到的总体响应时间以及总执行时间等因素至关重要。这就涉及到了时间复杂度的概念，在后面你将会看到更多与此相关的内容。

接下来，执行所选择的查询计划，由系统的执行引擎进行评估并返回查询结果。

在上节中描述的可能不是很清楚的是，Garbage In, Garbage Out（GIGO）原则在查询处理和执行中会自然地显现：制定查询的人掌握着你 SQL 查询性能的关键，如果优化器得到的是一个不好的查询语句，那么那么它也只能做到这么多...

这意味着你在编写查询时可以执行一些操作。如你在介绍中所见，责任是双重的：它不仅仅是写出符合一定标准的查询，而且还涉及收集查询中性能问题可能潜伏在哪里的意识。

一个理想的出发点是在你的查询中考虑可能会潜入问题的“地方”。新手通常会在以下四个子句和关键字中遇到性能问题。

WHERE 子句
任何 INNER JOIN 或 LEFT JOIN 关键字; 还有，
HAVING 子句；

当然，这种方法简单而原始，但作为初学者，这些子句和声明是很好的指引，而且确切地说，当你刚开始时，这些地方就是容易出错的地方，更讽刺的是这些错误很难被发现。

然而，你也应该意识到，性能只有在实际场景中才有意义：只是单纯的说这些子句和关键字是不好的没有任何意义。当然，查询中有 WHERE 或 HAVING 子句不一定意味着这是一个坏的查询...

查看以下内容，了解更多有关的构建查询的反模式和可替代的方法。这些提示和技巧可作为指导。如何重写以及是否真的需要重写取决于数据量，数据库，以及查询所需的次数等等。它完全取决于你查询的目标，并且有一些你要查询的数据库的之前的了解也是至关重要的！

1. 仅检索你需要的数据

当编写 SQL 查询时，「数据越多越好」的思维方式是不应该的：获取比你实际需求更多的数据不仅会有看错的风险，而且性能可能会因为查询太多数据而受到影响。

这就是小心处理 SELECT 语句，DISTINCT 子句和 LIKE 运算符是个不错的主意。

当你写好你的查询时，你能检查的第一件事情就是 SELECT 语句是否已经是最紧凑了。你的目标应该是从 SELECT 中删除不必要的列。这样，你强制自己只提取符合查询目的的数据。

如果具有 EXISTS 的相关子查询，则应尝试在该子查询的 SELECT 语句中使用常量，而不是选择实际列的值。当你只检查数据是否存在时，这是特别方便的。

记住相关子查询是使用外部查询中的值的子查询。注意，尽管 NULL 可以在此上下文中当作“常量”使用，但是这会令人非常困惑！

考虑下面这个例子，并理解使用常量的意义在哪：

SELECT driverslicensenr, name
FROM Drivers
WHERE EXISTS (SELECT '1' FROM Fines
              WHERE fines.driverslicensenr = drivers.driverslicensenr);

提示：可以很方便知道，使用相关子查询通常不是一个好主意。你应该考虑使用 INNER JOIN 重写来避免它们：

SELECT driverslicensenr, name
FROM drivers
INNER JOIN fines ON fines.driverslicensenr = drivers.driverslicensenr;

SELECT DISTINCT 语句是用来返回不同的值的。如果可以，你应该你要尽量避免使用 DISTINCT 这个子句；就像你在其他例子中看到的一样，如果你把这个子句添加到你的查询中，执行时间肯定会增加。因此，经常考虑是否真的需要 DISTINCT 操作来获取想要的结果是一个好主意。。

当你在一个查询中使用 LIKE 操作符时，如果匹配模式以 % 或者 _ 开始，那么是不会使用索引的。它将阻止数据库使用索引（如果存在）。当然，在另一个方面看，这种类型的查询会潜在地返回过多的记录，这不一定满足你的查询目标。

再次，你对存储在数据库中的数据的了解程度可以帮助你制订一个模式，这可以帮助你从所有数据中正确过滤出和你的查询真正相关的行。

2. 不要输出太多结果

当你不能过滤掉 SELECT 语句中的列时，你可以考虑用其他方法限制你的结果。以下是 LIMIT 语句和数据类型的转换方法。

你可以通过为查询添加 LIMIT 或者 TOP 子句来为查询结果设置最大行数。这儿是一些例子：

SELECT TOP 3 * FROM Drivers;

注意你可以进一步指定 PERCENT，比如，你可以通过 SELECT TOP 50 PERCENT * 这个查询语句来替换第一行。

SELECT driverslicensenr, name FROM Drivers LIMIT 2;

此外，你还可以添加 ROWNUM 子句，这相当于在查询中使用 LIMIT：

SELECT *
FROM Drivers
WHERE driverslicensenr = 123456 AND ROWNUM <= 3;

你应该始终使用最有效的，也就是最小的数据类型。当小的数据类型已经足够的时候你提供一个巨大的数据类型总是有风险的。

然而，当你将数据类型转换添加到查询中时，你肯定增加了它的执行时间。

一个替代方案是尽量避免数据类型转换。但是还要注意，数据类型转换不是总能从查询中被删除或者省略的，而且当你在查询语句包含它们的时候一定要注意，你可以在执行查询之前测试添加它们的影响。

3. 不要让查询比需求更复杂

数据类型转换将你带到了下一个关键点：你不应该过度设计你的查询。试着保持简单高效。作为一个提示，这可能看起来太简单或者愚蠢了，特别是在查询可能变得复杂的情况下。

然而，你将会在下一部分提到的示例中看到，你可以很轻松的把本应更复杂的查询变得简单。

当你在你的查询里使用 OR 操作符时，很可能你没有使用索引。

记住索引是一种数据结构，可以提高数据库表中的数据检索速度，但它是有代价的：它需要额外的写入和额外的存储空间来维护索引结构。索引用来快速定位或查找数据而无需在每次访问数据库时查询每一行。索引可以使用数据库表中的一列或多列来创建。

如果你不使用数据库包含的索引，你的查询会花费更长的时间来执行。这就是为什么最好在查询中找到使用 OR 运算符的替换方案；

考虑以下查询：

SELECT driverslicensenr, name
FROM Drivers
WHERE driverslicensenr = 123456 OR driverslicensenr = 678910 OR driverslicensenr = 345678;

你可以将运算符替换为：

SELECT driverslicensenr, name
FROM Drivers
WHERE driverslicensenr IN (123456, 678910, 345678);

包含 UNION 的两个 SELECT 语句。

提示：这儿你需要小心，没有必要就不要使用 UNION 运算符，因为你会多次查询同一个表多次，这是不必要的。同时，你必须意识到当你在查询语句里使用 UNION 时，执行时间会变长。UNION 操作符的替代是：将所有条件都放在一个 SELECT 结构中，或者使用 OUTER JOIN 替代 UNION 来重新构建查询。

提示：在这里也要记住的一点是，尽管 OR 以及下面将要提到的其他运算符可能不使用索引，索引查找不总是更好的。

就像 OR 运算符一样，当你的查询包含 NOT 操作符时，也很可能不使用索引。这将不可避免的减慢你的查询。如果你不明白这是什么意思，考虑下以下查询：

SELECT driverslicensenr, name FROM Drivers WHERE NOT (year > 1980);

这个查询跑起来肯定比你预料还要慢，主要是因为它构建的太过于复杂了：在这样的情况下，最好寻找一个替代方案。考虑使用比较运算符替换 NOT，比如 >，<> 或者 !>；上面的例子可能会被重写为这样：

SELECT driverslicensenr, name FROM Drivers WHERE year <= 1980;

看起来已经更加整洁了，不是吗？

AND 是另一个不使用索引的操作符，如果以过于复杂和低效的方式使用，它会减慢你的查询，就像下面的例子：

SELECT driverslicensenr, name
FROM Drivers
WHERE year >= 1960 AND year <= 1980;

最好使用 BETWEEN 运算符重写这个查询：

SELECT driverslicensenr, name
FROM Drivers
WHERE year BETWEEN 1960 AND 1980;

ALL 和 ALL 运算符你也应该小心使用，将他们包含进查询中会导致不使用索引。替代方法使用聚合功能，在这里比较方便的方法是使用像 MIN 或者 MAX 的聚合函数。

提示：在你使用所提出的方案的情况下，你应该意识到，所有的聚合函数比如 SUM，AVG，MIN，MAX 在多行的时候会导致很长时间的查询，在这种情况下，你可以尝试减少要处理的行数或预先计算这些值。当你决定使用哪个查询时，最重要的是清楚你的环境和查询目标。

在使用列进行计算或者列作为标量函数的参数时，也是不会使用索引的。一个特定的解决方案是简单的隔离这个特殊列，使其不再是计算或者函数的一部分或参数。请考虑一下示例：

SELECT driverslicensenr, name
FROM Drivers
WHERE year + 10 = 1980;

这看起来很有趣，是不？相反，试着重新考虑如何计算，然后像这样重写查询：

SELECT driverslicensenr, name
FROM Drivers
WHERE year = 1970;

4. 不要暴力查询

最后一个提示，你不应该总是太限制查询，因为这也会影响性能。特别是 join 语句和 HAVING 子句。

当你对两个表使用 join 时，考虑你 join 的两张表的顺序是很重要的。如果一张表比另一张大很多，你最好重写你的查询让最大的表最后做 join 操作。

减少 Joins 的条件

当你加了太多的条件到你的 joins 语句，你有义务选择一个特定的路径，虽然这个路径并不总是最高效的那个。

将 HAVING 子句添加进 SQL 是因为 WHERE 关键字不能和聚合方法一起使用。HAVING 的典型的用法就是和 GROUP BY 子句来约束分组聚合后的结果，使其满足一些精确匹配条件。然而，你知道的，使用这个子句是不会用到索引的，会导致查询不能很好的执行。

如果你在寻找替代的方案，考虑使用 WHERE 子句，请看如下的查询：

SELECT state, COUNT(*) FROM Drivers WHERE state IN ('GA', 'TX') GROUP BY state ORDER BY state

SELECT state, COUNT(*) FROM Drivers GROUP BY state HAVING state IN ('GA', 'TX') ORDER BY state

第一个查询使用 WHERE 子句限制需要求和的行数，而第二个查询对表中的所有行进行了求和，然后使用 HAVING 子句来舍弃其中的部分。在这种情况下，选择使用 WHERE 子句显然是更好的，因为你不会浪费任查询资源。

你会发现，这并不是限制最终结果集，而是限制查询中的中间记录的数量。

注意这两个子句之间的区别在于，WHERE 子句引入了单行的条件，而 HAVING 子句引入了一个选择集合或结果的条件，比如 MIN，MAX，SUM，… 这些都已经从多行生成了的。

你看，当你想以尽可能的提高性能为前提的时候，评估语句质量，构建查询还有改写查询并不是一件容易的工作；当你构建运行在专业环境中的查询的时候，避免反模式和考虑替代方案也将成为你责任的一部分。

这个清单只是一些小的反模式的概述和技巧，可能对新手有些帮助；如果你想了解更多高级开发人员常见的反模式，查看 stackoverflow 的这个讨论。

基于集合与程序方法的查询

上述反模式隐含的点实际上归结为基于集合与程序方法构建查询的差异。

程序方法的查询是一种很像编程的一种查询方式：你告诉系统做什么，怎么做。

一个例子是你使用冗余的连接操作或者滥用 HAVING 子句的情况下，就像上面的例子，你可以通过执行一个函数调用另一个函数来查询数据库，或者使用包含循环，用户定义方法，游标等，来获取最终结果。在这个方法中，你会经常发现你自己请求一个数据的子集，然后再请求这个数据的子集等等。

毫不奇怪，这个方法经常被称为「逐步」或者「逐行」查询。

另一种方法是基于集合的方法，你只需要指定做什么。你的职责包含从查询中指定要获得的结果集的条件或要求。至于你的数据是如何获取到的，这取决于内部决定查询实现的机制：让数据库引擎来确定查询最好的算法和执行逻辑。

由于 SQL 是基于集合的，这种方法（基于集合）比程序方法更有效几乎不会让人感到惊讶，这也是一个惊喜，也解释了为什么在某些情况下，SQL 可以比代码更快的工作。

提示在查询中基于集合的方法也是数据科学行业最顶级的雇主所要求你掌握的方法！你经常需要在这两种方法之间切换。

注意如果你发现你自己有程序类型的查询，你应该考虑重写或者重构它。

从查询到执行计划

-------------知道反模式不是静态的，而是随着你做为 SQL 开发者的成长而演进，当你考虑替代方案的时候也意味着你正在避免反模式查询和重写查询的这个事实，这是一个十分困难的任务。任何帮助都可以派上用场，这就是为什么使用一些工具通过更结构化的方式来优化你的查询或许是个不错的选择。

注意还有一些上一节提到的反模式源于性能的问题的考虑，比如 AND，OR 和 NOT 操作符缺少索引的使用。对性能的思考不仅需要结构化的方法，还需要更多的深入的方法。

然而可能的是，这种结构化和深入的方法更多是基于查询计划的，即首先被解析为「解析树」，然后在确定每个操作具体使用什么算法，还有如何使执行操作更协调。

正如你在介绍中读到的，你可能需要手动检查优化器的生成计划。在这种情况下，你将需要通过查看查询计划来再次分析你的查询。

要掌握这种查询计划，你将需要使用数据库管理系统为你提供工具，你可以使用的工具如下：

生成查询计划的图形表示的一些工具包，看以下这个例子：

其他工具将能够为你提供查询计划的文本描述。一个例子是 Oracle 中的 EXPLAIN PLAN 语句，但指令的名称根据你使用的 RDBMS 而有所不同。在其他数据库，你可能会看到 EXPLAN（MySQL，PostgreSQL）或者 EXPLAIN QUERY PLAN（SQLite）。

注意如果你平时使用 PostgreSQL，你可以在 EXPLAIN 之间做出区分，这里你只得到了一个描述，它是说明还未执行的查询计划会如何执行，而 EXPLAIN ANALYZE 实际上执行了查询然后返回对预期与实际的查询计划的分析。一般来说，一个实际的执行计划就是一个实际的查询计划，虽然在逻辑上是等价的，一个实际的执行计划更为有用，因为它包含执行查询时实际发生的其他细节和统计信息。

在本节的剩余部分，你将会学习到更多关于 EXPLAIN 和 ANALYZE 的信息，以及如何使用这两个去了解更多你的查询计划和查询性能的信息。

提示：如果你想了解更多关于 EXPLAIN 或更详细的查看实例，考虑阅读 Guillaume Lelarge 写的这本书 “Understanding Explain”。

时间复杂度和大 O

现在你已经简要的检查了查询计划，你可以在复杂度计算的帮助下开始更深入的研究具体的性能问题。理论计算机科学这一领域着重于根据难度对问题进行分类；这些计算问题可以是算法，也可以是查询。

然而，对于查询，你并不一定是根据他们的困难程度分类，而是根据运行它然后拿到返回结果的时间来分类。这个被叫做时间复杂度，你可以使用大 O 符号来表达和衡量这种复杂性。

使用大 O 符号，输入任意大时，你可以根据输入与运行时间的相对增长速度来衡量运行时间。大 O 表示法排除系数和低阶的项，以便于你关注查询运行时间的关键部分：增长率。当以这种方式表示时，丢弃系数与低阶的项，时间复杂度被认为是渐进式描述的。这意味着输入会变为无穷大。

在数据库语言中，复杂度衡量了数据库表数据增加之后，查询该表数据所花时间相对增加了多少的过程。

注意你的数据库大小不仅仅因为表里存储的数据增多而变大，索引在其中对大小影响也起了很大的作用。

正如前面所述，执行计划除了前面所说的以外，还定义了每一步操作使用什么算法，这使得每次查询执行的时间可以在逻辑上表示为查询计划中涉及表大小的函数。换句话说，你可以使用大 O 符号和执行计划预估查询的复杂性和性能。

在接下来的小节中，你会了解关于四种时间复杂度类型的一般概念，你将会看到一些示例，说明查询的时间复杂度如何根据你运行它们上下文的不同而有所不同的。

提示：索引是故事的一部分！

注意，因为不同的数据库有不同类型的索引、不同的执行计划、不同的实现，所以下面列出的几个时间复杂度是很通用的，会根据你配置的不同而变化。

更多阅读在这儿。

总而言之，你可以查看以下备忘单，以根据时间复杂度以及其执行情况估计查询的性能：

SQL 调优

考虑到查询计划和时间复杂性，你可以考虑进一步调整 SQL 查询，特别注意以下几点：

大表的全表扫描替换为索引的扫描；
确保你正在使用最佳的表连接顺序；
确保的使用索引优化；还有
缓存小表的全表扫描。

祝贺！你已经看到了这篇博文的结尾，这只是帮助你对 SQL 查询性能的一瞥。你希望对反模式，查询优化器，审查工具，预估和解释查询计划的复杂性有更多的见解，然而，还有更多的东西等你去发现！如果你想知道更多，可以考虑读这本由R. Ramakrishnan 和 J. Gehrke 写的「Database Management Systems」。

最后，我不想错过这个来自 StackOverFlow 用户那里的引用

「我最喜欢的反模式不是测试你的查询。

这适用于：

你的查询涉及了不止一张表。

你认为你的查询有一个优化的设计，但不愿意去验证你的假设。

你会接受第一个成功的查询，它是否是最优的，你并不清楚。」

如过你想开始使用 SQL，可以考虑学习 DataCamp 的 Intro to SQL for Data Science 课程！

掘金翻译计划是一个翻译优质互联网技术文章的社区，文章来源为掘金上的英文分享文章。内容覆盖 Android、iOS、React、前端、后端、产品、设计等领域，想要查看更多优质译文请持续关注掘金翻译计划、官方微博、知乎专栏。