Partitioned indexes

In a database, you can create indexes to speed up queries. 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 enhance 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 partitioning strategies to be specified. They have the same partition attribute as the primary table and will follow the partitioning operations of the primary table.

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

• Unique indexes: An index with the UNIQUE keyword specified has unique index keys.

• 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. Note that this is different from a prefix index in MySQL. For example, an index table named idx is built 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 has the partitioning key as the leftmost prefix of the index key and that contains subpartitioning keys is a local prefix index. Local prefix indexes can be unique or non-unique.

A query with a specified index key can be uniquely located in one index partition with a local prefix index. This is suitable for scenarios with a small result set but requires 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 strategy. Therefore, the idx index is partitioned by using c1 as the partitioning key. By definition, this index is a local prefix index. When a query contains a specified index key, the query can be partitioned by using the partitioning key c1 to uniquely locate one index partition. This greatly reduces 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 is not the leftmost prefix of the index key, or the index does not contain subpartitioning keys.

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 located in index partitions but must access all index partitions with a local non-prefix index. 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 by using c4. By definition, this index is a local non-prefix index. When a query contains a specified index key, the query cannot be partitioned by using the partitioning key but must access all index partitions to obtain results. Concurrency can play an important role in this case.

Global indexes

A global index has its own partitioning 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 strategies of the primary table and the global index are the same, unique global indexes are recommended to be defined as local indexes. This is because maintaining a global index costs much more in partition management and maintenance. In addition, the query performance with a global index is similar to that with a local index of the same partitioning strategy.

Global prefix indexes

A global prefix index is a global index whose first field is the partitioning key of the table. In other words, the partitioning key of the table is the leftmost prefix of the index key of the global index.

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

A global prefix index makes sense only for a table partitioned by RANGE but not for a table partitioned by HASH. This is because if a user chooses to partition a table by HASH, the user's query pattern will definitely be point queries by index key. In this case, whether an index is a prefix index is meaningless. The index keys in different partitions can be the same, and the user can calculate the index partition based on the specified index key. If a user does not specify all partitioning keys, a HASH-partitioned table needs to access all partition data, while a RANGE-partitioned table 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 table idx is partitioned by using c2. 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 index partitions 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 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 index table idx is partitioned by using (c1,c2). This can ensure that the same (c1,c2) value will always go to the same partition. In this partition, only the uniqueness of the index key needs to be maintained. If the index table idx is partitioned by using (c2,c4), the index does not cover the partitioning key, and therefore a unique local index cannot be ensured. In this case, a unique local 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, 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 managing index partitions and the partitions of the primary table are constantly pruned, we recommend that you do not create a global index. This is because pruning partitions in the primary table causes significant changes in the global index, making it difficult to restore, and may render the index unavailable.

• If your queries always specify all index partitioning 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 only partition pruning is needed, and full non-prefix indexes generally suit scenarios with large data volumes where partition parallelism is also required.

Partition selection strategy

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

• 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 has hot spots.

• If your queries are mostly range queries by index key, range partitioning is more suitable in terms of the number of partitions accessed. However, hash partitioning can better support concurrent queries, especially when the size of the query result set is large. In this case, concurrent hash partitioning can have a significant performance advantage.

Query-time index selection strategy

• If you want to perform partition pruning, a prefix index is a better choice. Partition pruning can be maximized by specifying the partition prefix in the query filter condition, reducing the number of index partitions that need to be read.

• If you prioritize throughput and a large amount of data is to be accessed, a non-prefix index is a better choice. Non-prefix indexes can support partition parallelism for range queries on partitioning keys. Local non-prefix indexes can concurrently access all partitions, whereas local prefix indexes perform partition pruning, handling a large amount of data from a few partitions. In this case, the response time may be slower compared to concurrent queries.