Architecture of MySQL

MySQL server's architecture sets it apart from different database servers, making it highly versatile. MySQL is a reliable and flexible database that works well in complex environments, such as web applications.

Moreover, MySQL can run data warehouses, embedded applications, content indexing and delivery software, highly available redundant systems, and online transaction processing (OLTP). An overview of MySQL's architecture is presented in this article.

The Three-Layered System

MYSQL is a relational database that has a layered kind of architecture. It's a database system has a three-layered architecture. An architecture consists of three layers: a server resource end, a storage engine, and a client-end or query execution end.

The architecture of MYSQL describes the relationships among the different components of the MYSQL System. It follows Client-Server Architecture. It is designed so that end users can access resources from the Computer that is the server using networking services. The Architecture of MYSQL contains the following major layers:

Client Layer

MySQL does not have a unique top layer, i.e., client. Network tools provide authentication, security, and connection handling in client-server models.

Server Layer

MySQL's second layer, or server, is the heart and soul of the database. It includes many operations, such as query parsing, optimization, analysis, caching, and all the built-in functions (e.g., dates, times, math, and encryption). At this level, any functionality, such as stored procedures, triggers, and views, can be used across storage engines.

Storage Layer

All MySQL data is stored and retrieved in the storage engine's third layer. There are a few storage engines commonly used, including MyISAM and InnoDB. Storage engines communicate with MySQL servers using API.

How Does MySQL Respond To A Query?

The following are the steps involved in generating results at the client layer for a MySQL query:

1. Clients send SQL statements to servers.
2. Queries are checked in the query cache by the server. During this process, it returns the cached result if there is a hit. Else, it passes the SQL statement on to the next stage if there is no hit.
3. A query execution plan is generated by parsing, preprocessing, and optimizing the SQL.
4. A query execution engine calls the storage engine API to execute the plan.
5. Results are sent to the client by the server.

Logical Architecture of MySQL

After introducing the three layers, it would be easier to understand the internal working of MySQL.

‍

Source: https://dev.mysql.com/doc/refman/8.0/en/images/mysql-architecture.png

Client Layer:

In the above diagram, the layer at the top is the Client Layer. The client instructs the server. A Client enters MYSQL commands and expressions using the Command Prompt and GUI screens. The output is displayed depending on whether the expressions and commands are valid. The Client layer is responsible for the following services:

Connection Handling

To establish a connection with the server, the client must send a request, and the server must accept it. There is a thread for every client connection within the server process. Every time a client connects to the server at that time, it gets its own thread. This thread executes all client-side queries.

Queries executed by the connection run on one core or CPU corresponding to one thread. Every time a new connection is initiated, the server caches the threads, so it doesn't have to create and destroy them every time. The API in MySQL 5.5 and higher supports thread-pooling plugins so that a small pool of threads can handle many connections.

Authentication

When a client connects to the MySQL server, authentication is performed on the server side. An authentication process takes place through the use of a username and password.

Security

Once the user has authenticated successfully, the server checks if the user is authorized to run certain queries on the database.

Server Layer:  

Throughout the MYSQL architecture, the second layer is responsible for the logical functioning of the relational database manager. This MYSQL system layer is known as the "Brain of MySQL Architecture." When the server receives a request from the client, it sends the output as soon as it matches the request. MySQL server consists of the following sub-components:

Connection Thread Handling

When a client sends a request to the server, the server accepts the request, and the client is connected. Whenever a client is connected to the server at that time, the connection gets its own thread. The server Layer provides thread handling for this thread. Also, thread handling handles the client-side queries executed by a thread.

Parser

A "parse tree" is built from the tokens of the query by MySQL's parser. Interpreting and validating the query is performed by the parser based on MySQL's SQL grammar. As an example, it checks that the tokens in the query are correct and in the right order, as well as invalid quoted strings that aren't terminated.

Parse trees are checked for additional semantics after parsing by the preprocessor. It ensures columns have been created and resolves names and aliases to prevent ambiguity. The preprocessor checks the privileges. It's usually extremely fast unless one has a high-privilege server.

Parsers build data structures based on input (parse trees). A lexical analysis is performed before parsing, i.e., input is broken into tokens. After the data has been parsed in the smaller elements, the parser performs a syntax analysis and a semantic analysis, and then a parse tree is generated.

Optimizer

At the Optimizer Block, different optimization techniques are applied immediately following parsing. Rewriting the query, rearranging the tables scanned, choosing the right indexes, and other changes may be necessary.

Query Cache

In the Query Cache, the results of each query statement are stored. Before parsing, MySQL Server consults the query cache. Whenever a client writes a query, if the query in the cache is identical to the one written by the client, the server simply skips parsing, optimization, and even execution and just displays the output from the cache.

Cache and buffer

Query caches and problem storage will be implemented. When a user writes a query, the query cache checks if it already contains the same query or problem. If the same query is available, it provides output without interfering with the Parser and Optimizer.

Table Metadata Cache

Information about objects, databases, and indexes is stored in an object cache called a metadata cache. The metadata cache size increases as the number of open databases, indexes, or objects increases.

Key Cache

In a cache, a key cache has the function of uniquely identifying objects. A query string and the entire resource path are used by default when caching content on edge servers.

Storage Layer:

An integral part of MySQL's architecture is its Storage Engine layer. In part, this layer contributes to MySQL's popularity as one of the most widely used RDBMSs. There are a variety of storage engines that MySQL Server can use depending on the situation. It includes InnoDB, MyISAM, NDB, and Memory, among others. Users can plug in user-created tables to these storage engines, making them pluggable storage engines.

The storage engines are the components on the server that handle the different types of tables on the server. The purpose of storage engines is to:

• Maintain a database
• Retrieving data
• An index provides access to data

Conclusion

Getting the most out of MySQL requires understanding its design to work with it. MySQL's most distinctive feature is its storage engine architecture, which separates data storage and retrieval from query processing. Various data types are supported, and one can configure it to run on a range of hardware.

To read more articles like this, visit the Cogent Infotech website.

‍