Shulou(Shulou.com)06/01 Report--

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

Server layer includes connector, query cache, analyzer, optimizer, executor, etc., covering most core service functions of MySQL, as well as all built-in functions. All cross-storage engine functions are implemented at this layer, such as stored procedures, triggers, views, etc.

The storage engine layer is responsible for storing and retrieving data. Its architecture mode is plug-in, supporting multiple storage engines such as InnoDB, MyISAM, Memory, etc. The most common storage engine is InnoDB.

Let's take a look at SQL execution flow

connector

The first step is to connect to this database, and then you'll be greeted by the connector. Connectors are responsible for establishing connections with clients, obtaining permissions, maintaining and managing connections

After the connection is complete, if you have no further action, the connection is idle, as you can see 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 more complex, so in use to minimize the action of establishing a connection, that is, try to use a long connection

However, after using all long connections, you may find that sometimes MySQL takes up memory very fast, because MySQL temporarily uses memory during execution to manage the connection object. These resources are released when the connection is broken

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

1. Disconnect long connections periodically. Use for a period of time, or the program to determine the execution of a large memory query, disconnect, and then query and reconnect.

2. If you are using MySQL 5.7 or later, you can reinitialize the connection resource by executing mysql_reset_connection after each large operation. This process does not require reconnection and revalidation of permissions, but restores the connection to the state it was created in.

query cache

Once the connection is established, you can execute the select statement. The execution logic takes you to the second step: querying the 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 as key-value pairs. key is the query statement and value is the query result. 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, execution continues at a later stage. After execution, the execution results are stored in the query cache. As you can see, if the query hits the cache, MySQL can return the result directly without performing the following complex operations, which is very efficient.

But don't use query caching in most cases, why? Because query caching often does more harm than good.

Query cache invalidation is very frequent, as long as there is an update to a table, all the query cache on that table will be emptied. So it's likely that you've saved the results, and before you can use them, an update clears them all. For databases with high update pressure, the hit rate of the query cache can be very low. Unless your business is a static table, it takes a long time to update.

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

MySQL version 8.0 directly deletes the whole query cache function, which means that 8.0 does not have this function at all.

analyzer

If it misses the query cache, it's time to actually execute the statement. First, MySQL needs to know what you want to do, so it needs to parse SQL statements.

The parser will do "lexical analysis" first. You enter a SQL statement consisting of multiple strings and spaces. MySQL needs to identify what the strings are and what they represent.

After doing this recognition, you have to do "grammar analysis." Based on the lexical analysis results, the parser will determine whether the SQL statement you entered satisfies MySQL syntax according to syntax rules.

Optimizer

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

Optimizer determines which index to use when there are multiple indexes in a table, or determines the order of joining tables when there are multiple joins in a statement

After the optimizer phase is complete, the execution scheme of the statement is determined and the executor phase is entered

effector

When starting execution, you should first determine whether you have the permission to execute the query on this table T. If not, you will return to the error of no permission.

If you have permission, open the table and continue. 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.

Here, the server layer will complete the execution of the specific engine layer logic. We will analyze it in the next article.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.