使用索引改善n1ql，couchbase中的性能 [英] performance improve in n1ql,couchbase using index
问题描述
我在下面的查询中
explain SELECT * FROM (select ROUND(sum(ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost)),2) total_revenue,
ROUND(sum(CASE WHEN DailyCampaignUsage.day between '2016-05-01' and '2016-05-23' THEN ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost) ELSE 0 END),2) period_revenue,
ROUND(sum(CASe WHEN DailyCampaignUsage.day between '2016-04-01' and '2016-04-23' THEN ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost) ELSE 0 END),2) period_prev_revenue
from Inheritx DailyCampaignUsage use index(dailyCampaignUsage_type_day_clicksCost)
JOIN Inheritx Campaign ON KEYS ('Campaign|'||TOSTRING(DailyCampaignUsage.campaignId))
JOIN Inheritx Users on keys('User|'|| TOSTRING(Campaign.`user`))
WHERE DailyCampaignUsage._type='DailyCampaignUsage' and CASE WHEN FALSE THEN Users.`user` in FALSE ELSE TRUE END ) AS __viewdef__ ORDER BY `created` DESC
我的索引低于
CREATE INDEX dailyCampaignUsage_type_day_clicksCost ON Inheritx
(_type,day,`statistics`[*].clicksCost) WHERE _type='DailyCampaignUsage'
我在查询中使用过。
我的解释计划很糟糕。
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"index": "dailyCampaignUsage_type_day_clicksCost",
"index_id": "37387d27d560354b",
"keyspace": "Inheritx",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"successor(\"DailyCampaignUsage\")"
],
"Inclusion": 1,
"Low": [
"\"DailyCampaignUsage\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"as": "DailyCampaignUsage",
"keyspace": "Inheritx",
"namespace": "default"
},
{
"#operator": "Join",
"as": "Campaign",
"keyspace": "Inheritx",
"namespace": "default",
"on_keys": "(\"Campaign|\" || to_string((`DailyCampaignUsage`.`campaignId`)))"
},
{
"#operator": "Join",
"as": "Users",
"keyspace": "Inheritx",
"namespace": "default",
"on_keys": "(\"User|\" || to_string((`Campaign`.`user`)))"
},
{
"#operator": "Filter",
"condition": "(((`DailyCampaignUsage`.`_type`) = \"DailyCampaignUsage\") and case when false then ((`Users`.`user`) in false) else true end)"
},
{
"#operator": "InitialGroup",
"aggregates": [
"sum(array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)))",
"sum(case when ((`DailyCampaignUsage`.`day`) between \"2016-04-01\" and \"2016-04-23\") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)",
"sum(case when ((`DailyCampaignUsage`.`day`) between \"2016-05-01\" and \"2016-05-23\") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)"
],
"group_keys": []
}
]
}
},
{
"#operator": "IntermediateGroup",
"aggregates": [
"sum(array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)))",
"sum(case when ((`DailyCampaignUsage`.`day`) between \"2016-04-01\" and \"2016-04-23\") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)",
"sum(case when ((`DailyCampaignUsage`.`day`) between \"2016-05-01\" and \"2016-05-23\") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)"
],
"group_keys": []
},
{
"#operator": "FinalGroup",
"aggregates": [
"sum(array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)))",
"sum(case when ((`DailyCampaignUsage`.`day`) between \"2016-04-01\" and \"2016-04-23\") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)",
"sum(case when ((`DailyCampaignUsage`.`day`) between \"2016-05-01\" and \"2016-05-23\") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)"
],
"group_keys": []
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"as": "total_revenue",
"expr": "round(sum(array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`))), 2)"
},
{
"as": "period_revenue",
"expr": "round(sum(case when ((`DailyCampaignUsage`.`day`) between \"2016-05-01\" and \"2016-05-23\") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end), 2)"
},
{
"as": "period_prev_revenue",
"expr": "round(sum(case when ((`DailyCampaignUsage`.`day`) between \"2016-04-01\" and \"2016-04-23\") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end), 2)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
{
"#operator": "Alias",
"as": "__viewdef__"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "self",
"star": true
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"sort_terms": [
{
"desc": true,
"expr": "(`__viewdef__`.`created`)"
}
]
},
{
"#operator": "FinalProject"
}
]
},
"text": "SELECT * FROM (select ROUND(sum(ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost)),2) total_revenue,\nROUND(sum(CASE WHEN DailyCampaignUsage.day between '2016-05-01' and '2016-05-23' THEN ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost) ELSE 0 END),2) period_revenue,\nROUND(sum(CASe WHEN DailyCampaignUsage.day between '2016-04-01' and '2016-04-23' THEN ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost) ELSE 0 END),2) period_prev_revenue \nfrom Inheritx DailyCampaignUsage use index(dailyCampaignUsage_type_day_clicksCost)\nJOIN Inheritx Campaign ON KEYS ('Campaign|'||TOSTRING(DailyCampaignUsage.campaignId)) \nJOIN Inheritx Users on keys('User|'|| TOSTRING(Campaign.`user`)) \nWHERE DailyCampaignUsage._type='DailyCampaignUsage' and CASE WHEN FALSE THEN Users.`user` in FALSE ELSE TRUE END ) AS __viewdef__ ORDER BY `created` DESC"
}
使用我无法减少偶数索引的执行，它是 13s
如何在 300到500ms 周围实现它？
我的json如下所示，我有 50k + json
even index using I can not reduce it execution. it is 13s how I can make it around 300 to 500ms ?? my json like below I have 50k+ json
DailyCampaignUsage|006657c0-c696-11e6-b6f2-7f0166ec7527{
"_id": "006657c0-c696-11e6-b6f2-7f0166ec7527",
"_type": "DailyCampaignUsage",
"campaignId": 249,
"day": "2015-11-19T00:00:00Z",
"statistics": [
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{
"clicks": 1741,
"clicksCost": 48.748
}
]
}
推荐答案
1）您可以首先尝试优化内部查询吗？通过在索引定义中的where / projections / join-on-keys中使用第一个键空间的所有字段，使其覆盖索引。请参阅 https://developer.couchbase.com/documentation/server /4.5/indexes/covering-indexes.html 。文档中的最后一个示例适用于您。像这样的东西：
1) can you first try to optimize the inner query. Make it use covering indexes by including all fields of the first keyspace used in where/projections/join-on-keys in the index definition. See https://developer.couchbase.com/documentation/server/4.5/indexes/covering-indexes.html. Last example in the documentation applies to you. Something like:
CREATE INDEX dailyCampaignUsage_type_day_clicksCost ON Inheritx
(_type,day, campaignId, `statistics`[*].clicksCost) WHERE _type='DailyCampaignUsage'
2）您可以尝试极大地优化内存优化索引（MOI）增加性能。这需要企业版。参见 https：// developer .couchbase.com / documentation / server / 4.5 / architecture / global-secondary-indexes.html＃story-h2-2
2) You can try Memory Optimized Indexes (MOI) to tremendously increase the perf. This needs Enterprise edition. See https://developer.couchbase.com/documentation/server/4.5/architecture/global-secondary-indexes.html#story-h2-2
3）外部查询只按创建进行排序，而内部查询未对其进行投影。如果它在第一个键空间中，则将其包括在索引中。
3) The outer query is only doing order by on 'created' which is not projected by the inner query. If it is in the first keyspace, include that in index.
4）还要使用CASE在Users.user上检查WHERE条件。它总是评估为true。不确定是否需要第三次加入。
4) Also check the WHERE condition with CASE on Users.user. It always evaluates to true. Not sure if you need the 3rd Join.
hth，
-Prasad
hth, -Prasad
这篇关于使用索引改善n1ql，couchbase中的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
