PolarDB:How to use global secondary indexes

Usage notes

The methods described in this topic to create and use global secondary indexes also apply to tables partitioned by using the automatic partitioning mode. However, to create global secondary indexes for tables partitioned by using the automatic partitioning mode, you must use the syntax described in CREATE INDEX (AUTO mode).

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 for 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 must specify a database sharding rule or a rule for database sharding and table sharding. Note that you cannot specify only a table sharding rule.

  • 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.

  • In DRDS mode databases, if all index columns of a local index in the base table 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 base 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

  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

  The DROP COLUMN statement cannot be used 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.

• 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 base table and the index tables of the base table, you cannot use TRUNCATE TABLE statements on the base table or index tables. If you want to delete data from a base table and the index tables of the base table, use the DELETE statement or the /*+TDDL:CMD_EXTRA(TRUNCATE_TABLE_WITH_GSI=TRUE)*/ hint.

  • The RENAME TABLE or ALTER TABLE RENAME statement cannot be used 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.

• 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;

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.

• Define a global secondary index when you create a table

• Add a global secondary index for a table

Examples:

# Define 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`);
# Add a global secondary index
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 a query requires the columns that are not contained in an index, you can query the index table first to retrieve all the recorded primary keys and the shard keys of the base table. Then, query the base table to retrieve the values of the missing columns. For more information, see How to use INDEX hints.

• 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 base 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. In the following SQL 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 statement

  You can use the following hints to specify the indexes that can be used by the optimizer and 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';