The following content applies to Mysql 5.7

The biggest difference between Mysql and other relational database systems (such as Oracle, Sql Server, etc.) is that storage engine and database server are loosely coupled. Storage engine appears in Mysql Server in the form of plug-in. You can use different storage engines in different application scenarios. For example, the default InnoDB storage engine supports transactions and row-level locks. MyISAM does not support transactions and row-level locks. One of the most important storage engines is InnoDB.

However, due to this open plug-in storage engine, file consistency is greatly reduced. When it comes to SQL execution optimization, there are inevitable bottlenecks. It is weak in multi-table association, sub-query optimization, statistical functions, and only supports very simple hints.

1. Physical architecture of Mysql

The physical architecture of Mysql is the structure of storing files. Different files store different types of data and are stored on disks in different ways.

Files are divided into two main groups: Mysql base directory and Mysql data directory.

1.1 Mysql Base Directory

The base directory mainly contains program log files, program executable files.

  • The MySQL PID file records the PID of the current mysqld Process. Pid is the Process ID.

  • There are two ways to connect to MySQL:

    (1) TCP/IP (2) Socket. For mysql.sock, it is used when the mysql client program mysql and the mysqlserver program mysqlServer are on the same machine to initiate a local connection. It is faster than TCP.

  • The Mysql command-line Client is a simple SQL shell with input Line editing capabilities.

Such as:

shell> mysql --user=user_name --password db_nameEnter password: your_passwordCopy the code

The SQL statement can then be executed.

  • Mysqld is also known as the mysql server. In the mysql installation process as the main program to complete many tasks. Mysql server manages data directory access to mysql. Mysqld execution can specify a number of options, such as:

shell> mysqld --verbose --helpCopy the code

  • mysqladmin is a client for performing administrative operations. Can be used to check the configuration and current status of the server, create or DROP DATABASE. Such as:

shell> mysqladmin [options] command [command-arg] [command [command-arg]] ...Copy the code

1.2 Mysql Data Directory

The data directory contains log files and data files.

1.2.1 Log Files

There are error log, binary log, Query log, slow Query log, InnoDB redo log, InnoDB undo log, etc.

  • Error log: records critical errors generated during the startup, shutdown, and running of mysql.

  • Binary log(write log after SQL execution, disabled by default) :

  1. Records all updates to the database and potential update statements. Statements are stored in the form of events, and record the statement occurrence time, execution duration, operation data, and so on.

  2. It does not log statements that do not modify any data. If you want to log all statements, you can use the Query log.

  3. Its main purpose is to update the database as completely as possible during a restore operation, since it contains all updates after a backup. It is also used in the Master Replication Server as a record sent to slave Servers;

  4. Turning on binary logging costs 1% of performance, but the benefits far outweigh these;

  5. To check whether binary logs are enabled, check whether the log_bin parameter is ON: >show variables like ‘binlog_format’;

  6. There are three different formats available: Mixed,Statement, and Row. The default format is Statement.

  7. Restart the mysql service or run mysql> flush logs each time; A new binary log file is generated, and the number of these log files is constantly increasing. In addition to the filename.number file, filename.index file is automatically generated.

  • general query log:

  1. General Query logs Record the logs of all operations performed during server running. When the client is connected or disconnected, the server logs information and records all SQL statements received from the client. It is helpful for debugging errors and checking which client sent the command.

  2. Mysqld logs statements as commands are received, which may be different from the order in which they are executed (as opposed to binary logs, which are logged after execution).

  • slow query log:

  1. Used when tuning, to record SQL statements that exceed the specified time;

  2. Slow query logs record SQL statements whose execution time exceeds the specified time of the long_QUERy_time variable. This time does not include the lock acquisition time, but only the execution time. The default time is 10s(long_QUERy_time =10.0).

  3. MySQL also provides a utility program called mysqlDumpslow that specializes in analyzing slow query logs

  • Innodb Redo log innoDB data engine

  1. Innodb table space data is not written to disk due to a power outage. Redo log can be used to recover data.

  2. To improve INNODB’S I/O performance, the InnoDB engine loads data and indexes into a buffer pool in memory Pool), if every time modify the data and index needs to be updated to disk, is bound to increase the I/o requests, and because each update position are random, head need to frequent orientation lead to efficiency is very low, so the innodb after each processing a transaction only add a log log sequence (IO), in addition, there are a thread is responsible for reading the log files Batch update to disk (random I/O) to achieve the most efficient disk write;

  3. The redo log is written by the storage engine and the binary log is written by the database server.

1.2.2 Data files

There are mainly data files and index files. Such as:

The data files of each MySQL database are stored in a folder with the same name as the database (in the directory corresponding to the datadir parameter). The MySQL database files include the database files created by MySQL and the database files created by the storage engine used by MySQL.

MySQL if the MyISAM storage engine is used, the database file types include. FRM,.MYD, and.MYI

If MySQL uses InnoDB storage engine, the database file types include. FRM, ibData1, and. Ibd, and the ibdata1 and. Ibd files are stored in the data folder of MySQL installation directory by default.

  • .frm file: frame structure for storing data tables. The file name is the same as the table name. Each table corresponds to an FRM file with the same name, independent of the operating system and storage engine.

In addition to the mandatory.frm files, storage engines create different database files depending on the storage engine used by MySQL (MyISAM and InnoDB are two common storage engines used by MySQL).

  • MYD file: MY Data, table Data file

  • MYI file: MY Index file

  • Log files: log files

The table space contains all data and index information for tables using the InnoDB engine, as well as undo logs required for transaction rollback. Log files record information about committed transactions and are used to prevent data loss. By default, table Spaces and log files are stored in the data directory.

InnoDB table space structure:

The management of InnoDB storage engine is completed by the engine itself, and the Tablespace is composed of scattered segments. Common segments are data segment, index segment, rollback segment, and so on. InnoDB storage engine tables are index organized, so data is index and index is data. The data segment is the page node of the B+ tree (leaf node segment) and the index segment is the non-index node of the B+ tree (non-leaf node segment).

A Segment consists of multiple extents. Extents consist of 64 consecutive pages (some documents are also called blocks). Each Page size is 16K, that is, each Extent size is 1MB. When creating a new table, a 32-page fragment is used to store data before an Extent is applied. Ensure sequential performance of data)

  • The undo log

Used to store data changes the value of the modified before that changes the t1 id = 1 row of data in the table, put the Name = ‘A’ is modified to Name = ‘A2’, then undo log will be used to store Name = ‘A’ record of the abnormal, if this change can use undo log to realize A rollback operation, Ensure transaction consistency.

Data change operations are mainly from INSERT UPDATE DELETE, while UNDO LOG is divided into two types. One is INSERT_UNDO (INSERT operation), which records the unique key value of the INSERT. One is UPDATE_UNDO (including UPDATE and DELETE operations), which records the unique key value changed and the old column record.

A total of 128 Rollback segments are stored in the IBData tablespace from RESG SLOt0 to RESG SLOt127. Each RESG slot contains one or more Rollback segments. It consists of 1024 undo segments.

UNDO manages consistent reads of the current active transaction [MVCC] by retaining modified uncommitted data. Unmodified data can be retrieved from this storage area. Undo logs are also called rollback segments.

2. Logical architecture of Mysql

The logical architecture of Mysql is a typical C/S system, as shown in the following figure.

2.1 the Client layer

The uppermost layer serves the client connection and is used for different languages to interact with SQL. It can be done through:

> show variables like '%connections%'Copy the code

Command to check the maximum number of connections of a MySQL instance and a single user.

2.2 Server layer

MySQL Server logical system architecture is divided into three layers:

  1. The application layer

  2. The MySQL service layer

  3. Storage engine layer

2.2.1 application layer

The application layer is the top layer of MySQL architecture. Like other client-server architectures, it mainly contains the following contents:

When a client sends a connection request to the server, the MySQL Server allocates a thread from the thread pool to connect to the client. In the future, requests from the client will be allocated to this thread. To improve performance, MySQL Server provides thread pools that reduce the time it takes to create and release threads.

2. User authentication After the client initiates a connection request to the MySQL server, the MySQL server authenticates the user based on the user name, client host ADDRESS, and user password

3. Security Management After a customer connects to the MySQL Server, the MySQL Server determines the operations that can be performed by the user based on the user permissions.

2.2.2 MySQL Service Layer

This layer is the core layer of MySQL Server and provides all logical functions of MySQL Server database system. This layer can be divided into the following different components:

  • MySQL Management Server & Utilities

  • SQL Interface

  • SQL Parser SQL Parser

  • Query Optimizer

  • Caches & Buffers

MySQL Management Server & Utilities provides a wide range of database Management functions.

  • Database backup and restore

  • Database security management, such as user and authority management

  • Database Replication Management

  • Database Cluster Management

  • Database partition, sub-library, sub-table management

  • Database metadata management

2. SQL Interface The SQL Interface receives SQL commands from users and processes them to obtain the results required by users. The specific processing functions are as follows:

  • Data Manipulation Language (DML).

  • Data Definition Language (DDL).

  • The stored procedure

  • view

  • The trigger

SQL Parser parses query statements and generates syntax trees. First, the parser parses the query and returns an error message if the query syntax is incorrect. After the syntax check passes, the parser queries the cache, and if there is a statement in the cache, returns the result without further optimization. Parser generates SQL_ID for each query statement.

Note: The cached data is not inconsistent with the real data, because the cached data is modified and removed from the cache.

The purpose of the Optimizer is to optimize the query statement, including rewriting the query, determining the order in which the tables are read, and selecting the appropriate index.

The optimizer doesn’t care what storage engine the table uses, but storage engines can affect optimization. The optimizer asks the storage engine for capacity or specific overhead information for an operation as well as statistics on table data.

5. Caches & buffers include global and engine specific Caches to improve query efficiency. If there is a matching query result in the query cache, the query statement can fetch data from the cache without parsing and execution. This caching mechanism consists of a series of small caches, such as table cache, record cache, key cache, permission cache, and so on.

How does Query Cache work

Query Cache is shared between sessions and executes queries based on SQL_ID. Only identical SQL statements are considered equal, byte for byte comparison.

The cache does not take effect in the following scenarios:

  • Queries that are a subquery of an outer query

  • Queries executed within the body of a stored function, trigger, or event

The flow of SQL execution is as follows:

2.2.3 Storage Engine Layer

Storage engine Storage engine is the subsystem of MySQL that deals with files. It is also the most distinctive feature of MySQL. The most important feature that sets MySQL apart from other databases is its plug-in table storage engine. He customized a file access mechanism (called the storage engine) based on the abstract interface of the file access layer provided by MySQL AB.

Servers communicate with storage engines through apis that mask the differences between storage engines.

2. Physical files Physical files include redolog, undolog, binlog, errorlog, querylog, slowlog, data, and index.

InnoDB storage engine architecture:




Reference Documents:

https://www.rathishkumar.in/2016/04/understanding-mysql-architecture.html

https://lalitvc.wordpress.com/2016/11/03/mysql-architecture-and-components/

https://blog.csdn.net/crpp0902/article/details/77249279

https://blog.51cto.com/zpf666/1910497

https://www.cnblogs.com/wade-luffy/p/6288656.html

https://zhuanlan.zhihu.com/p/43736857

https://www.jianshu.com/p/7d1552883289

https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html

                                                              — END —

Welcome to pay attention to the message, send a letter exchange ~