For learning/experimental purposes I'm trying to mimic some techniques I learned from studying databases. And I'm curious as to how MySQL (and maybe other databases) solve this particular problem.
为了学习/实验的目的，我试着模仿一些我从研究数据库中学到的技术。我很好奇MySQL(也许还有其他数据库)是如何解决这个问题的。
So I'm writing an application that, like other databases, stores records side-by-side in a single file. I use another file for indexing the positions of the records to quickly look them up. And everything works fine until I need to update a row which is longer than the current version of itself. I have some ideas, but none seem too performance-friendly.
因此，我正在编写一个应用程序，与其他数据库一样，将记录并排存储在一个文件中。我使用另一个文件对记录的位置进行索引，以便快速查找它们。一切都很好，直到我需要更新一个比当前版本长一些的行。我有一些想法，但似乎都不太适合表演。
Let's say I want to update record 200 out of 1,000 records. In my logic I place the file cursor where the row begins, and write the data. Let's say the current version of the row is 100 bytes long (and from the 101th byte the next record begins). The new data is 150 bytes long, so just writing with the file cursor would effectively overwrite bytes from the next record.
假设我想更新1000条记录中的200条。在我的逻辑中，我将文件光标放在行开始的位置，并写入数据。假设行的当前版本是100字节长(从第101字节开始下一条记录)。新的数据长度为150字节，因此仅使用文件游标进行写入就可以有效地覆盖下一条记录的字节。
To my knowledge you cannot "push" data forward in a file from the cursor - and if I could it doesn't seem like the most performance-friendly operation.
据我所知，您不能从游标中将数据“推”到文件中——如果可以的话，这似乎不是最适合性能的操作。
I would have the option the append the new data and replace the current row with NULL bytes. But that seems to be a) a waste of space b) again, requiring a lot of machine work to rebuild the file without the NULL bytes
我将拥有附加新数据的选项，并用空字节替换当前行。但这似乎是a)浪费空间b)再次，需要大量的机器工作来重建文件而没有空字节
And then there's the option of defragmentation, but I'm not ready to go that direction yet.
还有一种选择就是碎片整理，但我还没准备好。
Does anybody know how other databases deal with this?
有人知道其他数据库是如何处理的吗?
1 个解决方案
#1
2
Other databases handle this in several ways. I can answer for MySQL.
其他数据库以多种方式处理此问题。我可以负责MySQL。
-
The first time you write a record into some space in the file, leave a bit of extra room. Organize the storage into "pages" of 16KB, which several records fit in. But leave 1/16 space empty initially, to allow for rows to expand. Each page is loaded into RAM on demand, and the records in it may be reorganized a bit before the page is written back to disk.
第一次将记录写入文件中的某个空间时，请预留一点空间。将存储组织为16KB的“页面”，其中包含几个记录。但是一开始要保留1/16的空格，以允许行展开。每个页面都按需加载到RAM中，并且在将页面写入磁盘之前，可以对其中的记录进行重新组织。
-
If the records grow beyond the space in a page, then they may be split up. Some records may be relocated to other new pages, which might be quite far apart. The indexing that keeps track of locations of records does not require the records to be adjacent.
如果记录超出了页面的空间，那么它们可能会被分割。有些记录可能会被重新放置到其他新页面，而这些新页面之间的距离可能非常遥远。跟踪记录位置的索引不要求记录是相邻的。
-
The empty space left from all the record reorganizing and splitting results in some fragmentation, but it might be a small percentage of the storage overall, so we don't worry about it. Eventually, the fragmentation may get worse, so it's a good idea from time to time to make a fresh copy of all the records into a new set of pages, reorganized more efficiently, to replace the original. How often you should do this depends on how much activity you've done in your database, so there's no strict rule for it.
所有记录重组和拆分所留下的空空间会导致一些碎片化，但这可能只是整个存储的一小部分，因此我们不必为此担心。最终，碎片化可能会变得更糟，因此不时地将所有记录的新副本制作成一组新的页面，以更有效地重新组织，以替换原来的页面，这是一个好主意。执行此操作的频率取决于您在数据库中执行了多少活动，因此没有严格的规则。
-
Another relatively recent new feature that might help is called sparse files, or hole-punching. Traditionally, all the contiguous bytes of a file occupy space on disk, whether you are storing useful data in those bytes or not. But what if the gaps within the file could be treated as free disk space? Then you wouldn't care about the fragmentation. This is not supported by all filesystems, and the "holes" are generally limited to multiples of the filesystem block size (e.g. 4KB).
另一个相对近期的新特性可能会有所帮助，它被称为稀疏文件或穿孔。传统上，文件的所有连续字节都占用磁盘空间，无论您是否将有用的数据存储在这些字节中。但是，如果文件中的空白可以作为空闲磁盘空间来处理呢?这样你就不会关心碎片化了。这不是所有文件系统都支持的，而且“漏洞”通常限制在文件系统块大小的倍数(例如4KB)。
MySQL 5.7 makes use of hole-punching in its page compression feature. MySQL still stores data in 16KB pages, but you can enable an optional compression of the data within a page. If the compression leaves a gap of at 4KB (the size of a filesystem block), it treats that as a hole, and frees the filesystem storage for it.
MySQL 5.7在其页面压缩特性中使用了穿孔。MySQL仍然在16KB页面中存储数据，但是您可以在页面中启用数据的可选压缩。如果压缩在4KB(文件系统块的大小)上留下了空白，它就把它当作一个漏洞，并释放文件系统的存储空间。
There are many other tricks possible. It's not worth trying to make storage optimized down to the byte, because as soon as you do, another data update will require you undo it. It's usually better to optimize for quick updates than for perfectly compact storage. Everything comes down to a tradeoff between different types of efficiency (for example, speed vs. storage), and you have to make some decisions about what's more important for your database.
还有很多其他的技巧。尝试将存储优化为字节是不值得的，因为一旦这样做，另一个数据更新将要求您撤消它。优化快速更新通常比优化完美的紧凑存储要好。所有事情都归结为不同类型的效率之间的权衡(例如，速度与存储)，您必须决定什么对您的数据库更重要。
#1
2
Other databases handle this in several ways. I can answer for MySQL.
其他数据库以多种方式处理此问题。我可以负责MySQL。
-
The first time you write a record into some space in the file, leave a bit of extra room. Organize the storage into "pages" of 16KB, which several records fit in. But leave 1/16 space empty initially, to allow for rows to expand. Each page is loaded into RAM on demand, and the records in it may be reorganized a bit before the page is written back to disk.
第一次将记录写入文件中的某个空间时，请预留一点空间。将存储组织为16KB的“页面”，其中包含几个记录。但是一开始要保留1/16的空格，以允许行展开。每个页面都按需加载到RAM中，并且在将页面写入磁盘之前，可以对其中的记录进行重新组织。
-
If the records grow beyond the space in a page, then they may be split up. Some records may be relocated to other new pages, which might be quite far apart. The indexing that keeps track of locations of records does not require the records to be adjacent.
如果记录超出了页面的空间，那么它们可能会被分割。有些记录可能会被重新放置到其他新页面，而这些新页面之间的距离可能非常遥远。跟踪记录位置的索引不要求记录是相邻的。
-
The empty space left from all the record reorganizing and splitting results in some fragmentation, but it might be a small percentage of the storage overall, so we don't worry about it. Eventually, the fragmentation may get worse, so it's a good idea from time to time to make a fresh copy of all the records into a new set of pages, reorganized more efficiently, to replace the original. How often you should do this depends on how much activity you've done in your database, so there's no strict rule for it.
所有记录重组和拆分所留下的空空间会导致一些碎片化，但这可能只是整个存储的一小部分，因此我们不必为此担心。最终，碎片化可能会变得更糟，因此不时地将所有记录的新副本制作成一组新的页面，以更有效地重新组织，以替换原来的页面，这是一个好主意。执行此操作的频率取决于您在数据库中执行了多少活动，因此没有严格的规则。
-
Another relatively recent new feature that might help is called sparse files, or hole-punching. Traditionally, all the contiguous bytes of a file occupy space on disk, whether you are storing useful data in those bytes or not. But what if the gaps within the file could be treated as free disk space? Then you wouldn't care about the fragmentation. This is not supported by all filesystems, and the "holes" are generally limited to multiples of the filesystem block size (e.g. 4KB).
另一个相对近期的新特性可能会有所帮助，它被称为稀疏文件或穿孔。传统上，文件的所有连续字节都占用磁盘空间，无论您是否将有用的数据存储在这些字节中。但是，如果文件中的空白可以作为空闲磁盘空间来处理呢?这样你就不会关心碎片化了。这不是所有文件系统都支持的，而且“漏洞”通常限制在文件系统块大小的倍数(例如4KB)。
MySQL 5.7 makes use of hole-punching in its page compression feature. MySQL still stores data in 16KB pages, but you can enable an optional compression of the data within a page. If the compression leaves a gap of at 4KB (the size of a filesystem block), it treats that as a hole, and frees the filesystem storage for it.
MySQL 5.7在其页面压缩特性中使用了穿孔。MySQL仍然在16KB页面中存储数据，但是您可以在页面中启用数据的可选压缩。如果压缩在4KB(文件系统块的大小)上留下了空白，它就把它当作一个漏洞，并释放文件系统的存储空间。
There are many other tricks possible. It's not worth trying to make storage optimized down to the byte, because as soon as you do, another data update will require you undo it. It's usually better to optimize for quick updates than for perfectly compact storage. Everything comes down to a tradeoff between different types of efficiency (for example, speed vs. storage), and you have to make some decisions about what's more important for your database.
还有很多其他的技巧。尝试将存储优化为字节是不值得的，因为一旦这样做，另一个数据更新将要求您撤消它。优化快速更新通常比优化完美的紧凑存储要好。所有事情都归结为不同类型的效率之间的权衡(例如，速度与存储)，您必须决定什么对您的数据库更重要。