takeaway

Mysql is a hot property in small and medium enterprises. As one of the mainstream databases, there is almost no back-end developer who can’t use it, but as an old driver, it’s not enough.

Today through a simple query statement to describe the execution process in Mysql.

select * from table where id=10;Copy the code

Lu it

Let’s take a look at the Mysql infrastructure:

As can be seen from the figure above, Mysql is roughly divided into Server layer and storage engine layer.

Server layer includes connectors, query cache, analyzers, optimizers, etc. It contains most of the core functions of Mysql as well as all the built-in functions (such as date, time 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 is pluggable and supports multiple storage engines such as InnoDB and MyISAM. The dominant storage engine in Mysql is InnoDB, which has become the default storage engine since Mysql5.5.5 due to its transaction support.


Now that we have an overview of the architecture, what are the responsibilities of each of the basic modules?

1. The connector

As the name implies, it is the medium for the connection between the client and Mysql, responsible for logging in, obtaining permissions, maintaining connections, and managing connections.

The connection commands are as follows:

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

After completing the classic TCP handshake, the connector begins to authenticate the identity, asking for a password.

If the password is authenticated, the connector will query the permission. Even if the administrator changes the permission, it will not affect your connection this time. It will only take effect if you re connect.

Password authentication fails, and the message Access denied for user is displayed.

Once the connection is complete, the connection with no subsequent action will become free, which you can see by typing the show processList command.

In the figure below, the Command column displayed as sleep indicates that there is an idle connection in the system.



If the client does not perform an action for too long, the connector will disconnect automatically, controlled by the wait_timeout parameter, which defaults to 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.

2. Query cache [waste, version 8.0 completely removed]

After the connection is established, you can select the statement, which will query the cache before execution.

Query caching in Mysql is turned off by default because the cache hit ratio is very low. Any time an update is performed on a table, all the query cache for that table will be cleared. How’s that？ A waste is enough to describe it!!

Waste things do not need to say more, the mainstream Redis cache you do not need, do not make this waste.

3. The analyzer

If you do not hit the query cache, you need to execute the query, but before executing the query, you need to parse the SQL statement to determine whether you have syntax errors in the statement.

The parser does’ lexical analysis’, where you type a SQL statement consisting of multiple strings and Spaces. MYSQL needs to figure out what the string is, what it stands for, whether it has a keyword, etc.

MYSQL will recognize a query from your select keyword, table is the name of the table, id is the name of the column.

If your SQL statement does not contain any syntax errors, you will receive a warning similar to the following:


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1Copy the code

A common grammar error will indicate where the first error occurred, so focus on the words immediately following “use near”.

4. The optimizer

After parsing the lexical and syntax of the parser, you can now know what the SQL statement does. However, before starting to execute, MYSQL uses the optimizer to optimize the SQL statement.

MYSQL will evaluate this SQL query internally. For example, when it involves multiple indexes, which index is cheaper to use, and when it involves multi-table join, it will decide the join order of each table.

The optimizer’s role is summed up in one sentence:

MYSQL’s internal algorithms determine how to execute this SQL statement to achieve what MYSQL considers the least costly.


After the optimizer phase is complete, the statement execution scheme is determined and the executor is left to execute.

5. Actuators

MYSQL knows what to do with the parser, and knows how to do it with the optimizer, so it enters the executor phase.

Before executing the executor, check whether the user has the permission to execute the table. If the user does not have the permission to return the table, execute the table.

If the Id is not an index, the whole table is scanned and searched row by row. If the Id is an index, the index is queried in the index organization table. The index query is very complex, involving B+ tree and other algorithms, which will not be described in detail here.


conclusion

The internal modules involved in the execution of an SQL statement within MYSQL are: connector, query cache, analyzer, optimizer, executor, storage engine.