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

标签： mysqldatabase-designquery-optimization

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分钟vs 5分钟？你的意思是在后一种情况下你只会收集五分之一的行吗？我们可以在其他细节提出后讨论这个问题。
• 该查询在多种方面都没有意义。为什么停在“1000”？输出相当大；什么客户关心这么多数据？排序是不确定的——日期时间不能保证是有序的。为什么指定usd而不指定日期时间？请提供理由查询；那我可以帮你INDEX(es)。