Understanding MYSQL Logical Architecture

What is MySQL?

MySQL is developed and supported by MySQL AB, a Swedish Company, and is written in the C and C++ programming languages. It was created by Michael Widenius and David Hughes.

MySQL is a Relational Database Management System (RDBMS) that is available as free, open-source software under the GNU General Public License. It is a database management system that is fast, scalable, and simple to use. MYSQL is compatible with a variety of operating systems, including Windows, Linux, and macOS. MySQL is a Structured Query Language (SQL) that allows you to manipulate, manage, and extract data using various Queries. It is supported by Oracle Company.

Features:

• Relational Database Management System (RDBMS): MySQL is a relational database management system. The SQL queries are used to view and control the table’s records in this database language.
• Easy to use: MySQL is a simple database to work with. We just need to learn the fundamentals of SQL. Only a few simple SQL statements are needed to construct and interact with MySQL.
• It is secure: MySQL has a strong data protection layer in place to keep confidential data safe from intruders. In addition, MySQL encrypts passwords.
• Client/ Server Architecture: A client/server architecture is followed by MySQL. A database server (MySQL) and an unlimited number of clients (application programs) connect with the server, allowing them to query data, save changes, and so on.
• Free to download: MySQL is free to use, so we can get it for free from the MySQL official website.
• It is scalable: MySQL is modular since it allows multi-threading. It can deal with virtually any amount of data, up to 50 million rows or more. Approximately 4 GB is the default file size max. However, we can theoretically increase this number to 8 TB of data.
• Speed: MySQL is regarded as one of the fastest database languages, as shown by numerous benchmark tests.
• High Flexibility: MySQL is very versatile since it supports a wide range of embedded applications.
• Compatible on many operating systems: MySQL can run on a variety of operating systems, including Novell NetWare, Windows, Linux, UNIX (including Sun Solaris, AIX, and DEC UNIX), OS/2, FreeBSD, and others. MySQL also allows the clients to run on the same machine as the server or on a separate computer (communication via a local network or the Internet).
• Allows roll-back: MySQL supports rolled back, committed, and crash recovery transactions.
• Memory efficiency: Its performance is high due to the fact that memory leakage is minimal.
• High Performance: Because of its special storage engine architecture, MySQL is quicker, more efficient, and less expensive. It achieves very good performance outcomes as compared to other databases without sacrificing any of the software’s critical features. Because of the different cache memory, it has fast-loading utilities.
• High Productivity: Triggers, stored procedures, and views are all used in MySQL to help developers be more productive.
• Platform Independent: It can be downloaded, installed, and run on the majority of operating systems.
• Partitioning: This feature enhances efficiency and allows for quick management of large databases.
• GUI Support: MySQL offers the “MySQL Workbench,” a unified visual database graphical user interface tool for database architects, developers, and database administrators. MySQL Workbench includes SQL creation, data modeling, data migration, and server setup, user administration, backup, and other administration tools. MySQL Server versions 5.6 and higher provide full GUI support.
• Dual Password Support: MySQL version 8.0 supports dual passwords: one is the existing password, and the other is a backup password that we can use to switch to the new one. Learn More.

SQL vs MySQL:

SQL (Structured Query Language) is the standard language used for operating, managing, and accessing databases. SQL can be used to create programs that make changes to databases. SQL can also be used to build and change database schemes. One of SQL’s most appealing features is the ability to access multiple records in a database with a single order.

SQL:

• SQL is a language used for operating various relational databases.
• SQL is a query language.
• SQL is used to access, update, and manipulate the data stored in a database.
• SQL is used for writing queries for databases.
• SQL does not support connectors.
• SQL supports a single storage engine.
• In SQL, the server is independent of the database, that is, one can perform other operations on a database while it is being backed up.
• In SQL, external processes cannot access or manipulate the data directly.
  SQL is not an open-source language.

MYSQL:

• MySQL is the first open-source relational database.
• MySQL is a relational database that makes use of SQL to query a database.
• MySQL is a database that stores the existing data in a database in an organized fashion.
• MySQL helps in data storing, modifying, and management in a tabular format.
  - MySQL has an integrated tool — MySQL workbench — to facilitate the designing and building of databases.
• MySQL supports multiple storage engines as well as plug-in storage engines.
• In MySQL, one can perform a data backup by extract SQL statements.
• However, in MySQL, the server blocks the database during a data backup session, to avoid data corruption when switching from one MySQL version to another.
• In MySQL, with the use of binaries, one can easily manipulate and modify the database files during run time.
• It is an open-source platform.

Oracle vs MySQL

Oracle is a self-driving, self-securing, self-repairing relational database management system that eliminates error-prone manual database management. It runs on a variety of operating systems and allows for safe data storage and retrieval. Oracle, published in 1980 with simple SQL features, was the first database tool built for business purposes to handle data using a query language.

MYSQL:

• MySQL is open-source and free for all. It is licensed under the GNU.
• MySQL is compatible with Linux, UNIX, Windows, Mac OS X, z/OS, Symbian, BSD, and AmigaOS.
• MySQL does not support data partitioning.
• MySQL does not support distributed databases.

Oracle:

• Oracle is licensed for commercial purposes. Its express edition is free for students.
• Oracle is compatible with Linux, UNIX, Windows, Mac OS X, and z/OS.
• Oracle supports data partitioning.
• Oracle supports distributed databases.

Architecture:

The MYSQL architecture defines the relationship between the various components of the MYSQL system. Client-Server Architecture is used by MYSQL. It’s designed so that end-users, or clients, can use different networking services to access resources from a server computer. The main layers of MYSQL’s architecture are as follows:

• Client
• Server
• Storage

Learn More on some of the commonly asked interview questions on MYSQL here.

A logical view of the MySQL server architecture

Client:

In the diagram above, this is the topmost layer. With the support of the Client Layer, the Client sends request instructions to the Serve. The client submits a request using valid MYSQL commands and expressions via Command Prompt or GUI screen. The output is shown on the screen if the expressions and commands are true. The following are some of the most relevant client layer services:

• Connection Handling- When a client sends a request to the server, the server accepts it and connects the client. When a client connects to the server at that time, the link is given its own thread. All client-side queries are run with the aid of this thread.
• Authentication- When a client connects to an MYSQL account, authentication takes place on the server-side. The username, originating host, and password are used for authentication.
• Security- When a client connects successfully to MySQL server after authentication, the server verifies that the client has the permissions to run those queries against the MySQL server.

Server:

The logical functionalities of MYSQL’s relational database management framework are handled by the second layer of the MYSQL design. The “Brain of MYSQL Architecture” is another name for this layer of the MYSQL system. When a client sends a request to the server, the server responds with output as soon as the instruction is matched. The following are the different sub-components of the MYSQL server:

• Thread Handling- When a client sends a request to the server, the server accepts it and connects the client. When a client connects to the server at that time, the link is given its own thread. The thread management of the Server Layer provides this thread. The Thread Handling module also takes care of client-side queries that are run by the thread.
• Parser- A parser is a type of software component that creates a data structure (parse tree) from the given input. Lexical analysis is performed prior to parsing, in which the data is broken down into a number of tokens. After the data is accessible in smaller components, the parser performs Syntax and Semantics analysis and then generates a parse tree as an output.
• Optimizer- Once the parsing is complete, the Optimizer Block employs a variety of optimization techniques. These techniques may involve rewriting the query, scanning tables in a specific order, and selecting the appropriate indexes to use, among others.
• Query Cache- Query Cache saves the entire result set for the query statement that was entered. MYSQL Server consults the query cache before parsing. When a client writes a query, if the query in the cache matches the query written by the client, the server skips parsing, optimization, and even execution and simply displays the output from the cache.
• Buffer and Cache- The cache and buffer will save the user’s previous query or issue. When a user types a query, it first goes to the Query Cache, which checks to see if the same query or problem exists in the cache. If the same question is open, it will produce results without interfering with the Parser and Optimizer.
• Table Metadata Cache- The metadata cache is a section of memory that stores information about databases, indexes, and objects. The metadata cache grows in size as the number of available databases, indexes, or objects grows.
• Key Cache- An index entry that uniquely identifies an object in a cache is known as a key cache. Edge servers store content by default, based on the entire resource path as well as a query string.

Storage

MYSQL Architecture’s Storage Engine Layer distinguishes it as special and preferred by developers. As a result of this, the MYSQL layer is regarded as the most commonly used RDBMS. Different types of storage engines are used in MYSQL servers for different circumstances and requirements, such as InnoDB, MYiSAM, NDB, Memory, and so on. These storage engines are used as pluggable storage engines, with user-created tables being plugged in.

They’re in charge of storing and retrieving all of MySQL’s data. Each storage engine has its own set of advantages and disadvantages. The storage engine API is how the server interacts with them. At the query layer, this interface masks discrepancies between storage engines and renders them largely transparent. There are a few dozen low-level functions in the API that do things like “start a transaction” and “fetch the row with this primary key.”

• InnoDB Engine:

InnoDB is MySQL’s default transactional storage engine, as well as the most important and widely used. It was created to handle a large number of short-lived transactions that are normally completed rather than rolled back. It’s also common for non-transactional storage because of its performance and automatic crash recovery. Unless you have a good reason to use a different engine, you can use InnoDB for your tables.

The data in InnoDB is stored in a tablespace, which is a collection of one or more data files. A tablespace is basically a self-contained black box that InnoDB manages. InnoDB will store each table’s data and indexes in its own file in MySQL 4.1 and later versions. InnoDB can also build its tablespace from raw disc partitions, but modern file systems make this unnecessary.

Tables in InnoDB are based on a clustered index. Most other MySQL storage engines have index structures that are somewhat different from that of InnoDB’s. As a result, primary key lookups are extremely fast. Secondary indexes (non-primary key indexes) include the primary key columns, so if your primary key is high, your other indexes will be as well. If you’re going to have a lot of indexes on a table, you should aim for a small primary key. The storage format is cross-platform compatible.

Internally, InnoDB has a number of optimizations. Predictive read-ahead for prefetching data from the disc, an adaptive hash index that creates hash indexes in memory for very quick lookups, and an insert buffer to speed inserts are just a few of them.

• MyISAM Engine:

MyISAM, which was MySQL’s default storage engine in versions 5.1 and earlier, offers a wide range of capabilities, including full-text indexing, compression, and spatial (GIS) functions. Transactions and row-level locks are not supported by MyISAM. The fact that it isn’t even close to being crash-safe is arguably its greatest flaw. MyISAM is why, more than a decade after gaining transactions, MySQL still has the stigma of being a non-transactional database management system! It isn’t out of the question to use it if you need read-only data or if your tables aren’t big and won’t be difficult to fix.

Each table in MyISAM is normally stored in two files: a data file and an index file. The extensions of the two files are .MYD and .MYI, respectively. Dynamic or static (fixed-length) rows can be found in MyISAM tables. Based on the table specification, MySQL determines which format to use. The maximum number of rows a MyISAM table can hold is determined by the amount of disc space available on your database server and the largest file your operating system allows you to make.

• Other Built-In Engines

Archive Engine: Only INSERT and SELECT queries are supported by the Archive engine, and indexes are not supported until MySQL 5.1. Since it buffers data writes and compresses each row with zlib when it is inserted, it uses far less disc I/O than MyISAM. Each SELECT question also necessitates a complete table search. Archive tables are thus ideal for logging and data acquisition, where analysis typically scans the entire table, or where quick INSERT queries are needed.

Blackhole Engine: There is no storage device in the Blackhole engine. Instead of storing each INSERT, it discards it. The server, on the other hand, records queries against Blackhole tables so that they can be repeated or simply stored in the log.

CSV Engine: Comma-separated values (CSV) files can be treated as tables by the CSV engine, but indexes are not supported. While the server is running, this engine allows you to copy files into and out of the database. If you save a CSV file from a spreadsheet in the MySQL server’s data directory, the server will be able to read it right away. Similarly, if you write data to a CSV table, it can be read immediately by an external program. As a result, CSV tables are a useful data interchange format.

Federated Engine: This storage engine acts as a sort of intermediary between other servers. It establishes a client connection to a different server and runs queries against a table on that server, retrieving and sending rows as needed. It was presented at first as a rival to features found in many enterprise-grade proprietary database servers, such as Microsoft SQL Server and Oracle, but that was always a stretch.

Memory Engine: Memory tables (formerly known as HEAP tables) come in handy when you need quick access to data that never changes or doesn’t need to be saved after a restart. Memory tables can be hundreds of times faster than MyISAM tables. Since they keep all of their data in memory, queries don’t have to wait for disc I/O. A Memory table’s table structure survives a server restart, but no data is retained.

HASH indexes, which are very quick for lookup queries, are supported by memory tables. Memory tables are quick, but they aren’t always a good general-purpose substitute for disk-based tables. Table-level locking is used, resulting in low write concurrency. They don’t support TEXT or BLOB column types, and they only support fixed-size rows, so VARCHARs are stored as CHARs, wasting memory.

When processing queries that need a temporary table to store intermediate data, MySQL uses the Memory engine internally. MySQL can convert the intermediate result to a MyISAM table on disc if it becomes too large for a Memory table or contains TEXT or BLOB columns.

Merge Storage Engine: Merge is a variant of MyISAM. A Merge table combines several identical MyISAM tables into a single virtual table. This is useful when using MySQL in logging and data warehousing applications, but partitioning has been recommended instead.

NDB Cluster Engine: In 2003, MySQL AB purchased Sony Ericsson’s NDB database and developed the NDB Cluster storage engine as a bridge between MySQL’s SQL and the native NDB protocol. MySQL Cluster is a database that combines the MySQL server, the NDB Cluster storage engine, and the distributed, shared-nothing, fault-tolerant, highly accessible NDB database.

• Third-Party Storage Engines:

Since MySQL has a pluggable storage engine API, a bewildering variety of storage engines began springing up around 2007 to serve specific purposes. Some of these were pre-installed on the server, but the majority were third-party or open-source projects. Some of them are OLTP storage engines, column-oriented storage engines, and community storage engines.

Applications

Because of its high-performance query engine, distinctive memory caches, tremendously quick data insert functionality, and strong support for advanced web functions including fast full-text searches, MySQL has become the de-facto norm for high-traffic websites and business intelligence systems.

The MySQL database server is the pinnacle of scalability, with the ability to manage anything from deeply embedded applications with a 1MB footprint to large data centers with terabytes of data. MySQL is known for its platform versatility, which includes support for Linux, UNIX, and Windows. MySQL’s open-source design, of course, allows for full modification by those who wish to add custom specifications to the database server.

Corporations are realizing cost savings in the seven figures by migrating current database-driven applications to MySQL or using MySQL for new development projects. Corporations are discovering that by combining the MySQL database server with scale-out architectures that use low-cost commodity hardware, they can achieve incredible levels of scalability and efficiency at a fraction of the cost of proprietary and scale-up software vendors. Furthermore, MySQL’s dependability and ease of maintenance ensure that database administrators may focus on having a positive effect on higher-level activities involving the business side of data rather than wasting time troubleshooting performance or downtime issues.

Conclusion

MySQL’s architecture is layered, with server-wide resources and query execution on top and storage engines underneath. The storage engine API is the most significant of the many different plugin APIs. You’ve grasped one of the key fundamentals of MySQL’s architecture if you know that queries are executed by passing rows back and forth through the storage engine API.

Oracle’s acquisitions of InnoDB and then MySQL brought the two products together under one roof, allowing them to be produced together. This seems to be benefiting everyone: InnoDB and the server are improving by leaps and bounds in several respects, MySQL remains GPL’ed and completely open-source, the community and consumers are getting a strong and reliable database, and the server is becoming increasingly extensible and useful. Here’s a link covering the most commonly asked interview questions and concepts on MYSQL.