优化 Azure Cosmos DB 的查询性能Tuning query performance with Azure Cosmos DB

适用于: SQL API

Azure Cosmos DB 提供了一个用于查询数据的 SQL API,不需要使用架构或辅助索引。Azure Cosmos DB provides a SQL API for querying data, without requiring schema or secondary indexes. 本文为开发者提供了以下信息:This article provides the following information for developers:

  • 有关 Azure Cosmos DB 的 SQL 查询执行如何工作的概要详细信息High-level details on how Azure Cosmos DB's SQL query execution works
  • 有关查询请求和响应标头以及客户端 SDK 选项的详细信息Details on query request and response headers, and client SDK options
  • 有关查询性能的提示和最佳做法Tips and best practices for query performance
  • 有关如何使用 SQL 执行统计信息调试查询性能的示例Examples of how to utilize SQL execution statistics to debug query performance

关于 SQL 查询执行About SQL query execution

在 Azure Cosmos DB 中,数据存储在容器中,容器可以增长到任何存储大小或请求吞吐量In Azure Cosmos DB, you store data in containers, which can grow to any storage size or request throughput. Azure Cosmos DB 在幕后无缝地在物理分区之间缩放数据来以预配的吞吐量处理数据增长。Azure Cosmos DB seamlessly scales data across physical partitions under the covers to handle data growth or increase in provisioned throughput. 可以使用 REST API 或受支持的 SQL SDK 之一向任何容器发出 SQL 查询。You can issue SQL queries to any container using the REST API or one of the supported SQL SDKs.

分区的简要概述:定义一个分区键(例如“city”),它决定了如何在物理分区之间拆分数据。A brief overview of partitioning: you define a partition key like "city", which determines how data is split across physical partitions. 属于单个分区键(例如 "city" == "Seattle")的数据存储在一个物理分区中,但单个物理分区通常具有多个分区键。Data belonging to a single partition key (for example, "city" == "Seattle") is stored within a physical partition, but typically a single physical partition has multiple partition keys. 当某个分区达到其存储大小时,服务会无缝地将分区拆分为两个新分区,并且会将分区键平均分割到这些分区中。When a partition reaches its storage size, the service seamlessly splits the partition into two new partitions, and divides the partition key evenly across these partitions. 因为分区是暂时的,因此,API 使用“分区键范围”的抽象,它表示分区键哈希的范围。Since partitions are transient, the APIs use an abstraction of a "partition key range", which denotes the ranges of partition key hashes.

当向 Azure Cosmos DB 发出查询时,SDK 执行以下逻辑步骤:When you issue a query to Azure Cosmos DB, the SDK performs these logical steps:

  • 分析 SQL 查询来确定查询执行计划。Parse the SQL query to determine the query execution plan.
  • 如果查询包括一个针对分区键的筛选器,例如 SELECT * FROM c WHERE c.city = "Seattle",则它被传送到单个分区。If the query includes a filter against the partition key, like SELECT * FROM c WHERE c.city = "Seattle", it is routed to a single partition. 如果查询没有针对分区键的筛选器,则会在所有分区中执行该查询,并且结果是合并的客户端。If the query does not have a filter on partition key, then it is executed in all partitions, and results are merged client side.
  • 查询将根据客户端配置在每个分区内串行或并行执行。The query is executed within each partition in series or parallel, based on client configuration. 在每个分区内,查询可能会进行一次或多次往返,具体取决于查询复杂性、所配置的页面大小和预配的集合吞吐量。Within each partition, the query might make one or more round trips depending on the query complexity, configured page size, and provisioned throughput of the collection. 每个执行都返回由查询执行使用的请求单位数以及可选的查询执行统计信息。Each execution returns the number of request units consumed by query execution, and optionally, query execution statistics.
  • SDK 对跨分区的查询结果进行汇总。The SDK performs a summarization of the query results across partitions. 例如,如果查询涉及跨分区的 ORDER BY,则会对来自各个分区的结果进行合并排序以返回多区域排序的结果。For example, if the query involves an ORDER BY across partitions, then results from individual partitions are merge-sorted to return results in multiple-regionally sorted order. 如果查询是类似于 COUNT 的聚合,则会对来自各个分区的计数进行求和以生成总数。If the query is an aggregation like COUNT, the counts from individual partitions are summed to produce the overall count.

SDK 针对查询执行提供了各种选项。The SDKs provide various options for query execution. 例如,在 .NET 中,FeedOptions 类中提供了以下选项。For example, in .NET these options are available in the FeedOptions class. 下表介绍了这些选项以及它们如何影响查询执行时间。The following table describes these options and how they impact query execution time.

选项Option 说明Description
EnableCrossPartitionQuery 对于需要跨多个分区执行的任何查询,都必须将其设置为 true。Must be set to true for any query that requires to be executed across more than one partition. 这是一个显式标志,可用来在开发时有意识地进行性能权衡。This is an explicit flag to enable you to make conscious performance tradeoffs during development time.
EnableScanInQuery 如果已决定不使用索引编制,但仍然希望通过扫描方式运行查询,必须将其设置为 true。Must be set to true if you have opted out of indexing, but want to run the query via a scan anyway. 只有针对所请求的筛选器路径禁用了索引编制时才适用。Only applicable if indexing for the requested filter path is disabled.
MaxItemCount 到服务器的每次往返要返回的最大项数。The maximum number of items to return per round trip to the server. 通过将其设置为 -1,可以让服务器来管理此项数。By setting to -1, you can let the server manage the number of items. 或者,可以减小此值来使每次往返仅检索少量项。Or, you can lower this value to retrieve only a small number of items per round trip.
MaxBufferedItemCount 这是一个客户端选项,在执行跨分区 ORDER BY 时用来限制内存占用。This is a client-side option, and used to limit the memory consumption when performing cross-partition ORDER BY. 较高的值有助于降低跨分区排序的延迟。A higher value helps reduce the latency of cross-partition sorting.
MaxDegreeOfParallelism 获取或设置在 Azure Cosmos 数据库服务中并行执行查询期间客户端运行的并发操作数。Gets or sets the number of concurrent operations run client side during parallel query execution in the Azure Cosmos database service. 属性值为正会将并发操作数限制为所设置的值。A positive property value limits the number of concurrent operations to the set value. 如果它设置为小于 0,则系统会自动决定要运行的并发操作数。If it is set to less than 0, the system automatically decides the number of concurrent operations to run.
PopulateQueryMetrics 详细记录在执行查询的各个阶段花费的时间的统计信息,例如编译时间、索引循环时间和文档加载时间。Enables detailed logging of statistics of time spent in various phases of query execution like compilation time, index loop time, and document load time. 可以与 Azure 支持共享来自查询统计信息的输出以诊断查询性能问题。You can share output from query statistics with Azure Support to diagnose query performance issues.
RequestContinuation 可以通过传入任何查询返回的不透明继续标记来继续执行查询。You can resume query execution by passing in the opaque continuation token returned by any query. 继续标记封装了执行查询所需的所有状态。The continuation token encapsulates all state required for query execution.
ResponseContinuationTokenLimitInKb 可以限制服务器返回的继续标记的最大大小。You can limit the maximum size of the continuation token returned by the server. 如果应用程序主机对响应标头大小有限制,则可能需要设置此项。You might need to set this if your application host has limits on response header size. 设置此项可能会增加查询的总体持续时间和所使用的 RU。Setting this may increase the overall duration and RUs consumed for the query.

例如,让我们以针对在某个集合上请求的分区键的查询为例,该集合以 /city 作为分区键并且预配的吞吐量为 100,000 RU/s。For example, let's take an example query on partition key requested on a collection with /city as the partition key and provisioned with 100,000 RU/s of throughput. 你使用 .NET 中的 CreateDocumentQuery<T> 请求执行此查询,如下所示:You request this query using CreateDocumentQuery<T> in .NET like the following:

IDocumentQuery<dynamic> query = client.CreateDocumentQuery(
    UriFactory.CreateDocumentCollectionUri(DatabaseName, CollectionName), 
    "SELECT * FROM c WHERE c.city = 'Seattle'", 
    new FeedOptions 
    { 
        PopulateQueryMetrics = true, 
        MaxItemCount = -1, 
        MaxDegreeOfParallelism = -1, 
        EnableCrossPartitionQuery = true 
    }).AsDocumentQuery();

FeedResponse<dynamic> result = await query.ExecuteNextAsync();

上面显示的 SDK 片段对应于以下 REST API 请求:The SDK snippet shown above, corresponds to the following REST API request:

POST https://arramacquerymetrics-chinanorth.documents.azure.cn/dbs/db/colls/sample/docs HTTP/1.1
x-ms-continuation: 
x-ms-documentdb-isquery: True
x-ms-max-item-count: -1
x-ms-documentdb-query-enablecrosspartition: True
x-ms-documentdb-query-parallelizecrosspartitionquery: True
x-ms-documentdb-query-iscontinuationexpected: True
x-ms-documentdb-populatequerymetrics: True
x-ms-date: Tue, 27 Jun 2017 21:52:18 GMT
authorization: type%3dmaster%26ver%3d1.0%26sig%3drp1Hi83Y8aVV5V6LzZ6xhtQVXRAMz0WNMnUuvriUv%2b4%3d
x-ms-session-token: 7:8,6:2008,5:8,4:2008,3:8,2:2008,1:8,0:8,9:8,8:4008
Cache-Control: no-cache
x-ms-consistency-level: Session
User-Agent: documentdb-dotnet-sdk/1.14.1 Host/32-bit MicrosoftWindowsNT/6.2.9200.0
x-ms-version: 2017-02-22
Accept: application/json
Content-Type: application/query+json
Host: arramacquerymetrics-chinanorth.documents.azure.cn
Content-Length: 52
Expect: 100-continue

{"query":"SELECT * FROM c WHERE c.city = 'Seattle'"}

每个查询执行页面对应于一个标头为 Accept: application/query+json 的 REST API POST,SQL 查询在正文中。Each query execution page corresponds to a REST API POST with the Accept: application/query+json header, and the SQL query in the body. 每个查询都使用为了继续执行而在客户端与服务器之间回应的 x-ms-continuation 标记进行到服务器的一次或多次往返。Each query makes one or more round trips to the server with the x-ms-continuation token echoed between the client and server to resume execution. FeedOptions 中的配置选项将以请求标头的形式传递给服务器。The configuration options in FeedOptions are passed to the server in the form of request headers. 例如,MaxItemCount 对应于 x-ms-max-item-countFor example, MaxItemCount corresponds to x-ms-max-item-count.

该请求返回以下响应(为便于阅读已将其截断):The request returns the following (truncated for readability) response:

HTTP/1.1 200 Ok
Cache-Control: no-store, no-cache
Pragma: no-cache
Transfer-Encoding: chunked
Content-Type: application/json
Server: Microsoft-HTTPAPI/2.0
Strict-Transport-Security: max-age=31536000
x-ms-last-state-change-utc: Tue, 27 Jun 2017 21:01:57.561 GMT
x-ms-resource-quota: documentSize=10240;documentsSize=10485760;documentsCount=-1;collectionSize=10485760;
x-ms-resource-usage: documentSize=1;documentsSize=884;documentsCount=2000;collectionSize=1408;
x-ms-item-count: 2000
x-ms-schemaversion: 1.3
x-ms-alt-content-path: dbs/db/colls/sample
x-ms-content-path: +9kEANVq0wA=
x-ms-xp-role: 1
x-ms-documentdb-query-metrics: totalExecutionTimeInMs=33.67;queryCompileTimeInMs=0.06;queryLogicalPlanBuildTimeInMs=0.02;queryPhysicalPlanBuildTimeInMs=0.10;queryOptimizationTimeInMs=0.00;VMExecutionTimeInMs=32.56;indexLookupTimeInMs=0.36;documentLoadTimeInMs=9.58;systemFunctionExecuteTimeInMs=0.00;userFunctionExecuteTimeInMs=0.00;retrievedDocumentCount=2000;retrievedDocumentSize=1125600;outputDocumentCount=2000;writeOutputTimeInMs=18.10;indexUtilizationRatio=1.00
x-ms-request-charge: 604.42
x-ms-serviceversion: version=1.14.34.4
x-ms-activity-id: 0df8b5f6-83b9-4493-abda-cce6d0f91486
x-ms-session-token: 2:2008
x-ms-gatewayversion: version=1.14.33.2
Date: Tue, 27 Jun 2017 21:59:49 GMT

从查询返回的主要响应标头包括以下内容:The key response headers returned from the query include the following:

选项Option 说明Description
x-ms-item-count 响应中返回的项数。The number of items returned in the response. 这取决于所提供的 x-ms-max-item-count、在最大响应有效负载大小内可以容纳的项数、预配的吞吐量以及查询执行时间。This is dependent on the supplied x-ms-max-item-count, the number of items that can be fit within the maximum response payload size, the provisioned throughput, and query execution time.
x-ms-continuation: 用于继续执行查询的继续标记(如果有更多结果)。The continuation token to resume execution of the query, if additional results are available.
x-ms-documentdb-query-metrics 执行的查询统计信息。The query statistics for the execution. 这是一个经分隔的字符串,其中包含在执行查询的各个阶段中花费的时间的统计信息。This is a delimited string containing statistics of time spent in the various phases of query execution. 如果 x-ms-documentdb-populatequerymetrics 设置为 True,则会返回。Returned if x-ms-documentdb-populatequerymetrics is set to True.
x-ms-request-charge 查询使用的请求单位数。The number of request units consumed by the query.

有关 REST API 请求标头和选项的详细信息,请参阅使用 REST API 查询资源For details on the REST API request headers and options, see Querying resources using the REST API.

有关查询性能的最佳做法Best practices for query performance

下面是影响 Azure Cosmos DB 查询性能的最常见因素。The following are the most common factors that impact Azure Cosmos DB query performance. 本文深入探讨了其中的每一个主题。We dig deeper into each of these topics in this article.

因素Factor 提示Tip
预配的吞吐量Provisioned throughput 度量每个查询的 RU,并确保你具有查询所需的预配吞吐量。Measure RU per query, and ensure that you have the required provisioned throughput for your queries.
分区和分区键Partitioning and partition keys 支持在查询的筛选器子句中使用分区键值以降低延迟。Favor queries with the partition key value in the filter clause for low latency.
SDK 和查询选项SDK and query options 遵循 SDK 最佳做法(例如直接连接)并优化客户端查询执行选项。Follow SDK best practices like direct connectivity, and tune client-side query execution options.
网络延迟Network latency 在度量时考虑网络开销,并使用多宿主 API 从最近的区域进行读取。Account for network overhead in measurement, and use multi-homing APIs to read from the nearest region.
索引策略Indexing Policy 确保具有查询所需的索引路径/策略。Ensure that you have the required indexing paths/policy for the query.
查询执行指标Query execution metrics 对查询执行指标进行分析来查明潜在的查询和数据形状重写。Analyze the query execution metrics to identify potential rewrites of query and data shapes.

预配的吞吐量Provisioned throughput

在 Cosmos DB 中,创建数据容器,每个容器都具有以每秒请求单位 (RU) 表示的预留吞吐量。In Cosmos DB, you create containers of data, each with reserved throughput expressed in request units (RU) per-second. 读取 1-KB 文档为 1 个 RU,每个操作(包括查询)都根据其复杂性规范化为固定数量的 RU。A read of a 1-KB document is 1 RU, and every operation (including queries) is normalized to a fixed number of RUs based on its complexity. 例如,如果你为容器预配了 1000 RU/s,并且你具有使用 5 个 RU 的类似于 SELECT * FROM c WHERE c.city = 'Seattle' 的查询,则每秒可以执行200 个这样的查询((1000 RU/s) / (5 RU/查询) = 200 查询/s)。For example, if you have 1000 RU/s provisioned for your container, and you have a query like SELECT * FROM c WHERE c.city = 'Seattle' that consumes 5 RUs, then you can perform (1000 RU/s) / (5 RU/query) = 200 query/s such queries per second.

如果你每秒提交的查询多于 200 个,则服务会对高于 200/s 的传入请求进行速率限制。If you submit more than 200 queries/sec, the service starts rate-limiting incoming requests above 200/s. SDK 会通过执行回退/重试自动处理此情况,因此你可能会注意到这些查询有较高的延迟。The SDKs automatically handle this case by performing a backoff/retry, therefore you might notice a higher latency for these queries. 将预配的吞吐量提高到所需的值可以改进查询延迟和吞吐量。Increasing the provisioned throughput to the required value improves your query latency and throughput.

若要了解请求单位的详细信息,请参阅请求单位To learn more about request units, see Request units.

分区和分区键Partitioning and partition keys

使用 Azure Cosmos DB 时,查询通常采用以下顺序按从最快/最高效到较慢/较低效的顺序执行。With Azure Cosmos DB, typically queries perform in the following order from fastest/most efficient to slower/less efficient.

  • 针对单个分区键和项键的 GETGET on a single partition key and item key
  • 具有针对单个分区键的筛选器子句的查询Query with a filter clause on a single partition key
  • 针对任何属性都没有等于或范围筛选器子句的查询Query without an equality or range filter clause on any property
  • 没有筛选器的查询Query without filters

需要查阅所有分区的查询需要较高的延迟,并且会使用较高的 RU。Queries that need to consult all partitions need higher latency, and can consume higher RUs. 因为每个分区都具有针对所有属性的自动索引编制功能,因此,在这种情况下,可以基于索引高效地执行查询。Since each partition has automatic indexing against all properties, the query can be served efficiently from the index in this case. 可以通过使用并行度选项使跨分区的查询更快地执行。You can make queries that span partitions faster by using the parallelism options.

若要了解有关分区和分区键的详细信息,请参阅在 Azure Cosmos DB 中进行分区To learn more about partitioning and partition keys, see Partitioning in Azure Cosmos DB.

SDK 和查询选项SDK and query options

请参阅性能提示性能测试来了解如何从 Azure Cosmos DB 获得最佳客户端性能。See Performance Tips and Performance testing for how to get the best client-side performance from Azure Cosmos DB. 这包括使用最新的 SDK、配置特定于平台的配置(例如默认连接数、垃圾收集频率)以及使用诸如直连/TCP 之类的轻型连接。This includes using the latest SDKs, configuring platform-specific configurations like default number of connections, frequency of garbage collection, and using lightweight connectivity options like Direct/TCP.

最大项计数Max Item Count

对查询而言,MaxItemCount 的值对端到端查询时间具有显著影响。For queries, the value of MaxItemCount can have a significant impact on end-to-end query time. 服务器的每次往返所返回的项数不超过 MaxItemCount 中的数目(默认为 100 个项)。Each round trip to the server will return no more than the number of items in MaxItemCount (Default of 100 items). 将此值设为更大值(-1 为最大值,推荐使用此值),通过限制服务器和客户端之间的往返数(尤其针对具有大型结果集的查询)可提高总体查询持续时间。Setting this to a higher value (-1 is maximum, and recommended) will improve your query duration overall by limiting the number of round trips between server and client, especially for queries with large result sets.

IDocumentQuery<dynamic> query = client.CreateDocumentQuery(
    UriFactory.CreateDocumentCollectionUri(DatabaseName, CollectionName), 
    "SELECT * FROM c WHERE c.city = 'Seattle'", 
    new FeedOptions 
    { 
        MaxItemCount = -1, 
    }).AsDocumentQuery();

最大并行度Max Degree of Parallelism

对于查询,优化 MaxDegreeOfParallelism 来确定适合你的应用程序的最佳配置,尤其是当执行跨分区查询时(没有针对分区键值的筛选器)。For queries, tune the MaxDegreeOfParallelism to identify the best configurations for your application, especially if you perform cross-partition queries (without a filter on the partition-key value). MaxDegreeOfParallelism 控制并行任务的最大数目,即要并行访问的最大分区数。MaxDegreeOfParallelism controls the maximum number of parallel tasks, i.e., the maximum of partitions to be visited in parallel.

IDocumentQuery<dynamic> query = client.CreateDocumentQuery(
    UriFactory.CreateDocumentCollectionUri(DatabaseName, CollectionName), 
    "SELECT * FROM c WHERE c.city = 'Seattle'", 
    new FeedOptions 
    { 
        MaxDegreeOfParallelism = -1, 
        EnableCrossPartitionQuery = true 
    }).AsDocumentQuery();

假设Let's assume that

  • D = 默认的最大并行任务数(客户端计算机中处理器的总数)D = Default Maximum number of parallel tasks (= total number of processor in the client machine)
  • P = 用户定义的最大并行任务数P = User-specified maximum number of parallel tasks
  • N = 为响应查询需要访问的分区数N = Number of partitions that needs to be visited for answering a query

以下为 P 值不同时并行查询行为的影响。Following are implications of how the parallel queries would behave for different values of P.

  • (P == 0) => 串行模式(P == 0) => Serial Mode
  • (P == 1) => 一个任务的最大数(P == 1) => Maximum of one task
  • (P > 1) => Min (P, N) 并行任务数(P > 1) => Min (P, N) parallel tasks
  • (P < 1) => Min (N, D) 并行任务数(P < 1) => Min (N, D) parallel tasks

有关 SDK 发行说明和已实现的类和方法的详细信息,请参阅 SQL SDKFor SDK release notes, and details on implemented classes and methods see SQL SDKs

网络延迟Network latency

请参阅 Azure Cosmos DB 多区域分发,了解如何设置多区域分发以及如何连接到最近的区域。See Azure Cosmos DB multiple-region distribution for how to set up multiple-region distribution, and connect to the closest region. 当需要进行多次往返或需要通过查询检索大型结果集时,网络延迟对查询性能有显著影响。Network latency has a significant impact on query performance when you need to make multiple round-trips or retrieve a large result set from the query.

有关查询执行指标的部分介绍如何检索查询的服务器执行时间 ( totalExecutionTimeInMs),以便可区分查询执行和网络传输所用的时间。The section on query execution metrics explains how to retrieve the server execution time of queries ( totalExecutionTimeInMs), so that you can differentiate between time spent in query execution and time spent in network transit.

索引编制策略Indexing policy

若要了解索引编制路径、种类和模式以及它们对查询执行有何影响,请参阅配置索引编制策略See Configuring indexing policy for indexing paths, kinds, and modes, and how they impact query execution. 默认情况下,索引编制策略为字符串使用哈希索引编制,字符串比较适合进行等式查询,但不适合进行范围查询/order by 查询。By default, the indexing policy uses Hash indexing for strings, which is effective for equality queries, but not for range queries/order by queries. 如果需要对字符串使用范围查询,建议为所有字符串指定范围索引类型。If you need range queries for strings, we recommend specifying the Range index type for all strings.

默认情况下,Azure Cosmos DB 会对所有数据应用自动索引。By default, Azure Cosmos DB will apply automatic indexing to all data. 对于高性能插入方案,考虑排除路径,因为这会降低每项插入操作的 RU 成本。For high performance insert scenarios, consider excluding paths as this will reduce the RU cost for each insert operation.

查询执行指标Query execution metrics

可以通过传入可选的 x-ms-documentdb-populatequerymetrics 标头(在 .NET SDK 中为 FeedOptions.PopulateQueryMetrics)获取有关查询执行的详细指标。You can obtain detailed metrics on query execution by passing in the optional x-ms-documentdb-populatequerymetrics header (FeedOptions.PopulateQueryMetrics in the .NET SDK). x-ms-documentdb-query-metrics 中返回的值具有适用于对查询执行进行高级故障排除的以下键-值对。The value returned in x-ms-documentdb-query-metrics has the following key-value pairs meant for advanced troubleshooting of query execution.

IDocumentQuery<dynamic> query = client.CreateDocumentQuery(
    UriFactory.CreateDocumentCollectionUri(DatabaseName, CollectionName), 
    "SELECT * FROM c WHERE c.city = 'Seattle'", 
    new FeedOptions 
    { 
        PopulateQueryMetrics = true, 
    }).AsDocumentQuery();

FeedResponse<dynamic> result = await query.ExecuteNextAsync();

// Returns metrics by partition key range Id
IReadOnlyDictionary<string, QueryMetrics> metrics = result.QueryMetrics;

指标Metric 单位Unit 说明Description
totalExecutionTimeInMs 毫秒milliseconds 查询执行时间Query execution time
queryCompileTimeInMs 毫秒milliseconds 查询编译时间Query compile time
queryLogicalPlanBuildTimeInMs 毫秒milliseconds 生成逻辑查询计划的时间Time to build logical query plan
queryPhysicalPlanBuildTimeInMs 毫秒milliseconds 生成物理查询计划的时间Time to build physical query plan
queryOptimizationTimeInMs 毫秒milliseconds 优化查询时花费的时间Time spent in optimizing query
VMExecutionTimeInMs 毫秒milliseconds 查询运行时花费的时间Time spent in query runtime
indexLookupTimeInMs 毫秒milliseconds 在物理索引层中花费的时间Time spent in physical index layer
documentLoadTimeInMs 毫秒milliseconds 加载文档时花费的时间Time spent in loading documents
systemFunctionExecuteTimeInMs 毫秒milliseconds 执行系统(内置)函数花费的总时间(毫秒)Total time spent executing system (built-in) functions in milliseconds
userFunctionExecuteTimeInMs 毫秒milliseconds 执行用户定义的函数花费的总时间(毫秒)Total time spent executing user-defined functions in milliseconds
retrievedDocumentCount countcount 检索的文档总数Total number of retrieved documents
retrievedDocumentSize 字节bytes 检索的文档的总大小(字节)Total size of retrieved documents in bytes
outputDocumentCount countcount 输出文档数Number of output documents
writeOutputTimeInMs 毫秒milliseconds 查询执行时间(毫秒)Query execution time in milliseconds
indexUtilizationRatio 比率 (<=1)ratio (<=1) 由筛选器匹配出的文档数与加载的文档数的比率Ratio of number of documents matched by the filter to the number of documents loaded

客户端 SDK 可以在内部执行多个查询操作来为每个分区中的查询提供服务。The client SDKs may internally make multiple query operations to serve the query within each partition. 如果总结果数超出了 x-ms-max-item-count、查询超出了分区的预配吞吐量,或者查询有效负载达到了每页最大大小或查询达到了系统分配的超时限制,则客户端会对每个分区进行多次调用。The client makes more than one call per-partition if the total results exceed x-ms-max-item-count, if the query exceeds the provisioned throughput for the partition, or if the query payload reaches the maximum size per page, or if the query reaches the system allocated timeout limit. 每一部分查询执行都会为该页返回一个 x-ms-documentdb-query-metricsEach partial query execution returns a x-ms-documentdb-query-metrics for that page.

下面提供了一些示例查询,并说明了如何解释从查询执行返回的某些指标:Here are some sample queries, and how to interpret some of the metrics returned from query execution:

查询Query 示例指标Sample Metric 说明Description
SELECT TOP 100 * FROM c "RetrievedDocumentCount": 101 为匹配 TOP 子句而检索的文档数为 100+1。The number of documents retrieved is 100+1 to match the TOP clause. 查询时间主要花费在 WriteOutputTimeDocumentLoadTime 中,因为它是一个扫描。Query time is mostly spent in WriteOutputTime and DocumentLoadTime since it is a scan.
SELECT TOP 500 * FROM c "RetrievedDocumentCount": 501 RetrievedDocumentCount 现在较高(为匹配 TOP 子句为 500+1)。RetrievedDocumentCount is now higher (500+1 to match the TOP clause).
SELECT * FROM c WHERE c.N = 55 "IndexLookupTime": "00:00:00.0009500" IndexLookupTime 中花费的用于查找键的时间大约为 0.9 毫秒,因为它是针对 /N/? 的索引查找。About 0.9 ms is spent in IndexLookupTime for a key lookup, because it's an index lookup on /N/?.
SELECT * FROM c WHERE c.N > 55 "IndexLookupTime": "00:00:00.0017700" IndexLookupTime 中花费的时间(1.7 毫秒)稍高于范围扫描,因为它是针对 /N/? 的索引查找。Slightly more time (1.7 ms) spent in IndexLookupTime over a range scan, because it's an index lookup on /N/?.
SELECT TOP 500 c.N FROM c "IndexLookupTime": "00:00:00.0017700" DocumentLoadTime 上花费的时间与前面的查询相同,但 WriteOutputTime 较低,因为我们仅对一个属性进行了投影。Same time spent on DocumentLoadTime as previous queries, but lower WriteOutputTime because we're projecting only one property.
SELECT TOP 500 udf.toPercent(c.N) FROM c "UserDefinedFunctionExecutionTime": "00:00:00.2136500" UserDefinedFunctionExecutionTime 中对 c.N 的每个值执行 UDF 时花费的时间大约为 213 毫秒。About 213 ms is spent in UserDefinedFunctionExecutionTime executing the UDF on each value of c.N.
SELECT TOP 500 c.Name FROM c WHERE STARTSWITH(c.Name, 'Den') "IndexLookupTime": "00:00:00.0006400", "SystemFunctionExecutionTime": "00:00:00.0074100" IndexLookupTime 中在 /Name/? 上花费的时间大约为 0.6 毫秒。About 0.6 ms is spent in IndexLookupTime on /Name/?. 大多数查询执行时间花费在了 SystemFunctionExecutionTime 中(~7 毫秒)。Most of the query execution time (~7 ms) in SystemFunctionExecutionTime.
SELECT TOP 500 c.Name FROM c WHERE STARTSWITH(LOWER(c.Name), 'den') "IndexLookupTime": "00:00:00", "RetrievedDocumentCount": 2491, "OutputDocumentCount": 500 查询是作为扫描执行的,因为它使用了 LOWER,并且返回了所检索的 2491 个文档中的 500 个。Query is performed as a scan because it uses LOWER, and 500 out of 2491 retrieved documents are returned.

后续步骤Next steps