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

A preface

In the insert operation of mysql, the page that requires insert are clustered index page, unique index page, and non-unique index page. Under normal circumstances

For the insert of a clustered index (with self-increment as the primary key), it is a sequential io operation, so it is very efficient, and of course, the insertion of the bound primary key is also random, but this is rare.

However, for non-unique secondary indexes, most of the insertions are random IO processes, and for mysql, the cost of random io is relatively large. InnoDB has done a special work for this, that is, insert buffer.

Two Insert BUFFER

1 > insert buffer is that BP has a memory area and, like the data page, has its own physical page.

2 > insert buffer bitmap stores information about all pages, each of which takes up 4 bytes. So one bitmap page can manage 16384 pages, then a bitmap manages the page, and so on.

1. The proportion of the remaining space on the page, 2 bytes

two。 Whether the page is cached in insert buffer B+Tree, 1 byte

3. Whether the page is an index page, 1 byte

The insert bitmap is stored in the system tablespace.

Construction process:

1. For the insert operation of a non-unique secondary index page, first determine whether the page is in the BP, if not, construct the search key through sapce offset, and put the page into the insert buffer B+Tree through the key.

2 if yes, insert the page directly.

Merge process:

1. If the select/update/delete operation needs to read the secondary index page, first check whether the page exists in the insert buffer b+Tree in bitmap page. If there is a page change, read the page merge from insert buffer B+TRee to the secondary index page first.

two。 For insert operation, if the auxiliary index page is not in the BP, the bitmap page is first detected to determine whether the space of the auxiliary index page is less than 1x32 page size after inserting the index record; if it is less than 1x32, the index page is forced to be read, that is, the operation of (1) above is carried out, and if not less than, it is directly inserted into the insert buffer.

3. Master Thread has operations related to insert buffer's merge every 1s or 10s. Random the location of an insert buffer B+Tree and then sequentially merge N page.

Reference:

"inside MySQL Technology: InnoDB Storage engine"

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.