MySQL Server Components
MySQL is built on a server-client architecture.
The MySQL database acts as the server, and any application that connects to the MySQL Server is called a client. The MySQL Server itself can be divided into the MySQL engine and the storage engine.
The MySQL engine is the brain of the DBMS: it parses the requested SQL statements and optimizes them, while the storage engine actually writes data to and reads data from disk storage.
Because the roles of the two engines are so clearly separated, requesting and transferring data between them is essential to process a user request. MySQL calls these requests handler requests, and the API used for them is the Handler API. In other words, the MySQL engine and the storage engine exchange data through the Handler API.
MySQL Engine
In this post, we look at the MySQL engine, one of the components of the MySQL Server.
- The MySQL engine consists of the Connection Handler, the SQL Interface, the Parser, and the Optimizer, which plans the optimal execution of queries.
- A MySQL Server can use several storage engines at the same time, but it can use only one MySQL engine.
Connection Pool Layer
The Connection Pool is the top layer of the MySQL architecture. It creates and manages client connections and handles the queries they send.
- Connection handling
The MySQL Server assigns a thread to each client connection request, and the client runs its queries on that thread. (Threads are cached by the server, so a new one does not always have to be created for a new connection.)
- Authentication
Every time a client connects, the MySQL Server authenticates it based on the client host, user name, password, and so on.
- Security
Once a client has connected successfully, the server checks whether that client has the privileges to run a given query.
SQL Interface
MySQL complies with the ANSI SQL standard for the interface that receives commands and sends results to clients, and uses SQL as the interface, like most ANSI-compliant database servers.
Components of the SQL Interface include DML, DDL, stored procedures, views, and triggers.
Parser
Parsing splits the user query into tokens (the smallest units of vocabulary or symbols MySQL can recognize) and builds a tree structure from them. If a syntax error is found in the query during this step, an error message is returned to the user.
Optimizer
The optimizer decides how to process the user query efficiently at the lowest cost. It rewrites queries, adjusts scan order, and selects indexes.
Cache & Buffers
This is a memory area used to read and write data and indexes quickly. It refers to auxiliary storage such as the MyISAM key cache or the InnoDB buffer pool.
Related articles
