本页面提供了索引策略示例。对于针对多个字段使用范围和不等式过滤条件的查询,您可以使用这些策略来打造高效的查询体验。
在优化查询之前,请先了解针对多个属性的范围和不等式过滤条件概念。
使用 Query Explain 优化查询
如需确定使用的查询和索引是否为最佳,您可以使用 Query Explain 创建查询并查看执行摘要。
Java
...
// Build the query
Query<Entity> query =
Query.newEntityQueryBuilder()
.setKind("employees")
.setFilter(
CompositeFilter.and(
PropertyFilter.gt("salary", 100000), PropertyFilter.gt("experience", 0)))
.setOrderBy(OrderBy("experience"), OrderBy("salary"))
.build();
// Set the explain options to get back *only* the plan summary
ExplainResults<Entity> explainResults = datastore.run(query, ExplainOptions.newBuilder().build());
// Get the explain metrics
Optional<ExplainMetrics> explainMetrics = results.getExplainMetrics();
if (!explainMetrics.isPresent()) {
throw new Exception("No explain metrics returned");
}
// Get the plan summary
PlanSummary planSummary = explainMetrics.get().getPlanSummary();
List<Map<String, Object>> indexesUsed = planSummary.getIndexesUsed();
System.out.println("----- Indexes Used -----");
indexesUsed.forEach(map -> map.forEach((s, o) -> System.out.println(s + ": " + o)));
// Get the execution stats
if (!explainMetrics.getExecutionStats().isPresent()) {
throw new Exception("No execution stats returned");
}
ExecutionStats queryStats = explainMetrics.getExecutionStats().get();
Map<String, Object> debugStats = queryStats.getDebugStats();
System.out.println("----- Debug Stats -----");
debugStats.forEach((s, o) -> System.out.println(s + ": " + o));
以下示例展示了如何使用正确的索引排序来减少 Datastore 模式 Firestore 扫描的实体数。
简单查询
在前面的示例中,使用 (salary, experience)
索引运行的简单查询如下所示:
GQL
SELECT *
FROM /employees
WHERE salary > 100000 AND experience > 0
ORDER BY experience, salary;
Java
Query<Entity> query =
Query.newEntityQueryBuilder()
.setKind("employees")
.setFilter(
CompositeFilter.and(
PropertyFilter.gt("salary", 100000), PropertyFilter.gt("experience", 0)))
.setOrderBy(OrderBy("experience"), OrderBy("salary"))
.build();
该查询仅扫描 95,000 个索引条目,以返回 5 个实体。系统读取了大量索引条目,但因不符合查询谓词而被滤除。
// Output query planning info { "indexesUsed": [ { "query_scope": "Collection Group", "properties": "(experience ASC, salary ASC, __name__ ASC)" } ] }, // Output Query Execution Stats { "resultsReturned": "5", "executionDuration": "2.5s", "readOperations": "100", "debugStats": { "index_entries_scanned": "95000", "documents_scanned": "5", "billing_details": { "documents_billable": "5", "index_entries_billable": "95000", "small_ops": "0", "min_query_cost": "0" } } }
根据前面的示例,我们可以推断出 salary
约束条件比 experience
约束条件更具有选择性。
GQL
SELECT *
FROM /employees
WHERE salary > 100000 AND experience > 0
ORDER BY salary, experience;
Java
Query<Entity> query =
Query.newEntityQueryBuilder()
.setKind("employees")
.setFilter(
CompositeFilter.and(
PropertyFilter.gt("salary", 100000), PropertyFilter.gt("experience", 0)))
.setOrderBy(OrderBy("salary"), OrderBy("experience"))
.build();
当您按之前的顺序显式使用 orderBy()
子句添加谓词时,Datastore 模式 Firestore 会使用 (salary, experience)
索引来运行查询。因此,由于选择第一个范围过滤条件优于前一个查询,因此查询的运行速度更快,而且成本效益更高。
// Output query planning info { "indexesUsed": [ { "query_scope": "Collection Group", "properties": "(salary ASC, experience ASC, __name__ ASC)" } ], // Output Query Execution Stats "resultsReturned": "5", "executionDuration": "0.2s", "readOperations": "6", "debugStats": { "index_entries_scanned": "1000", "documents_scanned": "5", "billing_details": { "documents_billable": "5", "index_entries_billable": "1000", "small_ops": "0", "min_query_cost": "0" } } }
后续步骤
- 了解查询说明。