如何为这个查询优化Azure Cosmos索引

Question

我有一个Cosmos DB大约。10GB的数据，用于存储分析数据。

模型如下:

{
"publisherID": "",
"managerID": "",
"mediaID": "",
"type": "",
"ip": "",
"userAgent": "",
"playerRes": "",
"title": "",
"playerName": "",
"videoTimeCode": 0,
"geo": {
"country": "",
"region": "",
"city": "",
"ll": []
},
"date": "",
"uuid": "",
"id": ""
}

我有时有非常重的查询，因为我的RU限制达到节流。在考虑增加RU限制之前，我想确保我的查询是优化的。

我所有的查询都遵循以下模式:

SELECT c.id,c.date,c.uuid,c.type FROM c WHERE c.mediaID = "{ID}" AND (c.type = "Load OR c.type = "Progress" OR c.type = "Play") AND (c.date BETWEEN "2021-06-30T22:00:00.000Z" AND "2021-07-31T21:59:59.999Z")

所以在做了一些研究之后，我得出结论，我能得到的最好的索引是:

{
"indexingMode": "consistent",
"automatic": true,
"includedPaths": [
{
"path": "/type/?"
},
{
"path": "/mediaID/?"
},
{
"path": "/date/?"
}
],
"excludedPaths": [
{
"path": "/*"
}
]
}

我得到了这个查询的以下统计数据:

Request Charge: 324.51000000000005 RUs
Showing Results: 1 - 100
Retrieved document count: 10024
Retrieved document size: 8597509 bytes
Output document count: 200
Output document size: 28324 bytes
Index hit document count: 199.24
Index lookup time: 2.41 ms
Document load time: 62.93 ms
Query engine execution time: 15.709800000000001 ms
System function execution time: 0 ms
User defined function execution time: 0 ms
Document write time: 0.47000000000000003 ms
Round Trips: 1

让我担心的是检索文档计数和输出文档计数之间的差异。我猜这就是为什么我需要324个RU才能得到前100个结果…

我不确定如何设置索引来优化查询的性能(总是相同的模式:WHERE mediaID = {ID} AND type = {type} AND date Between 2日期)

欢迎任何帮助。

Answer 1

感谢您的反馈，帮助很大!

我添加了如下的综合索引:

"compositeIndexes": [
[
{
"path": "/mediaID",
"order": "ascending"
},
{
"path": "/type",
"order": "ascending"
},
{
"path": "/date",
"order": "ascending"
}
]
]

我第一篇文章中最初请求的RU现在是7个RU(之前是320个!)由于@404

如果你不介意的话，我想再推一点……:)对于另一个请求(相同的结构)，我知道有很多数据需要检索，我需要42.02 RUs才能获得前100个结果。这有意义吗?

Request Charg 42.02 RUs
Showing Results 1 - 100
Retrieved document count 200
Retrieved document size 164847 bytes
Output document count 200
Output document size 28544 bytes
Index hit document count 200
Index lookup time 6.98 ms
Document load time 1.3399 ms
Query engine execution time 0.6601 ms
System function execution time 0 ms
User defined function execution time 0 ms
Document write time 0.47000000000000003 ms
Round Trips 1

除了增加RU限制之外，还有什么优化可以做吗?