前言
接着电子书：《Troubleshooting PostgreSQL》。本文就涉及书中的如下内容：
高效正确读取数据 （对应第四章）
4、高效正确读取数据
读取数据不仅要高效，还要正确。这就是这一章的主指。
4.1 理解NULL值的威力
有些人可能想知道为什么在这本书中包含了关于NULL的一节。请记住，这都是关于故障排除的，相信我，如果不能正确理解NULL的缺陷，它可能是真正的麻烦来源。NULL实际上是一个相当古老的东西。E. F. Codd(关系数据库之父)在1975年的一篇论文中提到了NULL的概念。因此，NULL的思想是关系系统世界的核心。
然而，SQL开发人员并不总是广泛理解和接受NULL。许多人在面对NULL时都经历过麻烦。在本节中，我们将阐明这个神秘的东西并解决NULL之谜。首先也是最重要的是要知道NULL并不意味着空字符串;它的意思是未知。不知道不等于空。例如，如果你知道你的钱包是空的，那么这意味着你知道里面什么也没有。不知道钱包里有什么是完全不同的情况。记住这一点很重要。黄金法则是NULL意味着未定义。
4.1.1 看下NULL的行为
在这一点理论介绍之后，是时候深入到一些实际操作中，看看NULL的实际行为。第一个重要的观察是NULL与空字符串不同:
test=# SELECT NULL = '';
?column?
----------
(1 row)复制
许多人期望这个查询返回false。实际上，返回值是NULL。为什么? 想象你的左手什么也没拿。我们不知道你右手里是什么。两只手的东西是一样的吗? 我们不知道。第二只手也可能是空的，但我们不确定。因此，这个问题的答案是未知的。
要检查某个值是否为NULL，必须使用is NULL。在这种情况下，答案肯定是错误的:
test=# SELECT '' IS NULL;
?column?
----------
f
(1 row)复制
同样的逻辑适用于下边的示例：
test=# SELECT NULL = NULL;
?column?
----------
(1 row)
test=# SELECT NULL IS NULL;
?column?
----------
t
(1 row)复制
同样，第一个查询返回NULL，因为我们不知道两边是否相同。记住，你左手的未知项目可能与你右手的未知项目不一样。
4.1.2 NULL以及相关的存储
好消息是NULL不需要磁盘中的空间。在PostgreSQL中，每一行都包含一个NULL位图。它指示字段是否恰好为NULL。因此，单独的NULL值不需要磁盘中的任何额外空间。
对于空字符串，这并不完全正确。varchar字符串需要相当大的空间，即使它是空的，因为它是一个完全定义的值。
4.2 解决灾难性的JOIN问题
NULL并不是唯一需要灾难的东西。如果使用不当，连接也是一个很好的麻烦来源。如果您觉得自己已经了解了有关连接的所有内容，并且可以跳过本节，那么请尝试一下。事情可能不像预期的那么容易。
4.2.1 为JOIN创建示例数据
为了显示在连接期间可能出现的问题，最好的方法是创建一个简单的示例。在本例中，创建了两个表:
test=# CREATE TABLE a (aid int);
CREATE TABLE
test=# CREATE TABLE b (bid int);
CREATE TABLE复制
添加一些行：
test=# INSERT INTO a VALUES (1), (2), (3);
INSERT 0 3
test=# INSERT INTO b VALUES (2), (3), (4);
INSERT 0 3复制
看看一下普通的JOIN：
test=# SELECT * FROM a, b WHERE aid = bid;
aid | bid
-----+-----
2 | 2
3 | 3
(2 rows)复制
4.2.2 理解外连接
在讨论连接和一般问题时，简单的内连接并不是我们最感兴趣的。更多的麻烦来自于所谓的外部连接。
让我们查找左侧的所有值，并匹配右侧的值:
test=# SELECT *
FROM a LEFT JOIN b
ON (aid = bid);
aid | bid
-----+-----
1 |
2 | 2
3 | 3
(3 rows)复制
查询非常简单，实际上也没有风险，但是如果在ON子句中添加and子句会发生什么呢?许多人倾向于这样做来过滤数据并减少结果:
test=# SELECT *
FROM a LEFT JOIN b
ON (aid = bid AND bid = 2);
aid | bid
-----+-----
1 |
2 | 2
3 |
(3 rows)复制
大多数人都没有预料到这个结果。请记住，ON子句在这里告诉PostgreSQL要加入什么。在LEFT JOIN关键字中，左侧总是被完全占用。ON子句仅指示适合左侧的内容。这里可怕的部分是，查询返回的行数根本没有减少。必须是这样。
有人可能会说这是一个很容易解决的问题。为什么不直接将过滤器移动到WHERE子句，如下所示?
test=# SELECT *
FROM a LEFT JOIN b
ON (aid = bid)
WHERE bid = 2;
aid | bid
-----+-----
2 | 2
(1 row)复制
现在只返回一行。那么在这里使用LEFT JOIN关键字有什么意义呢?在这种情况下，外部连接是完全无用的，因为过滤器会删除由外部连接创建的所有行。
外连接的问题在于，错误的外连接会影响到我们的支持部门，并被很好地掩盖为性能问题。原因如下:
test=# SELECT avg(aid), avg(bid)
FROM a LEFT JOIN b
ON (aid = bid AND bid = 2);
avg | avg
--------------------+--------------------
2.0000000000000000 | 2.0000000000000000
(1 row)复制
结果乍一看是正确的。然而，客户期望bid = 2实际上减少了处理的数据量。人们想知道为什么不使用索引，并抱怨糟糕的性能。实际上，这不是性能问题，而是逻辑问题，因为查询很可能不是开发人员最初想要的。
外连接的第二个问题与NULL值有关:
test=# SELECT count(aid), count(bid), count(*)
FROM a LEFT JOIN b
ON (aid = bid AND bid = 2);
count | count | count
-------+-------+-------
3 | 1 | 3
(1 row)复制
count(bid)函数返回1，而count()函数返回3。请记住，count()将对行进行计数，而不考虑其内容。但是，count(column)将计算所有碰巧不是NULL的值。因为只有一个数字，所以正确答案是1。如果不明智地使用count(*)函数和外部连接，可能会导致致命的混合物——随时会爆炸!
在编写聚合之前，总是尝试先修复外部连接，然后再三思。在哪里应该预期NULL值?什么样的计数是真正需要的?ON子句中的AND操作符真的在做您认为它正在做的事情吗?当ON子句中有AND时，要注意并检查两次。这同样适用于count(*)和count(column)。这些方面的错误可能会以一种残酷的方式影响您。现在让我们把注意力集中在FULL JOIN上:
test=# SELECT *
FROM a FULL JOIN b
ON (aid = bid);
aid | bid
-----+-----
1 |
2 | 2
3 | 3
| 4
(4 rows复制
FULL JOIN关键字的思想是查找左侧的所有内容，并将其与右侧的所有内容进行匹配。结果将显示每条边的所有值。
在你学了这么多之后，你还期待下面的结果吗?
test=# SELECT * FROM a FULL JOIN b
ON (aid = bid AND aid = 2);
aid | bid
-----+-----
1 |
2 | 2
3 |
| 4
| 3
(5 rows)复制
FULL JOIN关键字的思想是查找左侧的所有内容，并将其与右侧的所有内容进行匹配。结果将显示每条边的所有值。同样，AND子句只会告诉系统什么是一起的;它不会减少数据——恰恰相反。在本例中，返回的数据量甚至增加了一行。
4.3 读取大批量数据
一些应用程序需要数据库读取大量数据。通常，可以区分两种主要情况:
读取大量数据并将其发送给客户端
读取大量数据，以便服务器能够处理它们
在本节中，将讨论这两种情况，因为它们的需求略有不同。
4.3.1使用游标
游标是所有合理的关系数据库都提供的基本概念。然而，游标经常被忽略，并且没有按其应有的方式使用，这导致了一直存在的问题。为什么会有人想要使用光标? 考虑下面的例子:
SELECT * FROM very_large_table;
对于PostgreSQL来说，这根本不是问题。它将计算结果并将其发送到客户机服务器。但是，客户机可能会立即崩溃，因为它会尝试将所有数据存储在内存中。如果表非常大，客户机可能无法处理来自服务器的所有数据。客户端将消耗大量内存，甚至死亡。最大的问题是，您的客户端是否能够生存将取决于表中的数据量——这无疑会导致灾难，而且很难识别。
一般来说，您的应用程序应该设计成这样一种方式:无论数据量多少，无论从服务器返回的结果集如何，它们都将始终工作。游标就是用来解决这个问题的。游标背后的思想是允许客户端在特定时间只保留所需的数据。
使用游标，大都要用到DECLARE命令：
test=# \h DECLARE
Command: DECLARE
Description: define a cursor
Syntax:
DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query复制
在我们的示例中，我们的目标是读取10行，而不必一次将所有10行保存在内存中。可以轻松创建演示数据:
test=# CREATE TABLE t_test AS
SELECT * FROM generate_series(1, 10) AS x;
SELECT 10复制
在下一步中，应用程序应该读取数据。这里要做的最重要的事情是将操作包装在事务中(在本书中，事务也将在后面的章节中详细介绍)。然后可以很容易地定义游标：
test=# BEGIN;
BEGIN
test=# DECLARE mycur CURSOR FOR SELECT * FROM t_test;
DECLARE CURSOR复制
注意，此时创建游标并不执行任何操作。这仅仅意味着PostgreSQL现在准备发送大量数据给你。真正的工作在调用FETCH时就完成了:
test=# FETCH NEXT FROM mycur;
x
---
1
(1 row)复制
FETCH NEXT将从结果集中产生一行。不可能对结果中的每一行调用FETCH NEXT。FETCH NEXT的问题是它非常非常慢。如果您逐个获取100万行，这意味着大量的网络开销。因此，强烈建议在较大的桶中获取数据。一般来说，几百到几千行的存储桶是非常合理的，因为内存消耗和网络开销将达到完美的平衡。但是，不要把这当作固定的指导方针。这取决于您的应用程序类型、数据、网络等。实验是有意义的。
下面是一次获取多个行的方法:
test=# FETCH 3 FROM mycur;
x
---
2
3
4
(3 rows)复制
现在可以根据需要循环遍历整个结果。
要完成操作，提交事务就足够了。所有游标将自动关闭:
test=# COMMIT;
COMMIT复制
重要的是要注意，在COMMIT语句之后，游标不再存在:
test=# FETCH NEXT FROM mycur;
ERROR: cursor "mycur" does not exist复制
默认情况下，游标仅在事务中可见，因此，与游标相关的非常长的操作也可能导致复制冲突等问题。如果您确实希望游标在事务中存活，则必须创建一个所谓的WITH HOLD游标。然而，在这种情况下，您必须通过调用CLOSE来再次清理游标:
test=# \h CLOSE
Command: CLOSE
Description: close a cursor
Syntax:
CLOSE { name | ALL }复制
WITH - HOLD游标应该小心处理。最重要的观察是，WITH HOLD游标必须实现其结果，这可能对数据库服务器上所需的存储量有很大影响。
4.3.2 同步扫描
有时，读取不是将数据发送到客户机，而是处理服务器端数据。这正是同步序列扫描发挥作用的时候。
让我们想象一下以下场景:一个10tb的表包含一些旧的报告数据。它被存储在一个包含几个传统机械磁盘的服务器上。让我们考虑下面的查询:
SELECT error_type, count(*) FROM big_table GROUP BY 1;
假设error_type只有两个不同的值，PostgreSQL将转向seq扫描并处理查询。I/O系统可能以每秒500mb的速率传送数据。所有的磁盘都会很好地旋转。
现在让我们假设这里显示的查询已经处理了2tb的数据。然后有人出现并发出第二个类似的查询。即使数据已经被前一个作业读取，它现在肯定已经从缓存中掉出来了。因此，它必须再读一次。但情况更糟;那些旋转的磁盘将开始在第一个和第二个查询之间跳跃。添加的查询越多，磁盘行为就越随机。在大约10次查询之后，总吞吐量可能下降到大约每秒60 MB，这意味着每个数据库连接6 MB。显然，这不是理想的性能级别，是不可接受的。
同步序列扫描来拯救。让我们假设第一个查询已经以每秒500 MB的全速处理了2 TB的表。当第二个(类似的)查询在同一个表上启动时，PostgreSQL将使它开始读取大约2tb的数据。第二个查询将从那里继续。同步这些扫描的好处是，一个扫描可以从另一个扫描生成的缓存项中获益。在良好的条件下，一次从磁盘读取可以为两个(甚至更多)扫描提供数据。因此，性能不是每秒500 MB除以2减去每次扫描额外的随机访问，而是理想情况下每秒500 MB乘以扫描次数。并行运行扫描可以通过大幅减少I/O瓶颈并将其慢慢转变为CPU瓶颈来显著提高速度，这更容易扩展。
重要的是，同步顺序扫描在默认情况下是开启的，一般来说，确保这个设置真的被设置为开启是一个好主意:
test=# SHOW synchronize_seqscans ;
synchronize_seqscans
----------------------
on
(1 row)复制
您可以在默认配置中充分利用它们的潜力。
在许多情况下，并行执行比按顺序执行更有意义，因为许多分析作业可能共享相同的I/O。
4.4 理解Prepared查询
本节主要讨论准备好的查询。什么是准备好的查询?考虑下面的例子:
SELECT * FROM website WHERE domain = 'www.cybertec.at';
PostgreSQL将解析查询以检查语法错误等等。然后，它将通过重写系统，并击中PostgreSQL优化器，然后得出一个好的计划。最后，执行器将执行查询规划器选择的所有步骤。如果对不同的域反复执行这种类型的查询，那么解析和规划过程也必须反复运行，这非常耗时。
准备好的查询试图解决这个问题。其思想是后端进程将缓存计划并在再次需要时重用它。美妙之处在于，发送参数而不是完整的查询就足够了。通常，准备好的语句在需要的时候在后台发生(如果驱动程序为此做好了准备，则通常通过驱动程序完成)。然而，你也可以手动准备查询:
test=# \h PREPARE
Command: PREPARE
Description: prepare a statement for execution
Syntax:
PREPARE name [ ( data_type [, ...] ) ] AS statement复制
如前所述，类似的事情由驱动程序完成。但是，它也可以手动工作:
test=# PREPARE myplan(int) AS SELECT $1;
PREPARE
test=# EXECUTE myplan(10);
?column?
----------
10
(1 row)复制
在本例中，创建了一个名为myplan的计划，它接受一个参数。传递给准备好的查询的参数是诸如1、2、$3等数字。要运行一个计划，这里使用EXECUTE。
当然，有一些协议级别的支持来优化这个过程，强烈建议利用这个功能。
然而，在某些情况下，可能会出现问题。如果客户端应用程序创建了数千甚至数百万个准备好的计划，则后端内存消耗可能会增加。要弄清楚你的活动数据库连接是否创建了太多的准备语句，可以参考系统视图:
test=# \d pg_prepared_statements
View "pg_catalog.pg_prepared_statements"
Column | Type | Modifiers
-----------------+--------------------------+-----------
name | text |
statement | text |
prepare_time | timestamp with time zone |
parameter_types | regtype[] |
from_sql | boolean |复制
pg_prepared_statements视图告诉我们在数据库连接中准备了哪些语句。
如果你想从后台删除一条语句，你可以使用DEALLOCATE命令:
test=# \h DEALLOCATE
Command: DEALLOCATE
Description: deallocate a prepared statement
Syntax:
DEALLOCATE [ PREPARE ] { name | ALL }复制
另一个要点是，准备好的查询不仅与速度有关，还与SQL注入有关。准备好的查询是完全避免安全漏洞(SQL注入)的好方法。
参考：
《Troubleshooting PostgreSQL》第四章
