PostgreSQL查询计划剖析
⽬录
介绍
了解PostgreSQL查询计划对于开发⼈员和数据库管理员来说都是⼀项关键技能。这可能是我们开始优化查询的第⼀件事，也是验证我们优化的查询是否确实按照我们期望的⽅式优化的第⼀件事。
PostgreSQL数据库中的查询⽣命周期
在我们尝试阅读查询计划之前，提出⼀些⾮常基本的问题很重要：
为什么我们甚⾄需要查询计划？
计划中具体体现了什么？
PostgreSQL不够智能，⽆法⾃动优化我的查询吗？我为什么要担⼼计划者？
计划器是我唯⼀需要看的东西吗？
每个查询都会经历不同的阶段，了解每个阶段对数据库的意义很重要。
第⼀阶段是通过（分别由Microsoft和Oracle创建的⽤于与数据库交互的API）或通过其他⽅式如（Postgres的终端前端）连接到数据库。
第⼆阶段是将查询转换为称为解析树的中间格式。讨论的内部超出了本⽂的范围，但您可以想象它就像SQL查询的编译形式。
第三阶段就是我们所说的重写系统/规则系统。它采⽤从第⼆阶段⽣成的解析树，并以计划器/优化器可以开始在其中⼯作的⽅式重写它。
第四阶段是最重要的阶段，也是数据库的核⼼。如果没有计划器，执⾏器就会对如何执⾏查询、使⽤什么索引、是否扫描较⼩的表以消除更多不必要的⾏等问题⼀⽆所知。这个阶段就是我们将在本⽂中讨论的。
第五个也是最后⼀个阶段是执⾏器，它执⾏实际执⾏并返回结果。⼏乎所有的数据库系统都遵循⼀个或多或少与上述类似的过程。
数据设置
让我们⽤假数据设置⼀些虚拟表来运⾏我们的实验。
create table fake_data(id serial, name text, sentence text, company text);
然后⽤数据填充这个表。我使⽤下⾯的Python脚本来⽣成随机⾏。
from faker import Faker
fake = Faker()
# Change this range to whatever value you like
MAX_RANGE = 1000
with open('data.csv', 'w') as f:
for i in range(0, MAX_RANGE):
name = fake.name().replace(",", "")
sentence = fake.sentence(
nb_words=16, variable_nb_words=True
).replace(",", "")
company = fakepany().replace(",", "")
content = "'" + name + "'" + "," + \
"'" + sentence + "'" + "," \
+ "'" + company + "'" + "\n"
f.write(content)
该脚本使⽤来⽣成假数据。它将在根级别⽣成⼀个csv⽂件，并且可以使⽤以下命令作为常规csv导⼊到PostgreSQL中：
COPY fake_data(name, sentence, company)
FROM '/path/to/csv' DELIMITER ','
由于id是串⾏的，它会由PostgreSQL本⾝⾃动填充。该表现在包含1119284记录。
SELECT COUNT(*) FROM fake_data;
下⾯的⼤多数⽰例将基于上表。有意保持简单，专注于过程⽽不是表/数据的复杂性。
安卓在线解析json
本⽂的特⾊图⽚来⾃。
进⼊计划阶段
PostgreSQL和许多其他数据库系统让⽤户可以在计划阶段看到实际发⽣的事情。我们可以通过运⾏所谓的EXPLAIN命令来做到这⼀点。
PostgreSQL解释⼀个查询
EXPLAIN SELECT * FROM fake_data LIMIT 10;
EXPLAIN查询输出显⽰为正常⾏。
通过使⽤EXPLAIN，您可以在数据库实际执⾏查询计划之前查看它们。我们将在下⾯的部分中了解每⼀个的部分，但让我们先看看另⼀
个EXPLAIN扩展版本，叫做EXPLAIN ANALYSE。
⼀起解释分析
EXPLAIN ANALYSE SELECT * FROM fake_data LIMIT 10;
将ANALYZE参数添加到查询会产⽣计时信息。
与EXPLAIN不同，EXPLAIN ANALYSE实际上在数据库中运⾏查询。这个选项对于了解计划器是否没有正确发挥其作⽤⾮常有帮助，即，从EXPLAIN和EXPLAIN ANALYSE⽣成的计划是否存在巨⼤差异。
PostgreSQL是兼容了ANAYLYZE和ANALYSE
什么是数据库中的缓冲区和缓存？
让我们继续讨论⼀个更有趣的指标，称为BUFFERS。这解释了有多少数据来⾃PostgreSQL缓存以及有多少必须从磁盘中获取。
EXPLAIN (ANALYSE,BUFFERS) SELECT * FROM fake_data LIMIT 10 OFFSET 200
包含BUFFERS作为参数显⽰查询正在创建的页⾯命中。
Buffers:sharedhit=5意味着从PostgreSQL缓存本⾝获取了五个。让我们调整查询以从不同的⾏偏移。
EXPLAIN (ANALYSE,BUFFERS) SELECT * FROM fake_data LIMIT 10 OFFSET 500
更改OFFSET会导致不同的页⾯点击次数。
Buffers:sharedhit=7read=5显⽰5页来⾃磁盘。该read部分是显⽰有多少页⾯来⾃磁盘的变量，正如已经解释过的hit来⾃缓存。如果我们再次执⾏相同的查询（记住ANALYSE运⾏查询），那么所有数据现在都来⾃缓存。
再次执⾏查询意味着缓存现在提供所有结果。
PostgreSQL使⽤⼀种称为LRU（最近最少使⽤）缓存的机制将经常使⽤的数据存储在内存中。了解缓存的⼯作原理及其重要性是另⼀篇⽂章的主题，但现在我们必须了解的是PostgreSQL具有坚如磐⽯的缓存机制，我们可以使⽤EXPLAIN(ANALYSE,BUFFERS)命令查看它是如何⼯作的。
VERBOSE命令参数
EXPLAIN (ANALYSE,BUFFERS,VERBOSE) SELECT * FROM fake_data LIMIT 10 OFFSET 500
Verbose是另⼀个提供额外信息的命令参数。
VERBOSE命令参数将为复杂查询提供更多信息。
请注意，Output:id,name,sentence,company是附加的。在复杂的查询计划中，将打印⼤量其他信息。默认情况下，设置
的COSTS和TIMING选项是TRUE，除⾮您想将它们设置为FALSE，否则⽆需明确指定它们。
Postgres中的FORMAT解释
PostgreSQL能够以⼀种很好的格式给出查询计划，例如JSON，这些计划可以以⼀种语⾔中⽴的⽅式进⾏解释。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。