本頁提供索引策略的範例,該策略應用於在多個欄位設有範圍和不等式篩選條件的查詢,以便提供高效的查詢體驗。
最佳化查詢前,請先瞭解相關概念。
使用查詢說明功能最佳化查詢
如要判斷使用的查詢和索引是否正確,您可以使用「查詢說明」功能,取得查詢計畫的摘要和執行統計資料:
Java
Query q = db.collection("employees").whereGreaterThan("salary",
100000).whereGreaterThan("experience", 0);
ExplainResults<QuerySnapshot> explainResults = q.explain(ExplainOptions.builder().analyze(true).build()).get();
ExplainMetrics metrics = explainResults.getMetrics();
PlanSummary planSummary = metrics.getPlanSummary();
ExecutionStats executionStats = metrics.getExecutionStats();
System.out.println(planSummary.getIndexesUsed());
System.out.println(stats.getResultsReturned());
System.out.println(stats.getExecutionDuration());
System.out.println(stats.getReadOperations());
System.out.println(stats.getDebugStats());
Node.js
let q = db.collection("employees")
.where("salary", ">", 100000)
.where("experience", ">",0);
let options = { analyze : 'true' };
let explainResults = await q.explain(options);
let planSummary = explainResults.metrics.planSummary;
let stats = explainResults.metrics.executionStats;
console.log(planSummary);
console.log(stats);
以下範例顯示如何使用正確的索引排序功能,減少 Cloud Firestore 掃描的索引項目數量。
簡易查詢
根據員工集合的舊版範例,以 (experience ASC, salary ASC)
索引執行的簡易查詢如下:
Java
db.collection("employees")
.whereGreaterThan("salary", 100000)
.whereGreaterThan("experience", 0)
.orderBy("experience")
.orderBy("salary");
查詢僅掃描 95,000 個索引項目,只會傳回 5 份文件。不符合查詢述詞,因此會讀取大量索引項目,但會篩除。
// Output query planning info { "indexesUsed": [ { "properties": "(experience ASC, salary ASC, __name__ ASC)", "query_scope": "Collection" } ], // 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" } } }
我們可以從領域的專業知識推測,大多數員工至少都有一定經驗,但很少有薪資超過 100,000。有鑑於此,我們可以得出結論,salary
限制比 experience
限制更嚴格。如要影響 Cloud Firestore 執行查詢時使用的索引,請指定 orderBy
子句,該子句可將 salary
限制排序在 experience
限制之前。
Java
db.collection("employees")
.whereGreaterThan("salary", 100000)
.whereGreaterThan("experience", 0)
.orderBy("salary")
.orderBy("experience");
當您明確使用 orderBy()
子句新增述詞時,Cloud Firestore 會使用 (salary ASC, experience ASC)
索引執行查詢。因此,因為與前一個查詢相比,這項查詢中第一個範圍篩選器的選擇程度較高,查詢執行速度會更快,成本效益也更高。
// Output query planning info { "indexesUsed": [ { "properties": "(salary ASC, experience ASC, __name__ ASC)", "query_scope": "Collection" } ], // 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" } } }