Series of articles

Tip: Table of contents for all articles

preface

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

Tip: The following is the text of this article

A, SQL execution process is what?

Execute the process

MySQL framework has several components, each is what function?

1. The connector

Responsibilities: The connector is responsible for establishing connections with clients, obtaining permissions, maintaining and managing connections

root@30aa56390615:/# mysql -h 127.0. 01. -uroot -p123456
Copy the code
  1. After the TCP handshake, the identity is authenticated.
  2. The process of establishing connections is tedious, and using long connections reduces creation. If the memory usage is too large for a long time, the system will be forcibly killed.
  3. Reinitialize the connection resource by executing mysql_reset_connection

2. Query the cache

Responsibility: Query cache. There are significant differences between 5.7 and 8.0 due to different releases. In version 5.7, use SQL_CACHE to view the cache

select SQL_CACHE * from yy_passenger_coupon;
Copy the code

After 8.0, the cache module was officially deprecated

3. The analyzer

Responsibilities:

  1. Syntax analysis, built-in parse tree, from, Join, on, WHERE, group by, etc.
  2. Attention should be paid to the content after use near if abnormalities occur.
  3. It is also in the analyzer stage that the table structure is judged.

4. The optimizer

Responsibilities:

  1. Confirm index (minimum calculated cost index)

2. Know what you need to do

5. Actuators

Responsibilities: Do what the optimizer tells you to do, but judge permissions before doing it

Update process

Write the value to a new line and update the value to the memory. 2. Use the Redo Log and Bin Log to perform secondary submission to ensure data consistency

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

Redo Log: InnoDB-specific. Binlog is implemented by MySQL Server.

Q: If I write Redo Log and Bin Log during update, do NOT write Redo Log?

The answer is no, because Redo logs are unique to InnoDB, binlogs are appends, and Redo logs are reused and do not have archiving capabilities. The two of them complete the two-phase commit to ensure data consistency

conclusion

MySQl execution process

  1. Use the connector to establish connection verification permissions, and use the connector to manage and maintain connections to avoid repeated connections.
  2. Query cache, reduce query cost.
  3. Through the analyzer, establish syntax analysis, determine the execution sequence, verify the table structure, return error information in advance.
  4. With the optimizer, confirm the lowest-cost index, know what to do
  5. After executing the final task through the actuator, the first data meeting the conditions is obtained by polling down in turn.