Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is Insert Buffer, the key features of Innodb?". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Insert Buffer

In the Innodb storage engine, the primary key is the unique identifier, and if the primary key is incremented, the primary key insertion null will automatically grow, and the inserted data will be stored in order. If the primary key is not self-increasing, such as UUID, then the insertion, like the secondary index, is random. For nonclustered indexes, discrete reads are required for each insert, which greatly reduces the efficiency of insertion.

Innodb introduces Insert Buffer. For the insert or update operation of nonclustered index, instead of inserting directly into the index page every time, first determine whether the inserted nonclustered index page is in the buffer pool, if so, insert it directly; if not, put it into an Insert Buffer first. Then the merge operation is carried out with a certain frequency and situation. The efficiency of insertion is greatly improved.

Insert Buffer needs to meet the following two conditions:

The index is a secondary index (secondary index)

The index is not unique (unigue)

The secondary index cannot be unique, because at insert time, the database does not look up the index page to determine the uniqueness of the inserted record, because if the search results in discrete reads, then insert buffer is meaningless.

II. Change Buffer

Innodb introduced Change Buffer from version 1.0.x as an upgrade to Insert Buffer. Starting with this version, the Innodb engine operates on DML-insert,delete,update corresponds to Insert Buffer,Delete Buffer,Purge Buffer.

Like previous Insert Buffer, the objects used by Change Buffer are still non-unique secondary indexes.

A delete operation can be divided into two processes:

Mark the record as deleted.

Actually delete the record.

III. Merge Insert Buffer

Insert/Change Buffer is a B + tree,Insert/Change Buffer. How can records be merged into a real index?

Merge Insert Buffer can occur in the following situations:

The secondary index is read into the buffer pool

The Insert Buffer Bitmap page traces to the secondary index page and there is no space available.

Master Thread (Page Cleaner Thread)

The first, such as a normal select query, checks the Insert Buffer Bitmap page to see if any records on the secondary index page exist in Insert Buffer B + tree, and if so, inserts the records from Insert Buffer B + tree into the secondary index.

Second, the Insert Buffer Bitmap page is used to track the possible space of each secondary index page. If the available space is less than 1 + 32, the secondary index page is forced to be read, and the records in Insert Buffer B + tree are inserted into the secondary index.

Third, Master Thread (Page Cleaner Thread) merge insert buffer every second or every ten seconds.

4. View insert buffer > show engine innodb statusINSERT BUFFER AND ADAPTIVE HASH INDEX--Ibuf: size 1, free list len 6236, seg size 6238, 50345247 mergesmerged operations:insert 78487598, delete mark 0, delete 0discarded operations:insert 0, delete mark 0, delete 0

Seg size shows that the current insert buffer size is 6238x16KB, about 97.46MB

Free represents the length of the free column

Size represents the number of record pages that have been merged

Insert represents the number of records inserted, and merged recs represents the number of inserted records merged.

Mergess represents the number of mergers

In write-intensive cases, inserting buffers take up too much buffer pool memory (innodb_buffer_pool), and the default maximum is 1 Universe 2.

This is the end of the content of "what is Insert Buffer in the key features of Innodb". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.