Partitioned indexes

In a database, you can create indexes to enhance query performance. As the volume of data in a table increases, you can partition the table to divide the data into several shards and use load balancing to distribute the data shards across the cluster to improve the overall service capability of the cluster. This topic explains local indexes, global indexes, and unique indexes.

Index types

OceanBase Database supports the following index types:

• Local indexes: An index created with the LOCAL keyword specified is a local index. Local indexes do not require a partitioning rule to be specified. It inherits the partitioning attribute from the primary table and will change accordingly after the partitioning operation is performed on the primary table.

• Global indexes: An index created with the GLOBAL keyword specified is a global index. Global indexes can be partitioned based on a specified partitioning rule.

• Unique indexes: An index with the UNIQUE keyword specified as the index name or the first column name is a unique index.

• Prefix indexes: In a partitioned index table, if the partitioning key is the leftmost prefix of the index key, the index is a prefix index. This is different from a prefix index in MySQL. For example, an index table named idx is created based on columns c1 and c2. 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 indexes: An index that is not a prefix index is a non-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 index that is also a prefix index is a local prefix index. A local prefix index can be a unique index or a non-unique index.

A query with a specified index key can be uniquely positioned in one index partition with a local prefix index, making it 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 by using c1. Based on the characteristics of the local index, the index table and the primary table have the same partitioning key. Therefore, the idx table is also partitioned by using c1. By definition, this index is a local prefix index. When a query contains a specified index key, you can use the value of the partitioning key c1 to uniquely position the query result in one index partition, significantly reducing the number of partitions to be accessed.

Local non-prefix indexes

A local index that is not a prefix index is a local non-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 the 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 positioned in a specific index partition with a local non-prefix index, but needs to access all index partitions. Therefore, it is suitable for scenarios with substantial data and require high concurrency.

For example, a local index idx(c1,c2,c3) is created on table A. The primary table is partitioned by using c4. By definition, this index is a local non-prefix index. When a query contains a specified index key, the system cannot use the values of the partitioning key to position the query result in specific index partitions. Therefore, the system needs to scan 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 partitioning definition, which is different from that of the primary table. In addition, partitions of a global index can be split or merged. Generally, if the partitioning methods of the primary table and the global index are the same, unique global indexes excluding unique non-prefix indexes are recommended to be defined as local indexes. This is because maintaining a global index incurs much higher partition management and maintenance costs than maintaining a local index, and global indexes and local indexes have the same query costs and benefits when they have the same partitioning method.

Global prefix indexes

A global index that has the same field as the first field of the primary table's partitioning key is a global prefix index.

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

A global prefix index is meaningful only when the RANGE partitioning method is used, but not the HASH partitioning method. This is because if the HASH partitioning method is chosen, the user's query pattern is most likely to be exact matching by index key. In this case, whether an index is a prefix index is irrelevant. The partitioning key and the index key that cover the partitioning key can be used to calculate the index partition. If the user does not specify all partitioning keys, the HASH partitioned index needs to access all partitions, while the 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 idx table is partitioned by using c2. In this case, the idx table is a global non-prefix index. In this setup, partition pruning can be achieved only when the user specifies all index keys. Otherwise, the system needs to scan all index partitions. Therefore, there is no need for the user to partition the data by using c2.

Unique indexes

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

To define a unique 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 idx table is partitioned by using (c1,c2). This setup ensures that the same (c1,c2) can be entered into the same partition. The unique local index maintains the uniqueness within a single partition. If the idx table is partitioned by using (c2,c4), the index does not contain the partitioning key. In this case, the system cannot use a local index to ensure the uniqueness of index keys across partitions. 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 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 the index as a local index. Otherwise, define the index as a global index.

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

• If the partitioning attribute of the primary table is the same as that of the index, we recommend that you define the index as 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 will result in substantial changes, difficulties in restoration, and even unavailability of the global index.

• If your queries always specify all index partition keys, you can create an index on other columns without partitioning to reduce the maintenance and storage costs; prefix indexes are suitable for scenarios with small data volumes where partition pruning can be performed, and non-prefix indexes generally work better in scenarios with large data volumes such as parallel partition access.

Partition selection strategy

When you create a partitioned index, if a single index table contains a large volume of data, you need to divide the data into several partitions to facilitate parallel execution of queries and load balancing.

• If your queries are mostly single-point queries with the index key specified, the query costs of 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 has hot spots.

• If your queries are mostly range queries with the index key specified, range partitioning is more advantageous than hash partitioning in terms of the number of partitions accessed. However, hash partitioning can better leverage concurrency in parallel queries. Therefore, hash partitioning is more advantageous than range partitioning in scenarios with large query result sets.

Query-time index selection strategy

• Prefix indexes are more suitable for partition pruning. If the filter conditions of your queries include the partition prefix, the system can prune partitions to reduce the amount of data that needs to be retrieved from the index partitions.

• Non-prefix indexes are more suitable for scenarios with large data volumes where parallel access to all partitions can improve performance. Local non-prefix indexes can concurrently access all partitions, whereas local prefix indexes prune partitions. As a result, a small number of partitions need to process a large amount of data, which can result in longer response times for sequential access.