表垂直分割的优点 [英] Advantages to Vertical Partitioning of Table

问题描述

（注意，这种情况并不完全如何，但我以此为例）

(Note that this situation isn't exactly how it is, but I made this as an example)

我有一个实体在每5秒更新一次的数据表（运动数据：速度，标题，纬度，长和位置时间）以及几乎没有更新的数据（如果有的话） ，OriginTime）。

I have an entity in a table with data that is updated every 5 seconds (Kinematic Data: Speed, Heading, Lat, Long, and PositionTime), and other data that is updated hardly at all, if ever (Color, Make, OriginTime).

alt text http ：//www.freeimagehosting.net/uploads/a67205e99e.jpg

现在我的老板要我把这个数据分割成数据库中的不同表格一对一关系），如下所示：

Now my boss wants me to partition this data into separate tables in our database (With a One to One Relationship), as so:

alt text http://www.freeimagehosting.net/uploads/1c699bc3c5.jpg

他使它听起来很明显，应该是这样，但是如果将这些数据分离成插入和更新（例如，如果我在Color或Make中放置一个索引），那么这个数据真的有什么好处吗？

He makes it sound "obvious" that it should be this way, but is there really any advantages to having this data separated as so for inserting and updating (For instance if I put an index on Color or Make)?

推荐答案

这样做垂直分区可能是有意义的。或者它可能不会。

It might make sense to do vertical partitioning like this. Or it might not.

当您使用基于MVCC的引擎时，每次更新一行时，通常会复制整个行，并创建一个新的修改。这就是说，如果还没有看到更新的其他交易可以继续阅读原始行，如果需要的话。

When you use a MVCC based engine, each time you update a row, it generally* copies the entire row and creates a new one with the modifications. This is so that other transactions which do not yet see the update can continue to read the original row if they need to.

这意味着经常更新几个小列非常宽的一行会导致数据库的写入次数比需要的多很多。

This means that updating a few small columns frequently in a very wide row causes the database to do a lot more writes than it needs to.

但不是那么多，因为引擎通常只会同步其事务日志，无论大小还是未更新的列，都将具有相同的大小，同时也是因为数据行通常存储在需要写入整个块的块中，无论其改变了多少。

But not that many, because generally the engine will only sync its transaction log, which will be the same size regardless of the size of non-updated columns, and also because the data rows are usually stored in blocks where a whole block needs to be written anyway, regardless of how much of it changed.

所以这听起来像一个潜在的无意义的优化，像其他任何一样，应该考虑在
a的基础上）是真的有一个性能问题（即任何优化需要）和
b）这个特殊的优化是修复它的最佳方式吗？

So it sounds like a potentially pointless optimisation, which like any other, should be considered on the grounds of a) IS there really a performance problem (i.e. is ANY optimisation needed) and b) Is this particular optimisation the best way of fixing it?

我认为a）的机会是不太可能的，b）也不太可能，所以机会这是所需的几乎不大可能的平方。

I think the chance of a) is unlikely, and b) is also unlikely, so the chances of this being required is approximately unlikely-squared.

*某些引擎对于非常大的列（例如大型BLOB或文本列）进行例外处理，这些列可以在其他地方进行，也不会被复制如果行中的其他列已更新。

* Some engines make an exception for very large columns such as big BLOBs or text columns, which are held elsewhere and not copied if other columns in the row are updated.

这篇关于表垂直分割的优点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！