PolarDB-X支持创建局部索引和全局二级索引 (Global Secondary Index，GSI) ，同时支持删除这两种索引。本语法仅适用于AUTO模式数据库。
局部索引
关于局部索引，详情请参见CREATE INDEX Statement。
注意事项
若全局索引要使用二级分区的相关功能，实例版本必须为5.4.17-16952556及以上。
全局二级索引
关于全局二级索引基本原理，请参见全局二级索引。
语法
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] ...
# 全局二级索引特有语法，具体说明请参见CREATE TABLE文档
global_secondary_index_option:
[COVERING (col_name,...)]
[partition_options]
[VISIBLE|INVISIBLE]
# 分区定义
partition_options:
partition_columns_definition
[subpartition_columns_definition]
[subpartition_specs_definition]/*用于定义模板化二级分区*/
partition_specs_definition
# 一级分区的分区列定义
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)
# 二级分区的分区列定义
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
# 分区函数定义
partition_func：
YEAR
| TO_DAYS
| TO_MONTHS
| TO_WEEKS
| TO_SECOND
| UNIX_TIMESTAMP
| MONTH
| DAYOFWEEK
| DAYOFMONTH
| DAYOFYEAR
| SUBSTR
| SUBSTRING
# 一级分区的三种分区类型定义
partition_specs_definition:
hash_partition_list
| range_partition_list
| list_partition_list
# 二级分区的三种分区类型定义
subpartition_specs_definition:
hash_subpartition_list
| range_subpartition_list
| list_subpartition_list
# 一级分区的Hash/Key分区定义
hash_partition_list:
/*hash允许不指定各个具体分区定义*/
| ( hash_partition [, hash_partition, ...] )
hash_partition:
PARTITION partition_name [partition_spec_options] /*适用于纯一级分区或使用模板化子分区*/
| PARTITION partition_name subpartitions_count [subpartition_specs_definition] /*适用于定义一级分区下的非模板化子分区*/
# 二级级分区的Hash/Key分区定义
hash_subpartition_list:
| empty
| ( hash_subpartition [, hash_subpartition, ...] )
hash_subpartition:
SUBPARTITION subpartition_name [partition_spec_options]
# 一级分区的Range/Range Columns分区定义
range_partition_list:
( range_partition [, range_partition, ... ] )
range_partition:
PARTITION partition_name VALUES LESS THAN (range_bound_value) [partition_spec_options] /*适用于纯一级分区或使用模板化子分区*/
| PARTITION partition_name VALUES LESS THAN (range_bound_value) [[subpartitions_count] [subpartition_specs_definition]] /*适用于定义一级分区下的非模板化子分区*/
# 二级分区的Range/Range Columns分区定义
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 /*适用于定义range的maxvalue分区*/
| expr /*适用于使用单个分区列时range边界值*/
| value_list /*适用于使用多个分区列时range边界值*/
# 一级分区的List/List Columns分区定义
list_partition_list:
(list_partition [, list_partition ...])
list_partition:
PARTITION partition_name VALUES IN (list_bound_value) [partition_spec_options] /*适用于纯一级分区或使用模板化子分区*/
| PARTITION partition_name VALUES IN (list_bound_value) [[subpartitions_count] [subpartition_specs_definition]] /*适用于定义一级分区下的非模板化子分区*/
# 二级分区的List/List Columns分区定义
list_subpartition_list:
(list_subpartition [, list_subpartition ...])
list_subpartition:
SUBPARTITION subpartition_name VALUES IN (list_bound_value) [partition_spec_options]
list_bound_value:
default /*适用于定义list的default分区*/
| value_set
value_set:
value_list /*适用于使用单个分区列时values集合*/
| (value_list) [, (value_list), ...] /*适用于使用多个分区列时的values集合*/
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(...)
CREATE GLOBAL INDEX系列语法用于在建表后添加GSI，该系列语法在MySQL语法上新引入了GLOBAL关键字，用于指定添加的索引类型为GSI。目前建表后创建GSI存在一定限制，关于GSI的限制与约定，详情请参见如何使用全局二级索引。
关于全局二级索引定义子句的详细说明，请参见CREATE TABLE（DRDS模式）。
示例
下面以建立普通全局二级索引为例，介绍如何在建表后创建GSI。
# 先创建表
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 GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) partition by hash(`seller_id`) partitions 16;主表：”t_order“是分区表，分区的拆分方式为按照”order_id“列进行哈希。
索引表：”g_i_seller“按照”seller_id“列进行哈希，指定覆盖列为”order_snapshot“。
索引定义子句：
GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) partition by hash(`seller_id`) partitions 16。
通过SHOW INDEX查看索引信息，包含拆分键order_id上的局部索引，seller_id、id和order_id上的GSI，其中seller_id为索引表的拆分键，id和order_id为默认的覆盖列（主键和主表的拆分键）。
说明
关于GSI的限制与约定，详情请参见如何使用全局二级索引，SHOW INDEX详细说明，请参见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_****_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)通过SHOW GLOBAL INDEX可以单独查看GSI信息，详情请参见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 |
+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+ 查看索引表的结构，索引表包含主表的主键、分库分表键、默认的覆盖列和自定义覆盖列，主键列去除了AUTO_INCREMENT属性，并且去除了主表中的局部索引。
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 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+