Shulou(Shulou.com)05/31 Report--

This article focuses on "what is the MySQL architecture", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is MySQL architecture"?

Write at the front

Many partners have been working for a long time, but their mastery of MySQL is only superficial CRUD, and they know very little about the deep-seated principles and technical knowledge of MySQL. With the continuous growth of working years, the competitiveness of the workplace continues to decline. Most of the time, when you go out for an interview, the phenomenon of being hanged by the interviewer has become a common occurrence. For example, the high-frequency interview questions for MySQL are:

What is the principle of MVCC mechanism in MySQL?

The transaction principle and implementation of MySQL?

What is the principle of MySQL data master-slave replication?

What is the storage structure of MySQL underlying data?

How to make MySQL support mass data storage?

The principle and implementation of locking mechanism in MySQL?

MySQL indexing mechanism? What are the data structures and algorithms involved in the index?

Why does MySQL use the B+ tree as the index structure?

How much data can be stored in a B + tree?

Talk about the implementation principle of MySQL gap lock?

Why did the InnoDB engine crash? What is the principle of its implementation?

Wait, a series of other high-frequency interviews.

If you list one by one, you can probably list hundreds of high-frequency interview questions about MySQL. Can you do all of these?

Not just an interview, if you want to rise from a low-level programmer to a senior engineer, architect, etc., you must master the underlying principles and techniques of MySQL.

Note: glacier will serialize articles on the underlying principles and technologies of MySQL from time to time, bang on MySQL with friends, share with you the underlying MySQL skills mastered by Glacier, hang the interviewer during the interview and other partners at work.

The article has been included in:

Https://github.com/sunshinelyz/technology-binghe

Https://gitee.com/binghe001/technology-binghe

MySQL architecture

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

Note: the picture is from the Internet.

From the architecture diagram of MySQL, we can see that the architecture of MySQL can be 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 of each part.

Network connection layer

The network connection layer is located at the top of the entire MySQL architecture and mainly acts as a client connector. Provides the ability to establish a connection with the MySQL server, and supports almost all mainstream server languages, such as Java, C, C++, Python, etc., all of which establish a connection with MySQL through their own API interfaces.

Database service layer

Database service layer is the core of the whole database server, which mainly includes system management and control tools, connection pool, SQL interface, parser, query optimizer, cache and so on.

Connection pool

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

System management and control tools

Provide the management and control functions of the database system, such as backing up and restoring the data in the database, ensuring the security of the whole database, providing security management, coordinating and managing the cluster of the whole database and so on.

SQL interface

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

Analytic tree

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

Query optimizer

In MySQL, if the parse tree passes the parser's syntax check, the optimizer converts it into an execution plan, and then interacts with the storage engine, which interacts with the underlying data files.

Caching

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

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, and 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 need friends to focus on mastering, high-frequency interview test sites, but also to become an architect must know the content.

System file layer

The system file layer mainly includes the underlying files that store data in MySQL, which interacts with the upper storage engine and is the physical storage layer of files. The main files stored are: log files, data files, configuration files, MySQL pid files and socket files and so on.

Log file

The logs in MySQL mainly include: error log, general query log, binary log, slow query log and so on.

Error log

The main storage is the error messages 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%'

General query log

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

Show variables like'% general%'

Binary log

It mainly records the insert, modify and delete operations to the MySQL database, and also records the time and time of execution of SQL statements, but the binary log does not record the SQL of select, show and other databases that do not modify the database. It is mainly used to recover the data of the database and to realize MySQL master-slave replication.

Check to see if the binary log is on.

Show variables like'% log_bin%'

View the parameters of the binary log

Show variables like'% binlog%'

View log files

Show binary logs

Slow query log

Slow queries mainly record SQL statements that take more than a specified time to execute, which defaults to 10 seconds.

Check to see if slow log is enabled

Show variables like'% slow_query%'

Check the length of time set for slow query

Show variables like'% long_query_time%'

Data file

Data files mainly include: db.opt file, frm file, MYD file, MYI file, ibd file, ibdata file, ibdata1 file, ib_logfile0 and ib_logfile1 file and so on.

Db.opt file

It mainly records the information such as the character set and verification rules used in the current database.

Frm file

Store the structure 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 a frm file.

It is worth noting that the table of the innodb storage engine in the MySQL8 version does not have an frm file. (the following articles are devoted to some of the new features of MySQL8, from use to the underlying principles of how it is different from MySQL5.)

MYD file

MyISAM storage engine specific file format, mainly stores the data in the MyISAM storage engine data table, each MyISAM storage engine table corresponds to a .MYD file.

MYI file

MyISAM storage engine specific file format, mainly stores index information related to MyISAM storage engine data tables, each MyISAM storage engine table corresponds to a .myi file.

Ibd file

Storing the data files and index files of the Innodb storage engine mainly stores the data and indexes of the exclusive tablespace, and each table corresponds to an .ibd file.

Ibdata file

Storing the data files and index files of the Innodb storage engine mainly stores the data and indexes of the shared tablespace. All tables share one (or more) .ibdata files. You can specify the number of shared .ibdata files according to the configuration.

Ibdata1 file

MySQL system tablespace data file, mainly stores MySQL data table metadata, Undo log and other information.

Ib_logfile0 and ib_logfile1 files

The Redo log file in the MySQL database is mainly used for MySQL to achieve transaction persistence. If MySQL fails at a certain point in time, if a dirty page is not written to the ibd file in the database, when MySQL is restarted, MySQL will redo it according to the Redo Log information and persist the data that is written to Redo Log and has not been written to the data table.

Configuration file

Used to have all the configuration information for MySQL, which is a my,cnf file in a Unix/Linux environment and a my.ini file in a Windows environment.

Pid file

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

Socket file

Socket files, like pid files, are only available when MySQL runs in a Unix/Linux environment. In a Unix/Linux environment, clients can connect to MySQL directly through socket.

At this point, I believe you have a deeper understanding of "what is the MySQL architecture". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.