如何设置MySQL表以快速选择

Question

问题是关于 *.fit文件（链接到定义）不断更多），从运动手表，速度计， 其中始终有时间戳（1到n秒），以及1至n个参数（还具有时间戳或计数器为1到x）。

要执行数据分析，我需要数据库中的数据来计算几个拟合文件 /培训单元 /时间段的高度相关的心率。

由于拟合文件中的参数数量变化（取决于连接的设备，创建文件等的设备）以及将来集成更多/新参数的可能性，我的想法是为每个参数，而不是在一个大表中写所有内容（每当拟合文件中不存在参数时，它就会有很多“空”单元格）。

基本表：

1 x tbl_file < / strong>

ID	文件	名日期
1	xyz.fit	2022-01-01
2	vwx.fit	2022-01-02
..	.. ..	..

nx tbl_parameter_xy_xy / tbl_ parameter_yz /。 ...

id	Timestamp/counter	File_id	值
1	0	1	值
2	1	1	值
3	0	2	值
..	..	..	.. .....

然后将通过 file_id互相链接这些参数表以及适合文件。

然后，我使用了一台测试服务器，设置了MySQL-DB进行测试并感到震惊：

SELECT * FROM tbl_parameter_xy as x
LEFT JOIN tbl_parameter_yz as y
ON x.file_id = y.file_id
WHERE x.file_id = 999

花了几乎 30秒给我结果。

在我的参数表中有 209918 行。 File_id 999由 1964 行组成。 但是我选择加入返回 3857269 行，因此必须有一个/错误，这就是为什么它需要30秒的原因。

相比之下，从“大完整”表中获取 0.5秒：

SELECT * FROM tbl_all_parameters 
WHERE file_id = 999

经过一些研究，我遇到了索引，并认为我已经有了解决方案。 我为每个参数表创建了一个索引（file_id），但结果甚至更慢/相同。

现在，我正在考虑构建那张大“一张”桌子，这使得更易于处理和更快地选择，但是我必须经常更新它，以将新的Cols插入新的Cols以获取新的新科尔斯参数。我害怕它会变得如此大，以至于自我杀死

我有2个问题：

• 建议使用哪个桌子设置，主要专注于选择速度，次要db的大小。
• 我有我选择中的一个基本错误使其如此慢？

解释选择

Answer 1

您正在加入中会有组合爆炸。您的结果集在两个参数表中的每对输入行中包含一个输出行。

如果您说的

  SELECT * FROM a LEFT JOIN b

根本没有条件，则会在结果集中获得计数（a） *计数（b）行。您说的是，这

  SELECT * FROM a LEFT JOIN b WHERE a.file_id = b.file_id

为您提供了类似的结果集。

您需要另一个条件...可能会尝试一下。

SELECT * 
  FROM tbl_parameter_xy as x
  LEFT JOIN tbl_parameter_yz as y
         ON x.file_id = y.file_id
        AND x.timestamp = y.timestamp

如果两个表中的时间戳在某种程度上是同步的。

但是，就尊重而言，我认为您还没有很好的数据库设计。
这是一种创建最佳数据库布局的棘手数据，因为它是可扩展的。

如果您发现自己经常在生产中创建新表（例如，在添加新设备类型时），您几乎可以肯定会误导您的数据库。

您可能采用的一种方法是创建一个属性 /值表。它会有很多行，但是它们会简短且易于索引。

您的观察结果将进入这样的桌子。

file_id       part of your primary key 
parameter_id  part of your primary key
timestamp     part of your primary key
value

然后，当您需要从特定文件中检索参数2和3时，我建议您执行

    SELECT timestamp, parameter_id, value
      FROM observation_table
     WHERE file_id = xxxx
       AND parameter_id IN (2,3)
     ORDER BY timestamp, parameter_id

多列主键，我建议将优化此特定查询。

完成此工作后，请阅读有关统计化的信息。