Shulou(Shulou.com)06/01 Report--

This article mainly introduces what is the whole process of Mysql executing a sentence. It is very detailed and has a certain reference value. Friends who are interested must read it!

Logical Architecture of 1.Mysql

The logical architecture of Mysql is shown below and is divided into two parts, the Server layer and the storage engine layer.

Operations independent of the storage engine are done in the Server layer, and the storage engine layer is responsible for data access.

The function of each step is described below according to the process in the figure above. Here, take querying a record as an example.

two。 Connector

This step is mainly about managing connections and permission verification.

It is responsible for managing the connection of the client, such as mysql-u root-p, which is completed between the client and the connector. The connection is divided into long connection and short connection. It is recommended to use long connection, because establishing a connection is a relatively complex process. However, long connections also have room for optimization, that is, too many long connections, which will take up more memory with the execution of large query operations.

After the connection is established, the connector determines the permissions of the user, and then the user's actions are based on permissions to determine whether or not to allow it.

3. Analyzer

This step is mainly lexical analysis and grammatical analysis.

Lexical analysis is mainly to determine what users want to do, for example, select wants to query.

The main purpose of syntax analysis is to determine whether the SQL entered by the user conforms to the syntax of Mysql.

4. Optimizer

Through the analyzer, Mysql already knows what the user wants to do, but for the same SQL statement, there may be many ways to implement Mysql, and the efficiency is not the same.

At the optimizer stage, mysql needs to decide how to execute it for the best efficiency.

5. Actuator

This step is mainly about operating the engine and returning results. Traverse a data table by manipulating the storage engine layer to find the data that meets the criteria and return it to the client.

The process of 6.Mysql executing an update statement

Like a SQL query statement, Mysql also has to go through connectors, analyzers, optimizers, executors, and storage engines to access data.

The difference is that the update statement involves two important logging modules, redo log and binlog

7.redo log

A restaurant is doing brisk business, but as a restaurant, it is inevitable that there are credits and debts to be paid every day.

If there are not many people on credit and repayment, the boss can directly use a pink board to record, if there are many people, the pink board can not write a month's credit record at all.

So the boss thought that he could write all the credit records in the account book, and what was written on the pink board was for a short time, and when he got off work, he would reconcile it with the account book.

In this example, the fan board is redo log, and the account book is the record in mysql. We use the debt repayment analogy to compare the update process of mysql. If every time someone updates, we go to mysql to find this record, which is very inefficient, so mysql's idea is the same as this boss. Update operations are first put into redo log, and then slowly digested over a period of time.

This idea is called WAL technology, that is, Write Ahead Logging technology, which writes logs first, and then writes disks.

It should be noted that if the powder board is full and the boss is not off duty, the boss must stop his work. In total, redo log in mysql can record the operations of 4GB.

When write pos catches up with check point,mysql, the boss will have to deal with redo log.

In addition, with redo log persistence, the database will not lose logs even if it is restarted abnormally, which is the crash safe mechanism, but it is still important to note that redo log is unique to the innodb storage engine.

8.bin log

Binlog is the log of the Server layer and can be applied to all storage engines.

So since there is binlog, why do you want to build a redo log dedicated to the innodb storage engine?

Because mysql initially did not have an innodb engine, but a myisam engine, using binlog, but binlog is limited to archiving, there is no crash safe mechanism, so I added a redo log.

Redo log is specific to the innodb storage engine, while binlog is specific to the server layer

Redo log stores physical logs, and binlog is a logical log

Redo log, as mentioned above, supports 4GB size. If there is more, you have to deal with it and overwrite it. When the binlog log is full, one log will be filled.

After the file, a new log file is created.

Here's an example of updating a row of data with an ID of 2:

The light box in the figure indicates that it is executed inside the InnoDB, and the dark box indicates that it is executed in the actuator.

The actuator first goes to the engine to fetch the ID=2 line. ID is the primary key, and the engine uses a tree search to find this line. If the data page of the ID=2 line is already in memory, it is returned directly to the executor; otherwise, you need to read the memory from disk and then return it.

The executor takes the row data given by the engine, adds 1 to this value, for example, it was N, now it is Number1, gets a new row of data, and then calls the engine interface to write this new line of data.

The engine updates the new line of data to memory and records the update operation to redo log, when the redo log is in the prepare state. The executor is then informed that the execution is complete and the transaction can be committed at any time. The executor generates the binlog for this operation and writes the binlog to disk.

The executor calls the commit transaction interface of the engine, and the engine changes the redo log just written to the commit (commit) state, and the update is completed.

The above is all the content of the article "what is the whole process of executing a sentence in Mysql?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.