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

This article mainly introduces "example Analysis of INNODB SYSTEM RECORD infimum and supremum". In daily operation, I believe many people have doubts about the example analysis of INNODB SYSTEM RECORD infimum and supremum. The editor consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "example Analysis of INNODB SYSTEM RECORD infimum and supremum". Next, please follow the editor to study!

We know that the NEXT pointer of the last linked list in a C language points to a NULL null pointer, so here SUPREMUM is actually a NULL null pointer and 0.

They are fixed in 94-120 bytes of the block, of which 94-107 is infimum-related information and 107-120 is supremum-related information.

Info flags 4bits

Number of records owned 4bits

Order 13bits

Record type 3bits

Next record offset 2bytes

"infimum\ 0" 8bytes (C language array ends with\ 0)

The above is infimum fixed information.

Info flags 4bits

Number of records owned 4bits

Order 13bits

Record type 3bits

Next record offset 2bytes

"supremum" 8bytes

The above is supremum fixed information.

We also use bcview to view infimum information, of course, block 3 (0 is actually the fourth block).

Bcview km1.ibd 16 94 16 | more

Current block:00000003--Offset:00094--cnt bytes:13--data is:0100020041696e66696d756d00

1 、 info flags

These four-bit (4bits) marks are a row identity, where binary 0001 represents the smallest row, where binary 0010 indicates deleted rows, and infimum and supremum rows are binary 0000 in my test database.

2 、 number of records owned

These four bits (4bits) represent the number of records in this page directory (slot). The concept of slot is discussed in detail later.

3 、 order

These 13 bits (13bits) represent the order in which records are inserted into the block, where INFIMUM is always equal to 0 and SPREMUM is always equal to 1, while the ORDER of data rows starts at 2.

4 、 record type

These three bits (3bits) represent the type of record, supermum is always equal to 3 and binary 011 and binary010, node pointer is 1 and 001, data behavior is 000

5 、 next record offset

These two bytes represent the offset of the first row in INFIMUM, which is the position of the current record + offset, and this offset points directly to the data and the associated header is the overhead of the line at the beginning of the offset-n.

Of course, the offset of supermum is the null pointer of NULL.

6. "infimum\ 0" OR "supremum"

There's nothing to explain but the actual ASCII value.

We still apply my writing tools mysqlblock and bcview for verification.

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

* * *

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

You can see

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

It's the actual data, because I only have

Mysql > select * from km1

+-+ +

| | id | name |

+-+ +

| | 2 | gaopeng |

| | 4 | gaopeng |

| | 5 | gaopeng |

| | 6 | gaopeng |

| | 7 | gaopeng |

| | 8 | gaopeng |

+-+ +

6 rows in set (0.04 sec)

6 lines of data, which was left by the last test, partly from the DELETE space used, that is to say, your INSERT is not necessarily stored sequentially in the file.

Because the delete space will be reused.

[root@hadoop1 test] # bcview km1.ibd 16 94 26 | more

*

This Tool Is Uesed For Find The Data In Binary format (Hexadecimal)

Usage:./bcview file blocksize offset cnt-bytes!

File: Is Your File Will To Find Data!

Blocksize: Is N kb Block.Eg: 8 Is 8 Kb Blocksize (Oracle)!

Eg: 16 Is 16 Kb Blocksize (Innodb)!

Offset:Is Every Block Offset Your Want Start!

Cnt-bytes:Is After Offset,How Bytes Your Want Gets!

Edtor QQ:22389860!

Used gcc version 4.1.2 20080704 (Red Hat 4.1.2-46)

*

-Current file size is: 0.093750 Mb

-Current use set blockszie is 16 Kb

Current block:00000000--Offset:00094--cnt bytes:26--data is:00000000ffffffff0000ffffffff000000000000000000030000

Current block:00000001--Offset:00094--cnt bytes:26--data is:0000000000000000000000000000000000000000000000000000

Current block:00000002--Offset:00094--cnt bytes:26--data is:00000000ffffffff0000ffffffff000005d669d200000003ffff

Current block:00000003--Offset:00094--cnt bytes:26--data is:0100020041696e66696d756d0007000b000073757072656d756d

Current block:00000004--Offset:00094--cnt bytes:26--data is:0000000000000000000000000000000000000000000000000000

Current block:00000005--Offset:00094--cnt bytes:26--data is:0000000000000000000000000000000000000000000000000000

We only study the actual data of block current block:00000003

Current block:00000003--Offset:00094--cnt bytes:26--data is:0100020041696e66696d756d0007000b000073757072656d756d

Break it down:

0100020041696e66696d756d00 is the related information of infimum.

07000b000073757072656d756d is the related information of SPREMUM.

1 、 info flags

4

0X0 infimum

0X0 supremum

The infimum and supremum lines are binary 0000 in my test database

2 、 number of records owned

4

0X1 infimum binary 0001 1 line

0X7 supremum binary 0111 7 lines

3 、 order

Since 13 bits cannot be converted to hexadecimal, binary binary is given directly.

Infimum 0X0002 two bytes 0000 0000 0000 0010

Supermum 0X000b two bytes 0000 0000 0000 1011

Binary 0000 0000 0000 0 infimum can see that infimum is indeed 0 in these 13 bits.

Binary 0000 0000 0000 1 supermum can see that supermum is indeed 1 in these 13 bits.

4 、 record type

Infimum 3 bits are binary 010 and decimal 2. We can see that infimum is always equal to 2 and binary 010.

Supermum 3 bits are binary 011 and 10 base 3, we can see that supermum is always equal to 3 and binary 011.

5 、 next record offset

Infimum 2 bytes is 0X0041 where infimum points to the next line pointer

Supermum 2 bytes is 0X0000 where supermum is a null pointer 0X0000

So we need to see if infimum's pointer 0X0041 points to the data.

The 0X0041 decimal is 65, which is the relative offset of the end position of infimum header and 99.

We know that the int type is 4BYTES and my data 'gaopeng' is 7 bytes.

So it's 11 bytes.

At the same time, in our page data block, of course, I have only one block here, that is, the page block.

His data structure is actually as follows:

The number of CLUSTER KEY FIELDS primary key bytes, which I don't have here is ROWID,6 bytes

Transaction id fixed 6 bytes

Roll pointer fixed 7 bytes

Non-key fields is the number of bytes of data. I have 11 bytes here.

Then we can calculate that the number of bytes viewed at the starting position of 99 "65" 164 is 11 "6" 6 "7" 30.

Notice that the offset here is 65. Obviously, this is not the first row of physics, the first row of order sorting. Order is the order of insertion, the first line has been dropped by my DELETE, and the space has been reused.

Bcview km1.ibd 16 164 30 | more

Current block:00000003--Offset:00164--cnt bytes:30--data is:000001cc64260000002d0272d300000d1201108000000267616f70656e67

Decompose the data.

000001cc6426 ROWID

0000002d0272 transaction id

D300000d120110 roll pointer

80000002 data 2, where 8 appears in the 15th bit, which may be a symbol bit

The ascII value of the 67616f70656e67 data 'gaopeng'

You can see that there is no problem, we found his data, because the ROW HEADER is mutable, so this pointer points to the beginning of the data, not including the header.

The header information needs to be calculated after the offset image is offset by N bytes. Of course, this will be discussed later.

The format of the line will be given in a future article, where infimum and supermum are important explanations.

6. "infimum\ 0" and "supermum"

There's nothing to say about this. It's the ASCII value.

696e66696d756d00 = "infimum\ 0"

73757072656d756d = "supermum"

Last

Here's a test to try the bigint UNSIGNED type.

First test whether bigint is 8 bytes, and test symbol bits at the same time

Mysql > create table km15 (id bigint UNSIGNED, name varchar (20))

Query OK, 0 rows affected (0.15 sec)

Mysql > insert into km15 values (10MAGONG')

Query OK, 1 row affected (0.02 sec)

No primary key is also required.

Bcview km15.ibd 16 94 26 | more

Current block:00000003--Offset:00094--cnt bytes:26--data is:010002001c696e66696d756d0002000b000073757072656d756d

Decompose data

010002001c696e66696d756d00 infimum

02000b000073757072656d756d supermum

Offset of infimum row 001c=28

99, 28, 127

The number of CLUSTER KEY FIELDS primary key bytes, which I don't have here is ROWID,6 bytes

Transaction id fixed 6 bytes

Roll pointer fixed 7 bytes

Non-key fields is the number of bytes of data. Here I have 8 (bigint) + 7 bytes = 15 bytes.

Bcview km15.ibd 16 127 34 | more

Current block:00000003--Offset:00127--cnt bytes:34--data is:000001cc680a0000002d0e74e0000080240110000000000000000a67616f70656e67

Decompose data

000001cc680a

0000002d0e74

E0000080240110

Actual data of 0000000000000a 10

67616f70656e67 data 'goapeng'

There is no problem that the bigint UNSIGNED is 8 bytes and there is no symbol bit, of course, there is no symbol bit, the maximum data stored is 2 ^ 64, if there is a symbol, of course one bit less.

That is, from-2 ^ 32 to 2 ^ 32, it is not difficult to understand if there is a foundation of the C language.

At this point, the study of "example Analysis of INNODB SYSTEM RECORD infimum and supremum" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.