PolarDB-XYou can create and delete local indexes and global secondary indexes. This syntax applies to only AUTO mode databases.

Local indexes

For more information about local indexes, see CREATE INDEX.

Global secondary indexes

For more 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] ...    
# The syntax applies only to global secondary indexes. For more information, see the documentation of the CREATE TABLE statement.  
global_secondary_index_option:   
    [COVERING (col_name,...)]
    [partition_options]
    [VISIBLE|INVISIBLE]

# Specify the partitioning policy.
partition_options:
    PARTITION BY
          HASH({column_name | partition_func(column_name)})
        | KEY(column_list)
        | RANGE{({column_name | partition_func(column_name)}) 
        | RANGE COLUMNS(column_list)}
        | LIST{({column_name | partition_func(column_name)}) 
        | LIST COLUMNS(column_list)} }
    partition_list_spec

# Specify the partitioning function.
partition_func:
    YEAR
  | TO_DAYS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH

# Specify the type of the partition list.
partition_list_spec:
    hash_partition_list
  | range_partition_list
  | list_partition_list

# Specify the number of partitions that you want to generate by hash partitioning or key partitioning.
hash_partition_list:
    PARTITIONS partition_count

# Specify the ranges by which data is partitioned in range partitioning or range columns partitioning.
range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name VALUES LESS THAN {(expr | value_list)} [partition_spec_options]
    
# Specify the lists by which data is partitioned in list partitioning or list columns partitioning.
list_partition_list:
    list_partition [, list_partition ...]

list_partition:
    PARTITION partition_name VALUES IN (value_list) [partition_spec_options]

After you create a table, you can execute the CREATE GLOBAL INDEX statement to create a global secondary index. The GLOBAL keyword included in the CREATE GLOBAL INDEX statement specifies that the index to be added is a global secondary index. For more information about the limits and precautions on creating global secondary indexes for existing tables, see How to use global secondary indexes.

For more information about the CREATE GLOBAL INDEX statement, see CREATE TABLE ( DRDS mode ).

Examples

Execute the following statements to create a table and then create a global secondary index for the 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 partition by hash(`order_id`) partitions 16;
# Create a global secondary index.
CREATE GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) partition by hash(`seller_id`) partitions 16;
  • Base table: In the preceding example, the base table t_order is a partitioned table. The data is partitioned by using the hash algorithm. The order_id column is specified as the hashed partition key.
  • Index table: In the preceding example, g_i_seller is partitioned by using the hash algorithm. The seller_id column is specified as the hashed partition key. The order_snapshot column is specified as a covering column.
  • Clause that is used to define the global secondary index: GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) partition by hash(`seller_id`) partitions 16.
You can execute the SHOW INDEX statement to view the index information.
Note For more information about the limits and precautions on using global secondary indexes, see How to use global secondary indexes. For more information about the SHOW INDEX statement, see SHOW INDEX.
show index from t_order;

The following information is returned:

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table              | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_order_****_00000 |          0 | PRIMARY   |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t_order_****_00000 |          1 | l_i_order |            1 | order_id    | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.02 sec)
You can execute the SHOW GLOBAL INDEX statement to query only global secondary indexes. 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 execute the following statement to query the schema of an index table. An index table contains the primary key and shard key of the base table, the default covering columns, and the custom covering columns. An index table does not contain the AUTO_INCREMENT attribute for the primary key column or local secondary indexes for the base table.
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 partition by hash(`seller_id`) partitions 16 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+