Analysis of mysql execution process

MySQL can be divided into two parts: Server layer and storage engine layer

The Server layer includes connectors and query cache , analyzers, optimizers, executors, etc., covering most of MySQL's core service functions and all built-in functions. All cross-storage engine functions are implemented in this layer, such as stored procedures, triggers, views, etc.

The storage engine layer is responsible for data storage and retrieval. Its architectural model is plug-in and supports multiple storage engines such as InnoDB, MyISAM, and Memory. The most commonly used storage engine now is InnoDB

(free learning video tutorial recommendation: mysql video tutorial)

Let’s take a look at the sql execution process

Connector

In the first step, you will connect to the database first, and the connector will receive you at this time. The connector is responsible for establishing a connection with the client, obtaining permissions, maintaining and managing the connection

After the connection is completed, if you have no subsequent actions, the connection will be in an idle state. You can see it in the show processlist command.

If the client is inactive for too long, the connector will automatically disconnect it. This time is controlled by the parameter wait_timeout. The default value is 8 hours.

The process of establishing a connection is usually complicated, so the actions of establishing a connection should be minimized during use, that is, try to use long connections

But after all long connections are used, you may find that sometimes the memory occupied by MySQL increases very quickly. This is because the memory temporarily used by MySQL during execution is managed in the connection object. These resources will be released only when the connection is disconnected

How to solve this problem? You can consider the following two options.

1. Disconnect long connections regularly. After using it for a period of time, or after the program determines that a large query that takes up memory has been executed, the connection is disconnected, and then the query is required and then reconnected.

2. If you are using MySQL 5.7 or newer, you can reinitialize the connection resource by executing mysql_reset_connection each time after performing a relatively large operation. This process does not require reconnection and permission verification, but the connection will be restored to the state when it was just created

Query cache

After the connection is established, you You can execute the select statement. The execution logic will come to the second step: query cache. After MySQL gets a query request, it will first go to the query cache to see if this statement has been executed before. Previously executed statements and their results may be cached directly in memory in the form of key-value pairs. The key is the query statement, and the value is the result of the query. If your query can find the key directly in this cache, then the value will be returned directly to the client

If the statement is not in the query cache, it will continue with the subsequent execution stages. After execution is completed, the execution results will be stored in the query cache. You can see that if the query hits the cache, MySQL can directly return the result without performing subsequent complex operations. This efficiency will be very high

But in most cases do not use the query cache. Why? Because? Query caching often does more harm than good.

The query cache fails very frequently. As long as there is an update to a table, all query caches on this table will be cleared. So it's possible that you took the trouble to save the results, and before you even used them, they were wiped out by an update. For databases with heavy update pressure, the hit rate of the query cache will be very low. Unless your business has a static table that will only be updated for a long time

You can set the parameter query_cache_type to DEMAND, so that the query cache is not used for the default SQL statements

MySQL version 8.0 directly deletes the entire query cache function, which means that this function is completely gone from 8.0

Analyzer

If the query cache is not hit, the actual execution of the statement begins. First of all, MySQL needs to know what you want to do, so it needs to parse the SQL statement

The analyzer will first do "lexical analysis". What you input is an SQL statement composed of multiple strings and spaces. MySQL needs to identify what the strings in it are and what they represent.

After completing these identifications, it needs to do "syntactic analysis" . According to the results of lexical analysis, the syntax analyzer will determine whether the SQL statement you entered satisfies the MySQL syntax

Optimizer

After passing the analyzer, MySQL knows what you want to do. Before execution begins, it must be processed by the optimizer.

The optimizer determines which index to use when there are multiple indexes in the table; or when a statement has multiple table associations (joins), it determines the connection order of each table

After the optimizer phase is completed, the execution plan of this statement is determined, and then enters the executor phase

executor

When starting execution, you must first determine whether you have permission to execute queries on this table T. If not, an error of no permission will be returned.

If you have permission, open the table and continue execution. When the table is opened, the executor will use the interface provided by the engine to execute according to the engine definition of the table.

At this point, the server layer has completed executing the specific engine layer logic. We will analyze it in the next article

The above is the detailed content of Analysis of mysql execution process. For more information, please follow other related articles on the PHP Chinese website!