What file is .ibd in mysql?

In MySQL, ibd is a data file and index file, which is mainly used to store data and is an essential part of MySQL. MySQL IBD files can improve the performance of MySQL databases and are very suitable for unstructured large databases. MySQL's IBD files have good scalability and can protect the integrity of the data; in the hierarchical database architecture mechanism, using IBD files can effectively expand the database without affecting the integrity of the database.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

ibd is the data file and index file of MySQL and cannot be read directly.

The importance of MySQL’s IBD file (mysqlibd)

MySQL’s IBD file is one of the key files of MySQL and is mainly used for storage Data is an essential part of MySQL. Here we talk about the importance of IBD files.

First of all, MySQL's IBD file can improve the performance of the MySQL database. In the process of storing data, if you do not use IBD files, you need to read the data through complete I/O operations, which will bring a lot of burden and reduce the performance of the database. But using IBD files is different. It utilizes compression algorithms to greatly reduce file size, making them easier to read and more transportable, thereby significantly improving database performance and transportability.

Secondly, MySQL's IBD file is very suitable for unstructured large databases. When storing unstructured large-scale data, using ordinary database file formats will cause problems such as inability to meet data types and field identification, and using IBD files can effectively solve these problems.

Finally, MySQL's IBD file is very scalable and can protect the integrity of the data. In a layered database architecture mechanism, using IBD files can effectively expand the database without affecting the integrity of the database. In addition, IBD files can also effectively support data consistency to ensure data integrity and security.

In short, MySQL's IBD file is of great significance, which can improve database performance and reconstruct MySQL's unstructured large database. Additionally, it can support scaling to large databases and maintain data integrity and consistency.

Preliminary analysis of MySQL ibd file format

After MySQL creates the table, 2 files will be created in the corresponding library folder: one One is frm and one is ibd. Let's briefly analyze the ibd file format.

Here we must first know some preliminary knowledge:

Check the InnoDB block size, usually 16k

show vaiables like '%page%'

From which you can see innodb_page_size is 16384 bytes.

Use ibd to divide the byte size, you can know how many blocks this file has!

For example:

##98304/16384 It can be seen that this gyj_t3.ibd has 6 pages

The data corresponding to this library is:

Look at the official file diagram:

First The block is: File Space Header

The second block is: Insert Buffer Bitmap

The third block is: File Segment inode

Starting from the fourth block, data is stored , put the corresponding ibd file under windows, open it and take a look. Let’s look directly at the fourth block, the data block. The header is marked as 0x45B

and search for it:

Let’s first verify whether this is the third block. The end of:

C018h, the corresponding decimal notation is 49176

The position of the first block:

is 0018h, the corresponding decimal notation is 24 .

The characters in front of this are the file header, which is used to identify the format of the file and other information.

Let’s calculate whether this is the end of the third block. The position is: (49176 - 24) / 16384 = 3 is just right.

This is the data bit!

Look at the corresponding data file:

As you can see, in the MySQL data segment, NULL and '' do not occupy space.

The length of int is 4 bytes, and the length of other variable-length names is 10 bytes, whichever is longer.

The second row of data ID: 80 00 00 03, the next row is at 80 00 00 04

[Related recommendations: mysql video tutorial]

The above is the detailed content of What file is .ibd in mysql?. For more information, please follow other related articles on the PHP Chinese website!