Understanding MySQL Architecture

The architecture of the world’s most popular open source database system is very important for the Information Technology people. There are many reasons for MySQL’s popularity around the world, but one of the main reasons is its architecture, while there are many big players such as Oracle, Microsoft SQL and DB2, MySQL’s architecture makes it as unique and preferred choice for most of the developers. In this article, we are going to discuss about of the internal architecture of the MySQL relational database management system. The article is for novice database administrators, database developers, software developers and those who are interested to work with MySQL database.

Major components:

The MySQL architecture describes how the different components of a MySQL system relate to one another. The MySQL architecture is basically a client – server system. MySQL database server is the server and the applications which are connecting to MySQL database server are clients. The MySQL architecture contains the following major components.

MySQL ARCHITECTURE

Application Layer:

This layer is the top most layers in MySQL architecture; you can see this same layer in many of the client – server architecture. This layer includes some of the services which are common to most of the client – server applications, some of the services are given below:

• Connection Handling.
• Authentication.
• Security.

Connection Handling:

When a client connects to server, the client gets its own thread for its connection. All the queries from that client executed within that specified thread. The thread is cached by the server, so they don’t need to created and destroyed for each new connection.

Authentication:

Whenever a client connects to a MySQL server, the server performs the authentication in the server side. The authentication is based on the username, host of the client and password of the client user.

Example:

root@localhostroot          : Username of the client.
localhost  : host name, from where it is originated.

Security:

After the client gets connected successfully to MySQL server, the server will check whether that particular client has the privileges to issue certain queries against MySQL server.

Example: 

mysql> show privileges \G
*************************** 1. row ***************************
  Privilege: Alter
  Context: Tables
  Comment: To alter the table
*************************** 2. row ***************************
  Privilege: Alter routine
  Context: Functions,Procedures
  Comment: To alter or drop stored functions/procedures

MySQL Server layer:

This layer takes care of all the logical functionalities of the MySQL relational database management system. The brain of the MySQL server is resides in this layer. The logical layer of the MySQL is divided into various sub components, which are given below:

• MySQL services and utilities.
• SQL Interface.
• SQL Parser.
• Optimizer.
• Caches & buffers.

MySQL services and utilities:

MySQL comparatively provides wide range of services and utilities. This is one of the main reasons for the popularity of the MySQL. This layer provides the services and utilities for administration and maintenance of MySQL system, some of them are mentioned below:

• Backup & recovery.
• Security.
• Replication.
• Cluster.
• Partitioning.
• Workbench.

SQL Interface:

Structured Query Language (SQL) is a query language, used to query MySQL server. It is a tool to interact between MySQL client user and server. Some of the SQL interface components are given below.

• Data Manipulation Language (DML).
• Data Definition Language (DDL).
• Stored Procedures.
• Views.
• Triggers.

Parser:

MySQL parses queries to create an internal structure (the parse tree).  The MySQL parser behaves as a single pass compiler. As per the MySQL internals, the parser structure is given below.

• Lexical analysis (making words or tokens from a character stream) is implemented at first stage, when parsing regular statements.
• Syntactic analysis (making “sentences”), semantic analysis (making sure these sentences do make sense), and code generation (for compilers) – all of them – are done at once, during the phase of code. 

Optimizer:

After creating the internal parse tree, the MySQL applies a variety of optimization techniques. These techniques may include, rewriting the query, order of scanning of tables and choosing the right indexes to use. Actually you can ask the server to explain the various aspects of optimization.

Example: 

EXPLAIN SELECT * FROM world.city;

Caches:

The MySQL cache (query cache) stores complete result sets for SELECT statements. Even before parsing the query, the MySQL server consults the query cache. If any client issues a query that is identical to one already in the in the cache, the server simply skip the parsing, optimization and even execution, it just simply display the output from the cache.

Storage Engine Layer:

The pluggable storage engine feature makes the MySQL as unique and preferred choice for most of the developers. This is the feature which makes the MySQL to reach an edge over the big player. MySQL allows us to choose the variety storage engines for different situations and requirements. We are going to discuss about the features of each storage engine in upcoming article, just the list of supported storage engines are mentioned below.

• MyISAM.
• InnoDB.
• Federated.
• Mrg_MyISAM.
• Blackhole.
• CSV.
• Memory.
• Archive.
• Performance_schema.

MySQL provides this as a pluggable storage engines, various storage engines can be used at table level. A database can contain the tables with the multiple storage engines.

mysql>SHOW ENGINES;

Show engines command will list all the storage engines supported by your server. I hope this post will give you an overall understanding of the MySQL architecture. In next post let us discuss about the different storage engines and their features.