CREATE TABLE (DRDS mode) - PolarDB - Alibaba Cloud Documentation Center

You can use the CREATE TABLE statement to create a table. This topic describes the CREATE TABLE statement and the clauses, parameters, and basic methods that you can use to create tables. The syntax described in this topic applies only to databases in Distributed Relational Database Service (DRDS) mode.

Syntax

CREATE [SHADOW] TABLE [IF NOT EXISTS] tbl_name
    (create_definition, ...)
    [table_options]
    [drds_partition_options]

create_definition:
    col_name column_definition
  | mysql_create_definition
  | [UNIQUE] GLOBAL INDEX index_name [index_type] (index_sharding_col_name,...)
      [global_secondary_index_option]
      [index_option] ...

# Clauses related to global secondary indexes
global_secondary_index_option:
    [COVERING (col_name,...)]
    [drds_partition_options]
    [VISIBLE|INVISIBLE]

# Clauses that you can use to implement database and table sharding
drds_partition_options:
    DBPARTITION BY db_partition_algorithm
    [TBPARTITION BY table_partition_algorithm [TBPARTITIONS num]]
    [LOCALITY=locality_option]

db_sharding_algorithm:
    HASH([col_name])
  | {YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  | UNI_HASH(col_name)
  | RIGHT_SHIFT(col_name, n)
  | RANGE_HASH(col_name, col_name, n)

table_sharding_algorithm:
    HASH(col_name)
  | {MM|DD|WEEK|MMDD|YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  | UNI_HASH(col_name)
  | RIGHT_SHIFT(col_name, n)
  | RANGE_HASH(col_name, col_name, n)

# Clauses that you can use to specify the storage location of a non-sharded table when you create the table
locality_option:
    'dn=storage_inst_id_list'

storage_inst_id_list:
    storage_inst_id[,storage_inst_id_list]
    
# MySQL DDL syntax
index_sharding_col_name:
    col_name [(length)] [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

index_type:
    USING {BTREE | HASH}

Note

The Standard Edition does not support the database and table sharding syntax.
The PolarDB-X DDL syntax is developed based on the MySQL syntax. The preceding section only provides the different parts between the MySQL syntax and the PolarDB-X DDL syntax. For more information, see MySQL documentation.

Clauses and parameters to implement database and table sharding

DBPARTITION BY hash(partition_key): specifies the database shard key and the hash-based database sharding algorithm.
TBPARTITION BY { HASH(column) | {MM|DD|WEEK|MMDD|YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(column): optional. This clause specifies the table sharding method. By default, this clause works in the same manner as the DBPARTITION BY clause.
TBPARTITIONS num: optional. This clause specifies the number of physical tables in each database shard. The default value of this parameter is 1. If table sharding is not required, you do not need to configure this parameter.
For information about sharding functions, see Sharding functions.

Clauses and parameters to define GSIs

[UNIQUE] GLOBAL: specifies that the index is a global secondary index (GSI). UNIQUE GLOBAL specifies that the index is a unique GSI.
index_name: specifies the name of the index. The index table uses the same name.
index_type: specifies the type of the local index on the shard keys in the index table. For information about the supported types of local indexes, see the MySQL documentation.
index_sharding_col_name,...: specifies the index columns. The index columns include all shard key columns of the index table and does not include other columns. For more information, see How to use global secondary indexes.
global_secondary_index_option: specifies the extended syntax for PolarDB-X GSIs.
- COVERING (col_name,...): specifies the covering columns. The covering columns include all columns of the index table except the index columns. By default, the covering columns include the primary key columns and the shard key columns of the base table. For more information, see How to use global secondary indexes.
- drds_partition_options: specifies one or more sharding clauses for the index table. For more information, see the "Clauses and parameters to implement database and table sharding" section of this topic.
index_option: specifies the attributes of the local index on the shard keys of the index table. For more information, see the MySQL documentation.

Shadow table clause for end-to-end stress testing

SHADOW: creates a shadow table for end-to-end stress testing. The table name must use _test_ as the prefix. The table name after the prefix must be the same as the associated formal table name, and the formal table must be created before the shadow table.

LOCALITY

The LOCALITY clause specifies the storage location of a non-sharded table.

Note

After you specify the storage location of a database or a non-sharded table by using the LOCALITY clause, you cannot change the storage location.
The storage location of a non-sharded table in a database is independent of the storage location of the database. If you do not specify a storage location when you create a non-sharded table in a PolarDB-X instance, the table is stored on a random data node. Subsequent non-sharded tables for which you do not specify a storage location are also stored on the same data node.

Create a non-sharded table

Create a non-sharded table. The table is a logical table.

CREATE TABLE single_tbl(
 id bigint not null auto_increment, 
 name varchar(30), 
 primary key(id)
);

View the node topology of the logical table. In this example, the topology shows that a non-sharded table is created in database 0.

show topology from single_tbl;
+------+------------------------------------------------------------------+------------+
| ID   | GROUP_NAME                                                       | TABLE_NAME |
+------+------------------------------------------------------------------+------------+
|    0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | single_tbl |
+------+------------------------------------------------------------------+------------+
1 row in set (0.01 sec)

For example, a PolarDB-X instance has two data nodes: polardbx-storage-0-master and polardbx-storage-1-master. A database named db1 is created on the polardbx-storage-1-master data node. If you want to create a table in the db1 database and specify polardbx-storage-0-master as the storage location of the table, use the following syntax:

CREATE TABLE tb1 (id int) LOCALITY='dn=polardbx-storage-0-master';

After the table is created, you can execute the following statement to query the topology of the table:

SHOW TOPOLOGY FROM tb1;

The following result is returned:

+----+------------------+------------+.
| ID | GROUP_NAME       | TABLE_NAME | 
+----+------------------+------------+
| 0  | DB1_000000_GROUP | tb1        |
+----+------------------+------------+
1 row in set

Note

The preceding result shows that the tb1 table is stored in the DB1_000000_GROUP database shard.

Create a table and distribute the table data by using database sharding

For example, eight database shards are created. Create a table and shard the table data by using database sharding based on the hash value of the ID column. The table is a logical table.

CREATE TABLE multi_db_single_tbl(
  id bigint not null auto_increment, 
  name varchar(30), 
  primary key(id)
) dbpartition by hash(id);

View the node topology of the logical table. In this example, the topology shows that one table shard is created in each of the eight database shards. The table shards are not further sharded.

show topology from multi_db_single_tbl;
+------+------------------------------------------------------------------+---------------------+
| ID   | GROUP_NAME                                                       | TABLE_NAME          |
+------+------------------------------------------------------------------+---------------------+
|    0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | multi_db_single_tbl |
|    1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | multi_db_single_tbl |
|    2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0002_RDS | multi_db_single_tbl |
|    3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0003_RDS | multi_db_single_tbl |
|    4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0004_RDS | multi_db_single_tbl |
|    5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0005_RDS | multi_db_single_tbl |
|    6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0006_RDS | multi_db_single_tbl |
|    7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | multi_db_single_tbl |
+------+------------------------------------------------------------------+---------------------+
8 rows in set (0.01 sec)

Create a table and distribute the table data by using database sharding and table sharding

You can use one of the following sharding methods to implement database sharding and table sharding:

Use a hash function for sharding.
Use a hash function that contains double fields for sharding.
Use a date function for sharding.

Note

In the following examples, eight database shards are created.

Use a hash function for sharding

Create a table and distribute the table data by using database sharding and table sharding. Use a hash function on the ID column to distribute data across the eight database shards, and then use a hash function to shard the data in each database shard across three physical tables based on the bid column.

CREATE TABLE multi_db_multi_tbl(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30), 
 primary key(id)
) dbpartition by hash(id) tbpartition by hash(bid) tbpartitions 3;

Query the node topology of the logical table. In this example, the topology shows that three table shards are created in each database shard.

show topology from multi_db_multi_tbl;
+------+------------------------------------------------------------------+-----------------------+
| ID   | GROUP_NAME                                                       | TABLE_NAME            |
+------+------------------------------------------------------------------+-----------------------+
|    0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | multi_db_multi_tbl_00 |
|    1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | multi_db_multi_tbl_01 |
|    2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | multi_db_multi_tbl_02 |
|    3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | multi_db_multi_tbl_03 |
|    4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | multi_db_multi_tbl_04 |
|    5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | multi_db_multi_tbl_05 |
|    6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0002_RDS | multi_db_multi_tbl_06 |
|    7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0002_RDS | multi_db_multi_tbl_07 |
|    8 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0002_RDS | multi_db_multi_tbl_08 |
|    9 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0003_RDS | multi_db_multi_tbl_09 |
|   10 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0003_RDS | multi_db_multi_tbl_10 |
|   11 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0003_RDS | multi_db_multi_tbl_11 |
|   12 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0004_RDS | multi_db_multi_tbl_12 |
|   13 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0004_RDS | multi_db_multi_tbl_13 |
|   14 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0004_RDS | multi_db_multi_tbl_14 |
|   15 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0005_RDS | multi_db_multi_tbl_15 |
|   16 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0005_RDS | multi_db_multi_tbl_16 |
|   17 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0005_RDS | multi_db_multi_tbl_17 |
|   18 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0006_RDS | multi_db_multi_tbl_18 |
|   19 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0006_RDS | multi_db_multi_tbl_19 |
|   20 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0006_RDS | multi_db_multi_tbl_20 |
|   21 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | multi_db_multi_tbl_21 |
|   22 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | multi_db_multi_tbl_22 |
|   23 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | multi_db_multi_tbl_23 |
+------+------------------------------------------------------------------+-----------------------+
24 rows in set (0.01 sec)

Query the sharding rule of the logical table. In this example, the rule uses a hash function based on the ID column to implement database sharding and uses a hash function based on the bid column to implement table sharding.

show rule from multi_db_multi_tbl;
+------+--------------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
| ID   | TABLE_NAME         | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |
+------+--------------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
|    0 | multi_db_multi_tbl |         0 | id               | hash                | 8                  | bid              | hash                | 3                  |
+------+--------------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
1 row in set (0.01 sec)

Use a hash function that contains double fields for sharding

The shard keys must be of a character or numeric data type.
Routing method: A hash value is calculated based on the last N characters of one shard key to determine the specific shards in which the data is stored. N specifies the third parameter for the hash function. For example, if the RANGE_HASH(COL1, COL2, N) function is used, COL1 is selected and then truncated to obtain the last N characters for calculation. If COL1 does not exist, COL2 is selected for calculation.
Scenarios: Two shard keys are required for database sharding and queries can be correctly directed based on one shard key. For example, a PolarDB-X database has eight physical database shards.
- The database needs to be sharded based on buyer IDs and order IDs.
- Only buyer IDs or order IDs are available for queries.

In this case, you can execute the following DDL statement to create an order table:

create table test_order_tb (
 id bigint not null auto_increment,
 seller_id varchar(30) DEFAULT NULL,
 order_id varchar(30) DEFAULT NULL,
 buyer_id varchar(30) DEFAULT NULL,
 create_time datetime DEFAULT NULL,
 primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by RANGE_HASH(buyer_id, order_id, 10) tbpartition by RANGE_HASH(buyer_id, order_id, 10) tbpartitions 3;

Note

The two shard keys cannot be modified.
If the two shard keys point to different database shards or table shards, data fails to be inserted.

Use a date function for sharding

In addition to a hash function, you can also use the MM, DD, WEEK, or MMDD date function as a sharding algorithm to shard tables. The following examples show how to use a date function for sharding.

Create a table and distribute the table data by using database sharding and table sharding. Implement database sharding based on the hash value of the userId column. Implement table sharding in each database shard based on the DAY_OF_WEEK value calculated from the actionDate column by using the WEEK(actionDate) function to distribute the table data into seven physical tables .

For example, if a value in the actionDate column is 2017-02-27, which is Monday, the value returned by the WEEK(actionDate) function is 2. In this case, the data record that includes 2017-02-27 is stored in the table shard that corresponds to 2 based on the following equation: 2(2 % 7 = 2). The table shard is located in a database shard and is named user_log_2. If a value in the actionDate column is 2017-02-26, which is Sunday, the value returned by the WEEK(actionDate) function is 1. In this case, the data record that includes 2017-02-26 is stored in the table shard that corresponds to 1 based on the following equation: 1(1 % 7 = 1). The table shard is located in a database shard and is named user_log_1.

CREATE TABLE user_log(
 userId int, 
 name varchar(30), 
 operation varchar(30), 
 actionDate DATE
) dbpartition by hash(userId) tbpartition by WEEK(actionDate) tbpartitions 7;

Query the node topology of the logical table. In this example, the topology shows that seven table shards are created in each database shard based on the weekdays in a week.

show topology from user_log;
+------+------------------------------------------------------------------+------------+
| ID   | GROUP_NAME                                                       | TABLE_NAME |
+------+------------------------------------------------------------------+------------+
|    0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_0 |
|    1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_1 |
|    2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_2 |
|    3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_3 |
|    4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_4 |
|    5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_5 |
|    6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_6 |
|    7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_0 |
|    8 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_1 |
|    9 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_2 |
|   10 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_3 |
|   11 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_4 |
|   12 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_5 |
|   13 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_6 |
...
|   49 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_0 |
|   50 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_1 |
|   51 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_2 |
|   52 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_3 |
|   53 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_4 |
|   54 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_5 |
|   55 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_6 |
+------+------------------------------------------------------------------+------------+
56 rows in set (0.01 sec)

Note

An ellipsis (…) is used to omit some data because the returned result is long.

Query the sharding rule of the logical table. In this example, the rule uses a hash function based on the database shard key userId to implement database sharding, and uses the WEEK date function based on the table shard key actionDate to implement table sharding.

show rule from user_log;
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
| ID   | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
|    0 | user_log   |         0 | userId           | hash                | 8                  | actionDate       | week                | 7                  |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
1 row in set (0.00 sec)

This way, if you specify a database shard key and a table shard key, you can query the physical database shards and the physical tables that belong to the physical database shards to which the SQL statement is routed.

Create a table and distribute the table data by using database sharding and table sharding. Implement database sharding based on the hash value of the userId column. Implement table sharding in each database shard based on the MONTH_OF_YEAR value calculated from the actionDate column by using the MM(actionDate) date function to distribute the table data into 12 physical tables .

For example, if a value in the actionDate column is 2017-02-27, the value returned by the MM(actionDate) function is 02. In this case, the data record that includes 2017-02-27 is stored in the table shard that corresponds to 02 based on the following equation: 02(02 % 12 = 02). The table shard is located in a database shard and is named user_log_02. For example, if a value in the actionDate column is 2016-12-27, the value returned by the MM(actionDate) function is 12. In this case, the data record that includes 2016-12-27 is stored in the table shard that corresponds to 00 based on the following equation:00(12 % 12 = 00). The table shard is located in a database shard and is named user_log_00.

CREATE TABLE user_log2(
 userId int, 
 name varchar(30), 
 operation varchar(30), 
 actionDate DATE
) dbpartition by hash(userId) tbpartition by MM(actionDate) tbpartitions 12;

Query the node topology of the logical table. In this example, 12 table shards are created in each database shard based on the months in a year.

show topology from user_log2;
+------+------------------------------------------------------------------+--------------+
| ID   | GROUP_NAME                                                       | TABLE_NAME   |
+------+------------------------------------------------------------------+--------------+
|    0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_00 |
|    1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_01 |
|    2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_02 |
|    3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_03 |
|    4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_04 |
|    5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_05 |
|    6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_06 |
|    7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_07 |
|    8 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_08 |
|    9 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_09 |
|   10 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_10 |
|   11 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_11 |
|   12 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_00 |
|   13 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_01 |
|   14 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_02 |
|   15 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_03 |
|   16 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_04 |
|   17 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_05 |
|   18 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_06 |
|   19 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_07 |
|   20 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_08 |
|   21 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_09 |
|   22 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_10 |
|   23 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_11 |
...
|   84 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_00 |
|   85 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_01 |
|   86 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_02 |
|   87 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_03 |
|   88 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_04 |
|   89 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_05 |
|   90 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_06 |
|   91 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_07 |
|   92 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_08 |
|   93 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_09 |
|   94 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_10 |
|   95 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_11 |
+------+------------------------------------------------------------------+--------------+
96 rows in set (0.02 sec)

Note

An ellipsis (…) is used to omit some data because the returned result is long.

Query the sharding rule of the logical table. In this example, the rules uses a hash function based on the database shard key userId to implement database sharding, and uses the MM date function based on the table shard key actionDate to implement table sharding.

show rule from user_log2;
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
| ID   | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
|    0 | user_log2  |         0 | userId           | hash                | 8                  | actionDate       | mm                  | 12                 |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
1 row in set (0.00 sec)

Create a table and distribute the table data by using database sharding and table sharding. Implement database sharding based on the hash value of the userId column. Implement table sharding in each database shard based on the DAY_OF_MONTH value calculated by using the DD(actionDate) function to distribute the table data into 31 physical tables.

For example, if a value in the actionDate column is 2017-02-27, the value returned by the DD(actionDate) function is 27. In this case, the data record that includes 2017-02-27 is stored in the table shard that corresponds to 27 based on the following equation: 27(27 % 31 = 27). This table shard is located in a database shard and is named user_log_27.

CREATE TABLE user_log3(
 userId int, 
 name varchar(30), 
 operation varchar(30), 
 actionDate DATE
) dbpartition by hash(userId) tbpartition by DD(actionDate) tbpartitions 31;

Query the node topology of the logical table. In this example, 31 table shards are created in each database shard based on the days in a month.

show topology from user_log3;
+------+------------------------------------------------------------------+--------------+
| ID   | GROUP_NAME                                                       | TABLE_NAME   |
+------+------------------------------------------------------------------+--------------+
|    0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_00 |
|    1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_01 |
|    2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_02 |
|    3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_03 |
|    4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_04 |
|    5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_05 |
|    6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_06 |
|    7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_07 |
|    8 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_08 |
|    9 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_09 |
|   10 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_10 |
|   11 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_11 |
|   12 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_12 |
|   13 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_13 |
|   14 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_14 |
|   15 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_15 |
|   16 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_16 |
|   17 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_17 |
|   18 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_18 |
|   19 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_19 |
|   20 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_20 |
|   21 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_21 |
|   22 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_22 |
|   23 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_23 |
|   24 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_24 |
|   25 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_25 |
|   26 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_26 |
|   27 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_27 |
|   28 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_28 |
|   29 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_29 |
|   30 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_30 |
...
|  237 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_20 |
|  238 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_21 |
|  239 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_22 |
|  240 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_23 |
|  241 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_24 |
|  242 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_25 |
|  243 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_26 |
|  244 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_27 |
|  245 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_28 |
|  246 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_29 |
|  247 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_30 |
+------+------------------------------------------------------------------+--------------+
248 rows in set (0.01 sec)

Note

An ellipsis (…) is used to omit some data because the returned result is long.

Query the sharding rule of the logical table. In this example, the rule uses a hash function based on the database shard key userId to implement database sharding, and uses the DD date function based on the table shard key actionDate to implement table sharding.

show rule from user_log3;
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
| ID   | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
|    0 | user_log3  |         0 | userId           | hash                | 8                  | actionDate       | dd                  | 31                 |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
1 row in set (0.01 sec)

Create a table and distribute the table data by using database sharding and table sharding. Implement database sharding based on the hash value of the userId column. Implement table sharding in each database shard based on the DAY_OF_YEAR % 365 value calculated by using the MMDD(actionDate) tbpartitions 365 date function to distribute the table data into 365 physical tables.

For example, if a value in the actionDate column is 2017-02-27, the value returned by the MMDD(actionDate) function is 58. In this case, the data record that includes 2017-02-27 is stored in the table shard that corresponds to 58. The table shard is located in a database shard and is named user_log_58.

CREATE TABLE user_log4(
 userId int, 
 name varchar(30), 
 operation varchar(30), 
 actionDate DATE
) dbpartition by hash(userId) tbpartition by MMDD(actionDate) tbpartitions 365;

Query the node topology of the logical table. In this example, the node topology shows that 365 table shards are created in each database shard based on the days in a year.

show topology from user_log4;
+------+------------------------------------------------------------------+---------------+
| ID   | GROUP_NAME                                                       | TABLE_NAME   |
+------+------------------------------------------------------------------+---------------+
...
| 2896 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_341 |
| 2897 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_342 |
| 2898 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_343 |
| 2899 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_344 |
| 2900 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_345 |
| 2901 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_346 |
| 2902 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_347 |
| 2903 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_348 |
| 2904 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_349 |
| 2905 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_350 |
| 2906 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_351 |
| 2907 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_352 |
| 2908 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_353 |
| 2909 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_354 |
| 2910 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_355 |
| 2911 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_356 |
| 2912 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_357 |
| 2913 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_358 |
| 2914 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_359 |
| 2915 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_360 |
| 2916 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_361 |
| 2917 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_362 |
| 2918 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_363 |
| 2919 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_364 |
+------+------------------------------------------------------------------+---------------+
2920 rows in set (0.07 sec)

Note

An ellipsis (…) is used to omit some data because the returned result is long.

Query the sharding rule of the logical table. In this example, the rule uses a hash function based on the database shard key userId to implement database sharding, and uses the MMDD date function based on the table shard key actionDate to implement table sharding.

show rule from user_log4;
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
| ID   | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
|    0 | user_log4  |         0 | userId           | hash                | 8                  | actionDate       | mmdd                | 365                |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
1 row in set (0.02 sec)

Create a table and distribute the table data by using database sharding and table sharding. Implement database sharding based on the hash value of the userId column. Implement table sharding in each database shard based on the DAY_OF_YEAR % 10 value calculated by using MMDD(actionDate) tbpartitions 10 date function to distribute the table data into 10 physical tables.

CREATE TABLE user_log5(
 userId int, 
 name varchar(30), 
 operation varchar(30), 
 actionDate DATE
) dbpartition by hash(userId) tbpartition by MMDD(actionDate) tbpartitions 10;

Query the node topology of the logical table. In this example, the node topology shows that 10 table shards are created in each database shard. The logical table is sharded to 10 physical tables in each database shard based on days in a year (365 days).

show topology from user_log5;
+------+------------------------------------------------------------------+--------------+
| ID   | GROUP_NAME                                                       | TABLE_NAME   |
+------+------------------------------------------------------------------+--------------+
|    0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_00 |
|    1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_01 |
|    2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_02 |
|    3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_03 |
|    4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_04 |
|    5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_05 |
|    6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_06 |
|    7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_07 |
|    8 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_08 |
|    9 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_09 |
...
|   70 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_00 |
|   71 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_01 |
|   72 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_02 |
|   73 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_03 |
|   74 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_04 |
|   75 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_05 |
|   76 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_06 |
|   77 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_07 |
|   78 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_08 |
|   79 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_09 |
+------+------------------------------------------------------------------+--------------+
80 rows in set (0.02 sec)

Note

An ellipsis (…) is used to omit some data because the returned result is long.

Query the sharding rule of the logical table. In this example, the rule uses a hash function based on the database shard key userId to implement database sharding, and uses the MMDD date function to implement table sharding based on the actionDate key to route the table data to 10 physical tables in each database shard.

show rule from user_log5;
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
| ID   | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
|    0 | user_log5  |         0 | userId           | hash                | 8                  | actionDate       | mmdd                | 10                 |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
1 row in set (0.01 sec)

Other attributes in the MySQL CREATE TABLE statement

When you execute the MySQL CREATE TABLE statement to create a table, you can specify a sharding method and other attributes for the table. The following sample code provides an example on how to specify other attributes:

CREATE TABLE multi_db_multi_tbl(
  id bigint not null auto_increment, 
  name varchar(30), 
  primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(id) tbpartition by hash(id) tbpartitions 3;

GSIs

Note

In the following examples, eight database shards are created.

Define a GSI

Examples

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`),
 GLOBAL INDEX `g_i_seller`(`seller_id`) dbpartition by hash(`seller_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);

t_order: the base table that is sharded by using database sharding based on the hash value of the order_id column.
g_i_seller: the index table that is sharded by using database sharding based on the hash value of the seller_id column. No covering column is specified.
GLOBAL INDEX `g_i_seller`(`seller_id`) dbpartition by hash(`seller_id`): the clause that is used to define the GSI.

You can execute the SHOW INDEX statement to query index information. In this example, you can query information about the local index on the order_id shard key and the GSIs on the seller_id, id, and order_id columns. seller_id is the shard key of the index table, and id and order_id are the default covering columns. The default covering columns include the primary key columns and the shard key columns of the base table.

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

You can execute the SHOW GLOBAL INDEX statement to query information about the 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 |
+--------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| d7     | t_order | 1          | g_i_seller | seller_id   | id, order_id   | NULL       | seller_id        | HASH                | 8                  |                  | NULL                | NULL               | PUBLIC |
+--------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

You can execute the following statement to query the schema of the index table. The index table contains the primary key of the base table, the shard keys, and the default covering columns. The primary key column does not have the AUTO_INCREMENT attribute, and the base table does not have a local index.

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 dbpartition by hash(`seller_id`) |
+------------+-----------------------------------------------------------+

Define a unique 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`),
 UNIQUE GLOBAL INDEX `g_i_buyer`(`buyer_id`) COVERING(`seller_id`, `order_snapshot`) 
   dbpartition by hash(`buyer_id`) tbpartition by hash(`buyer_id`) tbpartitions 3
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);

t_order: the base table that is sharded by using database sharding based on the hash value of the order_id column.
g_i_buyer: the index table that is sharded by using database sharding and table sharding based on the hash value of the buyer_id column. Covering columns include the seller_id and order_snapshot columns.
UNIQUE GLOBAL INDEX 'g_i_buyer'('buyer_id') COVERING('seller_id', 'order_snapshot') dbpartition by hash('buyer_id') tbpartition by hash('buyer_id') tbpartitions 3: the clause that is used to define the unique GSI.

You can execute the SHOW INDEX statement to query index information. In this example, you can query the local index on the order_id shard key and the unique GSI on the buyer_id, id, order_id, seller_id, and order_snapshot columns. buyer_id is the shard key of the index table, and id and order_id are the default covering columns. The default covering columns include the primary key columns and the shard key columns of the base table. seller_id and order_snapshot are the covering columns that are specified when the unique GSI is defined.

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_dthb |          0 | PRIMARY                 |            1 | id             | A         |           0 |     NULL | NULL   |      | BTREE      |          |               |
| t_order_dthb |          1 | auto_shard_key_order_id |            1 | order_id       | A         |           0 |     NULL | NULL   | YES  | BTREE      |          |               |
| t_order      |          0 | g_i_buyer               |            1 | buyer_id       | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | INDEX    |               |
| t_order      |          1 | g_i_buyer               |            2 | id             | NULL      |           0 |     NULL | NULL   |      | GLOBAL     | COVERING |               |
| t_order      |          1 | g_i_buyer               |            3 | order_id       | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
| t_order      |          1 | g_i_buyer               |            4 | seller_id      | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
| t_order      |          1 | g_i_buyer               |            5 | order_snapshot | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
+--------------+------------+-------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+

You can execute the SHOW GLOBAL INDEX statement to query information about the GSI. 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 |
+--------+---------+------------+-----------+-------------+-----------------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| d7     | t_order | 0          | g_i_buyer | buyer_id    | id, order_id, seller_id, order_snapshot | NULL       | buyer_id         | HASH                | 8                  | buyer_id         | HASH                | 3                  | PUBLIC |
+--------+---------+------------+-----------+-------------+-----------------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

You can execute the following statement to query the schema of the index table. The index table contains the primary key of the base table, the shard keys, the default covering columns, and the covering columns that are specified when the unique GSI is defined. The primary key column does not have the AUTO_INCREMENT attribute, and the base table does not have a local index. By default, a table is created for the unique GSI to ensure that the values in the indexed columns are unique across the entire database.

show create table g_i_buyer;
+-----------+--------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                           |
+-----------+--------------------------------------------------------------------------------------------------------+
| g_i_buyer | CREATE TABLE `g_i_buyer` (
  `id` bigint(11) NOT NULL,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext,
  PRIMARY KEY (`id`),
  UNIQUE KEY `auto_shard_key_buyer_id` (`buyer_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`buyer_id`) tbpartition by hash(`buyer_id`) tbpartitions 3 |
+-----------+--------------------------------------------------------------------------------------------------------+