MySQL – InnoDB schema

Time:2022-1-8

background

MySQL version: MySQL 8.0 0
Reference: MySQL official website

1、 InnoDB official architecture

MySQL - InnoDB schema

image.png

2、 Brief description

InnoDbThe architecture is divided intoMemory structureandDisk structure

1. Memory structure

1.1 buffer pool

buffer poolIs an area in main memory,InnoDBCache here when accessing table and index data.buffer poolAllows direct access to commonly used data from memory, increasing processing speed. On a dedicated server, up to80%Physical memory is usually allocated to the buffer pool.

For more information about buffer pool, please refer to-MySQL – InnoDB – understand buffer pool

1.2 change buffer

change bufferIt’s a kind ofSpecial data structure, whenSecondary index pagebe not inbuffer pooleWhen in, it caches changes toSecondary index page。 Buffered changes (possibly byINSERTUPDATEorDELETEoperation(DML)Cause) merge later when pages are loaded into the buffer pool by other read operations.

For more information about buffer pool, please refer to-MySQL – InnoDB – understand change buffer

1.3 adaptive hash index

adaptive hash index Make InnoDBCan be inAppropriate workload combinationandBuffer pool enough memoryIt is more like an in memory database without sacrificing transaction characteristics or reliability. Adaptive hash index passinnodb_adaptive_hash_indexVariable is enabled or passed when the server starts——skip-innodb- adaptive_hash -indexclose.

Detailed introduction address: to be continued

1.4 log buffer

log bufferAn area is an area of memory that stores data to be written to a disk log file.log bufferSize byinnodb_log_buffer_sizeVariable definition. The default size is16MB。 Regularlylog bufferRefresh the contents of to disk. largelog bufferEnables large transactions to run without having to commit the transaction before it is committedredo logWrite data to disk. Therefore, if you have transactions that update, insert, or delete many rows, addlog bufferThe size of can save disk I / O.

Detailed introduction address: to be continued

2. Disk structure

2.1 tables

Detailed introduction address: to be continued

2.2 indexes

Detailed introduction address: to be continued

2.3 tablespace

TablespaceContainsSYSTEM tablespaceIndependent table spaceUniversal table spaceTemporary tablespaceas well asUndo tablespace

2.3.1 SYSTEM tablespace:

a、SYSTEM tablespaceMainlychange bufferStorage area of;

b. If the table is inSYSTEM tablespaceCreated in instead of inIndependent table spaceperhapsCreate in a common tablespaceYes, thenSYSTEM tablespaceThe created is also stored inTables and indexesdata

c. Inmysql8.0Before version,mysqlofdata dictionaryAlso stored inSYSTEM tablespaceOf course, but8.0After the version,mysqlMerged oneTransactional data dictionary, metadata stored indata dictionaryMedium;

d. InMySQL 8.0.20Before,double write bufferThe storage area is located in InnoDBSYSTEM tablespaceYes. fromMySQL 8.0.20Start,double write bufferStorage area atdouble writeIn the document;

Detailed introduction address: to be continued

2.3.2 independent tablespaces

Independent table spaceInnoDB is included inTable data and index information, stored inSingle independent tablespace filein

Detailed introduction address: to be continued

2.3.3 common tablespaces

Universal table spaceRefers to the use ofCREATE tablespaceSyntax createdShared InnoDB tablespace

Detailed introduction address: to be continued

2.3.4 temporary tablespaces

Used in InnoDBTemporary tablespaceDivided intoSession temporary tablespaceandGlobal temporary tablespace

Detailed introduction address: to be continued

2.3.5 undo tablespaces

undo tablespace containUndo logUndo logIs a collection of records that contain information about how to undo the latest changes made by a transaction to a clustered index record.

Detailed introduction address: to be continued

2.4 double write buffer

double write bufferIs a storage area in which InnoDB writes from before writing pages to an appropriate location in the InnoDB data filebuffer poolPage refreshed in. If an operating system, storage subsystem, or unexpected error occurs during page writingMysqld process exit, InnoDB can recover from a crash during recoverydouble write bufferFound a good copy of the page in.

Although the data is written twice, thedouble write bufferIt does not require twice the I / O overhead or twice the I / O operations. Data is written to the doublewrite buffer in a large sequential block through a single fsync () call to the operating system (unlessinnodb_flush_methodSet toO_DIRECT_NO_FSYNC)。

stayMySQL 8.0.20Before,double write bufferThe storage area is located in InnoDBSYSTEM tablespaceYes. fromMySQL 8.0.20Start,double write bufferStorage area atdouble writeFile.

Detailed introduction address: to be continued

2.5 redo log

redo logIs a disk based data structure used to correct data written by incomplete transactions during crash recovery. During normal operation,redo logEncode table data change requests generated by SQL statements or low-level API calls. Modifications that did not complete the data file update before the unexpected shutdown will be automatically replayed during initialization and before accepting the connection. aboutredo logRole in crash recovery, seeSection 15.18.2 "InnoDB recovery"

By default,redo logPhysically, it consists of twoib_logfile0andib_logfile1File representation of. MySQL writes in a circular mannerredo logFile.redo logThe data in the are encoded according to the affected records; These data are collectively referred to as redo. Data passredo logThe channel is represented by increasing LSN values.

Detailed introduction address: to be continued

2.6 undo logs

undo logIs associated with a single read-write transactionundo logA collection of records.Undo recordContains information about how to undo the latest changes made by a transaction to a clustered index record.

Detailed introduction address: to be continued

Unfinished to be continued

Recommended Today

The whole tutorial of docker installation and use, installation / complete command / dockerfile image production / docker container arrangement and one click installation of nginx + redis + MySQL / visualizer portal (version 2022)

官网: https://docs.docker.com/ 官网: https://www.docker.com/ docker 镜像市场: https://hub.docker.com/ 一、docker 说明 1.1、docker 核心 1、Docker 是一个开源的应用容器引擎,基于 Go 语言 并遵从 Apache2.0 协议开源,Docker 是一个 CS 架构软件。 2、Docker 是一个虚拟化轻量级linux服务器,可以解决我们在开发环境中运行配置问题 3.、Docker的主要目标是‘build ,ship and run any app,anywhere’,一次封装,到处运行 4、容器是完全使用沙箱机制,相互之间不会有任何接口(类似 iPhone 的 app),更重要的是容器性能开销极低。 1.2、docker 版本问题 .Docker 从 17.03 版本之后分为 CE(Community Edition: 社区版) 和 EE(Enterprise Edition: 企业版),我们用社区版就可以了。 1.3、docker 架构( 3大核心) · 1、Images 镜像 (等于软件) · 2、Registry […]