sequence

Two diagrams to understand the MySQL architecture and query execution flow.

Architecture diagram

Connectors Refer to apis for interaction between languages and the MySQL server.

A Connection Pool is a Connection Pool component.

Manage connections and permissions: establish connections with clients, obtain permissions, maintain and manage connections. Each client request that successfully connects to MySQL Server will be created or assigned a thread, which is responsible for the communication between the client and MySQL Server, receiving the commands sent by the client, passing the result information of the Server, etc. Thread pool components provide thread reuse, connection limitations, and more.

Management Services & Utillties are Management Services and tools components.

Provides integrated management of MySQL, such as Backup, Recovery, and Security

SQL Interface is an SQL Interface component.

Receive user SQL commands, such as DML,DDL, and stored procedures, and return the final results to the user.

Parse is the parser component.

The MySQL parser validates and parses queries using MySQL syntax rules. The preprocessor further checks the parse tree against some MySQL rules and validates permissions.

Optimizer is an Optimizer component

Translate the parse tree generated by the parser into an execution plan. MySQL uses cost-based optimizers to find the best execution plan

Caches&Buffers is a caching component

Caching and buffering components

Pluggable Storage Engines Memory, Index & Storage Management Pluggable Storage Engines Memory, Index & Storage Management

Query flow chart

Overall process description:

  1. The client sends a query to the server,

  2. Queries the cache and returns the result stored in the cache immediately if a hit is made. Otherwise move on to the next stage.

    There is a query cache learning summary article, you can click here to see

  3. SQL is parsed and preprocessed by the server, and then the optimizer generates the corresponding execution plan.

    There is a learning summary article about MySQL’s cost-based optimizer. If you are interested, click here

  4. MySQL invokes the storage engine API to execute the query based on the execution plan generated by the optimizer.

    Understanding storage Engines

  5. Returns the result to the client

    Note: If query caching is enabled, MySQL will also store the results in the query cache. MySQL returns the result set to the client incrementally and incrementally. This way, the server does not have to store too many results, so it does not consume too much memory by returning too many results. The client can also get the results returned in the first time.

other

This article’s illustrations were drawn using Processon. See MySQL Tech Insider: InnoDB Storage Engine for the first architecture diagram. For details about the second query flow chart, see High-performance MySQL.

The author is not very good at writing articles, but this time he did the following:

  1. In the text, the left and right sides of English characters and Arabic numerals are separated by Spaces
  2. A written order
  3. The drawing with your heart

If you find this article helpful, please leave a red like before you go, or browse the author’s other articles if you are interested. If you think this article is a waste of your time, please leave your comments in the comments section. If you have any questions, please leave a message. The author is willing to spend time and energy to find answers and discuss learning together.