couchbase - Couchbase 4.5 - 在 where 子句中使用数组时不覆盖索引
问题描述
我有一个Couchbase(4.5)存储桶my-data。存储桶的简要概述如下。
文件结构
{
_class: “com.dom.Activity”,
depId: 984,
dayIds: [17896, 17897, 17898],
startTime: 10,
endTime: 20
}
指数
我在存储桶中也有一个索引,如下所示。
CREATE INDEX idx_dep_day ON my-data(depId, distinct array i for i in dayIds end, , meta().id) WHERE _class = “com.dom.Activity” and startTime is not null and endTime is not null;
我需要获取一些文档 ID,并希望使用上面给出的索引。另外,我希望结果被索引覆盖。
问题是当我在 where 子句中使用dayIds字段时,查询没有被覆盖。
以下是我尝试过的查询及其解释
Query-1(在 where 子句中使用 dayIds 数组)
select meta(b).id from my-data b use index (idx_dep_day)where _class= ‘com.dom.Activity’ and depId = 984 and any i in dayIds satisfies i = 17896 end and startTime is not null and meta().id > ‘Activity-a65e7e616f21e4c6d7b7bccbfd154da1’ and endTime is not null limit 80000
解释-1
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan",
"index": "idx_dep_day",
"index_id": "53398c61c49ae09a",
"keyspace": "my-data",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"984",
"17896"
],
"Inclusion": 2,
"Low": [
"984",
"17896",
"\"Activity-a65e7e616f21e4c6d7b7bccbfd154da1\""
]
}
}
],
"using": "gsi"
}
},
{
"#operator": "Fetch",
"as": "b",
"keyspace": "my-data",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((((((`b`.`_class`) = \"com.dom.Activity\") and ((`b`.`depId`) = 984)) and any `i` in (`b`.`dayIds`) satisfies (`i` = 17896) end) and ((`b`.`startTime`) is not null)) and (\"Activity-a65e7e616f21e4c6d7b7bccbfd154da1\" < (meta(`b`).`id`))) and ((`b`.`endTime`) is not null))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(meta(`b`).`id`)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
{
"#operator": "Limit",
"expr": "80000"
}
]
},
"text": "select meta(b).id from `my-data` b use index (`idx_dep_day`)where `_class`= 'com.dom.Activity' and depId = 984 and any i in dayIds satisfies i = 17896 end and startTime is not null and \nmeta().id > 'Activity-a65e7e616f21e4c6d7b7bccbfd154da1' and endTime is not null limit 80000"
}
]
但是当我从 where 子句中删除数组时
查询 -2(where 子句中没有 dayIds 数组)
select meta(b).id from my-data b use index (idx_dep_day)where _class= ‘com.dom.Activity’ and depId = 984 and startTime is not null and meta().id > ‘Activity-a65e7e616f21e4c6d7b7bccbfd154da1’ and endTime is not null limit 80000
解释-2
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan",
"covers": [
"cover ((`b`.`depId`))",
"cover ((distinct (array `i` for `i` in (`b`.`dayIds`) end)))",
"cover ((meta(`b`).`id`))",
"cover ((meta(`b`).`id`))"
],
"filter_covers": {
"cover (((`b`.`endTime`) is not null))": true,
"cover (((`b`.`startTime`) is not null))": true,
"cover ((`b`.`_class`))": "com.dom.Activity"
},
"index": "idx_dep_day",
"index_id": "53398c61c49ae09a",
"keyspace": "core-data-20190221",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"successor(984)"
],
"Inclusion": 1,
"Low": [
"984"
]
}
}
],
"using": "gsi"
}
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((((cover ((`b`.`_class`)) = \"com.dom.Activity\") and (cover ((`b`.`depId`)) = 984)) and cover (((`b`.`startTime`) is not null))) and (\"Activity-a65e7e616f21e4c6d7b7bccbfd154da1\" < cover ((meta(`b`).`id`)))) and cover (((`b`.`endTime`) is not null)))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "cover (meta(`b`).`id`))"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
{
"#operator": "Limit",
"expr": "80000"
}
]
},
"text": "select meta(`b`).`id` from \n`my-data` b use index (`idx_dep_day`)where \n`_class`= 'com.dom.Activity' and depId = 984 and startTime is not null and meta().id > 'Activity-a65e7e616f21e4c6d7b7bccbfd154da1' and endTime is not null limit 80000"
}
]
为什么在 where 子句中使用 dayIds 数组时无法获取索引覆盖?
解决方案
最后,我可以解决这个问题。事实证明,我们需要将数组作为标量添加到索引中才能使覆盖工作。
CREATE INDEX idx_dep_day ON my-data(depId, distinct array i for i in dayIds end, meta().id, dayIds) WHERE _class = “com.dom.Activity” and startTime is not null and endTime is not null;
现在它工作正常,结果如下
询问
explain select meta(b).id from my-data b use index (idx_dep_day)where _class= ‘com.dom.Activity’ and depId = 984 and any i in dayIds satisfies i = 17896 end and startTime is not null and meta().id > ‘Activity-2’ and endTime is not null limit 80000
输出
[
{
"plan":{
"#operator":"Sequence",
"~children":[
{
"#operator":"Sequence",
"~children":[
{
"#operator":"DistinctScan",
"scan":{
"#operator":"IndexScan",
"covers":[
"cover ((b.depId))",
"cover ((distinct (array i for i in (b.dayIds) end)))",
"cover ((meta(b).id))",
"cover ((b.dayIds))",
"cover ((meta(b).id))"
],
"filter_covers":{
"cover (((b.endTime) is not null))":true,
"cover (((b.startTime) is not null))":true,
"cover ((b._class))":"com.dom.Activity"
},
"index":"idx_dep_day",
"index_id":"cb0adb18bf0f081f",
"keyspace":"test",
"namespace":"default",
"spans":[
{
"Range":{
"High":[
"984",
"17896"
],
"Inclusion":2,
"Low":[
"984",
"17896",
"\"Activity-2\""
]
}
}
],
"using":"gsi"
}
},
{
"#operator":"Parallel",
"~child":{
"#operator":"Sequence",
"~children":[
{
"#operator":"Filter",
"condition":"((((((cover ((b._class)) = \"com.dom.Activity\") and (cover ((b.depId)) = 984)) and any i in cover ((b.dayIds)) satisfies (i = 17896) end) and cover (((b.startTime) is not null))) and (\"Activity-2\" < cover ((meta(b).id)))) and cover (((b.endTime) is not null)))"
},
{
"#operator":"InitialProject",
"result_terms":[
{
"expr":"cover ((meta(b).id))"
}
]
},
{
"#operator":"FinalProject"
}
]
}
}
]
},
{
"#operator":"Limit",
"expr":"80000"
}
]
},
"text":"select meta(b).id from\ntest b use index (idx_dep_day)where _class= ‘com.dom.Activity’ and depId = 984\nand any i in dayIds satisfies i = 17896 end and startTime is not null and\nmeta().id > ‘Activity-2’ and endTime is not null limit 80000"
}
]
推荐阅读
- html - 与内联样式相比,使用 HTML 属性/标签是否存在性能差异?
- python - Twisted 为 reactor 生成新客户端
- pandas - 如何为多个组制作条形图?
- node.js - RabbitMQ 消费者未实时自动检测消息
- elasticsearch - 在 Loki/Kibana 中,查看所有以错误结束的请求
- javascript - 使用具有相同 Js 功能的多个下拉菜单
- php - 在从帖子标题共享预览和通知标题中使用 Shorcodes
- python - 将网页中的表格元素存储在字典中
- c# - 由于索引问题,无法创建 Fluent API 外键
- android-emulator - 如何编辑模型和硬件模拟器