MySQL insert buffer and change buffer

One, foreword
Second, problem introduction
- 2.1. Cluster index
- 2.2. Common index
3. The meaning of change Buffer
4. Look at the change buffer
Limit of change Buffer
6. Related parameters of change Buffer
Check your MySQL change buffer
Soul interrogation
Nine, reference
Recommended reading

One, foreword

This is the 30th update of the MySQL Interview Guide for r&d students

It’s a long story. Baidu is said to be a pension factory, but the result is a PDD feeling. Recently I was really busy with work, and then I wanted to relax on Saturday and Sunday. I went out to play with my classmates on Saturday and Sunday.

Put up a flag! Follow-up updates should be made at least once a week. Welcome to daydream, dry goods to share constantly ~

Good! Here we go. Have you ever heard or seen insert buffer or change buffer? In this article, we will talk about MySQL insert buffer, change buffer, and completely uncover the veil of these two nouns!

Second, problem introduction

From a daydream point of view, if you want to better understand insert buffer, change buffer. First of all, you should master some knowledge, such as the MySQL index related knowledge. So don’t worry, let’s start talking a little bit, from your knowledge of data to insert buffer, change buffer, you will find that suddenly open.

2.1. Cluster index

First of all, let’s review MySQL’s cluster index. I bet you’ve all memorized a clustered index before you went to an interview.

If you look at this graph, it’s an abstraction of B plus tree. It has many features, but there are only a few you need to know about in this article

It’s a B plus tree.
We call the leaves of this tree the data page.
The rows stored in the leaves are a universal set. How do we explain the universal set? For example, the data table has 3 columns, id, name, age. The so-called complete set is to say: each row of data has ID, name, age these three columns.
The non-leaf nodes are called index pages.

And just so you know, this B+tree is going to be stored in Disk. If you don’t understand it well, read the following sentences:

For example, if an UPDATE SQL wants to modify a row with id = 999, what will it do with the data page? First, the buffer pool is checked to see if there is a page containing the data. If so, just update. If not, do a disk IO to load the data page into memory and then update it. Then the data page becomes dirty. Another mechanism refreshes the data page back to Disk. Complete memory and data.

(B+tree) (B+tree) (B+tree) (B+tree) (B+tree)

In fact, if you compare it to the data page, the same is true for the non-leaf nodes of B+tree. As I said above, we call non-leaf nodes index pages. Why do you say that? In fact, non-leaf nodes are essentially data pages, except that the data stored in them is index data. And just like a normal data page, when you need it and it’s not in memory, do a disk operation and read it into memory.

2.2. Common index

Common index is also we often say secondary index, joint index and so on.

For example, if we set the name column to index, MySQL will create a B+Tree for our index. (yes! It is a separate B+Tree from the primary key index. And pay attention to the following points:

Like a clustered index, we call its non-leaf nodes index pages
Its leaves do not store the complete set of all columns. For example, if we create an index on the name column, its leaf node will store the ID and name columns. And it’s sorted by name.

3. The meaning of change Buffer

Insert buffers and change buffers are easy to use.

Let’s look at the change buffer in this section:

Insert buffers and change buffers are used by MySQL to optimize DML (delete, write, modify) operations on secondary indexes that are not unique. The goal is to make MySQL perform better.

For example, the table has three columns. Id, Name, and AGE. Then ID is the primary key and name is a non-unique secondary index.

SQL: update xx set name = “send me daydream” where name = “send me daydream”

1. Check whether the data to be updated is in the buffer pool.

2. Update the buffer pool.

3. If it is not in the buffer pool, perform disk I/O operations, read it into memory, and update it.

The problem now is that the name column is an index column. As mentioned above, since it is an index column, we need to create a separate B+Tree for it.

If your UPDATE SQL is going to be modified, is it going to break into two big steps

1. Step1: Update the data in the buffer pool.

2. Step2: maintain the B+Tree created separately for name.

Since MySQL is going to optimize our DML operations on non-unique secondary indexes, there must be a point at which we need to optimize.

Insert buffer and change buffer exist.

Why do you say that? As we mentioned at the beginning of this article, B+ Tree is also stored in Disk, so it is inevitable that random Disk IO will occur.

Or think about it: You just want to update a few pieces of data. Suppose, by bad luck, that none of these entries are in the buffer pool. There’s no choice. We have to read the disk. Unfortunately, the secondary index page involved was not in memory either, and we had to wait synchronously for this random disk IO!!

4. Look at the change buffer

The change buffer is essentially a piece of memory.

Insert, delete, update, and other DML operations need a secondary index page (note that this is a secondary index page, such as the B+Tree leaf created for the name column, not the normal data page in the Buffer pool)

When these secondary index pages are not in memory, your actions on them are cached in the change buffer (in order to save random disk IO this time). When MySQL is idle, when MySQL is shut down, or when there is a read operation, merge the cache operation into B+Tree.

Limit of change Buffer

This reality has already been said

1, the first requirement is a secondary index. What is the meaning of the change buffer if it is not a secondary index? There’s nothing to optimize for. Why don’t you get rid of the change buffer

2. The secondary index cannot be unique. That makes sense. If the name column is unique. Do I have to check to see if there is an existing index of the same value on memory or Disk before I insert? This means that the insert operation cannot be cached! You must immediately know whether or not the insert succeeded. There you are! Otherwise, what are you going to return to the client?

6. Related parameters of change Buffer

Parameters: innodb_change_buffer_max_size

Function: Controls the ratio of the change buffer to the total memory of the buffer pool

Range: default 25 (meaning that the change buffer can occupy 25% of its memory), maximum 50.

Parameters: innodb_change_buffering

Action: Controls which DML the change buffer works on

Optional parameters: all (INSERT, delete, update), None (inserts, deletes, and purges), inserts, deletes, and Purges

Check your MySQL change buffer

# command
SHOW ENGINE INNODB STATUS\G

# See the following section
-------------------------------------
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 4425293, used cells 32, node heap has 1 buffer(s)
13577.57 hashSearches/s, 202.47 non - hash searches/s# insert: insert buffer
# delete mask: delete buffer
Delete: purge buffer
# discarded Operations: Table discarded when change Buffer merge No need to merge
Copy the code

Soul interrogation

If you can answer this question, you really understand change buffer!

When I open the change buffer, I now want to delete a row that is not unique to the secondary secondary index. For example, delete the row whose name=Tom and the index page is not in memory… What happens next?

If the index page is not in memory, instead of being read from disk, it will be written to the change buffer.

Since you are writing this operation in the change buffer, how do you calculate the number of rows you return to the client? You haven’t even read the disk. What if the disk doesn’t even have the data you want to delete? Do you tell the client that the deletion succeeded and the number of rows affected is 1?

A: The client gets the right number of rows every time! If the change buffer does not contain a row with name=Tom, it will still read the disk. If the change buffer does not contain a row with name=Tom, it will read the disk. The buffer pool and the change buffer are two caches

Nine, reference

Dev.mysql.com/doc/refman/…