Analysis of MySQL BLACKHOLE - MySQL architecture design

Most developers use MySQL as a "black hole".

blackhole.png

**Why is MySQL called“BLACKHOLE”?

Most developers use MySQL as a "black hole". They connect and operate the database by writing SQL statements for adding, deleting and modifying and querying. Most of them don't care about how the database listens to requests and extracts the request data from the connection, they tend to care about the table structure. If the SQL execution efficiency is slow, they build indexes for them.

Network connections must be handled by threads

MySQL uses internal threads to implement monitoring and read requests.

SQL API: responsible for processing the received SQL statements

MySQL simplifies the SQL statements we usually write through SQL API, allowing us to easily learn and write SQL statements, but its underlying implementation is actually very complicated. When the worker thread receives the SQL statement, it will be handed over to the SQL API for execution.

Query parser: Let MySQL understand SQL statements

MySQL is a data management system and it cannot directly read SQL statements like we do.

For instance, now we have such a SQL statement:

1. select id, name, age from users where id =1

MySQL will need query parser to parse the SQL statement, disassembling it into the following parts:

from users: query data from the users table

where id = 1 :query the row of data whose id field value is 1

select id, name, age : extract the three fields of "id, name, age" from the row of data found

Query optimizer: select the optimal query path

The query optimizer will generate a query path tree based on a SQL statement, and then select an optimal query path from it.

The query execution engine executes SQL statements by making calls to storage engine API

The data stored in the database may be stored on disk or in memory. How to tell where the query data is stored? The storage engine executes SQL logical plan according to the schedule of the executor. The storage engine actually executes SQL statements. It will follow certain steps to query memory cache data, update disk data, etc.

Executor: making calls to storage engine API according to execution plan

The executor will make calls to the storage engine API to execute SQL statements in a certain sequence and steps according to the execution plan selected by the optimizer.

Summary:

In the MySQL architecture design, the SQL API, SQL parser, query optimizer, and executor are actually a set of common components. Yet, it supports a variety of storage engines, such as InnoDB, MyISAM, Memory, etc. We can choose which storage engine to use to execute specific SQL statements.

To sum up, the sequence of executing SQL statements in MySQL is: SQL API -> parser: parse SQL -> optimizer: generate execution plan -> executor: execution plan to make calls to the InnoDB storage engine API to execute SQL.

**