The main purpose of buffer pools in Inndo is to reduce the interaction between the database and disk. Processes can read and write data from memory much faster than from disk.
Innodb memory structure consists of two main parts: redo_log_buffer and buffer pool. After redo_log_buffer is used, it is expanded in the persistence part of the transaction.
The buffer pool
The size of the buffer pool directly affects the performance of the Innodb database. Generally, the production level mysql server will set the buffer pool to 3/4 of the total memory of the operating system. The buffer pool consists of four parts:
- Data page
- Index page
- change buffer
- Adaptive hash index
Data page
A data page is a page that caches real data. In InnoDB, the data page is a B+Tree data structure formed by the clustered index where the primary key resides. Therefore, the data page contains both indexes and data.
Index page
The index page is the page of the cached data index. The index page represents the B+Tree data structure composed of secondary indexes (non-clustered indexes). This index contains the data of the index and the primary key ID of the data pair
Change Buffer
A Buffer is usually stored in memory. So the role of Change Buffer in the Buffer pool is to Buffer updates (Insert, Update, Delete) that are not in the Buffer pool.
Why Change Buffer is needed:
If the modified data is in the buffer pool, neither queries nor updates need to generate random IO. However, if the modified data is not in the Buffer pool, the data needs to be read into the Buffer pool and then updated. This will generate random IO Change Buffer. IO Change Buffer Takes up 1/4 of innodb_buffer_poolby default. The maximum value can be set to 1/2. Set with innodb_change_buffer_max_size.
Here are a few questions:
How does Change Buffer guarantee persistence
The Change Buffer also writes data to the redo log to ensure data reliability.
Change Buffer Indicates the index type
This applies only to non-unique indexes, because unique indexes require uniqueness verification. If uniqueness verification is required, reading data from disk cannot be avoided, so this applies only to non-unique indexes.
Change Buffer Applicable scenarios
- Non – unique index many
- Write more read less
- Finished reading the scene is called less situation
Adaptive hash index
Innodb itself does not support user-defined hash indexes, but it generates adaptive hash indexes based on criteria
Since B + Tree can effectively support range queries, hash indexes cannot handle range queries due to their unordered nature
By default, Innodb enables adaptive hash indexing. When the number of data accesses exceeds 1/16 of the total number of rows, data is added to the hash index
The key of a hash index is the prefix of the index, for example, the prefix of a field, and the value is the location where the index is locked on a page.
As you can clearly see from the figure above, there are fewer levels of hash indexed queries.
The parameter is set with the innodb_adaptive_hash_index parameter, which defaults to ON
| The name of the | Adaptive hash index |
|---|---|
| Suitable scenarios | Suitable for equivalence queries using the = and IN operators |
| Inappropriate scene | Range queries like and % are not suitable |
| advantages | Improved Innodb memory usage and secondary index query efficiency in some cases |
| disadvantages | Occupying Innodb’s memory cache |
reference
Inside MySQL Technology (2nd Edition)
Write buffer (change buffer), this time completely understand!!
InnoDB does not support hash index, why different people say different?
Advantages and disadvantages of Innodb’s adaptive hash index