The query process

For normal indexes, after finding the first record that meets the condition, the next record is searched until the first record that does not meet the condition is encountered.

For unique indexes, since the index defines uniqueness, the search stops after the first record that meets the condition is found.

A normal index will be searched several times until it is not satisfied, but if it is satisfied a certain number of times, it will not be searched many times. Therefore, the performance impact is minimal

The update process

change buffer

When a data page needs to be updated, it is directly updated in memory. If the data page is not already in memory, InnoDB caches the update in the Change Buffer without affecting the consistency of the data, so that the data page does not need to be read from disk. The next time a query needs to access the data page, the data page is read into memory and the change Buffer operations related to the page are performed. In this way, the correctness of the data logic can be guaranteed.

At the same time, the change buffer is copied in memory and written to disk.

The change Buffer operation is applied to the original data page, called merge. In addition to accessing the data page, background threads are merged periodically, as well as when the database is shut down. This reduces disk reads, speeds up statement execution, and reduces memory footprint relative to buffer pools (data pages).

For unique indexes, all update operations must first determine whether the operation violates the uniqueness constraint. For example, to insert the record (4,400), we need to determine whether there is already a record k=4 in the table, which must be read into memory. If it is already read into memory, it is faster to update the memory directly, and there is no need to use change buffer.

Therefore, only ordinary indexes are used. Random indexes do not use change Buffer

So there are two cases:

In the first case, the target page for which the record is to be updated is in memory. In this case, InnoDB’s processing flow is as follows:

For unique indexes, find the position between 3 and 5, determine that there is no conflict, insert this value, the statement is finished;

For normal indexes, find the position between 3 and 5, insert the value, and the statement completes.

In the second case, the target page for which the record is to be updated is not in memory. In this case, InnoDB’s processing flow is as follows:

For unique indexes, the data page needs to be read into memory, and if there is no conflict, insert the value, and the statement is executed.

For normal indexes, updates are recorded in the Change Buffer, and statement execution ends.

Unique indexes will definitely load data into memory to determine if there is a conflict, whereas normal indexes can not be loaded into memory, so ChangeBuffer can be accelerated.

Change Buffer speeds up the common index update process. It is suitable for scenarios where many writes and few reads are performed. Because each read triggers merge, changeBuffer is updated into data, increasing maintenance costs. Applies to normal indexes, not unique indexes. Suitable for such as billing class, log class system.

Since there is no difference in queries, it is recommended to use plain indexes as much as possible.

If each update is followed by a query for the record, changebuffer should be turned off and Innodb_change_buffer_max_size set to 0.

The change Buffer mechanism works especially well when using mechanical hard drives.

The difference between ChangeBuffer and Redolog is as follows

insert into t(id,k) values(id1,k1),(id2,k2);

Suppose k1’s database is in memory and K2 is not.

  1. Page 1 is in memory. It updates memory directly, regardless of changeBuffer.
  2. When Page 2 is not in memory, record the message “I want to insert a line into Page 2” in the change buffer
  3. Log the above two actions in the redo log (figure 3 and 4).

When reading these two data:

WAL does not have to read the disk or update the Redolog data to return the page. Therefore, the contents of the disk remain unchanged and the Redolog remains unchanged.

Page2 is not in the memory. You need to read Page2 from disk, use ChangeBuffer operation log, and generate the correct version after merge. And writes the merge result to disk.