Database Systems - MySQL Architecture Explained

The diagram shows the architecture layers of MySQL, which includes all the different components and their relationships to each other.

The software developers and DBAs interact with the database through MySQLConnector APIs and service layers that are above the storage engines. If there are new system requirements for your database that required you to change the underlying storage engine change, such as needing to add one or more storage engines, then the good news is that no process changes or significant coding is required to make the change. The MySQL server architecture provides a consistent API to protect your application from the underlying complexity of the storage engine.

The MySQL connection in your application provides the tools for sending queries to the query processor. The connectors layer includes APIs, MySQL Workbench, utility programs, and the command-line. The Command-Line Client processes SQL queries interactively, and is used by both database administrators and users.

The query processor has two main tasks.
It manages the pool of connections from many users. The connection is the users access to the database.
It compiles SQL queries into low-level instructions, called an execution plan, that the storage system can understand. While compiling the query parser checks for syntax errors, and the query optimizer reads the low-level instructions and uses relational algebra (described in an earlier video) to generates alternate execution plans to help speed up or optimize the database.

The cache manager stores reusable information in main memory, to provide faster database performance. For example, storing a query execution plans for queries uses many times, or saving query results for tables that don’t change much.

The storage engine layer provides transaction management and data access.
Transaction management includes the concurrency system, recovery system, and lock manager, and it also ensures that your transactions are atomic, consistent, isolated, and durable.
Data access translates table, column, and index reads into block addresses. It does this by communicating with the file system, and may also use a buffer manager to retain blocks for reuse to help reduce time (similar to a cache)

There are several options for the storage engine, with InnoDB being the most commonly used one, which managed the table indexes and interact with the file system. The file system contains user and system data, tables and index, log files, and a data dictionary (also called a catalog) which is a list of database objects including tables, columns, keys, and indexes.

If you’re a DBA and have access to the paid version of MySQL, called the MySQL Enterprise Edition, then you will have additional features such as a monitor to check the health of your database such as CPU and memory utilization. It also provides an audit which tracks all database changes, including sensitive data, and timestamp information. Additional Enterprise Edition components provide support for backup, security, firewall, and encryption.

Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!