Just like most kids these days do not seem to know where their meat comes from (hint: not growing on trees but could be from a lab) or why the sky is blue (hint: blue light has shorter, smaller waves), an alarming amount of developers do not (really) know where their data comes from and how it is processed & stored. This article should be a starting point into an engineer’s journey in understanding how that works. That is a very good thing regardless of what framework and no-code platform marketers are trying to teach you. Especially if data really is the new oil (apparently it has been for some years now), it might pay off to see how databases work.
就像这几天的大多数孩子一样，他们似乎不知道他们的肉来自哪里(提示：不是在树上生长，而是可能来自实验室)或为什么天空是蓝色的(提示：蓝光发出的是更短，更小的波浪)，这令人震惊大量的开发人员(真的)不知道他们的数据来自何处以及如何处理和存储数据。 本文应该是工程师了解其工作原理的起点。 无论营销人员试图教给您什么框架和无代码平台，这都是一件好事。 尤其是如果数据确实是新的石油(显然已经有几年了)，那么看看数据库如何工作可能会有所回报。
Disclaimer: this is not a SQL tutorial
免责声明：这不是SQL教程
Without further ado a simple SQL statement to fetch all users from a DB called users:
事不宜迟，一个简单SQL语句即可从称为用户的数据库中获取所有用户：
Disclaimer 2: the point is not to write fancy SQL statements but to showcase what is happening once you do
免责声明2：重点不是写奇特SQL语句而是要展示一旦发生的情况
SELECT * FROM users;So, how many of you actually know what is happening between pressing enter in your psql terminal and getting back that result?
那么，实际上有多少人知道在psql终端中按Enter键并获得该结果之间发生了什么？
基本原理 (Fundamentals)
In order to understand this one has to start at the beginning — PostgresDB’s architecture.
为了理解这一点，必须从一开始就开始-PostgresDB的体系结构。
At the end of the day it is a client/server application written in C++. A client is anyone who accesses the DB e.g. your psql terminal, your Lambda function, JDBC driver etc. and the server is the PGDB backend, which accepts your requests, stores the data etc.
归根结底，它是一个用C ++编写的客户端/服务器应用程序。 客户端是访问数据库的任何人，例如您的psql终端，Lambda函数，JDBC驱动程序等，而服务器是PGDB后端，它接受您的请求，存储数据等。
Here is a basic architecture overview of PostgresDB that will be zoomed into throughout the article:
这是PostgresDB的基本体系结构概述，本文将对其进行放大：
查询的路径 (The Path of a Query)
There is a pretty good overview of the “path of a query” in the depths of the official PostgresDB documentation. Here is what you should know:
在正式的PostgresDB文档的深度中，对“查询的路径”有一个很好的概述。 这是您应该知道的：
1. Establish connection, transmit query & wait for results
1.建立连接，传输查询并等待结果
PGDB can handle multiple connections concurrently (through the “postmaster”, see architecture) and for each connection it forks a new process (“postgres”, see architecture) to handle the requests (e.g. SQL statements) of that connection. In other words it is a simple “process per user” client/server model. The postmaster handles the initial connection and authentication and then hands that connection over to a new postgres process. Those processes communicate between each other with a shared memory and semaphores to ensure the overall data integrity even with concurrent connections (remember ACID?)
PGDB可以同时处理多个连接(通过“ postmaster”，请参见体系结构)，并且它为每个连接派生一个新进程(“ postgres”，请参见体系结构)以处理该连接的请求(例如SQL语句)。 换句话说，这是一个简单的“每用户进程”客户端/服务器模型。 邮政管理员处理初始连接和身份验证，然后将该连接移交给新的postgres进程。 这些进程之间使用共享内存和信号灯相互通信，以确保即使使用并发连接也能确保整体数据完整性(还记得ACID吗？)
2. Parsing the query
2.解析查询
This consists of 2 stages:
这包括两个阶段：
Parse: the PGDB parser utilises the UNIX tools bison and flex as parser and lexer in order to validate if an incoming query string (ASCII text) is a valid SQL query. This is done only with fixed rules about the syntax of SQL without any understanding of the underlying semantics of the query string. The output is a parse tree:
解析：PGDB解析器利用UNIX工具bison和flex作为解析器和词法分析器 ，以验证传入的查询字符串(ASCII文本)是否为有效SQL查询。 仅使用关于SQL语法的固定规则来完成此操作，而无需了解查询字符串的基础语义。 输出是一个解析树：
Transform: uses the parser tree to construct a query tree, which contains the semantic interpretation of the query e.g. which tables, data types, operators and functions are referenced. The root of the query trees is the Query data structure defined here. The output:
转换：使用解析器树来构造查询树，该查询树包含查询的语义解释，例如，引用了哪些表，数据类型，运算符和函数。 查询树的根是此处定义的查询数据结构。 输出：
Mini deep-dive into the Query:
迷你深入查询：
- targetList: holds the output of the query and its datatype in this case the columns id and data, which are both integers targetList：保存查询的输出及其数据类型，在这种情况下，列id和data均为整数
- rtable: holds the reference to the table rtable：保存对表的引用
- jointree: holds the FROM and WHERE operators jointree：拥有FROM和WHERE运算符
- sortClause: holds how data should be sorted sortClause：保存数据应该如何排序
An important note straight from the official docs:
直接来自官方文档的重要说明：
The reason for separating raw parsing from semantic analysis is that system catalog lookups can only be done within a transaction, and we do not wish to start a transaction immediately upon receiving a query string. The raw parsing stage is sufficient to identify the transaction control commands (BEGIN, ROLLBACK, etc), and these can then be correctly executed without any further analysis. Once we know that we are dealing with an actual query (such as SELECT or UPDATE), it is okay to start a transaction if we’re not already in one. Only then can the transformation process be invoked.
将原始解析与语义分析分开的原因是，系统目录查找只能在事务内完成，并且我们不希望在收到查询字符串后立即启动事务。 原始解析阶段足以识别事务控制命令(BEGIN，ROLLBACK等)，然后可以正确执行这些命令而无需任何进一步的分析。 一旦知道要处理的是实际查询(例如SELECT或UPDATE)，就可以开始事务(如果我们还不在其中)。 只有这样，才能调用转换过程。
3. The Rewrite
3.重写
PGDB’s rewrite system uses the query tree as input and performs transformations based on rules stored in its system catalogs that can be applied to the query tree. The output is again a query tree. A good example is the realization of views (virtual tables) where the rewrite system rewrites the user query to access the original tables in the view’s definition instead of the view’s alias.
PGDB的重写系统使用查询树作为输入，并根据存储在其系统目录中的规则执行转换，这些规则可以应用于查询树。 输出再次是查询树。 一个很好的例子是视图(虚拟表)的实现，其中重写系统重写用户查询以访问视图定义(而不是视图别名)中的原始表。
A comprehensive example on how the rule system works for views can be found here.
在这里可以找到有关规则系统如何为视图工作的综合示例。
4. The Query Plan
4.查询计划
The planner/optimizer uses the rewritten query tree from the last step to create the optimal/cheapest (=fastest/most efficient) execution plan for the query — the query plan. The optimizer looks at all possible paths to execute the query. Unless the statement has a large number of joins exceeding the geqo_treshold where looking at all possibilities is not computationally feasible. In that case a Generic Query Optimizer is used instead.
计划者/优化器使用最后一步中重写的查询树为查询创建最佳/最便宜(=最快/最高效)执行计划-查询计划。 优化器查看所有可能的路径以执行查询。 除非该语句具有大量的联接，超过了geqo_treshold ， 否则在其中查看所有可能性在计算上是不可行的。 在这种情况下，将使用通用查询优化器 。
The example in the diagram above shows a simple example where the executor has to do a sequential scan of the table tbl_a followed by a sort.
上图中的示例显示了一个简单的示例，其中执行者必须先对表tbl_a进行顺序扫描，然后再进行排序。
You can check your query plan by typing EXPLAIN in front of your query:
您可以通过在查询前面输入EXPLAIN来检查查询计划：
EXPLAIN SELECT (...)5. The Executor
5.执行人
The official documentation explains it as clearly as one could:
官方文档尽可能清楚地说明了这一点：
The executor recursively steps through the plan tree and retrieves rows in the way represented by the plan. The executor makes use of the storage system while scanning relations, performs sorts and joins, evaluates qualifications and finally hands back the rows derived.
执行程序以递归方式遍历计划树并以计划表示的方式检索行。 执行程序在扫描关系，执行排序和联接，评估资格并最终交回派生的行时利用存储系统。
The executor is used to evaluate all 4 basic SQL query types SELECT, INSERT, DELETE and UPDATE. You can find more details on the executor’s steps for each query type here.
执行程序用于评估所有4种基本SQL查询类型SELECT，INSERT，DELETE和UPDATE。 您可以在此处找到有关每种查询类型的执行程序步骤的更多详细信息。
Bonus: GPU Acceleration
奖励：GPU加速
There are actually tools out there like PG Strom for GPU acceleration especially for the query optimizer and executor as shown in this diagram:
实际有诸如PG Strom之类的工具可用于GPU加速，尤其是查询优化器和执行器，如下图所示：
This is not something that was tested for the article but it might inspire you to check out what is out there as this seems to be a pretty cool technology.
这不是本文测试过的内容，但它可能会激发您检查其中的内容，因为这似乎是一项非常酷的技术。
记忆 (Memory)
OK. Now you know (1) what happens when you connect to a PGDB instance and (2) how SQL queries are parsed, optimized and executed. The only thing that is missing is how the data is stored to cover the (very) basics of how modern* databases work.
好。 现在您知道了(1)连接到PGDB实例时会发生什么，以及(2)如何解析，优化和执行SQL查询。 唯一缺少的是如何存储数据以涵盖现代*数据库工作原理的(非常)基础。
*You can take “modern” with a grain of salt given how old PostgresDB is but it is still very widely used and it is one of the main relational databases in use today.
*考虑到PostgresDB的使用年代，您可以不花一分钱就可以“现代”使用，但它仍被广泛使用，并且是当今使用的主要关系数据库之一。
There is a great explanation of this topic here, so below is just a brief summary.
有这个主题的很好的解释在这里 ，所以下面只是一个简要介绍。
There are 2 types of memory:
有两种类型的内存：
1. Local memory
1.本地内存
It is used by each postgres backend process. This memory is allocated by each process for query processing and consists of:
每个postgres后端进程都使用它。 该内存由每个进程分配用于查询处理，并且由以下部分组成：
- temp_buffers: storing temp tables by executor temp_buffers：由执行程序存储临时表
- work_mem: used by executor for joins etc. work_mem：由执行者用于联接等。
- maintenance_work_mem: maintenance ops like REINDEX maintenance_work_mem：维护操作如REINDEX
2. Shared memory
2.共享内存
It is allocated when the PGDB server starts and is used by all backend processes to ensure data integrity (remember the beginning of the article?) This is also the memory that the backend process interfaces with — it does not normally interface directly with the persistent storage. This shared memory consists of:
它是在PGDB服务器启动时分配的，并且所有后端进程都使用它来确保数据完整性(还记得本文的开头吗？)这也是后端进程与之交互的内存—通常它不直接与持久性存储接口。 该共享内存包括：
- Shared buffer pool: where pages within tables and indexes are loaded into 共享缓冲池：将表和索引中的页面加载到的位置
- WAL buffer: Postgres has a write ahead log i.e. a transactional log making sure no data is lost due to server failure. WAL data is stored in the WAL buffer before being transferred to persistent storage. WAL缓冲区：Postgres具有预写日志，即事务日志，以确保不会由于服务器故障而丢失任何数据。 WAL数据在传输到持久存储之前先存储在WAL缓冲区中。
- Commit log: holds all transactional states as a part of the concurrency control mechanism 提交日志：将所有事务状态保存为并发控制机制的一部分
You can tune these values and how much memory is allocated to them to increase the performance of your database. Even more information on the utilities for each part of the memory can be found here.
您可以调整这些值以及为它们分配多少内存以提高数据库的性能。 可以在这里找到有关存储器各部分实用程序的更多信息。
放在一起/持久存储如何？ (Bringing it together/What about persistent storage?)
What happens in the end? How is data being persisted? The executor can read/write from/to tables and indexes in the database through the buffer manager, which can access data from the persistent storage by adding it to the shared buffer pool.
到底会发生什么？ 数据如何持久保存？ 执行程序可以通过缓冲区管理器从数据库中的表和索引读取/写入数据库，该管理器可以通过将持久性存储添加到共享缓冲池中来访问持久性存储中的数据。
摘要 (Summary)
By now you should have a basic understanding of the inner-workings of one of the most popular databases! At the end of the day you are just interfacing with a very smart C++ application — how cool is that? Hopefully this removes some of the stigma away from the “databases are complicated”. Maybe it will even inspire you to dig deeper and understand how you can optimize your applications by understanding better how your data is stored — by far and large one of the biggest bottlenecks in modern software engineering.
到目前为止，您应该对最流行的数据库之一的内部工作有基本的了解！ 归根结底，您只是在与一个非常智能的C ++应用程序进行交互-这有多酷？ 希望这可以消除“数据库很复杂”的一些污名。 也许它甚至会激发您进行更深入的研究，并了解如何通过更好地了解数据的存储方式来优化应用程序-到目前为止，这是现代软件工程中最大的瓶颈之一。
Sources:
资料来源：
翻译自: https://towardsdatascience.com/whats-behind-a-simple-sql-query-c78e3ded8321
扫一扫
1429
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
