This is the 28th day of my participation in the More Text Challenge. For more details, see more text Challenge

When you open a database client, connect to the database, enter a SQL statement, click execute, and output the result. What happens in the process of execution?

The following figure shows the schematic diagram of the interaction between the client and MySQL.

Query the trajectory of the statement

What is the trajectory of a query statement?

select id from bas_student;
Copy the code
  • The Client is connected through the MySQL Client Connection
  • Query the cache. The prerequisite is that the query cache function is enabled. If this function is enabled, if the same SQL statement is found in the cache query process, the query results are directly returned to the client. If this function is disabled or no data is found, go to the next step
  • The premise is that the query cache is not enabled or the data is not found, and the parser is used to parse syntax and semantics and generate a parse tree
  • The preprocessor generates a new parse tree
  • The query optimizer generates an execution plan, which is essentially selecting the optimal query path
  • The query execution engine executes SQL statements through the executor component. According to the execution plan, it calls various interfaces of the storage engine to execute SQL statements. After the data is queried, it not only returns the results to the client, but also saves the results to the query cache

Update the trajectory of the statement

What about a track that updates a statement?

update bas_student set name = 'jasen' where id= 2021;
Copy the code

The execution trajectory of the query statement is the same, but update statements involve two important log modes: redo log and bin log.

  • redo log prepare
    • The storage engine updates the new log to memory and records the operation in the redo log, which is in the prepare state and ready to commit.
  • bin log
    • The executor generates the bin log of the operation and writes the bin log to disk
  • redo log commit
    • Change the redo log state to COMMIT by calling the engine’s commit interface at execution time

extension

Query cache is weak and deprecated by MySQL8.0

If the query cache is enabled, the SQL statements must be exactly the same in order to find the result. For update statements, the data that is exactly the same is less, and if the data in the table is updated, the table cache will become invalid.