Summary of important components of MySQL InnoDB

Innodb includes the following components

1. innodb_buffer_pool:

It is mainly used to cache data and indexes (to be precise, since the table in InnoDB is organized by a clustered index, the data is only the leaf node of the primary key index).

2. Change buffer:

1 If the update statement is to update the record of the secondary index, but the page where the record is located is not in the innodb_buffer_pool, innodb will update the secondary index

The update action of the page is cached in a specific area (change buffer) of innodb_buffer_pool; if another transaction B wants to read this secondary index page later,

Since the page is not yet in the innodb_buffer_pool, transaction B will first load the page into the innodb_buffer_pool, so that the target page will enter the innodb_buffer_pool.

Next, you can update the index page according to the contents of the change buffer. This can save IO operations and improve performance.

2 Of course, there are other refresh mechanisms (changes in the change buffer are written to disk), for example, when MySQL is relatively idle, it will also refresh during the slow shutdown process.

Change buffer contents to disk

3 Monitoring the change buffer

show engine innodb status;

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len ​​0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 24635311
Log flushed up to 24635311
Pages flushed up to 24635311
Last checkpoint at 24635302
0 pending log flushes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second

3. Adaptive hash index:

1 If some rows in the table are used very frequently, due to the fact that the InnoDB table is organized in a B+ tree, in the best case, InnoDB reads the index page first, then the data page, and then

Find the data; the hash index uses the hash of the B+ tree index as the key and the value of the B+ tree index (the page it points to) as the value; with the introduction of the hash index, InnoDB can calculate the hash of the index

The value is directly located at the page where the data is located; therefore, hash indexing is advantageous for non-range searches.

2 If you want InnoDB to use the bash index, there are several conditions: 1. innodb_adaptive_hash_index=1, so that InnoDB will enable the hash index; however, this is only half of the work.

InnoDB does not create hash indexes for all rows in the table. It only creates hash indexes for frequently accessed rows in the table. It is a waste to create hash indexes for cold data.

innodb_adaptive_hash_index_parts can set the partitioning of hash index, which can improve concurrency.

4. redo log buffer:

The contents of the redo log buffer are periodically flushed to disk. If the redo log buffer is set larger, it is beneficial for MySQL to handle large transactions. The reason is that in the processing of large transactions,

You can write redo to the redo log buffer instead of writing it to disk. Since memory is faster than disk, large transactions can be processed faster. In other words, the redo log buffer is larger.

In this case, some unnecessary disk flushing operations can be reduced before commit.

5. System tablespace:

The innodb system tablespace contains the following: innodb data dictionary, some storage areas such as doublewrite\changebuffer\undolog, if innodb_file_per_table

If it is not opened, the tables created by the user will be saved in this system tablespace. In this case, the system tablespace can also be seen as including the shared tablespace.

The above is the detailed content of the summary of the important components of MySQL InnoDB. For more information about MySQL InnoDB components, please pay attention to other related articles on 123WORDPRESS.COM!