MySQL database architecture details

Preface:

Many colleagues have been working for a long time, but their mastery of MySQL is only limited to the surface CRUD. They know very little about the deep principles and technical knowledge of MySQL. As their working years increase, their competitiveness in the workplace continues to decline. Many times, when going out for an interview, being beaten by the interviewer has become a common occurrence. For example, the most frequently asked interview questions for MySQL are:

• Can you explain the principle of MVCC mechanism in MySQL?
• What is the transaction principle and implementation of MySQL?
• What is the principle of MySQL data master-slave replication?
• What is the storage structure of MySQL's underlying data?
• How to enable MySQL to support massive data storage?
• What is the principle and implementation of the locking mechanism in MySQL?
• MySQL index mechanism? What data structures and algorithms are involved in indexing?
• Why does MySQL use B+ tree as index structure?
• How much data can a B+ tree store?
• Can you tell me about the implementation principle of gap lock in MySQL?
• Why does the InnoDB engine crash? Can you explain its implementation principle?
• Wait for a series of other high-frequency interviews
  

If you list them one by one, you can probably list hundreds of high-frequency interview questions about MySQL. Do you know all of them?

It's not just an interview. If you want to move up from a low-level programmer to a senior engineer, architect, etc., you must master the underlying principles and technologies of MySQL.

Note: Binghe will serialize articles on the underlying principles and technologies of MySQL from time to time in the future. He will work hard with his friends to share the underlying MySQL technologies that Binghe has mastered with everyone, so as to beat the interviewers in interviews and beat other friends at work.

1. MySQL Architecture

Let's first take a look at the MySQL architecture diagram, as shown below.

From the MySQL architecture diagram, we can see that the MySQL architecture can be roughly divided into four parts from top to bottom: network connection layer, database service layer, storage engine layer and system file layer. Next, let’s briefly talk about the composition information of each part.

2. Network Connection Layer

The network connection layer is located at the top of the entire MySQL architecture and mainly serves as a client connector. It provides the ability to establish a connection with the MySQL server and supports almost all mainstream server-side languages, such as Java , C , C++、Python , etc. Each language establishes a connection with MySQL through its own API interface.

3. Database Service Layer

The database service layer is the core of the entire database server, and mainly includes system management and control tools, connection pool, SQL interface, parser, query optimizer and cache.

4. Connection Pool

It is mainly responsible for storing and managing the connection information between the client and the database. A thread in the connection pool is responsible for managing the connection information from a client to the database.

5. System Management and Control Tools

Provides management and control functions for the database system, such as backing up and restoring data in the database, ensuring the security of the entire database, providing security management, coordinating and managing the entire database cluster, etc.

6. SQL interface

It is mainly responsible for receiving various SQL commands sent by the client, sending the SQL commands to other parts, receiving the result data returned by other parts, and returning the result data to the client.

7. Parsing Tree

It is mainly responsible for parsing the requested SQL into a "parse tree", and then further grammatically verifying the "parse tree" according to some rules in MySQL to confirm whether it is legal.

Query Optimizer

In MySQL, if the "parse tree" passes the syntax check of the parser, it will be converted into an execution plan by the optimizer, and then interact with the storage engine, and interact with the underlying data files through the storage engine.

9. Cache

MySQL's cache is composed of a series of small caches. For example: MySQL table cache, record cache, permission cache in MySQL, engine cache, etc. The cache in MySQL can improve data query performance. If the query result can hit the cache, MySQL will directly return the result information in the cache.

10. Storage Engine Layer

The storage engine layer in MySQL is mainly responsible for writing and reading data and interacting with the underlying files. It is worth mentioning that the storage engine in MySQL is plug-in-based. The query execution engine in the server communicates with the storage engine through relevant interfaces. At the same time, the interface shields the differences between different storage engines. In MySQL, the most commonly used storage engines are InnoDB and MyISAM.

InnoDB and MyISAM storage engines are important for students to master. They are frequently tested in interviews and are also what you must know to become an architect .

11. System File Layer

The system file layer mainly includes the underlying files that store data in MySQL, interacts with the upper-level storage engine, and is the physical storage layer of the files. The files it stores mainly include: log files, data files, configuration files, MySQL pid files and socket files, etc.

12. Log Files

The logs in MySQL mainly include: error log, general query log, binary log, slow query log, etc.

1. Error log

It mainly stores error information generated during the operation of MySQL. You can use the following SQL statement to view the error log in MySQL.

show variables like '%log_error%';

2. General query log

It mainly records general query information during MySQL operation. You can use the following SQL statement to view the general query log file in MySQL.

show variables like '%general%';

3. Binary log

It mainly records the insert, modify, and delete operations performed on the MySQL database, and also records the execution time and execution duration of the SQL statement. However, the binary log does not record SQL statements such as select and show that do not modify the database. Mainly used to restore database data and implement MySQL master-slave replication.

Check whether binary logging is enabled.

show variables like '%log_bin%';

View binary log parameters

show variables like '%binlog%'

Viewing log files

show binary logs;

4. Slow query log

Slow queries mainly record SQL statements whose execution time exceeds the specified time. The default time is 10 seconds.

Check whether the slow query log is enabled

show variables like '%slow_query%';

View the duration of slow query settings

show variables like '%long_query_time%'

13. Data Files

The data files mainly include: db.opt file, frm file, MYD file, MYI file, ibd file, ibdata file, ibdata1 file, ib_logfile0 and ib_logfile1 files, etc.

1.db.opt file

Mainly records information such as the character set and verification rules used by the current database.

2. frm file

Stores the structural information of the data table, mainly the metadata information related to the data table, including the table structure definition information of the data table. Each table will have an frm file.

It is worth noting that the table of the innodb storage engine in MySQL 8 version does not have a frm file. (I will write some articles about the new features of MySQL 8 later, from usage to underlying principles and how they differ from MySQL 5).

3. MYD file

A file format dedicated to the MyISAM storage engine. It mainly stores data in the MyISAM storage engine data table. Each MyISAM storage engine table corresponds to a .MYD file.

4. MYI File

A file format dedicated to the MyISAM storage engine, which mainly stores index information related to the MyISAM storage engine data table. Each MyISAM storage engine table corresponds to a .MYI file.

5.ibd file

Stores the data files and index files of the Innodb storage engine. It mainly stores the data and indexes of the exclusive tablespace. Each table corresponds to a .ibd file.

6. ibdata file

Stores data files and index files of the Innodb storage engine. It mainly stores data and indexes of shared tablespaces. All tables share one (or more) .ibdata files. The number of shared .ibdata files can be specified according to the configuration.

7. ibdata1 file

MySQL system tablespace data files mainly store MySQL data table metadata, Undo logs and other information.

8. ib_logfile0 and ib_logfile1 files

Redo log files in the MySQL database are mainly used to implement transaction persistence in MySQL. If MySQL fails at a certain point in time, and if there are dirty pages that have not been written to the database's ibd file, when MySQL is restarted, MySQL will redo the data based on the Redo Log information and make the data that has been written to the Redo Log but not yet written to the data table persistent.

14. Configuration File

Used to store all MySQL configuration information. In Unix/Linux environment, it is the my.cnf file, and in Windows environment, it is the my.ini file.

1.pid file

The pid file is a file that stores the process ID of the MySQL process when it is running. It mainly exists in the Unix/Linux environment. The specific storage directory can be configured in the my.cnf or my.ini file.

2. Socket file

The socket file, like the pid file, is a file that exists only when MySQL is running in a Unix/Linux environment. In Unix/Linux environments, clients can connect to MySQL directly through sockets.

This is the end of this article about the details of MySQL architecture. For more relevant MySQL architecture content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!