Table management

Overview

Adjust Shard Key

A shard key is a field in a data table used to generate sharding rules for database or table sharding. PolarDB-X 1.0 allows you to adjust the shard key in the console. In PolarDB-X 1.0, the sharding method for a logical table is defined by the sharding function (including the number of shards and the routing algorithm) and the shard key (including its MySQL data type). In PolarDB-X 1.0, database sharding and table sharding are considered to use the same sharding method only if they use the same sharding function and shard key.

If database sharding and table sharding use the same sharding strategy, PolarDB-X 1.0 can locate a unique physical database shard and physical table shard based on the shard key value. If the sharding strategy used for database sharding differs from the sharding strategy used for table sharding, and the SQL query lacks either the database or table sharding condition, PolarDB-X 1.0 scans all physical database or table shards.

Disable full table scan

By default, PolarDB-X 1.0 enables the full table scan feature for new tables. However, frequent full table scans consume database resources and reduce query performance. PolarDB-X 1.0 allows you to manually disable the full table scan feature in the console.

View table schema

You can view the details of a table schema in the PolarDB-X 1.0 console.

Delete a data table

You cannot delete data tables directly in the PolarDB-X 1.0 console. Instead, you must log on to the database and use an SQL statement to delete the table. For more information, see Use an SQL statement to delete a table.

Go to the Tables page

1. Log on to the PolarDB-X 1.0 console.

2. In the top navigation bar, select the region where the instance is deployed.

3. In the left-side navigation pane, click Instances.

4. Find your target instance and click the instance ID.

5. In the left-side navigation pane, click Configuration and Management > Databases.

6. In the database list, find the target database and click the database name.

7. In the left-side navigation pane, click Table Management > Tables.

Adjust shard key

The Adjust Shard Key feature creates a new destination table based on the table name and shard key settings you specify. It then synchronizes data to the destination table in the background. You can use the new table directly, or use the rename statement to change the table name.

Limitations

• If the sharding function for either database sharding or table sharding uses YYYYMM, YYYYWEEK, or YYYYDD, the functions for both must be identical.

• STR_HASH sharding is not supported.

• MM, DD, WEEK, and MMDD are supported only as table-level sharding functions.

Procedure

1. On the Tables page, find your target table and click Adjust Shard Key in the Actions column.

2. In the dialog box that appears, adjust the shard key settings as needed, and then click Precheck.

   Important

   • Do not change the temporary or destination tables until the precheck is complete.

   • Do not adjust the shard key for a table that has a global secondary index. For more information about shard key selection and sharding methods, see CREATE TABLE and Overview of sharding functions.

   • DDL operations on the source and destination tables are not supported during data migration.

   • If you want to replace the original table with the new table by using the rename statement, stop application writes before you perform the operation.

   • If the new table uses an auto-increment primary key, you must manually update the sequence's start value after renaming the table with the rename statement. This action prevents primary key conflicts. For more information, see Modify Sequence.

3. In the dialog box that appears, wait until the precheck result shows Success, and then click Adjust.

4. Click the icon in the upper-right corner to view the progress of the Partitioning Tool - Migrate Data task until the Task Status changes to Success.

5. Click Stop Synchronization, and then click OK in the dialog box that appears.

6. Refresh the Data Table Information page. The Adjust Shard Key task is complete when the table's status returns to Normal.

Disable full table scan

1. On the Tables page, find your target table and click Advanced Settings in the Actions column.

   Note

   You can configure full table scan only when Table Attribute is set to Database Shard. You can use Adjust Shard Key to configure database sharding.

2. In the dialog box that appears, select No, and then click OK.

View table schema

Find the target table and click View Table Schema in the Actions column to view schema details, such as column names, data types, and whether a column is a primary key.