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


Without further ado a simple SQL statement to fetch all users from a DB called users:


Disclaimer 2: the point is not to write fancy SQL statements but to showcase what is happening once you do


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?


基本原理 (Fundamentals)

In order to understand this one has to start at the beginning — PostgresDB’s architecture.


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:


Image for post
SlideShare SlideShare

查询的路径 (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


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?)

PGD​​B可以同时处理多个连接(通过“ postmaster”,请参见体系结构),并且它为每个连接派生一个新进程(“ postgres”,请参见体系结构)以处理该连接的请求(例如SQL语句)。 换句话说,这是一个简单的“每用户进程”客户端/服务器模型。 邮政管理员处理初始连接和身份验证,然后将该连接移交给新的postgres进程。 这些进程之间使用共享内存和信号灯相互通信,以确保即使使用并发连接也能确保整体数据完整性(还记得ACID吗?)

2. Parsing the query


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语法的固定规则来完成此操作,而无需了解查询字符串的基础语义。 输出是一个解析树:

Image for post
interdb.jp interdb.jp
  • 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:

    转换:使用解析器树来构造查询树,该查询树包含查询的语义解释,例如,引用了哪些表,数据类型,运算符和函数。 查询树的根是此处定义的查询数据结构。 输出:

Image for post
interdb.jp interdb.jp

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

  • rtable: holds the reference to the table

  • jointree: holds the FROM and WHERE operators

  • sortClause: holds how data should be sorted


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


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.

PGD​​B的重写系统使用查询树作为输入,并根据存储在其系统目录中的规则执行转换,这些规则可以应用于查询树。 输出再次是查询树。 一个很好的例子是视图(虚拟表)的实现,其中重写系统重写用户查询以访问视图定义(而不是视图别名)中的原始表。

A comprehensive example on how the rule system works for views can be found here.


4. The Query Plan


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否则在其中查看所有可能性在计算上是不可行的。 在这种情况下,将使用通用查询优化器

Image for post
interdb.jp interdb.jp

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.


You can check your query plan by typing EXPLAIN in front of your query:



5. The Executor


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


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加速,尤其是查询优化器和执行器,如下图所示:

Image for post
SlideShare SlideShare

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.


There is a great explanation of this topic here, so below is just a brief summary.

有这个主题的很好的解释在这里 ,所以下面只是一个简要介绍。

Image for post
severalnines.com manynines.com

There are 2 types of memory:


1. Local memory


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

  • work_mem: used by executor for joins etc.

  • maintenance_work_mem: maintenance ops like REINDEX


2. Shared memory


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 ++应用程序进行交互-这有多酷? 希望这可以消除“数据库很复杂”的一些污名。 也许它甚至会激发您进行更深入的研究,并了解如何通过更好地了解数据的存储方式来优化应用程序-到目前为止,这是现代软件工程中最大的瓶颈之一。



翻译自: https://towardsdatascience.com/whats-behind-a-simple-sql-query-c78e3ded8321

参与评论 您还未登录,请先 登录 后发表或查看评论


  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
©️2022 CSDN 皮肤主题:游动-白 设计师:我叫白小胖 返回首页
钱包余额 0