Global Secondary Index(GSI)

Global secondary index (GSI) is an important feature of PolarDB-X. A GSI is different from a local secondary index. The data in a GSI is partitioned based on specified methods and then distributed among multiple storage nodes. GSIs allow you to add dimensions for data partitioning and create globally unique constraints.

Each GSI corresponds to a distributed index table. A distributed index table is horizontally split into multiple physical tables based on specified rules. PolarDB-X uses distributed transactions to ensure strong data consistency between the primary table and index tables of a database.

If you use GSIs, you can perform the following operations:

• You can use covering indexes to reduce the overhead of querying the primary table.
• You can change the schema of a table online and add a GSI without locking the primary table.
• You can use hints to specify indexes. After you perform this operation, the system can automatically determine whether to query the primary table.

Example 1: Add a dimension for data partitioning. For example, if you partition the order table of an online shopping platform by buyer, all partitions must be scanned when you query the data of a specified seller, such as all orders placed by a specified seller within the current month. GSIs allow you to scan only the index table partition where the data of the queried seller is stored. This can help you locate the order information in a quick manner.

Example 2: Add a globally unique constraint. For example, a user table is a distributed table that is partitioned by user ID. If you want the mobile phone number of a user to be globally unique, a unique index that uses the mobile phone number as the index key and the partition key must be created. The mobile phone number of a user cannot be globally unique if only local indexes are used.