Introduction
There are three things important in database systems:performance, performance, performance. Creating the right index, with the right keys, right order, and right expression is critical to query performance in any database system. That's true for Couchbase as well.
We've discussed data modeling for JSON and querying on JSONearlier. In this article, we'll discuss indexing options for JSON in Couchbase.
Couchbase 4.5 can create two types of indices:
- Standard global secondary index.
- Memory-Optimized global secondary index.
The standard secondary index stores uses the ForestDB storage engine to store the B-Tree index and keeps the optimal working set of data in the buffer. That means, the total size of the index can be much bigger than the amount of memory available in each index node.
A memory-optimized index uses a novel lock-free skiplist to maintain the index and keeps 100% of the index data in memory. A memory-optimized index has better latency for index scans and can also process the mutations of the data much faster.
Both standard and memory-optimized indeces implement multi-version concurrency control (MVCC) to provide consistent index scan results and high throughput. During cluster installation, choose the type of index.
The goal is to give you an overview of various indices you create in each of these services so that your queries can execute efficiently.
The goal of this article is not to describe or compare and contrast these two types of index services. It does not cover the Full Text Index (FTS), in developer preview now. Another topic not covered by this article is how the index selection is made for scans and joinsCouchbase documentation
Let's take travel-sample dataset shipped with Couchbase 4.5 to walk through this. To try out these indices, install Couchbase 4.5. On your web console, go to Settings->Sample Buckets to install travel-sample.
Here are the various indices you can create.
Background
Couchbase is a distributed database. It supports flexible data model using JSON. Each document in a bucket will have a user-generated unique document key. This uniqueness is enforced during insertion of the data.
Here's an example document.
1. Primary Index
Create the primary index on 'travel-sample';
The primary index is simply the index on the document key on the whole bucket. The Couchbase data layer enforces the uniqueness constraint on the document key. The primary index, like every other index, is maintained asynchronously. The primary index is used for full bucket scans (primary scans) when the query does not have any filters (predicates) or no other index or access path can be used.
Here is the metadata for this index:
The metadata gives you additional information on the index: Where the index resides (datastore_id), its state (state) and the indexing method (using).
2. Named Primary Index
CREATE PRIMARY INDEX `def_primary` ON `travel-sample`
You can also name the primary index. The rest of the features of the primary index are the same, except the index is named. A good side effect of this is that you can have multiple primary indices in the system. Duplicate indices help with high availability as well as query load distribution throughout them. This is true for both primary indices and secondary indices.
3. Secondary Index
The secondary index is an index on any key-value or document-key. This index can be any key within the document. The key can be of any time: scalar, object, or array. The query has to use the same type of object for the query engine to exploit the index.
Creating indexes on keys from nested objects is straightforward.
Schedule is an array of objects with flight details. This indexes on the complete array. Not exactly useful unless you're looking for the whole array.
4. Composite Secondary Index
It's common to have queries with multiple filters (predicates). So, you want the indices with multiple keys so the indices can return only the qualified document keys. Additionally, if a query is referencing only the keys in the index, the query engine will simply answer the query from the index scan result without going to the data nodes. This is a commonly exploited performance optimization.
CREATE INDEX travel_info ON `travel-sample`(name, type, id, icoo, iata);
Each of the keys can be a simple scalar field, object, or an array. For the index filtering to be exploited, the filters have to use respective object type in the query filter.
The keys to the secondary indices can include document keys (meta().id) explicitly if you need to filter on it in the index.
5. Functional Index
It's common to have names in the database with a mix of upper and lower cases. When you need to search, "John," you want it to search for any combination of "John," "john," etc. Here's how you do it.
CREATE INDEX travel_cxname ON `travel-sample`(LOWER(name));
Provide the search string in lowercase and the index will efficiently search for already lowercased values in the index.
You can use complex expressions in this functional index.
6. Array Index
JSON is hierarchical. At the top level, it can have scalar fields, objects, or arrays. Each object can nest other objects and arrays. Each array can have other objects and arrays. And so on. The nesting continues.
When you have this rich structure, here's how you index a particular array, or a field within the sub-object.
Consider the array, schedule:
The query below will exploit the array index.
Because the key is a generalized expression, you get the flexibility to apply additional logic and processing on the data before indexing. For example, you can create functional indexing on elements of each array. Because you're referencing individual fields of the object or element within the array, the index creation, size, and search are efficient.
The index above stores only the distinct values within an array. To store all elements of an array in an index, use the DISTINCT modifier to the expression.
7. Partial Index
So far, the indices we've created will create indices on the whole bucket. Because the Couchbase data model is JSON and JSON schema are flexible, an index may not contain entries to documents with absent index keys. That's expected.
Unlike relational systems, where each type of row is in a distinct table, Couchbase buckets can have documents of various types. Typically, customers include a type field to differentiate distinct types.
When you want to create an index of airline documents, you can simply add the type field for the WHERE clause of the index.
CREATE INDEX travel_info ON `travel-sample`(name, id, icoo, iata)
WHERE type = 'airline';
This will create an index only on the documents that have (type = ‘airline'). In your queries, you'd need to include the filter (type = ‘airline') in addition to other filters so this index qualifies.
You can use complex predicates in the WHERE clause of the index. Various use cases to exploit partial indexes are:
- Partitioning a large index into multiple indices using the mod function.
- Partitioning a large index into multiple indices and placing each index into distinct indexer nodes.
- Partitioning the index based on a list of values. For example, you can have an index for each state.
- Simulating index range partitioning via a range filter in the WHERE clause. One thing to remember is Couchbase N1QL queries will use one partitioned index per query block. Use UNION ALL to have a query exploit multiple partitioned indices in a single query.
8. Duplicate Index
This isn't really a special type of index, but a feature of Couchbase indexing. You can create duplicate indexes with distinct names.
All three indices have identical keys, identical WHERE clause; Only difference is the name of the indices. You can choose their physical location using the WITH clause of the CREATE INDEX.
During query optimization, query will choose one of the names. You see that in your plan. During query runtime, these indices are used in round-robin fashion to distribute the load. This gives you scale-out, multi-dimensional scaling, performance, and high availability. Not bad!
9. Covering Index
Index selection for a query solely depends on the filters in the WHERE clause of your query. After the index selection is made, the engine analyzes the query to see if it can be answered using only the data in the index. If it does, query engine skips retrieving the whole document. This is a performance optimization to consider while designing the indices.
Summary
Let's put together a partitioned composite functional array index now!
References
Nitro: A Fast, Scalable In-Memory Storage Engine for NoSQL Global Secondary Index :http://vldb2016.persistent.com/industrial_track_papers.php
Couchbase: http://www.couchbase.com
Couchbase Documentation: http://docs.couchbase.com
Comments
Post a Comment