Shulou(Shulou.com)06/01 Report--

The data inserted after the execution of the insert statement will not be immediately saved in the disk, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need this can learn, I hope you can gain something.

Here's the question: when you execute an insert statement, does the inserted data immediately stay on disk?

The answer is not necessarily!

Let's see how insert statements behave during write to disk!

We all know that there are transactions in the commit step, so we can look at it in two steps.

I. Prior to submission

First, enter the server layer to perform some necessary checks on SQL, and will not involve writing to disk.

2. Enter the engine layer and begin to formally submit execution data.

Here we first understand MySQL in InnoDB storage engine, how data is stored!

Like most databases, InnoDB has the concept of a page (also known as a block), which is the smallest unit of disk management in InnoDB. In the InnoDB storage engine, the default size of each page is 16 KB. Starting with InnoDB version 1.2.x, page sizes can be set to 4K, 8K, and 16K via the parameter InnoDB_page_size. If the setting is complete, the page size in all tables is InnoDBpagesize, and it cannot be modified again. Unless new libraries are generated through mysqldump import and export operations.

InnoDB data is read and written in units of data pages. That is to say, when a record needs to be read, it is not read from disk itself, but read into memory as a whole in pages.

Reading data from disk into memory involves random IO access, which is one of the most expensive operations in the database, so in order to reduce disk IO, InnoDB has designed the change buffer mechanism.

What is a change buffer:

① In versions before MySQL 5.5, because only insert operations are supported, they are initially called insert buffers, but later versions support more operation type caches, which is why there are a large number of functions or variables beginning with the ibuf prefix in the code.

However, using write buffering requires two conditions to be met simultaneously:

(1)Index is secondary index

Insert cluster index is generally sequential, generally do not need random disk read, so do not need to use change Buffer

(2)Index is not unique

Secondary indexes cannot be unique because, when inserting into a buffer, the database does not look up index pages to determine the uniqueness of the inserted record. If you look for it, there will definitely be discrete reads, which will cause the change buffer to lose its meaning.

② Low-level implementation of change Buffer

The underlying structure of a change Buffer is a global B+ tree responsible for performing change buffers on all tablespaces.

Therefore, insert statement does not need to write data to disk file immediately, just need to modify the corresponding data page in the memory buffer pool.

4, we all know that memory can not guarantee the persistence of data, if the database in this process of downtime, how to ensure that the data stored in the cache is not lost. The redo log is generated as a result, and by default each transaction commit triggers a redo log flush.

5. If binlog log is enabled, the transaction logic data will be written to binlog file, and in order to ensure replication security, it is recommended to set the binlog changes to disk every time the transaction is committed.

In summary, when the insert statement is successfully submitted, the data is not written to disk immediately, but finally written to disk through redo log and binlog files. However, the database cache cannot be infinite, and redo log is difficult to accommodate all the data. How can the data in the cache be written to disk?

II. After the submission of the transaction

1. When the data pages in the buffer pool reach a certain amount or the IO pressure of the database is small, the data page brushing operation will be performed.

2. When the shared table space is opened, a copy will be copied into the shared table space first when refreshing the data. Since the pages of the shared table space are continuous and the writing to the disk is also a sequential operation, this process does not consume much performance.

Regardless of whether or not it passes through the shared table space, the updated data page will eventually need to be brushed into the data file of the table space. Only after the completion of the brush can the space in the database cache be released.

4, change Buffer is also a part of the database cache, when the database cache space is insufficient to exchange part of the data update page, it is possible to write buffer data page out, brush into the change Buffer data file in the shared table space.

When innodb_stats_persistent=ON, the data involved in the insert statement will be scrubbed into innodb_table_stats and innodbi_ndex_stats.

To sum up, all the data involved in an insert statement will be written to redo log, binlog, shared table space on disk in turn, and finally settled as An in its own user table space.

Extension: says data is written to disk, so when do records in change Buffer merge into the real secondary index?

Change Buffer is a B+ tree. When the secondary index page to be inserted is not in the buffer pool, the secondary index record is inserted into the B+ tree first.

The merge operation can occur in several situations:

① Secondary index pages are read into cache

② The auxiliary page tracked by the Change buffer bitmap page has no available space

Master thread jobs

In the first case, when the auxiliary index page is read into the buffer pool, for example, when performing a normal SELECT query operation, it is necessary to check the Insert Buffer Bitmap page and then confirm whether the auxiliary index page has records stored in the Insert Buffer B+ tree. If yes, insert the record of this page in Insert Buffer B+ tree into this auxiliary index page. Multiple record operations on this page are merged into the original secondary index page through several operations, so performance will be greatly improved.

In the second case, the Insert Buffer Bitmap page tracks the available space for each secondary index page and has at least 1/32 of a page. If it is detected that the available space after inserting the auxiliary index record will be less than 1/32 page, a merge operation will be forced, that is, the auxiliary index page will be forcibly read, and the record of this page in the Insert Buffer B+ tree and the record to be inserted will be inserted into the auxiliary index page. This is the second case mentioned above.

The third case is that Merge Change Buffer operations are performed every second or every 10 seconds in the Master Thread thread, the difference being that the number of pages per merge operation varies according to the working state of the thread.

Did reading the above help you? If you still want to have further understanding of related knowledge or read more related articles, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.