To understand this problem, let’s start with the architecture of Mysql. For Mysql, it can be roughly divided into three layers.

The first layer is the connection between the client and the server, and the connector handles the connection to the client, as well as some authentication and so on. For example, the client universal username and password to connect to the Mysql server, and the execution permission for the database table.

The second layer is the core layer. Basically, most of the core functions of Mysql are located in this layer, including query cache, parser, optimizer, etc., such as SQL parsing, optimization, index selection, and finally generating execution plan.

The third layer is the storage engine. Mysql directly calls the storage engine API through the execution engine to query the data in the database.

Through the Mysql architecture layer, we can first clearly understand a SQL about the execution process.

  1. First the client sends a request to the server to establish a connection.
  2. The server checks whether the query cache matches and returns the query cache. Otherwise, the server continues.
  3. Then came to the parser, syntax analysis, some system keywords check, check whether the syntax is compliant.
  4. The optimizer then performs SQL optimizations, such as how to select indexes, and generates an execution plan.
  5. Finally, the execution engine calls the storage engine API to query the data and return the results.

This is a very general SQL execution process, next, in detail to describe each step.

The query cache

If you look at the official Mysql documentation, you will know that the query cache has been deprecated in version 5.7.20 and removed in version 8.0. Why did you delete it? Maybe it’s too chicken.

We can use the command to see if the query cache is available.

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
Copy the code

In addition, the query cache has some core parameters. For more detailed instructions, please refer to the official documentation.

Query_cache_type: The value is 0\1\2, which corresponds to OFF\ON\DEMAND. ON indicates that the query cache is enabled, but can be manually disabled by SELECT SQL_NO_CACHE. DEMAND indicates that only SQL statements starting with SELECT SQL_CACHE are cached.

Query_cache_limit: indicates the limit of the cache result size. If the query result exceeds the size, it will not be cached. The default size is 1 MB.

Query_cache_size: Specifies the memory allocated for the query cache. It is an integer multiple of 1024.

Query_cache_min_res_unit: queries the minimum unit of cache-allocated memory blocks. The default unit is 4KB. This is the basic unit of memory allocated by the query cache, even if only 1 byte of data is queried, the memory will be allocated according to the smallest memory cell size.

Before SQL parsing, the system determines whether the query cache is open. If so, it compares the cached query with the incoming query. If they are exactly the same, the query is returned directly from the cache.

However, it is important to note that all cases, Spaces, and comments affect the hit result of the cache, so it must be exactly the same!

For example, the following SQL will not hit the query cache if it has different case and more Spaces.

select * from user;
SELECT * from user;
select   * from user;
Copy the code

Parsers & preprocessors

If the query cache is not hit, normal SQL execution begins.

First of all, just like normal business development, the first step is to check the rules of the parameters. Mysql is the same, the parser will perform lexical parsing, based on the syntax rules to check the SQL.

For example, are the keywords used correctly, or are the keywords in the right order, for example, if you write select as selct, order by as order.

If the checksum is OK, a parse tree is generated.

The preprocessor then performs further checks against the parse tree generated by legitimate rules, such as table names, column names, and so on.

The optimizer

If the parser and preprocessor are the pre-validation of our business logic, the optimizer is where the real business logic is handled.

A query SQL can be executed in N ways, and the ultimate goal of the optimizer is to find the best execution plan and hand it over to the execution engine.

But in practice, we often find that Mysql often selects the wrong index. I have a faster index, but it does not use, resulting in a slow query.

This is because the Mysql optimizer is a cost model-based optimizer. It simply selects the lowest cost execution method based on the existing cost calculation formula. This execution method may not be the fastest, but most of the time, the optimizer’s choice is more accurate than our own choice.

In general, the optimization process is too complicated, and the process looks like the following. For more details, see the book “Database Query Optimizer art and SQL Performance” (I’m too tired to read it, throw up).

Execution engine

Most of the core work has been done by the optimizer, and the execution engine only needs to return the query data according to the generated execution plan, which is relatively easy.

The execution engine simply calls the storage engine API according to the execution plan instructions.

Of course, if this step can cache the query result, then at this stage to cache the query result, and then return the result to the client can be.

conclusion

A picture is worth a thousand words.