PolarDB-X supports global secondary indexes. This topic describes how to create and use global secondary indexes. The sharding syntax that is supported by PolarDB-X is also described.

Limits

You can use global secondary indexes for partitioned tables based on the information that is described in this topic. To create global secondary indexes for partitioned tables, you must use the syntax that is described in CREATE INDEX.

Create a global secondary index

PolarDB-X supports the MySQL DDL syntax and provides a syntax that can be used to define global secondary indexes. The syntax usage is the same as the usage of the syntax that is used to create indexes on MySQL.

  • Create a global secondary index when you create a table.
  • Create a global secondary index for a table.
Note
  • Index name: The name of the index.
  • Index key columns: The columns on which the index is created. The names of these columns are used as shard keys of the index table. You must specify the shard keys in the clause that specifies an index sharding rule.
  • Covering columns: The columns in the index table that are not index columns. By default, the columns of the primary key and all shard keys of the base table are used as covering columns.
  • Sharding clause: The sharding clause in an index creation statement specifies the sharding algorithm of the index table. The syntax of the sharding clause is the same as the syntax that is used in CREATE TABLE statements.
  • The preceding examples describe the syntax that is used to create global secondary indexes for sharded tables. For information about the syntax that is used to create global secondary indexes for partitioned tables, see CREATE INDEX.

Examples:

# Create a global secondary index when you create a table.
CREATE TABLE t_order (
 `id` bigint(11) NOT NULL AUTO_INCREMENT,
 `order_id` varchar(20) DEFAULT NULL,
 `buyer_id` varchar(20) DEFAULT NULL,
 `seller_id` varchar(20) DEFAULT NULL,
 `order_snapshot` longtext DEFAULT NULL,
 `order_detail` longtext DEFAULT NULL,
 PRIMARY KEY (`id`),
 GLOBAL INDEX `g_i_seller`(`seller_id`) COVERING (`id`, `order_id`, `buyer_id`, `order_snapshot`) dbpartition by hash(`seller_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);
# Create a global secondary index for a table.
CREATE UNIQUE GLOBAL INDEX `g_i_buyer` ON `t_order`(`buyer_id`) 
    COVERING(`seller_id`, `order_snapshot`) 
    dbpartition by hash(`buyer_id`) tbpartition by hash(`buyer_id`) tbpartitions 3

Use a global secondary index

After you create a global secondary index, use one of the following methods to specify an index for a query.

  • Use a hint to specify an index
    You can use one of the following hints to specify the index that you want to use for a query.
    • FORCE INDEX({index_name})
      Example:
      SELECT a.*, b.order_id 
       FROM t_seller a 
         JOIN t_order b FORCE INDEX(g_i_seller) ON a.seller_id = b.seller_id 
       WHERE a.seller_nick="abc";
    • /*+TDDL:INDEX({table_name/table_alias}, {index_name})*/
      Example:
      /*+TDDL:index(a, g_i_buyer)*/ SELECT * FROM t_order a WHERE a.buyer_id = 123
    Note If you want to query data in columns that are not included in an index, the system queries the index table to retrieve the recorded primary key and shard keys of the base table and then queries the base table to retrieve the requested data.
  • Query data from an index table

    If the index of a table contains all columns that are requested in a query, the result data can be retrieved from the index table.

  • Select an index

    To query a table that contains a global secondary index, the PolarDB-X optimizer automatically selects the index table that requires the minimum overhead. The optimizer can select an index only from covering indexes. PolarDB-X In the following statement, the name of the base table is t_order, and a value in the seller_id column is specified as the condition. The id, order_snapshot, and seller_id columns are covered by the g_i_seller global secondary index. The g_i_seller global secondary index is the covering index and seller_id is the shard key of the g_i_seller index table. When this global secondary index is used to query data, PolarDB-X can query data from the covering columns. In this case, the number of shards that are scanned is reduced. The result of the EXPLAIN statement shows that the PolarDB-X optimizer selects the g_i_seller index table.

    EXPLAIN SELECT t_order.id,t_order.order_snapshot FROM t_order WHERE t_order.seller_id = 's1';
    IndexScan(tables="g_i_seller_sfL1_2", sql="SELECT `id`, `order_snapshot` FROM `g_i_seller` AS `g_i_seller` WHERE (`seller_id` = ?)")          
  • Use IGNORE INDEX or USE INDEX statements
    You can use the following hints to specify the indexes that can be used by the optimizer and the indexes that cannot be used by the optimizer.
    • IGNORE INDEX({index_name},...)
      Example:
      SELECT t_order.id,t_order.order_snapshot FROM t_order IGNORE INDEX(g_i_seller) WHERE t_order.seller_id = 's1';
    • USE INDEX({index_name},...)
      Example:
      SELECT t_order.id,t_order.order_snapshot FROM t_order USE INDEX(g_i_seller) WHERE t_order.seller_id = 's1';

Note

  • Before you create a global secondary index for a table, you must take note of the following limits:
    • You cannot create a global secondary index for a table that is not sharded or a broadcast table.
    • Unique global secondary indexes do not support prefix indexes.
    • When you create an index table, you must specify a name for the index.
    • When you create an index table, you can specify a database sharding rule or a rule for database sharding and table sharding. Note that you cannot specify only a table sharding rule. You must also specify a rule for database sharding.
    • The index keys of an index table must include all shard keys of the index table.
    • You cannot specify the same column as an index key column and a covering column.
    • By default, an index table contains the primary key columns and all shard key columns of the base table. If you do not specify the columns of the primary key and shard keys as index key columns, these columns are used as covering columns by default.
    • If all index columns of a local index are included in the index table, the local index is added to the index table by default.
    • If no local indexes are created on the index key columns of a global secondary index, a local index is automatically created on each index key column of the global secondary index.
    • By default, a composite index is created for a global secondary index that is created on multiple columns. The composite index applies to all index key columns of the global secondary index.
    • The length parameter can be used to specify only the length of a sharding key prefix that you want to use to create a local index.
    • When you create a global secondary index for a table, the system verifies the data after the global secondary index is created. The DDL statement that is used to create the global secondary index can be executed only after the data in the index table is verified.
    Note You can use the CHECK GLOBAL INDEX statement to verify and correct the data in an index table.
  • Before you use the ALTER TABLE statement to manage a table, you must take note of the following information.
    Statement Change shard keys of the table Change the primary key Change the unique column of a local index Change shard keys of the index table Change columns in the unique index of the table Change index columns Change covering columns
    ADD COLUMN N/A Not supported N/A N/A N/A N/A N/A
    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 if the unique index is created only on 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
    • You cannot use the DROP COLUMN statement to delete columns from global secondary indexes. If you delete columns from a global secondary index, the performance of the global secondary index may be affected in a negative manner. If you want to delete columns in a global secondary index, you can use the DROP INDEX statement to delete the global secondary index and then create a new global secondary index. You can also submit a ticket to contact Alibaba Cloud Technical Support.
    • In specific scenarios, the same column belongs to different column types that are described in the preceding table. For example, the same shard key column of a table is used as an index column and a covering column. When the same column is used as columns of different types in an index table and an operation is not supported by one of the column types. You cannot perform the operation on the column.
    The following table describes the ALTER TABLE statements that you can use to manage indexes.
    Statement Description
    ALTER TABLE ADD PRIMARY KEY Supported.
    ALTER TABLE ADD [UNIQUE/FULLTEXT/SPATIAL/FOREIGN] KEY Supported. You can use this statement to add a local index on the base table and the index table at the same time. The name of the local index cannot be the same as the name of the global secondary index.
    ALTER TABLE ALTER INDEX index_name {VISIBLE | INVISIBLE} Not supported.
    ALTER TABLE {DISABLE | ENABLE} KEYS Supported. You can use this statement only on the base table. You cannot use this statement to change the status of a global secondary index.
    ALTER TABLE DROP PRIMARY KEY Not supported.
    ALTER TABLE DROP INDEX You can use this statement to delete only a common index or a global secondary index.
    ALTER TABLE DROP FOREIGN KEY fk_symbol Supported. You can execute this statement only on the base table.
    ALTER TABLE RENAME INDEX Not supported.
    Note You cannot use the DROP COLUMN statement to rename a global secondary index. If you rename a global secondary index, the performance of the global secondary index may be affected in a negative manner. If you want to rename a global secondary index, use the DROP INDEX statement to delete the global secondary index and then create a new global secondary index. You can also submit a ticket to contact Alibaba Cloud Technical Support.
  • Before you manage a global secondary index table, you must take note of the following limits:
    • You cannot execute DDL or DML statements on index tables.
    • You cannot execute DML statements that contain NODE hints to update base tables or index tables.
  • Before you execute DDL statements on a table that contains a global secondary index, you must take note of the limits that are described in the following table.
    Statement Description
    DROP TABLE Supported.
    DROP INDEX Supported.
    TRUNCATE TABLE Not supported.
    RENAME TABLE Not supported.
    ALTER TABLE RENAME Not supported.
    Note
    • To ensure that data is consistent between a table and the index tables of the table, you cannot use TRUNCATE TABLE statements on the base table or index tables. If you want to delete data from a table and the index tables of the table, use the DELETE statement or the /*+TDDL:CMD_EXTRA(TRUNCATE_TABLE_WITH_GSI=TRUE)*/ hint.
    • You cannot use the RENAME TABLE or ALTER TABLE RENAME statement to rename a global secondary index. If you rename a global secondary index, the performance of the global secondary index may be affected in a negative manner. If you want to rename a global secondary index, use the DROP INDEX statement to delete the global secondary index and then create a new global secondary index. You can also submit a ticket to contact Alibaba Cloud Technical Support.
  • Before you execute DML statements on tables that contain global secondary indexes, you must take note of the following limits.
    • You cannot execute DML statements on index tables.
    • If a DML statement that is used to write data to a table based on a specified index fails to be executed, other DML statements cannot be executed on the base table and transactions cannot be committed on the base table.
    CREATE TABLE t_order(
      `id` bigint(11) NOT NULL AUTO_INCREMENT,
      `order_id` varchar(20) DEFAULT NULL,
      `buyer_id` varchar(20) DEFAULT NULL,
      `seller_id` varchar(20) DEFAULT NULL,
      `order_snapshot` longtext DEFAULT NULL,
      `order_detail` longtext DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `l_i_order` (`order_id`),
      GLOBAL INDEX `g_i_seller` (`seller_id`) dbpartition by hash(`seller_id`) tbpartition by hash(`seller_id`),
      GLOBAL UNIQUE INDEX `g_i_buyer` (`buyer_id`) COVERING (order_snapshot) dbpartition by hash(`buyer_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);
    
    SET DRDS_TRANSACTION_POLICY='XA';
    INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', 'buyer_1', 'seller_1');
    # The execution of this DML statement fails.
    INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_1', 'seller_1');
    # DML statements cannot be executed.
    INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_2', 'seller_2');
    # Transactions cannot be committed.
    COMMIT;