Writing in the front

Many friends have worked for a long time, but their knowledge of MySQL is just CRUD on the surface, and they have little understanding of the deep principles and technical knowledge of MySQL. With the growth of working years, their competitiveness in the workplace is constantly decreasing. A lot of times, when you go out for an interview, you get slapped by the interviewer. For example, frequent interview questions for MySQL include:

  • How does MVCC work in MySQL?
  • MySQL transaction principle and implementation?
  • How does primary/secondary replication work?
  • What is the storage structure of MySQL’s underlying data?
  • How to make MySQL support mass data storage?
  • MySQL lock mechanism principle and implementation?
  • MySQL > select * from ‘MySQL’; What data structures and algorithms are involved in the index?
  • Why does MySQL use B+ tree as index structure?
  • How much data can a B+ tree hold?
  • How to implement gap lock in MySQL?
  • Why does InnoDB engine crash? How does that work?
  • And a series of other frequent interviews

If you could list one by one, you could probably list hundreds of common interview questions about MySQL. Do you know all of them?

Not only interview, if you want to move from a low-level programmer to a senior engineer, architect, etc., the underlying principles and techniques of MySQL are something you must master.

Note: In the future, He will publish articles on the underlying principles and technologies of MySQL from time to time. He will share the underlying technologies of MySQL with his friends. He will beat the interviewer in the interview and beat other friends at work.

MySQL Architecture

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

From the architecture diagram of MySQL, we can see that the architecture of MySQL 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 talk briefly about the information that makes up each part.

Network connection layer

The network connection layer is located at the top of the whole MySQL architecture and mainly acts as the client connector. It provides the ability to connect to MySQL server. It supports almost all mainstream server languages, such as Java, C, C++, Python, etc. Each language connects to MySQL through their respective API interfaces.

Database Service Layer

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

The 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 between a client and the database.

System management and control tools

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

SQL interface

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

The parse tree

It is mainly responsible for parsing the requested SQL into a “parse tree”, and then further 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 parser’s syntax check, the optimizer converts it into an execution plan, which then interacts with the storage engine, which interacts with the underlying data files.

The cache

The MySQL cache consists of a series of small caches. For example: MySQL table cache, record cache, MySQL permission cache, engine cache, etc. The cache in MySQL improves the query performance. If the query result matches the cache, MySQL directly returns the result 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. The query execution engine in the server communicates with the storage engine through the relevant interface, and the interface shields the differences between different storage engines. The most common storage engines in MySQL are InnoDB and MyISAM.

InnoDB and MyISAM storage engines need to be mastered by friends. It is also a must for architects to know.

System file layer

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

The log file

Logs in MySQL include error logs, general query logs, binary logs, and slow query logs.

  • The error log

It stores error information generated during the running of MySQL. You can use the following SQL statement to view error logs in MySQL.

show variables like '%log_error%';
Copy the code
  • General query log

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

show variables like '%general%';
Copy the code
  • Binary log

The system records the insert, modify, and delete operations performed on the MySQL database. It also records the execution time and execution duration of SQL statements. However, binary logs do not record SQL statements that do not modify the database, such as SELECT and show. It is used to restore database data and implement MySQL master-slave replication.

Check whether binary log is enabled.

show variables like '%log_bin%';
Copy the code

View the parameters of binary logs

show variables like '%binlog%'
Copy the code

Viewing log Files

show binary logs;
Copy the code
  • Slow Query logs

Slow query records the SQL statements that take longer than the specified execution time, which is 10 seconds by default.

Check whether slow log query is enabled

show variables like '%slow_query%';
Copy the code

View the duration of slow query

show variables like '%long_query_time%'
Copy the code

The data file

Data files include db.opt files, FRM files, MYD files, MYI files, IBD files, ibData files, ibDATA1 files, ib_logFILE0 and IB_logFILe1 files.

  • Db. Opt file

Records the character set and verification rules used by the current database.

  • FRM file

Stores the structure information of data tables, mainly the metadata information related to data tables, including the table structure definition information of data tables. Each table has an FRM file.

Note that innoDB storage engine tables in MySQL8 do not have FRM files. (I’ll write about some of the new MySQL8 features later, from how they work to how they differ from MySQL5.)

  • MYD file

The file format is dedicated to the MyISAM storage engine, which stores data in the MyISAM storage engine data table. Each MyISAM storage engine table has one file. MYD file.

  • MYI file

MyISAM storage engine specific file format, mainly stores index information related to MyISAM storage engine data table, each MyISAM storage engine table corresponds to one. MYI file.

  • Ibd file

Innodb storage engine data files and index files, mainly stores the data and index of the exclusive table space, each table corresponds to an. Ibd file.

  • Ibdata files

Innodb storage engine data files and index files, mainly stores the data and index of the shared tablespace, all tables share one (or more). Ibdata files, you can specify the number of shared.

  • Ibdata1 file

MySQL system tablespace data file, which stores MySQL table metadata and Undo logs.

  • Ib_logfile0 and ib_logFile1 files

The Redo log file in the MySQL database is used to implement transaction persistence. If the MySQL database fails at a certain point in time and dirty pages are not written to the IBD file of the database, when the MySQL database restarts, the MySQL database rewrites the Redo Log data based on the Redo Log information and persists the data that has not been written to the database tables.

The configuration file

It is used to store all MySQL configuration information, which is my, CNF file in Unix/Linux environment, and my.ini file in Windows environment.

The pid file

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

The socket file

Socket files and PID files are generated only when MySQL runs in Unix/Linux. In Unix/Linux, clients can connect to MySQL directly through sockets.

Liver is not moving, the original is not easy, small partners point a praise, to see and forward it

If you have any questions, you can leave a comment below or add me to wechat: SUN_shine_LYz. I will pull you into the group. We can exchange technology together, advance together, and make great force together