CREATE INDEX ( DRDS mode ) - PolarDB - Alibaba Cloud Documentation Center

You can use the CREATE INDEX statement to create indexes. PolarDB-X supports local secondary indexes and global secondary indexes. You can create or delete local secondary indexes and global secondary indexes based on your business requirements. This syntax applies to only DRDS mode databases.

Create a local secondary index

For information about how to create a local secondary index, see CREATE INDEX Statement.

Create a global secondary index

For basic information about global secondary indexes, see GSI.

Syntax

CREATE [UNIQUE]
    GLOBAL INDEX index_name [index_type]
    ON tbl_name (index_sharding_col_name,...)
    global_secondary_index_option
    [index_option]
    [algorithm_option | lock_option] ...

# Specific syntax that is used to create a global secondary index. For more information, see CREATE TABLE Statement on the official website of MySQL.
global_secondary_index_option:
    [COVERING (col_name,...)]
    drds_partition_options
    [VISIBLE|INVISIBLE]

# Clauses that are used for database sharding and table sharding. For more information, see CREATE TABLE Statement on the official website of MySQL.
drds_partition_options:
    DBPARTITION BY db_sharding_algorithm
    [TBPARTITION BY {table_sharding_algorithm} [TBPARTITIONS num]]

db_sharding_algorithm:
    HASH([col_name])
  | {YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  | UNI_HASH(col_name)
  | RIGHT_SHIFT(col_name, n)
  | RANGE_HASH(col_name, col_name, n)

table_sharding_algorithm:
    HASH(col_name)
  | {MM|DD|WEEK|MMDD|YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  | UNI_HASH(col_name)
  | RIGHT_SHIFT(col_name, n)
  | RANGE_HASH(col_name, col_name, n)

 # The DDL syntax that is supported by MySQL databases.
index_sharding_col_name:
    col_name [(length)] [ASC | DESC] # The length parameter can be used only when you create a local secondary index based on the shard key of an index table.

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

After you create a table, you can use CREATE GLOBAL INDEX to create a global secondary index. The syntax that is defined by PolarDB-X supports the GLOBAL keyword. You can use the GLOBAL keyword to specify an index as a global secondary index. Before you create a global secondary index for an existing table, you must take note of the limits on global secondary indexes. For more information about the limits and conventions on global secondary indexes, see How to use global secondary indexes.

For more information about the clauses that can be used to create global secondary indexes, see CREATE TABLE ( DRDS mode ).

Examples

The following example shows how to create a global secondary index for an existing table.

# 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`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);
# Create a global secondary index.
ALTER TABLE t_order ADD UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`) COVERING (`order_snapshot`) dbpartition by hash(`buyer_id`);

Base table: In the preceding example, a table named t_order is created in each database shard. The data is sharded by using the hash algorithm based on the order_id column.
Index table: In the preceding example, an index table named g_i_buyer is created for each database shard. The index values are sharded by using the hash algorithm based on the buyer_id column. The order_snapshot column is specified as a custom covering column.
Clause that defines the global secondary index: GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) dbpartition by hash(`seller_id`).

You can use the SHOW INDEX statement to query information about indexes of a table. In the following example, the returned information includes local secondary indexes that are created based on the order_id shard key and the global secondary indexes that are created based on the seller_id, id, and order_id columns. The shard key of the index table is seller_id. The id field is the primary key of the base table, and the order_id field is the shard key of the base table. The id and order_id columns are the default covering columns.

Note

For more information about the limits and conventions on global secondary indexes, see How to use global secondary indexes. For more information about SHOW INDEX, see SHOW INDEX.

show index from t_order;  
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+   
| TABLE   | NON_UNIQUE | KEY_NAME   | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULL | INDEX_TYPE | COMMENT  | INDEX_COMMENT |    
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+   
| t_order |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |          |               |  
| t_order |          1 | l_i_order  |            1 | order_id    | A         |           0 |     NULL | NULL   | YES  | BTREE      |          |               |   
| t_order |          1 | g_i_seller |            1 | seller_id   | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | INDEX    |               | 
| t_order |          1 | g_i_seller |            2 | id          | NULL      |           0 |     NULL | NULL   |      | GLOBAL     | COVERING |               |   
| t_order |          1 | g_i_seller |            3 | order_id    | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |   
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+

You can use the SHOW GLOBAL INDEX statement to query information about global secondary indexes of a table. For more information, see SHOW GLOBAL INDEX.

show global index from t_order;    
+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+    
| SCHEMA              | TABLE   | NON_UNIQUE | KEY_NAME   | INDEX_NAMES | COVERING_NAMES | INDEX_TYPE | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT | STATUS |
+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+    
| ZZY3_DRDS_LOCAL_APP | t_order | 1          | g_i_seller | seller_id   | id, order_id   | NULL       | seller_id        | HASH                | 4                  |                  | NULL                | NULL               | PUBLIC |   
+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

You can use the following statement to query the schema of an index table. A global secondary index table contains the primary key of the base table, shard keys, default covering columns, and custom covering columns. In a global secondary index table, the primary key column is not auto-increment, and local secondary indexes are not contained.

show create table g_i_seller;   
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+   
| Table      | Create Table                                                                                                                                                                                                                                                                                  |   
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
| g_i_seller | CREATE TABLE `g_i_seller` (  
  `id` bigint(11) NOT NULL,  
  `order_id` varchar(20) DEFAULT NULL,  
  `seller_id` varchar(20) DEFAULT NULL,   
  PRIMARY KEY (`id`),  
  KEY `auto_shard_key_seller_id` (`seller_id`) USING BTREE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`seller_id`) | 
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+