Exploring MySQL architecture

Under the hood, all computer systems consist of several layers. Each layer has a specific role to play within the system's overall design. A layer is responsible for one or more tasks. The tasks are broken down into smaller modules dedicated to one aspect of the layer's role. An operation needs to get through all the layers to succeed. If it fails at one, it cannot proceed to the next and an error occurs.

MySQL server also has several layers. The physical layer is responsible for storing the actual data in an optimized format. The physical layer is then accessed through the logical layer. The logical layer is responsible for structuring data in a sensible format, with all required permissions and structures applied. The highest layer is the application layer, which provides an interface for web applications, scripts, or any kind of applications that have the API to talk to the database.

As discussed before, an RDBMS system typically has a client-server architecture. You and your application are the client, and MySQL is the server.

The MySQL layers

There are three layers in the MySQL server:

• Application layer
• Storage layer
• Physical layer

These layers are essential for understanding which part is responsible for how your data is treated. The following is a graphical representation of the basic architecture of a MySQL server. It shows how the different components within the MySQL system relate to each other.

Figure 1.7 – MySQL architecture

Application layer – Client connection

The application layer accepts a connection using any one of the client technologies (JDBC, ODBC, .NET, PHP). It has a connection pool that represents the API for the application layer that handles communication with different consumers of the data, including applications and web servers. It performs the following tasks:

• Connection handling: The client is allocated a thread while creating a connection; think of it as a pipeline into the server. Everything the client does will be over this thread. The thread is cached so the client does not need to log in each time they send a request. The thread is destroyed when the client breaks the connection. All clients have their own threads. When a client wants to connect to a database, they will start by sending a request to the database server using their credentials. Typically, the requests will also include details about which database they specifically wish to connect to on the server. The server will then validate their request, establish a session with the server, and return a connection to the user.
• Authentication: When the connection is established, the server will then authenticate the client using the username and password details sent with the request. If the login details are incorrect, the client will not be allowed to proceed any further. If the login details are correct, the client will move to the security checks.
• Security: When the client has successfully connected, MySQL will check what the user account is permitted to do in it. It will check their read/write/update/delete status, and the security level for the thread will be set for all requests performed on this connection and thread.

When a client connects to the server, several services activate in the connection pool of the server layer.

MySQL server layer (logical layer)

This layer has all the logic and functionality of the MySQL RDBMS. Its first layer is the connection pool, which accepts and authenticates client connections. If the client connects successfully, the rest of the MySQL server layers will be available to them within the constraints. It has the following components:

• MySQL services and utilities: This layer provides services and utilities to administer and maintain the MySQL system. Additional services and utilities can be added as required; this is one of the main reasons why MySQL is so popular. Some of the services and utilities include backup and recovery, security, replication, clustering, portioning, and MySQL Workbench.
• SQL interface: SQL is a tool to provide interaction between the MySQL client and the MySQL server. The SQL tools provided by the SQL interface layer include, but are not limited to, Data Manipulation Language (DML), Data Definition Language (DDL), stored procedures, views, and triggers. These concepts will be taught thoroughly throughout the course of this book.
• Parser: MySQL has its own internal language to process data requests. When a SQL statement is passed into the MySQL server, it will first check the cache. If it finds that an identical statement has previously been run by any client, it will simply return the cached results. If it does not find the query that has been previously run, MySQL parses the statement and compiles it into the MySQL internal language.

The parser has three main operations it will perform on the SQL statement:

• A lexical analysis takes the stream of characters (SQL statement) and builds a word list making up the statement.
• A syntactic analysis takes the words and creates a structured representation of the syntax, verifying that the syntax is correctly defined.
• Code generation converts the syntax generated in Step 2 into the internal language of MySQL, which is a translation from syntactically correct queries to the internal language of MySQL.

• Optimizer: The internal code from the parser is then passed into the optimizer layer, which will work out to be the best and most efficient way to execute the code. It may rewrite the query, determine the order of scanning the tables, and select the correct indexes that should be used.
• Caches: MySQL will then cache the complete result set for the SELECT statements. The cached results are kept in case any client, including yourself, runs the same query. If they do so, the parsing is skipped, and the cached results are returned. You will notice this in action if you run a query twice. The first time will take longer for the results to be returned; subsequent runs will be faster.

Storage engine layer (physical layer)

The storage engine layer handles all the insert, read, and update operations with the data. MySQL uses pluggable storage engines technology. This means that you can add storage engines to better suit your needs. Storage engines are often optimized for certain tasks or types of storage and will perform better than others at their "specialty."

Now, you will look into different types of storage engines in the following section.