This topic describes the notes for using global secondary indexes (GSIs).

Notes for creating a GSI

  • You cannot create a GSI on ApsaraDB RDS for MySQL 5.6.
  • You cannot create a GSI in a single table or broadcast tables.
  • You cannot create a GSI in tables that do not have primary keys.
  • You cannot use a prefixed index in a UNIQUE GSI.
  • Specify the index name when you create an index table.
  • When you create an index table, specify database sharding rules or database sharding and table sharding rules. You cannot specify only table sharding rules or specify no sharding rules.
  • The INDEX column in index tables must contain all shard keys.
  • The name of a GSI cannot be the same as that of other local indexes in a primary table.
  • Index columns and covering columns cannot be the same in GSI definition clauses.
  • By default, an index table contains all the primary keys and shard keys of the primary table. If the primary keys and shard keys are not explicitly contained in the index columns, the keys are added to the covering columns by default.
  • For each local index in the primary table, if all the referenced columns are contained in the index table, the local index is added to the index table by default.
  • By default, an index is separately created for each index column of a GSI if no index exists.
  • By default, a composite local index is created for a GSI that contains multiple index columns. The composite local index contains all the index columns.
  • In an index definition, the length parameter of the index column is used only to create local indexes on the shard keys of the index table.
  • If you create a GSI after a table is created, data is automatically verified at the end of the creation process. The execution of the data definition language (DDL) statement to create the GSI can be successful only after the data passes the verification.
    Note You can also use the CHECK GLOBAL INDEX statement to verify or modify the index data.

Notes for executing the ALTER TABLE statement

  • The following table describes whether the ALTER TABLE statement can be executed to change columns.
    Statement Change the shard keys of the primary table Change the primary keys of the primary table, also referred to as the primary keys of the index table Change the local unique index column Change the shard keys of the index table Change the unique index column Change the index column Change the covering column
    ADD COLUMN No such scenario Not supported No such scenario No such scenario No such scenario No such scenario No such scenario
    ALTER COLUMN SET DEFAULT and ALTER COLUMN DROP DEFAULT Not supported Not supported Supported Not supported Not supported Not supported Not supported
    CHANGE COLUMN Not supported Not supported Supported Not supported Not supported Not supported Not supported
    DROP COLUMN Not supported Not supported Supported only when the unique key has only one column Not supported Not supported Not supported Not supported
    MODIFY COLUMN Not supported Not supported Supported Not supported Not supported Not supported Not supported
    Note
    • To ensure the stability and performance of GSIs, you are not allowed to delete columns from GSIs by executing the DROP COLUMN statement in a direct way. To delete some columns from a GSI,you can execute the DROP INDEX statement to delete the GSI and create another GSI. You can also Submit a ticket to contact technical support.
    • Overlaps exist in the preceding column classification. For example, the index column contains the shard keys of the index table. The covering column contains the shard keys of the primary table, the primary keys, and the specified columns. If a conflict occurs between Not supported and Supported, Not supported takes precedence over Supported.
  • The following table describes whether the ALTER TABLE statement can be executed to change indexes.
    Statement Supported or not
    ALTER TABLE ADD PRIMARY KEY Supported
    ALTER TABLE ADD [UNIQUE/FULLTEXT/SPATIAL/FOREIGN] KEY Supported. You can add local indexes to the primary table and the index table at the same time. An index name cannot be the same as the name of the GSI.
    ALTER TABLE ALTER INDEX index_name {VISIBLE | INVISIBLE} Supported. You can execute this statement on only the primary table. You are not allowed to change the GSI status.
    ALTER TABLE {DISABLE | ENABLE} KEYS Supported. You can execute this statement on only the primary table. You are not allowed to change the GSI status.
    ALTER TABLE DROP PRIMARY KEY Not supported
    ALTER TABLE DROP INDEX You can delete only a common index or a GSI.
    ALTER TABLE DROP FOREIGN KEY fk_symbol Supported. You can execute this statement on only the primary table.
    ALTER TABLE RENAME INDEX Not supported
    Note To ensure the stability and performance of GSIs, you are not allowed to rename GSIs by executing the ALTER TABLE RENAME INDEX statement in a direct way. To change the GSI name, you can execute the DROP INDEX statement to delete the GSI and create another GSI by using a new name. You can also Submit a ticket to contact technical support.

Notes for changing index tables

  • You cannot execute DDL or data manipulation language (DML) statements on index tables.
  • You cannot execute DML statements that contain NODE HINT to update primary tables or index tables.

Notes for executing other DDL statements

Statement Supported or not
DROP TABLE Supported
DROP INDEX Supported
TRUNCATE TABLE Not supported
RENAME TABLE Not supported
ALTER TABLE RENAME Not supported
Note
  • To ensure data consistency between primary tables and index tables, you are not allowed to execute the TRUNCATE TABLE statement. To clear data in the primary tables and the index tables, you can execute the DELETE statement. You can also Submit a ticket to contact technical support.
  • To ensure the stability and performance of GSIs, you are not allowed to rename GSIs by executing the RENAME TABLE or ALTER TABLE RENAME statement in a direct way. To change the GSI name, you can execute the DROP INDEX statement to delete the GSI, rename the table, and then create another GSI. You can also Submit a ticket to contact technical support.

Notes for executing DML statements