PolarDB:Global secondary indexes

Features

GSIs allow users to add dimensions for sharding as needed and provide globally unique constraints. Each GSI corresponds to an index table and uses XA transactions to ensure strong consistency between primary tables and index tables.

This type of index supports the following features:

• Adds dimensions for sharding.
• Supports globally unique indexes.
• Provides XA transactions to ensure strong consistency between primary tables and index tables.
• Supports covering columns to reduce overheads from querying the primary table.
• Provides the online schema change feature, so that the primary table can remain unlocked when a GSI is added.
• Uses hints to specify indexes to automatically determine whether to query the primary table.

FAQ

• Q: What problems can GSIs solve?
  A: If the queried dimension is different from the dimension for sharding of a logical table, cross-shard queries are initiated. When cross-shard queries increase, performance problems such as slow query and connection pool exhaustion may occur. GSIs can reduce cross-shard queries and eliminate performance bottlenecks by adding dimensions for sharding.

  Note When you create a GSI, select a shard key that is different from that of the primary table. For more information, see Use global secondary indexes.
• Q: What is the relationship between a GSI and a local secondary index (LSI)?

  A: The following content shows the difference and relationship between a GSI and an LSI:

  • A GSI stores data rows and corresponding index rows on different shards. It determines the data shards involved in the query.
  • An LSI stores data rows and corresponding index rows on the same shard in a distributed database. In PolarDB-X 1.0, it refers in particular to a MySQL secondary index of a physical table.
  • When PolarDB-X 1.0 distributes queries to a single shard by using a GSI, the LSI of the shard can improve the query performance within the shard.