终极指南——如何编写更好的SQL查询？-LINUX-PHP中文网

基于集合和程序的方法进行查询

反向模型中隐含的事实是，建立查询时基于集合和程序的方法之间存在着不同。

查询的程序方法是一种非常类似于编程的方法：你告诉系统需要做些什么以及如何做。例如上一篇文章中的示例，通过执行一个函数然后调用另一个函数来查询数据库，或者使用包含循环、条件和用户定义函数（UDF）的逻辑方式来获得最终查询结果。你会发现通过这种方式，一直在请求一层一层中数据的子集。这种方法也经常被称为逐步或逐行查询。
另一种是基于集合的方法，只需指定需要执行的操作。使用这种方法要做的事情就是，指定你想通过查询获得的结果的条件和要求。在检索数据过程中，你不需要关注实现查询的内部机制：数据库引擎会决定最佳的执行查询的算法和逻辑。

由于 SQL 是基于集合的，所以这种方法比起程序方法更加有效，这也解释了为什么在某些情况下，SQL 可以比代码工作地更快。

基于集合的查询方法也是数据挖掘分析行业要求你必须掌握的技能！因为你需要熟练的在这两种方法之间进行切换。如果你发现自己的查询中存在程序查询，则应该考虑是否需要重写这部分。

终极指南——如何编写更好的SQL查询？

从查询到执行计划

反向模式不是静止不变的。在你成为 SQL 开发者的过程中，避免查询反向模型和重写查询可能会是一个很艰难的任务。所以时常需要使用工具以一种更加结构化的方法来优化你的查询。

对性能的思考不仅需要更结构化的方法，还需要更深入的方法。

然而，这种结构化和深入的方法主要是基于查询计划的。查询计划首先被解析为“解析树”并且准确定义了每个操作使用什么算法以及如何协调操作过程。

查询优化

在优化查询时，很可能需要手动检查优化器生成的计划。在这种情况下，将需要通过查看查询计划来再次分析你的查询。

要掌握这样的查询计划，你需要使用一些数据库管理系统提供给你的工具。你可以使用以下的一些工具：

一些软件包功能工具可以生成查询计划的图形表示。
其它工具能够为你提供查询计划的文本描述。

请注意，如果你正在使用 PostgreSQL，则可以区分不同的 EXPLAIN，你只需获取描述，说明 planner 如何在不运行计划的情况下执行查询。同时 EXPLAIN ANALYZE 会执行查询，并返回给你一个评估查询计划与实际查询计划的分析报告。一般来说，实际执行计划会切实的执行这个计划，而评估执行计划可以在不执行查询的情况下，解决这个问题。在逻辑上，实际执行计划更为有用，因为它包含了执行查询时，实际发生的其它细节和统计信息。

接下来你将了解 XPLAIN 和 ANALYZE 的更多信息，以及如何使用这两个命令来进一步了解你的查询计划和查询性能。要做到这一点，你需要开始使用两个表： one_million 和 half_million 来做一些示例。

你可以借助 EXPLAIN 来检索 one_million 表的当前信息：确保已将其放在运行查询的首要位置，在运行完成之后，会返回到查询计划中：

EXPLAIN
SELECT *
FROM one_million;
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-emphasis">___</span>_
Seq Scan on one_million
(cost=0.00..18584.82 rows=1025082 width=36)
(1 row)

在以上示例中，我们看到查询的 Cost 是0.00..18584.82 ，行数是1025082，列宽是36。

Linux Security Scanner

Linux 安全审计工具，检查 SSH 配置、开放/监听端口、防火墙规则（ufw/iptables/nftables）、失败登录尝试、sudoers 权限...

下载

同时，也可以借助 ANALYZE 来更新统计信息。

ANALYZE one_million;
EXPLAIN
SELECT *
FROM one_million;
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-emphasis">___</span>_
Seq Scan on one_million
(cost=0.00..18334.00 rows=1000000 width=37)
(1 row)

除了 EXPLAIN 和 ANALYZE，你也可以借助 EXPLAIN ANALYZE 来检索实际执行时间：

EXPLAIN ANALYZE
SELECT *
FROM one_million;
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span>_
Seq Scan on one_million
(cost=0.00..18334.00 rows=1000000 width=37)
(actual time=0.015..1207.019 rows=1000000 loops=1)
Total runtime: 2320.146 ms
(2 rows)

使用 EXPLAIN ANALYZE 的缺点就是需要实际执行查询，这点值得注意！

到目前为止，我们看到的所有算法是顺序扫描或全表扫描：这是一种在数据库上进行扫描的方法，扫描的表的每一行都是以顺序（串行）的顺序进行读取，每一列都会检查是否符合条件。在性能方面，顺序扫描不是最佳的执行计划，因为需要扫描整个表。但是如果使用慢磁盘，顺序读取也会很快。

还有一些其它算法的示例：

EXPLAIN ANALYZE
SELECT *
FROM one<span class="hljs-emphasis">_million JOIN half_</span>million
ON (one<span class="hljs-emphasis">_million.counter=half_</span>million.counter);
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span>_
Hash Join (cost=15417.00..68831.00 rows=500000 width=42)
(actual time=1241241.471..5912.553 rows=500000 loops=1)
Hash Cond: (one<span class="hljs-emphasis">_million.counter = half_</span>million.counter)
<span class="hljs-code">    -> Seq Scan on one_million</span>
<span class="hljs-code">    (cost=0.00..18334.00 rows=1000000 width=37)</span>
<span class="hljs-code">    (actual time=0.007..1254.027 rows=1000000 loops=1)</span>
<span class="hljs-code">    -> Hash (cost=7213.00..7213.00 rows=500000 width=5)</span>
<span class="hljs-code">    (actual time=1241241.251..1241241.251 rows=500000 loops=1)</span>
<span class="hljs-code">    Buckets: 4096 Batches: 16 Memory Usage: 770kB</span>
<span class="hljs-code">    -> Seq Scan on half_million</span>
<span class="hljs-code">    (cost=0.00..7213.00 rows=500000 width=5)</span>
(actual time=0.008..601.128 rows=500000 loops=1)
Total runtime: 6468.337 ms

我们可以看到查询优化器选择了 Hash Join。请记住这个操作，因为我们需要使用这个来评估查询的时间复杂度。我们注意到了上面示例中没有 half_million.counter 索引，我们可以在下面示例中添加索引：

<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> <span class="hljs-keyword">ON</span> half_million(counter);
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">ANALYZE</span>
<span class="hljs-keyword">SELECT</span> *
<span class="hljs-keyword">FROM</span> one_million <span class="hljs-keyword">JOIN</span> half_million
<span class="hljs-keyword">ON</span> (one_million.counter=half_million.counter);
QUERY PLAN
______________________________________________________________
<span class="hljs-keyword">Merge</span> <span class="hljs-keyword">Join</span> (<span class="hljs-keyword">cost</span>=<span class="hljs-number">4.12</span>.<span class="hljs-number">.37650</span><span class="hljs-number">.65</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> width=<span class="hljs-number">42</span>)
(actual <span class="hljs-keyword">time</span>=<span class="hljs-number">0.033</span>.<span class="hljs-number">.3272</span><span class="hljs-number">.940</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> loops=<span class="hljs-number">1</span>)
<span class="hljs-keyword">Merge</span> Cond: (one_million.counter = half_million.counter)
    -> <span class="hljs-keyword">Index</span> <span class="hljs-keyword">Scan</span> <span class="hljs-keyword">using</span> one_million_counter_idx <span class="hljs-keyword">on</span> one_million
    (<span class="hljs-keyword">cost</span>=<span class="hljs-number">0.00</span>.<span class="hljs-number">.32129</span><span class="hljs-number">.34</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">1000000</span> width=<span class="hljs-number">37</span>)
    (actual <span class="hljs-keyword">time</span>=<span class="hljs-number">0.011</span>.<span class="hljs-number">.694</span><span class="hljs-number">.466</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500001</span> loops=<span class="hljs-number">1</span>)
    -> <span class="hljs-keyword">Index</span> <span class="hljs-keyword">Scan</span> <span class="hljs-keyword">using</span> half_million_counter_idx <span class="hljs-keyword">on</span> half_million
    (<span class="hljs-keyword">cost</span>=<span class="hljs-number">0.00</span>.<span class="hljs-number">.14120</span><span class="hljs-number">.29</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> width=<span class="hljs-number">5</span>)
(actual <span class="hljs-keyword">time</span>=<span class="hljs-number">0.010</span>.<span class="hljs-number">.683</span><span class="hljs-number">.674</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> loops=<span class="hljs-number">1</span>)
Total runtime: <span class="hljs-number">3833.310</span> ms
(<span class="hljs-number">5</span> <span class="hljs-keyword">rows</span>)

通过创建索引，查询优化器已经决定了索引扫描时，如何查找 Merge join。

请注意，索引扫描和全表扫描（顺序扫描）之间的区别：后者（也称为“表扫描”）是通过扫描所有数据或索引所有页面来查找到适合的结果，而前者只扫描表中的每一行。

教程的第二部分内容，就介绍到这里。后续还会有《如何编写更好的SQL查询》系列的最后一篇文章，敬请期待。

转载请注明出自：葡萄城控件