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

PolarDB-X allows you to create local indexes and global secondary indexes (GSIs), and delete these types of indexes. This syntax is applicable only to databases in AUTO mode.

Local indexes

For more information about local indexes, see CREATE INDEX Statement.

Usage notes

If you want to use features related to level-2 partitions in GSIs, the version of your PolarDB-X instance is 5.4.17-16952556 or later.

GSIs

For more information about the basic principles of GSIs, 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 GSIs. For more information, see the documentation of the CREATE TABLE statement.  
global_secondary_index_option:   
    [COVERING (col_name,...)]
    [partition_options]
    [VISIBLE|INVISIBLE]

# Specify the partition.
partition_options:
	partition_columns_definition
	[subpartition_columns_definition]
	[subpartition_specs_definition]/* Specify the templated level-2 partition.*/ 
  partition_specs_definition 

# Specify the partition key column of the level -1 partition.
partition_columns_definition:
		PARTITION BY
          HASH({column_name | partition_func(column_name)}) partitions_count
        | KEY(column_list) partitions_count
        | RANGE({column_name | partition_func(column_name)}) 
        | RANGE COLUMNS(column_list)
        | LIST({column_name | partition_func(column_name)}) 
        | LIST COLUMNS(column_list)

# Specify the partition key column of the level-2 partition.
subpartition_columns_definition:
		SUBPARTITION BY
          HASH({column_name | partition_func(column_name)}) subpartitions_count
        | KEY(column_list) subpartitions_count
        | RANGE({column_name | partition_func(column_name)}) 
        | RANGE COLUMNS(column_list)
        | LIST({column_name | partition_func(column_name)})
        | LIST COLUMNS(column_list)

partitions_count:
   PARTITIONS partition_count

subpartitions_count:
   SUBPARTITIONS partition_count

# Specify the partitioning function.
partition_func:
    YEAR
  | TO_DAYS
  | TO_MONTHS
  | TO_WEEKS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH
  | DAYOFWEEK
  | DAYOFMONTH
  | DAYOFYEAR
  | SUBSTR
  | SUBSTRING


# Specify three types of level -1 partitions.
partition_specs_definition:
	hash_partition_list
  | range_partition_list
  | list_partition_list

# Specify three types of level-2 partitions.
subpartition_specs_definition:
	hash_subpartition_list
  | range_subpartition_list
  | list_subpartition_list

# Specify the HASH or KEY subpartition of the level-1 partition.
hash_partition_list:
	  /* All subpartitions in the level-1 partition are of the HASH partitioning type.*/
	| ( hash_partition [, hash_partition, ...] )

hash_partition:
    PARTITION partition_name [partition_spec_options] /* Specify the pure level -1 partition or templated subpartition.*/
  | PARTITION partition_name subpartitions_count [subpartition_specs_definition] /* Specify the non-templated subpartition under the level -1 partition.*/

# Specify the HASH or KEY subpartition of the level-2 partition.
hash_subpartition_list:
  | empty
  | ( hash_subpartition [, hash_subpartition, ...] )

hash_subpartition:
	SUBPARTITION subpartition_name [partition_spec_options]

# Specify the RANGE or RANGE COLUMNS subpartition of the level-1 partition.
range_partition_list:
    ( range_partition [, range_partition, ... ] )

range_partition:
    	PARTITION partition_name VALUES LESS THAN (range_bound_value) [partition_spec_options] /* Specify the pure level -1 partition or templated subpartition.*/
    | PARTITION partition_name VALUES LESS THAN (range_bound_value) [[subpartitions_count] [subpartition_specs_definition]] /* Specify the non-templated subpartition under the level -1 partition.*/
   
# Specify the RANGE or RANGE COLUMNS subpartition of the level-2 partition.
range_subpartition_list:
	( range_subpartition [, range_subpartition, ... ] )

range_subpartition:
    SUBPARTITION subpartition_name VALUES LESS THAN (range_bound_value) [partition_spec_options]
  
range_bound_value:
	  maxvalue /* Specify the maximum number of RANGE partitions.*/
	| expr /* Specify the range boundary value for a single partition key column.*/
	| value_list /* Specify the range boundary values for multiple partition key columns.*/

# Specify the LIST or LIST COLUMNS subpartition of the level-1 partition.
list_partition_list:
    (list_partition [, list_partition ...])

list_partition:
    	PARTITION partition_name VALUES LESS THAN (range_bound_value) [partition_spec_options] /* Specify the pure level -1 partition or templated subpartition.*/
    | PARTITION partition_name VALUES IN (list_bound_value) [[subpartitions_count] [subpartition_specs_definition]] /* Specify the non-templated subpartition under the level -1 partition.*/

# Specify the LIST or LIST COLUMNS subpartition of the level-2 partition.
list_subpartition_list:
	(list_subpartition [, list_subpartition ...])

list_subpartition:
	SUBPARTITION subpartition_name VALUES IN (list_bound_value) [partition_spec_options]

list_bound_value:
		default /* Specify the default LIST partition.*/
	|	value_set

value_set:
	  value_list /* Specify a set of values for a single partition key column.*/
	| (value_list) [, (value_list), ...] /* Specify a set of values for multiple partition key columns.*/

value_list:
	value [, value, ...]

partition_spec_options:
	    [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string']
        [LOCALITY [=] locality_option]

table_option:
 	    [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string']
        [{CHARSET | CHARACTER SET} [=] charset]
        [COLLATE [=] collation]
        [TABLEGROUP [=] table_group_id]
        [LOCALITY [=] locality_option]  

locality_option:
    'dn=storage_inst_id_list'
    
storage_inst_id_list:
    storage_inst_id[,storage_inst_id_list]

local_partition_definition:
		LOCAL PARTITION BY RANGE (column_name)
    [STARTWITH 'yyyy-MM-dd']
    INTERVAL interval_count [YEAR|MONTH|DAY]
    [EXPIRE AFTER expire_after_count]
    [PRE ALLOCATE pre_allocate_count]
    [PIVOTDATE pivotdate_func]
    [DISABLE SCHEDULE]
    
pivotdate_func:
		NOW()
  |	DATE_ADD(...)
  | DATE_SUB(...)

The CREATE GLOBAL INDEX syntax is used to add a GSI after a table is created. This syntax introduces the GLOBAL keyword in the MySQL syntax to specify that only GSIs can be added. The creation of a GSI after a table is created has some limits. For more information about the limits on GSIs, see How to use global secondary indexes.

For more information about a clause that defines GSIs, see CREATE TABLE (DRDS mode).

Examples

The following example describes how to create a GSI after a table is created.

# 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 GSI.
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 t_order base table 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, the index table 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 defines indexes: GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) partition by hash(`seller_id`) partitions 16.

Execute the SHOW INDEX statement to view indexes, such as local indexes on the order_id column and GSIs on the id and order_id columns. seller_id is also specified as the shard key of the index table. In addition, id and order_id are the default covering columns. id is also specified as the primary key, and order_id is the shard key of the base table.

Note

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

show index from t_order;

Sample result:

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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)

Execute the SHOW GLOBAL INDEX statement to view GSIs. 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 |
+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

Execute the following statements to view the structure of the index table. The table contains the primary key of the base table, shard key, default covering column, and custom covering column. The primary key column removes the AUTO_INCREMENT attribute and the local indexes of 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 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+