mysql - dplyr - 使用索引进行查询优化

Question

在我的工作中，我们使用数据库（主要是 MySQL 5.7.2；这是我无法改变的）。任务通常是对价值数百万行的数据集编写复杂的查询。它的完成方式是通过建立在彼此之上的 MySQL 查询流（每个都是临时表；下一个临时表建立在前一个临时表之上，等等）。由于涉及多列连接的行数以百万计，因此使用 alter table 语句对这些临时表执行索引。

现在，当我尝试了解 dplyr 的使用并探索管道的强大功能时，对于我们解决的这类问题，它似乎是一个绝妙的解决方案；即，查询建立在彼此之上。因此，我编写了利用 dplyr 强大功能的简单脚本。我将所有查询发送回 MySQL 以评估最终结果（最终结果只有几百行）。所以所有的处理都将在远程 MySQL 服务器上完成，不需要将数百万行拉到本地 R 实例，只有结果集会被本地 R 实例拉取。

但是，我面临需要索引的瓶颈；由于性能问题，它是绝对需要的。如我所见，我可以在 dplyr 中使用 copy_to 函数，它使我能够添加索引；这里有两件事困扰着我：

1. 我需要在远程 MySQL 服务器上创建一个新表来存储 copy_to 结果（在大多数情况下，我们只允许使用临时表）。

2. 即使我设法编写了一个永久表，copy_to 函数也不允许我像在 MySQL 中那样定义自己的索引名称；而它提出的默认名称对于 MySQL 来说太长而无法接受（在索引名称长度上抛出错误）。

任何有关使用 dplyr 索引的建议将不胜感激。理想情况下，我想要如下粗体所示的内容：

Tbl1 %>% filter(...) %>% mutate(...) %>% left_join(Tbl2, by(“col1” = “col1”, “col2” = “col2”) %>% add_index(col_from_tbl1 , col_from_tbl2) %>% 其他操作

顺便说一句，我可以看到长索引名称的解决方案是 copy_to 一个新表；然后使用 DBI::dbSendQuery 以我选择的名称添加索引；这行得通。但是，索引的需要非常频繁。这使得使用管道的优雅消失了，因为我需要停在管道链的中间，复制到一个新表，然后使用 dbSendQuery 添加索引；然后再次开始使用管道。拥有可读性好的代码的全部意义就消失了。

标签： mysqlrindexingdplyr