PolarDB-X supports global secondary indexes. This topic describes how to create and use global secondary indexes in tables partitioned by using the DRDS mode.

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 ( DRDS 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.
    StatementChange shard keys of the base tableChange the primary keyChange the unique column of a local indexChange shard keys of the index tableChange columns in the unique index of the tableChange index columnsChange covering columns
    ADD COLUMNN/ANot supportedN/AN/AN/AN/AN/A
    ALTER COLUMN SET DEFAULT and ALTER COLUMN DROP DEFAULTNot supportedNot supportedSupportedNot supportedNot supportedNot supportedNot supported
    CHANGE COLUMNNot supportedNot supportedSupportedNot supportedNot supportedNot supportedNot supported
    DROP COLUMNNot supportedNot supportedSupported only if the unique index is created only on one columnNot supportedNot supportedNot supportedNot supported
    MODIFY COLUMNNot supportedNot supportedSupportedNot supportedNot supportedNot supportedNot supported
    Note
    • The DROP COLUMN statement cannot be used 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.
    • 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.
    StatementDescription
    ALTER TABLE ADD PRIMARY KEYSupported.
    ALTER TABLE ADD [UNIQUE/FULLTEXT/SPATIAL/FOREIGN] KEYSupported. 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} KEYSSupported. 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 KEYNot supported.
    ALTER TABLE DROP INDEXYou can use this statement to delete only a common index or a global secondary index.
    ALTER TABLE DROP FOREIGN KEY fk_symbolSupported. You can execute this statement only on the base table.
    ALTER TABLE RENAME INDEXNot 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.
    StatementDescription
    DROP TABLESupported
    DROP INDEXSupported
    TRUNCATE TABLENot supported
    RENAME TABLENot supported
    ALTER TABLE RENAMENot 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 table456789
  • Add a global secondary index for a table456789
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 tables partitioned by using the DRDS mode. For information about the syntax that is used to create global secondary indexes for tables partitioned by using the automatic partitioning mode, see CREATE INDEX ( DRDS mode ).

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