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

MySQL basic logical architecture diagram and InnoDB storage engine process is how, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

1. MySQL logical architecture 1. Logical architecture diagram

Based on the logical architecture diagram below, you can be familiar with the interworking relationship between the various architectural components of MySQL.

The classic Cmax S architecture style, that is, client / server mode.

2. Hierarchical description

Client connection

Usually carry out connection pool management, connection user rights authentication, security management and other operations.

You can view the connection configuration information with the following command: SHOW VARIABLES LIKE'% connect%'; can see related configurations such as the maximum connection and the memory consumed by each connection.

Core function

The second layer architecture encapsulates a series of core operations of MySQL, including query parsing, optimization, caching, built-in functions, triggers, views, etc., and functions across storage engines are implemented in this layer.

Storage engine

The lowest encapsulation of MySQL is also the core function. Different storage engines have different characteristics and functions, and what they have in common is to deal with data storage and extraction.

Concept introduction 1. Storage engine

MySQL database storage engine is the underlying architecture component of the database. The database management system uses the data engine to create, query, update and delete data. Different storage engines provide different storage mechanisms, indexing skills, locking levels and other functions, and different storage engines also have different characteristics and functions to meet the business needs in different scenarios.

2. Support relationship

You can view the current version of MySQL and the support for the storage engine through the following two commands.

SELECT VERSION (); SHOW ENGINES

You can see that the local environment is MySQL5.7 and supports the following storage engines:

The default storage engine in this version is InnoDB, which is the most rich and powerful, supporting transactions, distributed transactions, and transaction save points.

Common storage engine 1, InnoDB engine

(1) basic description

InnoDB engine is the default transactional engine of MySQL, which is widely used, is very good at dealing with short-term transactions, and has the characteristic of automatic crash recovery. It is generally required to be used in daily development.

(2), architecture diagram

InnoDB architecture diagram

The picture is from the MySQL official website document.

The whole is divided into three layers: memory structure, Cache, disk structure.

Memory structure

The memory structure also includes four major components.

Buffer Pool: buffer pool: an area of main memory that is cached when InnoDB accesses table and index data, greatly reducing disk IO operations and improving efficiency.

Change Buffer: write buffer: avoid IO operation every time you add, delete, modify and improve performance.

Adaptive Hash Index: adaptive hash indexing: use the prefix of the index keyword to build a hash index to improve query speed.

Log Buffer: log buffer: holds the data to be written to the log file on disk, and the contents of the buffer are flushed to disk periodically.

Disk structure

Tables: the physical structure of the data table.

Indexes: the physical structure of the index.

Tablespaces: table space, data storage area.

Data Dictionary: data dictionary, a table that stores metadata information, such as table description, structure, index, etc.

Doublewrite Buffer: a storage area in the system tablespace, and when InnoDB refreshes the page in BufferPool, the data page is written to the buffer before it is written to disk.

Redo Log: a log of DML operations for data recovery after a crash.

Undo Logs: a snapshot before the data is changed, which can be used to roll back the data.

(3) description of characteristics

Support transaction

When a set of SQL statements is executed within a transaction, either all succeed or all fail.

Support for distributed transactions

Distributed transaction means that even if different operations are on different service applications, it is still necessary to guarantee the characteristics of the transaction. Common scenarios: orders and inventory are in different services, but can be consistent.

Support for row-level locks

The locking mechanism for locking a row of data when locking is row-level locking (row-level). Only the InnoDB engine supports it in the MySQL5.7 version. If the granularity of locking is small, the concurrency supported naturally will be high, and the locking mechanism will become more complex.

Support for MVCC

Multi-version concurrency control is achieved by saving snapshots of data at a certain point in time. This means that no matter how long a transaction runs, you can see a consistent view of the data in the same transaction. Depending on the time the transaction starts, it also means that the data in the same table seen by different transactions at the same time may be different.

Support for clustered index

Is a method of reorganizing actual data on disk to sort by the value of one or more columns specified. Because the index page pointer of a clustered index points to the data page, using a clustered index to find data is almost always faster than using a non-clustered index.

2. MyISAM engine

(1) basic description

MySQL5.1 and previous versions of the default storage engine do not support transactions and row-level locks and cannot automatically recover after a natural crash.

(2) description of characteristics

Table locking mechanism

Lock the whole table, not for rows, read data with shared lock, write data with exclusive lock.

Full-text index

Support for full-text indexing, an index based on participle, which can support complex retrieval queries.

3. Other engines

In the MySQL system, InnoDB and MyISAM engines are most commonly used, and other various storage engines can be familiar with according to business needs.

Nagging: life is short, and the programming language is five horses and six roads, which is irritating, so we should pick the key points when learning, what is the key point, and what is the most frequently used is the key content.

Fourth, storage engine selection

In the company's development specifications, it is generally mandatory to use the InnoDB engine, unless the eccentric business InnoDB cannot support it.

Fifth, the source code address, GitHub address, https://github.com/cicadasmile/mysql-data-baseGitEE address, https://gitee.com/cicadasmile/mysql-data-base, is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.