Why is MySQL called a black box?

For most developers, add or delete check is written in the SQL statement by the database connection to database operation, but does not care about the database is how to monitor the request and the request data extracted from the connection, often in the expectation structure, slow SQL execution efficiency index to them, completely MySQL to use as a black box.

1. Network connections must be processed using threads

MySQL uses internal threads for listening and reading requests.

2. SQL interface: processes the received SQL statements

MySQL simplifies the SQL statements we usually write through the SQL interface, so that we can easily learn and write SQL statements, but its underlying implementation is actually very complex. When a worker thread receives an SQL statement, it gives it to the SQL interface to execute.

3. Query interpreter: enable MySQL to understand SQL statements

MySQL is a data management system and cannot read SQL statements as we do. For example:

select id, name, age from users where id = 1
Copy the code

You need to use the component query parser to parse and disassemble SQL statements into the following parts:

  1. From users: We need to query data from the Users table
  2. Where id = 1: Query the row where ID = 1
  3. Select id,name,age from row where id,name,age is selected

4. Query optimizer: Select the optimal query path

The query optimizer generates a query path tree from the SQL and selects an optimal query path from it.

5. Invoke the storage engine interface to execute the SQL statement

The data stored in the database may be stored on disk or in memory. So how do you determine where the query data is stored? The storage engine performs SQL logic according to the scheduling of the executor, whether it is querying data from the memory cache, updating data from disk, a series of operations are all performed by the storage engine.

6. Executor: Schedules storage engines based on execution plans

The executor calls various interfaces of the storage engine to execute SQL statements according to the optimizer’s execution plan.

Conclusion:

In MySQL architecture design, SQL interface, SQL parser, query optimizer, and executor are all a set of common components, but storage engines have different choices, such as: InnoDB, MyISAM, Memory, etc., correspond to different application scenarios. MySQL is InnoDB by default, which will be analyzed step by step later. SQL interface -> Parser: interpret SQL -> Optimizer: generate execution plan -> Executor: execute plan to call InnoDB storage engine interface to execute SQL