Partitioned indexes

In a database, you can create indexes to speed up queries. As the data volume in a data table increases, you can divide the data table into several shards, and distribute the shards across the cluster to improve the overall service capability of the cluster. This process is called partitioning. This topic explains local indexes, global indexes, 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. 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 partitioning rule.

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

• Prefix index: A prefix index is an index where the partitioning key of the index table is the prefix of the index key. For example, an index table 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 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 prefix of the index key. Local prefix indexes can be partitioned based on a 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 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 contains a specified index key, the system can use the partitioning key c1 to locate the unique index partition. This greatly reduces the number of index partitions to be accessed.

Local non-prefix indexes

A local non-prefix index is an index that is not a prefix index. This means that the partitioning key of the index table is not the 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 use a local non-prefix index to uniquely locate an index partition. Instead, the system must access all index partitions. Therefore, local non-prefix indexes are suitable for scenarios with large data volumes and 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 user queries a specified index key, the system cannot use the partitioning key to locate an index partition. Therefore, the system must access all index partitions to return the query result. Concurrency execution can play an important role in this scenario.

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 costs much more in partition management and maintenance than maintaining a local index, and global indexes and local indexes have the same effects on query costs and partition pruning when they have 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 the index based on 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 is meaningful only when the RANGE partitioning rule is used, but not when the HASH partitioning rule is used. If the user chooses to partition the table by using the HASH partitioning rule, the user's query pattern would 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 still locate the same data by using the index keys. If the user does not specify all the partitioning keys, the HASH-partitioned index must access all partition data. In contrast, with the RANGE partitioning rule, the system can prune 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 idx is partitioned based on the c2 column. In this case, the idx index is a global non-prefix index. In this scenario, the system can prune partitions only when the user specifies all index keys. Otherwise, the system must scan all index partitions. Therefore, there is no reason for the user to partition the data by using the c2 column instead of the c1 column.

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 to be 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 partitioning rule ensures that the same (c1,c2) values will definitely fall into the same partition. In this partition, the index maintains uniqueness. If the index table idx is partitioned based on (c2,c4), the index does not contain a partitioning key. In this case, the system cannot use a local index to guarantee 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 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 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 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 work better in scenarios with large data volumes.

Partition selection strategies

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

• If your queries are mostly single-point queries by using index keys, 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, if data has hot spots, hash partitioning can help avoid hot spots more effectively.

• If your queries are mostly range queries by using index keys, 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 a greater advantage over range partitioning in scenarios with large data volumes.

Query-time index selection strategies

• Prefix indexes are more suitable for partition pruning to reduce the amount of data that needs to be read. We recommend that you use the prefix in the filter condition of your query to specify the partitions to be pruned.

• If you are more concerned about throughput and your application involves large data volumes, non-prefix indexes are more suitable. You can use partition parallelism to handle range queries on partitioning keys. Local non-prefix indexes can concurrently access all partitions, whereas local prefix indexes prune partitions. As a result, a few partitions need to process a large amount of data, which can lead to longer response times for sequential queries.