Partitioned indexes

As data volumes in databases grow, you can partition a table into multiple shards and distribute the shards across the cluster to improve the overall service capability of the cluster. You can also create indexes to speed up queries. This topic explains local, global, and unique indexes.

Index types

OceanBase Database supports the following index types:

• Local partitioned index: A local partitioned index is created by specifying the LOCAL keyword when creating an index. A local partitioned index does not require a partitioning rule to be specified. It has the same partition attribute as the primary table and is affected by any partition operation on the primary table.

• Global partitioned index: A global partitioned index is created by specifying the GLOBAL keyword when creating an index. A global partitioned index can be partitioned according to a specified partitioning rule.

• Unique index: A unique index is created by specifying the UNIQUE keyword. The values of the index key are unique.

• Prefix index: A prefix index is an index where the partitioning key of the index table is the leftmost prefix of the index key. For example, an index table idx is created based on columns c1, c2, and c3. If the partitioning key of the index table is c1, the index is a prefix index; if the partitioning key of the index table is c2 or another column, the index is a non-prefix index.

• Non-prefix index: A non-prefix index is an index that is not a prefix index.

Local indexes

Local indexes are further divided into local prefix indexes and local non-prefix indexes based on the partitioning key.

Local prefix indexes

A local prefix index is a unique or non-unique index where the partitioning key of the index is the leftmost prefix of the index key. Local prefix indexes can be partitioned according to a specified partitioning rule.

A query with a specified index key can uniquely locate one index partition. Therefore, local prefix indexes are suitable for scenarios with small result sets but require partition pruning.

For example, a local index idx(c1,c2,c3) is created on table A. The primary table is partitioned according to the c1 value. Based on the characteristics of the local index, the index table and the primary table have the same partitioning rule. Therefore, the idx index is also partitioned according to the c1 value. As defined, the idx index is a local prefix index. When a query request contains a specified index key, the query can be directed to the unique partition based on the value of the partitioning key c1, greatly reducing the number of partitions to be accessed.

Local non-prefix indexes

A local non-prefix index is an index that is not a local prefix index. This means that the partitioning key of the index is not the leftmost prefix of the index key, or the index does not contain a partitioning key.

If the partitioning key is not a subset of the index key, a local non-prefix index cannot be a unique index. A query with a specified index key cannot be uniquely directed to one index partition but must access all index partitions. Therefore, local non-prefix indexes are suitable for scenarios with large data volumes and require high concurrency.

For example, a local index idx(c1,c2,c3) is created on table A. The primary table is partitioned according to the c4 value. Based on the definition, the idx index is a local non-prefix index. When a query request contains a specified index key, the query cannot be directed to index partitions based on the partitioning key values but must access all index partitions to obtain results. Concurrency execution can play an important role in this scenario.

Global indexes

A global index has its own independent partition definition, which does not need to be the same as that of the primary table. Partitions of a global index can be split or merged. Generally, if the partitioning rule of the primary table is the same as that of the global index, unique global indexes and unique local indexes are recommended. This is because a global index entails much higher costs in partition management and maintenance than a local index, and global indexes and local indexes have the same effects on query costs and partition pruning given the same partitioning rule.

Global prefix indexes

A global prefix index is a global index where the first field is the partitioning key of the table. In other words, a global prefix index is created by partitioning according to the same partitioning rule as the table.

A global prefix index can be a unique index or a non-unique index.

A global prefix index has practical significance only when RANGE partitioning is used. HASH partitioned indexes are not meaningful because, with HASH partitioned indexes, user queries are always precise key-based point queries. Whether an index is a prefix index or not does not matter if the index key covers the partitioning key. In that case, you can calculate the index partition where the query key lies based on the index key value. If the index key does not cover the partitioning key, the HASH partitioned index must access all partitions, whereas a RANGE partitioned index can prune partitions to a certain extent.

Global non-prefix indexes

OceanBase Database does not support global non-prefix indexes. Therefore, global non-prefix indexes have no significance for query optimization.

For example, a global index idx(c1,c2) is created on table A. The index idx is partitioned according to the c2 value. In this case, the idx index is a global non-prefix index. In this scenario, partition pruning can be achieved only when all index keys are specified. Otherwise, all partitions of the index must be scanned. Therefore, there is no need to create a global non-prefix index.

Unique indexes

A unique index can be a global index or a local index.

To create a local unique index, the index key must cover the partitioning key of the index. For example, a unique index idx(c1,c2,c3) is created on table A. The index table idx is partitioned according to (c1,c2). In this case, the same (c1,c2) value can be found in only one partition. Therefore, you only need to maintain uniqueness within a single partition. If the index table idx is partitioned according to (c2,c4), the index does not contain a partitioning key. In this case, you cannot ensure the uniqueness of index keys between partitions through a local index. Therefore, a local unique index cannot be created.

Index creation strategies

When you create an index, consider your query patterns, index management, performance, and availability needs to choose the most suitable index strategy for your business.

• If you need a unique index and the index key covers all partitioning keys, define it as a local index. Otherwise, define it as a global index.

• If the partitioning keys of the primary table are a subset of the index keys, you can define a local index.

• If the partitioning attribute of the primary table is the same as that of the index, we recommend that you define a local index.

• If you are more concerned about the costs of index partition management and the partitions of the primary table are constantly being sub-divided, we recommend that you do not create a global index. This is because sub-dividing partitions of the primary table causes significant changes in the global index that are difficult to restore and may render the index unavailable.

• If your queries always specify all index partition keys, you only need to create an index on other columns, without including the partitioning keys, to reduce the maintenance and storage costs; prefix indexes are more suitable for partition pruning and scenarios with small data volumes, whereas non-prefix indexes generally suit scenarios with large data volumes.

Partition selection strategy

When you create a partitioned index, if a single index table contains a large amount of data, partition the table to enable parallel partition processing and load balancing for queries.

• If your queries are mostly single-point queries by index key, the query costs for hash-partitioned and range-partitioned indexes are similar in terms of the number of partitions accessed and the level of concurrency. However, hash partitioning can better avoid hot spots when data contains hot spots.

• If your queries are mostly range queries by index key, range partitioning allows access to fewer partitions than hash partitioning. However, hash partitioning can better utilize concurrent queries when the result set is large. Therefore, hash partitioning has greater advantages than range partitioning in concurrent queries with large result sets.

Query index selection strategy

• If you want to perform partition pruning, a prefix index is a better choice. With a prefix index, you can specify the partition prefix in the query filter condition to prune partitions and reduce the amount of data that needs to be read from indexes.

• If you aim to achieve high throughput and a large amount of data is to be accessed, a non-prefix index is a better choice. Non-prefix indexes enable range queries on partitioning keys and support parallel access to all partitions. However, a local prefix index can only concurrently access a limited number of partitions. As a result, it may take a long response time when it handles a large volume of data.