azure - 错误: The order by query does not have a corresponding composite index that it can be served from

Question

执行以下查询时，我收到错误

按 c.Agent.LastStateChangeUnixTime desc,c.Priority asc 从 c order 中选择 *

The order by query does not have a corresponding composite index that it can be served from

我已经添加了文档 here 中所述的复合索引和 here

我的综合索引有错吗？还是我还漏掉了其他东西？

我的索引设置是:

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        {
            "path": "/*"
        }
    ],
    "excludedPaths": [
        {
            "path": "/\"_etag\"/?"
        }
    ],
    "compositeIndexes": [
        [
            {
                "path": "/Priority",
                "order": "descending"
            },
            {
                "path": "/Agent/LastStateChangeUnixTime",
                "order": "ascending"
            }
        ]
    ]
}

示例对象:

{
    "Agent": {
        "TenantId": 999999,
        "PrimaryState": "Null",
        "PendingState": "Null",
        "LastStateChange": "2020-01-18T05:48:11.5397269+00:00",
        "LastStateChangeUnixTime": 1579326491,
        "Notes": null,
        "AgentId": 123,
        "id": "agent-123"
    },
    "AgentId": 123,
    "SkillName": "English",
    "id": "SkillName123",
    "Priority": 10,
    "_rid": "SVIMANKqJboCAAAAAAAAAA==",
    "_self": "dbs/SVIMAA==/colls/SVIMANKqJbo=/docs/SVIMANKqJboCAAAAAAAAAA==/",
    "_etag": "\"2200819a-0000-0700-0000-5e229c1d0000\"",
    "_attachments": "attachments/",
    "_ts": 1579326493
}

Answer 1

是的，您的复合索引对于此查询不正确。它们出现的顺序很重要，因此 WHERE 子句需要匹配顺序。

更改以下内容或将以下内容添加到您的综合索引中。

"compositeIndexes": [
    [
        {
            "path": "/Agent/LastStateChangeUnixTime",
            "order": "descending"
        },
        {
            "path": "/Priority",
            "order": "ascending"
        }
    ]
]