Use CREATE INDEX to create a Global Secondary Index in AUTO mode - PolarDB

PolarDB-X supports creating local indexes and global secondary indexes (GSIs) on databases in AUTO mode. Use CREATE INDEX for local indexes and CREATE GLOBAL INDEX to add a GSI to an existing table.

Local indexes

Local indexes follow standard MySQL syntax. See CREATE INDEX Statement in the MySQL 8.0 reference manual.

GSIs

A global secondary index (GSI) stores index data in a separate, partitioned index table. Unlike a local index, a GSI spans all partitions of the base table, so queries on non-partition-key columns can avoid full-table scans.

When to use a GSI:

Queries frequently filter on columns that are not the base table's partition key
Query performance suffers because the database must scan all partitions

When a local index is sufficient:

Queries always filter on the partition key or a prefix of it
Write throughput is a priority and the overhead of maintaining a separate index table is unacceptable

For the full list of GSI limits, see How to use global secondary indexes.

Usage notes

To use level-2 partition features in a GSI, your PolarDB-X instance must be version 5.4.17-16952556 or later.

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_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 — level-1 partition key:

PARTITION BY
      HASH({column_name | partition_func(column_name)}) PARTITIONS partition_count
    | KEY(column_list) PARTITIONS partition_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 — level-2 partition key:

SUBPARTITION BY
      HASH({column_name | partition_func(column_name)}) SUBPARTITIONS partition_count
    | KEY(column_list) SUBPARTITIONS partition_count
    | RANGE({column_name | partition_func(column_name)})
    | RANGE COLUMNS(column_list)
    | LIST({column_name | partition_func(column_name)})
    | LIST COLUMNS(column_list)

Supported partition functions:

YEAR, TO_DAYS, TO_MONTHS, TO_WEEKS, TO_SECOND, UNIX_TIMESTAMP, MONTH, DAYOFWEEK, DAYOFMONTH, DAYOFYEAR, SUBSTR, SUBSTRING

partition_specs_definition — level-1 partition specifications:

partition_specs_definition:
    hash_partition_list
  | range_partition_list
  | list_partition_list

hash_partition_list:
    ( hash_partition [, hash_partition, ...] )

hash_partition:
    PARTITION partition_name [partition_spec_options]
  | PARTITION partition_name SUBPARTITIONS partition_count [subpartition_specs_definition]

hash_subpartition_list:
    ( hash_subpartition [, hash_subpartition, ...] )

hash_subpartition:
    SUBPARTITION subpartition_name [partition_spec_options]

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 partition_count] [subpartition_specs_definition]

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
  | expr
  | value_list

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 partition_count] [subpartition_specs_definition]

list_subpartition_list:
    ( list_subpartition [, list_subpartition, ...] )

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

list_bound_value:
    default
  | value_set

value_set:
    value_list
  | (value_list) [, (value_list), ...]

value_list:
    value [, value, ...]

subpartition_specs_definition — level-2 partition specifications:

subpartition_specs_definition:
    hash_subpartition_list
  | range_subpartition_list
  | list_subpartition_list

partition_spec_options:

[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string']
[LOCALITY [=] 'dn=storage_inst_id_list']

table_option:

[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string']
[{CHARSET | CHARACTER SET} [=] charset]
[COLLATE [=] collation]
[TABLEGROUP [=] table_group_id]
[LOCALITY [=] 'dn=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(...)

Parameters

Parameter	Required	Description
`UNIQUE`	No	Enforces uniqueness on the indexed columns.
`index_name`	Yes	Name of the GSI. Must be unique within the table.
`index_type`	No	Index type, such as `USING BTREE`.
`tbl_name`	Yes	Name of the base table to index.
`index_sharding_col_name`	Yes	Partition key column(s) for the index table. Queries that filter on these columns are routed directly to the matching index partitions.
`COVERING (col_name, ...)`	No	Additional columns to store in the index table. Enables covering queries without reading the base table. The primary key and shard key of the base table are always included as default covering columns.
`PARTITION BY`	No	Partitioning strategy for the index table. Supports `HASH`, `KEY`, `RANGE`, `RANGE COLUMNS`, `LIST`, and `LIST COLUMNS`.
`SUBPARTITION BY`	No	Level-2 partitioning strategy. Requires instance version 5.4.17-16952556 or later.
`VISIBLE` / `INVISIBLE`	No	Controls whether the optimizer uses the index. Defaults to `VISIBLE`.
`LOCALITY`	No	Binds partitions to specific storage nodes. Format: `'dn=storage_inst_id_list'`.

Example

The following example creates a table, adds a GSI on the seller_id column, and then verifies the result.

Step 1: Create the base 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;

The base table is hash-partitioned on order_id with 16 partitions.

Step 2: Create the GSI

CREATE GLOBAL INDEX `g_i_seller`
    ON t_order (`seller_id`)
    COVERING (`order_snapshot`)
    PARTITION BY HASH(`seller_id`) PARTITIONS 16;

This creates an index table g_i_seller that:

Is partitioned by seller_id (the index partition key)
Stores order_snapshot as a covering column, so queries on seller_id that also need order_snapshot do not require a base-table lookup
Automatically includes id (primary key) and order_id (base table shard key) as default covering columns

Step 3: Verify local indexes

Run SHOW INDEX to view local indexes on the base table. For more information, see SHOW INDEX.

SHOW INDEX FROM t_order;

Sample output:

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

The output shows the primary key on id and the local index l_i_order on order_id. GSIs are not shown here.

Step 4: Verify the GSI

Run SHOW GLOBAL INDEX to view GSIs. For more information, see SHOW GLOBAL INDEX.

SHOW GLOBAL INDEX FROM t_order;

Sample output:

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

The STATUS column shows PUBLIC, meaning the GSI is active and visible to the optimizer. The COVERING_NAMES column confirms that id and order_id are included automatically.

Step 5: Inspect the index table structure

SHOW CREATE TABLE g_i_seller;

Sample output:

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

The index table includes:

id — primary key of the base table (without AUTO_INCREMENT)
order_id — shard key of the base table (default covering column)
seller_id — partition key of the index table

The base table's local index l_i_order is not copied to the index table.

What's next

GSI — learn about GSI architecture and internal behavior
How to use global secondary indexes — full list of GSI limits and usage guidelines
CREATE TABLE (DRDS mode) — GSI clause syntax reference for CREATE TABLE