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

About INNODB index header

The tools used are self-written mysqlblock and bcview.

I put it on Baidu Cloud disk.

Http://pan.baidu.com/s/1num76RJ

For everyone to download and use

The normal tablespace (and with innodb_file_per_table set up for each table corresponds to an idb file) starts with the fourth block and is usually the data page of innodb.

The first 38 bytes are FILE HEADER

INDEX HEADER from 38 bytes to 74 bytes, as follows:

Number of number of directory slot 2bytes slots

Offset address of the highest data record of the heap top position 2bytes block

The number of records for number of heap records/format flag 2bytes lines, but the 15th bit is marked for row mode. If the 15th bit is 1, it is COMPACT mode.

The offset of the deleted record in the first line of first garbage record offset 2bytes

Garbage space 2bytes deleted space size unit bytes

The position offset of the last insertion of last insert position 2bytes

The insertion direction of the page direction 2bytes row. The value is left, right or unordered 0x02 right 0x01 left 0x05 unordered

The number of rows that number of inerts in page direction 2bytes inserts data in the same direction, and resets if the direction changes

Total number of rows of number of record 2bytes

ID of the biggest thing in maximum transaction id 8bytes

The level of the page level 2bytes page, the page node is 0, and then add 1 to the network. If there are three layers, the root node is 2, the branch node is 1, and the page node is 0.

The ID of index ID 8bytes index is also available in INNODB_SYS_INDEXES.

Next, I set up innodb_file_per_table.

Create table km1 (id int, name varchar (20)

Insert into km1 values (1 recording gaopeng')

Insert into km1 values (2 recording gaopeng')

Insert into km1 values (3 recorder gaopeng')

Insert into km1 values (4)

Analyze the file:

[root@hadoop1 test] # mysqlblock km1.ibd-d | more

* * *

USEAGE: mysqlblock datafile-tmomomod

This small tool used in study and test database,not

Uesd on online database!

This tool is used to find how many blocks and types

In specified datafile,Exp:how many undo block in d

Ata file!

QQ:2238980

* * *

-t Only Total blocks types in ibdata!

-d Blocks types detail in ibdata!

* * *

FILE SIZE IS: 98304

Current read blocks is: 0-- This Block is file space header blocks!

Current read blocks is: 1-- This Block is insert buffer bitmap blocks!

Current read blocks is: 2-- This Block is inode blocks!

Current read blocks is: 3-This Block is data blocks (index pages)!

Current read blocks is: 4-This Block is new allocate blocks!

Current read blocks is: 5-This Block is new allocate blocks!

Total Block Status:

Total block: 6,Total size is: 0.093750 MB

Total undo block: 0,Total size is: 0.000000 MB

Total inode block: 1,Total size is: 0.015625 MB

Total insert buffer free blocks: 0,Total size is: 0.000000 MB

Total data (index pages) block: 1 total size is: 0.015625 MB

Total new allocate blocks: 2,Total size is: 0.031250 MB

Total insert buf bitmap blocks: 1,Total size is: 0.015625 MB

Total system blocks: 0,Total size is: 0.000000 MB

Total transaction system blocks: 0,Total size is: 0.000000 MB

Total file space header blocks: 1,Total size is: 0.015625 MB

Total extrenl disc blocks: 0,Total size is: 0.000000 MB

Total LOB blocks: 0,Total size is: 0.000000 MB

Total Unkown blocks: 0,Total size is: 0.000000 MB

I have very little data here, and there are 3 values, so there are only 6 blocks, and block 3 is the data block (starting with 0).

1 、 number of directory slot

Bcview km1.ibd 16 38 2 | more

Current block:00000003--Offset:00038--cnt bytes:02--data is:0002

Here is a concept of slot, which is actually used to locate data faster, and future articles will study it in more detail.

2 、 heap top position

Bcview km1.ibd 16 40 2 | more

Current block:00000003--Offset:00040--cnt bytes:02--data is:010c

It shows that the highest recorded value is 10c and 268 in hexadecimal.

3 、 number of heap records/format flag

Bcview km1.ibd 16 42 2 | more

Current block:00000003--Offset:00042--cnt bytes:02--data is:8006

Here is 8006 hexadecimal, which means that it is a row record in compact mode. If we use Redundant, let's take a look.

Create table km3 (id int,name varchar (20)) ROW_FORMAT = Redundant

Current block:00000003--Offset:00042--cnt bytes:02--data is:0002

As you can see, the 15th is 0, which means it is in Redundant format.

While our row record is 6, obviously only 4 records are inserted. Why 6? in fact, every block of MYSQL has

Two virtual columns infimum and supremum, both in a fixed location, while infimum points to the first record's

Offset, and the offset of the last record is represented as the offset of supremum, which forms a

Single item linked list.

4 、 first garbage record offset

Bcview km1.ibd 16 44 2 | more

Current block:00000003--Offset:00044--cnt bytes:02--data is:0000

0, if there are no deleted records, if we delete 2 records at this time

Delete from km1 where id in (1pr 3)

(since bcview is a file-based tool, the modified data dirty data is unloaded in buffer, so I restart the data once to see it.)

Check again

Current block:00000003--Offset:00044--cnt bytes:02--data is:00c9

See here for c9 and offset 201

5 、 garbage space

Bcview km1.ibd 16 46 2 | more

Current block:00000003--Offset:00046--cnt bytes:02--data is:004a

The bytes deleted here are 4a and 74 bytes. Why do I delete 2 rows of data but have so many bytes?

In fact, each row of data has about 24 bytes of overhead in addition to the data itself, and here

A table MYSQL INNODB without a primary key automatically generates a 6-byte 48-bit ROWID.

About 30 bytes of overhead, if you add the primary key ROWID 6-byte overhead when building a table, it will be gone.

Try it out:

Create table km4 (id int primary key,name varchar (1000))

Insert into km4 values (1 recording gaopeng')

Insert into km4 values (2 recording gaopeng')

Insert into km4 values (3 recorder gaopeng')

Insert into km4 values (4)

Delete from km4 where id in (1pr 3)

Check km4 again

Bcview km4.ibd 16 46 2 | more

Current block:00000003--Offset:00046--cnt bytes:02--data is:003

Here 3e is the decimal system 62j 74-62cm 12 exactly 6 bytes to prove my point.

Also note: if you do not add a primary key when building a table, insert data and then add, there is also an overhead of 6 bytes per row, so try to add a primary key when building a table.

If there are no deleted rows or all deleted spaces are reduced from this value to 0. 0.

6 、 last insert position

Bcview km1.ibd 16 48 2 | more

Current block:00000003--Offset:00048--cnt bytes:02--data is:0000

This value is all 0 when the reuse space is not deleted, but in subsequent tests this value represents the

Is the offset that was last inserted. If the deleted space is used, then this value appears

Point to a situation that is less than the current offset, because the space of the deleted data is the physical offset of the current row

before

7 、 page direction

Bcview km1.ibd 16 50 2 | more

Current block:00000003--Offset:00050--cnt bytes:02--data is:0002

The insertion order represented here is 0x02 right.

8 、 number of inerts in page direction

Bcview km1.ibd 16 52 2 | more

Current block:00000003--Offset:00052--cnt bytes:02--data is:0003

This means that the number of rows inserted in the order of 0x02 is 4-1, because I inserted 4 rows and it is 3

9 、 number of record

Bcview km1.ibd 16 54 2 | more

Current block:00000003--Offset:00054--cnt bytes:02--data is:0002

You can see that the number of rows here is actually 2 because I deleted 2 lines, and

Number of heap records/format flag

The record is still 8006, you can see that the record is still 6, excluding infimum and supremum, there are still four lines.

Then we can come to a conclusion.

Number of heap records is the number of rows of delete in this block + the number of rows without delete + infimum and supremum=6

And number of record is the number of rows in this block that are not delete

We can do the following experiment:

Now that there are 2 rows in the table, let's add a row of data to see the changes.

Mysql > insert into km1 values (5meme gaopeng)

Number of record

Current block:00000003--Offset:00054--cnt bytes:02--data is:0003

Number of heap records/format flag

Current block:00000003--Offset:00042--cnt bytes:02--data is:8006

You can see that our free space is reused, because number of heap records is still 6 and number of record becomes 3.

Then you can also take a look at page direction and number of inerts in page direction

According to the theory, since the space is reused, it will be inserted in the opposite order, so the two values will change.

it is as expected

Page direction

Current block:00000003--Offset:00050--cnt bytes:02--data is:0005

The value is 5

Number of inerts in page direction

Current block:00000003--Offset:00052--cnt bytes:02--data is:0000

Becomes 0, because the order is changed, 0X02 is changed to 0X05, and this value is reset.

At the same time, let's take a look at this time.

Last insert position

Bcview km1.ibd 16 48 2 | more

Current block:00000003--Offset:00048--cnt bytes:02--data is:00c9

It used to be worth

Current block:00000003--Offset:00048--cnt bytes:02--data is:0000

And this value happens to be the value of first garbage record offset after the data was deleted just now.

Current block:00000003--Offset:00044--cnt bytes:02--data is:00c9

And at this time, the value first garbage record offset

Bcview km1.ibd 16 44 2 | more

Become

Current block:00000003--Offset:00044--cnt bytes:02--data is:007f

Here 7f is getting smaller, and this is our id=1 address, and the id=3 address has been reused.

10 、 maximum transaction id

Bcview km1.ibd 16 56 8 | more

Current block:00000003--Offset:00056--cnt bytes:08--data is:0000000000000000

This value should represent the highest thing in all rows, ID, but it was not tested.

11 、 page level

Bcview km1.ibd 16 64 2 | more

Current block:00000003--Offset:00064--cnt bytes:02--data is:0000

Represents the hierarchy of the index, where the root node is the page node and is a node, so it is 0

12 、 index ID

Bcview km1.ibd 16 66 8 | more

Current block:00000003--Offset:00066--cnt bytes:08--data is:0000000000000255

This is the ID of the index, which is also available in INNODB_SYS_INDEXES.

After learning these contents, we have some understanding of the structure of INDEX PAGE.

To sum up:

1. Heap top position is the high water level of the block, which is the highest point the index page has ever reached.

2. Last insert position is the offset of the row inserted by the last index page. If the data is deleted, it will be used as a slave, so this value has nothing to do with heap top position.

3. Number of record is the number of undeleted rows of data in the block, and the data will change each time it is deleted and inserted.

4. Number of heap records/format flag records the number of undeleted rows + deleted rows + infimum and supremum. Undeleted rows can be reused, so this value may not change after you insert data.

5. Format flag is the 15th value. 1 is in compact format and 0 is Redundant.

6. If you do not add the primary key, a 6-byte 48-bit ROWID will be generated automatically, which will increase your storage space, so when you add the primary key as much as possible, when you build the table and insert the data, adding the primary key will not improve

7. Garbage space is the space left after deleting the data DELETE. With the continuous reuse, this space continues to decrease. If there is no delete, the available space is 0.

8. Index ID this is the ID of the index, which is also available in INNODB_SYS_INDEXES.

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.