CREATE INDEX
PolarDB-X supports the creation of both local indexes and Global Secondary Indexes (GSI), as well as the deletion of these two types of indexes.
Local Index
For details about local indexes, please refer to the CREATE INDEX Statement .
Global Secondary Index
For basic principles regarding Global Secondary Indexes, please see 全局二级索引.
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] ...
# Global Secondary Index specific syntax, see CREATE TABLE documentation for details
global_secondary_index_option:
[COVERING (col_name,...)]
[partition_options]
# Partition strategy definition
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
# Partition function definition
partition_func:
YEAR
| TO_DAYS
| TO_SECOND
| UNIX_TIMESTAMP
| MONTH
# Partition list definition
partition_list_spec:
hash_partition_list
| range_partition_list
| list_partition_list
# Column definition of Range / Range Columns type
hash_partition_list:
PARTITIONS partition_count
# Column definition of Range / Range Columns type
range_partition_list:
range_partition [, range_partition ...]
range_partition:
PARTITION partition_name VALUES LESS THAN {(expr | value_list)} [partition_spec_options]
# Column definition of List / List Columns type
list_partition_list:
list_partition [, list_partition ...]
list_partition:
PARTITION partition_name VALUES IN (value_list) [partition_spec_options]
The CREATE GLOBAL INDEX syntax series is used to add a GSI (Global Secondary Index) after a table has been created. This syntax series introduces the new keyword GLOBAL in the MySQL syntax, which is used to specify the index type being added as a GSI. Currently, there are certain restrictions when creating a GSI after a table has been created. For detailed information about GSI restrictions and conventions, please see How to Use Global Secondary Index.
For a detailed explanation of the global secondary index definition clause, please refer to CREATE TABLE.
Example
The following example demonstrates how to create a GSI after a table has been created, using the creation of a standard global secondary index as an instance.
# Create 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`);
# Create global index
CREATE GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) partition by hash(`seller_id`);
Main table: "t_order" is partitioned only by database, not by table, with the partitioning method being hashing on the "order_id" column.
Index table: "g_i_buyer" is partitioned only by database, not by table, with the partitioning method being hashing on the "buyer_id" column, and the specified covering column is "order_snapshot".
Index definition clause: GLOBAL INDEX g_i_seller ON t_order (seller_id) dbpartition by hash(seller_id).
By using SHOW INDEX, you can view index information, which includes local indexes on the partition key "order_id", and GSIs on "seller_id", "id", and "order_id", where "seller_id" is the partition key for the index table, and "id" and "order_id" are the default covering columns (primary key and the main table's partition key).
Note For details on the restrictions and conventions of GSI, please refer to How to Use Global Secondary Index, For a detailed explanation of SHOW INDEX, please refer to SHOW INDEX.
mysql> 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 | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
The command SHOW GLOBAL INDEX can be used to view GSI (Global Secondary Index) information specifically. For more details, please refer to SHOW GLOBAL INDEX.
mysql> 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 |
+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
To view the structure of the index table, the index table includes the primary key of the main table, the sharding key for database and table, the default covering columns, and the custom covering columns. The primary key column has the AUTO_INCREMENT attribute removed, and local indexes from the main table are also removed.
mysql> 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`) |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+