MySQL is a relational database, and a relational database, as its name implies, is built on the basis of a relational model, which is generally used to represent the various entities in our real world and the various relationships between entities. After decades of development, relational database in theory and industrial practice have developed to a very mature point, it can be said that the vast majority of current applications, the use of MySQL have mature solutions.

Database architecture can be generally divided into application layer, logical layer, physical layer, MySQL can also be understood in this way.

Corresponding to the Mysql:

The application layer, which is responsible for interacting with clients and users, needs to interact with different clients and intermediate servers, establish connections, remember the status of connections, respond to their requests, return data and control information (error messages, status codes, etc.).

Logical layer, responsible for specific query processing, transaction management, storage management, recovery management, and other additional functions. The query processor is responsible for query parsing and execution. When receiving the Sql query from the client, the database allocates a thread to process it. The query processor generates an execution plan, which is delivered to the plan executor for execution. The transaction, storage manager is responsible for our transaction management, concurrency control, storage management, our transaction manager ensures ACID properties, our lock manager controls our concurrency, our log manager ensures our data persistence, and storage managers generally include a Bufer manager, It determines the transfer of data between disk and memory buffers.

Physical layer, database files on the actual physical disk (storage). Like our data files, log files, etc.

We can start by looking at the infrastructure diagram provided by Mysql:

Mysql logical architecture after simplification

As can be seen from the figure, the logical layer can be roughly divided into Server layer and storage engine layer

The Server layer mainly includes: connector, analyzer, optimizer, executor, query cache, etc. It covers most of the core functions of MYSQL, as well as all the 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.

Different storage engines share a Server layer

  1. The connector

Connectors are responsible for establishing connections, granting permissions, maintaining and managing connections to clients.

mysql -h$ip -P$port -u$username -p$password

After the MYSQL connector establishes a connection, even if you change the permissions of the current user, the current connection will not be affected. After the connection is disconnected and reconnected, the permissions will be verified again. After the connection is complete, there is no subsequent operation called idle connection. You can query the idle connection with the show processList command as shown below:

The default MYSQL connection hold time is 8h, which can be controlled by the wait_timeout parameter. Setting up a connection is a very complicated process, so it is recommended to use the long connection mode. Mysql temporarily uses memory management in the connection object, which will be released only after the connection is disconnected. If the connection is kept for a long time, OOM is likely to occur.

1. Disconnect the long-term connection 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.Copy the code
  1. The query cache

Before mysql8 version, with the query cache module, each of the query results to the query cache cache, the next time the same SQL cache results returned directly go, generally the default query cache, is not recommended because the cache for tables, each table changes will delete all cache on this table, don’t go the default cache can be configured as follows parameters, according to the need to go slow Deposit:

DEMAND select SQL_CACHE * from T where ID=10; //SQL_CACHE Displays the current SQL cacheCopy the code
  1. analyzer

The mysql analyzer disassembles SQL statements to determine select, UPDATE, DELETE, and INSERT, and disassembles tables after FROM and where conditions. Check whether the current SQL statement conforms to the mysql specification by disassembly. If not, the error message “You have an error in your SQL syntax” is displayed.

  1. The optimizer

The mysql optimizer determines which index to select if there are multiple indexes in the current SQL server.

select * from a join b useing(ID) where  a_id = 1;
Copy the code

Optimizer will by comparing two table, if a table data less b table data, through the optimizer is given priority to with a table table b table query number is far less than that of b table associated with a table, the query result is same but the execution efficiency is different, the optimizer can choose the optimal solution, there is also the most left we often say mysql optimization principle, index, index, push down, etc, We can view the SQL execution plan by Explain to determine whether to move the index.

  1. actuator

Mysql executor is the actual execution of SQL, the first will be checked to determine whether the execution permission, and then divided into two cases whether there is no index:

select * from a  where ID = 1;
Copy the code

If the ID field has an index, it quickly traverses the index tree to match the index based on conditions. The primary key index directly queries data and saves it to the result set. The common index returns the query to the table and saves the result to the result set

If the ID field does not have an index, innoDB engine interface is called to fetch the first row in the table to determine whether it meets the condition. Innodb engine interface is not called to query the next row until all rows in the table are traversed and the queried result is returned to the client in the result set

Summary:

This article introduces the mysql infrastructure, and focuses on the logical architecture, so that we know a query statement in mysql execution order, so that we have a preliminary impression of mysql execution flow, I will continue to step by step to disassemble mysql execution flow.

References:

Zhihu zhuanlan.zhihu.com/p/19965157

Geek time mysql combat 45 talk