mysql - 如何设计一个每分钟增加很多行的数据库/表

Question

我的情况是每分钟需要存储 1900 多种加密货币的数据，我使用 MySQL innoDB。

目前，表格是这样的

coins_minute_id | coins_minute_coin_fk | coins_minute_usd | coins_minute_btc | coins_minute_datetime | coins_minute_timestamp

coins_minute_id = autoincrement id
coins_minute_coin_fk  = medium int unsigned
coins_minute_usd  = decimal 20,6
coins_minute_btc = decimal 20,8
coins_minute_datetime = datetime
coins_minute_timestamp = timestamp

表很快就增长得惊人，每分钟有 1900 多行添加到表中。

数据将作为每种加密货币的 D3.js 折线图用于历史价格显示。

我的问题是我如何最好地优化这个数据库，我想过只每 5 分钟而不是 1 次收集数据，但它仍然会立即加起来很多数据，我也想过如果最好为每种加密货币创建一个唯一的表，喜欢设计数据库的人知道其他一些非常聪明的方法来做这样的事情吗？

亲切的问候

(来自评论)

SELECT  coins_minute_coin_fk, coins_minute_usd
    FROM  coins_minutes
    WHERE  coins_minute_datetime >= DATE_ADD(NOW(),INTERVAL -1 DAY)
      AND  coins_minute_coin_fk <= 1000
    ORDER BY  coins_minute_coin_fk ASC

Answer 1

• 摆脱coins_minute_前缀；它在不提供任何有用信息的情况下使 SQL 困惑。
• 不要两次指定时间 -- DATETIME 和 TIMESTAMP 之间有简单的转换函数。 为什么你有两个 '创建”和“更新”时间戳？您在执行 UPDATE 语句吗？如果是这样，那么代码比简单的“插入”更复杂。您需要一个唯一的键来知道要更新哪一行。
• 提供SHOW CREATE TABLE；它比您提供的内容更具描述性。
• 30 次插入/秒很容易处理。 300/秒可能有问题。
• 如果没有真正的理由，不要PARTITION 表。常见的有效 原因是您想定期删除“旧”数据。如果您在 3 个月后删除，我将使用 PARTITION BY RANGE(TO_DAYS(...)) 构建表并使用每周分区。更多讨论:http://mysql.rjweb.org/doc.php/partitionmaint
• 向我们展示查询。如果不知道如何访问模式，就无法对其进行优化。
• “批量”插入比单行 INSERT 语句快得多。这可以采用 INSERT INTO x (a,b) VALUES (1,2), (11,22), ... 或 LOAD DATA INFILE 的形式。如果您已经有 CSV 文件，后者就非常有用。
• 您的数据是否来自单一来源？还是 1900 个不同的来源？
• 对于您的任务而言，MySQL 和 MariaDB 可能是相同的。 (同样，需要查看查询。)PDO 对任何一个都很好；无需重新编码。
• 看到查询后，我们可以讨论要有什么 PRIMARY KEY 和要有什么辅助 INDEX(es)。
• 1 分钟还是 5 分钟？你的意思是在后一种情况下你只会收集五分之一的行数吗？我们可以在其他细节提出来后再讨论。
• 该查询在多个方面都没有意义。为什么停在“1000”？产量相当大；什么客户关心那么多数据？排序是不确定的——不能保证日期时间是有序的。为什么指定 us 而不指定日期时间？请提供理由查询；那么我可以帮助您处理 INDEX(es)。