For MySQL, it is actually divided into client side and server side.

  • Net start MySQL: net start MySQL: net start MySQL
  • The client is responsible for sending requests to the server and getting data from the server. The client can be in various forms, including the black window we open through mysql-uroot-p1234, the database connection tools we use such as Nativecat and SQLyog, and even our programs. Also known as the MySQL client.

When we enter a SQL statement in the mysql window or database connection tool, we can get the data we want. How does mysql work?

After we execute SQL, such as a simple select * from user where name = ‘yanger’, the client sends a request to the Server, and the request reaches the Server layer, passing through connectors, query caches, analyzers, optimizers, actuators, etc. Finally, the storage engine retrieves data from or inserts data into the file system.

The connector

When the client initiates a connection, the server program authenticates the host information, user name, and password provided by the client program. If the authentication fails, the server program rejects the connection.

The join command is familiar.

mysql -h$ip -P$port -u$user -pCopy the code

After entering the command, you need to enter the password again. You can also enter the password directly after -p, but this may cause your password to be leaked. If you are connected to a production server, you are strongly advised not to do this.

MySQL uses TCP as the network communication protocol between the server and client. After the TCP handshake is complete, the connector is used to verify passwords and obtain permissions.

  • If the username or password is wrong, you’ll get an error saying “Access denied for user”
  • If the user name and password are authenticated, the connector will go to the permission table to find out which permissions you have. After that, the permission determination logic in the connection will depend on the permissions read at that time

The default MySQL connection is 8 hours, which is controlled by wait_timeout. If the connection is not used after this time, it is automatically disconnected, and the Lost Connection to MySQL Server during query is thrown later.

The query cache

In the case of a query statement, 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. If the cache is hit, the result is returned directly. If it is not in the query cache, subsequent execution phases continue. After the execution is complete, the results are stored in the query cache.

For update statements, including insert and delete statements, when MySQL receives an update request, it clears all the cache data related to this table in the query cache.

As we can see, as long as there is update, the cache will be invalid, and for normal business, update is actually relatively frequent, that is to say, in fact, MySQL query cache hit rate is not very high, so it is generally recommended not to enable.

Query caching can be turned off by setting QueryCacheType to DEMAND. In fact, in MySQL 8.0, query caching was removed directly.

analyzer

MySQL first needs to analyze SQL statements, the analysis process is essentially a compilation process, involving lexical analysis, syntax analysis, semantic analysis and other stages, through the analysis of MySQL to know what to do.

‘You have an error in your SQL syntax’, so You should pay attention to what follows’ use near ‘.

The optimizer

MySQL performs some optimizations on the results obtained by the parser, such as determining which index to use when there are multiple indexes in a table, or determining the join order of each table when a statement has multiple table joins.

The result of optimization is an execution plan that indicates which indexes should be used for the query and in what order the tables should be joined. We can use EXPLAIN statements to see the execution plan for a statement.

Here, G can display the result vertically for easy viewing when the command window fails to display a line.

actuator

After the analyzer and optimizer, the execution phase is officially started, but before execution, permission validation is required. If the permission is insufficient, permission errors will be thrown. In fact, when querying the cache, permissions will also be verified.

If it passes validation, the executor opens the table and continues execution. When a table is opened, the executor uses the interface provided by the table engine according to its definition.

The storage engine

MySQL supports many kinds of storage engines. The most common ones are InnoDB and MyISAM. The default storage engine of MySQL is InnoDB.

If we choose InnoDB engine, InnoDB will take the first row of the table to determine whether it meets the requirements for the query. If it does, it will be in the result set. Otherwise, proceed to the next row until the last row of the table.

The storage engine then returns the result to the executor, which returns the result to the client, and an SQL statement is executed.