All Products
Search
Document Center

PolarDB:GSIs

Last Updated:Jan 16, 2024

PolarDB for MySQL allows you to create global secondary indexes (GSIs) on partitioned tables. GSIs enable you to use partitioned table as ordinary tables and greatly simplify the use of partition keys on partitioned table.

Note
  • The GSI feature is in the canary release phase. You can submit a ticket to apply for the feature.

  • For more information about GSIs, join the DingTalk group 24490017825.

Background information

Traditionally, local indexes are used on partitioned tables. For logical tables, local indexes are partitioned in the same way. The data of individual partitions for a local index is from the data of individual partitions for a partitioned table. The partitions for a local index correspond on a one-to-one basis to the partitions of a partitioned table. The data of a local index is sorted only within a single partition. To create a locally unique index, you must include all partition keys in the index field.

When a partitioned table contains only local indexes, the following issues occur on the partitioned table due to limits on partition keys:

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

  • When sorted data is required for local indexes, the index data may not be globally sorted in case of cross-partition queries even if the data of each partition in a local index is sorted. 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, global indexes are opposed to local indexes. Global indexes are not partitioned but use data from all partitions. Therefore, the data of a global index is sorted in the partitioned table. To create a globally unique index, you do not need to include all partition keys in the index field.

Prerequisites

A cluster of PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.7 or later. You can check the version of the cluster as set out in Query Version.

Limits

  • GSIs can be created only on partitioned table that use the InnoDB engine, but not hybrid partitioned tables.

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

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

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

  • Generated columns are not supported for tables where GSIs are created.

  • Executing partition-level DDL statements (except for creating range or list partitions) invalidates existing GSIs. You must delete all existing GSIs on the table and then create GSIs again. You can also execute the UPDATE GLOBAL INDEX statement to create GSIs again.

Feature enhancements

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

  • The Instant ADD COLUMN feature is supported on partitioned tables that have GSIs created.

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

  • You can switch a table that has GSIs created to an interval range partitioned table or create GSIs on an INTERVAL RANGE partitioned table.

  • You can execute the UPDATE GLOBAL INDEX statement to create GSIs again if you execute partition-level DDL statements on partitioned tables that have GSIs created. Example:

    1. Create a range partitioned table named t1 that uses a field as the partition key and that has the k1 GSL created 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 in the t1 table and create the GSL again.

      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.

Note

If the GLOBAL keyword is not specified when you create an index, a local index is created by default.

Examples

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

    CREATE TABLE t1(
      a INT PRIMARY KEY,
      b INT,
      INDEX k1(b) GLOBAL
    ) PARTITION BY HASH(a) PARTITIONS 3;
  • Create the t1 partitioned table that uses the a field as the partition key, and then create the k1 global index and the k2 globally unique index on the b field of the t1 table.

    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 1 million data entries. A local index is created on one table and a GSI is created on the other table.

The mytest1.big_table_1 and a mytest2.big_table_1 partitioned tables are created in the following examples. 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

The query conditions of SELECT, UPDATE, and DELETE statements do not contain partition keys. Test the time to execute these statements on partitioned tables that contain different numbers of partitions and that has a local index or a GSI created.

Test results

  • The time to execute the SELECT statement which does not contain partition keys in query conditions on partitioned tables that contain different numbers of partitions and where a local index or a GSI is created.image

  • The time to execute the UPDATE statement which does not contain partition keys in query conditions on partitioned tables that contain different numbers of partitions and where a local index or a GSI is created.image

  • The time to execute the DELETE statement which does not contain partition keys in query conditions on partitioned tables that contain different numbers of partitions and where a local index or a GSI is created.image

The preceding test results show that executing the SELECT, UPDATE, and DELETE statements which do not contain partition keys in query conditions on partitioned tables where GSIs are created consumes less time. When the tables contain more data, the difference in execution time is higher.