mysql - 创建行与列的性能 [英] mysql - Creating rows vs. columns performance

问题描述

我建立了一个分析引擎，从数据库中提取50-100行原始数据（可以称为 raw_table ），在PHP中运行一组统计测量，然后得到恰好140个数据点，然后我需要存储在另一个表（让它调用 results_table ）。所有这些数据点都是非常小的整数（40，2.23， - 1024是数据类型的好例子）。

I built an analytics engine that pulls 50-100 rows of raw data from my database (lets call it raw_table), runs a bunch statistical measurements on it in PHP and then comes up with exactly 140 datapoints that I then need to store in another table (lets call it results_table). All of these data points are very small ints ("40","2.23","-1024" are good examples of the types of data).

最大mysql的列数相当高（4000+），但是性能真的开始下降，似乎有很多灰色区域。

I know the maximum # of columns for mysql is quite high (4000+) but there appears to be a lot of grey area as far as when performance really starts to degrade.

1）140个数据点可以是，如果它是更好的，分成20行的7个数据点，所有具有相同的 experiment_id '如果列数越少越好。然而，我总是需要拉所有20行（每个7列，加id，所以我不认为这将是比拉1行140列更好的性能。所以问题是：最好存储20行7-9列（这将需要一次拉）或140-143列的1行？

1) The 140 datapoints could be, if it is better, broken up into 20 rows of 7 data points all with the same 'experiment_id' if fewer columns is better. HOWEVER I would always need to pull ALL 20 rows (with 7 columns each, plus id, etc) so I wouldn't think this would be better performance than pulling 1 row of 140 columns. So the question: is it better to store 20 rows of 7-9 columns (that would all need to be pulled at once) or 1 row of 140-143 columns?

2）给定我的数据示例（40，2.23， - 1024是什么将被存储的好例子）我正在想 smallint 类型。任何反馈，性能方面或其他？

2) Given my data examples ("40","2.23","-1024" are good examples of what will be stored) I'm thinking smallint for the structure type. Any feedback there, performance-wise or otherwise?

3）任何其他反馈mysql性能问题或提示是受欢迎的。

3) Any other feedback on mysql performance issues or tips is welcome.

感谢您提前输入。

推荐答案

我认为存储为更多行

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

Also, if the 140 columns have the same meaning or if it differs per experiment - properly modeling the data according to normalization rules - i.e. how is data related to a candidate key.

至于性能，如果使用所有的列，它几乎没有什么区别。有时，对于大量数据，pivot / unpivot操作可能是昂贵的，但是对单个密钥访问模式几乎没有什么区别。有时，数据库中的一个枢轴可以使你的前端代码更简单，后端代码更加灵活面对变化。

As far as performance, if all the columns are used it makes very little difference. Sometimes a pivot/unpivot operation can be expensive over a large amount of data, but it makes little difference on a single key access pattern. Sometimes a pivot in the database can make your frontend code a lot simpler and backend code more flexible in the face of change.

如果你有很多NULL，它可能可以消除标准化设计中的行，这将节省空间。我不知道MySQL是否支持稀疏表的概念，这可能会在那里发挥。

If you have a lot of NULLs, it might be possible to eliminate rows in a normalized design and this would save space. I don't know if MySQL has support for a sparse table concept, which could come into play there.

这篇关于mysql - 创建行与列的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！