我有一个 MYSQL InnoDB 表table
,其中包含以下列(表和列名称已更改):
其中 rel_ab
是描述给定日期 2 个变量 var_a
和 var_b
之间关系的列。 (var_a
和 var_b
引用不同的表)
数据每天批量上传,每天总计约 700 万行。问题是,仅仅几周后,上传每个新的每日批次就开始需要几个小时。显然我们需要改进我们的表格设计。以下是我们表格的一些其他详细信息。
COMPRESSION="zlib"
。var_a
和 var_b
列上建立了外键所需的索引。
的查询 SELECT * FROM table WHERE date =
。选择仅需几分钟。var_a
和 var_b
引用的表中删除条目。df.to_sql('temp', con, if_exists='replace', index=False, method='multi')
上传,我们在其中插入忽略 < code>temp 到 table
,然后删除 temp
。因此,我计划至少执行以下操作之一:
var_a
和 var_b
上的外键约束,并依靠数据上传过程来正确完成所有操作。这是因为在我们的用例中,这两个索引实际上都没有提高查询速度。table_230501
的表,其中包含 var_a
、var_b
、rel_ab
列。这是因为我们一次只选择一个日期。我知道第一个解决方案可能会威胁数据完整性,而第二个解决方案会使我们的架构变得混乱。以我有限的经验,我也从未听说过第二种选择,也无法在网上找到这种设计的任何示例。这些选项中的任何一个都是明智的解决方案吗?两者都会提高上传速度并减少磁盘使用,但也都有其缺点。否则,还有哪些其他方法可以提高上传速度?
编辑:我的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粉7812356892023-09-10 13:05:25
有一些潜在的解决方案可以帮助您提高 MySQL 表的上传速度:
删除 var_a 和 var_b 上的索引:由于您没有使用这些索引来加速查询,因此删除它们可以帮助加快上传过程。但是,如果您使用外键约束,通常建议在属于外键的列上保留索引。
按日期对表进行分区:分区有助于提高查询性能,因为它允许数据库仅扫描给定查询的相关分区。但是,它也会使维护和备份变得更加复杂,如果您的查询已经表现良好,则可能没有必要。
使用批量插入方法:您可以尝试使用批量插入方法,例如 LOAD DATA INFILE 或 MySQL 批量插入 API,而不是使用 df.to_sql 插入单独的行。这比单独插入要快,特别是如果您可以批量上传数据而不是一次一行。
使用不同的压缩算法:您当前正在使用 zlib 压缩,但还有其他压缩算法可能对您的数据更快或更有效。您可以尝试尝试不同的压缩选项,看看它们是否可以提高上传速度。
增加服务器资源:如果您有预算和资源,升级服务器硬件或增加服务器数量可能有助于提高上传速度。这可能不是每个人都可行的选择,但如果您已经用尽其他选择,则值得考虑。
就您建议的选项而言,删除外键约束可能会导致数据完整性问题,因此我不推荐这种方法。如果您的查询已经遇到性能问题,则按日期分区可能是一个很好的解决方案,但如果您的查询已经快速运行,则可能没有必要。
P粉0989790482023-09-10 11:03:23
要加快上传速度,请将其删除。说真的,如果您所做的唯一一件事就是准确获取某个日期文件中的内容,为什么要将数据放入表中呢? (您的评论指出单个文件实际上是几个文件。首先将它们组合起来可能是一个好主意。)
如果您确实需要表中的数据,让我们讨论这些...
显示创建表
;您提供的内容可能遗漏了一些细微的内容。加载数据
?希望不是一次插入一行。我不知道熊猫是如何工作的。 (也不知道其他 99 个“简化”MySQL 访问的软件包是如何工作的。)请了解它的幕后功能。您可能必须绕过 Pandas 才能获得更好的性能。批量加载的速度至少是逐行加载的 10 倍。FLOAT
?MEDIUMINT [UNSIGNED]
每天至少可以节省 7MB。多个“相同”的表总是不明智的。一张桌子总是更好。但是,正如上面所建议的,零表仍然更好。