InnoDB

1. What Is the Buffer Pool - A contiguous memory space requested from the operating system when MySQL starts 2. Why the Buffer Pool Is Needed - The speed gap between disk and CPU is too large, so memory is needed as a cache - When InnoDB accesses table and index data, it caches them here, greatly reducing disk I/O and improving efficiency 3. Buffer Pool Workflow

1. What Is MVCC - Multi-Version Concurrency Control - To allow read-write operations from different transactions to execute concurrently, data is maintained in multiple versions, and transaction visibility determines which data version a transaction should see 2. MVCC Principle - Where to get data from: version chain - Which version of the data to get: ReadView 2.1. Version Chain - InnoDB undo log.md

1. What Is redo log - A log of the MySQL InnoDB storage engine, used for crash recovery - redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions 2. Why redo log Is Needed - According to durability requirements, once a transaction commit completes, it must be persisted to disk. There are two approaches

1. What Is undo log - A log of the MySQL InnoDB storage engine - Mainly records logical changes to data - An INSERT statement corresponds to a DELETE undo log - An UPDATE statement corresponds to an opposite UPDATE undo log - A DELETE statement corresponds to an INSERT undo log - Before actually inserting, deleting, or modifying a record, the corresponding undo log needs to be recorded first

1. What Is a Transaction Database Transactions.md 2. Using Transactions - Start a transaction: begin or start transaction - Commit a transaction: commit - Automatically committed transactions: - SHOW VARIABLES LIKE 'autocommit' - By default, each statement is an independent transaction - Roll back a transaction: rollback 3. Consistency 4. Durability 5. Atomicity 6. Isolation

1. What Is a Tablespace - A tablespace is an abstract concept - Logically - It can be imagined as a pool of pages - Physically - For the system tablespace, it corresponds to one or more actual files in the file system - By default, InnoDB creates a file named ibdata1 with a size of 12M under the data directory, and the size grows automatically - For each file-per-table tablespace, it corresponds to an actual file named table_name.ibd in the file system 2. Tablespace Structure

1. InnoDB - MySQL's default storage engine - InnoDB divides data into pages, each page is 16KB, and pages are the basic unit of interaction between disk and memory - That is, a read reads at least one page and a write writes at least one page 1.1. Index Implementation 1.1.1. Primary-Key Index - The leaf node's data stores the actual data - Because the index stores the actual data, there is only one ibd file 1.1.2. Secondary Index - The leaf node's data stores the primary-key index 2. MyISAM

1. InnoDB Features 1.1. Supports Transactions - InnoDB Transactions.md 1.2. Supports Row-Level Locks - MySQL Locks.md 1.3. Supports MVCC - InnoDB MVCC.md 2. InnoDB Architecture 2.1. Memory Layer 2.1.1. Buffer Pool - Read buffer; its purpose is to improve InnoDB performance, accelerate read requests, and avoid disk I/O for every data access

1. What Is Flush - When the contents of an in-memory data page and an on-disk data page are inconsistent, the memory page is called a dirty page. After the memory data is written to disk, the contents of the memory and disk data pages become consistent, and it is called a clean page - Flush means flushing dirty pages to disk 2. When Flush Is Triggered - redo-log is full - memory is full - when MySQL considers the system idle - when MySQL shuts down normally 3. InnoDB Dirty-Page Flushing Control Strategy