MySql statement execution flow:
Client -> Connector -> Query Cache -> Analyzer -> Optimizer -> Executor ->InnoDB Storage Engine
Here is an InnoDB architecture diagram:
This section introduces the Buffer Pool in the figure:
A Buffer Pool is an in-memory cache, as the name implies.select * from t1 where a = 1;InnoDB will godiskTo find the corresponding of this datapage, as shown below:
page.copyInto the Buffer Pool, as shown below:
select * from t1 where a = 1;InnoDB will fetch data directly from Buffer Pool instead of disk.
Similarly, executeupdate t1 set b = 2 where a = 1;The InnoDB will godiskTo find the corresponding of this datapageAnd then put this onepage.copyModify the Buffer Pool data directly. In this case, memory and disk data are inconsistent, and InnoDB will periodically persist Buffer Pool data to disk.
The default size of the Buffer Pool is 128 MB. If many pages of data are cached, see the following figure:
InnoDB regularly persists Buffer Pool data to disk. In fact, InnoDB overwrites old data in disk with updated Buffer Pool data. This leads to a situation as shown below:
pageIs the data that has been removed from the Buffer Pool and persisted to disk, yellow bottompageIs the data in the Buffer Pool that has not been persisted to disk;
To be clear, there is no yellow base in the Buffer PoolpageAll the other positions are vacant. If new data is added to the Buffer Pool, where should it be placed? (Because the free space is scattered, not continuous)
InnoDB has a linked list, called the Free list, which is used to record free locations, as shown below:
Control block: record pointer to free position;
The free list contains as many control blocks as there are free positions in the Buffer Pool. (Only 3 are drawn here for convenience)
If you add data to the Buffer Pool, go directly to the free list to find the corresponding free position of the control block, put the data into the free position, and remove the control block from the free list. Similarly, if a page in the Buffer Pool is empty, a control block will be generated that points to the current free position. Add to the free list;
When does InnoDB persist modified data from a Buffer Pool to disk?
The default Buffer Pool size is 128 MB. What if the Buffer Pool is full and you want to add new data?
There is also a linked list inside InnoDB called LRU linked list. The structure is the same as the free list
Each time a new page is added to the Buffer Pool, it is added to the LRU listThe headInsert a control block and point to the data page, since it is recently used. Similarly, any manipulation of a data page in the Buffer Pool will move the corresponding control block to the LRU listThe head;
In the LRU linked list, the earlier the position (hotspot data) is, the more recently it has been accessed, and the later the position, the earlier it has been accessed; If the Buffer Pool is full and new data comes in, remove the control block at the end of the LRU list and insert the new control block to the head of the list.
There is a problem: if the implementationselect * from t1In the Buffer Pool, all data cached in the Buffer Pool will be replaced.
To solve this problem, InnoDB splits the LRU list into two regions:
Green area: cold data area;
If new data enters the Buffer Pool, remove the control block at the end of the cold data area and the data pointing to the Buffer Pool first. The control block corresponding to the new data will be placed at the position of the head node of the cold data area:
Data in the cold data area, the current access timeMinus theTime of last accessMore than 1 second, the accessed data block is moved from the cold data area to the head node of the hot data area.
We haven’t explained how data in a Buffer Pool is persisted to disk. And how to prevent data loss?
Let’s start with two logs:
Redo log: Where data is modified on a page in the Buffer Pool.
Undo log: Redo log reverse operation, records how to return to the original data SQL;
Update statement execution:
1. Modify the data in the Buffer Pool.
Create a redo log.
Redo log persistence;
4. The undo log;
5. The modification is successful.
Why not persist to the database disk instead of generating and persisting a redo log?
We know that onepageData =16KB. Update only modifs one row of data on a page. If the data is persisted directly to the database disk, the entire page of data needs to be persisted.
Redo log file:
var/lib/mysql/ib_logfile0
var/lib/mysql/ib_logfile1
These two files are automatically generated by mysql from the beginning, so it is very quick to append data to them sequentially; Even if mysql fails, the next time mysql restarts, data can be restored based on the redo log.
What if both log files are full and new data comes in?
In this case, InnoDB will be triggeredcheckpointInnoDB persists all pages in the redo log Buffer Pool to the database disk and then empties the redo log before inserting new data into the redo log. Of course, this process, mysql performance is slow.
Update statement execution:
1. Modify the data in the Buffer Pool.
Create a redo log.
Redo log persistence;
4. The undo log;
5. The modification is successful.
Step 2 above: The generated redo log is placed firstLog BufferWithin; (See the InnoDB architecture diagram at the beginning of this article)
Step 3 above: Redo log persistence is done in theTransaction commitWhen;
This is how InnoDB transactions are executed:
Begin After the transaction is started, commit: Redo log is persisted. Rollback: Undo log data is restored.
We know the four properties of transactions: ACID, atomicity, consistency, isolation, and persistence
Atomicity: undo log to achieve;
Persistence: redo log;
Isolation: MVCC to achieve;
Consistency: The above three ultimately guarantee consistency;