Firebird会进行碎片整理吗?如果可以的话，像聚集索引吗? [英] does Firebird defrag? If so, like a clustered index?

问题描述

我已经看到一些链接(实际上只有几个)，而在有关使用Firebird进行群集的文档中却没有看到可以实现的链接.

I've seen a few (literally, only a few) links and nothing in the documentation that talks about clustering with Firebird, that it can be done.

然后，我针对这个问题向月球开枪针对Firebird的CLUSTER命令?，但是回答者告诉我，Firebird甚至根本没有聚集索引，所以现在我真的很困惑.

Then, I shot for the moon on this question CLUSTER command for Firebird?, but answerer told me that Firebird doesn't even have clustered indexes at all, so now I'm really confused.

Firebird完全可以物理订购数据吗?如果是这样，是否可以通过任何键(不仅是主键)对其进行排序，并且是否可以打开和关闭群集/碎片整理功能，以便仅在停机期间进行操作?

Does Firebird physically order data at all? If so, can it be ordered by any key, not just primary, and can the clustering/defragging be turned on and off so that it only does it during downtime?

如果不是这样，这是否会对性能造成影响，因为将磁盘放在一起原本应该彼此相邻的不同行会花费更长的时间?

If not, isn't this a hit to performance since it will take the disk longer to put together disparate rows that naturally should be right next to each other?

(数据库菜鸟)

MVCC

我发现Firebird是基于MVCC的，因此直到扫描"，实际上旧数据才被覆盖.我非常喜欢！

I found out that Firebird is based upon MVCC, so old data actually isn't overwritten until a "sweep". I like that a lot!

同样，我找不到很多，但是似乎很遗憾，不会根据密钥对数据进行碎片整理.

Again, I can't find much, but it seems like a real shame that data wouldn't be defragged according to a key.

这表示对数据库页面进行了碎片整理，但未提供进一步的解释.

This says that database pages are defragmented but provides no further explanation.

推荐答案

Firebird不会对记录进行聚类.它被设计为避免需要聚类的问题以及聚簇索引所带来的碎片化问题.索引和数据分别存储在不同类型的页面上.每个数据页仅包含来自一个表的数据.记录按插入顺序，同时插入或同时插入的顺序存储，这些插入通常在单独的页面上进行.删除旧记录后，新记录将存储在它们的位置，因此新记录有时会与旧记录出现在同一页面上.

Firebird does not cluster records. It was designed to avoid the problems that require clustering and the fragmentation problems that come with clustered indexes. Indexes and data are stored separately, on different types of pages. Each data page contains data from only one table. Records are stored in the order they were inserted, give or take concurrent inserts, which generally go on separate pages. When old records are removed, new records will be stored in their place, so new records sometimes appear on the same page as older ones.

许多表使用人工主键(通常升序)，该主键可能是数据库生成的序列或时间戳.这种做法导致记录以关键顺序存储，但是绝对不能保证该顺序.也不是很有趣.当主键是人工的时，大多数返回相关记录组的查询都是在辅助索引上完成的.对于聚集的记录而言，这会带来性能上的损失，因为在二级索引上的查询需要遍历两个索引，因为二级索引仅提供了主索引的键，必须遍历该键才能找到数据.

Many tables use an artificial primary key, generally ascending, which might be a database generated sequence or a timestamp. That practice causes records to be stored in key order, but that order is by no means guaranteed. Nor is it very interesting. When the primary key is artificial, most queries that return groups of related records are done on secondary indexes. That's a performance hit for records that are clustered because look-ups on secondary indexes require traversing two indexes because the secondary index provides only the key to the primary index, which must be traversed to find the data.

在较大的碎片整理和空间使用问题上，Firebird跟踪页面上的可用空间，因此新记录将插入已删除记录的页面上.如果页面完全变空，将对其进行重新分配.数据库运行时将完成此空间管理.如您所知，Firebird使用多版本并发控制，因此当记录被更新或删除时，Firebird会创建一个新的记录版本，但会保留旧版本.提交更改之前运行的所有事务都结束后，旧记录版本将不再具有任何用途，并且Firebird会将其删除.在许多应用程序中，在运行数据库的正常过程中会删除旧版本.当事务处理具有旧版本的记录时，Firebird将检查旧版本的状态，如果没有正在运行的事务可以读取它们，则将其删除.有一个称为清除"的功能，可以系统地删除不需要的旧记录版本. Sweep可以与其他数据库活动同时运行，尽管最好在数据库负载较低时安排它.因此，在运行一次扫描之前，什么也不会清除.

On the larger issue of defragmentation and space usage, Firebird tracks the free space on pages so new records will be inserted on pages that have had records removed. If a page becomes completely empty, it will be reallocated. This space management is done as the database runs. As you know, Firebird uses Multi-Version Concurrency Control, so when a record is updated or deleted, Firebird creates a new record version, but keeps the old version around. When all transactions that were running before the change was committed have ended, the old record version no longer serves any purposes, and Firebird will remove it. In many applications, old versions are removed in the normal course of running the database. When a transaction touches a record with old versions, Firebird checks the state of the old versions and removes them if no running transaction can read them. There is a function called "Sweep" that systematically removes unneeded old record versions. Sweep can run concurrently with other database activity, though it's better to schedule it when the database load is low. So no, it's not true that nothing is removed until you run a sweep.

最诚挚的问候，

安·哈里森

谁曾与Firebird合作，并且是Firebird的前身，却经历了一段令人尴尬的长时间

who's worked with Firebird and it's predecessors for an embarassingly long time

顺便说一句-作为第一个回答的人，Firebird确实在页面上保留了空间，以便记录的旧版本与新版本保持在同一页面上.它不是固定的空间百分比，而是存储在页面上的每条记录16个字节，因此具有非常短记录的表的页面具有更多的可用空间，而具有长记录的表的页面具有更少的可用空间.

BTW - as the first person to answer mentioned, Firebird does leave space on pages so that the old version of a record stays on the same page as the newer version. It's not a fixed percentage of the space, but 16 bytes per record stored on the page, so pages of tables with very short records have more free space and tables that have long records have less.

这篇关于Firebird会进行碎片整理吗?如果可以的话，像聚集索引吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！