首页  >  问答  >  正文

为每个日期的相同数据创建不同的表是否明智?

我有一个 MYSQL InnoDB 表table,其中包含以下列(表和列名称已更改):

其中 rel_ab 是描述给定日期 2 个变量 var_avar_b 之间关系的列。 (var_avar_b 引用不同的表)

数据每天批量上传,每天总计约 700 万行。问题是,仅仅几周后,上传每个新的每日批次就开始需要几个小时。显然我们需要改进我们的表格设计。以下是我们表格的一些其他详细信息。

因此,我计划至少执行以下操作之一:

我知道第一个解决方案可能会威胁数据完整性,而第二个解决方案会使我们的架构变得混乱。以我有限的经验,我也从未听说过第二种选择,也无法在网上找到这种设计的任何示例。这些选项中的任何一个都是明智的解决方案吗?两者都会提高上传速度并减少磁盘使用,但也都有其缺点。否则,还有哪些其他方法可以提高上传速度?

编辑:我的SHOW CREATE TABLE应该看起来像

CREATE TABLE table (
  date date NOT NULL,
  var_a int NOT NULL,
  var_b int NOT NULL,
  rel_ab decimal(19,16) NOT NULL,
  PRIMARY KEY (date,`var_a`,`var_b`),
  KEY a_idx (var_a),
  KEY b_idx (var_b),
  CONSTRAINT a FOREIGN KEY (var_a) REFERENCES other_table_a (var_a) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT b FOREIGN KEY (var_b) REFERENCES other_table_b (var_b) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION="zlib"

P粉665679053P粉665679053426 天前489

全部回复(2)我来回复

  • P粉781235689

    P粉7812356892023-09-10 13:05:25

    有一些潜在的解决方案可以帮助您提高 MySQL 表的上传速度:

    删除 var_a 和 var_b 上的索引:由于您没有使用这些索引来加速查询,因此删除它们可以帮助加快上传过程。但是,如果您使用外键约束,通常建议在属于外键的列上保留索引。

    按日期对表进行分区:分区有助于提高查询性能,因为它允许数据库仅扫描给定查询的相关分区。但是,它也会使维护和备份变得更加复杂,如果您的查询已经表现良好,则可能没有必要。

    使用批量插入方法:您可以尝试使用批量插入方法,例如 LOAD DATA INFILE 或 MySQL 批量插入 API,而不是使用 df.to_sql 插入单独的行。这比单独插入要快,特别是如果您可以批量上传数据而不是一次一行。

    使用不同的压缩算法:您当前正在使用 zlib 压缩,但还有其他压缩算法可能对您的数据更快或更有效。您可以尝试尝试不同的压缩选项,看看它们是否可以提高上传速度。

    增加服务器资源:如果您有预算和资源,升级服务器硬件或增加服务器数量可能有助于提高上传速度。这可能不是每个人都可行的选择,但如果您已经用尽其他选择,则值得考虑。

    就您建议的选项而言,删除外键约束可能会导致数据完整性问题,因此我不推荐这种方法。如果您的查询已经遇到性能问题,则按日期分区可能是一个很好的解决方案,但如果您的查询已经快速运行,则可能没有必要。

    回复
    0
  • P粉098979048

    P粉0989790482023-09-10 11:03:23

    要加快上传速度,请将其删除。说真的,如果您所做的唯一一件事就是准确获取某个日期文件中的内容,为什么要将数据放入表中呢? (您的评论指出单个文件实际上是几个文件。首先将它们组合起来可能是一个好主意。)

    如果您确实需要表中的数据,让我们讨论这些...

    • 在确定索引之前,我们必须查看所有主要查询。
    • PK 中的列顺序对于加载和查询都很重要。
    • 分区可能有助于加载,但不太可能有助于查询。例外:您会删除“旧”数据吗?
    • 请提供显示创建表;您提供的内容可能遗漏了一些细微的内容。
    • 加载是如何完成的?一个巨大的加载数据?希望不是一次插入一行。我不知道熊猫是如何工作的。 (也不知道其他 99 个“简化”MySQL 访问的软件包是如何工作的。)请了解它的幕后功能。您可能必须绕过 Pandas 才能获得更好的性能。批量加载的速度至少是逐行加载的 10 倍。
    • 我在加载时还没有看到需要临时表。也许。如果您删除 FK(如您所建议的),您可以执行查询来验证其他表中 var_a 和 var_b 的存在。那就是“模拟FK”。
    • 如果可行,请根据 PK 对传入数据进行排序。 (这可能是经济增长缓慢的根源。)
    • 有辅助键吗?它们会影响加载速度。
    • 我认为您的 FK 暗示了其他表的索引。
    • 您是否正在向其他表添加新行?
    • “rel_ab (DECIMAL)”——多少位小数?确切的声明是什么?如果是某种测量,您是否考虑过FLOAT
    • 现在其他表中有很多行。也就是说,您真的需要一个 4 字节的 INT 来引用它们吗?切换到 3 字节 MEDIUMINT [UNSIGNED] 每天至少可以节省 7MB。
    • 您如何处理该 SELECT 中的 700 万行?
    • 无压缩。在InnoDB中效率很低。 4 列中只有一列可能是可压缩的。压缩需要额外的buffer_pool_space。压缩会占用大量CPU。对于 InnoDB,2 倍收缩是典型的。

    多个“相同”的表总是不明智的。一张桌子总是更好。但是,正如上面所建议的,零表仍然更好。

    回复
    0
  • 取消回复