优化的用于读写数据的mysql表模式
我正在从事一个项目，该项目需要将数据存储在mysql中，从多个跟踪设备进入服务器。数据间隔为10s。
目前，我们存储数据的方式如下：
每个设备都有一个表({Device_Number}_info)，其中以Unix时间戳作为主键。(因此，如果我们有10,000个设备，我们最终会出现在10,000个表中。这是为了防止锁定，因为我们每10s插入一次表)。
每10s插入一次数据到相应的表中，然后再进行访问。
这种方法的问题是，如果我们必须为每个设备获得一行-我们必须循环所有10,000个表并执行一个查询。我们尝试了所有优化查询和向表添加索引的可能方法，但都没有效果。遍历所有表并执行查询需要时间。我们的目标是获取<10s中的行。我觉得有些东西可以用mysql优化技术来改进。
我们试过的是：
我们创建了所有10,000个表的一个视图(采用联合)。然后询问风景。这也不起作用。需要超过2分钟。
对于如何为优化的读写设计模式有任何建议吗？
下面是{device_number}_info表的架构：
{device_number}_info:
device_number int(11) NOT NULL,
Date date NOT NULL,
Time time NOT NULL,
Timestamp int(10) unsigned DEFAULT NULL,
Speed float NOT NULL,
Latitude double NOT NULL,
Longitude double NOT NULL,
...
) ENGINE=InnoDB DEFAULT CHARSET=latin1;回答 2
Stack Overflow用户
发布于 2017-08-30 11:41:59
正如另一次讨论中所建议的那样：
- 将所有表合并到一个主表中
- 在查询的
Timestamp部分使用索引列(where)可以大大提高速度 - 增加
innodb_buffer_pool_size以减少磁盘IO时间
Stack Overflow用户
发布于 2017-08-30 17:18:35
“装置”在动吗？如果没有，请不要将lat/lng包括在表中。对于任何其他不变的值也是如此。
有一张单桌。
一定要有PRIMARY KEY(device_id, timestamp) --按这个顺序。请注意，这将将插入分离到表的不同部分。
不要(无充分理由)在date和time中重复该time。在大多数情况下，您可以动态转换。
DOUBLE对lat/液化天然气来说太过分了。有关较小的选项，请参见http://mysql.rjweb.org/doc.php/latlng#representation_choices。
缩小表的大小将提高性能。
当每秒插入1000行时，对它们进行批处理，然后使用单个LOAD DATA或单个多行INSERT进行处理。这将需要一些时间，但它应该是远小于10秒(跌落死亡的极限)，除非在“冷”的系统。
device_number可以是MEDIUMINT UNSIGNED (3个字节而不是4个字节；限制为16M-1.6crore)。
如果要在给定时间内获取所有设备的数据，则需要一个辅助INDEX(timestamp)。
请记住，更多的索引意味着较慢的INSERTs，因此请提供您认为需要的所有索引以及它们设计的查询。我们应该讨论一下。
你保存这些数据多长时间？听起来像每年300亿行？如果您正在清除，那么DELETE将成为一个严重的问题。我们可以讨论这个问题。
内存多少？硬盘还是SSD驱动器？
https://stackoverflow.com/questions/45957873
复制相似问题
腾讯云开发者
