Partitioned indexes

In a database, you can create indexes to speed up queries. As the volume of data in a table grows larger, you can partition the table to split 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 need to specify partitioning rules. They have the same partitioning 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 rules.

• Unique indexes: A unique index is created with the UNIQUE keyword. The index key values must be unique.

• Prefix indexes: In a partitioned index table, if the partitioning key is the leftmost prefix of the index column, the index is a prefix index. This is different from a prefix index in MySQL. For example, an index table named idx is created 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 prefix local indexes and non-prefix local indexes based on the partitioning key.

Prefix local indexes

A local index that is a prefix index is an index where the partitioning key is the leftmost prefix of the index column. A prefix local index can be a unique index or a non-unique index.

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

For example, a table named A has a local index idx(c1,c2,c3). The primary table is partitioned by using c1. 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 by using c1. By definition, this local index is a prefix local index. When a query specifies the index key, the system can locate the unique index partition based on the values of the partitioning key c1, greatly reducing the number of index partitions to be accessed.

Non-prefix local indexes

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

If the partitioning key is not a subset of the index keys, a non-prefix local index cannot be a unique index. A query with a specified index key cannot uniquely locate an index partition through a non-prefix local index. Instead, it needs to access all index partitions. Therefore, non-prefix local indexes are suitable for scenarios with large data volumes and high concurrency.

For example, a table named A has a local index idx(c1,c2,c3). The primary table is partitioned by using c4. By definition, this index is a non-prefix local index. When a query specifies the index key, the system cannot locate the index partition based on the values of the partitioning key. Therefore, the system needs to scan all index partitions to return the query result. In this scenario, concurrency can help improve performance.

Global indexes

A global index has its own independent partitioning definition, which does not necessarily need to be the same as that of the primary table. Partitions of a global index table can be split or merged. Generally, if the partitioning rules of the primary table and the global index are the same, unique global indexes other than unique non-prefix indexes are recommended to be defined as local indexes. This is because maintaining a global index incurs 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 rule.

Prefix global indexes

A global index that has the same partitioning attribute as the primary table is a prefix global index.

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

A prefix global index is meaningful only when the RANGE partitioning method is used. The HASH partitioning method is irrelevant to prefix global indexes. This is because if the user chooses the HASH partitioning method, the user's query pattern is most likely to be exact point queries by index key. In this case, whether an index is a prefix index is irrelevant. The index keys in different partitions can be the same, and the user can calculate the index partition where a specific index key is located based on the index key. If the user does not specify all index keys, the HASH partitioned index needs to access all partitions, while the RANGE partitioned index can prune partitions to some extent.

Non-prefix global indexes

OceanBase Database does not support non-prefix global indexes. Non-prefix global indexes are of little significance for query optimization.

For example, a table named A has a global index idx(c1,c2). The index table idx is partitioned by using c2. In this case, the global index idx is a non-prefix global index. In this setup, the system can prune partitions only when the user specifies all index keys. Otherwise, the system needs to scan all index partitions to return the query result. Therefore, there is no need for the user to create a non-prefix global index.

Unique indexes

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

To create a unique local index, the index keys must cover the partitioning keys. For example, a table named A has a unique index idx(c1,c2,c3). The index table idx is partitioned by using (c1,c2). This setup ensures that the same (c1,c2) values will always be in the same partition. The unique local index maintains the uniqueness of index keys within a single partition. If the index table idx is partitioned by using (c2,c4), the index does not contain the partitioning keys. In this case, the system cannot use the local index to ensure the uniqueness of index keys between partitions. Therefore, a unique local 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 significant changes, difficulty 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, partition the index to enable parallel query 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 contains hot spots.

• If your queries are mostly range queries by index key, range partitioning enables 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-time index selection strategy

• If you want to perform partition pruning, a prefix index is a better choice. Partition pruning can be performed efficiently based on the specified partition prefix in the query filter conditions, thereby reducing the amount of data that needs to be read from index partitions.

• 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 enable parallel access to partitions for range queries on partitioning keys. Local non-prefix indexes can concurrently access all partitions, whereas local prefix indexes perform partition pruning and process a large amount of data from a few partitions. As a result, the response time may be longer with prefix indexes.