PolarDB:GSIs

PolarDB for MySQL allows you to create global secondary indexes (GSIs) on partitioned tables. GSIs allow you to use partitioned tables as common tables and enable data queries and access by using an alternative key in addition to the primary key.

Background information

Traditionally, local indexes are used on partitioned tables. A local index offers a way to sort and access data only within an individual partition of a partitioned table. It does not provide sorting for data across all partitions of a table. To create a locally unique index, you must include all partition keys in the index field.

If a partitioned table contains only local indexes, the table may encounter the following issues due to limits on partition keys:

• If no partition keys are specified in query conditions, all partitions on the partitioned table are scanned for data query. This causes read amplification, which increases with the number of partitions.

• Data that requires to be sorted by an index may not be sorted in cross-partition queries even if the data is sorted by the index in each partition. Global sorting may be triggered.

• A locally unique index must contain all partition keys. Otherwise, the index may not be globally unique among all partitions.

In PolarDB for MySQL, a global index, unlike a local index which is confined to an individual partition, sorts data across all table partitions. To create a globally unique index, you do not need to include all partition keys in the index field.

Prerequisites

Your cluster runs PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.7 or later. To query the version of a cluster, see the "Query the engine version" section in the Engine versions topic.

Limits

• You can create GSIs only on partitioned tables that use the InnoDB engine. You cannot create GSIs on hybrid partitioned tables.

• GSIs cannot be full-text indexes or spatial indexes.

• You cannot create GSIs on compressed tables, temporary tables, encrypted tables, or tables that use the redundant or compressed row store formats.

• The column in which GSIs are created cannot be the primary key of the partitioned table.

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

• If you execute partition-level DDL statements, except for creating range or list partitions, existing GSIs become invalid. You must delete all existing GSIs on the partitioned table, and then recreate GSIs. You can also execute the UPDATE GLOBAL INDEX statement to recreate GSIs.

Feature enhancements

• You can use the parallel DDL feature to create GSIs in parallel.

• Partitioned tables on which GSIs are created support the Instant ADD COLUMN feature.

• If you have created a range or list partitioned table for a global secondary index, partition-level metadata locks (MDLs) are supported when you add partitions.

• You can convert a table on which GSIs are created into an interval range partitioned table or create GSIs on an INTERVAL RANGE partitioned table.

• If you execute partition-level DDL statements on partitioned tables on which GSIs are created, you can execute the UPDATE GLOBAL INDEX statement to recreate GSIs. Examples:

  1. Create a range partitioned table named t1 that uses the a field as the partition key. Create a GSI 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 partition p1 in table t1 and recreate the GSI.

     ALTER TABLE t1 DROP PARTITION p1 UPDATE GLOBAL INDEX;

Syntax

You can create a local index or a global index by adding the LOCAL or GLOBAL keyword after the index name.

Examples

• Create a partitioned table named t1 that uses the a field as the partition key. 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;

• Create a partitioned table named t1 that uses the a field as the partition key. 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 test

Test tables

Create two partitioned tables that use the same schema and contain one million data entries. Create a local index on one table and a GSI on the other table.

In the following examples, the mytest1.big_table_1 and mytest2.big_table_1 partitioned tables are created. A local index is created on the mytest1.big_table_1 table and a GSI is created on the mytest2.big_table_1 table.

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

Execute SELECT, UPDATE, and DELETE statements that do not contain partition keys in query conditions on the two tables. Test the time that is required to execute the statements on the two tables with different numbers of partitions.

Test results

• The time that is required to execute a SELECT statement that does not contain partition keys in query conditions on the two tables.

• The time that is required to execute an UPDATE statement that does not contain partition keys in query conditions on the two tables.

• The time that is required to execute a DELETE statement that does not contain partition keys in query conditions on the two tables.

The preceding test results show that less time is required to execute the statements on the partitioned table on which GSIs are created. If the tables contain more data, the difference in execution time is larger.