Exploring the Mysteries of MySQL IBD Files: A Comprehensive Guide（mysqlibd文件）
MySQL IBD files, also known as InnoDB data files, are essential to an effective MySQL database. Without them, the database wouldn’t be able to store, access, modify or index data. For many MySQL administrators and developers, these files can represent a bit of a mystery. How do the IBD files store data, and why are some of the files so much larger than others?
In this comprehensive guide, we’ll explore the mysteries of IBD files, and dive into the internals of how they work.
At a high level, every IBD file stores one or more MySQL databases. The files are organized as a set of tables and table indexes, so when you query data from one of these databases, the IBD files all get in line to answer the request.
Each file contains a number of components, including the main data file (usually called ibdata1), a file that stores the database log structures, a control file that stores information about the database (such as its version number), and a file that stores the index data.
The data file contains all of the actual data stored in the database. When a SQL query is executed, the IBD files transactions that modify, add or delete data in the database are written to this file.
The log structure file contains syncing information for each transaction in the database. This is important for data robustness, as the log structure file can be used to restore the database to a certain point in time if something goes wrong.
The control file contains various control information, such as the MySQL server’s version number, the number of IBD files in the database, and configuration settings.
Finally, the index file stores data that helps MySQL quickly look up what row and column is being queried. Without an index, these queries would process significantly slower.
Now that we’ve taken a look at what’s inside an IBD file, let’s explore how they grow in size.
The primary factor that determines the size of an IBD file is the amount of data stored within it. Every time data is modified, the file must grow to accommodate the changes. This is why the size of IBD files can vary significantly.
In addition, when the database is backed up, the log structure and index files are also written to the backup, which can increase the size of the IBD files.
As you can see, there are a lot of moving parts to MySQL IBD files. Hopefully this guide has taught you a bit about how they work, and why some of the files can end up being much larger than others. Knowing this information can help you better troubleshoot, back up, and maintain your MySQL databases.
