如何为Couchbase中的嵌入式对象创建索引？含百万文档查询场景

Hey there! Let's break down how to create the right index for your query that filters on both top-level and embedded fields in Couchbase.

First, let's recap your query and document structure:

You're running:
SELECT * FROM bucket WHERE type = 'toy' AND attributes.material = 'plastic' AND attributes.color = 'red' AND attributes.weight = '200gr' AND attributes.height = '5cm' AND attributes.width = '15cm'
And your docs have a top-level type field plus an embedded attributes object containing the other filter criteria.

The Best Index for This Scenario #

You'll want to create a composite index that includes all the filter fields from your query. This lets Couchbase quickly narrow down the exact documents matching your conditions without scanning the entire bucket.

Here's the index creation command:

CREATE INDEX idx_toy_attributes ON bucket(type, attributes.material, attributes.color, attributes.weight, attributes.height, attributes.width)
WHERE type = 'toy';

What's Going On Here? #

• Composite Fields: We're including every field from your WHERE clause in the index. Order matters here—start with the top-level type (since it's a high-selectivity filter that immediately narrows results to only "toy" docs), followed by the embedded attributes fields.
• Partial Index: The WHERE type = 'toy' clause makes this a partial index, which reduces the index size (it only includes docs where type is "toy") and speeds up query performance even more.

Bonus: Verify the Index is Being Used #

To make sure your query is leveraging this index, run an EXPLAIN on your query:

EXPLAIN SELECT * FROM bucket WHERE type = 'toy' AND attributes.material = 'plastic' AND attributes.color = 'red' AND attributes.weight = '200gr' AND attributes.height = '5cm' AND attributes.width = '15cm';

Look for references to idx_toy_attributes in the output—this confirms the query planner is using your new index.

If You Need Further Optimization #

If you frequently run queries that return specific fields instead of SELECT *, you could turn this into a covering index by adding the fields you need to the index's INCLUDE clause. For example, if you only need name and attributes, you'd modify the index like this:

CREATE INDEX idx_toy_attributes_covering ON bucket(type, attributes.material, attributes.color, attributes.weight, attributes.height, attributes.width)
WHERE type = 'toy'
INCLUDE (name, attributes);

This lets Couchbase return results directly from the index without fetching the full document, which is even faster.

内容的提问来源于stack exchange，提问作者Peter Karlsson