In a database, you can create indexes to improve query performance. As the volume of data in a table grows, 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 index: An index with the keyword
LOCALspecified during index creation is a local index. A local index does not require a partitioning rule to be specified. It has the same partitioning attribute as the primary table and will follow the partitioning operations of the primary table.Global index: An index with the keyword
GLOBALspecified during index creation is a global index. A global index can be partitioned based on a specified partitioning rule.Unique index: A unique index is an index with the keyword
UNIQUEspecified. The index key values must be unique.Prefix index: A prefix index is an index that is created on a table partitioned by using a partitioning key that is the prefix of the index. This is different from a prefix index in MySQL. For example, an index table named
idxis created on columnsc1andc2. If the partitioning key of the index table isc1, the index is a prefix index. If the partitioning key of the index table isc2or another column, the index is a non-prefix index.Non-prefix index: 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 prefix index is a unique or non-unique index where the partitioning key is the prefix of the index and the index contains a subpartitioning key. Local prefix indexes can be partitioned based on a specified partitioning rule.
A query with a specified index key can uniquely locate one index partition based on 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 as the partitioning key. Based on the characteristics of the local index, the index table and the primary table have the same partitioning attribute. Therefore, the idx table is also partitioned by using c1 as the partitioning key. By definition, this index is a local prefix index. When a query request contains a specified index key, you can use the value of the partitioning key c1 to uniquely locate one 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 local prefix index. This means that the partitioning key of the index is not the prefix of the index, or the index does not contain a subpartitioning key.
If the partitioning key is not a subset of the index, a local non-prefix index cannot be a unique index. A query with a specified index key cannot uniquely locate an index partition based on a local non-prefix index, but needs to 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 by using c4 as the partitioning key. Based on the definition, this index is a local non-prefix index. When a query request contains a specified index key, the system cannot use the values of the partitioning key to locate 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 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 methods 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 costs 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 although they have different partitioning attributes.
Global prefix indexes
A global prefix index is a global index where the first field is the partitioning key of the table. This means that the global prefix index is created based on the RANGE partitioning method.
A global prefix index can be a unique index or a non-unique index.
A global prefix index has meaning only when the RANGE partitioning method is used, but not when the HASH partitioning method is used. This is because if you choose to create a global index based on the HASH partitioning method, your query pattern is most likely to be point queries by index key. In this case, whether an index is a prefix index is irrelevant. You can calculate the index partition where a specified index key is located based on the partitioning key regardless of whether the partitioning key is covered by the index key. If the partitioning key is not covered by the index key, 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 HASH partitioned by using c2 as the partitioning key. In this case, the idx table is a global non-prefix index. In this case, partition pruning can be achieved only when all index keys are specified in a query request. Otherwise, the system needs to scan all index partitions to return the query result. Therefore, there is no need to use c2 as the partitioning key for index creation. Moreover, the system can perform partition pruning based on the prefix of the index key.
Unique indexes
A unique index can be a global index or a local index.
A unique index must be a local index when it is created. To be 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) as the partitioning key. This setup ensures that the same (c1,c2) value can enter only one partition. The index maintains uniqueness by ensuring the uniqueness of index keys within a single partition. If the idx table is partitioned by using (c2,c4) as the partitioning key, the index does not cover the partitioning key. In this case, the system cannot use the unique index to ensure the uniqueness of index keys across partitions. Therefore, a unique local index cannot be created.
Index creation strategies
When you create indexes, 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 managing index partitions 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, making the global index difficult to restore or unavailable.
If your queries always specify all index partitioning 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 and scenarios with small data volumes, whereas non-prefix indexes generally suit scenarios with large data volumes and parallel partition access.
Partition selection strategies
When you create a partitioned index, if the data volume of a single index table is too large, you need to divide it into several partitions to facilitate parallel execution of queries and load balancing.
If your queries are mostly single-point queries with the index partitioning keys and have similar access concurrency regardless of whether the data has hot spots, hash partitioning and range partitioning have similar query costs. However, hash partitioning can better avoid hot spots when they occur.
If your queries are mostly range queries with the index partitioning keys, range partitioning allows access to fewer partitions, whereas hash partitioning supports better concurrency. Therefore, range partitioning generally has lower query costs for a small number of partitions, whereas hash partitioning generally has higher query costs for a large result set.
Query-time index selection strategies
If you want to perform partition pruning, a prefix index is a better choice. With the partition pruning condition specified in the query filter, the system accesses only a few partitions, reducing the amount of index data that needs to be read.
If you want to achieve high throughput and the data volumes of your queries are large, a non-prefix index is a better choice. 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 result in longer response times for sequential access.