MySQL - 创建行 vs 列的性能表现

Question

我建立了一个分析引擎，从我的数据库中提取50-100行原始数据（我们称之为raw_table），在PHP中运行一堆统计测量，并得出确切的140个数据点，然后我需要将它们存储在另一个表格中（我们称之为results_table）。所有这些数据点都是非常小的整数（“40”、“2.23”、“-1024”是这些数据类型的好例子）。

我知道mysql的最大列数相当高（4000+），但在性能何时真正开始下降方面存在很多灰色地带。

因此，在最佳性能实践方面有几个问题：

1）如果少列更好，则可以将这140个数据点分成20行，每行7个数据点，所有行都具有相同的“experiment_id”。但是，我总是需要拉取所有20行（每行有7列，加上id等），因此我认为这不比拉取140列的1行更好。那么问题来了：是将20行7-9列（所有行都需要一次性拉取）存储更好，还是将1行140-143列存储更好？

2）考虑到我的数据示例（“40”、“2.23”、“-1024”是将被存储的数据类型的好例子），我认为smallint是结构类型。在性能方面还有其他反馈吗？

3）欢迎提供关于mysql性能问题或技巧的任何其他反馈。

感谢您提前的帮助。

- themerlinproject

---

希望您知道int和int(1)在大小上是相同的，即使用相同数量的字节进行存储（只有在启用zero-padding时长度才有影响）。此外，如果数字不能为负数，则可以使用unsigned。同时，您无法将浮点数（例如2.23）存储在int类型中。 - Amil Waduwawara

那就是“double”啦，谢谢。对于行和列的问题，您有什么建议吗？ - themerlinproject

Answer 1

我认为将数据存储为更多的行（即规范化）的优点取决于面对变化时的设计和维护考虑因素。

此外，如果这 140 列具有相同的含义或每个实验都不同 - 根据规范化规则正确地建模数据 - 即数据与候选键的关系如何。

就性能而言，如果所有列都被使用，它几乎没有任何区别。有时，在大量数据上进行旋转/逆旋转操作可能很昂贵，但对于单个键访问模式来说几乎没有什么影响。有时，在数据库中进行旋转可以使您的前端代码更加简单，并使后端代码在面对变化时更加灵活。

如果存在大量空值，可能可以在规范化设计中消除行，从而节省空间。我不知道 MySQL 是否支持稀疏表概念，这可能会产生影响。

- Cade Roux

---

感谢您的回复。我决定选择20x7，因为它将在未来给我更多的灵活性。没有NULL值。 - themerlinproject

Answer 2

每次需要返回 140 个双精度数据项。

无论是 1x140、20x7、7x20 还是 4x35 等等，实际上并没有什么区别。当然，某些形状可能会稍微快一点，但您是否考虑过处理不同形状所需的 PHP 代码的额外复杂性。

您是否已经确认了瓶颈，或者这只是随意的过早优化？

- gbn

---

感谢您的回复。我决定选择20x7，因为它将在未来给我更多的灵活性。我更喜欢使用“仔细规划”这个术语，而不是“过早优化” ;) - themerlinproject

Answer 3

您并没有提出您打算在数据库中存储大数据的建议，但为了这个讨论，我将假设您有10亿（10^9）个数据点。

如果您将它们存储在140列中，那么您只会有700万行，但是，如果您想从许多实验中检索单个数据点，则必须获取大量非常宽的行。

这些非常宽的行将占用更多的innodb_buffer_pool空间，因此您将无法缓存太多内容；当您再次访问它们时，这可能会使您的速度变慢。

如果您每行存储一个数据点，在一个具有非常少列（实验ID，数据点ID，值）的表中，那么您需要提取相同数量的较小行。

但是，行的大小对所需的IO操作次数几乎没有影响。如果我们假设您的10亿个数据点不适合RAM（这在现今不是一个安全的假设），那么结果性能可能大致相同。

使用较少的列可能是更好的数据库设计；但是，如果您使用许多列，则将使用更少的磁盘空间，并且也许更快地填充。

- MarkR