This is the 16th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

Mysql query execution process 1

Query execution process

  1. The client sends a query to the server
  2. The server first checks the query cache, and if a hit is made, it immediately returns the result stored in the cache.
  3. If the cache is not hit, the server parses and preprocesses the SQL, and the optimizer generates the corresponding execution plan.
  4. Mysql invokes the storage engine API to execute the query based on the execution plan generated by the optimizer.
  5. Returns the result to the client

The communication protocol between the Mysql client and server is half duplex. At any time, either the server sends data to the client or the client sends data to the server. The two actions cannot be performed simultaneously.

The query cache

Before parsing a query statement, if the query cache is open, mysql first checks whether the query matches the data in the query cache. This check is implemented through a case-sensitive hash lookup. The query differs from the cached query by only one byte and does not match the cached result.

If the current query happens to hit the query cache, mysql checks the user permissions before returning the query result. If the permissions are ok, mysql will fetch the results directly from the cache and return them to the client.

The query process

The SQL is converted into an execution plan by which mysql interacts with the storage engine.

Parsing SQL, preprocessing, optimizing SQL execution plan.

Parsing and preprocessing

Mysql parses SQL statements with keywords, generating a parse tree, and the mysql parser validates and parses queries using mysql syntax rules.

The preprocessor further checks the parse tree against some mysql rules, and then the preprocessor validates the permissions

After the syntax tree validation is complete, the optimizer transforms it into an execution plan. A query can be executed in multiple ways, and the optimizer finds the best execution plan.