The anatomy of an SQL query


Every query to the database goes through several important stages of processing. Let's break down this process using the example of a query:


Step 1: Transport Subsystem

When you execute a query, the query string first enters the database's transport subsystem. This subsystem is responsible for managing the connection with the client and performs the initial authentication and authorization checks. If everything is in order, the query is passed to the next stage.


Step 2: Query Handler

The query enters the query handler, which consists of two main components:

  • Query Parser: It breaks the SQL query into its components (SELECT, FROM, WHERE, etc.), checks for syntax errors, and creates a parse tree.
  • Query Optimizer: After the query is parsed, the optimizer checks for semantic errors (e.g., whether the "users" table exists). It also determines the most efficient way to execute the query. As a result of the optimizer’s work, an execution plan is created, which describes how the query will be executed.

 

Step 3: Execution Engine

The execution plan is passed to the execution engine. This component coordinates the execution of the query, using the plan created in the previous step. It calls the storage engine, executes the steps of the query, and collects the results to return them to the client.

 

Step 4: Storage Engine

The execution engine sends low-level read/write queries to the storage engine according to the execution plan. The storage engine consists of several subsystems:

  • Transaction Manager: Ensures that the query is executed within the context of a transaction to guarantee data consistency.
  • Lock Manager: Acquires locks on the "users" table to avoid conflicts with other queries.
  • Buffer Manager: Checks if the required data is already in memory. If not, it requests the data from the disk and loads it into memory.
  • Recovery Manager: Logs operations to a journal to allow for data rollback or recovery if needed.

Comments

Popular posts from this blog

PET-Projects for Analysts

Machine Learning in medicine.

Intro to my blog