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 create indexes on the shards to enhance query performance. 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 operations 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 based on 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 prefix of the index column. 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 classified 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 prefix of the index column. Local prefix indexes can be partitioned based on a specified partitioning rule.

A query with a specified index key can uniquely locate a single index partition with a local prefix index. This is suitable for scenarios with a small result set but requiring partition pruning.

For example, a local index idx(c1,c2,c3) is created on table A. The primary table is partitioned based on the c1 column. According to 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 based on the c1 column. By definition, this index is a local prefix index. When a query request contains a specified index key, the system can locate the unique index partition based on the value of the partitioning key c1, thereby reducing the number of partitions to be accessed.

Local non-prefix indexes

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

A local non-prefix index cannot be unique if the partitioning key is not a subset of the index column. A query with a specified index key cannot uniquely locate an index partition with a local non-prefix index. Instead, it must access all index partitions. Therefore, it is suitable for scenarios with a large amount of data and that require high concurrency.

For example, a local index idx(c1,c2,c3) is created on table A. The primary table is partitioned based on the c4 column. According to the definition, this index is a local non-prefix index. When a query request contains a specified index key, the system cannot locate an index partition based on the values of the partitioning key. Therefore, it must access all index partitions to return the query result. In this scenario, concurrency can play an important role.

Global indexes

A global index has its own independent partition definition, which does not necessarily 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 a global index is the same as that of the primary table, unique indexes except for unique non-prefix indexes are recommended to be defined as local indexes. This is because maintaining a global index incurs much higher partition management overheads than maintaining a local index, and global indexes and local indexes have the same query performance with the same partitioning rule.

Global prefix indexes

A global prefix index is a global index where the first column is the partitioning key of the table. In other words, a global prefix index is created by specifying the GLOBAL keyword and using the primary key as the partitioning key.

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

A global prefix index has practical significance only when RANGE partitioning is used, not when HASH partitioning is used. This is because if you choose to use HASH partitioning, your query pattern will necessarily be point queries by index key. In this case, whether an index is a prefix index or not does not matter. You can calculate the index partition where a specified index key is located based on the index key. If you do not specify all values of the partitioning key, a HASH-partitioned index must access all partition data, whereas a RANGE-partitioned index can prune some partitions.

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 table idx is partitioned based on the c2 column. 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, the system must scan all index partitions. Therefore, there is no need to use c2 as the partitioning key for index creation. If you use c1 as the partitioning key, you can apply prefix filtering to prune partitions.

Unique indexes

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

A unique index must meet the following criteria if it is a local index: The index key must cover the partitioning key. For example, a unique index idx(c1,c2,c3) is created on table A. The index table idx is partitioned based on (c1,c2). This setup ensures that the same (c1,c2) value can enter only one partition. The unique index maintains the uniqueness of index keys within a single partition. If the index table idx is partitioned based on (c2,c4), the index does not contain a partitioning key. In this case, the unique index cannot guarantee the uniqueness of index keys between partitions. Therefore, such a unique index cannot be created.

Index creation strategies

When you create an index, consider your query patterns, index management, performance, and availability requirements 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, 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 frequently pruned, we recommend that you do not create a global index. This is because frequent partition pruning of the primary table causes significant changes, making it difficult to restore the global index, which may even become 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 in scenarios with small data volumes, whereas non-prefix indexes generally perform better in scenarios with large data volumes and support parallel partition access.

Partition selection strategy

When you create a partitioned index, if a single index table contains a large amount of data, partitioning is necessary to facilitate parallel query execution and load balancing.

• If your queries are mostly single-point queries by index key and the number of partitions accessed and the level of concurrency are similar, the cost of a hash-partitioned index is similar to that of a range-partitioned index. However, if data has hot spots, a hash-partitioned index can better avoid hot spots.

• If your queries are mostly range queries by index key, a range-partitioned index is better than a hash-partitioned index in terms of the number of partitions accessed; however, a hash-partitioned index can better support concurrent queries, especially when the size of the query result set is large. In other words, a concurrent hash-partitioned index is expected to perform better than a concurrent range-partitioned index.

Query-time 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 the partitions. This reduces the amount of index partition data that is read.

• If you want to achieve a high throughput and access a large amount of data, a non-prefix index is a better choice. With a non-prefix index, you can use partition parallelism to handle range queries on the partitioning key. Both local and global non-prefix indexes can concurrently access all partitions, but a local prefix index accesses only a few partitions. As a result, a large amount of data must be processed by a few partitions, which can result in longer response times.