在数据库中存储大量数据

Question

我有一个关于存储大量数据的问题。情况如下：

1. 我想要存储： - GPS坐标（纬度和经度）（每分钟或更短时间间隔，但我考虑每分钟） - 可以在多个坐标上重复的事件 - 条目的日期时间或时间戳（不知道在我的情况下哪个更好） - （用户ID）

2. 我希望能够查询： - 通过区域定义（例如从（1,1）到（2,2））查询事件 - 从日期X到日期Y跟踪一个或多个用户

到目前为止，我考虑的解决方案是： 解决方案1

id_user (int)
id_experince (int)
id_event (int)
dt (datetime)
latitude (decimal)
longitude (decimal)

我开始进行一些计算，大约每个用户每天会有500个条目；因为我正在为某种负载准备应用程序，可能会有100-150个用户，这将是75000个条目/天；一个月后就会有数百万个条目。解决方案1可能不是好的解决方案，因为数据库的大小将会快速增长。 解决方案2 有两张表，其中之一将根据事件汇总坐标，例如我有一个事件“晚餐”，需要30分钟，那么30个条目将归为一个具有BLOB类型的字段。此表将如下所示：

id_user (int)
id_experience (int)
id_event (int)
dt (datetime)
coordinates(blob)

还有另一张表格，计算了一些“宽度”和“长度”的位置，并指向第一张表格。

latitude (decimal)
longitude (decimal)
id_entry_in_first_table (int)

这个解决方案只是部分地解决了我的问题。想象一下，有些事件不会超过几分钟，需要第二个数据库... 解决方案 3 这可能不是非常正确的解决方案，但它似乎有一定意义。我有一个用户关联到某种经历，它有开始日期和结束日期。当添加经历时，我将创建该经历的数据转储并保存到文件中，并删除与该经历相关的条目。当用户想要查看“存档”的经历时，我将把数据加载到某个临时表中，并在一天内删除它（例如），在这种情况下，我将按照解决方案1保存数据。

主要问题是：在数据库性能方面，这些解决方案中是否有可接受的方案？对于我的问题，是否有更好的解决方案？

- Serhiy

---

浮点数是用于经纬度非常糟糕的数据类型。它不是精确的，会导致距离计算错误。使用预先定义好小数位数的十进制数。 - HLGEM

MySQL有空间数据类型吗？这就是你应该使用的。 - Stephanie Page

目前我已经使用十进制设计了数据库，抱歉之前用了浮点数的伪代码，不过还是提前感谢你。 - Serhiy

Answer 1

我会选择主从关系的方法。

有两个优点：

1. 您没有冗余条目（1个主行和x个带有坐标的子行）

2. 相比于blob方法，这种方法仍然很容易查询。

   SELECT m.id_user, m.id_experince, m.id_event, c.latitude, c.longitude
   FROM master_table m
   LEFT JOIN child_table c ON m.id = c.master_table_id
   

如果在主表上设置了外键或索引，即使主表中有数百万条记录，这也应该非常快。

- Jürgen Steinblock

---

而且表的数量不是问题吗？我也在考虑这个解决方案，例如为每个经验制作“子”表。 - Serhiy

为什么要使用多个数据表？您可以将所有内容存储在单个表中，并创建到主表的关系。如果您想获取一个经验的所有坐标，可以从经验表连接到主表（例如）再连接到子表：SELECT e.name, c.latitude, c.longitude FROM experience e LEFT JOIN master_table m ON e.id = m.id_experience LEFT JOIN child_table c ON m.id = c.master_table_id - Jürgen Steinblock

Answer 2

“数百万条记录”听起来很多，但这正是数据库设计的目的。无论你如何设计它，如果你根据以后从中提取结果的方式进行优化（因为这将花费时间而不是插入），那么你就可以放心使用。

当然，如果你有很多用户同时对数据库进行大量操作，那么我认为你的服务器/带宽会先崩溃，而不是你的数据库！

- Codecraft

---

但是你认为即使有索引表，有数百万条目，数据库也不会成为瓶颈吗？ - Serhiy

数百万条记录只是一个非常小的数据库！如果正确设计，关系型数据库通常可以存储数十亿条记录并且表现良好。 - HLGEM

当你处理分钟级别的数据时，如果插入操作需要超过一分钟的时间，那么你永远也赶不上。高频数据的插入速度非常重要，不能轻易忽略。我在一家大型公用事业公司工作，我们从成千上万个仪表中获取每分钟的数据。我们必须绝对优化插入操作。 - Stephanie Page

所以，您建议坚持使用我在问题中提出的解决方案1，并根据我的需求进行优化（重点放在数据插入上）？ - Serhiy

如果我在设计你所建议的内容，我会选择你的解决方案#1，因为将每个捕获数据集作为单独的记录存储，后期选择和分析数据会更加容易-即使它会产生更多的记录，但你的SELECT查询语句将变得更简单。 - Codecraft

Answer 3

你可能想要阅读这个链接：http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html。

总的来说，只要你能在查询中使用索引，巨大的表格不是问题——消费级笔记本电脑可以查询数十亿条记录。如果你打算扩展到大量历史记录，应该有一个归档策略，但这并不是一个很大的优先事项。

更棘手的是支持你想要在某个地理边界内查找事件的愿望；这很容易破坏你的索引策略，导致各种令人讨厌的问题。如果你必须基于数学运算进行查询，可能无法使用索引——因此，在数据库表中为每个记录计算一个半径为1英里的圆内的用户可能需要评估圆公式。

空间扩展提供了一个解决方案——但它们不是“免费”的，你必须专门为此优化你的设计。

- Neville Kuyt