我想知道为什么下面的方面计算需要这么慢：
FOR q IN LRQ
COLLECT profile = q.LongRunningQuery.Profile INTO profiles
RETURN { "Profile" : profile, "Count" : LENGTH(profiles)}
它需要大约30秒，尽管数据库中只有5.000个文档，结果中只有30个不同的方面 .
LongRunningQuery.Profile字段使用哈希索引和skiplist索引编制索引 . （我也尝试过不同的组合） .
有没有人暗示我可能会出错？查询是否可能无法从索引中受益？ （5.000记录的大小约为1 GB，所以我假设不会使用哈希索引，可能是完整的扫描？）
有趣的是，以下替代方案仅持续2秒：
FOR q IN SKIPLIST(LRQ, { "LongRunningQuery.Profile": [ [ '>', '' ] ] })[*].LongRunningQuery.Profile
COLLECT profile = q INTO profiles
RETURN { "Profile" : profile, "Count" : LENGTH(profiles) }
但它仍然需要2秒 - 对于如此少量的记录 . 这里看起来像是使用了skiplist索引，但它可能不是完美的索引变体 .
Update 2014-11-27:
arangosh [_system]> stmt._query
FOR q IN LRQ COLLECT profile = q.LongRunningQuery.Profile INTO profiles RETURN {
"Profile" : profile, "Count" : LENGTH(profiles)}
arangosh [_system]> db.LRQ.ensureHashIndex("LongRunningQuery.Profile");
{
"id" : "LRQ/296017913256",
"type" : "hash",
"unique" : false,
"fields" : [
"LongRunningQuery.Profile"
],
"isNewlyCreated" : false,
"error" : false,
"code" : 200
}
查询大约需要32秒，并返回31个简短结果 .
执行计划：
{
"plan": {
"nodes": [
{
"type": "SingletonNode",
"dependencies": [],
"id": 1,
"estimatedCost": 1,
"estimatedNrItems": 1
},
{
"type": "EnumerateCollectionNode",
"dependencies": [
1
],
"id": 2,
"estimatedCost": 5311,
"estimatedNrItems": 5310,
"database": "_system",
"collection": "LRQ",
"outVariable": {
"id": 0,
"name": "q"
}
},
{
"type": "CalculationNode",
"dependencies": [
2
],
"id": 3,
"estimatedCost": 10621,
"estimatedNrItems": 5310,
"expression": {
"type": "attribute access",
"name": "Profile",
"subNodes": [
{
"type": "attribute access",
"name": "LongRunningQuery",
"subNodes": [
{
"type": "reference",
"name": "q",
"id": 0
}
]
}
]
},
"outVariable": {
"id": 3,
"name": "3"
},
"canThrow": false
},
{
"type": "SortNode",
"dependencies": [
3
],
"id": 4,
"estimatedCost": 56166.713176593075,
"estimatedNrItems": 5310,
"elements": [
{
"inVariable": {
"id": 3,
"name": "3"
},
"ascending": true
}
],
"stable": true
},
{
"type": "AggregateNode",
"dependencies": [
4
],
"id": 5,
"estimatedCost": 61476.713176593075,
"estimatedNrItems": 5310,
"aggregates": [
{
"outVariable": {
"id": 1,
"name": "profile"
},
"inVariable": {
"id": 3,
"name": "3"
}
}
],
"outVariable": {
"id": 2,
"name": "profiles"
}
},
{
"type": "CalculationNode",
"dependencies": [
5
],
"id": 6,
"estimatedCost": 66786.71317659307,
"estimatedNrItems": 5310,
"expression": {
"type": "array",
"subNodes": [
{
"type": "array element",
"name": "Profile",
"subNodes": [
{
"type": "reference",
"name": "profile",
"id": 1
}
]
},
{
"type": "array element",
"name": "Count",
"subNodes": [
{
"type": "function call",
"name": "LENGTH",
"subNodes": [
{
"type": "list",
"subNodes": [
{
"type": "reference",
"name": "profiles",
"id": 2
}
]
}
]
}
]
}
]
},
"outVariable": {
"id": 4,
"name": "4"
},
"canThrow": false
},
{
"type": "ReturnNode",
"dependencies": [
6
],
"id": 7,
"estimatedCost": 72096.71317659307,
"estimatedNrItems": 5310,
"inVariable": {
"id": 4,
"name": "4"
}
}
],
"rules": [],
"collections": [
{
"name": "LRQ",
"type": "read"
}
],
"variables": [
{
"id": 0,
"name": "q"
},
{
"id": 1,
"name": "profile"
},
{
"id": 4,
"name": "4"
},
{
"id": 2,
"name": "profiles"
},
{
"id": 3,
"name": "3"
}
],
"estimatedCost": 72096.71317659307,
"estimatedNrItems": 5310
},
"warnings": []
}
更新2014-12-05：
以下是其他措施：理解，谢谢 . 这是输出：
执行AQL_EXECUTE（'FOR q IN LRQ FILTER q.LongRunningQuery.Profile ==“Admin”LIMIT 1 RETURN q.LongRunningQuery.Profile'，{}，{profile：true}） . profile - > {“initializing”：0 ，“解析”：0，“优化ast”：15.364980936050415，“instanciating plan”：0，“优化计划”：0，“执行”：0}
执行AQL_EXECUTE（'FOR q IN LRQ COLLECT profile = q.LongRunningQuery.Profile INTO profiles RETURN {“Profile”：profile，“Count”：LENGTH（profiles）}'，{}，{profile：true}） . profile - - > {“初始化”：0，“解析”：0，“优化ast”：0，“instanciating plan”：0，“优化计划”：0，“执行”：77.88313102722168}
更新19.12.2014：
从2.3.2开始查询的执行计划arangosh [_system]> stmt2 = db._createStatement（'FOR q IN LRQ COLLECT profile = q.LongRunningQuery.Profile INTO profiles RETURN {“Profile”：profile，“Count”：LENGTH（ Profiles ）}'）
看起来像这样：
arangosh [_system]> stmt2.explain()
{
"plan" : {
"nodes" : [
{
"type" : "SingletonNode",
"dependencies" : [ ],
"id" : 1,
"estimatedCost" : 1,
"estimatedNrItems" : 1
},
{
"type" : "IndexRangeNode",
"dependencies" : [
1
],
"id" : 8,
"estimatedCost" : 5311,
"estimatedNrItems" : 5310,
"database" : "_system",
"collection" : "LRQ",
"outVariable" : {
"id" : 0,
"name" : "q"
},
"ranges" : [
[ ]
],
"index" : {
"type" : "skiplist",
"id" : "530975525379",
"unique" : false,
"fields" : [
"LongRunningQuery.Profile"
]
},
"reverse" : false
},
{
"type" : "CalculationNode",
"dependencies" : [
8
],
"id" : 3,
"estimatedCost" : 10621,
"estimatedNrItems" : 5310,
"expression" : {
"type" : "attribute access",
"name" : "Profile",
"subNodes" : [
{
"type" : "attribute access",
"name" : "LongRunningQuery",
"subNodes" : [
{
"type" : "reference",
"name" : "q",
"id" : 0
}
]
}
]
},
"outVariable" : {
"id" : 3,
"name" : "3"
},
"canThrow" : false
},
{
"type" : "AggregateNode",
"dependencies" : [
3
],
"id" : 5,
"estimatedCost" : 15931,
"estimatedNrItems" : 5310,
"aggregates" : [
{
"outVariable" : {
"id" : 1,
"name" : "profile"
},
"inVariable" : {
"id" : 3,
"name" : "3"
}
}
],
"outVariable" : {
"id" : 2,
"name" : "profiles"
}
},
{
"type" : "CalculationNode",
"dependencies" : [
5
],
"id" : 6,
"estimatedCost" : 21241,
"estimatedNrItems" : 5310,
"expression" : {
"type" : "array",
"subNodes" : [
{
"type" : "array element",
"name" : "Profile",
"subNodes" : [
{
"type" : "reference",
"name" : "profile",
"id" : 1
}
]
},
{
"type" : "array element",
"name" : "Count",
"subNodes" : [
{
"type" : "function call",
"name" : "LENGTH",
"subNodes" : [
{
"type" : "list",
"subNodes" : [
{
"type" : "reference",
"name" : "profiles",
"id" : 2
}
]
}
]
}
]
}
]
},
"outVariable" : {
"id" : 4,
"name" : "4"
},
"canThrow" : false
},
{
"type" : "ReturnNode",
"dependencies" : [
6
],
"id" : 7,
"estimatedCost" : 26551,
"estimatedNrItems" : 5310,
"inVariable" : {
"id" : 4,
"name" : "4"
}
}
],
"rules" : [
"use-index-for-sort"
],
"collections" : [
{
"name" : "LRQ",
"type" : "read"
}
],
"variables" : [
{
"id" : 0,
"name" : "q"
},
{
"id" : 1,
"name" : "profile"
},
{
"id" : 4,
"name" : "4"
},
{
"id" : 2,
"name" : "profiles"
},
{
"id" : 3,
"name" : "3"
}
],
"estimatedCost" : 26551,
"estimatedNrItems" : 5310
},
"warnings" : [ ],
"stats" : {
"rulesExecuted" : 25,
"rulesSkipped" : 0,
"plansCreated" : 1
}
}
2 回答
嗯，看解释有一个sortnode，而你的查询没有提供排序？ collect可能会阻止优化器使用你的索引（你会有一个IndexRangeNode而不是EnumerateCollectionNode）
如果传递查询的options参数（db._query（）的第4个参数）{profile：true}，它将输出阶段使用的时间;你能用那个重新运行你的查询，并告诉我们答复吗？
COLLECT语句需要排序输入 . 因此，即使原始查询字符串不包含显式SORT语句，SORT语句也会自动添加到执行计划中 .这就是计划中出现SortNode的原因 . 如果sort属性上有跳转列表索引（在本例中为
LongRunningQuery.Profile），SortNode将被优化掉 . 因此，在属性上添加跳转列表索引将加快速度，因为（昂贵的）排序步骤可以节省 .如果您已设置此类索引并运行查询，则它应该比仅存在哈希索引时更快 . 实际上，原始查询应该忽略哈希索引 .
如果您已设置跳转列表索引并解释查询，则还应该看到不再存在SortNode .
从ArangoDB 2.4（目前处于开发阶段）开始，只有计算方面的语法添加更有效：
这样可以进一步加快查询速度 .