The following is a schematic of the basic architecture of MySQL, from which you can clearly see the execution process of SQL statements in various functional modules of MySQL.Generally speaking, MySQL can be divided into Server layer and storage engine layer two parts. The **Server layer includes connectors, query caches, analyzers, optimizers, actuators, etc. It covers most of MySQL’s core service functions, as well as all built-in functions (such as date, time, math and encryption functions, etc.). All cross-storage engine functions are implemented in this layer, such as stored procedures, triggers, views, etc. ** The storage engine layer is responsible for storing and extracting data. Its architecture mode is plug-in, supporting InnoDB, MyISAM, Memory and other storage engines.

The most commonly used storage engine is InnoDB, which has been the default storage engine since MySQL 5.5.5. In other words, if you do not specify the engine type when executing create Table, InnoDB is used by default. For example, use engine=memory in the CREATE TABLE statement to specify that a memory engine is used to create the table. Different storage engines have different ways to access table data and support different functions.

01 connector

The first step, connector: is responsible for establishing a connection with the client, obtaining permissions, maintaining and managing the connection.

mysql -h$ip -P$port -u$user -p
Copy the code

Mysql is the client tool used to establish a connection with the server. After the classic TCP handshake, the connector authenticates your identity using the username and password you enter.

(1) If the user name or password is denied, you will receive an error saying “Access denied for user” and the client will stop executing. (2) If the user name and password are authenticated, the connector will check your permissions in the permission table. After that, the permission determination logic in the connection will depend on the permissions read at that time.

This means that once a user successfully establishes a connection, even if you change the user’s permissions using the administrator account, the existing connection permissions will not be affected. After the modification is complete, only newly created connections will use the new permission Settings.

Once the connection is complete, if you have no subsequent action, the connection is idle, as you can see in the show ProcessList command. The graph in the text is the result of show ProcessList, where the Command column is displayed as “Sleep”, indicating that there is now a free connection in the system.

mysql> show processlist; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 23 | root | localhost | NULL | Query | 0 | init | show processlist | + - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql >Copy the code

If the client is inactive for too long, the connector automatically disconnects it. This time is controlled by the wait_timeout parameter, and the default is 8 hours.

If the client sends a request again after the connection has been disconnected, it will receive an error message: Lost Connection to MySQL Server during Query. At this point, if you want to continue, you need to reconnect and then execute the request.

** However, the memory usage of MySQL sometimes increases very quickly with the full use of long connections. This is because the memory temporarily used by MySQL during execution is managed in connection objects. These resources are released when the connection is disconnected. If long connections are accumulated, the system may use too much memory and kill it forcibly (OOM). In this case, MySQL restarts abnormally. How does ** solve this problem? There are two options you can consider. 1. Disconnect long connections regularly. Use for a period of time, or in the program to determine the execution of a large memory – consuming query, disconnect, then query and reconnect.

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

02 Querying Cache Information

Second, the query cache: when MySQL receives a query request, it first checks the query cache to see if the query has been executed before. Previously executed statements and their results may be directly cached in memory as key-values.

Key is the query statement and value is the query result. If the query can find the key directly in the cache, the value is returned directly to the client. If the statement is not in the query cache, subsequent execution phases continue. After the execution is complete, the results are stored in the query cache.

If the query hits the cache, MySQL can return the result directly without having to perform subsequent complex operations, which can be very efficient.

But in most cases I would advise you not to use query caching, why? Because query caching often does more harm than good. The query cache invalidates so frequently that whenever a table is updated, all the query cache on that table is cleared. So it’s very likely that you struggled to save the results, and an update wiped them out before you even used them. For databases under pressure to update, the hit ratio of the query cache can be very low. Unless your business has a static table, it only updates once in a while. For example, a system configuration table, the query on that table is suitable for query caching.

MySQL also provides this “on demand” approach. You can set the query_cache_type parameter to DEMAND so that query caching is not used for default SQL statements. For statements where you are sure to use the query cache, you can specify it explicitly with SQL_CACHE, as in the following statement:

Mysql > select SQL_CACHE * from T where ID=10;Copy the code

Note that MySQL 8.0 directly removed the entire query cache function, meaning that this function has been completely removed from MySQL 8.0.

03 analyzer

Third, if the query cache is not hit, the parser performs “lexical” and “syntax” analysis on the SQL statement.

Judge whether the morphology is correct and the sentence is correct.

04 optimizer

The fourth step, the optimizer: is in the table there are multiple indexes, which index to use; Or determine the order in which tables are joined when a statement has multiple joins.

For example, if you execute a statement like the following, this statement performs a join on two tables:

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;
Copy the code

(1) The ID value of the record c=10 can be obtained from table T1 first, and then associated with table T2 according to the ID value, and then determine whether the value of D in T2 is equal to 20.

(2) You can also take the ID value of the record d=20 from table T2, and then associate it with T1 according to the ID value, and then judge whether the value of C in T1 is equal to 10.

Function: The logical result of the two execution methods is the same, but the efficiency of the execution is different, and the role of the optimizer is to decide which one to use.

05 actuators

If you do not have the permission to execute a query on table T, (1) if you do not have the permission to execute a query on table T, (2) if you do not have the permission to execute a query on table T, (3) if you do not have the permission to execute a query on table T, (4) if you do not have the permission to execute a query on table T, (4) if you do not have the permission to execute a query on table T, (4) if you do not have the permission to execute a query on table T, you do not have the permission to execute a query on table T. The query also calls Precheck to verify permissions before the optimizer).

mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'
Copy the code

(2) If you have permission, open the table to continue execution. When a table is opened, the executor uses the interface provided by the table engine according to its definition. For example, in table T of our example, the ID field has no index, then the execution flow of the executor is like this:

1) Call InnoDB engine interface to get the first row of this table, check whether the ID value is 10, if not, skip, if yes, save this row in the result set; 2) Call the engine interface to fetch "next row" and repeat the same logic until the last row of the table is fetched. The executor returns the recordset of all rows that meet the criteria in the above traversal as a result set to the client.Copy the code

At this point, the statement is complete.

The same logic is performed for indexed tables. The first call is the “fetch the first row that meets the condition” interface, and then the “next row that meets the condition” interface is iterated, all of which are already defined in the engine.

You’ll see a ROWS_EXAMINED field in the database’s slow query log that shows how many rows were scanned during this statement execution. This value is accumulated each time the executor calls the engine to retrieve the row.

In some scenarios, the executor is called once and multiple rows are scanned inside the engine, so the number of rows scanned by the engine is not exactly the same as rows_EXAMINED.