PolarDB:Global Secondary Indexes (GSIs)

PolarDB for MySQL supports creating global secondary indexes (GSIs) on partitioned tables. A GSI makes a partitioned table transparent, allowing you to use it as you would a non-partitioned table. This significantly reduces the limitations imposed by partition keys.

Background information

A traditional index on a partitioned table is a local index. A local index is partitioned in the same way as its table, and the data for each index partition is built from the corresponding table partition. Therefore, a local index only guarantees that data is sorted within each partition. To create a local unique index, the index fields must include all partition keys.

When a partitioned table has only local indexes, using the table can be challenging because of the limitations of partition keys:

• If a query condition does not include the partition key, the query must scan all partitions of the table. This causes significant read amplification, which worsens as the number of partitions increases.

• If a query result requires a specific order based on the index fields, the data is not guaranteed to be globally sorted across partitions, even if the data within each partition is sorted. This may trigger an additional global sorting operation.

• A local unique index must include all partition keys. Otherwise, it cannot guarantee the global uniqueness constraint across all partitions.

Unlike a local index, a global index in PolarDB for MySQL is not partitioned. It is built from the data of all partitions. Therefore, the data in a global index is sorted across the entire partitioned table. To create a globally unique index, the index fields do not need to include all partition keys.

Prerequisites

Your cluster must run PolarDB for MySQL 8.0.2, revision 8.0.2.2.7 or later. To check your cluster version, see Query the version number.

Limits

• Global secondary indexes are supported only on partitioned tables that use the InnoDB engine, not on hybrid partitioned tables.

• A global secondary index cannot be a full-text index or a spatial index.

• Global secondary indexes are not supported on compressed tables, temporary tables, encrypted tables, or tables that use the REDUNDANT or COMPRESSED row format.

• Tables on which GSIs are created do not support generated columns.

• Partition-level DDL operations, except for adding new RANGE or LIST partitions, invalidate existing GSIs. You must then delete and rebuild all GSIs on the table. Alternatively, you can use the UPDATE GLOBAL INDEX syntax to rebuild the secondary indexes.

Feature enhancements

• You can create GSIs in parallel using parallel DDL.

• You can use the instant add column feature on partitioned tables that have GSIs.

• For RANGE or LIST partitioned tables that have GSIs, partition-level metadata locks (MDLs) are supported when you add new partitions.

• You can convert a table with GSIs into an INTERVAL RANGE partitioned table. You can also create GSIs on an INTERVAL RANGE partitioned table.

• When you perform partition-level DDL operations on a partitioned table that has GSIs, you can use the UPDATE GLOBAL INDEX syntax to rebuild the GSIs on the table. For example:

  1. Create a range-partitioned table named t1 that uses the a field as the partition key, and create a global index named k1 on the b field.

     CREATE TABLE t1(
       a INT PRIMARY KEY,
       b INT,
       INDEX k1(b) GLOBAL
     ) PARTITION BY RANGE (`a`)
     (PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
      PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB);

  2. Delete the p1 partition from the t1 table and rebuild the GSI.

     ALTER TABLE t1 DROP PARTITION p1 UPDATE GLOBAL INDEX;

Syntax

When you create an index, you can add the LOCAL or GLOBAL keyword after the index name to specify whether it is a local or global index.

Examples

• Create a partitioned table named t1 that uses the a field as the partition key, and create a global index named k1 on the b field.

  CREATE TABLE t1(
    a INT PRIMARY KEY,
    b INT,
    INDEX k1(b) GLOBAL
  ) PARTITION BY HASH(a) PARTITIONS 3;

• You can create a partitioned table named t1 that uses the a field as the partition key, and then create a global index named k1 and a globally unique index named k2 on the b field of table t1.

  CREATE TABLE t1(
    a INT PRIMARY KEY,
    b INT
  ) PARTITION BY HASH(a) PARTITIONS 3;
  
  ALTER TABLE t1 ADD INDEX k1(b) GLOBAL;
  
  CREATE UNIQUE INDEX k2 ON t1(b) GLOBAL;

Performance testing

Test objects

The test uses two partitioned tables that have the same table schema and one million data entries each. A local index is created on one partitioned table, and a GSI is created on the other.

This example uses the partitioned tables mytest1.big_table_1 and mytest2.big_table_1. The mytest1.big_table_1 table has a local index, and the mytest2.big_table_1 table has a GSI.

CREATE TABLE mytest1.big_table_1(
  a INT PRIMARY KEY,
  b INT,
  c INT,
  INDEX k1(b) LOCAL
) PARTITION BY HASH(a) PARTITIONS 32;

CREATE TABLE mytest2.big_table_1(
  a INT PRIMARY KEY,
  b INT,
  c INT,
  INDEX k1(b) GLOBAL
) PARTITION BY HASH(a) PARTITIONS 32;

Test method

This test measures the time required to execute SELECT, UPDATE, and DELETE statements with query conditions that do not contain the partition key. The test is run on tables with different numbers of partitions to compare the performance of local indexes and GSIs.

Test results

• Execution time of a SELECT statement with a query condition that does not include the partition key.

• Execution time of an UPDATE statement with a query condition that does not include the partition key.

• Execution time of a DELETE statement with a query condition that does not include the partition key.

The test results show that when query conditions do not include the partition key, SELECT, UPDATE, and DELETE commands execute faster with GSIs. This performance advantage becomes more significant as the data volume increases.