MySQL Notes | 1. Understand the execution process of SQL

Series Article Directory

Tips: Directory of all articles
1. Understand the execution process of SQL


By learning MySQL output notes, before systematically learning MySQL, first have a general understanding of the overall architecture.

Tip: The following is the content of this article

1. What is the SQL execution process?

Implementation process

Insert picture description here

2. There are several components of the MySQL framework, what is the role of each?

1. Connector

Responsibilities: The connector is responsible for establishing a connection with the client, obtaining permissions, maintaining and managing the connection

[email protected]:/# mysql -h -uroot -p123456
  1. After the TCP handshake, the identity is authenticated.
  2. The process of establishing a connection is cumbersome, and long connections are used to reduce creation. If the memory usage is too large for a long time, it will be forcibly killed.
  3. Reinitialize connection resources by executing mysql_reset_connection

2. Query cache

Responsibilities: query the cache. Due to the different versions, there are obvious differences between version 5.7 and version 8.0.
Look at the cache through SQL_CACHE in version 5.7

select SQL_CACHE * from yy_passenger_coupon;

After 8.0, the cache module was officially abandoned

Insert picture description here

3. Analyzer


  1. Syntax analysis, built-in parse tree, in the order of from, join, on, where, group by, etc.
  2. If an exception occurs, you need to pay attention to the content after use near.
  3. Also in the analyzer stage, the table structure is judged.

4. Optimizer


  1. Confirm the index (computing the least expensive index)
    Confirm the connection order of the table
  2. Know what you are going to do

5. Actuator

Responsibilities: perform the tasks provided by the optimizer, but will first determine the permissions before executing

Three, the update process of update

Insert picture description here

1. Write the value to a new row and update it to the memory
2. Use Redo Log and Bin Log to cooperate for the second submission to ensure data consistency

1. Understand the role of Redo Log, Bin Log, and Undo Log

Insert picture description here

 Redo Log: It is unique to Innodb, and binlog is implemented by the Server layer of MySQL.

Question: During the update process, I saw that it was written to Redo Log first, and then to Bin log. Is it possible not to use Redo Log?

The answer is no, because Redo Log is only available in InnoDB. BinLog uses an additional form, while Redo Log is reused and does not have the ability to archive.
The two of them complete the two-phase submission to ensure data consistency

to sum up

The execution process of MySQl

  1. Through the connector, the connection verification authority is established, and the connection is managed and maintained through the connector to avoid repeated connections.
  2. Query cache to reduce query costs.
  3. Through the parser, a grammatical analysis is established, the execution order is determined, the table structure verification is performed, and error information is returned in advance.
  4. Through the optimizer, confirm the lowest cost index, know what to do
  5. Through the executor, in the execution of the final task, the first data of the data that meets the conditions is obtained by polling in turn.