多排除条件下Fast data.table子集化的性能优化问询

我在编写一段需多次迭代的代码，通过profvis定位到一处性能瓶颈：outcomes_dt[outcome_1 != exclude & outcome_2 != exclude & outcome_3 != exclude]，该操作速度比其他代码慢约100倍。此操作针对已按outcome_1、outcome_2、outcome_3建键的大型data.table，目的是从所有潜在观测中排除指定观测。

可复现代码 #

exp_keys <- c(letters,LETTERS,month.name)
outcomes_dt <- data.table(outcome_1 = rep(exp_keys, each = 64*64),outcome_2 = rep(rep(exp_keys, each = 64), 64),outcome_3 = rep(exp_keys,64*64), value = 1:(64*64))
setkeyv(outcomes_dt, c("outcome_1","outcome_2","outcome_3"))
exclude <- "a"
outcomes_dt[outcome_1 != exclude & outcome_2 != exclude & outcome_3 != exclude]

性能测试结果 #

我已用microbenchmark对多种方案进行性能测试：

测试代码 #

microbenchmark(
  "keyed" = outcomes_dt[data.table(exclude,exclude,exclude)], # 查找单行
  "keyed_exc" = outcomes_dt[!data.table(exclude,exclude,exclude)], # 排除单行
  "keyed_single" = outcomes_dt[data.table(exclude)], # 查找第一列所有匹配项
  "keyed_exc_single" = outcomes_dt[!data.table(exclude)], # 排除第一列所有匹配项
  "filtered_1" = outcomes_dt[outcome_1 != exclude & outcome_2 != exclude & outcome_3 != exclude], # 过滤方案1（性能瓶颈）
  "filtered_2" = outcomes_dt[!(outcome_1 == exclude | outcome_2 == exclude | outcome_3 == exclude)], # 替代过滤方案
  "chained" = outcomes_dt[outcome_1 != exclude][outcome_2 != exclude][outcome_3 != exclude], # 链式过滤
  "exclude_keys" = outcomes_dt[data.table(exp_keys[2:64])] # 预过滤键值
)

测试输出 #

Unit: milliseconds
             expr       min        lq       mean     median         uq      max neval
            keyed  2.201301  3.291002   4.347171   3.858901   4.978051  14.9554   100
        keyed_exc 22.536301 31.464652  43.713268  35.506901  51.614801 184.8499   100
     keyed_single  2.545301  3.599850   4.607506   4.216601   5.199201  10.9757   100
 keyed_exc_single 23.042200 30.461651  37.625179  34.187701  41.726401  80.6452   100
       filtered_1 30.460302 42.361101  58.039301  48.205951  68.223401 238.8149   100 # 能否优化？
       filtered_2 38.597800 45.118002  57.723060  52.782100  65.944351 181.1115   100 
          chained 69.779000 93.019601 114.897401 105.665200 123.846851 263.9510   100
     exclude_keys 27.473900 34.473251  43.897457  38.198352  46.258451 227.0823   100

我清楚这些方案并非完全等价，但希望找到类似keyed方法的更优提速方案，尤其是能利用已建键的方法。

内容的提问来源于stack exchange，提问作者Chris