The Server layer includes connectors, query caches, analyzers, optimizers, executors, etc., covering most of the core service functions of MySQL.
The storage engine layer is responsible for data storage and retrieval. Its architecture is plug-in style, supporting multiple storage engines such as InnoDB, MyISAM, and Memory. The most commonly used storage engine is InnoDB, which became the default storage engine starting from MySQL version 5.5.5.
When executing the 'create table' command to create a table, if no engine type is specified, InnoDB will be used by default.
You can choose other engines by specifying the type of storage engine, for example, by using 'engine=memory' in the 'create table' statement to specify the creation of a table with a memory engine. The data access methods and supported features of tables with different storage engines vary.
Connector
The connector is responsible for establishing connections with clients, obtaining permissions, and maintaining and managing connections.
Once a user successfully establishes a connection, even if you modify the user's permissions with the administrator account, it will not affect the permissions of existing connections. After the modification is completed, only new connections will use the new permission settings.
After the connection is completed, if there is no follow-up action, the connection will be in an idle state (sleep), which can be seen in the 'show processlist' command.
If the client has no activity for a long time, the connector will automatically disconnect it. This time is controlled by the 'wait_timeout' parameter, with a default value of 8 hours.
If the client sends a request again after the connection is disconnected, an error reminder will be received: 'Lost connection to MySQL server during query'. At this point, if you want to continue, you need to reconnect and then execute the request.
In the database, a long connection refers to the connection that will continue to be used for the same request if the client continues to have requests after a successful connection. A short connection refers to the connection that disconnects after executing only a few queries each time, and a new one is established for the next query.
Using long connections can cause MySQL to occupy memory very quickly because the memory temporarily used by MySQL during execution is managed within the connection object. These resources are only released when the connection is disconnected. Therefore, the accumulation of long connections may lead to too much memory occupied.
Solutions:
- Regularly disconnect long connections.
- If it's MySQL 5.7 or a newer version, you can reinitialize connection resources by executing 'mysql_reset_connection' after each significant operation. This process does not require reconnection and re-authorization verification, but it will restore the connection to the state just after creation.
Query Cache
After receiving a query request, MySQL will first check the query cache to see if the statement has been executed before. Previously executed statements and their results may be directly cached in memory in the form of key-value pairs. The key is the query statement, and the value is the query result.
In most cases, it is recommended not to use the query cache, because the query cache invalidates very frequently. Any update to a table will clear all query caches on this table.
MySQL 8.0 version completely deleted the entire feature of query cache, stating there's no such feature starting from 8.0.
Analyzer
The analyzer first performs "lexical analysis". The SQL statement you input is composed of multiple strings and spaces. MySQL needs to recognize what each string inside represents.
The syntax analyzer will judge whether the SQL statement you input meets MySQL syntax based on the grammar rules.
Optimizer
The optimizer decides which index to use when there are multiple indexes in a table, or decides the connection order of various tables when a statement involves multiple table associations (join). Different execution methods have different efficiencies.
Executor
Before starting to execute, the executor needs to determine whether you have the permission to execute the query on this table. If not, an error message indicating a lack of permissions will be returned.
If you have permission, the executor will open the table and continue to execute. When opening the table, the executor will use the interface provided by the engine according to the table's engine definition.
Procedure:
- Call the InnoDB engine interface to get the first row of the table, judge whether it meets the condition, if not, skip it, if yes, store this row in the result set.
- Call the engine interface to get the "next row", repeat the same judgment logic until the last row of this table is obtained.
- The executor returns the record set composed of all rows that meet the condition in the above traversal process as the result set to the client.
