Mysql Architecture Design

SQL execution process, as shown in the following figure

Connector:

As a first step, you will connect to the database, and your connector will receive you. Connectors establish connections to clients, obtain permissions, and maintain and manage connections. Mysql -h IP −Pip -pip −Pport -u$user -p

After typing the command, you need to enter the password in the interactive dialogue. Run the show processList command to view the connection informationCommand displays information about the connection. In the figure above, the connection is in the sleep state. When the connection is inactive for a long time, it is disconnected, controlled by wait_timeout. There are two types of connections in MySql,A long connectionIf the client continues to receive requests after a successful connection, the same connection is used all the time.Short connectionDisconnects after a few queries are executed and re-establishes the connection the next time. As the process of creating a connection is complex, the operation of establishing a connection should be minimized, that is, long connections should be used as far as possible. Mysql memory usage increases after all long connections are used, which is managed using memory.How to solve the problem of long connection and large memory?

  1. Disconnect long connections periodically. 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.

In practice, connection pools are used to maintain connection counts.

Database connection pool, is the server application to establish a number of connections to the database, has not used the connection pool on the connection pool, when the connection pool, this is faster than no connection to establish a new connection (TCP connection is needed time), so as to improve the transmission efficiency.

For example, in the Spring framework, it implements a persistent connection pool, which allows other programs and clients to connect. This connection pool will be shared by all connected clients. Connection pool can accelerate connection, reduce database connection and reduce the load of database server.

analyzer

MySQL needs to know what you are going to do, so it needs to parse SQL statements. The parser first does a “lexical analysis”. You are entering an SQL statement consisting of multiple strings and Spaces. MySQL needs to figure out what the strings are and what they represent. Based on the results of the lexical analysis, the parser will determine whether the SQL statement you entered meets the MySQL syntax based on the syntax rules.

The optimizer

The optimizer determines which index to use when there are multiple indexes in the table. Or determine the order in which tables are joined when a statement has multiple joins.

actuator

MySQL knows what you want to do from the parser, it knows how to do it from the optimizer, so it goes to the executor phase and starts executing statements. If you do not have permission to perform a query on T, an error will be returned, as shown below. 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 fetch the first row of the table, check whether the ID value is 10, if not skip, if yes, save the 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.
  3. The executor returns the recordset of all rows that meet the criteria in the above traversal as a result set to the client.

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.

It is also an SQL execution process.