在R语言中从MySQL中查询/导入数百万行

Question

我有性能问题。我有一个索引表，有300万行，在MySQL中最终将达到1500万行。

当我运行一个简单的查询从R通过dbi/RMySQL

Select * from sales where transaction_year = 2022;

所以transaction_year在其他列之间被索引。这个表有60列。我花了一分钟。我的RAM是16gb, i7处理器。

是否有办法显著减少下载时间少于10秒?谢谢你。

Answer 1

我们真的需要看到SHOW CREATE TABLE和重要的查询。我假设你有

CREATE TABLE stuff (
id INT UNSIGNED NOT NULL  AUTO_INCREMENT,
transaction_year YEAR NOT NULL,
...
PRIMARY KEY(id)
) ENGINE=InnoDB;

在这种情况下，将PK更改为:

PRIMARY KEY(transaction_year, id),  -- to significantly speed up that  query
INDEX(id)    -- just to keep auto_inc happy

我不知道这是否会影响其他查询的性能;我真的需要了解更多的信息。

如果你真的有transaction_date DATE而不是year，那么

where transaction_year = 2022

必须是

where transaction_year >= "2022-01-01"
AND transaction_year  < "2022-01-01" + INTERVAL 1 YEAR

(我的索引建议仍然适用)

如果你希望在几年后清除旧数据，那么我们可以讨论分区。分区看到