How MySQL user access works

Mastering the principles helps us to grasp this from the whole and helps us clear our mind in the process of troubleshooting. Then, starting with how MySQL works, let's start with a classic picture:

The above diagram is the MySQL internal architecture, you can clearly see that MySQL is composed of SQL interface, parser, optimizer, cache, storage engine.

Here is an introduction to the above parts:

1. Connectors

   Interact with SQL statements in other programming languages, such as PHP, Java, and so on.

2. Management Serveices & Utilities

   System Management and control tools

3. Connection Pool (connection pooling)

Manage buffered user connections, threading, and other needs for caching

4. SQL Interface (SQL interface)
   Accepts the user's SQL command and returns the result that the user needs to query. For example, select from is called SQL Interface

5. Parser (parser)
   When the SQL command is passed to the parser, it is validated and parsed by the parser.
   Main functions:
   A. The SQL statement is decomposed into a data structure, and the structure is passed to the next step, and the subsequent SQL statements are passed and processed based on this structure.
   B. If an error is encountered in the decomposition composition, then the SQL statement is unreasonable and the statement will not continue

6. Optimizer (query optimizer)
   The SQL statement uses the query optimizer to optimize the query before querying (resulting in a variety of execution plans, the final database chooses the most optimized scheme to execute and returns as soon as possible) he uses the Select-drop-join strategy for querying.
   As an example, you can understand: Select Uid,name from user where gender = 1;
   This select query is selected based on the where statement instead of querying all of the tables before gender filtering
   This select query first attributes the property based on the UID and name, instead of removing the attributes from the filter
   Join these two query conditions to generate the final query results.

7. Cache and buffer (query cache)
   If the query cache has hit query results, the query can go directly to the query cache to fetch the data.
   This caching mechanism is made up of a series of small caches. such as table cache, record cache, key cache, permission cache, etc.

8.Engine (Storage engine)

The storage engine is the specific subsystem in MySQL that deals with files. It is also one of MySQL's most distinctive places.

The MySQL storage engine is plug-in. It customizes a file access mechanism based on an abstract interface of the file access layer provided by MySQL AB (the access mechanism is called the storage engine)

SQL Statement Execution procedure

Databases are not typically used directly, but are called by other programming languages through SQL statements, processed by MySQL and returned to the execution results. So how does MySQL handle the SQL statement after it is accepted?

First, the request of the program interacts with the MySQL connectors, and after the request is over, it is temporarily stored in the connection pool (connection pool) and managed by the processor (Management serveices & Utilities). When the request enters the processing queue from the waiting queue, the manager drops the request to the SQL interface (SQL Interface). After the SQL interface receives the request, it hashes the request and compares it to the results in the cache, and returns the processing result directly from the cache if the exact match is made;

(1) The SQL interface is dropped to the back of the Interpreter (Parser), it has been said that the interpreter will determine whether the SQL statement is correct, if it is correct to convert it into a data structure.

(2) The interpreter finishes processing, then comes to the back of the optimizer (Optimizer), it will produce a variety of execution plans, the final database will choose the most optimized scheme to execute, as soon as possible to return results.

(3) After the optimal execution plan is determined, the SQL statement can then be processed by the storage engine and the storage engine will take the corresponding data to the back-end storage device and return the original path to the program.

Here are a few things to note:

(1) How to cache query data?
While the storage engine finishes processing the data and returns it to the program, it also retains a copy of the data in the cache for faster processing of the next same request. In particular, MySQL will hash the query statements, execution results, and so on, and remain in the cache for the next query.

(2) What is the difference between buffer and cache?

As you can see from the diagram above, there is actually buffer and cache two there, so is there any difference between them? In a nutshell, buffer is the write cache, and the cache is read.

(3) How to determine if the required data is cached in the cache

There may be a misunderstanding, think of the processing of SQL statements, in order to determine whether the query results are cached, the entire process will go through, get execution results and then compare with the need to see if the hit, and to say, since the cache is not cached to the query content, the entire process to go through, So what are the advantages of caching?

In fact, after the first query, MySQL will hash the query statement and query results and remain in the cache, after the SQL query arrives, after the same hash processing, the two hash value is compared, if the same, hit, return the query results from the cache; , the whole process needs to go through.

How MySQL user access works