查询中未使用 Couchbase 数组索引

debugcn 发表于 Dev

斯雷拉格

我有以下文档结构：


{    
  "customerId": "",
  "schemeId": "scheme-a",  
  "type": "account",
  "events": [
    {
      "dateTime": "2019-03-14T02:23:58.573Z",
      "id": "72998bbf-94a6-4031-823b-6c304707ad49",
      "type": "DebitDisabled",
      "authorisedId": ""
    },
    {
      "dateTime": "2018-05-04T12:40:15.439Z",
      "transactionReference": "005171-15-1054-7571-60990-20180503165536",      
      "id": "005171-15-1054-7571-60990-20180503165536-1",
      "type": "Credit",
      "authorisedId": ",
      "value": 34,
      "funder": "funder-a"
    },
    {
      "dateTime": "2019-03-06T04:14:54.564Z",
      "transactionReference": "000000922331",
      "eventDescription": {
        "language": "en-gb",
        "text": "
      },
      "id": "000000922331",
      "type": "Credit",
      "authorisedId": "",
      "value": 16,
      "funder": "funder-b"
    },
    {
      "dateTime": "2019-03-10T04:24:17.903Z",
      "transactionReference": "000001510154",
      "eventDescription": {
        "language": "en-gb",
        "text": ""
      },
      "id": "000001510154",
      "type": "Credit",
      "authorisedId": "",
      "value": 10,
      "funder": "funder-c"
    }
  ]
}

以及以下索引：

CREATE INDEX `scheme-a_customers_index` 
ON `default`(`type`,`schemeId`,`customerId`) 
WHERE ((`schemeId` = "scheme-a") and (`type` = "account")) 
WITH { "num_replica":1 }

CREATE INDEX `scheme-a_credits_index` 
ON `default`(
`type`,
`schemeId`,
`customerId`,
(distinct (array (`e`.`funder`) for `e` in `events` when ((`e`.`type`) = "Credit") end))
) 
WHERE ((`type` = "scheme") and (`schemeId` = "scheme-a")) 
WITH { "num_replica":1 }

我正在尝试为每个 where type="credit" 和诸如“funder%”之类的资助者查询所有 customerIds 和事件

以下是我的查询：

SELECT 
    customerId, 
    (ARRAY v.`value` FOR v IN p.events WHEN v.type = "Credit" AND v.funder like "funder%" END) AS credits 
FROM default AS p 
WHERE p.type = "account" AND p.schemeId = "scheme-a" 
AND (ANY e IN p.events SATISFIES e.funder = "funder-a" END)

我期待查询使用 index scheme-a_credits_index，而不是使用scheme-a_customers_index. 不明白为什么！查询不应该使用scheme-a_credits_index吗？

对决

您的查询没有关于 customerId 的谓词。因此查询只能将两个谓词推送给索引器，并且两个索引都是合格的。由于非数组索引导致索引中的条目数，scheme-a_customers_index 效率更高。

您应该尝试以下操作。

CREATE INDEX `ix1` ON `default`
(DISTINCT ARRAY e.funder FOR e IN events WHEN e.type = "Credit" END, `customerId`)
WHERE ((`schemeId` = "scheme-a") and (`type` = "account")) ;

SELECT
    customerId,
    (ARRAY v.`value` FOR v IN p.events WHEN v.type = "Credit" AND v.funder like "funder%" END) AS credits
FROM default AS p
WHERE p.type = "account" AND p.schemeId = "scheme-a"
AND (ANY e IN p.events SATISFIES e.funder LIKE "funder%" AND e.type = "Credit" END);

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。