为什么两个mysql文件（相同的表，相同的内容）大小不同？
我采用了现有的 MySQL 数据库，并在新主机上设置了一个副本。
新主机上某些表的文件大小比旧主机上的对应文件小 1-3%。
我很好奇这是为什么。
我的猜测是，旧主机的文件随着时间的推移而增长，并且在该文件的 b 树结构中，存在更多碎片。 而新主机，因为它是从头开始创建文件（通过二进制日志），所以避免了这种碎片。
B 树结构本身存在碎片是否有意义？ （在数据库层内说话，而不是操作系统文件系统层）我最初认为“不”，但话又说回来，这样的碎片难道不是DBA压缩数据库文件任务的基础吗？
我想知道这是否只是文件系统层的一个产物。 即新主机的磁盘驱动器大部分是空的，因此更少的碎片将导致分配新文件。 话又说回来，我不认为碎片会出现在报告的文件大小（Linux 操作系统）中。
I took an existing MySQL database, and set up a copy on a new host.
The file size for some tables on the new host are 1-3% smaller than their counterpart files on the old host.
I am curious why that is.
My guess is, the old host's files have grown over time, and within the b-tree structure for that file, there is more fragmentation. Whereas the new host, because it was creating the file from scratch (via a binary log), avoided such fragmentation.
Does it even make sense for there to be fragmentation within the b-tree structure itself? (Speaking within the database layer, and not with regards to the OS file system layer) I originally thought "no", but then again, isn't such fragmentation the basis for the DBA task of compressing your database files?
I'm wondering maybe if this is simply an artifact of the file system layer. i.e. the new host has a mostly empty disk drive, hence less fragmentation would result in the allocation of a new file. Then again, I didn't think that fragmentation would show up in the reported file size (Linux OS).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
MySQL 数据文件或索引文件中肯定存在碎片。 这是很常见的，甚至是故意的。
也就是说，存储引擎可能会故意在各处留下一些额外的空间，因此当您更改值时，它可以容纳行而无需重新排序整个数据文件。 您甚至可以使用服务器属性来配置要分配的倾斜空间的大小。
对于 1-3% 的文件差异，我什至不会眨眼。
There can certainly be fragmentation in MySQL data files or index files. This is common, even deliberate.
That is, the storage engine may deliberately leave some extra space here and there so when you change values, it can fit the rows in without having to reorder the whole data file. There are even server properties you can use to configure how much of this slop space to allocate.
I wouldn't even blink at a file discrepancy of 1-3%.
根据我对 mysql 的理解，它在接近容量时有一个增长算法，安装时它选择不同的大小，Prolly 修剪多余的存储
From what i understand of mysql It has a growth algo as it approaches capacity, when mounted it chose a different size, prolly trimming excess storage