Global secondary indexes
Global secondary indexes (GSIs) let you add sharding dimensions to PolarDB-X tables so that queries on non-shard-key columns don't require full cross-shard scans. Each GSI is backed by an index table that PolarDB-X keeps in sync with the primary table using XA transactions.
How it works
In a distributed database, each logical table is split across shards by a shard key. When a query filters on a column that isn't the shard key, PolarDB-X must scan all shards—a cross-shard query. As table size and traffic grow, these scans cause slow queries and connection pool exhaustion.
A GSI solves this by maintaining a separate index table sharded by the indexed column. PolarDB-X routes the query directly to the relevant shard in the index table, then fetches matching rows from the primary table only when needed.
Key capabilities
| Capability | Description |
|---|---|
| Additional sharding dimensions | Index a column other than the primary table's shard key to route queries without cross-shard scans |
| Globally unique indexes | Enforce uniqueness constraints across all shards |
| Strong consistency | XA transactions keep the index table and primary table in sync at all times |
| Covering columns | Include extra columns in the index table to avoid lookups against the primary table |
| Online schema change | Add a GSI without locking the primary table |
| Index hints | Specify which index to use; PolarDB-X determines whether a primary table lookup is needed |
FAQ
What problems do GSIs solve?
When the column you query differs from the primary table's shard key, PolarDB-X initiates cross-shard queries. At scale, this leads to slow queries and connection pool exhaustion. A GSI reduces cross-shard queries by adding an alternative sharding dimension, so queries can target a specific shard in the index table rather than scanning all shards.
When creating a GSI, choose a shard key that differs from the primary table's shard key. See Use global secondary indexes for details.
What is the difference between a GSI and a local secondary index (LSI)?
A GSI stores index rows on different shards from the data rows. It determines which shard holds the relevant data and routes the query there, enabling efficient queries across shard boundaries.
An LSI stores index rows on the same shard as the data rows. In PolarDB-X 1.0, an LSI refers specifically to a MySQL secondary index on a physical table. When PolarDB-X 1.0 routes a query to a single shard using a GSI, the LSI on that shard can further improve query performance within the shard.