Normal index

The normal index page records the normal index and the primary key value, so when querying the non-primary key field, you need to return to the table.

A binary method is used to locate records within a common index data page.

Ordinary indexes need to look up the next record one more time, while unique indexes will not look up further

change buffer

Change Buffers are copied in memory and written to disk When updating data, if the data is directly in memory, it will be written directly to memory. If the data page is not in memory and needs to be read from disk, the next time the data page needs to be accessed by the query, the data page will be read first, and then the relevant page operation in ChangeBuffer will be performed, namely the merge operation

When is a merge triggered?

  • To access this data page,
  • Background threads merge periodically.
  • Database normal shutdown

Under what conditions is a change buffer used? A unique index must first read the data to determine whether the uniqueness constraint is violated during the update operation. Therefore, a unique index cannot use the change buffer, so only ordinary indexes can use it

What is the size of the change buffer? The change buffer uses the memory in the buffer pool, which is set by the parameter innodb_change_buffer_max_size. When the value is 50, it means that the memory in the buffer pool is used at most 50%. When set to 0, the change buffer is turned off

When to use a change buffer? Merge is the time for actual data update, and the main purpose of the change buffer is to cache the recorded changes, so the more changes recorded in the change buffer before a page is merged (i.e. the more times the page needs to be updated), The greater the gain. For businesses that write more and read less, the page will not be accessed immediately after writing. For mechanical hard disk, the effect is very significant. For libraries similar to historical data, the common index should be used as far as possible, and then the change buffer should be opened as large as possible.

The difference between redo log and change buffer

  1. Page1 is in memory, update memory directly;
  2. Page2 is not in memory, so just record the message “I need to insert a line into Page 2” in the change buffer area
  3. Record the above two actions in the redo log (Figure 3 and 4)
  4. Dotted arrows perform operations in the background and do not affect the update time

To sum up, the whole process only writes two places of memory in sequence. The main savings of writing one place of disk redo log is the IO consumption of randomly writing disk, while the main savings of change buffer is the consumption of randomly reading disk

Is the change buffer lost when it crashes and resumes?

  • Change Buffer is persisted to IBData
  • The redo log records the changes to the data page and the information written in the change buffer. After a crash, you can restore the redo log from the binlog and then restore the change buffer based on the redo log