background
MySQL version: MySQL 8.0 0
Reference: MySQL official website
1、 InnoDB official architecture
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 byINSERT、UPDATEorDELETEoperation(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 tablespace、Independent table space、Universal table space、Temporary 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 log，Undo 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