生成意外SQL查询的dbplyr

Question

我使用MySQL向dbplyr数据库发送一个简单的查询

library(dbplyr)

my_conn<-dbConnect(...)
tab<-tbl(my_conn, "myTable")

tab %>% select(id, date, type) %>%
    filter(type = 'foobar')

但是，如果我使用show_query()检查生成的SQL，就会得到以下结果：

SELECT *
FROM (SELECT `id`, `date`, `type`
FROM `myTable`) `q01`
WHERE (`type` == 'foobar')

这个查询执行起来非常慢。

如果我转而在服务器上执行以下SQL命令：

SELECT id, date, type FROM db.myTable WHERE type = 'foobar'

然后返回几乎是瞬时的。

我的问题是:为什么dbplyr要执行SELECT * (即选择all)，然后执行第2行中的嵌套选择？此外，为什么在这个查询中出现了"q01“？这可能是为什么与直接在服务器上执行最小命令相比，我的查询运行得非常慢吗？随着操作复杂性的增加，我可能会期望dbplyr创建效率低下的SQL查询，但是--至少在本例中--我不能编写一组更简洁的操作。它只是一个select和过滤器(SELECT和WHERE)。

Answer 1

dbplyr正在像我预期的那样生成SQL查询。它所做的是在另一个查询中执行：

SELECT id, date, type FROM myTable

是超级查询中的子查询。

SELECT *
FROM (
   subquery
) q01
WHERE type = foobar

q01是赋予子查询的名称。以与AS关键字相同的方式。例如：FROM very_long_table_name AS VLTN。

是的，这个巢很难看。但是，许多SQL引擎都有一个查询优化器，用于计算执行查询的最佳方法。在Server上，我注意到性能上的差别很小，因为查询优化器找到了一种比书面方式更快的执行方式。

但是，对于MySQL，已知嵌套查询会导致性能下降。参见here、here和here。

可以解决这一问题的一件事是改变R中select和filter命令的顺序：

tab %>%
  filter(type = 'foobar') %>%
  select(id, date, type)

可能会生成已翻译的查询：

SELECT `id`, `date`, `type`
FROM `myTable`
WHERE (`type` == 'foobar')

会表现得更好。

Answer 2

我觉得你想把这一切都串在一起。完成tab <- tbl(my_conn, "myTable")之后，您已经下载了整个表。

tbl(my_conn, "myTable") %>% 
  select(id, date, type) %>%
  filter(type = 'foobar')