如何在Cosmos DB中使用一个非常长的复合索引?

huangapple go评论59阅读模式
英文:

How do you use a very long composite index in Cosmos DB?

问题

I'm here to provide the translated content for you. Here is the translated text:

"我正试图降低我的GET操作的RU,因为我有大量的记录,但99%的时间只想返回最近的记录。

因此,我添加了一个复合索引,其中包括我查询的所有字段。

这些字段也都作为普通的范围索引包含在内。然而,即使给它足够的时间来完成更新,我的查询仍然以完全相同的RU成本和速度运行。

以下是我的查询样例,我只是不确定如何指定我要使用这个复合索引。

这个是否不是使用复合索引的正确时机?试图使这个尽可能快速。"

英文:

I am trying to get the RU of my GET operations down, as I have loads of records but 99 percent of the time only want to return the most recent record.

So I added a Composite Index, this includes all of the fields that I'm querying on.

 [
            {
                "path": "/resourceIdentity/subscription",
                "order": "ascending"
            },
            {
                "path": "/resourceIdentity/resourceGroup",
                "order": "ascending"
            },
                        {
                "path": "/resourceIdentity/extensionProviderNamespace",
                "order": "ascending"
            },
                        {
                "path": "/resourceIdentity/extensionResourceType",
                "order": "ascending"
            },
                        {
                "path": "/resourceIdentity/extensionResourceName",
                "order": "ascending"
            },
             {
                "path": "/resourceIdentity/resourceType",
                "order": "ascending"
            },
            {
                "path": "/resource/systemData/createdAt",
                "order": "descending"
            }
        ]

These fields are all included as normal range indexes too. However even after giving it time to finish updating, my queries still run with literally the exact same RU cost and speed.

Here's what my query looks like, I'm just not sure how to specify that I want to use this composite index.

SELECT top 1  * FROM root WHERE ((((((root["resourceIdentity"]["subscription"] = "de453a71-7d81-493d-bce6-224fec7223a9") 
AND (LOWER(root["resourceIdentity"]["resourceGroup"]) = "newyears_group")) 
AND (LOWER(root["resourceIdentity"]["extensionProviderNamespace"]) = "x.compute")) 
AND (LOWER(root["resourceIdentity"]["extensionResourceType"]) = "virtualmachines")) 
AND (LOWER(root["resourceIdentity"]["extensionResourceName"]) = "newyearsro1")) 
AND (LOWER(root["resourceIdentity"]["resourceType"]) = "xResource/reports"))
order by  root["resource"]["systemData"]["createdAt"] desc

Is this just not the right time to use a composite index? Trying to make this as fast as possible

答案1

得分: 1

根据文档中的说明,

> 如果查询在一个或多个属性上进行过滤,并且在ORDER BY子句中具有不同的属性,将这些属性添加到ORDER BY子句中可能会有所帮助。

因此,为了利用复合索引,您需要按照在复合索引中定义的顺序将它们全部包含在您的ORDER BY子句中。因此,您可以尝试保持复合索引不变,但更改您的查询为:

SELECT top 1  * FROM root WHERE ((((((root["resourceIdentity"]["subscription"] = "de453a71-7d81-493d-bce6-224fec7223a9") 
AND (LOWER(root["resourceIdentity"]["resourceGroup"]) = "newyears_group")) 
AND (LOWER(root["resourceIdentity"]["extensionProviderNamespace"]) = "x.compute")) 
AND (LOWER(root["resourceIdentity"]["extensionResourceType"]) = "virtualmachines")) 
AND (LOWER(root["resourceIdentity"]["extensionResourceName"]) = "newyearsro1")) 
AND (LOWER(root["resourceIdentity"]["resourceType"]) = "xResource/reports"))
ORDER BY root["resourceIdentity"]["subscription"] ASC, root["resourceIdentity"]["resourceGroup"] ASC, root["resourceIdentity"]["extensionProviderNamespace"] ASC , root["resourceIdentity"]["extensionResourceType"] ASC,  root["resourceIdentity"]["extensionResourceName"] ASC, root["resourceIdentity"]["resourceType"] ASC, root["resource"]["systemData"]["createdAt"] DESC  

这应该不会影响您的查询结果,但应该会更有效地使用您的复合索引。

英文:

From the documentation it states,

> If a query filters on one or more properties and has different
> properties in the ORDER BY clause, it may be helpful to add the
> properties in the filter to the ORDER BY clause.

So in order to utilize the composite indexes, you need to include all of them in your ORDER BY clause in the same order as you defined in your composite index.
So can you try keeping the composite indexes as is but change your query to :

SELECT top 1  * FROM root WHERE ((((((root["resourceIdentity"]["subscription"] = "de453a71-7d81-493d-bce6-224fec7223a9") 
AND (LOWER(root["resourceIdentity"]["resourceGroup"]) = "newyears_group")) 
AND (LOWER(root["resourceIdentity"]["extensionProviderNamespace"]) = "x.compute")) 
AND (LOWER(root["resourceIdentity"]["extensionResourceType"]) = "virtualmachines")) 
AND (LOWER(root["resourceIdentity"]["extensionResourceName"]) = "newyearsro1")) 
AND (LOWER(root["resourceIdentity"]["resourceType"]) = "xResource/reports"))
order by root["resourceIdentity"]["subscription"] asc, root["resourceIdentity"]["resourceGroup"] asc, root["resourceIdentity"]["extensionProviderNamespace"] asc , root["resourceIdentity"]["extensionResourceType"] asc,  root["resourceIdentity"]["extensionResourceName"] asc, root["resourceIdentity"]["resourceType"] asc, root["resource"]["systemData"]["createdAt"] desc  

This should have no impact in your query results but it should start using your composite indexes more effectively.

huangapple
  • 本文由 发表于 2023年5月7日 10:54:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/76192008.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定